Postgresql – When will PostgreSQL execute archive_command to archive wal files

postgresql

I use the archive_command below to archive WAL files. Suppose I have touched the file archive_active already. When will PostgreSQL execute the supplied archive_command to archive WAL files?

I know we can use the function pg_switch_xlog() to archive the WAL on demand, are there other trigger conditions?

PostgreSQL version: 9.3.0.

–postgresql.conf 

archive_command = 'test ! -f /archive/pg93/archive_active || cp %p /archive/pg93/%f'
wal_level = hot_standby
archive_mode = on

–archive directory

[pg93@redhatB pg93]$ pwd
/archive/pg93

[pg93@redhatB pg93]$ ll
total 32M
-rw-------. 1 pg93 pg93 16M Oct 16 11:05 00000001000000000000007A
-rw-------. 1 pg93 pg93 16M Oct 16 11:07 00000001000000000000007B
-rw-rw-r--. 1 pg93 pg93   0 Oct 16 11:05 archive_active

Best Answer

The archive command is executed every time it switches the archive log to a new one. Which as you say can be triggered manually by calling the pg_switch_xlog() function.

Other than that, an archive log needs to be changed to a new one when it is full, which by default is when it reaches 16MB, but can be changed at compile time.

You can also specify a timeout value using the parameter archive_timeout which will execute the command after the set amount of seconds, which is useful for databases that have low activity.

Update:

Version 10 introduced a change that renames everything referencing "xlog" to "wal", so pg_switch_xlog() is now called pg_switch_wal()

Related Question