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