When will PostgreSQL execute archive_command to archive wal files?

Question

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

• Milen A. Radev: What are the values for wal_level and archive_mod?
• francs: @MilenA.Radev, I edited the question, and add the wal_level and archive_mod .

Answer 1

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()

• Neil McGuigan: pedantically, archive_timeout will only trigger archive_command if required (aka there was database activity)
• deFreitas: Ran select pg_switch_xlog() and got HINT: No function matches the given name and argument types. You might need to add explicit type casts.
• deFreitas: Looks lile this function was removed in pg 10
• deFreitas: Ok, it was renamed to pg_switch_wal
• Jimmy Stenke: good point, I'll add a note about it