In streaming replication, how to synchronize $PGDATA/pg_password_history file both primary and standby?

[hwlee-db]

Thank you for your development of this extension.

I am using this extension with pg 15.6 streaming replication 2 nodes(primary-hot standby). In this case, they can't synchronize pg_password_history.

primary

postgres=# SELECT * FROM pg_password_history ;
 rolename |         password_date         |                          password_hash

----------+-------------------------------+---------------------------------------------
---------------------
 test     | 2024-04-17 15:01:15.906522+09 | 5ed47b6552a9c401c1b07a70ccbc1f7edcbf1464e316
85c0ddbf5cbf730ffa70
(1 row)

postgres=# ALTER ROLE test PASSWORD '123456ab@@';
ALTER ROLE

postgres=# SELECT * FROM pg_password_history ;
 rolename |         password_date         |                          password_hash

----------+-------------------------------+---------------------------------------------
---------------------
 test     | 2024-04-17 15:01:15.906522+09 | 5ed47b6552a9c401c1b07a70ccbc1f7edcbf1464e316
85c0ddbf5cbf730ffa70
 test     | 2024-04-17 15:06:58.034963+09 | fdde864a87458eab181a55ef4fe5c187a3fb18512dc7
da89b15aaf703357ce3a
(2 rows)

standby

postgres=# SELECT * FROM pg_password_history ;
 rolename |         password_date         |                          password_hash

----------+-------------------------------+---------------------------------------------
---------------------
 test     | 2024-04-17 15:01:15.906522+09 | 5ed47b6552a9c401c1b07a70ccbc1f7edcbf1464e316
85c0ddbf5cbf730ffa70
(1 row)

I had executed ALTER ROLE test PASSWORD, but it didn't synchronized. Because history be written into $PGDATA/pg_password_history on primary only.

So I copied $PGDATA/pg_password_history to standby server, but it couldn't be loaded in standby server.

How can I synchronize both of them?

[glushakov]

We also encountered this problem, with patroni and auto failover features

[darold]

Yes, I'm thinking about some possible solutions to enable that. I can not tell you when the feature will be available, I'm missing time for the moment.