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:
- Is there a way to have my Master(server1) act as a slave to server3 while still being the Master for the HS(server2)?
- 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.
- 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.
