Tag Archives: pg_wal

Backup and Restore on Same Host Using pg_basebackup

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

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

1. Environment

ASPECTEnv
Hostnamelxicbpgdsgv01
IP Address192.168.2.51
OSRHEL 9
DB VersionPostgreSQL v17.6
Archive modearchive_mode=off
pgData/pgData/pgsql17/data
WAL Directory/pgWal/pgsql17/wal
Tablespacepg_default
DatabasesDELL, 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/

pg_basebackup

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , ,

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:

Featurepg_basebackup Supports?
Backup of data directoryYes
Include enough WALs to make backup consistentYes
Backup of WAL archives (even with archive mode=on & archive_command configured)No
Works without archive_modeYes
  • 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 FlagDescription
-U <username>Specifies the PostgreSQL user to connect as.
-D <directory>Specifies the target directory for the backup.
-F pTakes the backup in Plain format (file system copy).
-F tTakes the backup in Tar archive format.
-zCompresses 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 sIncludes the Write-Ahead Log (WAL) files by copying the WAL segment files.
-X streamStreams the WAL files while taking the backup for continuous consistency.
-PShows 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:

  1. Stop PostgreSQL (if running)
  2. Copy or extract the backup into a clean data directory (PGDATA)
  3. Place a file named recovery.signal in the data directory
  4. 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:

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

What is recovery.signal ?

FilePurpose
recovery.signalTells 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.

OperationKey Point
BackupFile-level copy using replication protocol
RestorePlace files into PGDATA, handle symlinks, configs, and versions
RecoveryTriggered by recovery.signal, replays WAL, auto-removes signal file

 

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/

Change PostgreSQL WAL Directory Path (pg_wal)

PostgreSQL, , , , , , , , , , , , , , , ,

Table of Contents

0. Aim
1. Verify Existing pg_wal Directory
2. Create the New Directory
3. Stop PostgreSQL Service
4. Copy WAL Files to New Location
5. Backup Old WAL Directory
6. Create Symlink
7. Fix Permissions
8. Start PostgreSQL Service
9. Verify WAL Functionality
10. Remove Old WAL Directory (Optional)

0. Aim

To change the PostgreSQL 17 WAL File directory from its default location to new mount point

From : /var/lib/pgsql/17/data

TO : /pgData/pgsql17/data

1. Verify Existing pg_wal directory


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

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

postgres=# exit
[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgData/pgsql17/data/pg_wal
total 48M
drwx------. 2 postgres postgres   6 Sep 30 21:50 summaries
-rw-------. 1 postgres postgres 16M Oct  8 04:24 000000010000000000000008
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000007
drwx------. 2 postgres postgres  43 Oct  8 16:08 archive_status
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ du -sh /pgData/pgsql17/data/pg_wal
48M     /pgData/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv01 ~]$

2. Create the New Directory on a new disk

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

3. Stop PostgreSQL Service

[root@lxicbpgdsgv01 ~]# systemctl stop postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# ps -ef | grep postgres
root        6887    6721  0 16:08 pts/0    00:00:00 grep --color=auto postgres
[root@lxicbpgdsgv01 ~]#

4. Copy Existing WAL Files to New Location

[postgres@lxicbpgdsgv01 ~]$ nohup rsync -avh --progress /pgData/pgsql17/data/pg_wal/ /pgWal/pgsql17/wal/ > rsync_pgwal.log 2>&1 &
[1] 6943
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$
[1]+  Done                    nohup rsync -avh --progress /pgData/pgsql17/data/pg_wal/ /pgWal/pgsql17/wal/ > rsync_pgwal.log 2>&1
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ cat rsync_pgwal.log
nohup: ignoring input
sending incremental file list
./
000000010000000000000006
         16.78M 100%   81.89MB/s    0:00:00 (xfr#1, to-chk=5/7)
000000010000000000000007
         16.78M 100%   43.84MB/s    0:00:00 (xfr#2, to-chk=4/7)
000000010000000000000008
         16.78M 100%   31.07MB/s    0:00:00 (xfr#3, to-chk=3/7)
archive_status/
archive_status/000000010000000000000006.done
              0 100%    0.00kB/s    0:00:00 (xfr#4, to-chk=0/7)
summaries/

sent 50.34M bytes  received 107 bytes  33.56M bytes/sec
total size is 50.33M  speedup is 1.00
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgWal/pgsql17/wal/
total 48M
drwx------. 2 postgres postgres   6 Sep 30 21:50 summaries
-rw-------. 1 postgres postgres 16M Oct  8 04:24 000000010000000000000008
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000007
drwx------. 2 postgres postgres  43 Oct  8 16:08 archive_status
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ du -sh /pgWal/pgsql17/wal/
48M     /pgWal/pgsql17/wal/
[postgres@lxicbpgdsgv01 ~]$

5. Move old directory as backup

[postgres@lxicbpgdsgv01 ~]$ mv /pgData/pgsql17/data/pg_wal /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal
ls: cannot access '/pgData/pgsql17/data/pg_wal': No such file or directory
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal.bak
drwx------. 4 postgres postgres 141 Oct  8 16:08 /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$

6. Create symlink

[postgres@lxicbpgdsgv01 ~]$ ln -s /pgWal/pgsql17/wal /pgData/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgData/pgsql17/data/pg_wal
lrwxrwxrwx. 1 postgres postgres 18 Oct  8 16:16 /pgData/pgsql17/data/pg_wal -> /pgWal/pgsql17/wal
[postgres@lxicbpgdsgv01 ~]$

7. Fix permissions (if required)

[postgres@lxicbpgdsgv01 ~]$ chown -R postgres:postgres /pgWal/pgsql17/wal

8. Start PostgreSQL Service

[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 Wed 2025-10-08 16:20:46 +08; 7s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 7079 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 7084 (postgres)
      Tasks: 8 (limit: 15835)
     Memory: 18.1M
        CPU: 94ms
     CGroup: /system.slice/postgresql-17.service
             ├─7084 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─7086 "postgres: logger "
             ├─7087 "postgres: checkpointer "
             ├─7088 "postgres: background writer "
             ├─7090 "postgres: walwriter "
             ├─7091 "postgres: autovacuum launcher "
             ├─7092 "postgres: archiver "
             └─7093 "postgres: logical replication launcher "

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

9. Verify

-- Load WAL File generation (Testing)

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  /pgWal/pgsql17/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 ~]$

10. Remove Old WAL Directory (Optional, later)

[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal.bak
drwx------. 4 postgres postgres 141 Oct  8 16:08 /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$ rm -rf /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$

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/

WAL Files

PostgreSQL, , , , , , , , , , , , ,

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

AspectOraclePostgreSQL
Redo Log FilesCalled Redo Log FilesCalled WAL Files
Archive Log FilesCalled Archive Log FilesCalled WAL Archives
Redo Log File Size~200MB (default)16MB (default and fixed)
Redo Log Files CountMinimum 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 StorageRedo log files stored in redo log groupsWAL files stored in pg_wal directory
Archiving MechanismArchive logs created if archiving is enabledArchived via archive_command if archive_mode = on
Overwrite BehaviorRedo logs are overwritten when full (if archive not enabled)WAL segments are recycled/overwritten when full (if archive_mode is off)
Force Switch BehaviorALTER SYSTEM SWITCH LOGFILE;SELECT pg_switch_wal();

PostgreSQL WAL Configuration Parameters

ParameterMeaningDefault
wal_segment_sizeSize of each WAL file16MB
min_wal_sizeMinimum total WAL size retained80MB (5 WAL files)
Common in production: 8GB
max_wal_sizeMax WAL before triggering a checkpoint1GB
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

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/