WAL Switch Manually – PostgreSQL database

PostgreSQL – WAL Switch Manually

PostgreSQL database writes database changes to WAL files. It will automatically start writing to next WAL when the current WAL file fill completely.Additionally, you can do WAL switch manually too.

If the transaction load is small, then it may take a long time to fill the current WAL file. In such scenario, you may need to switch to next WAL file. You can use pg_switch_wal() function to force PostgreSQL WAL writer to switch to new WAL file. When this function completes, it output will be next LSN i.e. end LSN of previous WAL + 1

Example –

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

pg_switch_wal() will not always switch WAL writer to the new WAL file. In case there is not any activity in the database, it will do nothing.

Example –

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

Finally, if you execute pg_switch_wal() on standby server, it will remind you that where you are i.e. it is not the correct environment to execute pg_switch_wal()

Example –

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

If you are preparing for PostgreSQL database interview , Please check useful questions here.

  If you want to crack an interview for the PostgreSQL DBA/Database Engineer role, a demo interview will fortify your chances. Thus, Book an interview preparation session.

• 
• 
• 
•