Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have a primary server, with a secondary replicating it.
I want to change a role from LOGIN to NOLOGIN.
I can do this easily on the primary, but the change doesn't replicate to the secondary, and I can't change it on the secondary:
postgres=# ALTER ROLE myrolename WITH NOLOGIN;
ERROR: cannot execute ALTER ROLE in a read-only transaction
Is there an easy solution to this?
It certain will replicate if replication is working. But has to wait for all WAL before it to get replayed, it can't jump the queue. But existing connection from the user won't get severed, only new ones will be blocked.
You could also edit the replica's pg_hba to specifically block that user, which wouldn't depend on the master at all.
This change will certainly replicate to the standby. If it doesn't, that means that replication is broken or lagging. Consult the log on the standby server for more.
