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?