Tag Archives: pg_basebackup restore

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/

Clone PostgreSQL Cluster from One Host to Another Using pg_basebackup (No Archive Mode)

How To Backup and Restore PostgreSQL DB Cluster from One Host to Another Using pg_basebackup (No Archive Mode)

1. Goal
2. Environment (Source / Target)
3. Verify Existing Setup
4. Verify postgresql.conf
5. Verify pg_hba.conf
6. Verify Users
7. Directory Permissions
8. Take Backup
9. Verify Backup
10. Transfer Backup to Target
11. Prepare Target & Restore

11.1 Backup Target (Optional)
11.2 Stop PostgreSQL and Clean Directories
11.3 Restore Data and WAL
11.4 Set Permissions
11.5 Verify Restored Files
11.6 Create Symbolic Link for WAL

12. Start PostgreSQL on Target
13. Final Verification

In PostgreSQL, a cluster is a group of databases managed by one PostgreSQL server. This is just a standalone server, not like an Oracle RAC cluster.

1. Goal

1. Perform a consistent backup using pg_basebackup from lxicbpgdsgv01.
2. Restore the backup on lxicbpgdsgv02 without applying archived WALs.

2. Environment (Source / Target)

Aspect	Source	Target	Difference
Hostname	lxicbpgdsgv01	lxicbpgdsgv02	Different hostnames
IP Address	192.168.2.51	192.168.2.52	Different IPs
OS	RHEL 9	RHEL 9	Same
DB Version	PostgreSQL v17.6	PostgreSQL v17.6	Same
Archive mode	No Archivelog	No Archivelog	Same
PGDATA	/pgData/pgsql17/data	/pgdata/pgsql17/data	Different path case (D vs d)
WAL Directory	/pgWal/pgsql17/wal	/pgwal/pgsql17/wal	Different path case (W vs w)
Tablespace	pg_default	pg_default	Same
Databases	DELL, ORCL	No Databases	Need to clone

3. Verify Existing 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=#

postgres=# \c dell
You are now connected to database "dell" as user "postgres".
dell=#

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

dell=# 
dell=# \dn test
 List of schemas
 Name |  Owner
------+----------
 test | postgres
(1 row)

dell=#
dell=# \echo :AUTOCOMMIT
on
dell=#

dell=# CREATE TABLE test.emp ( name TEXT, designation TEXT, project TEXT, company TEXT);
CREATE TABLE
dell=# INSERT INTO test.emp VALUES ('Sugi', 'DBA', 'Jetstar', 'iGATE');
INSERT 0 1
dell=# INSERT INTO test.emp VALUES ('Teja', 'DBA', 'RCM', 'iGATE');
INSERT 0 1
dell=# INSERT INTO test.emp VALUES ('RAJ', 'DBA', 'RCM', 'iGATE');
INSERT 0 1
dell=#
dell=# select * from test.emp;
 name | designation | project | company
------+-------------+---------+---------
 Sugi | DBA         | Jetstar | iGATE
 Teja | DBA         | RCM     | iGATE
 RAJ  | DBA         | RCM     | iGATE
(3 rows)

dell=#

dell=# \c orcl
You are now connected to database "orcl" as user "postgres".
orcl=#
orcl=# \dt
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | sample_data | table | postgres
(1 row)

orcl=#
orcl=# select count(*) from sample_data;
 count
-------
  1000  <----- 
(1 row)

orcl=#

4. Verify postgresql.conf


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

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

postgres=# SHOW archive_mode;
 archive_mode
--------------
 off  <----- No archive log mode
(1 row)

postgres=# show archive_command;
 archive_command
-----------------
 (disabled)
(1 row)

postgres=#

5. 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

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

7. Directory Permissions for Backup

[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgBackup/pgsql17/backup/
drwx------. 2 postgres postgres 6 Oct  9 18:46 /pgBackup/pgsql17/backup/
[postgres@lxicbpgdsgv01 ~]$

8. Take Backup

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).

[postgres@lxicbpgdsgv01 ~]$ nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/pg_basebackup_lxicbpgdsgv01_10sep2025 -Ft -Xs -P > /pgBackup/pgsql17/backup/pg_basebackup_lxicbpgdsgv01_10sep2025.log 2>&1 &
[1] 4438
[postgres@lxicbpgdsgv01 ~]$

**** I have forgot use -v (verbose option to get extra output)
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/pg_basebackup_lxicbpgdsgv01_10sep2025 -Ft -Xs -P -v > /pgBackup/pgsql17/backup/pg_basebackup_lxicbpgdsgv01_10sep2025.log 2>&1 &

9. Verify Backup


[postgres@lxicbpgdsgv01 ~]$ cat /pgBackup/pgsql17/backup/pg_basebackup_lxicbpgdsgv01_10sep2025.log
nohup: ignoring input
waiting for checkpoint
133816/536026 kB (24%), 0/1 tablespace
299960/536026 kB (55%), 0/1 tablespace
447864/536026 kB (83%), 0/1 tablespace
536037/536037 kB (100%), 0/1 tablespace
536037/536037 kB (100%), 1/1 tablespace
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgBackup/pgsql17/backup/pg_basebackup_lxicbpgdsgv01_10sep2025
total 540M
-rw-------. 1 postgres postgres 524M Oct  9 18:56 base.tar  
-rw-------. 1 postgres postgres 223K Oct  9 18:56 backup_manifest
-rw-------. 1 postgres postgres  17M Oct  9 18:56 pg_wal.tar 
[postgres@lxicbpgdsgv01 ~]$

10. Transfer Backup to Target


[postgres@lxicbpgdsgv01 pg_basebackup_lxicbpgdsgv01_10sep2025]$ scp * 192.168.2.52:/pgbackup/pgsql17/backup/
postgres@192.168.2.52's password:  
backup_manifest                   100%  222KB  30.7MB/s   00:00
base.tar                          100%  523MB  74.0MB/s   00:07
pg_wal.tar                        100%   16MB  43.5MB/s   00:00
[postgres@lxicbpgdsgv01 pg_basebackup_lxicbpgdsgv01_10sep2025]$

11. Prepare Target & Restore

-- List Source Backup files 
[postgres@lxicbpgdsgv02 ~]$ ls -lrth /pgbackup/pgsql17/backup
total 540M
-rw-------. 1 postgres postgres 223K Oct  9 19:08 backup_manifest
-rw-------. 1 postgres postgres 524M Oct  9 19:08 base.tar
-rw-------. 1 postgres postgres  17M Oct  9 19:08 pg_wal.tar
[postgres@lxicbpgdsgv02 ~]$

11.1 Backup Target (Optional)


nohup pg_basebackup -U postgres -D /pgbackup/pgsql17/pg_basebackup_lxicbpgdsgv02_10sep2025 -Ft -Xs -P > /pgbackup/pgsql17/pg_basebackup_lxicbpgdsgv02_10sep2025.log 2>&1 &

-- Verify Tablespaces path -- It's pg_default in our case
-- Verify WAL File (Redo log files) location 

[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data/pg_wal
lrwxrwxrwx. 1 postgres postgres 18 Oct  9 17:37 /pgdata/pgsql17/data/pg_wal -> /pgwal/pgsql17/wal
[postgres@lxicbpgdsgv02 ~]$

11.2 Stop PostgreSQL and Clean Directories

-- Stop PostgreSQL DB Cluster
[root@lxicbpgdsgv02 ~]# systemctl stop postgresql-17.service
[root@lxicbpgdsgv02 ~]#
[root@lxicbpgdsgv02 ~]# ps -ef | grep postgres
root       10259    7832  0 19:20 pts/0    00:00:00 grep --color=auto postgres
[root@lxicbpgdsgv02 ~]#

-- Remove all from Data directory 

[postgres@lxicbpgdsgv02 ~]$ rm -rf /pgdata/pgsql17/data/*
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data/
total 0
[postgres@lxicbpgdsgv02 ~]$


-- Remove all from WAL directory 

[postgres@lxicbpgdsgv02 ~]$ rm -rf /pgwal/pgsql17/wal/*
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgwal/pgsql17/wal/
total 0
[postgres@lxicbpgdsgv02 ~]$

11.3 Restore Data and WAL

-- List source backup files

[postgres@lxicbpgdsgv02 ~]$ cd /pgbackup/pgsql17/backup/
[postgres@lxicbpgdsgv02 backup]$ ls -ltr
total 552652
-rw-------. 1 postgres postgres    227724 Oct  9 19:08 backup_manifest
-rw-------. 1 postgres postgres 548901888 Oct  9 19:08 base.tar
-rw-------. 1 postgres postgres  16778752 Oct  9 19:08 pg_wal.tar
drwxr-xr-x. 3 postgres postgres        18 Oct  9 19:32 data_lxicbpgdsgv02_old
[postgres@lxicbpgdsgv02 backup]$

# Restore DATA Directory 
[postgres@lxicbpgdsgv02 ~]$ nohup tar -xvf /pgbackup/pgsql17/backup/base.tar -C /pgdata/pgsql17/data > /pgbackup/pgsql17/backup/base_restore.log 2>&1 &
[1] 11289
[postgres@lxicbpgdsgv02 ~]$


# Restore WAL Directory 

[postgres@lxicbpgdsgv02 ~]$ nohup tar -xvf /pgbackup/pgsql17/backup/pg_wal.tar -C /pgwal/pgsql17/wal > /pgbackup/pgsql17/backup/pg_wal_restore.log 2>&1 &
[1] 11304
[postgres@lxicbpgdsgv02 ~]$

-- OR -- 

# Restore base and WAL archives sequentially in a single command

nohup bash -c "tar -xvf /pgbackup/pgsql17/backup/base.tar -C /pgdata/pgsql17/data && tar -xvf /pgbackup/pgsql17/backup/pg_wal.tar -C /pgwal/pgsql17/wal" > /pgbackup/pgsql17/backup/fulltar_restore.log 2>&1 &

11.4 Set Permissions

[postgres@lxicbpgdsgv02 ~]$ chown -R postgres:postgres /pgdata/pgsql17/data
[postgres@lxicbpgdsgv02 ~]$ chmod 700 /pgdata/pgsql17/data
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ chown -R postgres:postgres /pgwal/pgsql17/wal
[postgres@lxicbpgdsgv02 ~]$ chmod 700 /pgwal/pgsql17/wal
[postgres@lxicbpgdsgv02 ~]$

11.5 Verify Restored Files

[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data
total 72
-rw-------. 1 postgres postgres    88 Sep 30 21:50 postgresql.auto.conf
drwx------. 2 postgres postgres    18 Sep 30 21:50 pg_xact
-rw-------. 1 postgres postgres     3 Sep 30 21:50 PG_VERSION
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_twophase
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_tblspc
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_subtrans
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_stat_tmp
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_snapshots
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_serial
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_notify
drwx------. 4 postgres postgres    36 Sep 30 21:50 pg_multixact
-rw-------. 1 postgres postgres  2640 Sep 30 21:50 pg_ident.conf
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_dynshmem
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_commit_ts
-rw-------. 1 postgres postgres 30702 Oct  7 18:20 postgresql.conf.bkp
drwx------. 2 postgres postgres    84 Oct  9 00:01 log
-rw-------. 1 postgres postgres  1169 Oct  9 16:31 postgresql.conf.bkp_10sep2025
-rw-------. 1 postgres postgres  1171 Oct  9 16:32 postgresql.conf
drwx------. 7 postgres postgres    59 Oct  9 18:08 base
drwx------. 2 postgres postgres     6 Oct  9 18:44 pg_stat
-rw-------. 1 postgres postgres    30 Oct  9 18:44 current_logfiles
-rw-------. 1 postgres postgres  5600 Oct  9 18:53 pg_hba.conf
-rw-------. 1 postgres postgres     0 Oct  9 18:56 tablespace_map
drwx------. 2 postgres postgres     6 Oct  9 18:56 pg_replslot
drwx------. 4 postgres postgres    68 Oct  9 18:56 pg_logical
-rw-------. 1 postgres postgres   227 Oct  9 18:56 backup_label
drwx------. 4 postgres postgres    45 Oct  9 20:06 pg_wal <--- Created as Directory, instead of symbolic link 
drwx------. 2 postgres postgres  4096 Oct  9 20:06 global
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgwal/pgsql17/wal
total 16384
-rw-------. 1 postgres postgres 16777216 Oct  9 18:56 000000010000000000000045
[postgres@lxicbpgdsgv02 ~]$

Please note:

--- Want symlinks preserved → use -Fp
--- Want single tar archive → use -Ft, but recreate symlinks after restore, for pg_wal

11.6 Create Symbolic Link for WAL

[postgres@lxicbpgdsgv02 ~]$ ls -ld /pgdata/pgsql17/data/pg_wal
drwx------. 4 postgres postgres 45 Oct  9 20:06 /pgdata/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ rm -rf /pgdata/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv02 ~]$ ln -s /pgwal/pgsql17/wal /pgdata/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ ls -ld /pgdata/pgsql17/data/pg_wal
lrwxrwxrwx. 1 postgres postgres 18 Oct  9 21:04 /pgdata/pgsql17/data/pg_wal -> /pgwal/pgsql17/wal
[postgres@lxicbpgdsgv02 ~]$ 
[postgres@lxicbpgdsgv02 ~]$ cd /pgwal/pgsql17/wal
[postgres@lxicbpgdsgv02 wal]$ ll
total 16384
-rw-------. 1 postgres postgres 16777216 Oct  9 18:56 000000010000000000000045
[postgres@lxicbpgdsgv02 wal]$

12. Start PostgreSQL on Target — no recovery needed.

[root@lxicbpgdsgv02 ~]# systemctl start postgresql-17.service
[root@lxicbpgdsgv02 ~]# 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 21:06:08 +08; 5s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 12375 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 12380 (postgres)
      Tasks: 7 (limit: 20496)
     Memory: 33.6M
        CPU: 114ms
     CGroup: /system.slice/postgresql-17.service
             ├─12380 /usr/pgsql-17/bin/postgres -D /pgdata/pgsql17/data
             ├─12381 "postgres: logger "
             ├─12382 "postgres: checkpointer "
             ├─12383 "postgres: background writer "
             ├─12385 "postgres: walwriter "
             ├─12386 "postgres: autovacuum launcher "
             └─12387 "postgres: logical replication launcher "

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

13. Final Verification

[postgres@lxicbpgdsgv02 ~]$ 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=#
postgres=# \c dell
You are now connected to database "dell" as user "postgres".
dell=# \dt test.*
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test   | emp  | table | postgres
(1 row)

dell=#

dell=# select * from test.emp;  <---- We can see Data 
 name | designation | project | company
------+-------------+---------+---------
 Sugi | DBA         | Jetstar | iGATE
 Teja | DBA         | RCM     | iGATE
 RAJ  | DBA         | RCM     | iGATE
(3 rows)

dell=#
dell=# \c orcl
You are now connected to database "orcl" as user "postgres".
orcl=# \dt
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | sample_data | table | postgres
(1 row)

orcl=#
orcl=# select count(*) from sample_data;
 count
-------
  1000 <----
(1 row)

orcl=#

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

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/

PostgreSQL DBA Step by Step Learning

How To Backup and Restore PostgreSQL DB Cluster from One Host to Another Using pg_basebackup (No Archive Mode)

Table of Contents

In PostgreSQL, a cluster is a group of databases managed by one PostgreSQL server. This is just a standalone server, not like an Oracle RAC cluster.

1. Goal

2. Environment (Source / Target)

3. Verify Existing Setup

4. Verify postgresql.conf

5. Verify pg_hba.conf

6. Verify user permissions:REPLICATION or SUPERUSER required

7. Directory Permissions for Backup

8. Take Backup

9. Verify Backup

10. Transfer Backup to Target

11. Prepare Target & Restore

11.1 Backup Target (Optional)

11.2 Stop PostgreSQL and Clean Directories

11.3 Restore Data and WAL

11.4 Set Permissions

11.5 Verify Restored Files

11.6 Create Symbolic Link for WAL

12. Start PostgreSQL on Target — no recovery needed.

13. Final Verification

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

2. Custom Tablespaces Outside `PGDATA`