How to have different server config on replication server for postgres_fdw? I have a postgres sharding based on postgres_fdw and postgres partitioning, lets say 3 machine (1 coordinator and 2 Shard machines). What I want to do is have read replica version of same? How should I do it?
What I have tried for now is, I have created read replicas of all the three machines but since server configuration also gets copied to the replica of coordinator server, there is no way for me to redirect queries from replica of coordinator to replica of shard machines? How do i do that?
If you define the foreign server using host and port connection parameters, these data are part of the PostgreSQL metadata and replicated. Streaming replication creates an identical copy of the database.
So you'd have to define the foreign server using connection parameters that refer to a file outside the database proper.
What comes to mind is the service connection parameter. With that, the actual connection parameters are taken from a file pg_service.conf that is outside the database. Then all you have to do is provide a different service file on the standby server.