7

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
Improve this question
2
  • What are the values for wal_level and archive_mod? – Milen A. Radev Oct 16 '13 at 8:17
  • @MilenA.Radev, I edited the question, and add the wal_level and archive_mod . – francs Oct 16 '13 at 8:24
11

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

Improve this answer
6
  • pedantically, archive_timeout will only trigger archive_command if required (aka there was database activity) – Neil McGuigan Jun 27 '15 at 0:05
  • 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 Aug 28 '18 at 2:00
  • Looks lile this function was removed in pg 10 – deFreitas Aug 28 '18 at 2:03
  • Ok, it was renamed to pg_switch_wal – deFreitas Aug 28 '18 at 2:05
  • good point, I'll add a note about it – Jimmy Stenke Aug 28 '18 at 3:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.