5

I have two postgres database servers in AWS, A and B. These databases have different tables. Both databases also have their own read replica.

My goal is a simple solution for doing cross database joins from A replica to B replica. Querying from master is not an option, as this feature is meant for the analyst on my team, not for an application.

I set up postgres_fdw on A's master following the instructions by doing CREATE SERVER, CREATE USER MAPPING, and CREATE FOREIGN TABLEs, and I was able to easily query from A master to B replica. However, these server and foreign table definitions did NOT replicate to A replica. I am unable to run these create commands on the replica as it is read-only.

Is it possible to use postgres_fdw from A replica to B replica? If not, is there another solution for this problem?

Improve this question
3
  • If you set up the foreign server etc. on A master, they will replicate to A replica. You are doing something wrong. Without seeing the error message you got, it is hard to say what that is.
    – jjanes
    Apr 25, 2017 at 16:52
  • Okay....so the foreign tables are on the replica under the user whose mapping I defined. But when I try to SELECT on them, the query hangs and times out eventually with [08001] ERROR: could not connect to server "api".
    – maniciam
    Apr 25, 2017 at 20:28
  • Maybe a firewall issue? Can you run psql on the same machine as A replica is running, and connect to B replica manually using the same hostname, port, username, and password given in the server and user mapping?
    – jjanes
    Apr 25, 2017 at 21:37

2 Answers 2

Reset to default
1

You can create replicas using logical decoding. Check pglogical https://github.com/2ndQuadrant/pglogical

When creating replica using logical decoding it's not read-only.Might help in your case.

Improve this answer
0

The general approach here would be creating extension, server, user mapping and foreign tables on master.

Then query all those on any slave...

Improve this answer
2
  • In my question I stated: "However, these server and foreign table definitions did NOT replicate to A replica"
    – maniciam
    Apr 25, 2017 at 15:29
  • Oh. I thought you have streaming replication
    – Vao Tsun
    Apr 25, 2017 at 15:31

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.