Search code examples
postgresqlcluster-computingreplicationmaster-slave

Is it possible to provide write permissions for slave/standby servers in postgresql in a cluster?

I have two servers on the same network which I have live streaming with a master-slave structure. I'm not using any tools like Slony or Bucardo. It's a simple replication process that can be accomplished by changing some parameters in the .conf files. Curently it can accept read/write on the master and read-only on the slave. However I want a master-master structure, wherein both the servers can accept read as well as write requests. Is this possible in PostgreSQL 9.3 or above?

Solution

  • Is it possible to provide write permissions for slave/standby servers in postgresql

    No.

    It's a simple replication process that can be accomplished by changing some parameters in the .conf files

    So, PostgreSQL's built-in streaming replication.

    However I want a master-master structure, wherein both the servers can accept read as well as write requests. Is this possible in PostgreSQL 9.3 or above?

    There's an extension for PostgreSQL 9.4 that adds multi-master, called BDR. See http://bdr-project.org/docs/stable/ and http://2ndquadrant.com/BDR . It's not really ready for widespread adoption by new database users though - you should be pretty confident with PostgreSQL and replication if you're going to use it at this point. (Note: I'm one of the developers of BDR). BDR is asynchronous multi-master replication, it's not transparent clustering that you can just point an existing app at and expect it to work without changes. See the manual for details.

    There is also Postgres-XC and its fork Postgres-XL, which add tightly coupled multi-master clustering and parallel dispatch to PostgreSQL. Apps may require fewer changes to run on XC, but it requires fast, reliably, low-latency networking between nodes.

    There are other options like a multi-master variant of Slony-I, the Bucardo project, etc.

    All of these share some common details: it's really, really important to know what you're doing and what the exact limitations, caveats and special cases with the multi-master implementation you have chosen are. If you just go ahead and deploy an app pointing at multiple masters without careful thought and testing you are going to have some sleepless weekends and angry users.

    That's before you even get into disaster recovery planning, failover, backup management, etc.

    If you do not know that you absolutely need multi-master, then do not use it.