Tag Archives: write-ahead logging

WAL Commit Process

PostgreSQLACID Transactions, archive_command, archive_mode, br8dba, Checkpoint Process, Crash Recovery, Database Durability, Database Internals, Database Recovery, Dirty Pages, Enterprise PostgreSQL, fsync, Log Sequence Number, LSN, Open Source Database, pg_switch_wal, pg_wal, PostgreSQL, PostgreSQL 16, PostgreSQL 17, PostgreSQL administration, PostgreSQL architecture, PostgreSQL checkpoint, PostgreSQL DBA, PostgreSQL Guide, PostgreSQL internals, PostgreSQL Monitoring, PostgreSQL Performance, PostgreSQL performance tuning, PostgreSQL recovery, PostgreSQL training, PostgreSQL Tutorial, PostgreSQL WAL, Shared Buffers, synchronous_commit, Transaction Commit, WAL archiving, WAL Buffer, WAL Commit Process, WAL files, WAL Flush, WAL Lifecycle, WAL Record, WAL Segment, WAL Writer, write-ahead logging

PostgreSQL WAL Commit Process

1. The Modification (In-Memory)

When a user executes a data-modifying query (like INSERT, UPDATE, or DELETE):

The change is made to the table or index data inside the Shared Buffers (RAM). The page in memory is now marked as “dirty.”
Simultaneously, a record of this exact change is constructed and written sequentially into the WAL Buffers (also in RAM).

2. The `COMMIT` Command Issued

When the client sends the COMMIT command, PostgreSQL must guarantee that this change will survive a sudden power outage or crash before it can tell the user “Success.”

3. Flushing to Disk (`XLogFlush`)

To ensure durability without the massive overhead of writing entire data pages to disk immediately, Postgres uses the Write-Ahead Logging protocol:

The internal function XLogFlush() is called.
It identifies the exact position (Log Sequence Number, or LSN) of the commit record in the WAL Buffer.
It issues a synchronous write to flush all WAL buffers up to that LSN out of RAM and into the current 16MB WAL segment file on permanent storage.
An fsync() system call is issued to ensure the OS cache actually commits the data to physical disk platters or flash memory.

4. Acknowledgment to the Client

Once the operating system confirms that the WAL record is safely written to the physical storage, the transaction status is updated to “committed” in the commit log (CLOG), and PostgreSQL sends a success acknowledgment back to the client application.

Crucial Architectural Concepts

Write-Ahead Rule: The core rule of WAL is that changes to data pages must not be written to permanent database files on disk until the log records describing those changes have been flushed to stable storage. If the server crashes, Postgres reads the WAL from the last checkpoint forward and reapplies the changes (“redoes” them).
Asynchronous Commit Alternative: If you set the configuration parameter synchronous_commit = off, Postgres will acknowledge the client’s COMMIT before the WAL buffer is flushed to disk (relying on the WAL Writer background process to flush it within roughly 3 times wal_writer_delay). This massively increases write throughput but introduces a risk of losing up to a split-second of recent transactions if the server suddenly loses power.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you

Rajasekhar Amudala

Email: br8dba@gmail.com
LinkedIn: linkedin.com/in/rajasekhar-amudala

PostgreSQL

Uncategorizedadvanced PostgreSQL, asynchronous replication PostgreSQL, autovacuum tuning, backup all databases postgresql linux, build postgresql from source, change PGDATA, change pgdata postgresql linux, change postgresql wal directory, change wal directory postgresql rhel, compile PostgreSQL, configure streaming replication postgresql, convert async to sync replication postgresql, copy postgresql database to new host, create database PostgreSQL, create tablespace postgresql, create user postgresql, database administration, database performance tuning, DBA Training, disable archive mode postgresql, disable wal archiving postgresql, enable archivelog postgresql, how to create database in postgresql linux, how to install postgresql on rhel, how to use pg_basebackup, how to use pg_dump, install postgresql from source code linux, install PostgreSQL Linux, install postgresql linux rpm, Linux PostgreSQL DBA, logical replication PostgreSQL, manage users postgresql linux, manual failover PostgreSQL, migrate Oracle to PostgreSQL, move pg_wal to different disk, move postgresql data directory, move table to tablespace postgresql, Open Source Database, Oracle DBA to PostgreSQL, oracle rman pitr equivalent postgresql, oracle vs postgresql wal, Patroni PostgreSQL, pg_basebackup, pg_basebackup another server, pg_basebackup postgresql, pg_basebackup recovery.conf postgresql, pg_basebackup restore, pg_basebackup same server, pg_basebackup step by step, pg_basebackup wal archive restore, pg_ctl promote postgresql, pg_ctl start stop postgresql, pg_dump, pg_dump command examples linux, pg_dump pg_restore postgresql, pg_dumpall, pg_dumpall postgresql backup, pg_dumpall psql restore, pg_dumpall restore, pg_hba conf examples, pg_hba.conf, pg_hba.conf configuration guide, pg_restore, pg_restore postgresql example, pg_stat_activity, pg_wal, point in time recovery, Postgres admin guide, Postgres DBA course, Postgres PITR, Postgres query optimization, Postgres real time scenarios, PostgreSQL, PostgreSQL 17, postgresql 17 archive mode, PostgreSQL access control, PostgreSQL administration, postgresql allow remote connection pg_hba, PostgreSQL architecture, PostgreSQL archive mode, postgresql archive_mode off, postgresql archive_mode on, PostgreSQL Authentication, postgresql authentication methods, PostgreSQL automatic failover, PostgreSQL AWS, PostgreSQL Azure, PostgreSQL Backup, postgresql backup all databases, PostgreSQL backup and recovery, postgresql backup restore different host, postgresql backup restore pg_dump, postgresql backup restore same host, postgresql base backup linux, PostgreSQL CentOS, postgresql change data directory, postgresql client authentication, postgresql clone database linux, PostgreSQL cloud, PostgreSQL Cluster Backup, PostgreSQL clustering, postgresql compile from source rhel7, PostgreSQL Configuration, postgresql continuous archiving, postgresql create database command, PostgreSQL data directory, postgresql data directory location linux, postgresql database copy same host linux, PostgreSQL database creation, postgresql datadir change rhel, PostgreSQL DBA, PostgreSQL DBA Scripts, PostgreSQL DBA training, postgresql default tablespace, postgresql disable archivelog linux, PostgreSQL disaster recovery, postgresql enable archive mode, PostgreSQL failover, postgresql failover streaming replication, PostgreSQL for beginners, postgresql full backup pg_dumpall, PostgreSQL GCP, postgresql grant privileges, PostgreSQL HA, PostgreSQL high availability, PostgreSQL Indexing, PostgreSQL installation, postgresql installation linux step by step, PostgreSQL internals, PostgreSQL Linux 7, postgresql linux tutorial, PostgreSQL logical backup, postgresql logical backup restore, postgresql manual failover, postgresql manual installation linux, postgresql master slave replication linux, postgresql migration, PostgreSQL Monitoring, postgresql new database tutorial, PostgreSQL on Linux, PostgreSQL Performance, PostgreSQL performance tuning, postgresql pg_hba.conf, postgresql pg_wal path change, PostgreSQL physical backup, postgresql physical backup pg_basebackup, PostgreSQL PITR, postgresql pitr example linux, postgresql pitr step by step, postgresql point in time recovery, postgresql point in time recovery new server, postgresql promote replica to primary, PostgreSQL recovery, PostgreSQL replication, postgresql replication step by step, postgresql replication tutorial rhel, postgresql restart linux command, PostgreSQL Restore, postgresql restore new host wal archives, postgresql restore on same machine, postgresql restore to specific time, postgresql restore with wal archives, PostgreSQL RHEL, PostgreSQL roles, PostgreSQL Roles and Privileges, PostgreSQL security, postgresql service commands linux, PostgreSQL service management, postgresql setup linux, PostgreSQL source installation, postgresql source installation linux, PostgreSQL SSL setup, postgresql standby server setup, PostgreSQL step by step, PostgreSQL storage management, postgresql streaming replication setup, postgresql switchover manual, postgresql sync replication configuration, postgresql synchronous commit, postgresql synchronous replication, postgresql synchronous_standby_names, PostgreSQL Tablespace, postgresql tablespace linux, postgresql tablespace management, PostgreSQL training, postgresql transaction logs explained, PostgreSQL Troubleshooting, postgresql turn off archiving, PostgreSQL Tutorial, PostgreSQL user management, postgresql user permissions, PostgreSQL users, PostgreSQL WAL, postgresql wal archiving setup, postgresql wal based recovery, PostgreSQL WAL directory, postgresql wal files explained, postgresql wal location linux, postgresql write ahead logging, promote standby postgresql, psql create database, REPMGR, restore postgresql to another server no archive, SQL Tuning, Start PostgreSQL, start stop postgresql linux, Stop PostgreSQL, streaming replication, streaming replication PostgreSQL, synchronous replication PostgreSQL, synchronous replication postgresql setup, systemctl postgresql start stop, vacuum analyze PostgreSQL, WAL archiving, WAL archiving PostgreSQL, wal files oracle dba postgresql, what is wal in postgresql, write-ahead logging

PostgreSQL DBA Step by Step Learning

#	PostgreSQL DBA Topics
1	How to Install PostgreSQL ON Linux?
2	How to Install PostgreSQL on Linux 7 using source code?
3	How to START/STOP PostgreSQL ON Linux?
4	How to Create Database in PostgreSQL?
5	PostgreSQL User Management
6	PostgreSQL pg_hba.conf Guide
7	PostgreSQL Change Data Directory
8	Understanding WAL Files in PostgreSQL – For Oracle DBAs
9	Change PostgreSQL WAL Directory Path (pg_wal)
10	Enable Archive Mode in PostgreSQL 17
11	How to Disable ARCHIVELOG Mode
12	PostgreSQL Tablespace Management
13	PostgreSQL pg_dump and pg_restore Guide
14	PostgreSQL Backup and Restore Using pg_dumpall and psql
15	pg_basebackup – Backup, Restore, and Recovery
16	Backup & Restore PostgreSQL DB Cluster to Another Host (No Archive Mode)
17	Backup & Restore PostgreSQL DB Cluster on Same Host
18	Restore PostgreSQL to New Host using pg_basebackup + WAL Archives
19	PostgreSQL PITR – Point in Time Recovery
20	Configure Streaming Replication in PostgreSQL
21	Manual Failover in PostgreSQL Streaming Replication
22	Convert Asynchronous Replication to Synchronous Replication

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Enable Archive Mode (WAL Archiving)

PostgreSQLarchive_command, enable archive mode, Linux PostgreSQL setup, PostgreSQL, PostgreSQL 17, PostgreSQL Admin, PostgreSQL archive log, PostgreSQL archive_mode, PostgreSQL Backup, PostgreSQL Configuration, PostgreSQL disaster recovery, PostgreSQL PITR, PostgreSQL replication, PostgreSQL Tutorials, WAL files, write-ahead logging

Enable Archive Mode in PostgreSQL 17

0. Importance of Archive Log Files
1. Verify Existing Archive Mode
2. Ensure Archive Directory Exists
3. Edit postgresql.conf
4. Restart PostgreSQL
5. Verify the Configuration
6. Test Archiving Works

0. Importance of Archive Log Files

Archive log files, also known as WAL archives (Write-Ahead Log archives) , play a critical role in PostgreSQL for data protection, recovery, and replication.

A. Point-in-Time Recovery (PITR): Restore the database to a specific point in time, useful for accidental data changes or loss.
B. Continuous Backup: Works with base backups to enable robust disaster recovery.
C. Streaming Replication Support: Helps standby servers catch up if they fall behind.
D. Disaster Recovery: Enables full recovery after hardware or data corruption.
E. Data Audit & Analysis: Allows decoding WAL logs for auditing and compliance.

🛑 Important Note:

If WAL archiving is disabled and a backup is taken, you can only restore to the exact backup time, not to any point after.

1. Verify Existing Archive Mode

[postgres@lxicbpgdsgv01 ~]$ psql
psql (17.6)
Type "help" for help.

postgres=# SHOW archive_mode;
 archive_mode 
--------------
 off <--- it's off
(1 row)

postgres=# SHOW archive_command;
 archive_command 
-----------------
 (disabled) <----
(1 row)

[root@lxicbpgdsgv01 ~]# systemctl status postgresql-17
● postgresql-17.service - PostgreSQL 17 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
     Active: active (running) since Tue 2025-10-07 18:33:05 +08; 2min 26s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 5911 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 5916 (postgres)
      Tasks: 7 (limit: 15700)
     Memory: 21.3M
        CPU: 177ms
     CGroup: /system.slice/postgresql-17.service
             ├─5916 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─5917 "postgres: logger "
             ├─5918 "postgres: checkpointer "
             ├─5919 "postgres: background writer "
             ├─5921 "postgres: walwriter "
             ├─5922 "postgres: autovacuum launcher "
             └─5923 "postgres: logical replication launcher "

Oct 07 18:33:05 lxicbpgdsgv01.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 07 18:33:05 lxicbpgdsgv01.rajasekhar.com postgres[5916]: 2025-10-07 18:33:05.392 +08 [5916] LOG:  redirecting log output to logging collector process
Oct 07 18:33:05 lxicbpgdsgv01.rajasekhar.com postgres[5916]: 2025-10-07 18:33:05.392 +08 [5916] HINT:  Future log output will appear in directory "log".
Oct 07 18:33:05 lxicbpgdsgv01.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv01 ~]#

Currently, there is no active archive process running in the background.

2. Ensure Archive Directory Exists

[root@lxicbpgdsgv01 ~]# mkdir -p /pgArch/pgsql17/arch/
[root@lxicbpgdsgv01 ~]# chown postgres:postgres /pgArch/pgsql17/arch/
[root@lxicbpgdsgv01 ~]# chmod 700 /pgArch/pgsql17/arch/

3. Edit `postgresql.conf`

[postgres@lxicbpgdsgv01 ~]$ vi /pgData/pgsql17/data/postgresql.conf

Update or add the following lines:

archive_mode = on
archive_command = 'cp %p /pgArch/pgsql17/arch/%f'

Explanation:

%p = Full path of WAL file
%f = WAL file name
cp %p /pgArch/pgsql17/arch/%f = Command to copy the WAL file to archive directory

4. Restart PostgreSQL

[root@lxicbpgdsgv01 ~]# systemctl status postgresql-17
● postgresql-17.service - PostgreSQL 17 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
     Active: active (running) since Tue 2025-10-07 23:48:29 +08; 18s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 7868 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 7873 (postgres)
      Tasks: 8 (limit: 15700)
     Memory: 19.0M
        CPU: 96ms
     CGroup: /system.slice/postgresql-17.service
             ├─7873 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─7874 "postgres: logger "
             ├─7875 "postgres: checkpointer "
             ├─7876 "postgres: background writer "
             ├─7878 "postgres: walwriter "
             ├─7879 "postgres: autovacuum launcher "
             ├─7880 "postgres: archiver "  <------
             └─7881 "postgres: logical replication launcher "

Oct 07 23:48:29 lxicbpgdsgv01.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 07 23:48:29 lxicbpgdsgv01.rajasekhar.com postgres[7873]: 2025-10-07 23:48:29.503 +08 [7873] LOG:  redirecting log output to logging collector process
Oct 07 23:48:29 lxicbpgdsgv01.rajasekhar.com postgres[7873]: 2025-10-07 23:48:29.503 +08 [7873] HINT:  Future log output will appear in directory "log".
Oct 07 23:48:29 lxicbpgdsgv01.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv01 ~]#

Make sure the archiver process is running.

5. Verify the Configuration

[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW archive_mode;"
 archive_mode 
--------------
 on <-----------
(1 row)

[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW archive_command;"
        archive_command         
-------------------------------
 cp %p /pgArch/pgsql17/arch/%f  <-----------------
(1 row)

6. Test Archiving Works

[postgres@lxicbpgdsgv01 ~]$ psql -c "SELECT pg_switch_wal();"

Check archive folder for new WAL files:

[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgArch/pgsql17/arch/
total 16M
-rw-------. 1 postgres postgres 16M Oct  7 23:50 000000010000000000000001

[postgres@lxicbpgdsgv01 ~]$ psql -c "SELECT pg_switch_wal();"
[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgArch/pgsql17/arch/
total 32M
-rw-------. 1 postgres postgres 16M Oct  7 23:50 000000010000000000000001
-rw-------. 1 postgres postgres 16M Oct  7 23:51 000000010000000000000002

Archiving is working successfully.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

PSQL

PostgreSQLadvanced PostgreSQL, asynchronous replication PostgreSQL, autovacuum tuning, change PGDATA, compile PostgreSQL, create database PostgreSQL, database administration, database performance tuning, DBA Training, install PostgreSQL Linux, Linux PostgreSQL DBA, logical replication PostgreSQL, manual failover PostgreSQL, migrate Oracle to PostgreSQL, Open Source Database, Oracle DBA to PostgreSQL, Patroni PostgreSQL, pg_basebackup, pg_dump, pg_dumpall, pg_hba.conf, pg_restore, pg_stat_activity, pg_wal, point in time recovery, Postgres admin guide, Postgres DBA course, Postgres PITR, Postgres query optimization, Postgres real time scenarios, PostgreSQL, PostgreSQL 17, PostgreSQL access control, PostgreSQL administration, PostgreSQL architecture, PostgreSQL archive mode, PostgreSQL Authentication, PostgreSQL automatic failover, PostgreSQL AWS, PostgreSQL Azure, PostgreSQL Backup, PostgreSQL backup and recovery, PostgreSQL CentOS, PostgreSQL cloud, PostgreSQL Cluster Backup, PostgreSQL clustering, PostgreSQL Configuration, PostgreSQL data directory, PostgreSQL database creation, PostgreSQL DBA, PostgreSQL disaster recovery, PostgreSQL failover, PostgreSQL for beginners, PostgreSQL GCP, PostgreSQL HA, PostgreSQL high availability, PostgreSQL Indexing, PostgreSQL installation, PostgreSQL internals, PostgreSQL Linux 7, PostgreSQL logical backup, postgresql migration, PostgreSQL Monitoring, PostgreSQL on Linux, PostgreSQL Performance, PostgreSQL performance tuning, PostgreSQL physical backup, PostgreSQL PITR, PostgreSQL recovery, PostgreSQL replication, PostgreSQL Restore, PostgreSQL RHEL, PostgreSQL roles, PostgreSQL Roles and Privileges, PostgreSQL security, PostgreSQL service management, PostgreSQL source installation, PostgreSQL SSL setup, PostgreSQL step by step, PostgreSQL storage management, PostgreSQL Tablespace, PostgreSQL training, PostgreSQL Troubleshooting, PostgreSQL Tutorial, PostgreSQL user management, PostgreSQL users, PostgreSQL WAL, PostgreSQL WAL directory, REPMGR, SQL Tuning, Start PostgreSQL, Stop PostgreSQL, streaming replication, streaming replication PostgreSQL, synchronous replication PostgreSQL, vacuum analyze PostgreSQL, WAL archiving, WAL archiving PostgreSQL, write-ahead logging

PostgreSQL DBA Step by Step Learning

#	PostgreSQL DBA Topics
1	How to Install PostgreSQL ON Linux?
2	How to Install PostgreSQL on Linux 7 using source code?
3	How to START/STOP PostgreSQL ON Linux?
4	How to Create Database in PostgreSQL?
5	PostgreSQL User Management
6	PostgreSQL pg_hba.conf Guide
7	PostgreSQL Change Data Directory
8	Understanding WAL Files in PostgreSQL – For Oracle DBAs
9	Change PostgreSQL WAL Directory Path (pg_wal)
10	Enable Archive Mode in PostgreSQL 17
11	How to Disable ARCHIVELOG Mode
12	PostgreSQL Tablespace Management
13	PostgreSQL pg_dump and pg_restore Guide
14	PostgreSQL Backup and Restore Using pg_dumpall and psql
15	pg_basebackup – Backup, Restore, and Recovery
16	Backup & Restore PostgreSQL DB Cluster to Another Host (No Archive Mode)
17	Backup & Restore PostgreSQL DB Cluster on Same Host
18	Restore PostgreSQL to New Host using pg_basebackup + WAL Archives
19	PostgreSQL PITR – Point in Time Recovery
20	Configure Streaming Replication in PostgreSQL
21	Manual Failover in PostgreSQL Streaming Replication
22	Convert Asynchronous Replication to Synchronous Replication

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

BRIGHT DBA

ORACLE DBA – Tips and Techniques

Tag Archives: write-ahead logging

WAL Commit Process

PostgreSQL WAL Commit Process

1. The Modification (In-Memory)

2. The `COMMIT` Command Issued

3. Flushing to Disk (`XLogFlush`)

4. Acknowledgment to the Client

Crucial Architectural Concepts

PostgreSQL

PostgreSQL DBA Step by Step Learning

Enable Archive Mode (WAL Archiving)

Enable Archive Mode in PostgreSQL 17

Table of Contents

0. Importance of Archive Log Files

1. Verify Existing Archive Mode

2. Ensure Archive Directory Exists

3. Edit `postgresql.conf`

4. Restart PostgreSQL

5. Verify the Configuration

6. Test Archiving Works

PSQL

PostgreSQL DBA Step by Step Learning

1. The Modification (In-Memory)

2. The COMMIT Command Issued

3. Flushing to Disk (XLogFlush)

4. Acknowledgment to the Client

Crucial Architectural Concepts

PostgreSQL DBA Step by Step Learning

Enable Archive Mode in PostgreSQL 17

Table of Contents

0. Importance of Archive Log Files

1. Verify Existing Archive Mode

2. Ensure Archive Directory Exists

3. Edit postgresql.conf

4. Restart PostgreSQL

5. Verify the Configuration

6. Test Archiving Works

PostgreSQL DBA Step by Step Learning

2. The `COMMIT` Command Issued

3. Flushing to Disk (`XLogFlush`)

3. Edit `postgresql.conf`