archive_timeout was added in PostgreSQL 8.2.
Normally a WAL file is archived as soon as it is filled. However, if activity is low, it may be some while before the file is archived. By setting
archive_timeout to a positive value, it is guaranteed that after the specified interval a new WAL file will be created, and the previous one archived, however only if there is any activity which generates WAL.
Note that if improperly used, this can result in a larger number of largely empty WAL files being generated, which will occupy more archive storage than would otherwise be necessary.
To ensure near-real time transmission of WAL to another server, it is recommended to use streaming replication.
The default value for
Demonstration of the effect of setting
archive_timeout to 10s (requires
log_min_messages to be set to
DEBUG1 or higher):
[2021-04-01 10:22:24 UTC] psql postgres postgres LOG: 00000: statement: INSERT INTO foo VALUES(1); [2021-04-01 10:22:29 UTC] DEBUG: 00000: write-ahead log switch forced (archive_timeout=10) [2021-04-01 10:22:29 UTC] DEBUG: 00000: archived write-ahead log file "000000010000000000000008" [2021-04-01 10:22:43 UTC] DEBUG: 00000: autovacuum: processing database "proddb" [2021-04-01 10:23:55 UTC] psql postgres postgres LOG: 00000: statement: INSERT INTO foo VALUES(1); [2021-04-01 10:23:59 UTC] DEBUG: 00000: write-ahead log switch forced (archive_timeout=10) [2021-04-01 10:23:59 UTC] DEBUG: 00000: archived write-ahead log file "000000010000000000000009"
In the above, it can be seen that shortly after an
INSERT into a table, the WAL file is switched and archived within the interval specified by
archive_timeout. However as there is no activity which generates WAL, more than
archive_timeout seconds pass; the next WAL switch only occurs after another
INSERT is executed.
The archived WAL file has the normal on-disk file size:
$ ls -l /var/lib/postgres/wal-archive/000000010000000000000008 -rw------- 1 postgres postgres 16777216 Apr 1 10:22 /var/lib/postgres/wal-archive/000000010000000000000008
but is largely empty:
$ pg_waldump /var/lib/postgres/wal-archive/000000010000000000000008 rmgr: XLOG len (rec/tot): 49/ 8033, tx: 0, lsn: 0/08000028, prev 0/07000148, desc: FPI_FOR_HINT , blkref #0: rel 1663/14973/1249 blk 17 FPW rmgr: Heap len (rec/tot): 59/ 59, tx: 555, lsn: 0/08001F90, prev 0/08000028, desc: INSERT+INIT off 1 flags 0x00, blkref #0: rel 1663/14973/16470 blk 0 rmgr: Transaction len (rec/tot): 34/ 34, tx: 555, lsn: 0/08001FD0, prev 0/08001F90, desc: COMMIT 2021-04-01 10:22:24.426073 UTC rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/08001FF8, prev 0/08001FD0, desc: RUNNING_XACTS nextXid 556 latestCompletedXid 554 oldestRunningXid 555; 1 xacts: 555 rmgr: XLOG len (rec/tot): 24/ 24, tx: 0, lsn: 0/08002048, prev 0/08001FF8, desc: SWITCH $