Thread: Postgresql Replication Comparison Required

Postgresql Replication Comparison Required

From
saurabh gupta
Date:
I am doing POC on Posgtresql replication. I am using latest version of postgresql i.e. 9.1. There are multiple replication solutions avaliable in the market (PGCluster, Pgpool-II, Slony-I). Postgresql also provide in-built replication solutions (Streaming replication, Warm Standby and hot standby). I am confused which solution is best for the financial application for which I am doing POC. The application will write around 160 million records with row size of 2.5 KB in database. My questions is for following scenarios which replication solution will be suitable: 

If I would require replication for backup purpose only 
If I would require to scale the reads 
If I would require High Avaliability and Consistency 
Also It will be very helpful if you can share the perfomance or experience with postgresql replication solutions. 

Thanks

Re: Postgresql Replication Comparison Required

From
Gabriele Bartolini
Date:
 Hello,

 in general my advice would be to stick with native features, therefore
 use either Streaming Replication (or alternatively log shipping
 replication). You might need some tools to help you manage the cluster,
 clients routing and balancing but I suggest you look into this later.

 On Thu, 29 Dec 2011 16:55:00 +0530, saurabh gupta
 <saurabh.b85@gmail.com> wrote:
> If I would require replication for backup purpose only 

 For disaster recovery, you need physical base backups with continous
 archiving
 (http://www.postgresql.org/docs/9.1/interactive/continuous-archiving.html)

>  If I would require to scale the reads 

 You need Hot Standby here (and you might need to route read only
 applications to one of the slaves somehow - but you can worry about this
 later).

>  If I would require High Avaliability and Consistency 

 Streaming replication. With 9.1 you also have Synchronous Streaming
 Replication which means you have zero data loss of committed
 transactions within your PostgreSQL cluster. Another useful tool you
 might want to look into is repmgr (www.repmgr.org).

>  Also It will be very helpful if you can share the perfomance or
> experience with postgresql replication solutions. 

 I wish I could help you more here, but most of our professional work is
 performed under strict NDAs.

 An interesting and useful documentation section is this also:
 http://www.postgresql.org/docs/9.1/interactive/high-availability.html

 I hope this helps.

 Cheers,
 Gabriele
--
  Gabriele Bartolini - 2ndQuadrant Italia
  PostgreSQL Training, Services and Support
  Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it