Tag Archives: PostgreSQL performance tuning

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

How SELECT, INSERT, UPDATE, and DELETE Work Internally in PostgreSQL

PostgreSQLHow DELETE works internally in PostgreSQL, How INSERT works internally in PostgreSQL, How MVCC works in PostgreSQL internally, How PostgreSQL processes DELETE query internally, How PostgreSQL processes INSERT query internally, How PostgreSQL processes SELECT query internally, How PostgreSQL processes UPDATE query internally, How PostgreSQL stores data internally, How SELECT works internally in PostgreSQL, How UPDATE works internally in PostgreSQL, Learn PostgreSQL Internals, PostgreSQL architecture, PostgreSQL architecture deep dive, PostgreSQL backend process architecture, PostgreSQL database internals for DBAs, PostgreSQL DBA interview questions internals, PostgreSQL dead tuples explained, PostgreSQL DELETE internal architecture, PostgreSQL DELETE query execution flow, PostgreSQL DML Internals, PostgreSQL DML internals explained, PostgreSQL DML operations internal flow, PostgreSQL execution engine internals, PostgreSQL execution plan internals, PostgreSQL free space map FSM explained, PostgreSQL Heap File, PostgreSQL heap file internals explained, PostgreSQL heap page internals, PostgreSQL index file internals explained, PostgreSQL Index Internals, PostgreSQL INSERT internal architecture, PostgreSQL INSERT query execution flow, PostgreSQL internal working architecture, PostgreSQL internals, PostgreSQL internals tutorial for beginners, PostgreSQL MVCC, PostgreSQL MVCC internals explained, PostgreSQL MVCC row versioning internals, PostgreSQL MVCC with INSERT UPDATE DELETE, PostgreSQL page storage internals, PostgreSQL parser analyzer planner executor explained, PostgreSQL performance tuning, PostgreSQL postmaster process explained, PostgreSQL query execution flow chart, PostgreSQL Query Execution Plan, PostgreSQL Query Processing, PostgreSQL row versioning explained, PostgreSQL SELECT internal architecture, PostgreSQL SELECT query execution flow, PostgreSQL SELECT vs INSERT vs UPDATE vs DELETE internals, PostgreSQL Shared Buffers, PostgreSQL shared buffers explained, PostgreSQL Storage Internals, PostgreSQL Transaction Internals, PostgreSQL transaction internals explained, PostgreSQL tuple internals explained, PostgreSQL UPDATE internal architecture, PostgreSQL UPDATE query execution flow, PostgreSQL VACUUM internals explained, PostgreSQL Vacuum Process, PostgreSQL visibility map VM explained, PostgreSQL WAL, PostgreSQL WAL buffer and commit process, PostgreSQL WAL internals explained, PostgreSQL xmin xmax explained, Why DELETE creates dead tuples in PostgreSQL, Why UPDATE creates dead tuples in PostgreSQL

How SELECT, INSERT, UPDATE, and DELETE work internally in PostgreSQL?

PostgreSQL uses MVCC (Multi-Version Concurrency Control), which means every modification creates a new version of the row instead of overwriting the old one. This is the core concept behind all DML operations.

1. Common Foundation (Applies to All Operations)

Heap: Data is stored in heap files (tables). Each row is a tuple.
Tuple Header: Every row contains:
- xmin: Transaction ID that created the row
- xmax: Transaction ID that deleted/updated the row (null if active)
- Other visibility information
WAL (Write-Ahead Log): All changes are first written to WAL for durability before being applied to data files.
Shared Buffers: Data pages are read into memory before being modified.
Visibility Rules: A row is visible to a transaction based on its snapshot (which transactions it can see).

2. How SELECT Works

Visibility Check: For each row, PostgreSQL checks:

- The row’s xmin must be committed and visible to the current transaction.
- The row’s xmax must be either null or not visible to the current transaction.

3. How INSERT Works

Transaction Starts: A new transaction ID (xid) is assigned.
Buffer Allocation: PostgreSQL finds a free slot in a heap page (or allocates a new page).
Tuple Creation: A new row is created with:
- xmin = current transaction ID
- xmax = null
WAL Logging: The change is written to the WAL buffer first.
Page Modification: The new tuple is inserted into the shared buffer.
Commit: On commit, the WAL is flushed to disk (fsync), making the change durable.

4. How UPDATE Works

UPDATE in PostgreSQL does not modify the existing row. It creates a new version of the row.

Find the row using the same visibility rules as SELECT.
Mark old row as dead: Set xmax = current transaction ID on the old tuple.
Insert new row: Create a completely new tuple with:
- xmin = current transaction ID
- xmax = null
- Updated column values
Update indexes (if needed): New index entries point to the new tuple.
WAL Logging: Both the old row’s xmax change and the new tuple are logged in WAL.
Commit: Changes become visible to other transactions after commit.

Important: The old row version remains in the table until VACUUM cleans it up.

5. How DELETE Works

DELETE also uses MVCC — it doesn’t remove the row immediately.

Locate the row using visibility rules.
Mark as deleted: Set xmax = current transaction ID on the tuple.
WAL Logging: The change is logged.
Commit: The row becomes invisible to new transactions.
Physical Removal: The row is not physically deleted from disk until VACUUM runs.

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

PostgreSQL 17 DBA Free Training Roadmap

PostgreSQLdatabase administration, Database Backup and Recovery, Database Course, Database Security, DBA Training, DevOps, Failover, Free Training, High Availability, IT training, Linux for DBA, Online Training, Open Source Database, pg_basebackup, pg_dump, PITR, PostgreSQL, PostgreSQL 17, PostgreSQL architecture, PostgreSQL DBA, PostgreSQL for beginners, PostgreSQL installation, PostgreSQL performance tuning, PostgreSQL replication, PostgreSQL training, PostgreSQL Tutorial, PostgreSQL Upgrade, RDBMS, SQL, streaming replication, Telugu Tutorial

PostgreSQL DBA Free Coaching

DBA Training Program

// 5-week structured training · 19 sessions · hands-on labs

Weeks

Sessions

100+

Topics

∞

Future Free

Week 01 Lab Setup, Linux & PostgreSQL Fundamentals

Session 01 Lab Setup & Storage Configuration ▾

Pre-requisites

Install VirtualBox on Windows (Do your self)
Install PgAdmin4 on Windows (Do your self)
Configure Oracle Linux 9.6 VM (will share link)

Linux VM Setup

Create Oracle Linux 9.6 VM
VM Hardware Sizing for PostgreSQL
Add Multiple Virtual Disks

Storage Configuration

Create Partitions
Create Filesystems
Mount Filesystems
Configure /etc/fstab

PostgreSQL Directory Layout

Create Mount Points: /pgData, /pgWal, /pgArch, /pgBackup, /pgLog

Session 02 Linux Administration for DBAs ▾

Basic Linux Commands
File and Directory Management
User and Group Management
Permissions and Ownership
Process Management
Service Management using systemctl
Network Commands
Disk and Memory Monitoring

Session 03 PostgreSQL 17 Installation ▾

Overview

PostgreSQL Overview and Versions

Installation Methods

RPM Installation
DNF/YUM Installation
Source Code Installation

Database Initialization

initdb
Custom WAL Location
PostgreSQL Service Configuration

Validation

Start PostgreSQL
Connect using psql
Connect using PgAdmin4

Session 04 PostgreSQL Architecture ▾

Memory Architecture

Shared Buffers
WAL Buffers
Work Memory
Maintenance Work Memory

Process Architecture

Postmaster
Backend Processes
Checkpointer
Background Writer
WAL Writer
Autovacuum

Physical Storage Layout

Base Directory
Global Directory
WAL Directory
Tablespaces

Session 05 PostgreSQL Configuration Files ▾

postgresql.conf
postgresql.auto.conf
pg_hba.conf
pg_ident.conf
Reload vs Restart

Week 02 Administration & Security

Session 06 Startup, Shutdown ▾

PostgreSQL Startup Process
Smart Shutdown
Fast Shutdown (default)
Immediate Shutdown

Session 07 Database Administration ▾

Creating Databases
Creating Schemas
Schema Search Path
Roles and Users
Access Control

Session 08 Tablespaces Management ▾

pg_default
pg_global
Custom Tablespaces
Move Objects Between Tablespaces
Tablespace Monitoring

Session 09 Security ▾

pg_hba.conf
Authentication Methods
pg_ident.conf
.pgpass
SSL/TLS Setup

Session 10 Vacuum and Analyze ▾

MVCC Concepts
Dead Tuples
VACUUM
VACUUM FULL
ANALYZE
Autovacuum

Week 03 Backup & Recovery

Session 11 WAL Archiving ▾

WAL Fundamentals
Archive Mode
Archive Command

Session 12 Logical Backup and Restore ▾

pg_dump
pg_dumpall
Backup Formats
pg_restore
Restore using psql

Session 13 Physical Backup and Restore ▾

pg_basebackup
Full Cluster Backup
Database Refresh on New Server

Session 14 PITR — Point-in-Time Recovery ▾

PITR Concepts and Architecture
PITR Demonstration (hands-on)

Session 15 Full Recovery & Disaster Recovery ▾

Full Database Recovery
Disaster Recovery Scenarios

Week 04 Replication, Failover

Session 16 Streaming Replication ▾

Primary Configuration
Replica Configuration
Base Backup for Replica
Verify Replication

Session 17 Replication Administration ▾

Replication Slots
Monitoring Replication
Async Replication
Sync Replication
Convert from ASYNC to SYNC

Session 18 Failover and pg_rewind ▾

Manual Failover
Promote Replica
pg_rewind
Rejoin Old Primary as Replica

Week 05 Upgrades

Session 19 PostgreSQL Upgrades & Final Lab ▾

Minor Version Upgrade
Major Version Upgrades
pg_upgrade
Upgrade using pg_dump/pg_restore
Upgrade Validation
Final End-to-End Lab

Future Batch Advanced Topics — Free Training

// Topics reserved for the next advanced batch · access is free

EXPLAIN / EXPLAIN ANALYZE Index Types & Tuning Table Partitioning Performance Tuning PostgreSQL Extensions PgBouncer pgmetrics pgcollector pgbadger Repmgr Patroni Maintenance Operations Oracle → PostgreSQL (Ora2Pg) Advanced Data Migration Logical Replication Multi-Node HA Architectures

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/

Disable ARCHIVELOG Mode

PostgreSQLarchive_mode, Database Backup, pg_checkpoint, pg_switch_wal, PostgreSQL, PostgreSQL administration, PostgreSQL archiving, PostgreSQL checkpoint, PostgreSQL Configuration, PostgreSQL DBA, PostgreSQL performance tuning, PostgreSQL recovery, PostgreSQL Tips, WAL files, write ahead log

How to Disable ARCHIVELOG Mode

1. Verify Existing Archive Mode
2. Edit the archive settings
3. Restart PostgreSQL
4. Verify Current Mode
5. Verify WAL Archiving Behavior

1. Verify Existing Archive Mode

postgres=# SHOW archive_mode;
 archive_mode
--------------
 on  <------ 
(1 row)

postgres=#

postgres=# SHOW archive_command;
        archive_command
-------------------------------
 cp %p /pgArch/pgsql17/arch/%f  <----- 
(1 row)

postgres=#

2. Edit the archive settings


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

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

3. Restart PostgreSQL

[root@lxicbpgdsgv01 ~]# systemctl stop postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# systemctl start  postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# systemctl status postgresql-17.service
● postgresql-17.service - PostgreSQL 17 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
     Active: active (running) since Thu 2025-10-09 16:34:01 +08; 3s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 3492 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 3497 (postgres)
      Tasks: 7 (limit: 15835)
     Memory: 17.6M
        CPU: 92ms
     CGroup: /system.slice/postgresql-17.service
             ├─3497 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─3498 "postgres: logger "
             ├─3499 "postgres: checkpointer "
             ├─3500 "postgres: background writer "
             ├─3502 "postgres: walwriter "
             ├─3503 "postgres: autovacuum launcher "
             └─3504 "postgres: logical replication launcher "

Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com postgres[3497]: 2025-10-09 16:34:01.929 +08 [3497] LOG:  redirecting log output to logging collector process
Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com postgres[3497]: 2025-10-09 16:34:01.929 +08 [3497] HINT:  Future log output will appear in directory "log".
Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv01 ~]#

4. Verify Current Mode

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

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

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

postgres=#

5. Verify WAL Archiving Behavior


postgres=# CHECKPOINT;
CHECKPOINT
postgres=#
postgres=# CHECKPOINT;
CHECKPOINT
postgres=# CHECKPOINT;
CHECKPOINT
postgres=#
postgres=# exit
postgres=# SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/44000000
(1 row)

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

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

postgres=#
[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgArch/pgsql17/arch/
total 0  <---- Archivelogs not generating
[postgres@lxicbpgdsgv01 ~]$

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: PostgreSQL performance tuning

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

How SELECT, INSERT, UPDATE, and DELETE Work Internally in PostgreSQL

How SELECT, INSERT, UPDATE, and DELETE work internally in PostgreSQL?

1. Common Foundation (Applies to All Operations)

2. How SELECT Works

3. How INSERT Works

4. How UPDATE Works

5. How DELETE Works

PostgreSQL 17 DBA Free Training Roadmap

PostgreSQL DBA Free Coaching

PostgreSQL

PostgreSQL DBA Step by Step Learning

Disable ARCHIVELOG Mode

How to Disable ARCHIVELOG Mode

Table of Contents

1. Verify Existing Archive Mode

2. Edit the archive settings

3. Restart PostgreSQL

4. Verify Current Mode

5. Verify WAL Archiving Behavior

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

How SELECT, INSERT, UPDATE, and DELETE work internally in PostgreSQL?

1. Common Foundation (Applies to All Operations)

2. How SELECT Works

3. How INSERT Works

4. How UPDATE Works

5. How DELETE Works

PostgreSQL DBA Step by Step Learning

How to Disable ARCHIVELOG Mode

Table of Contents

1. Verify Existing Archive Mode

2. Edit the archive settings

3. Restart PostgreSQL

4. Verify Current Mode

5. Verify WAL Archiving Behavior

PostgreSQL DBA Step by Step Learning

2. The `COMMIT` Command Issued

3. Flushing to Disk (`XLogFlush`)