On Wed, Aug 7, 2013 at 6:00 AM, BladeOfLight16 <bladeoflight16@gmail.com> wrote:
> The company I work for has a client who has expressed interest in having a
> staging database for their data. Staging as in they add data, do some QCing,
> then push to a database with an identical schema to make it public.
> Fortunately, we're not doing that right now, but it's something they may
> want later on. So I wanted to at least start thinking about it. What are
> some ways of going about it? Some kind of replication set up comes to mind,
> but it would need to be on demand, not constant. It also could be as
> infrequent as quarterly, if that makes a difference. Preferably, it would
> only require one server with PostgreSQL installed, but I'm not ruling out
> multiple servers.
>
Not really helpful, but here are my considerations.
The low frequency and the preference for a single server suggest me a
dump and restore cycle on two databases, assuming this is possible due
to not high volume data.
I would also consider some way of data partitioning in order to
isolate the data that has to be pushed from staging into the master
(you say data is only added or queried).
The problem for replication is that both the staging and the master
would be in read-write mode, so sounds to me like a multi-master
setup.
Luca