PostgreSQL replication and large import

Question

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?

Answer 1

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.

• Andrew Smith: I am investigating cascading replication, seems to be similar to the MySQL Distribution Master - thanks for linking it up!

Answer 2

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

Alternatives are listed on the Replication wiki page.

• Frank Heikens: This only works with one master, not with multiple masters. Postgres-XC does multi-master replication.
• Craig Ringer: @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.
• Andrew Smith: Since it's long distance replication, I have chosen asynchronous replication, hence the streaming as for now looks OK