Thread: Using Postgresql Replication

On Fri, 2010-02-19 at 05:49 -0500, Manasi Save wrote:
> I want a sychronized but a master-slave replication.
<snip>

> So, I thought of PGCluster.

Forget about it. Check pgcluster archives about recent discussions about
it. There is no stable sync replication solution for PostgreSQL right
now.

Regards,
--
Devrim GÜNDÜZ, RHCE
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

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.

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?

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

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?
>