archive_timeout

A configuration parameter determining the maximum interval before a WAL file is archived

archive_timeout is a configuration parameter determining the maximum length of time which can elapse before a WAL file is archived, regardless of whether or not it was filled.

archive_timeout was added in PostgreSQL 8.2.

Usage

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.

Default value

The default value for archive_timeout is: 0 (disabled).

Change history

Examples

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
$

Categories

GUC configuration item, Replication

See also

archive_command