Обсуждение: Postgres Replication Options
Hello,
I am looking for a replication solution for PG 9.x. Idea is to have one master replication server and multiple (around 20) slave servers read only. I see PG 9 has inbuilt Streaming replication. Is this the best replication solution. How about slony? Which option will keep the slave nodes in closest sync to master and which is more stable.
I found some stuff on google, but looking for some guidance from people who has “been there and done that”.
-JP
On Tue, Feb 8, 2011 at 5:34 PM, Rangi, Jai <jrangi@automotive.com> wrote: > I am looking for a replication solution for PG 9.x. Idea is to have one > master replication server and multiple (around 20) slave servers read only. I know (anecdotally) of at least one organization that's using Bucardo [1] to synchronize many more machines than that. I've used it to manage eight to ten slaves, simultaneously with master-master replication without significant issue, and with a replication lag typically in the low single-digit seconds, at the outside. In order to reduce replication overhead and keep things performant with as many slaves as you're talking about, you might want to use "cascaded" slaves, where the R/W master updates a subset of the slaves, which then propagate the changes to slaves of their own. Look at the documentation for "makedelta" to enable cascading slave tiers. rls [1] http://bucardo.org -- :wq
Il 09/02/11 01:34, Rangi, Jai ha scritto:
With PostgreSQL 9.0 in terms of builtin replication you can take advantage of Hot Standby based on either WAL shipping or on Streaming Replication. I would personally stick with builtin solutions if you plan to have full replicas of your databases, even in terms of maintenance and upgrades later on (in the long term). Some tools, including replication manager (repmgr) are coming out in order to manage HA clusters (for more info: http://blog.2ndquadrant.com/en/2011/01/easier-postgresql-90-clusters.html).Hello,
I am looking for a replication solution for PG 9.x. Idea is to have one master replication server and multiple (around 20) slave servers read only. I see PG 9 has inbuilt Streaming replication. Is this the best replication solution. How about slony? Which option will keep the slave nodes in closest sync to master and which is more stable.
Cheers,
Gabriele
-- Gabriele Bartolini - 2ndQuadrant ItaliaPostgreSQL Training, Services and Supportgabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it
--hope this helps
/Kevin
Hi,
Il 09/02/11 01:34, Rangi, Jai ha scritto:
With PostgreSQL 9.0 in terms of builtin replication you can take advantage of Hot Standby based on either WAL shipping or on Streaming Replication. I would personally stick with builtin solutions if you plan to have full replicas of your databases, even in terms of maintenance and upgrades later on (in the long term). Some tools, including replication manager (repmgr) are coming out in order to manage HA clusters (for more info: http://blog.2ndquadrant.com/en/2011/01/easier-postgresql-90-clusters.html).Hello,
I am looking for a replication solution for PG 9.x. Idea is to have one master replication server and multiple (around 20) slave servers read only. I see PG 9 has inbuilt Streaming replication. Is this the best replication solution. How about slony? Which option will keep the slave nodes in closest sync to master and which is more stable.
Cheers,
Gabriele
-- Gabriele Bartolini - 2ndQuadrant ItaliaPostgreSQL Training, Services and Supportgabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it
Il 09/02/11 17:53, CS DBA ha scritto:
One of the main considerations per Hot Standby vs SLONY is replication scope. With Hot Standby you get everything that occurs in the cluster, across all databases.Yep, I agree with you Kevin regarding the replication scope. I assumed that Jai was looking for a full replica of the server.
database. So, IMHO I'd go with Hot Standby if I wanted to replicate the full cluster and SLONY if I wanted to "slice & dice" tables and target slaves (i.e. replicate all tables for a single db to slave 1, only 50 tables to slave 2, etc...)If you are looking for table level replication, I suggest also too look at Londiste and Skytools.
Ciao,
Gabriele
-- Gabriele Bartolini - 2ndQuadrant ItaliaPostgreSQL Training, Services and Supportgabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it