Tag Archives: PostgreSQL WAL

Types of Shutdown in PostgreSQL

Types of Shutdown in PostgreSQL

PostgreSQL Shutdown Type	Oracle	Behavior
Smart (SIGTERM)	SHUTDOWN NORMAL	The server stops accepting new connections but allows existing sessions to continue running normally. PostgreSQL shuts down only after all active sessions disconnect and their transactions complete successfully.
Fast (SIGINT – Default)	SHUTDOWN IMMEDIATE	The server stops accepting new connections and immediately terminates all active sessions. Any uncommitted transactions are rolled back, and PostgreSQL performs a clean shutdown gracefully.
Immediate (SIGQUIT)	SHUTDOWN ABORT	The server terminates all processes instantly without performing a proper shutdown. No cleanup or checkpoint occurs, so PostgreSQL performs crash recovery by replaying WAL files during the next startup.

Shutdown Type	Signal	Command Example	Behavior
Smart	SIGTERM	`pg_ctl stop -D /pgData -m smart`	Stops new connections, waits for existing sessions to finish normally, then shuts down.
Fast (Default)	SIGINT	`pg_ctl stop -D /pgdata -m fast`	Stops new connections, terminates active sessions, rolls back uncommitted transactions, and shuts down cleanly.
Immediate	SIGQUIT	`pg_ctl stop -D /pgdata -m immediate`	Stops immediately without proper shutdown. Recovery from WAL files happens during next startup.

Difference Between Reload and Restart

When we change server configuration parameters, PostgreSQL needs to read those changes before they become active.
Reload applies the new configuration without stopping or restarting the database service. Existing connections and database activity continue normally.
Some configuration parameter changes cannot be applied through reload and will only take effect after a restart.
Restart stops the PostgreSQL server gracefully, ends all running activities, releases resources, closes open files, and then starts the server again with the new configuration.

Smart Shutdown (SIGTERM):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m smart
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:26:59.741 +08 [3476] LOG:  received smart shutdown request
2026-07-03 14:26:59.752 +08 [3476] LOG:  background worker "logical replication launcher" (PID 3483) exited with exit code 1
2026-07-03 14:26:59.753 +08 [3478] LOG:  shutting down
2026-07-03 14:26:59.755 +08 [3478] LOG:  checkpoint starting: shutdown immediate
2026-07-03 14:26:59.765 +08 [3478] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.003 s, total=0.012 s; sync files=2, longest=0.003 s, average=0.002 s; distance=0 kB, estimate=0 kB; lsn=1/105465F0, redo lsn=1/105465F0
2026-07-03 14:26:59.771 +08 [3476] LOG:  database system is shut down


[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:27:18.394 +08 [3487] LOG:  redirecting log output to logging collector process
2026-07-03 14:27:18.394 +08 [3487] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:27:18.394 +08 [3487] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:27:18.396 +08 [3487] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:27:18.396 +08 [3487] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:27:18.399 +08 [3487] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:27:18.404 +08 [3487] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:27:18.413 +08 [3491] LOG:  database system was shut down at 2026-07-03 14:26:59 +08
2026-07-03 14:27:18.425 +08 [3487] LOG:  database system is ready to accept connections

Fast Shutdown (SIGINT – Default):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m fast
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:28:26.839 +08 [3487] LOG:  received fast shutdown request
2026-07-03 14:28:26.844 +08 [3487] LOG:  aborting any active transactions
2026-07-03 14:28:26.854 +08 [3487] LOG:  background worker "logical replication launcher" (PID 3494) exited with exit code 1
2026-07-03 14:28:26.860 +08 [3489] LOG:  shutting down
2026-07-03 14:28:26.862 +08 [3489] LOG:  checkpoint starting: shutdown immediate
2026-07-03 14:28:26.874 +08 [3489] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.005 s, total=0.015 s; sync files=2, longest=0.004 s, average=0.003 s; distance=0 kB, estimate=0 kB; lsn=1/105466A0, redo lsn=1/105466A0
2026-07-03 14:28:26.882 +08 [3487] LOG:  database system is shut down

[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:29:00.574 +08 [3505] LOG:  redirecting log output to logging collector process
2026-07-03 14:29:00.574 +08 [3505] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:29:00.575 +08 [3505] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:29:00.579 +08 [3505] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:29:00.580 +08 [3505] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:29:00.582 +08 [3505] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:29:00.587 +08 [3505] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:29:00.595 +08 [3509] LOG:  database system was shut down at 2026-07-03 14:28:26 +08
2026-07-03 14:29:00.607 +08 [3505] LOG:  database system is ready to accept connections

Immediate Shutdown (SIGQUIT):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m immediate
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:29:47.471 +08 [3505] LOG:  received immediate shutdown request
2026-07-03 14:29:47.485 +08 [3505] LOG:  database system is shut down


[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:30:17.965 +08 [3520] LOG:  redirecting log output to logging collector process
2026-07-03 14:30:17.965 +08 [3520] HINT:  Future log output will appear in directory "log".
. done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:30:17.966 +08 [3520] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:30:17.966 +08 [3520] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:30:17.966 +08 [3520] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:30:17.970 +08 [3520] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:30:17.974 +08 [3520] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:30:17.983 +08 [3524] LOG:  database system was interrupted; last known up at 2026-07-03 14:29:00 +08
2026-07-03 14:30:19.211 +08 [3524] LOG:  database system was not properly shut down; automatic recovery in progress
2026-07-03 14:30:19.216 +08 [3524] LOG:  redo starts at 1/10546718
2026-07-03 14:30:19.216 +08 [3524] LOG:  invalid record length at 1/10546750: expected at least 24, got 0
2026-07-03 14:30:19.217 +08 [3524] LOG:  redo done at 1/10546718 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2026-07-03 14:30:19.221 +08 [3522] LOG:  checkpoint starting: end-of-recovery immediate wait
2026-07-03 14:30:19.231 +08 [3522] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.003 s, total=0.012 s; sync files=2, longest=0.002 s, average=0.002 s; distance=0 kB, estimate=0 kB; lsn=1/10546750, redo lsn=1/10546750
2026-07-03 14:30:19.239 +08 [3520] LOG:  database system is ready to accept connections

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: https://www.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

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/

Backup and Restore on Same Host Using pg_basebackup

How to Backup and Restore PostgreSQL DB Cluster on the Same Host Using pg_basebackup

1. Environment
2. Verify Existing DB Setup
3. Pre-requisites
4. Take Backup
5. Prepare & Restore
5.1 Stop PostgreSQL and Clean Directories
5.2 Remove all from Data & WAL directories
5.3 Restore Data and WAL
A. Copy Backup files to $PGDATA
B. Copy WAL files from $PGDATA/pg_wal to /pgWal/pgsql17/wal/
C. Remove the existing $PGDATA/pg_wal directory
D. Create a symbolic link pointing $PGDATA/pg_wal to a separate WAL directory
E. Set Permissions
6. Start PostgreSQL
7. Final Verification

1. Environment

ASPECT	Env
Hostname	lxicbpgdsgv01
IP Address	192.168.2.51
OS	RHEL 9
DB Version	PostgreSQL v17.6
Archive mode	archive_mode=off
pgData	/pgData/pgsql17/data
WAL Directory	/pgWal/pgsql17/wal
Tablespace	pg_default
Databases	DELL, ORCL

2. Verify Existing DB Setup

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

postgres=# \l+
                                                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------+---------+------------+--------------------------------------------
 dell      | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 7763 kB | pg_default |
 orcl      | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 7907 kB | pg_default |
 postgres  | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 492 MB  | pg_default | default administrative connection database
 template0 | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           | =c/postgres          +| 7545 kB | pg_default | unmodifiable empty database
           |          |          |                 |             |             |        |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           | =c/postgres          +| 7723 kB | pg_default | default template for new databases
           |          |          |                 |             |             |        |           | postgres=CTc/postgres |         |            |
(5 rows)

postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres |
 pg_global  | postgres |
(2 rows)

postgres=# \c dell
You are now connected to database "dell" as user "postgres".
dell=# select * from test.emp;
 name | designation | project | company
------+-------------+---------+---------
 Sugi | DBA         | Jetstar | iGATE
 Teja | DBA         | RCM     | iGATE
 RAJ  | DBA         | RCM     | iGATE
(3 rows)

dell=#

3. Pre-requisites

Verify postgresql.conf

postgres=# SHOW wal_level;
 wal_level
-----------
 replica <----
(1 row)

postgres=#
postgres=# SHOW max_wal_senders;
 max_wal_senders
-----------------
 10
(1 row)

postgres=# SHOW archive_mode;
 archive_mode
--------------
 off
(1 row)

postgres=#

Verify pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS             METHOD
# Local connections for replication (for pg_basebackup run locally)
local   replication     all                                 trust

# Remote connections for replication (for pg_basebackup run remotely)
#host    replication     repl_user       192.168.2.52/32     scram-sha-256

Verify user permissions:REPLICATION or SUPERUSER required

postgres=# \du
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres=#

Verify free space for backup

[postgres@lxicbpgdsgv01 ~]$ du -sh /pgData/pgsql17/data/
524M    /pgData/pgsql17/data/
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ du -sh /pgWal/pgsql17/wal/
801M    /pgWal/pgsql17/wal/
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ df -h /pgBackup/
Filesystem      Size  Used Avail Use% Mounted on
/dev/sdd1       100G  746M  100G   1% /pgBackup <----
[postgres@lxicbpgdsgv01 ~]$ mkdir -p /pgBackup/pgsql17/backup/basebackup_10OCT2025
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ mkdir -p /pgBackup/pgsql17/backup/log/
[postgres@lxicbpgdsgv01 ~]$

4. Take Backup

[postgres@lxicbpgdsgv01 ~]$ ls -ltrh /pgWal/pgsql17/wal | wc -l
53 <---- 
[postgres@lxicbpgdsgv01 ~]$

pg_basebackup does not include all files from the pg_wal directory in the backup. Instead, it selectively includes only the WAL files required to make the base backup consistent at the point in time the backup was taken.

-- If you want to store the base backup and WAL files in separate backup directories. 

nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/basebackup_10OCT2025 --waldir=/pgBackup/pgsql17/backup/wal_backup -Fp -Xs -P -v > /pgBackup/pgsql17/backup/log/basebackup_10OCT2025.log 2>&1 &

The --waldir option in pg_basebackup is supported only when using the plain format (-Fp), not with the tar format (-Ft).

If we want symlinks preserved → use (both -Fp & --waldir use together) -Fp --waldir=/pgWal/pgsql17/wal

-- OR --

The directory mush be empty: /pgBackup/pgsql17/backup/basebackup_10OCT2025

[postgres@lxicbpgdsgv01 ~]$ nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/basebackup_10OCT2025 -Fp -Xs -P -v > /pgBackup/pgsql17/backup/log/basebackup_10OCT2025.log 2>&1 &
[1] 4973
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ cat /pgBackup/pgsql17/backup/log/basebackup_10OCT2025.log
nohup: ignoring input
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/85000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_4975"
279413/536037 kB (52%), 0/1 tablespace (...asebackup_10OCT2025/base/5/16533)
536047/536047 kB (100%), 0/1 tablespace (...ckup_10OCT2025/global/pg_control)
536047/536047 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/85000158
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed  <-----
[postgres@lxicbpgdsgv01 ~]$

5. Prepare & Restore

5.1 Stop PostgreSQL and Clean Directories

[root@lxicbpgdsgv01 ~]# systemctl stop postgresql-17.service
[root@lxicbpgdsgv01 ~]# 
[root@lxicbpgdsgv01 ~]# ps -ef | grep postgres
root        5057    3151  0 18:57 pts/0    00:00:00 grep --color=auto postgres
[root@lxicbpgdsgv01 ~]#

5.2 Remove all from Data & WAL directories

-- Remove all from PGDATA directory

[root@lxicbpgdsgv01 ~]# rm -rf /pgData/pgsql17/data/*
[root@lxicbpgdsgv01 ~]# ls -ltr /pgData/pgsql17/data/
total 0
[root@lxicbpgdsgv01 ~]#

-- Remove all from WAL directory 

[root@lxicbpgdsgv01 ~]# rm -rf /pgWal/pgsql17/wal/*
[root@lxicbpgdsgv01 ~]# ls -ltr /pgWal/pgsql17/wal/
total 0
[root@lxicbpgdsgv01 ~]#

5.3 Restore Data and WAL

A. Copy Backup files to $PGDATA

Since we did not use the -Fp format with the --waldir option, all required WAL files will be included in the backup under the default path: PGDATA/pg_wal.

[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgData/pgsql17/data/
total 0
[postgres@lxicbpgdsgv01 ~]$ cd /pgBackup/pgsql17/backup/basebackup_10OCT2025/
[postgres@lxicbpgdsgv01 basebackup_10OCT2025]$ cp -Rp * /pgData/pgsql17/data/
[postgres@lxicbpgdsgv01 basebackup_10OCT2025]$
[postgres@lxicbpgdsgv01 basebackup_10OCT2025]$ ls -ltr /pgData/pgsql17/data/
total 296
-rw-------. 1 postgres postgres    227 Oct 10 18:41 backup_label
drwx------. 4 postgres postgres     77 Oct 10 18:41 pg_wal
drwx------. 7 postgres postgres     59 Oct 10 18:41 base
drwx------. 4 postgres postgres     68 Oct 10 18:41 pg_logical
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_dynshmem
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_commit_ts
drwx------. 2 postgres postgres    110 Oct 10 18:41 log
-rw-------. 1 postgres postgres   1169 Oct 10 18:41 postgresql.conf.bkp_10sep2025
-rw-------. 1 postgres postgres  30702 Oct 10 18:41 postgresql.conf.bkp
-rw-------. 1 postgres postgres     88 Oct 10 18:41 postgresql.auto.conf
drwx------. 2 postgres postgres     18 Oct 10 18:41 pg_xact
-rw-------. 1 postgres postgres      3 Oct 10 18:41 PG_VERSION
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_twophase
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_tblspc
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_subtrans
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_stat_tmp
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_stat
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_snapshots
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_serial
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_replslot
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_notify
drwx------. 4 postgres postgres     36 Oct 10 18:41 pg_multixact
-rw-------. 1 postgres postgres   2640 Oct 10 18:41 pg_ident.conf
-rw-------. 1 postgres postgres   5600 Oct 10 18:41 pg_hba.conf
-rw-------. 1 postgres postgres   1171 Oct 10 18:41 postgresql.conf
drwx------. 2 postgres postgres   4096 Oct 10 18:41 global
-rw-------. 1 postgres postgres     30 Oct 10 18:41 current_logfiles
-rw-------. 1 postgres postgres 227736 Oct 10 18:41 backup_manifest
[postgres@lxicbpgdsgv01 basebackup_10OCT2025]$

B. Copy WAL files from $PGDATA/pg_wal to /pgWal/pgsql17/wal/

[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgWal/pgsql17/wal/
total 0
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ cd /pgData/pgsql17/data/pg_wal/
[postgres@lxicbpgdsgv01 pg_wal]$ cp -Rp * /pgWal/pgsql17/wal
[postgres@lxicbpgdsgv01 pg_wal]$
[postgres@lxicbpgdsgv01 pg_wal]$ ls -ltr /pgWal/pgsql17/wal
total 16384
drwx------. 2 postgres postgres        6 Oct 10 18:41 summaries
drwx------. 2 postgres postgres        6 Oct 10 18:41 archive_status
-rw-------. 1 postgres postgres 16777216 Oct 10 18:41 000000010000000000000085
[postgres@lxicbpgdsgv01 pg_wal]$ cd
[postgres@lxicbpgdsgv01 ~]$

C. Remove the existing $PGDATA/pg_wal directory

[postgres@lxicbpgdsgv01 ~]$ rm -rf /pgData/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv01 ~]$

D. Create a symbolic link pointing $PGDATA/pg_wal to a separate WAL directory

[postgres@lxicbpgdsgv01 ~]$ ln -s /pgWal/pgsql17/wal /pgData/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal
lrwxrwxrwx. 1 postgres postgres 18 Oct 10 19:43 /pgData/pgsql17/data/pg_wal -> /pgWal/pgsql17/wal
[postgres@lxicbpgdsgv01 ~]$

E. Set Permissions

[postgres@lxicbpgdsgv01 ~]$ chown -R postgres:postgres /pgData/pgsql17/data
[postgres@lxicbpgdsgv01 ~]$ chmod 700 /pgData/pgsql17/data
[postgres@lxicbpgdsgv01 ~]$ chown -R postgres:postgres /pgWal/pgsql17/wal
[postgres@lxicbpgdsgv01 ~]$ chmod 700 /pgWal/pgsql17/wal

6. Start PostgreSQL

[root@lxicbpgdsgv01 ~]# systemctl start postgresql-17.service
[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 Fri 2025-10-10 19:45:21 +08; 5s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 5230 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 5235 (postgres)
      Tasks: 7 (limit: 15835)
     Memory: 34.2M
        CPU: 94ms
     CGroup: /system.slice/postgresql-17.service
             ├─5235 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─5236 "postgres: logger "
             ├─5237 "postgres: checkpointer "
             ├─5238 "postgres: background writer "
             ├─5240 "postgres: walwriter "
             ├─5241 "postgres: autovacuum launcher "
             └─5242 "postgres: logical replication launcher "

Oct 10 19:45:21 lxicbpgdsgv01.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 10 19:45:21 lxicbpgdsgv01.rajasekhar.com postgres[5235]: 2025-10-10 19:45:21.177 +08 [5235] LOG:  redirecting log output to logging collector process
Oct 10 19:45:21 lxicbpgdsgv01.rajasekhar.com postgres[5235]: 2025-10-10 19:45:21.177 +08 [5235] HINT:  Future log output will appear in directory "log".
Oct 10 19:45:21 lxicbpgdsgv01.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv01 ~]#

7. Final Verification

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

postgres=# \l+
                                                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------+---------+------------+--------------------------------------------
 dell      | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 7609 kB | pg_default |
 orcl      | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 7753 kB | pg_default |
 postgres  | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 492 MB  | pg_default | default administrative connection database
 template0 | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           | =c/postgres          +| 7545 kB | pg_default | unmodifiable empty database
           |          |          |                 |             |             |        |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           | =c/postgres          +| 7569 kB | pg_default | default template for new databases
           |          |          |                 |             |             |        |           | postgres=CTc/postgres |         |            |
(5 rows)

postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres |
 pg_global  | postgres |
(2 rows)

postgres=# \c dell
You are now connected to database "dell" as user "postgres".
dell=# select * from test.emp;
 name | designation | project | company
------+-------------+---------+---------
 Sugi | DBA         | Jetstar | iGATE
 Teja | DBA         | RCM     | iGATE
 RAJ  | DBA         | RCM     | iGATE
(3 rows)

dell=#

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 WAL

Types of Shutdown in PostgreSQL

Types of Shutdown in PostgreSQL

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

PostgreSQL DBA Step by Step Learning

Backup and Restore on Same Host Using pg_basebackup

How to Backup and Restore PostgreSQL DB Cluster on the Same Host Using pg_basebackup

Table of Contents

1. Environment

2. Verify Existing DB Setup

3. Pre-requisites

4. Take Backup

5. Prepare & Restore

5.1 Stop PostgreSQL and Clean Directories

5.2 Remove all from Data & WAL directories

5.3 Restore Data and WAL

A. Copy Backup files to $PGDATA

B. Copy WAL files from $PGDATA/pg_wal to /pgWal/pgsql17/wal/

C. Remove the existing $PGDATA/pg_wal directory

D. Create a symbolic link pointing $PGDATA/pg_wal to a separate WAL directory

E. Set Permissions

6. Start PostgreSQL

7. Final Verification

PSQL

PostgreSQL DBA Step by Step Learning