Re: Staging Database

On Wed, Aug 7, 2013 at 12:38 PM, BladeOfLight16
<bladeoflight16(at)gmail(dot)com> wrote:

> The client would be modifying data,
> not end users. (It's a catalog site; the client is a non-profit that's
> publishing information in their field.) You're right in guessing that the
> data is not high volume.
>

In such case this means that the master node will be the staging one,
with the hot standby the one publicly available. Replication could be
the right and easiest solution assuming that
- updates and addition can be "available" immediatly to the public
(that is they are cut/filtered from your web application)
- you don't change the schema or the structure in a way that could
damage your existing front-end application

Another idea that comes into my mind is to use three database and a
pgbouncer/pgpool approach: one node is the current indipendent node
and is accessed via pgpool from the web application, the staging
database is the master and is replicated in the third slave node. Once
the staging is completed the third node is the new pgpool target, so
that the web application start seeing the new database, the old
indipendent node becomes the new slave of the staging that is always
the master. But as you can imagine, this is a more complex approach.

Please consider that in the case changes involve some damage to the
application you will probably need a full re-deploy, and therefore a
dump-restore could be the easiest approach (and you can create very
complex scripts for managing that).

Hope this helps.

Luca