PostgreSQL replication and large import

importpostgresqlreplication

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?

Best Answer

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.

Related Question