I have sharded and replicated Postgre database. I use CLUSTER RUN way of running functions. I define target (master/replica) using CLUSTER param and shard using RUN ON. How can I make PL/Proxy run or not to run function on master, if originally target was replica, but it failed?

CodePudding user response：

In PL/Proxy, you define shards via a libpq connection string. Now if a shard is replicated, you can simply use a connection string like

host=slave.myorg.com,master.myorg.com port=5432,5432 dbname=...

This will try to connect to the first host, and if that fails, it will fall back to the second host. PostgreSQL v14 has the additional connection string parameter target_session_attrs=prefer-standby to preferable connect to the standby server, even if it is not the first in the list.