I have tried to document step by step How to Perform PostgreSQL Backup and Restore Operations with with pg_basebackup. I hope it will be useful for those who need it!
High Level Installation and Configuration Steps
1. General Steps
1.1. Environment Overview
1.2. Set Hostnames on Both Servers
1.3. Update /etc/hosts Files on Both Servers
1.4. Disable Firewall on Both Servers
1.5. Install PostgreSQL 16 on Both Servers
1.6. Enable WAL Archiving (Required for PITR & Consistent Backups) on Primary Server
2. Full Restore Test Steps
2.1. Create Sample Database and Data on Primary Server
2.2. Take a Base Backup (with WAL, Timestamp, and Compression) on Primary Server
2.3. Transfer Backup and WALs to Restore Server from Primary Server
2.4. Prepare the Environment on Restore Server
2.5. Extract and Restore the Backup on Restore Server
2.6. Verify Data After Restore on Restore Server
3. Point-in-Time Recovery (PITR) Test (Simulated Data Deletion) Steps
3.1. Create Sample Database and Data on Primary Server
3.2. Take a Base Backup (with WAL, Timestamp, and Compression) on Primary Server
3.3. Add Another Database and Data After Backup on Primary Server
3.4. Record Current Time (Before Data Loss) on Primary Server
3.5. Simulate Accidental Data Deletion on Primary Server
3.6. Ensure All WALs Are Archived on Primary Server
3.7. Transfer Backup and WALs to Restore Server from Primary Server
3.8. Prepare the Restore Server Environment
3.9. Extract and Restore the Backup on Restore Server
3.10. Restore Archived WALs on Restore Server
3.11. Update Config on Restore Server
3.12. Create recovery.signal on Restore Server
3.13. Start PostgreSQL and Monitor Recovery on Restore Server
3.14. Verify PITR Result (Data Restored) on Restore Server
3.15. Cleanup on Restore Server (Optional)
1. General Steps
1.1. Environment Overview
1.2. Set Hostnames on Both Servers
192.168.1.151 -> sudo hostnamectl set-hostname postgres16-primary
192.168.1.152 -> sudo hostnamectl set-hostname postgres16-restoretest1.3. Update /etc/hosts Files on Both Servers
vi /etc/hosts
192.168.1.151 postgres16-primary
192.168.1.152 postgres16-restoretest1.4. Disable Firewall on Both Servers
systemctl status ufw
systemctl stop ufw
sudo systemctl disable ufw
sudo systemctl mask ufw
systemctl status ufw1.5. Install PostgreSQL 16 on Both Servers
Install PostgreSQL Package Tools
# This package provides tools required to manage PostgreSQL versions and to install the official PostgreSQL APT repository.
sudo apt update
sudo apt install -y postgresql-commonAdd the Official PostgreSQL APT Repository
# This script automatically adds the PostgreSQL Global Development Group (PGDG) APT repository to your
# system, enabling access to the latest PostgreSQL versions — including PostgreSQL 16.
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.shUpdate Package List and Install PostgreSQL 16 with Additional Modules
# postgresql-16: The core PostgreSQL 16 server package.
# postgresql-contrib-16: Adds useful extensions like pg_stat_statements, uuid-ossp, and more.
sudo apt update
sudo apt install -y postgresql-16 postgresql-contrib-16
psql --version
1.6. Enable WAL Archiving (Required for PITR & Consistent Backups) on Primary Server
# Backup PostgreSQL config before editing:
sudo cp -p /etc/postgresql/16/main/postgresql.conf /etc/postgresql/16/main/postgresql.conf_original
# Edit PostgreSQL config:
sudo vi /etc/postgresql/16/main/postgresql.conf
# Add or modify:
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
wal_level = replica
# Create WAL archive directory:
sudo mkdir -p /var/lib/postgresql/wal_archive
sudo chown postgres:postgres /var/lib/postgresql/wal_archive
sudo chmod 700 /var/lib/postgresql/wal_archive
# Restart PostgreSQL:
sudo systemctl restart postgresql@16-main
# Check PostgreSQL:
sudo systemctl status postgresql@16-main
# Verify settings:
sudo -u postgres psql -c "SHOW archive_mode;"
sudo -u postgres psql -c "SHOW archive_command;"
sudo -u postgres psql -c "SHOW wal_level;"
# Explanation:
Each time a new WAL file is completed, PostgreSQL executes:
cp %p /var/lib/postgresql/wal_archive/%f
%p → the full path of the source WAL file
%f → the file name (e.g., 0000000100000000000000A1)
cp ... → copies the file to the specified directory
In short:
Every time a new WAL file is created, PostgreSQL copies it to the directory /var/lib/postgresql/wal_archive/.
This is required for point-in-time recovery (PITR) and backup purposes.
2. Full Restore Test Steps
2.1. Create Sample Database and Data on Primary Server
##### Steps Used When Repeating Tests #####
sudo -u postgres psql
\l
drop database restore_test;
drop database pitr_test;
drop database pitr_test_2;
\l
##### Steps Used When Repeating Tests #####
sudo -u postgres psql
CREATE DATABASE restore_test;
\c restore_test
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT,
department TEXT,
salary NUMERIC
);
INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Finance', 6500),
('Bob', 'IT', 7200),
('Charlie', 'HR', 5500);
SELECT * FROM employees;
\q
Expected:
id | name | department | salary
----+----------+-------------+--------
1 | Alice | Finance | 6500
2 | Bob | IT | 7200
3 | Charlie | HR | 5500
2.2. Take a Base Backup (with WAL, Timestamp, and Compression) on Primary Server
##### Steps Used When Repeating Tests #####
# Remove Older Backups If Exists
ls -lrt /backup/
rm -f /backup/*
ls -lrt /backup/
##### Steps Used When Repeating Tests #####
# Define backup location and timestamp:
BACKUP_DIR="/backup"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
FULL_BACKUP_PATH="$BACKUP_DIR/basebackup_$TIMESTAMP"
# Create backup directory:
sudo mkdir -p "$FULL_BACKUP_PATH"
sudo chown postgres:postgres "$FULL_BACKUP_PATH"
# Take the base backup:
# Includes all required WAL files for consistency.
sudo -u postgres pg_basebackup \
-D "$FULL_BACKUP_PATH" \
-Fp \
-Xs \
-P \
-v \
-h localhost \
-p 5432 \
-U postgres
# Compress backup:
cd "$BACKUP_DIR"
sudo tar -czvf "basebackup_$TIMESTAMP.tar.gz" "basebackup_$TIMESTAMP"
sudo rm -rf "basebackup_$TIMESTAMP"
# Check Resulting file:
pwd
ls -lrt
basebackup_20251026_125856.tar.gz
2.3. Transfer Backup and WALs to Restore Server from Primary Server
##### Steps Used When Repeating Tests #####
# basebackup_20251026_125856.tar.gz
sudo -u postgres scp /backup/basebackup_20251026_125856.tar.gz postgres16-restoretest:/backup/
# Remove Older wal_archive directory (if exists) from Restore Server
ls -lrt /var/lib/postgresql/wal_archive
rm -rf /var/lib/postgresql/wal_archive
ls -lrt /var/lib/postgresql/wal_archive
sudo -u postgres scp -r /var/lib/postgresql/wal_archive postgres16-restoretest:/var/lib/postgresql/
##### Steps Used When Repeating Tests #####
# Create backup directory on Restore Server:
sudo mkdir -p /backup
sudo chown postgres:postgres /backup/
# Remove Older wal_archive directory (if exists) from Restore Server
ls -lrt /var/lib/postgresql/wal_archive
rm -rf /var/lib/postgresql/wal_archive
ls -lrt /var/lib/postgresql/wal_archive
# Remove Older backup files (if exists) from Restore Server
ls -lrt /backup
rm -rf /backup/*
ls -lrt /backup
# Using scp: (change basebackup_20251026_125856.tar.gz name with yours)
sudo -u postgres scp /backup/basebackup_20251026_125856.tar.gz postgres16-restoretest:/backup/
sudo -u postgres scp -r /var/lib/postgresql/wal_archive postgres16-restoretest:/var/lib/postgresql/
2.4. Prepare the Environment on Restore Server
##### Steps Used When Repeating Tests #####
ls -lrt /var/lib/postgresql/16/
rm -rf /var/lib/postgresql/16/main_old
ls -lrt /var/lib/postgresql/16/
##### Steps Used When Repeating Tests #####
# Check PostgreSQL:
sudo systemctl status postgresql@16-main
# Stop PostgreSQL:
sudo systemctl stop postgresql@16-main
# Clean or rename old data directory:
sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main_old
# Check data directory:
ls -lrt /var/lib/postgresql/16/
2.5. Extract and Restore the Backup on Restore Server
##### Steps Used When Repeating Tests #####
# basebackup_20251026_125856.tar.gz
cd /backup
ls -lrt
sudo tar -xzvf basebackup_20251026_125856.tar.gz -C /backup
sudo cp -R /backup/basebackup_20251026_125856 /var/lib/postgresql/16/main
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
sudo chmod 700 /var/lib/postgresql/16/main
ls -lrt /var/lib/postgresql/16/main
##### Steps Used When Repeating Tests #####
# Uncompress the backup (change basebackup_20251026_125856.tar.gz name with yours)
cd /backup
ls -lrt
sudo tar -xzvf basebackup_20251026_125856.tar.gz -C /backup
# Copy data into PostgreSQL data directory (change basebackup_20251026_125856.tar.gz name with yours)
sudo cp -R /backup/basebackup_20251026_125856 /var/lib/postgresql/16/main
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
sudo chmod 700 /var/lib/postgresql/16/main
ls -lrt /var/lib/postgresql/16/main
2.6. Verify Data After Restore on Restore Server
# Start PostgreSQL to ensure the base backup itself is consistent:
sudo systemctl start postgresql@16-main
# Check status:
sudo systemctl status postgresql@16-main
# Then connect and verify:
sudo -u postgres psql
\l
\c restore_test
SELECT * FROM employees;
\q
# Expected output:
id | name | department | salary
----+----------+-------------+--------
1 | Alice | Finance | 6500
2 | Bob | IT | 7200
3 | Charlie | HR | 5500
# If everything looks correct, stop PostgreSQL again before proceeding with PITR:
sudo systemctl stop postgresql@16-main
3. Point-in-Time Recovery (PITR) Test (Simulated Data Deletion) Steps
3.1. Create Sample Database and Data on Primary Server
##### Steps Used When Repeating Tests #####
sudo -u postgres psql
\l
drop database restore_test;
drop database pitr_test;
drop database pitr_test_2;
\l
##### Steps Used When Repeating Tests #####
sudo -u postgres psql
CREATE DATABASE pitr_test;
\c pitr_test
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT,
department TEXT,
salary NUMERIC
);
INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Finance', 6500),
('Bob', 'IT', 7200),
('Charlie', 'HR', 5500);
SELECT * FROM employees;
\q
Expected:
id | name | department | salary
----+----------+-------------+--------
1 | Alice | Finance | 6500
2 | Bob | IT | 7200
3 | Charlie | HR | 5500
3.2. Take a Base Backup (with WAL, Timestamp, and Compression) on Primary Server
##### Steps Used When Repeating Tests #####
# Remove Older Backups If Exists
ls -lrt /backup/
rm -f /backup/*
ls -lrt /backup/
##### Steps Used When Repeating Tests #####
# Define backup location and timestamp:
BACKUP_DIR="/backup"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
FULL_BACKUP_PATH="$BACKUP_DIR/basebackup_$TIMESTAMP"
# Create backup directory:
sudo mkdir -p "$FULL_BACKUP_PATH"
sudo chown postgres:postgres "$FULL_BACKUP_PATH"
# Take the base backup:
# Includes all required WAL files for consistency.
sudo -u postgres pg_basebackup \
-D "$FULL_BACKUP_PATH" \
-Fp \
-Xs \
-P \
-v \
-h localhost \
-p 5432 \
-U postgres
# Compress backup:
cd "$BACKUP_DIR"
sudo tar -czvf "basebackup_$TIMESTAMP.tar.gz" "basebackup_$TIMESTAMP"
sudo rm -rf "basebackup_$TIMESTAMP"
# Check Resulting file:
pwd
ls -lrt
basebackup_20251026_142852.tar.gz
3.3. Add Another Database and Data After Backup on Primary Server
sudo -u postgres psql
CREATE DATABASE pitr_test_2;
\c pitr_test_2
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT,
department TEXT,
salary NUMERIC
);
INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Finance', 6500),
('Bob', 'IT', 7200),
('Charlie', 'HR', 5500);
SELECT * FROM employees;
SELECT pg_switch_wal();
\q
Expected:
id | name | department | salary
----+----------+-------------+--------
1 | Alice | Finance | 6500
2 | Bob | IT | 7200
3 | Charlie | HR | 5500
3.4. Record Current Time (Before Data Loss) on Primary Server
# On Primary Server:
sudo -u postgres psql -d pitr_test_2 -c "SELECT now();"
Example:
root@postgres16-primary:~# sudo -u postgres psql -d pitr_test_2 -c "SELECT now();"
now
------------------------------
2025-10-26 14:33:47.85731+03
(1 row)
# This is our recovery target time:
recovery_target_time = '2025-10-26 14:33:47'
3.5. Simulate Accidental Data Deletion on Primary Server
# Check:
sudo -u postgres psql -d pitr_test_2 -c "SELECT * FROM employees;"
# Delete
sudo -u postgres psql -d pitr_test_2 -c "DELETE FROM employees WHERE name = 'Charlie';"
# Check:
sudo -u postgres psql -d pitr_test_2 -c "SELECT * FROM employees;"
# Output now shows data loss:
id | name | department | salary
----+----------+-------------+--------
1 | Alice | Finance | 6500
2 | Bob | IT | 7200
-- Charlie row missing --
3.6. Ensure All WALs Are Archived on Primary Server
Ensure all WALs exist in wal_archive directory on Primary Server. It is so important. Otherwise you can not see all changes after database recovery.
# Ensure All WALs exist in wal_archive directory on Primary Server
# It is so important. Otherwise you can not see all changes after database recovery
ls -lrt /var/lib/postgresql/16/main/pg_wal
ls -lrt /var/lib/postgresql/wal_archive | tail -10
3.7. Transfer Backup and WALs to Restore Server from Primary Server
##### Steps Used When Repeating Tests #####
# basebackup_20251026_142852.tar.gz
sudo -u postgres scp /backup/basebackup_20251026_142852.tar.gz postgres16-restoretest:/backup/
# Remove Older wal_archive directory (if exists) from Restore Server
ls -lrt /var/lib/postgresql/wal_archive
rm -rf /var/lib/postgresql/wal_archive
ls -lrt /var/lib/postgresql/wal_archive
sudo -u postgres scp -r /var/lib/postgresql/wal_archive postgres16-restoretest:/var/lib/postgresql/
##### Steps Used When Repeating Tests #####
# Create backup directory on Restore Server:
sudo mkdir -p /backup
sudo chown postgres:postgres /backup/
# Remove Older wal_archive directory (if exists) from Restore Server
ls -lrt /var/lib/postgresql/wal_archive
rm -rf /var/lib/postgresql/wal_archive
ls -lrt /var/lib/postgresql/wal_archive
# Remove Older backup files (if exists) from Restore Server
ls -lrt /backup
rm -rf /backup/*
ls -lrt /backup
# Using scp: (change basebackup_20251026_142852.tar.gz name with yours)
sudo -u postgres scp /backup/basebackup_20251026_142852.tar.gz postgres16-restoretest:/backup/
sudo -u postgres scp -r /var/lib/postgresql/wal_archive postgres16-restoretest:/var/lib/postgresql/
3.8. Prepare the Restore Server Environment
##### Steps Used When Repeating Tests #####
ls -lrt /var/lib/postgresql/16/
rm -rf /var/lib/postgresql/16/main_old
ls -lrt /var/lib/postgresql/16/
##### Steps Used When Repeating Tests #####
# Check PostgreSQL:
sudo systemctl status postgresql@16-main
# Stop PostgreSQL:
sudo systemctl stop postgresql@16-main
# Clean or rename old data directory:
ls -lrt /var/lib/postgresql/16/
rm -rf /var/lib/postgresql/16/main_old
sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main_old
# Check data directory:
ls -lrt /var/lib/postgresql/16/
3.9. Extract and Restore the Backup on Restore Server
##### Steps Used When Repeating Tests #####
# basebackup_20251026_142852.tar.gz
cd /backup
ls -lrt
sudo tar -xzvf basebackup_20251026_142852.tar.gz -C /backup
sudo cp -R /backup/basebackup_20251026_142852 /var/lib/postgresql/16/main
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
sudo chmod 700 /var/lib/postgresql/16/main
ls -lrt /var/lib/postgresql/16/main
##### Steps Used When Repeating Tests #####
# Uncompress the backup (change basebackup_20251026_142852.tar.gz name with yours)
cd /backup
ls -lrt
sudo tar -xzvf basebackup_20251026_142852.tar.gz -C /backup
# Copy data into PostgreSQL data directory:
sudo cp -R /backup/basebackup_20251026_142852 /var/lib/postgresql/16/main
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
sudo chmod 700 /var/lib/postgresql/16/main
ls -lrt /var/lib/postgresql/16/main
3.10. Restore Archived WALs on Restore Server
# Delete Older WALs on Restore Server
ls -lrt /var/lib/postgresql/16/main/pg_wal/
rm -f /var/lib/postgresql/16/main/pg_wal/0*
ls -lrt /var/lib/postgresql/16/main/pg_wal/
# Copy Archived WALs to pg_wal Directory
ls -lrt /var/lib/postgresql/wal_archive
ls -lrt /var/lib/postgresql/16/main/pg_wal/
sudo -u postgres cp /var/lib/postgresql/wal_archive/* /var/lib/postgresql/16/main/pg_wal/
ls -lrt /var/lib/postgresql/16/main/pg_wal/3.11. Update Config on Restore Server
# Check recovery_target_time from "Record Current Time (Before Data Loss) on Primary Server" Section
# recovery_target_time = '2025-10-26 14:33:47'
sudo vi /etc/postgresql/16/main/postgresql.conf
# Add or modify:
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2025-10-26 14:33:47'
# Verify settings:
more /etc/postgresql/16/main/postgresql.conf | grep restore_command
more /etc/postgresql/16/main/postgresql.conf | grep recovery_target_time | grep -v recovery_target_timeline
3.12. Create recovery.signal on Restore Server
# recovery.signal must be owned by postgres user.
ls -lrt /var/lib/postgresql/16/main/recovery.signal
sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal
ls -lrt /var/lib/postgresql/16/main/recovery.signal
3.13. Start PostgreSQL and Monitor Recovery on Restore Server
# Start PostgreSQL to ensure the base backup itself is consistent:
sudo systemctl start postgresql@16-main
# Check status:
sudo systemctl status postgresql@16-main
# Exit Read-Only Mode
sudo -u postgres psql
SELECT pg_wal_replay_resume();
# Monitor the logs:
sudo tail -20f /var/log/postgresql/postgresql-16-main.log
# Watch for:
database system is ready to accept connections
3.14. Verify PITR Result (Data Restored) on Restore Server
# Then connect and verify:
sudo -u postgres psql
\l
\c pitr_test
SELECT * FROM employees;
# Expected output:
id | name | department | salary
----+----------+-------------+--------
1 | Alice | Finance | 6500
2 | Bob | IT | 7200
3 | Charlie | HR | 5500
\c pitr_test_2
SELECT * FROM employees;
✅ Expected (Charlie restored!):
id | name | department | salary
----+----------+-------------+--------
1 | Alice | Finance | 6500
2 | Bob | IT | 7200
3 | Charlie | HR | 5500
3.15. Cleanup on Restore Server (Optional)
ls -lrt /var/lib/postgresql/16/
sudo rm -rf /var/lib/postgresql/16/main_old
ls -lrt /var/lib/postgresql/16/
SERHAT CELIK DATABASE BLOG 