How to connect pgxpool to the master and replica hosts with possibility of write to the table?

Question

Got host variable equals to the database hosts

host=<masters fqdn>,<replicas fqdn>

But, when the program tries to write something in db, it gets an error:

cannot set transaction read-write mode during recovery (SQLSTATE 0A000)

Looks like pgx tries to write into the replica , instead of master

I found out, this problem can be fixed by changing connection opts:

cfg.Config.RuntimeParams["target_session_attrs"] = "read-write"

But this doesn't work

Please help me solve this problem

Answer 1

Your server is in recovery mode and you're attempting to perform a write operation on a replica which is typically read-only during recovery.

You need to set hot_standby = on in postgresql.conf on replicas and confirm max_wal_senders and wal_level = replica on the primary. In your primary server's postgresql.conf, the synchronous_standby_names parameter is probably set to an empty string, which should be changed to your standby name. And, remove any recovery.conf or recovery.signal file there is - these files are used to control the recovery process and might contain settings that affect read-only vs. read-write behavior.

Restart the server on both, the primary and the replica for the settings to take effect.