I have a problem that's driving me insane. I have two PostgreSQL 9.1 installations (yes, I know... very old.) One is configured as a master and the other is a streaming-replication hot-standby that supports read-only queries.
Every now and then, although the standby claims to be streaming transactions if you look with "ps", it seems to stall and the replication delay grows by one second for each second of elapsed time.
If I restart the hot-standby, it consumes WAL files and very quickly catches up. And then a few minutes later, it happens again.
So, what are the possible causes of this behaviour? There are no long transactions on the standby, nor on the master. And how can I find out exactly what query is causing the problem, assuming it's a query?
The relevant settings in the hot-standby postgresql.conf are:
hot_standby = on max_standby_archive_delay = 10s max_standby_streaming_delay = 10s hot_standby_feedback = on
and on the master:
wal_level = hot_standby archive_mode = on archive_command = (a command to send WAL files to the standby) max_wal_senders = 8