0

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?

1

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.

0

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.

1
  • Hmmm, I did wonder that, it seemed like it should. This is on my test server (thankfully), so maybe the replication isn't working properly on that one, I'll check. Thanks! – spookypeanut Jun 9 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.