1

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?

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
1
  • I am investigating cascading replication, seems to be similar to the MySQL Distribution Master - thanks for linking it up!
    – Andrew Smith
    Sep 21, 2012 at 0:28
2

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

Alternatives are listed on the Replication wiki page.

Improve this answer
3
  • This only works with one master, not with multiple masters. Postgres-XC does multi-master replication.
    – Frank Heikens
    Sep 18, 2012 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, 2012 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, 2012 at 0:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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