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.

The Outage Usually Begins Before the Database Fails

A database incident rarely begins when the monitoring system raises an alert. It usually begins weeks earlier - with an unreviewed execution plan, an ignored capacity trend, an oversized privilege, or a recovery procedure that nobody has tested.

By the time the DBA receives the call, the database is often exposing an operational weakness that was already present. The immediate symptom may be exhausted storage, a failed deployment, excessive I/O, or a missing object, but the underlying cause is frequently a gap in engineering discipline.

I have seen expensive Oracle platforms fail because nobody noticed that the Fast Recovery Area was growing rapidly. I have also seen relatively modest PostgreSQL environments remain stable for years because the team consistently reviewed changes, monitored capacity, controlled access, and tested recovery.

RESETLOGS and Backups: Debunking a Common Oracle Myth

 If you have ever recovered an Oracle database and opened it with RESETLOGS, chances are someone whispered or shouted—the dreaded question: “Are all my old backups now useless?” Lets set the record straight: in modern Oracle releases, your old backups are still very much usable, and panicking is optional.

In this post, I wil walk you through why this myth exists, how Oracle’s handling of RESETLOGS has evolved over the years, and what you should do to ensure your recovery strategy remains solid. We will cover the mechanics of RESETLOGS, explain the differences between pre-10g version and modern behaviour, and share some practical tips I have learned from managing production environments with terabytes of data. By the end, you you will understand why you can sleep a little easier after recovery and make smarter backup decisions.

DB Time vs CPU: The Metric Most DBAs Ignore

 If you have worked in production environments long enough, you have probably seen this situation before. An alert fires in the middle of your daily shift appears.. as "CPU is at 90%. The database must be overloaded." 

Infrastructure teams immediately start discussing scaling CPU, adding cores, or moving the database to a bigger server. But experienced DBAs know something important: High CPU utilization does not automatically mean the database is the bottleneck.

How Oracle RAC Handles a Node Failure: Quick Insights About Interview Discussion

 In high‑availability database environments, About Interview discussion often centers on how systems react when things go wrong .,  especially in mission‑critical deployments like Oracle RAC (Real Application Clusters). One of the most common interview questions DBAs face is: In a 3‑node RAC, if one node goes down, how does instance recovery occur?

 Understanding this not only helps you ace interviews but also equips you with real‑world insights into RAC's fault‑tolerance mechanics.

pg_gather: PostgreSQL Snapshot Tool Every DBA Should Use

 Assume Its 2 AM. Alerts are firing. CPU is maxed out, connections are piling up, and the application team is already asking for an ETA.

You SSH into the server, open psql, and start your usual routine - check pg_stat_activity, look at locks, scan logs, maybe run a few custom queries you’ve built over the years. Fifteen minutes in, you still don’t have the full picture.

This is exactly where things break down in production - - not because PostgreSQL lacks visibility, but because the data is scattered.

That is where pg_gather changes the game.

Oracle PDB Point-in-Time Recovery Without Downtime of other PDBs

 Most Oracle outages do not begin with hardware failure.

They start with a bad deployment, an accidental delete statement, a broken batch job, or a developer connecting to the wrong pluggable database at 2 AM. In a large multitenant environment, that usually means one application becomes corrupted while dozens of other applications inside the same CDB continue running normally.

Years ago, recovering from that kind of incident often meant painful decisions. Either accept application-level data loss or restore the entire database and impact every tenant sharing the environment. Neither option was ideal for production systems running critical workloads.

PostgreSQL vs Oracle: Choosing the Right Database for Your Architecture

  Selecting a database today isn’t just about technology - it's a strategic business decision. In production environments, the choice between PostgreSQL and Oracle Database affects scalability, reliability, compliance, and cost for years. As a DBA, architect, or infrastructure engineer, understanding the trade-offs between these systems can save significant headaches during implementation and future growth.

PostgreSQL Checkpoint Tuning for Stable Performance

   If you have been running PostgreSQL in production for a while, you have probably seen this pattern. Everything looks fine on the surface, queries are tuned, indexes are in place, and yet the system slows down at regular intervals. No obvious reason. No runaway query. Just sudden latency.

In many of these cases, the real issue is not in the foreground workload but in the background engine, specifically checkpoints.

PostgreSQL HugePages Explained for DBAs

   If you have spent years tuning Oracle Database, HugePages is probably second nature to you. You would not even think about running a large SGA without it.

Now, when moving into PostgreSQL, many DBAs assume memory works differently or that HugePages are optional. Technically, they are optional. Practically, ignoring them in a serious production system is a mistake I have seen more than once.

PostgreSQL relies heavily on shared memory, especially for its buffer cache. As systems scale and memory grows into tens or hundreds of GB, the way Linux manages memory pages starts to matter a lot. That is where HugePages step in.

In this article, I will walk through how HugePages behave in PostgreSQL, how they differ from Oracle, and what actually matters when you enable them in real production environments. More importantly, I will share the kind of operational lessons you only learn after seeing systems misbehave at 2 AM.

Oracle 26ai Read-Only Users and Sessions

One very common production request sounds simple:

"Can we give this user access, but make sure they cannot change anything?"

Before Oracle AI Database 26ai, DBAs usually handled this by creating a separate reporting user, granting only SELECT, removing DML privileges, or depending on carefully designed roles. That approach works when access is cleanly designed from the beginning. But in real production systems, users often collect privileges over time. Support users get emergency grants, application users may have broader access than expected, and batch accounts sometimes have privileges that nobody wants to touch during an incident.

PostgreSQL Performance Tuning That Survives Production

    A PostgreSQL performance issue rarely starts with one bad setting.

In production, it usually looks like this: the application team says the database is slow, CPU is not always high, storage graphs look confusing, and nobody changed anything “major”. Then we check deeper and find long transactions, dead tuples, stale statistics, unused indexes, chatty application queries, or checkpoint pressure.

PostgreSQL VACUUM: Bloat, Autovacuum and Real Fixes

    A PostgreSQL table can grow quietly for weeks before anyone notices. The application team says they already deleted old data. Storage still looks high. Queries are touching more blocks than expected. Autovacuum is running, but the table does not seem to become smaller. This is where many DBAs first realize that DELETE in PostgreSQL is not the same as physically removing rows from the table file.

PostgreSQL uses MVCC, so old row versions remain inside the table until VACUUM can clean them. This is normal behavior, not a bug. The problem starts when dead tuples grow faster than VACUUM can remove them, or when long-running transactions prevent cleanup. Then you get table bloat, index bloat, stale statistics, poor plans, unnecessary I/O, and sometimes transaction ID wraparound pressure.

My Oracle ACE Journey: From Practical Sharing to Oracle ACE Pro

  Some journeys do not begin with a plan. They begin with curiosity, consistency, and a simple intention to share what we learn.

My Oracle ACE journey is one such journey.

Recognized Among FeedSpot’s Top 70 Database Blogs to Follow in 2026

I am happy to share that Learn DBA : A Life Long Learning Experience has been selected by FeedSpot as one of the Top 70 Database Blogs to Follow in 2026.

RMAN Restartable Backups Explained for Production DBAs

Most DBAs have seen this at least once. You wake up, check the overnight backup report, and RMAN failed halfway through a 14 TB database backup because the backup filesystem filled up, a network mount disconnected, or one RAC node crashed during the run.

ORA-01017 in RAC 12c and above ? Stepwise Permission Fix & Cause identification

  As an Oracle DBA, few things are more frustrating than a sudden loss of remote connectivity right after a routine SYS password reset. You type in the credentials, and bam -- ORA-01017 greets you, even though your local connections work fine. In production RAC environments, this isn’t just about a mistyped password.

Recently, I faced a tricky scenario in an Oracle 19c RAC setup with proper role separation between the grid and oracle OS users. What seemed like a simple password mismatch quickly unraveled into a multi-layered “permission deadlock,” involving rogue listeners, contaminated IPC sockets, and GPnP directory access issues. It took a careful, stepwise approach to restore connectivity across all nodes without compromising the cluster.