Tag Archives: PostgreSQL recovery

PostgreSQL

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=#

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/

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/

pg_basebackup

PostgreSQLBackup PostgreSQL Database, base backup, compressed backup, How to Backup Postgresql, pg_basebackup, pg_basebackup examples, pg_basebackup gzip, pg_basebackup restore, pg_basebackup tar, pg_hba.conf, pg_wal, physical backup, PITR, point in time recovery, postgres disaster recovery, postgres replication, postgresql 17 backup, PostgreSQL archive, PostgreSQL Backup, PostgreSQL recovery, PostgreSQL Restore, recovery.signal, replication role, tar backup

pg_basebackup – Backup, Restore, and Recovery

What is pg_basebackup?

pg_basebackup is a utility provided by PostgreSQL to take a physical base backup of the entire database cluster.

Common Use Cases:

Setting up standby servers for streaming replication
Creating physical backups for disaster recovery
Performing Point-In-Time Recovery (PITR)
Performing Incremental Backups (PostgreSQL v17 feature)

It connects to a running PostgreSQL server and copies all necessary data files and WAL segments, producing a consistent and restorable backup.

———- Backup ———-

Pre-requisites parameter and config:

1. User : Requires a user with replication role or superuser privileges

CREATE ROLE repl_user WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'replpass';

2. postgresql.conf

wal_level=replica (Mandatory)
max_wal_senders >=1 (Mandatory)
archive_mode=on (Optional if Database in NO Archivelog mode)
archive_command = ‘cp %p /pgArch/pgsql17/arch/%f’ (Optional if Database in NO Archivelog mode)

3. pg_hba.conf

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

# or you can use peer (if same OS user postgres is used)
# local   replication     all                                 peer

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

How pg_basebackup Works Internally:

Feature	`pg_basebackup` Supports?
Backup of data directory	Yes
Include enough WALs to make backup consistent	Yes
Backup of WAL archives (even with archive mode=on & archive_command configured)	No
Works without `archive_mode`	Yes

Performs a physical file-level backup by copying the full data directory
Uses PostgreSQL’s streaming replication protocol
Requires a user with replication role or superuser privileges
Can be used while the server is running (online backup)
Ensures a transactionally consistent snapshot of the database
Even with archive_mode=on and archive_command configured, pg_basebackup does not back up WAL archive logs from the archive location. It only includes enough live WAL files to make the backup consistent, streamed from pg_wal/.
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.

What It Includes:

All essential data files of the cluster
Necessary WAL (Write-Ahead Log) segments for recovery
Custom tablespaces, replication slots, and large objects

WAL Handling Options:

Default: WAL files included after backup (-X fetch)
Streaming: WAL files streamed live during backup (-X stream)

How to take Backup using pg_basebackup ?

Want symlinks preserved → use (both -Fp & –waldir)-Fp --waldir=/pgWal/pgsql17/wal
Want single tar archive → use -Ft, but recreate symlinks after restore, for pg_wal
The –waldir option in pg_basebackup is supported only when using the plain format (-Fp), not with the tar format (-Ft).

-- Do not use -R here since it's not a replica (No standby).

Typically Backup using: Plain format, Tar format & Tar format (gzip)

1. Take backup in Plain format
nohup pg_basebackup -U postgres -D /pgBackups/pgsql17/demo_restore -Fp -Xs -P -v > pg_basebackup_demo_restore.log 2>&1 &

2. Take backup in Tar format
nohup pg_basebackup -U postgres -D /pgBackups/pgsql17/demo_tar_backup -Ft -Xs -P -v > pg_basebackup_tar.log 2>&1 &

3. Take backup in Compressed Tar format (gzip)
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/ -Ft -z -Xs -P -v > pg_basebackup_tar.log 2>&1 &

4. Take compressed Tar backup with transfer rate limit
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/ -Ft -z -X stream -P --max-rate=5M -v > pg_basebackup_tar.log 2>&1 &

5. Take compressed Tar backup with server-side gzip compression at max level (9)
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/ -Ft --compress=server-gzip:9 -Xs -P -v > pg_basebackup.log 2>&1 &

6. Take Tar backup from a remote host
nohup pg_basebackup -h remote_host -p port -U postgres -D /pgBackup/remote_tar_backup -Ft -Xs -P -v > pg_basebackup_remote_tar.log 2>&1 &

PG BASE BACKUP Flag	Description
`-U <username>`	Specifies the PostgreSQL user to connect as.
`-D <directory>`	Specifies the target directory for the backup.
`-F p`	Takes the backup in Plain format (file system copy).
`-F t`	Takes the backup in Tar archive format.
`-z`	Compresses the backup using gzip compression (only valid with tar format).
`--compress=server-gzip:<level>`	Enables server-side gzip compression with specified compression level (1-9).
`-X s`	Includes the Write-Ahead Log (WAL) files by copying the WAL segment files.
`-X stream`	Streams the WAL files while taking the backup for continuous consistency.
`-P`	Shows progress information during the backup.
`--max-rate=<rate>`	Limits the maximum transfer rate during the backup (e.g., 5M for 5 megabytes/sec).
`-v`	–verbose, extra output

———- Restore ———-

1. Restore is a File-Level Operation

A base backup is a physical copy of the database files — including system catalogs, user data, WAL files, and optionally config files.
Restoring is done by simply copying or extracting the backup files into a valid PostgreSQL data directory (PGDATA) on the target system.

Restore Steps:

Stop PostgreSQL (if running)
Copy or extract the backup into a clean data directory (PGDATA)
Place a file named recovery.signal in the data directory
Start PostgreSQL to begin recovery

2. Custom Tablespaces Outside `PGDATA`

Backups include symlinks to external tablespace locations
On restore:
- Ensure original paths exist and are accessible
- Or remap symlinks to new locations
- Check ownership and permissions (postgres:postgres, 0700)

3. Restoring on a Different Host

Ensure matching directory structure or adjust accordingly
PostgreSQL version and architecture must match
Update postgresql.conf and pg_hba.conf as needed

4. Restoring Across PostgreSQL Versions

pg_basebackup is version-specific.

Not allowed: PostgreSQL 14 → PostgreSQL 15 or 17 (Lower to Higher)
Not allowed: PostgreSQL 17 → PostgreSQL 14 (Higher to Lower)
Use pg_dump/pg_restore or pg_upgrade for version upgrades

———- Recovery ———-

How Recovery Works:

PostgreSQL detects recovery.signal at startup
WAL files (in pg_wal/ or archive) are replayed (Redo apply / Archive apply from restore_command location )
When recovery completes:
- PostgreSQL automatically removes recovery.signal
- The server becomes a primary (read/write)
Recovery stops when:
- All available WALs are applied, or
- A recovery target (e.g., timestamp, transaction ID) is reached

What is recovery.signal ?

File	Purpose
`recovery.signal`	Tells PostgreSQL to enter recovery mode during startup
Automatically removed?	Yes, after recovery completes
Required for standalone restore?	Yes, otherwise WAL replay is skipped

Summary

1. Take a base backup on the source server using ‘pg_basebackup’.
2. Take Backup of WAL Archive files separately on Source (Manually, because pg_basebackup does NOT take backup of WAL archive files)
3. Transfer both the base backup and WAL archive logs to the target server.
4. Restore the base backup to the PostgreSQL data directory on the target.
5. Copy the WAL files to the dedicated WAL location (e.g., ‘/pgwal/pgsql17/wals’).
6. Remove ‘$PGDATA/pg_wal’ and create a symbolic link to the WAL location:
‘ln -s /pgwal/pgsql17/wals /pgdata/pgsql17/data/pg_wal’
7. Create an empty ‘recovery.signal‘ file in ‘$PGDATA’.
8. Set ‘restore_command‘ in ‘postgresql.conf’ to point to WAL archive backup path (e.g., ‘/pgbackup/pgsql17/backup/wal_archive_bkp’).
9. Ensure WAL archive files are restored to ‘/pgbackup/pgsql17/backup/wal_archive_bkp’ for ‘restore_command’ to access.
10. Update ‘tablespace_map‘ to reflect correct paths if using custom tablespaces.
11. Start PostgreSQL on the target; recovery will complete and automatically remove ‘recovery.signal’.
12. Tablespace symbolic links will create automatically by PostgreSQL.

Operation	Key Point
Backup	File-level copy using replication protocol
Restore	Place files into `PGDATA`, handle symlinks, configs, and versions
Recovery	Triggered by `recovery.signal`, replays WAL, auto-removes signal file

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

WAL Files

PostgreSQLarchive log, archive_command, database logs, min_wal_size, pg_wal, PITR, PostgreSQL 17, PostgreSQL Backup, PostgreSQL Configuration, PostgreSQL recovery, PostgreSQL vs Oracle, redo logs, WAL archives, WAL files

Understanding WAL Files in PostgreSQL – For Oracle DBAs

What is WAL?

WAL (Write-Ahead Logging) ensures data durability and crash recovery. Every change is first written to WAL before being flushed to data files. 

1. WAL Location: WAL files are stored in $PGDATA/pg_wal/.
2. Fixed Size: Each WAL segment is pre-allocated with a fixed size (typically 16MB), set at cluster initialization (initdb).
3. Writing & Switching: WAL segments are written sequentially. PostgreSQL switches to the next segment when the current one is full or a manual switch is triggered (e.g., via pg_switch_wal()).
4. WAL and Archive File Size: When archived, the exact same 16MB WAL segment file is copied as raw binary to the archive destination. Therefore, archive files always match the WAL segment size.
5. Archiving: If archive_mode = on, PostgreSQL uses the archive_command to copy completed WAL segments to the archive destination (e.g., /pgArch/...). Only full segments are archived.
6. Recycling: If archiving is disabled, PostgreSQL reuses old WAL segments once they’re no longer needed for recovery or replication.

WAL Files = Redo Log Files
WAL Archives = Archive Log Files

PostgreSQL vs Oracle Comparison

Aspect	Oracle	PostgreSQL
Redo Log Files	Called Redo Log Files	Called WAL Files
Archive Log Files	Called Archive Log Files	Called WAL Archives
Redo Log File Size	~200MB (default)	16MB (default and fixed)
Redo Log Files Count	Minimum 2 or more redo log groups (Best practice: 3)	No fixed number Controlled by: min_wal_size / wal_segment_size Example: 80MB / 16MB = 5 WAL files
Archive Log File Size	~200MB (same as redo log file size)	16MB (same as WAL file size)
Log File Storage	Redo log files stored in redo log groups	WAL files stored in `pg_wal` directory
Archiving Mechanism	Archive logs created if archiving is enabled	Archived via `archive_command` if `archive_mode = on`
Overwrite Behavior	Redo logs are overwritten when full (if archive not enabled)	WAL segments are recycled/overwritten when full (if `archive_mode` is off)
Force Switch Behavior	ALTER SYSTEM SWITCH LOGFILE;	SELECT pg_switch_wal();

PostgreSQL WAL Configuration Parameters

Parameter	Meaning	Default
`wal_segment_size`	Size of each WAL file	16MB
`min_wal_size`	Minimum total WAL size retained	80MB (5 WAL files) Common in production: 8GB
`max_wal_size`	Max WAL before triggering a checkpoint	1GB Common in production: 16GB

Check Configuration

[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW wal_segment_size;"
wal_segment_size
------------------
16MB  <----- Each redo log file size
(1 row)

[postgres@lxicbpgdsgv01 ~]$


[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW min_wal_size;"
 min_wal_size
--------------
 80MB <---- 80MB/16MB = 5 <--- Total 5 redo log files with each 16MB
(1 row)

[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW max_wal_size;"
 max_wal_size
--------------
 1GB  <---- 1024MB
(1 row)

[postgres@lxicbpgdsgv01 ~]$

View WAL files (Redo log files)

postgres=# SELECT current_setting('data_directory') || '/pg_wal' AS wal_directory;
        wal_directory
-----------------------------
 /pgData/pgsql17/data/pg_wal  <---- Redo log file directory
(1 row)

postgres=# 
[postgres@lxicbpgdsgv01 ~]$ ls -lh /pgData/pgsql17/data/pg_wal | grep -E '^-.* [0-9]+ .* [0-9A-F]{24}$'
-rw-------. 1 postgres postgres 16M Oct 8 01:33 000000010000000000000005
-rw-------. 1 postgres postgres 16M Oct 8 01:28 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct 8 01:28 000000010000000000000007
[postgres@lxicbpgdsgv01 ~]$

min_wal_size = 80MB means PostgreSQL will try to keep at least 5 WAL segment files on disk (since 80MB ÷ 16MB = 5 files).

However, in our case, only 3 WAL files are currently present. This is likely because the system is under low load in the testing environment, so fewer WAL files are needed at the moment.

---- Lets Generate Load

postgres=# -- Create test table
DROP TABLE IF EXISTS wal_test;
CREATE TABLE wal_test (
    id serial PRIMARY KEY,
    data text
);

-- Generate WAL traffic
DO $$
DECLARE
    i integer;
BEGIN
    FOR i IN 1..50 LOOP
        -- INSERT: 10,000 rows
        INSERT INTO wal_test (data)
        SELECT repeat('wal_test_data_', 50)
        FROM generate_series(1, 10000);

        -- UPDATE: 5,000 rows using CTE with LIMIT
        WITH to_update AS (
            SELECT id FROM wal_test WHERE id % 2 = 0 LIMIT 5000
        )
        UPDATE wal_test
        SET data = data || '_updated'
        WHERE id IN (SELECT id FROM to_update);

        -- DELETE: 5,000 rows using CTE with LIMIT
        WITH to_delete AS (
            SELECT id FROM wal_test WHERE id % 3 = 0 LIMIT 5000
        )
        DELETE FROM wal_test
        WHERE id IN (SELECT id FROM to_delete);

        -- Commit to flush WAL
        COMMIT;

        -- Optional pause to slow down the loop
        PERFORM pg_sleep(0.5);
    END LOOP;
END$$;
DROP TABLE
CREATE TABLE
DO
postgres=# exit
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ 
ls -lrth /pgData/pgsql17/data/pg_wal
total 752M
drwx------. 2 postgres postgres    6 Sep 30 21:50 summaries
-rw-------. 1 postgres postgres  16M Oct  8 16:34 000000010000000000000009
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000A
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000B
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000C
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000D
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000E
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000F
-rw-------. 1 postgres postgres  16M Oct  8 16:34 000000010000000000000010
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000011
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000012
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000013
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000014
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000015
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000016
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000017
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000018
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000019
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001A
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001B
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001C
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001D
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001E
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001F
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000020
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000021
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000022
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000023
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000024
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000025
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000026
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000027
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000028
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000029
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002A
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002B
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002C
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002D
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002E
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002F
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000030
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000031
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000032
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000033
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000034
-rw-------. 1 postgres postgres  16M Oct  8 16:36 000000010000000000000035
drwx------. 2 postgres postgres 4.0K Oct  8 16:36 archive_status
-rw-------. 1 postgres postgres  16M Oct  8 16:36 xlogtemp.7289
[postgres@lxicbpgdsgv01 ~]$ 

-- Now We can see Many WAL Files (Redo log files) generated, count increased from 3 WAL files to 50+ WAL files

View Archived WAL Files (Archive log files)

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

postgres=#

[postgres@lxicbpgdsgv01 ~]$ 
[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgArch/pgsql17/arch/
total 976M
-rw-------. 1 postgres postgres 16M Oct  7 23:50 000000010000000000000001
-rw-------. 1 postgres postgres 16M Oct  7 23:51 000000010000000000000002
-rw-------. 1 postgres postgres 16M Oct  8 01:28 000000010000000000000003
-rw-------. 1 postgres postgres 16M Oct  8 01:28 000000010000000000000004
-rw-------. 1 postgres postgres 16M Oct  8 04:24 000000010000000000000005
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct  8 16:24 000000010000000000000007
-rw-------. 1 postgres postgres 16M Oct  8 16:25 000000010000000000000008
-rw-------. 1 postgres postgres 16M Oct  8 16:34 000000010000000000000009
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000A
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000B
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000C
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000D
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000E
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000F
-rw-------. 1 postgres postgres 16M Oct  8 16:34 000000010000000000000010
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000011
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000012
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000013
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000014
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000015
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000016
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000017
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000018
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000019
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001A
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001B
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001C
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001D
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001E
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001F
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000020
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000021
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000022
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000023
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000024
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000025
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000026
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000027
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000028
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000029
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002A
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002B
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002C
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002D
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002E
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002F
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000030
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000031
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000032
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000033
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000034
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000035
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000036
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000037
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000038
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000039
-rw-------. 1 postgres postgres 16M Oct  8 16:36 00000001000000000000003A
-rw-------. 1 postgres postgres 16M Oct  8 16:36 00000001000000000000003B
-rw-------. 1 postgres postgres 16M Oct  8 16:36 00000001000000000000003C
-rw-------. 1 postgres postgres 16M Oct  8 16:36 00000001000000000000003D
[postgres@lxicbpgdsgv01 ~]$

-- WAL and Archive File Size: When archived, the exact same 16MB WAL segment file is copied as raw binary to the archive destination. Therefore, archive files always match the WAL segment size (redo log size).

Best Practices

Use a dedicated mount point for pg_wal (WAL directory) – eg. /pgWal
Set min_wal_size to higher value in production (e.g. 8GB)
Keep WAL archive files on a separate mount point for better I/O and space isolation – eg. /pgArch
Monitor WAL archive directory regularly to avoid disk full errors

Note:

WAL file size is fixed at cluster init (default: 16MB). You cannot change it for an existing cluster.
PostgreSQL WAL Size Be Can Increased only at NEW cluster creation time via initdb (64MB)
/usr/pgsql-17/bin/initdb –wal-segsize=64 -D /pgData/pgsql17/data

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/

PostgreSQL DBA Step by Step Learning

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

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

pg_basebackup – Backup, Restore, and Recovery

What is pg_basebackup?

Common Use Cases:

———- Backup ———-

Pre-requisites parameter and config:

1. User : Requires a user with replication role or superuser privileges

2. postgresql.conf

3. pg_hba.conf

How pg_basebackup Works Internally:

What It Includes:

WAL Handling Options:

How to take Backup using pg_basebackup ?

———- Restore ———-

1. Restore is a File-Level Operation

Restore Steps:

2. Custom Tablespaces Outside PGDATA

3. Restoring on a Different Host

4. Restoring Across PostgreSQL Versions

———- Recovery ———-

How Recovery Works:

What is recovery.signal ?

Summary

Understanding WAL Files in PostgreSQL – For Oracle DBAs

What is WAL?

PostgreSQL vs Oracle Comparison

PostgreSQL WAL Configuration Parameters

Check Configuration

View WAL files (Redo log files)

View Archived WAL Files (Archive log files)

Best Practices

PostgreSQL DBA Step by Step Learning

2. Custom Tablespaces Outside `PGDATA`