PostgreSQL Master server act as a slave for 2nd DB on new server

master-slave-replicationpostgresql

I have a strange situation I'm are trying to resolve. I've googled around but I haven't found a clear answer so I thought I'd pose it to the good people at DBA on StackEx to get a second opinion.

We have a Master server(server1) that has a Hot Standby attached(server2). This runs great (using SR).

We've ended up acquiring a new PostgreSQL server(server3) that has it's own DB and software that regularly adds INSERTs into a table. We need to get that table's INSERTs over to our Master(server1). I am trying to figure out how best to do this using PostgreSQL only. All servers are in the same warehouse now and are on the same LAN(connection reliability/latency is excellent).

My questions are:

  1. Is there a way to have my Master(server1) act as a slave to server3 while still being the Master for the HS(server2)?
  2. If that is possible, will the INSERTs that are replicated to the Master(server1) from server3 likely bring with them a serious performance hit? I would be using asynchronous replication.
  3. I'm the only one here with any DB experience so I have no one to bounce ideas off of… Am I missing anything obvious? Is there a better way to get the table receiving the INSERTs on server3 over to server1? I can't decommission server3 for various reasons.

As a note: I have looked at/considered the following other options:

  • SQLWorkBench – I'd like to avoid adding JRE to the servers if possible.

  • Batch SELECT/COPY to an output file on server3 that is dropped onto a file share owned by Master(server1) that it then picks up and works on.

  • Because of the steady stream of INSERTs pg_dump is not a solution.

Best Answer

After a lot more digging I now know a couple of things:

  1. A PostgreSQL server can't be a master of one DB and a slave for a second DB. In order to have a master DB and a slave DB on the same physical hardware with PostgreSQL I need to run two separate PostgreSQL services/clusters.
  2. Updates are actually more expensive than Inserts because Updates have to seek out the row first. Also always try to append your Insert(or use a clustered index if necessary).

Thanks

Related Question