Change postgres user password in hot standby read only mode

Question

I want to change the password of the postgres user on my PostgreSQL Slave Server which is in read-only mode because of an active replication. SELECT pg_is_in_recovery(); returns true and because of this I can't change the password - so far so good.

How can I temporarily pause the replication (and read-only mode) to set a new password for the postgres user? SELECT pg_xlog_replay_pause(); and didn't work.

Or should I reset the postgres password on the master server and then I have identically passwords on the master & slave?

I don't think that's possible. You need to change the password on the master — , Sep 06 '17 at 08:28
Changing the DB user password on master will replicate on slave as well. — Lohit Gupta, Sep 06 '17 at 08:37

score 1 · Accepted Answer · answered Sep 06 '17 at 08:59

https://www.postgresql.org/docs/current/static/hot-standby.html

When the hot_standby parameter is set to true on a standby server, it will begin accepting connections once the recovery has brought the system to a consistent state. All such connections are strictly read-only; not even temporary tables may be written.

and so forth the list of what you can do on the slave. You can NOT ALTER USER on slave - do it on master and it will be replicated to slave. What you can try to do to have different passwords though is:

change password on master - it will replicate to slave so you have same passwords
pg_xlog_replay_pause on slave to stop replaying form master
change password back on master

this way you will have different passwords for a while (until you resume replay)

Change postgres user password in hot standby read only mode

1 Answers1