I have a Postgres 9.3 database server with this configuration:
# grep archive_ postgresql.conf archive_mode = on # allows archiving to be done archive_command = 'rsync -a %p postgres@backupserver:9.3/standby/archive/%f' # command to use to archive a logfile segment archive_timeout = 300 # force a logfile segment switch after this
and another server that follows in hot standby mode.
I expect a WAL packet to be sent when it becomes full or 5 minutes (300 seconds).
If I run this query in the hot standby:
SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END
Then I get a mean value of about 1500 seconds, that is about 5 times bigger than the configured 300 seconds.
Why?
In other servers with a lot of traffic the delay is small.
Related questions: – When will PostgreSQL execute archive_command to archive wal files?