1

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.

Improve this question
3
  • What OS are you on? What version of PostgreSQL are you using? Are you looking for solutions within the PostgreSQL ecosystem that aren't included in the PostgreSQL core?
    – Kassandry
    Feb 15, 2018 at 5:58
  • Could it be as simple as backup from S3, import to S1 and re-point S3 code to look at S1. ?
    – Gurmokh
    Feb 15, 2018 at 10:05
  • S3 has to keep running for other reasons. I've resorted to writing code that peels out new rows in the table and then connects back to S1 and Inserts them into a new table in S1's DB. Thanks for the suggestions.
    – Ryan
    Feb 20, 2018 at 2:50

1 Answer 1

Reset to default
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.