Re: Using Postgresql Replication

On Friday 19 February 2010 06:27:10 Brad Nicholson wrote:
> On Fri, 2010-02-19 at 05:49 -0500, Manasi Save wrote:
> > Hi All,
> >
> >
> > I am very new to Postgres in that I have to setup replication. For
> > Which I am very confused between Postgres's replication types.
> >
> > I want a sychronized but a master-slave replication.
> >
> > I have read about Slony-I replication method. But I am confused if I
> > have to manually setup slony-I replication for all databases. As the
> > interface which I have seen for slony-I setup, it has given
> > replication tab under every database.
>
> With Slony, you must set up every database, table, and sequence you want
> to replicate yourself.
>
>
> Slony is an asynchronous replication engine, btw.
>
> > If I have to manually define replication master and slave node for any
> > database I am creating then it is not a feasible option for me to use
> > Slony-I.
> >
> > SLONY-I = Is it possible that all the databases getting created on
> > Master Node will be replicated to specified Slave node.

SLONY is database specific, not cluster specific, so you'll need to create a
SLONY replication cluster (which may or may not have multiple replication
sets) for each database that you want to replicate

It's pretty straight forward but it is a manual setup and there are some down
sides depending on what your use case is, for example if all your write
connections are hitting the master and you want to do a switch-over so you can
bring the current master node down for maintenance, things can become overly
complicated trying to coordinate the switchover commands for each
cluster/replication set.

The easy fix is to stop or pause application traffic during the switch, then it
becomes a non issue.

However if this is not an option you could end up with a tough issue in that
say one db switches over quickly and another takes more time at which point
you have the master for one db on node2 and the other still on node1 thus
until the other db finishes it's switch you have a scenario where writes for
db1 are only allowed on node2 and writes for db2 are only allowed on node1.

>
> Not possible.
>
> > So, I thought of PGCluster. But it comes with multi-master
> > replication.
> >
> > PGCluster = Is it possible that I can configure Master-Slave
> > replication in PGCluster.
> >
> >
> > Please let me know if I am unclear in any point. Any input will be a
> > great help.
>
> What do you need replication for? DR? Query offloading? Custom schema
> or data transformations on a replica?
>
> Can you justify the cost warm standby hardware?
>