pg_switch_wal()

A function for switching to a new WAL file

pg_switch_wal() is a system function which forces PostgreSQL to switch to a new WAL file.

pg_switch_wal() was added in PostgreSQL 8.2 as pg_switch_xlog().

Usage

pg_switch_wal() → pg_lsn

pg_switch_wal() returns the end LSN + 1 of the old WAL file.

However, if there has been no activity which generates WAL since the last WAL file switch, a switch will not be carried out and the start location of the current WAL file will be returned.

pg_switch_wal() can only be executed on a primary server (i.e. not a read-only standby).

Change history

Examples

Basic usage example for pg_switch_wal():

postgres=# SELECT pg_switch_wal();
 pg_switch_wal 
---------------
 0/161F710
(1 row)

If there has been no activity which generated WAL since the last WAL file switch, a new WAL file will not be created:

postgres=# SELECT pg_walfile_name(pg_switch_wal()), pg_walfile_name(pg_switch_wal());
     pg_walfile_name      |     pg_walfile_name      
--------------------------+--------------------------
 000000010000000200000086 | 000000010000000200000086
(1 row)

However, in the following example the function foo() results in WAL file activity, so will always cause a WAL file switch:

postgres=# SELECT pg_walfile_name(pg_switch_wal()), foo(), pg_walfile_name(pg_switch_wal());
     pg_walfile_name      | foo |     pg_walfile_name      
--------------------------+-----+--------------------------
 000000010000000200000097 |     | 000000010000000200000098
(1 row)

Attempting to execute pg_switch_wal() on a standby:

postgres=# SELECT pg_switch_wal();
ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.

Categories

System function, WAL

See also

pg_walfile_name(), pg_walfile_name_offset()