pg_switch_wal() in PostgreSQL: Small Command, Big Recovery Impact

A PostgreSQL database can look completely healthy until the day you try to restore it.

Backups are available. WAL archiving is enabled. The archive directory has files. Monitoring shows green. Then, during PITR, PostgreSQL throws something like:

could not locate required checkpoint record


or recovery stops before the target time because the WAL needed for that moment was never archived.

This is where many DBAs learn an uncomfortable lesson: the WAL segment that contains the changes you need may still be sitting inside pg_wal/ as the active segment. It has not filled up yet, so the archiver has not copied it.

On a busy system, WAL segments rotate quickly. On a quiet database, the same 16 MB segment may remain open for hours or even days.



pg_switch_wal() is the function that forces PostgreSQL to close the current WAL segment and move to the next one.
It sounds simple. In recovery work, backup validation, and archive troubleshooting, it is extremely useful.

What pg_switch_wal() Actually Does

PostgreSQL writes every change to the Write-Ahead Log before the actual data file change is considered safe. WAL is split into segment files, normally 16 MB each, stored under pg_wal/.

A typical WAL file looks like this:
000000010000000000000019


That filename contains: Timeline ID Log ID & Segment number
Normally, PostgreSQL closes a WAL segment only when it becomes full.

 When you execute:

SELECT pg_switch_wal();

PostgreSQL forces the active WAL segment to finish early and starts writing into a new segment.
Operationally, this means:

• Current WAL segment is closed
• Remaining space is padded
• Next WAL segment becomes active
• Closed segment becomes ready for archiving
• Function returns the LSN where the switch happened

The return value may look like this: 0/2B7A9C0

That is the LSN where the WAL switch happened.

0 = upper 32 bits of the byte position
2B7A9C0 = lower 32 bits in hexadecimal

Converting: 0x2B7A9C0 = 45,590,976 bytes, approximately 43.5 MB into the WAL stream.

With a 16 MB WAL segment size, this position falls inside the second WAL segment of that timeline range. To identify the WAL file name:

SELECT pg_walfile_name('0/2B7A9C0');

That tells you which WAL segment contains that LSN.

Small detail, but useful during recovery: the returned LSN is the switch location. The file containing that LSN is the segment that was active at the time of the switch and should now become available for archiving.

Why DBAs Use It Before Recovery Testing

The most practical use of pg_switch_wal() is not academic. It is recovery safety.

Imagine this situation:
10:00 - Base backup completed
10:15 - Application made important changes
10:20 - You want PITR to 10:15


But the database is quiet. The WAL segment containing the 10:15 changes is still open. It has not reached 16 MB. Since it is not complete, the archiver has not copied it yet.

Now your archive location looks fine, but recovery does not have the WAL required to reach 10:15.

Before running PITR tests or relying on a precise recovery target, force a WAL switch:
SELECT pg_switch_wal();


Then check whether the segment arrived in the archive destination:

SELECT
    archived_count,
    last_archived_wal,
    last_archived_time,
    failed_count,
    last_failed_wal,
    last_failed_time
FROM pg_stat_archiver;

If last_archived_wal does not move, do not trust your recovery setup yet.

A lot of backup checks only validate that a base backup exists. That is not enough. PostgreSQL recovery needs both the base backup and the required WAL chain.

Missing one WAL file can make an otherwise good backup useless.

Using It to Validate Archive Command

pg_switch_wal() is also one of the fastest ways to test whether archiving is actually working.

First check archive settings:
    SHOW archive_mode;
    SHOW archive_command;


Then force a WAL switch:
    SELECT pg_switch_wal();


Now check archiver status:

SELECT
    archived_count,
    last_archived_wal,
    last_archived_time,
    failed_count,
    last_failed_wal,
    last_failed_time
FROM pg_stat_archiver;

On the OS side, check the archive destination:

ls -ltr /app/postgres/arch/

If the archive command is correct, a new WAL file should appear shortly.

If not, check PostgreSQL logs. Most archive failures are boring but painful:

• Permission denied
• Destination full
• Wrong path
• NFS stale mount
• archive_command returns non-zero exit code
• File already exists

A bad archive command can sit unnoticed for a long time if nobody monitors failed_count.

This is a common DBA blind spot. People monitor database availability, replication lag, and CPU. But archiver failures are often discovered only during restore.

That is too late.

The No-Op Behavior That Confuses People

There is one nuance DBAs should remember. pg_switch_wal() may do nothing if no WAL has been generated since the last switch.

So if you run this twice:
    SELECT pg_switch_wal();
    SELECT pg_switch_wal();


the second call may return the same or nearly same LSN. That does not mean PostgreSQL is broken. It means there was nothing new to switch. To force WAL activity, generate some WAL first.

For example:

CREATE TABLE wal_test_tmp(id int);
DROP TABLE wal_test_tmp;

SELECT pg_switch_wal();

Or, depending on the test:

CHECKPOINT; SELECT pg_switch_wal();

Be careful with unnecessary forced switches on busy systems. Every switch creates a new WAL segment for archiving. If someone puts this into a frequent cron job without thinking, archive volume can increase, storage can fill faster, and backup systems may process more WAL files than needed.

Useful command. Bad habit if abused.

Production Failure Scenarios

1. PITR Fails Even Though Backup Exists

Symptoms:

Recovery starts
WAL restore works for some files
Recovery stops before target time
Required checkpoint record not found

Typical cause: The WAL segment containing the target time was never archived.

Fix: SELECT pg_switch_wal();

Then verify:

SELECT last_archived_wal, last_archived_time
FROM pg_stat_archiver;

Operational lesson: never validate PostgreSQL recovery using only the presence of base backups.

---

2. Archive Destination Fills Up

Symptoms:

WAL files accumulating in pg_wal
archive_command failing
Database disk usage increasing

Check: SELECT failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver;

OS check: 

df -h

du -sh /home/postgres/arch/

If archiving is broken long enough, pg_wal can grow and eventually threaten database availability.

---

3. Standby or Backup Lag Caused by WAL Handling

In streaming replication or backup-heavy systems, WAL is the lifeline. If archiving is delayed, replicas or restore jobs may fall behind or fail to fetch required segments.

SELECT
    pg_current_wal_lsn(),
    pg_walfile_name(pg_current_wal_lsn());

For replication:

SELECT
    application_name,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    sync_state
FROM pg_stat_replication;

pg_switch_wal() will not fix a bad replica by itself. But it helps validate whether WAL generation and archiving are behaving as expected.

DBA Insights

Do not treat pg_switch_wal() as a magic recovery command. It does one thing: it closes the current WAL segment and makes it eligible for archiving.

The real DBA work is around validation.

Check whether archiving is enabled:

SHOW archive_mode;

Check whether the command is sane:

SHOW archive_command;

Check whether archiving is progressing:

SELECT archived_count, last_archived_wal, last_archived_time FROM pg_stat_archiver;

Check whether failures are increasing:

SELECT failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver;

And always check from the OS side:

ls -ltr /home/postgres/arch/

df -h

Common mistakes I have seen:

Assuming base backup success means PITR is safe
Not monitoring pg_stat_archiver
Forgetting quiet databases may not archive WAL quickly
Running pg_switch_wal too frequently without storage planning
Not testing restore until an actual incident
Ignoring archive_command failures because the database is still online

The most dangerous backup system is the one that has never been restored.

FAQs

Does pg_switch_wal() force WAL archiving?

Not directly. It closes the current WAL segment. Once the segment is complete, the archiver can pick it up if archive_mode is enabled and archive_command is working.

Why did pg_switch_wal() return the same LSN twice?

Most likely no WAL was generated between the two calls. Generate a small change, then run it again.

Is it safe to run in production?

Yes, but do not abuse it. Occasional use for backup, PITR validation, or archive testing is normal. Frequent forced switches can increase archive file count and storage pressure.

How do I know the switched WAL was archived?

SELECT last_archived_wal, last_archived_time FROM pg_stat_archiver;
Then confirm at the archive destination: ls -ltr /home/postgres/arch/

Can this fix broken PITR?

It can help if the required WAL is still in the active segment and has not yet been archived. It will not help if the WAL was already lost, deleted, or never generated.

Conclusion

pg_switch_wal() is a small PostgreSQL function, but it sits right in the middle of backup confidence, WAL archiving, and PITR reliability.

It is useful before restore testing, after important changes, and while validating archive behavior. But the command itself is only part of the story. The real discipline is checking whether the WAL segment reached the archive location and whether recovery can actually consume it.

A good DBA does not stop at “backup completed.”
A good DBA asks:

• Can I restore it?

• Can I recover to the required time?

• Do I have every WAL file needed?

• Did I test this before the outage?

That mindset saves production systems.