PostgreSQL replication strategies

Question

Right now we are using PostgreSQL 8.3 (on Linux) as a database backend to our Ruby on Rails web application.

Considering that on PostgreSQL database we actively use row level blocking and PL/PGSQL, what can we employ to secure our data -- I mean tools, packages, scripts, strategies -- to successfully replicate the database and build multi-master combination?

I will appreciate master-slave suggestions as well.

For example, if I put several application servers running Apache/Ruby to achieve higher performance and at the end deploy several database servers, is there any way to build multi-master replication in PostgreSQL?

Right now we use PostgreSQL WAL mechanism to backup data to file system.

Thanks a lot.

Mekk · Accepted Answer · 2009-01-08 19:39:10Z

12

There are a few tools for master-slave (and master-multislave) scenarios, usually trigger-based. Slony-I has already been mentioned (is stable and solid, but a bit difficult to operate). People having problems with Slony-I wrote Londiste (by Skype team) and PyReplica. Bah, and I just spotted Mammoth has been open-sourced

For multimaster there is Bucardo (note: it is not that polished) or commercial offerings - for example by Continuent or CyberTec.

answered Jan 8, 2009 at 19:39

Mekk

1,46710 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Henrik Gustafsson · Accepted Answer · 2010-12-21 19:27:43Z

7

If you haven't already, I'd suggest a look at the High Availability, Load Balancing, and Replication chapter of the PostgreSQL manual. It gives a clear overview of the available techiques and their features.

edited Dec 21, 2010 at 19:27

answered Jan 8, 2009 at 19:45

Henrik Gustafsson

55.1k9 gold badges51 silver badges60 bronze badges

Comments

Renato · Accepted Answer · 2009-11-25 12:23:48Z

2

Hm, Bucardo is really good and stable, in comparison to the others here. It is as polished as can be a Perl-based replication system, and supports master-slave as well as multi-master replication, with interesting conflict resolution concepts.

If you need simple master-slave rep I'd recommend Londiste, but for the multi-master needs, Bucardo is the only acceptable solution IMHO.

answered Nov 25, 2009 at 12:23

Renato

8469 silver badges9 bronze badges

Comments

Kent Fredric · Accepted Answer · 2009-01-08 11:44:09Z

1

I though Postgres-R looked promising, however, its still in development.

It was supposedly stabilised and purported to be a potential for integration with the standard issue, but its yet to come to fruition.

answered Jan 8, 2009 at 11:44

Kent Fredric

57.6k14 gold badges112 silver badges151 bronze badges

Comments

Arndt Lehmann · Accepted Answer · 2009-05-08 09:03:34Z

1

Late answer but there is a new open source software for asynchronous master-master replication of PostgreSQL (also works for MySQL): rubyrep

Focus is on easy setup. Disclosure: I wrote it.

answered May 8, 2009 at 9:03

Arndt Lehmann

2 Comments

malaverdiere Over a year ago

Its not as simple as it looks, however.

fatuhoku Over a year ago

It doesn't seem to be very actively maintained any more, which is a shame.

Patryk Kordylewski · Accepted Answer · 2009-01-08 17:16:16Z

0

You can have a look at slony.

answered Jan 8, 2009 at 17:16

Patryk Kordylewski

1,2691 gold badge7 silver badges11 bronze badges

Comments

Eric · Accepted Answer · 2009-09-14 16:24:57Z

0

PGCluster looks promising - we use it in limited situations without much problems.

http://pgfoundry.org/projects/pgcluster/

answered Sep 14, 2009 at 16:24

Eric

1,7456 gold badges29 silver badges39 bronze badges

Collectives™ on Stack Overflow

PostgreSQL replication strategies

7 Answers 7

Comments

Comments

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related