postgresql pgbackrest

How to Perform PostgreSQL Backup and Restore Operations with pgBackRest?

Serhat ÇELİK PostgreSQL ,

I have tried to document step by step How to Perform PostgreSQL Backup and Restore Operations with pgBackRest. I hope it will be useful for those who need it!

High Level Installation and Configuration Steps

1-Setup Environment Details
2-Set Hostnames on Each Server
3-Update /etc/hosts Files on All Servers
4-Disable Firewall on All Servers
5-Install PostgreSQL on Primary DB Server & Restore Test Server
6-Install pgBackRest on All Servers
7-Setup Initial Configuration for Backup Server
8-Setup Initial Configuration on Primary DB Server & Restore Test Server
9-Set Passwordless SSH between Primary DB Server & Backup Server
10-Configure Archive Parameters on Primary DB Server
11-Configure the Stanza on Backup Server
12-Try to Take First Full Backup on Backup Server
13-Try to Schedule First Backups on Backup Server
14-Check Backup & Expire Log Files on Backup Server
15-Check Backup & Expire Log Files on Backup Server
16-Check Detail Backup Information on Backup Server
17-Full Backup Restore Steps on Restore Test Server
18-Delta Changes Restore Steps on Restore Test Server
19-Specific Database Restore Steps on Restore Test Server
20-Point-In-Time Recovery (PITR) Steps using Time on Restore Test Server
21-Point-In-Time Recovery (PITR) Steps using LSN on Restore Test Server
22-Multi-Repo Configuration for Backup Redundancy (If Needed)
23-Configure Backup Retention Parameters
24-Expire Backups Manually (If Needed)

Low Level Installation and Configuration Steps

1-Setup Environment Details

2-Set Hostnames on Each Server

192.168.1.151 -> sudo hostnamectl set-hostname postgres-primary
192.168.1.152 -> sudo hostnamectl set-hostname postgres-pgbackrest
192.168.1.153 -> sudo hostnamectl set-hostname postgres-restoretest

3-Update /etc/hosts Files on All Servers

This step should be repeated on below servers.

  • postgres-primary
  • postgres-pgbackrest
  • postgres-restoretest
vi /etc/hosts

192.168.1.151 postgres-primary
192.168.1.152 postgres-pgbackrest
192.168.1.153 postgres-restoretest

4-Disable Firewall on All Servers

This step should be repeated on below servers.

  • postgres-primary
  • postgres-pgbackrest
  • postgres-restoretest
systemctl status ufw
systemctl stop ufw
sudo systemctl disable ufw
sudo systemctl mask ufw
systemctl status ufw

5-Install PostgreSQL on Primary DB Server & Restore Test Server

This step should be repeated on below servers.

  • postgres-primary
  • postgres-restoretest

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-common

Add 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.sh

Update 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

6-Install pgBackRest on All Servers

IMPORTANT NOTE: The pgBackRest version must be the same on all servers, otherwise you will encounter an error.

This step should be repeated on below servers.

  • postgres-primary
  • postgres-pgbackrest
  • postgres-restoretest
sudo apt update
sudo apt-get install pgbackrest
which pgbackrest
pgbackrest version

# To Install Specific pgBackRest Version Follow Below Steps
sudo apt remove pgbackrest (If another version is installed, remove it)
apt-cache policy pgbackrest
sudo apt install pgbackrest=2.50-1build2
pgbackrest version

7-Setup Initial Configuration for Backup Server

# Create a directory in which the backup files will reside and make sure that it is owned by postgres user
sudo mkdir -p /data/backups
sudo chown postgres:postgres /data/backups
 
#Create the log directory for pgBackRest and keep postgres as the owner of this log directory:
sudo mkdir -p 770 /var/log/pgbackrest
sudo chown postgres:postgres /var/log/pgbackrest

#Create a directory where the configuration files of the pgBackRest will reside and set proper permission and owner for the config as well:
sudo mkdir -p /etc/pgbackrest
sudo touch /etc/pgbackrest/pgbackrest.conf 
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
ls -lrt /etc/pgbackrest/pgbackrest.conf
 
#Add below contents in /etc/pgbackrest/pgbackrest.conf
vi /etc/pgbackrest/pgbackrest.conf

[global]
repo1-path=/data/backups
log-level-file=detail
start-fast=y

[demo]
pg1-host=192.168.1.151
pg1-path=/var/lib/postgresql/16/main
pg1-host-user=postgres

# Check the contents of /etc/pgbackrest/pgbackrest.conf
more /etc/pgbackrest/pgbackrest.conf
Explanation of the options:

[global]
These are global settings that apply to all database instances (called stanzas in pgBackRest).

- repo1-path=/data/backups
This defines the path where backups will be stored. It can be a local directory or a mounted remote location.

- log-level-file=detail
Sets the logging level for log files to detail, which provides more in-depth logging (useful for troubleshooting and auditing).

- start-fast=y
Enables a fast start by forcing PostgreSQL to run a checkpoint more aggressively at the beginning of the backup. This speeds up the backup process but may use more resources.


[demo]
This section defines a stanza, named demo, which represents a specific PostgreSQL instance.

-pg1-host=192.168.1.151
The IP address of the primary PostgreSQL server.

-pg1-path=/var/lib/postgresql/16/main
The data directory path of the PostgreSQL instance (typically the default location for PostgreSQL 16).

-pg1-host-user=postgres
The SSH user that pgBackRest will use to connect to the remote server (e.g., ssh postgres@192.168.1.151).

8-Setup Initial Configuration on Primary DB Server & Restore Test Server

This step should be repeated on below servers.

  • postgres-primary
  • postgres-restoretest
#Check PostgreSQL cluster
sudo su - postgres
pg_lsclusters

#Create the log directory for pgBackRest and keep postgres as the owner of this log directory:
exit
sudo mkdir -p 770 /var/log/pgbackrest
sudo chown postgres:postgres /var/log/pgbackrest

#Create a directory where the configuration files of the pgBackRest will reside and set proper permission and owner for the config as well:
sudo mkdir -p /etc/pgbackrest
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
ls -lrt /etc/pgbackrest/pgbackrest.conf

#Add below contents in /etc/pgbackrest/pgbackrest.conf
vi /etc/pgbackrest/pgbackrest.conf

[global]
repo1-host=192.168.1.152
repo1-path=/data/backups
repo1-host-user=postgres
log-level-file=detail
start-fast=y

[demo]
pg1-path=/var/lib/postgresql/16/main
pg1-host-user=postgres

# Check the contents of /etc/pgbackrest/pgbackrest.conf
more /etc/pgbackrest/pgbackrest.conf
Explanation of the options:

[global] Section
This section contains settings that apply globally across all stanzas (like [demo] below).

-repo1-host=192.168.1.152	
IP address of the remote backup server where the backups will be stored.

-repo1-path=/data/backups	
Directory path on the backup server where backups will be saved.

-repo1-host-user=postgres	
Linux user used to connect to the backup server via SSH (usually the same user running PostgreSQL).

-log-level-file=detail	
Sets the logging level for log files. detail means detailed logs will be written.

-start-fast=y	
Allows PostgreSQL to start without waiting for WAL replay during restore. This speeds up restarts.


[demo] Section
This is the stanza definition (a named PostgreSQL instance). You can have multiple stanzas, each representing a different database cluster.

-pg1-path=/var/lib/postgresql/16/main	
Path to the PostgreSQL data directory on the primary server.

-pg1-host-user=postgres	
The Linux user that connects to the PostgreSQL host (typically the postgres user).

9-Set Passwordless SSH between Primary DB Server & Backup Server

Generate and Note SSH Key on Backup Server

#Switch to postgres user on Backup Server
sudo su - postgres
 
#Generate the SSH key on Backup Server
ssh-keygen -t rsa

#Switch to hidden directory under user’s home directory on Backup Server
cd ~/.ssh

# Check the created files. public key with .pub extension and private key without any extension on Backup Server
ls -lrt

# Check and note public key on Backup Server
more id_rsa.pub

postgres@postgres-pgbackrest:~/.ssh$ more id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQC2+0LaXhqf9IdlKtUOfMSt52gDBzm/lVHRCUe+bQUr4R0skWFnQ2aBHXM4Z9z5UE2HaxPVrCtl5yMHMakzz39XSioaO728XBKyuP8nEW9XziYOsLuIy7zmJcA0TAubM9+QaXrIbNI2wY6jesNuz22Bbk2tPRL1eJK7gotGZ4pmB9KFk0gCzrDz2IuvnzFO+f85/KRmlm2HwkN1d9leSqRHdQSg/T6TVoJR87hn2YdVNWSQgTalDDC/h3D5obM0/SVBYiKbxiZXqCPPhICVVk/cXJHyMX5p5G+maHUJ2Yk5D/DfJrRwbfWYmvz3iux7ihExJqRTMRohoWk8/S6RgKMWk+FPNtfKukxf72LcNQVpeNdbwxqU4azrCqjeSAdyZGk6hYCs/X50iC9dMOQQw03XN7YmjTZ3lQfT8RlnjjAcbd6K0tKAQBhAdBW0xV5m53N3iYI/CsDgtSsc9+uqMaaW328raF728xMOF8j9t3vJZRBEgyJdzr6m7irfzLSLqXU= postgres@postgres-pgbackrest

Generate and Note SSH Key on Primary DB Server

#Switch to postgres user on Primary DB Server
sudo su - postgres
 
#Generate the SSH key on Primary DB Server
ssh-keygen -t rsa

#Switch to hidden directory under user’s home directory on Primary DB Server
cd ~/.ssh

# Check the created files. public key with .pub extension and private key without any extension on Primary DB Server
ls -lrt

# Check and note public key on Primary DB Server
more id_rsa.pub

postgres@postgres-primary:~/.ssh$ more id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQCeBnbcCMJHeSCbM8J3QzP4xzCBDciHV2CyrbcoCTLCQLCmGVgcwt8WwV+x/s+LaL3UljxHGE/73D1ZqmF0Yg+KKwN6n1Lj+QXvEesqu3bpIRBt+z8+keh1PA0eOO2g/r9glcC7G1NxBpG01uOU2EoZnR+R67rNvsrMSzBuIwuVqCIhkawIX7PldqGVGJW1LuJF9v7RIQ/HrVR5vXLyY1ctGrPgeErAja6165Vgr0LYy6GMxIoPVPnqtvCVNFQtLkpCOZ/gGm/uLp5fgGq6VDaKWyaTjJjqHVGmvZTCdy25LShZAxMHap0q02WkyyMOw7D0RFSt1My3ObUi6RSuh/CGvngQU9mxzpOz2SIIQYivUMMdh8Dp8tMwnombjECug2ZBOZ3LVzSMFzUeBTBI/2q+3bhnU5X5X7XmjKQLqxPVbMjG6Qcwgu2HG9LKJjc86AKEbSDET+eqqlnuq0meb1fwy0rOPLSjpuk2D1p7IWxySrFlL19AFY1qoThDujR8tbM= postgres@postgres-primary

Create authorized_keys File on Backup Server

If you do not paste the public key as a single line into the authorized_keys file, you will get an error.

#Switch to postgres user on Backup Server
sudo su - postgres

#Switch to hidden directory under user’s home directory on Backup Server
cd ~/.ssh

# Create a file which contains .pub key of the primary db server
# paste the contains from the primary db server of .pub key
vi authorized_keys

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQCeBnbcCMJHeSCbM8J3QzP4xzCBDciHV2CyrbcoCTLCQLCmGVgcwt8WwV+x/s+LaL3UljxHGE/73D1ZqmF0Yg+KKwN6n1Lj+QXvEesqu3bpIRBt+z8+keh1PA0eOO2g/r9glcC7G1NxBpG01uOU2EoZnR+R67rNvsrMSzBuIwuVqCIhkawIX7PldqGVGJW1LuJF9v7RIQ/HrVR5vXLyY1ctGrPgeErAja6165Vgr0LYy6GMxIoPVPnqtvCVNFQtLkpCOZ/gGm/uLp5fgGq6VDaKWyaTjJjqHVGmvZTCdy25LShZAxMHap0q02WkyyMOw7D0RFSt1My3ObUi6RSuh/CGvngQU9mxzpOz2SIIQYivUMMdh8Dp8tMwnombjECug2ZBOZ3LVzSMFzUeBTBI/2q+3bhnU5X5X7XmjKQLqxPVbMjG6Qcwgu2HG9LKJjc86AKEbSDET+eqqlnuq0meb1fwy0rOPLSjpuk2D1p7IWxySrFlL19AFY1qoThDujR8tbM= postgres@postgres-primary

# Check authorized_keys on Backup Server
more authorized_keys

Create authorized_keys File on Primary DB Server

If you do not paste the public key as a single line into the authorized_keys file, you will get an error.

#Switch to postgres user on Primary DB Server
sudo su - postgres

#Switch to hidden directory under user’s home directory on Primary DB Server
cd ~/.ssh

# Create a file which contains .pub key of the backup server
# paste the contains from the backup server of .pub key
vi authorized_keys

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQC2+0LaXhqf9IdlKtUOfMSt52gDBzm/lVHRCUe+bQUr4R0skWFnQ2aBHXM4Z9z5UE2HaxPVrCtl5yMHMakzz39XSioaO728XBKyuP8nEW9XziYOsLuIy7zmJcA0TAubM9+QaXrIbNI2wY6jesNuz22Bbk2tPRL1eJK7gotGZ4pmB9KFk0gCzrDz2IuvnzFO+f85/KRmlm2HwkN1d9leSqRHdQSg/T6TVoJR87hn2YdVNWSQgTalDDC/h3D5obM0/SVBYiKbxiZXqCPPhICVVk/cXJHyMX5p5G+maHUJ2Yk5D/DfJrRwbfWYmvz3iux7ihExJqRTMRohoWk8/S6RgKMWk+FPNtfKukxf72LcNQVpeNdbwxqU4azrCqjeSAdyZGk6hYCs/X50iC9dMOQQw03XN7YmjTZ3lQfT8RlnjjAcbd6K0tKAQBhAdBW0xV5m53N3iYI/CsDgtSsc9+uqMaaW328raF728xMOF8j9t3vJZRBEgyJdzr6m7irfzLSLqXU= postgres@postgres-pgbackrest

# Check authorized_keys on Primary DB Server
more authorized_keys

Test SSH without Password between Primary DB Server and Backup Server using postgres User

#Switch to postgres user on Primary DB Server and Backup Server
sudo su - postgres

#Test ssh from postgres-primary to postgres-pgbackrest
postgres@postgres-primary:~$ ssh postgres-pgbackrest

#Test ssh from postgres-pgbackrest to postgres-primary
postgres@postgres-pgbackrest:~$ ssh postgres-primary

10-Set Passwordless SSH between Restore Test Server & Backup Server

Note SSH Key on Backup Server

#Switch to postgres user on Backup Server
sudo su - postgres
 
#Switch to hidden directory under user’s home directory on Backup Server
cd ~/.ssh

# Check the created files. public key with .pub extension and private key without any extension on Backup Server
ls -lrt

# Check and note public key on Backup Server
more id_rsa.pub

postgres@postgres-pgbackrest:~/.ssh$ more id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQC2+0LaXhqf9IdlKtUOfMSt52gDBzm/lVHRCUe+bQUr4R0skWFnQ2aBHXM4Z9z5UE2HaxPVrCtl5yMHMakzz39XSioaO728XBKyuP8nEW9XziYOsLuIy7zmJcA0TAubM9+QaXrIbNI2wY6jesNuz22Bbk2tPRL1eJK7gotGZ4pmB9KFk0gCzrDz2IuvnzFO+f85/KRmlm2HwkN1d9leSqRHdQSg/T6TVoJR87hn2YdVNWSQgTalDDC/h3D5obM0/SVBYiKbxiZXqCPPhICVVk/cXJHyMX5p5G+maHUJ2Yk5D/DfJrRwbfWYmvz3iux7ihExJqRTMRohoWk8/S6RgKMWk+FPNtfKukxf72LcNQVpeNdbwxqU4azrCqjeSAdyZGk6hYCs/X50iC9dMOQQw03XN7YmjTZ3lQfT8RlnjjAcbd6K0tKAQBhAdBW0xV5m53N3iYI/CsDgtSsc9+uqMaaW328raF728xMOF8j9t3vJZRBEgyJdzr6m7irfzLSLqXU= postgres@postgres-pgbackrest

Generate and Note SSH Key on Restore Test Server

#Switch to postgres user on Restore Test Server
sudo su - postgres
 
#Generate the SSH key on Restore Test Server
ssh-keygen -t rsa

#Switch to hidden directory under user’s home directory on Restore Test Server
cd ~/.ssh

# Check the created files. public key with .pub extension and private key without any extension on Restore Test Server
ls -lrt

# Check and note public key on Restore Test Server
more id_rsa.pub

postgres@postgres-restoretest:~/.ssh$ more id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQCoqsrVUcN/mOr7WrFcZEPz5QBv7cBRnyZyNnkl2xaHk4yWJIWmU39MQFKYZ6qX7wpum+AdrjknzrcDrt016X6lv+1D65+AZ8vJ/C+R5Wriq1owZE8lLzG+174WaUWR8XaCAwPAC4J37OmV9UruRgPARsz7sn8iwReV4gT8bG9XgdM9WSEobdBQMnz1OSZM4NFEqSiSd9+cVOH7cTCp3Q9piX0G0ZpQhK3a99HvQgws1ckWc8s884zIeULMsOlWIEdAJ7W+F6kK2exblgWCDihKdxEtEkdeNgW7u91GJnbTsdbRzyH34WHR3pyPnkNFP67MuYsGI42HYMgcIdYUii1e+Clb6j2+okHP8hGc32TooF/DkP85XenSm+g/usxGfV8+XBBr7uWhTFjI6Q6rqo1vVqzGwWTgArcxTBYuN/L+ibhmVdd/9jslM/QRm9f+3HZG+POORFohSKBWdiBsx4ybabBZTlSFrxlIuIb8RmJJI6kvqcs8eGD9IiNzO/duskc= postgres@postgres-restoretest

Add Restore Test Server’s Public Key to Backup Server’s authorized_keys File on Backup Server

If you do not paste the public key as a single line into the authorized_keys file, you will get an error.

#Switch to postgres user on Backup Server
sudo su - postgres

#Switch to hidden directory under user’s home directory on Backup Server
cd ~/.ssh

# Add the contains from the restore test server of .pub key
vi authorized_keys

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQCoqsrVUcN/mOr7WrFcZEPz5QBv7cBRnyZyNnkl2xaHk4yWJIWmU39MQFKYZ6qX7wpum+AdrjknzrcDrt016X6lv+1D65+AZ8vJ/C+R5Wriq1owZE8lLzG+174WaUWR8XaCAwPAC4J37OmV9UruRgPARsz7sn8iwReV4gT8bG9XgdM9WSEobdBQMnz1OSZM4NFEqSiSd9+cVOH7cTCp3Q9piX0G0ZpQhK3a99HvQgws1ckWc8s884zIeULMsOlWIEdAJ7W+F6kK2exblgWCDihKdxEtEkdeNgW7u91GJnbTsdbRzyH34WHR3pyPnkNFP67MuYsGI42HYMgcIdYUii1e+Clb6j2+okHP8hGc32TooF/DkP85XenSm+g/usxGfV8+XBBr7uWhTFjI6Q6rqo1vVqzGwWTgArcxTBYuN/L+ibhmVdd/9jslM/QRm9f+3HZG+POORFohSKBWdiBsx4ybabBZTlSFrxlIuIb8RmJJI6kvqcs8eGD9IiNzO/duskc= postgres@postgres-restoretest

# Check authorized_keys on Backup Server
more authorized_keys

Create authorized_keys File on Restore Test Server

If you do not paste the public key as a single line into the authorized_keys file, you will get an error.

#Switch to postgres user on Restore Test Server
sudo su - postgres

#Switch to hidden directory under user’s home directory on Restore Test Server
cd ~/.ssh

# Create a file which contains .pub key of the backup server
# paste the contains from the backup server of .pub key
vi authorized_keys

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQC2+0LaXhqf9IdlKtUOfMSt52gDBzm/lVHRCUe+bQUr4R0skWFnQ2aBHXM4Z9z5UE2HaxPVrCtl5yMHMakzz39XSioaO728XBKyuP8nEW9XziYOsLuIy7zmJcA0TAubM9+QaXrIbNI2wY6jesNuz22Bbk2tPRL1eJK7gotGZ4pmB9KFk0gCzrDz2IuvnzFO+f85/KRmlm2HwkN1d9leSqRHdQSg/T6TVoJR87hn2YdVNWSQgTalDDC/h3D5obM0/SVBYiKbxiZXqCPPhICVVk/cXJHyMX5p5G+maHUJ2Yk5D/DfJrRwbfWYmvz3iux7ihExJqRTMRohoWk8/S6RgKMWk+FPNtfKukxf72LcNQVpeNdbwxqU4azrCqjeSAdyZGk6hYCs/X50iC9dMOQQw03XN7YmjTZ3lQfT8RlnjjAcbd6K0tKAQBhAdBW0xV5m53N3iYI/CsDgtSsc9+uqMaaW328raF728xMOF8j9t3vJZRBEgyJdzr6m7irfzLSLqXU= postgres@postgres-pgbackrest

# Check authorized_keys on Restore Test Server
more authorized_keys

Test SSH without Password between Restore Test Server and Backup Server using postgres User

#Switch to postgres user on Restore Test Server and Backup Server
sudo su - postgres

#Test ssh from postgres-restoretest to postgres-pgbackrest
postgres@postgres-restoretest:~$ ssh postgres-pgbackrest

#Test ssh from postgres-pgbackrest to postgres-restoretest
postgres@postgres-pgbackrest:~$ ssh postgres-restoretest

11-Configure Archive Parameters on Primary DB Server

# Switch to postgres user on the primary db server
sudo su - postgres

# Set archive_mode and archive_command on the primary db server postgresql.conf file
vi /etc/postgresql/16/main/postgresql.conf

archive_mode = on
archive_command = 'pgbackrest --config=/etc/pgbackrest/pgbackrest.conf  --stanza=demo archive-push %p'

# Check parameters on the primary db server postgresql.conf file
more /etc/postgresql/16/main/postgresql.conf | grep archive_mode
more /etc/postgresql/16/main/postgresql.conf | grep archive_command

# Switch to root user
exit

# It requires the restart of the primary db server service
systemctl restart postgresql@16-main.service
systemctl status postgresql@16-main.service

# Check the parameters on the primary db server
su - postgres
psql
show archive_mode;
show archive_command;

12-Configure the Stanza on Backup Server

Create Stanza on Backup Server

# Switch to postgres user on the backup server
sudo su - postgres

#Using stanza-create command on the backup server
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail stanza-create

Validate Stanza Setup on Backup Server

# Switch to postgres user on the backup server
sudo su - postgres

#Validate the setup before taking backups using the check command on the backup server
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail check

13-Try to Take First Full Backup on Backup Server

# Switch to Postgres user
sudo su - postgres

# Check config
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail check

# Try to take first full backup
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail backup --type=full

14-Try to Schedule First Backups on Backup Server

# Switch to Postgres
sudo su - postgres

# Edit the crontab
crontab -e

# We will take Wednesday full backups at 01:15 a.m. server time. Rest of the days we wil take incremental backups at the same time.
#m h   dom mon dow           command
15 08  *   *   3             pgbackrest --type=full --stanza=demo backup
15 08  *   *   0,1,2,4,5,6   pgbackrest --type=incr --stanza=demo backup

15-Check Backup & Expire Log Files on Backup Server

# Switch to Postgres
sudo su - postgres

# Check log files
cd /var/log/pgbackrest/
ls -lrt

more demo-backup.log | grep '2025-07-16 08:15' | head -7
more demo-backup.log | grep '2025-07-16 08:15' | tail -7

more demo-expire.log | grep '2025-07-16 08:15' | tails

16-Check Detail Backup Information on Backup Server

# Switch to Postgres User
sudo su - postgres

# Check Backup Details 
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail info

17-Full Backup Restore Steps on Restore Test Server

# Check Postgres Setup on the Restore Test Server
pg_lsclusters

# Stop the DB Service on the Restore Test Server
systemctl stop postgresql@16-main.service

# Check the DB Service on the Restore Test Server
systemctl status postgresql@16-main.service

# Create Empty Data Directory or Rename Existing Data Directory
sudo su - postgres
mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main_20250716
# Restore Backup
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=info restore
# Check the Restored Environment
pg_lsclusters
cd /var/lib/postgresql/16/
ls -lrt
exit
systemctl start postgresql@16-main.service
systemctl status postgresql@16-main.service
sudo su - postgres
pg_lsclusters
psql
\l

18-Delta Changes Restore Steps on Restore Test Server

Perform Full Backup on Backup Server

# Switch to Postgres user
sudo su - postgres

# Check config
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail check

# Perform full backup
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail backup --type=full

Check Full Backup Information on Backup Server

# Switch to Postgres User
sudo su - postgres

# Check Backup Details 
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail info

Create Test Database, Table and Insert Test Data on Primary DB Server

sudo su - postgres

psql -U postgres -d postgres <<EOF
CREATE DATABASE restore_test_01;
\c restore_test_01
CREATE TABLE restore_test_01 (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO restore_test_01 (name) VALUES
('Alice'), ('Bob'), ('Charlie');
SELECT * FROM restore_test_01;
EOF

Perform Incremental Backup on Backup Server

# Switch to Postgres user
sudo su - postgres

# Check config
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail check

# Perform incremental backup
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail backup --type=incr

Check Incremental Backup Information on Backup Server

# Switch to Postgres User
sudo su - postgres

# Check Backup Details 
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail info

Restore Delta Changes on Restore Test Server

# Check Postgres on Restore Test Server
pg_lsclusters

# Stop the DB Cluster on Restore Test Server
sudo systemctl stop postgresql@16-main.service

# Switch to Postgres User on Restore Test Server
sudo su - postgres

# Restore Delta Changes on Restore Test Server
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=info restore --delta

Check the Database, Table, and Data Created Before the Incremental Backup on Restore Test Server

# Switch to Root User on Restore Test Server
su - root

# Start the DB Cluster on Restore Test Server
sudo systemctl start postgresql@16-main.service

# Check the Status of DB Cluster on Restore Test Server
sudo systemctl status postgresql@16-main.service

# Check the Database, Table, and Data Created Before the Incremental Backup on Restore Test Server
sudo su - postgres

psql -U postgres -d postgres <<EOF
\c restore_test_01
SELECT * FROM restore_test_01;
EOF

19-Specific Database Restore Steps on Restore Test Server

Create Test Database (restore_test_02), Table and Insert Test Data on Primary DB Server

sudo su - postgres

psql -U postgres -d postgres <<EOF
CREATE DATABASE restore_test_02;
\c restore_test_02
CREATE TABLE restore_test_02 (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO restore_test_02 (name) VALUES
('Alice'), ('Bob'), ('Charlie');
SELECT * FROM restore_test_02;
EOF

Perform Incremental Backup on Backup Server

# Switch to Postgres user
sudo su - postgres

# Check config
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail check

# Perform incremental backup
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail backup --type=incr

Check Incremental Backup Information on Backup Server

# Switch to Postgres User
sudo su - postgres

# Check Backup Details 
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail info

Restore Specific Database (restore_test_02) on Restore Test Server

# Check Postgres on Restore Test Server
pg_lsclusters

# Stop the DB Cluster on Restore Test Server
sudo systemctl stop postgresql@16-main.service

# Create Empty Data Directory or Rename Existing Data Directory
sudo su - postgres
mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main_20250717

# Restore Specific Database on Restore Test Server
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=info restore --db-include=restore_test_02

Check the restore_test_02 Database, Table, and Data on Restore Test Server

# Switch to Root User on Restore Test Server
su - root

# Start the DB Cluster on Restore Test Server
sudo systemctl start postgresql@16-main.service

# Check the Status of DB Cluster on Restore Test Server
sudo systemctl status postgresql@16-main.service

# Check the restore_test_02 Database, Table, and Data on Restore Test Server
sudo su - postgres

psql -U postgres -d postgres <<EOF
\l
\c restore_test_02
SELECT * FROM restore_test_02;
EOF

Why does restore_test_01 Appear Even Though the Database has not been Restored?

- It is expected situation. 
- You can see restore_test_01 database but you can not connect. 
- restore_test_01 database can be dropped.

sudo su - postgres
psql
\l
\c restore_test_01
drop database restore_test_01;
\l

20-Point-In-Time Recovery (PITR) Steps using Time on Restore Test Server

Truncate Test Table Data and Note Time Before Truncate on Primary DB Server

Target Time: 2025-07-20 10:24:54

# Truncate Test Table Data and Note Time at which Data was Deleted
sudo su - postgres

psql -U postgres -d postgres <<EOF
\l
\c restore_test_02
SELECT * FROM restore_test_02;
select now();
TRUNCATE TABLE restore_test_02;
SELECT * FROM restore_test_02;
EOF

Perform Incremental Backup on Backup Server

# Switch to Postgres user
sudo su - postgres

# Check config
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail check

# Perform incremental backup
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail backup --type=incr

Check Incremental Backup Information on Backup Server

# Switch to Postgres User
sudo su - postgres

# Check Backup Details 
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail info

Perform PITR using Time on Restore Test Server

Target Time: 2025-07-20 10:24:54

# Check Postgres on Restore Test Server
pg_lsclusters

# Stop the DB Cluster on Restore Test Server
sudo systemctl stop postgresql@16-main.service

# Create Empty Data Directory or Rename Existing Data Directory
sudo su - postgres
mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main_20250720

# Perform PITR on Restore Test Server
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=info --delta --type=time "--target=2025-07-20 10:24:54" --target-action=promote restore

Check Deleted Test Table Data Recovered or not on Restore Test Server

# Switch to Root User on Restore Test Server
su - root

# Start the DB Cluster on Restore Test Server
sudo systemctl start postgresql@16-main.service

# Check the Status of DB Cluster on Restore Test Server
sudo systemctl status postgresql@16-main.service

# Check the restore_test_02 Database, Table, and Data on Restore Test Server
sudo su - postgres

psql -U postgres -d postgres <<EOF
\l
\c restore_test_02
SELECT * FROM restore_test_02;
EOF

21-Point-In-Time Recovery (PITR) Steps using LSN on Restore Test Server

Truncate Test Table Data and Note LSN Before Truncate on Primary DB Server

Target LSN: 0/21000500

# Truncate Test Table Data and Note LSN Before Truncate on Primary DB Server
sudo su - postgres

psql -U postgres -d postgres <<EOF
\l
\c restore_test_02
SELECT * FROM restore_test_02;
select pg_current_wal_lsn();
TRUNCATE TABLE restore_test_02;
SELECT * FROM restore_test_02;
EOF

Perform Incremental Backup on Backup Server

# Switch to Postgres user
sudo su - postgres

# Check config
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail check

# Perform incremental backup
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail backup --type=incr

Check Incremental Backup Information on Backup Server

# Switch to Postgres User
sudo su - postgres

# Check Backup Details 
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail info

Perform PITR using LSN on Restore Test Server

Target LSN: 0/21000500

# Check Postgres on Restore Test Server
pg_lsclusters

# Stop the DB Cluster on Restore Test Server
sudo systemctl stop postgresql@16-main.service

# Create Empty Data Directory or Rename Existing Data Directory
sudo su - postgres
mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main_20250722

# Perform PITR on Restore Test Server
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=info --delta --type=lsn "--target=0/21000500" --target-action=promote restore

Check Deleted Test Table Data Recovered or not on Restore Test Server

# Switch to Root User on Restore Test Server
su - root

# Start the DB Cluster on Restore Test Server
sudo systemctl start postgresql@16-main.service

# Check the Status of DB Cluster on Restore Test Server
sudo systemctl status postgresql@16-main.service

# Check the restore_test_02 Database, Table, and Data on Restore Test Server
sudo su - postgres

psql -U postgres -d postgres <<EOF
\l
\c restore_test_02
SELECT * FROM restore_test_02;
EOF

22-Multi-Repo Configuration for Backup Redundancy (If Needed)

Create New Backup Directory for New Repo on Backup Server

I will use same backup server for test purpose. You can use different server located on DRC or Cloud environment.

sudo mkdir -p /data/backups2
sudo chmod 750 /data/backups2
sudo chown postgres:postgres /data/backups2
ls -lrt /data

Edit pgbackrest.conf file and Add One More Repo on Backup Server

sudo su - postgres

# Edit pgbackrest.conf file and Add One More Repo on Backup Server
vi /etc/pgbackrest/pgbackrest.conf

[global]
repo1-path=/data/backups
repo2-path=/data/backups2

log-level-file=detail
start-fast=y

repo1-retention-full=2
repo2-retention-full=2

archive-async=y
spool-path=/var/spool/pgbackrest

[demo]
pg1-host=192.168.1.151
pg1-path=/var/lib/postgresql/16/main
pg1-host-user=postgres

# Check New pgbackrest.conf file on Backup Server
more /etc/pgbackrest/pgbackrest.conf

Edit pgbackrest.conf file and Add One More Repo on Primary DB Server

sudo su - postgres

# Edit pgbackrest.conf file and Add One More Repo on Primary DB Server
vi /etc/pgbackrest/pgbackrest.conf

[global]
repo1-host=192.168.1.152
repo1-path=/data/backups
repo1-host-user=postgres

repo2-host=192.168.1.152
repo2-path=/data/backups2
repo2-host-user=postgres

log-level-file=detail
start-fast=y

archive-async=y
spool-path=/var/spool/pgbackrest

[demo]
pg1-path=/var/lib/postgresql/16/main
pg1-host-user=postgres

# Check New pgbackrest.conf file on Primary DB Server
more /etc/pgbackrest/pgbackrest.conf

Edit pgbackrest.conf file and Add One More Repo on Restore Test Server

sudo su - postgres

# Edit pgbackrest.conf file and Add One More Repo on Restore Test Server
vi /etc/pgbackrest/pgbackrest.conf

[global]
repo1-host=192.168.1.152
repo1-path=/data/backups
repo1-host-user=postgres

repo2-host=192.168.1.152
repo2-path=/data/backups2
repo2-host-user=postgres

log-level-file=detail
start-fast=y

archive-async=y
spool-path=/var/spool/pgbackrest

[demo]
pg1-path=/var/lib/postgresql/16/main
pg1-host-user=postgres

# Check New pgbackrest.conf file on Restore Test Server
more /etc/pgbackrest/pgbackrest.conf

Create pgBackRest archive Directory and Adjust Permissions on Primary DB Server

sudo mkdir -p /var/spool/pgbackrest/archive
sudo chown -R postgres:postgres /var/spool/pgbackrest
sudo chmod -R 750 /var/spool/pgbackrest
sudo systemctl restart postgresql@16-main
sudo systemctl status postgresql@16-main

Create the Stanza Again on Backup Server

# Switch to postgres user on the backup server
sudo su - postgres

# Using stanza-create command on the backup server
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail stanza-create

Validate Stanza Setup on Backup Server

# Switch to postgres user on the backup server
sudo su - postgres

#Validate the setup before taking backups using the check command on the backup server
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail check

Try to Take First Full Backup to repo2 on Backup Server

# Switch to postgres user on the backup server
sudo su - postgres

# Check config
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail check

# Try to take first full backup
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail backup --type=full --repo=2

Check Backup Information for repo2 on Backup Server

# Switch to Postgres User
sudo su - postgres

# Check Backup Details For Repo2
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail info --repo=2

23-Configure Backup Retention Parameters

General Information about Backup Retention Parameters

Backup Retention Parameters in pgBackRest:

  • repo1-retention-full-type can have values time or count. Default is count.
    • repo1-retention-full-type
    • repo1-retention-full
    • repo1-retention-diff
  • Time denotes how many days backup can be retained, and count denotes how many copies of backup can be retained
  • Not only can we define how many full backups can be retained but also how many differential backups can be retained using repo1-retention-full and repo1-retention-diff
  • There is no separate retention parameter for incremental backups in pgBackRest.
    • The lifespan of incremental backups is managed indirectly through the retention settings of the full and differential backups they depend on.
    • The parameter repo1-retention-incr is not valid in pgBackRest.

Set Backup Retention Parameters on Backup Server

# Switch to Postgres User
sudo su - postgres

# Edit pgbackrest.conf file on Backup Server
vi /etc/pgbackrest/pgbackrest.conf

[global]
repo1-path=/data/backups
repo2-path=/data/backups2

log-level-file=detail
start-fast=y

repo1-retention-full-type=count
repo1-retention-full=2

repo2-retention-full-type=time
repo2-retention-full=1

archive-async=y
spool-path=/var/spool/pgbackrest

[demo]
pg1-host=192.168.1.151
pg1-path=/var/lib/postgresql/16/main
pg1-host-user=postgres

# Check New pgbackrest.conf file on Backup Server
more /etc/pgbackrest/pgbackrest.conf

Set Backup Retention Parameters on Primary DB Server

# Switch to Postgres User
sudo su - postgres

# Edit pgbackrest.conf file on Primary DB Server
vi /etc/pgbackrest/pgbackrest.conf

[global]
repo1-host=192.168.1.152
repo1-path=/data/backups
repo1-host-user=postgres
repo1-retention-full-type=count
repo1-retention-full=2

repo2-host=192.168.1.152
repo2-path=/data/backups2
repo2-host-user=postgres
repo2-retention-full-type=time
repo2-retention-full=1

log-level-file=detail
start-fast=y

archive-async=y
spool-path=/var/spool/pgbackrest

[demo]
pg1-path=/var/lib/postgresql/16/main
pg1-host-user=postgres

# Check New pgbackrest.conf file on Primary DB Server
more /etc/pgbackrest/pgbackrest.conf

Set Backup Retention Parameters on Restore Test Server

# Switch to Postgres User
sudo su - postgres

# Edit pgbackrest.conf file on Restore Test Server
vi /etc/pgbackrest/pgbackrest.conf

[global]
repo1-host=192.168.1.152
repo1-path=/data/backups
repo1-host-user=postgres
repo1-retention-full-type=count
repo1-retention-full=2

repo2-host=192.168.1.152
repo2-path=/data/backups2
repo2-host-user=postgres
repo2-retention-full-type=time
repo2-retention-full=1

log-level-file=detail
start-fast=y

archive-async=y
spool-path=/var/spool/pgbackrest

[demo]
pg1-path=/var/lib/postgresql/16/main
pg1-host-user=postgres

# Check New pgbackrest.conf file on Restore Test Server
more /etc/pgbackrest/pgbackrest.conf

Ensure Retention Policy Works for repo1 on Backup Server

  • You should see maximum two full backups.
  • Since we have below parameters
    • repo1-retention-full-type=count
    • repo1-retention-full=2
  • There is no separate retention parameter for incremental backups in pgBackRest.
    • The lifespan of incremental backups is managed indirectly through the retention settings of the full and differential backups they depend on.
    • The parameter repo1-retention-incr is not valid in pgBackRest.
# Switch to Postgres User
sudo su - postgres

# Check How Many Backups Exist
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail info --repo=1

# Perform Full Backup for repo1
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=info backup --type=full --repo=1

# Perform Incremental Backup for repo1
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=info backup --type=incr --repo=1

Ensure Retention Policy Works for repo2 on Backup Server

  • Since the repo2-retention-full-type parameter is set to time and repo2-retention-full is set to 1, it is normal for more than one full and incremental backup to appear for the same day.
    • repo2-retention-full-type=time
    • repo2-retention-full=1
# Switch to Postgres User
sudo su - postgres

# Check How Many Backups Exist
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail info --repo=2

# Perform Full Backup for repo1
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=info backup --type=full --repo=2

# Perform Incremental Backup for repo1
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=info backup --type=incr --repo=2

24-Expire Backups Manually (If Needed)

# Switch to Postgres User
sudo su - postgres

# Check How Many Backups Exist
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail info --repo=1

# Expire Backup Using Set
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail expire --set=20250720-173624F --repo=1

# Check How Many Backups Exist
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail info --repo=1