Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am tasked with making large PostgreSQL imports to master instance, and then replicate it across the nodes as read-only data.

Still, the CMS system has to be able to write data and replicate across all of them.

I can use the latest version of PostgreSQL server. There are just few tables in single database.

I havent been working with PostgreSQL for 5 years, so I was wondering if this is doable the same way as with MySQL, that I have the DM and so on?

The thing is, that I need to run it in two data centres, so ideally it would be close to:

  • 2 master PostgreSQL instances, which are doing writes from CMS and imports
  • 10 slave PostgreSQL instances doing only reads.

Is there any preferred approach in doing this for PostgreSQL?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

PostgreSQL out of the box replication has one master and can have multiple (cascading, as of version 9.2) slaves. If you need multi master replication, 2 masters in your case, you need Postgres-XC.

My last experience with MySQL replication was a big fail, it couldn't handle the load and the slaves always got way behind the master during the day. During the night they caught up, but that was pretty useless. It just didn't work for our workload.

share|improve this answer
    
I am investigating cascading replication, seems to be similar to the MySQL Distribution Master - thanks for linking it up! –  Andrew Smith Sep 21 '12 at 0:28
add comment

This sounds like an ideal candidate for streaming replication with hot standby.

Alternatives are listed on the Replication wiki page.

share|improve this answer
    
This only works with one master, not with multiple masters. Postgres-XC does multi-master replication. –  Frank Heikens Sep 18 '12 at 6:38
    
@FrankHeikens True. I didn't find it clear from Andrew's post whether the two masters were supposed to be an a multi-master config sharing 10 slaves, or whether there were two independent masters each with some subset of the slaves under their control. If it's the latter, SR+HS would still potentially work if something like Bucardo is sufficient to keep the two masters in sync; depends a lot on the application. –  Craig Ringer Sep 18 '12 at 6:51
    
Since it's long distance replication, I have chosen asynchronous replication, hence the streaming as for now looks OK –  Andrew Smith Sep 21 '12 at 0:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.