How to Change the Backup Location in pgBackRest for PostgreSQL 15

This guide walks you through updating pgBackRest to use a new backup directory — in this case, /u01/pgbackrest.

📁 Step 1: Create the New Backup Directory

mkdir -p /u01/pgbackrest
chown postgres:postgres /u01/pgbackrest
chmod 750 /u01/pgbackrest

⚙️ Step 2: Update pgBackRest Configuration

Edit the config file:

vi /etc/pgbackrest.conf

Update the [global] section to use the new path:

ini

[global]
repo1-path=/u01/pgbackrest
repo1-retention-full=2
repo1-retention-diff=4
log-level-console=info
log-level-file=debug
log-path=/var/log/pgbackrest

[pg15]
pg1-path=/u01/postgres/pgdata_15/data
pg1-port=5432
pg1-user=postgres

🔐 Step 3: Set Permissions on Config File

chmod 640 /etc/pgbackrest.conf
chown postgres:postgres /etc/pgbackrest.conf

🧱 Step 4: Recreate the Stanza

Since the backup location changed, you need to recreate the stanza:
sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 stanza-create

🔍 Step 5: Verify the New Location

Run a check to confirm everything is working:

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 check

✅ You should see a message like:

Code
INFO: WAL segment ... successfully archived to '/u01/pgbackrest/archive/pg15/...'

💾 Step 6: Run a Test Backup

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --type=full backup

📊 Step 7: Confirm Backup Storage

Check that backups are stored in the new location:

ls -l /u01/pgbackrest/pg15/backup

🧹 Optional: Clean Up Old Backup Location

If you no longer need the old backups:

bash

rm -rf /var/lib/pgbackrest

⚠️ Only do this if you're sure the new backups are working and complete.

pgBackRest Setup and PostgreSQL 15 Backup Guide

This guide walks you through installing, configuring, backing up, restoring, and automating PostgreSQL 15 backups using pgBackRest.

Installation

# Add PostgreSQL Yum repo

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable built-in PostgreSQL module

sudo dnf -qy module disable postgresql

# Install pgBackRest

sudo dnf install -y pgbackrest --nobest


# Verify installation

which pgbackrest

# Output: /usr/bin/pgbackrest

1. Create Backup Directory

mkdir -p /u01/pgbackrest
chown postgres:postgres /u01/pgbackrest
chmod 750 /u01/pgbackrest

2. Configure pgBackRest

Backup and edit the config file:

cp /etc/pgbackrest.conf /etc/pgbackrest.conf_bkp

vi /etc/pgbackrest.conf

[global]
repo1-path=/u01/pgbackrest
repo1-retention-full=2
repo1-retention-diff=4
log-level-console=info
log-level-file=debug
log-path=/var/log/pgbackrest

[pg15]
pg1-path=/u01/postgres/pgdata_15/data
pg1-port=5432
pg1-user=postgres

3. Set Permissions

chmod 640 /etc/pgbackrest.conf
chown postgres:postgres /etc/pgbackrest.conf

4. Confirm PostgreSQL Is Running

ps aux | grep postgres

Look for:

postgres ... -D /u01/postgres/pgdata_15/data

5. Test PostgreSQL Connection

sudo -u postgres psql -h /tmp -p 5432 -d postgres

6. Create Stanza

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --log-level-console=info stanza-create

7. Configure WAL Archiving

vi /u01/postgres/pgdata_15/data/postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=pg15 archive-push %p'

Reload PostgreSQL:

sudo -u postgres pg_ctl reload -D /u01/postgres/pgdata_15/data

8. Verify Stanza and Archiving

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --log-level-console=info check

9. Run Full Backup

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --type=full backup

10. View Backup Info

sudo -u postgres pgbackrest --stanza=pg15 info

PostgreSQL Test Database Setup

# Log in

sudo -u postgres psql

# Create Database

CREATE DATABASE test_db;

\c test_db

# Create Table

CREATE TABLE employees (id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, position VARCHAR(50),salary NUMERIC(10, 2));

# Insert Sample Data

INSERT INTO employees (name, position, salary) VALUES
('Ayaan Siddiqui', 'Manager', 75000.00),
('Zara Qureshi', 'Sr. Developer', 60000.00),
('Imran Sheikh', 'Jr. Developer', 50000.00);

# Query Table

SELECT * FROM employees;

# Exit

\q

Backup Types

Full Backup:

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --type=full backup

Differential Backup:

\c test_db

INSERT INTO employees (name, position, salary) VALUES
('Fatima Ansari', 'DBA', 70000.00),
('Yusuf Khan', 'Sr. Developer', 50000.00),
('Nadia Rahman', 'Jr. Developer', 40000.00);

SELECT * FROM employees;

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --type=diff backup

Incremental Backup:

\c test_db

INSERT INTO employees (name, position, salary) VALUES
('Bilal Ahmed', 'DEO', 34000.00),
('Sana Mirza', 'System Admin', 45000.00),
('Tariq Hussain', 'Accountant', 50000.00);

SELECT * FROM employees;

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --type=incr backup

Verify Backups:

sudo -u postgres pgbackrest --stanza=pg15 info

Restore Options:

Option 1: Partial Restore

systemctl stop postgresql
sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --delta restore
systemctl start postgresql
systemctl status postgresql

Option 2: Full Clean Restore

systemctl stop postgresql
mv /var/lib/postgresql/15/main /var/lib/postgresql/15/main_backup_$(date +%F)
mkdir /var/lib/postgresql/15/main
chown -R postgres:postgres /var/lib/postgresql/15/main
chmod 700 /var/lib/postgresql/15/main
sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 restore
systemctl start postgresql
systemctl status postgresql

Verify Restore:

sudo -u postgres psql -c "SELECT datname FROM pg_database;"

tail -f /var/log/pgbackrest/pgbackrest.log

Automate Backups with Cron:

crontab -u postgres -e

Add:

# Full Backup - Sunday 2 AM
0 2 * * 0 pgbackrest --stanza=pg15 --type=full backup

# Incremental Backup - Mon-Sat 2 AM
0 2 * * 1-6 pgbackrest --stanza=pg15 --type=incr backup

Monitor and Test:

Monitor Logs

tail -f /var/log/pgbackrest/pgbackrest.log

journalctl -u postgresql

Test Recovery Plan:

Regularly test backup restoration
Document recovery steps

Troubleshooting:

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 check

Step-by-Step: Sanity Check for Cascading Replication in PostgreSQL

Cascading replication allows PostgreSQL replicas to stream data not just from the primary, but from other replicas as well. This setup is ideal for scaling read operations and distributing replication load.

In this guide, we’ll walk through a simple sanity check to ensure your 4-node cascading replication setup is working as expected.

🧩 Assumptions
We’re working with a 4-node setup:

Node 1 is the Primary
Node 2 replicates from Node 1
Node 3 replicates from Node 2
Node 4 replicates from Node 3

Replication is already configured and running, and you have access to psql on all nodes.

Step 1: Create reptest Database on Primary (Node 1)

Open a terminal and connect to PostgreSQL:

psql -U postgres

Create the database and switch to it:

sql

CREATE DATABASE reptest;

\c reptest

Step 2: Create a Sample Table

Inside the reptest database, create a simple table:

sql

CREATE TABLE sanity_check (id SERIAL PRIMARY KEY,message TEXT,created_at TIMESTAMP DEFAULT now());

Step 3: Insert Sample Data

Add a few rows to test replication:

sql

INSERT INTO sanity_check (message) VALUES ('Hello from Node 1'), ('Replication test'), ('Cascading is cool!');

Step 4: Wait a Few Seconds

Give replication a moment to propagate. Cascading replication may introduce slight delays, especially for downstream nodes like Node 4.

Step 5: Verify Data on Nodes 2, 3, and 4

On each replica node, connect to the reptest database:
psql -U postgres -d reptest

Then run:
sql
SELECT * FROM sanity_check;

You should see the same rows that were inserted on the primary.

✅ Conclusion

If the data appears correctly on all replicas, your cascading replication setup is working as expected. This simple sanity check can be repeated periodically or automated to ensure ongoing replication health.

PostgreSQL Cascading Replication: Full 4-Node Setup Guide

Cascading replication allows PostgreSQL replicas to stream data not only from the primary node but also from other replicas. This setup is ideal for scaling read operations and distributing replication load across multiple servers.

🗂️ Node Architecture and Port Assignment

Node 1 (Primary) — Port 5432 — Accepts read/write operations and streams to Node 2
Node 2 (Standby 1) — Port 5433 — Receives from Node 1 and streams to Node 3.
Node 3 (Standby 2) — Port 5434 — Receives from Node 2 and streams to Node 4.
Node 4 (Standby 3) — Port 5435 — Receives from Node 3.

IP assignments:

Node 1 → 172.19.0.21
Node 2 → 172.19.0.22
Node 3 → 172.19.0.23
Node 4 → 172.19.0.24

⚙️ Prerequisites

PostgreSQL installed on all four nodes (same version recommended).
Network connectivity between nodes.
A replication user (repuser) with REPLICATION privilege created on the primary.
PostgreSQL data directories initialized for each node.

Step 1: Configure the Primary Server (Node 1, Port 5432)

🔹 Modify postgresql.conf

listen_addresses = '*'
port = 5432
wal_level = replica
max_wal_senders = 10
wal_keep_size = 512MB
hot_standby = on

🔹 Update pg_hba.conf to allow replication from Node 2

Generic:
host replication repuser <Node2_IP>/32 trust

Actual:
host replication repuser 172.19.0.22/32 trust

🔹 Reload PostgreSQL Configuration

pg_ctl reload
Or from SQL:
sql
SELECT pg_reload_conf();

🔹 Start Node 1

pg_ctl -D /path/to/node1_data -o '-p 5432' -l logfile start

Actual:
pg_ctl -D /u01/postgres/pgdata_15/data -o '-p 5432' -l logfile start

🔹 Create Replication User

sql
CREATE USER repuser WITH REPLICATION ENCRYPTED PASSWORD 'your_password';

🔹 Verify Node 1 is Not in Recovery

sql
SELECT pg_is_in_recovery();

Step 2: Set Up Standby 1 (Node 2, Port 5433)

🔹 Take a Base Backup from Node 1

Generic:
pg_basebackup -h <Node1_IP> -p 5432 -D /path/to/node2_data -U repuser -Fp -Xs -P -R

Actual:
pg_basebackup -h 172.19.0.21 -p 5432 -D /u01/postgres/pgdata_15/data -U repuser -Fp -Xs -P -R

If you get an error:
pg_basebackup: error: directory "/u01/postgres/pgdata_15/data" exists but is not empty

Clear the directory:
rm -rf /u01/postgres/pgdata_15/data/*

🔹 Modify postgresql.conf on Node 2

listen_addresses = '*'
port = 5433
hot_standby = on
primary_conninfo = 'host=172.19.0.21 port=5432 user=repuser password=your_password application_name=node2'

🔹 Start Node 2

Generic:
pg_ctl -D /path/to/node2_data -o '-p 5433' -l logfile start

Actual:
pg_ctl -D /u01/postgres/pgdata_15/data -o '-p 5433' -l logfile start

🔹 Verify Replication on Node 1

sql
SELECT * FROM pg_stat_replication WHERE application_name = 'node2';

Step 3: Set Up Standby 2 (Node 3, Port 5434)

🔹 Allow Replication from Node 3 in Node 2’s pg_hba.conf

Generic:
host replication repuser <Node3_IP>/32 trust

Actual:
host replication repuser 172.19.0.23/32 trust

🔹 Reload Node 2

pg_ctl -D /u01/postgres/pgdata_15/data reload

🔹 Take a Base Backup from Node 2

Generic:
pg_basebackup -h <Node2_IP> -p 5433 -D /path/to/node3_data -U repuser -Fp -Xs -P -R

Actual:
pg_basebackup -h 172.19.0.22 -p 5433 -D /u01/postgres/pgdata_15/data -U repuser -Fp -Xs -P -R

🔹 Modify postgresql.conf on Node 3

listen_addresses = '*'
port = 5434
hot_standby = on
primary_conninfo = 'host=172.19.0.22 port=5433 user=repuser password=your_password application_name=node3'

🔹 Start Node 3

Generic:
pg_ctl -D /path/to/node3_data -o '-p 5434' -l logfile start

Actual:
pg_ctl -D /u01/postgres/pgdata_15/data -o '-p 5434' -l logfile start

🔹 Verify Replication on Node 2

sql
SELECT * FROM pg_stat_replication;

Step 4: Set Up Standby 3 (Node 4, Port 5435)

🔹 Allow Replication from Node 4 in Node 3’s pg_hba.conf

Generic:
host replication repuser <Node4_IP>/32 trust

Actual:
host replication repuser 172.19.0.24/32 trust

🔹 Reload Node 3

pg_ctl -D /u01/postgres/pgdata_15/data reload

🔹 Take a Base Backup from Node 3

Generic:
pg_basebackup -h <Node3_IP> -p 5434 -D /path/to/node4_data -U repuser -Fp -Xs -P -R

Actual:
pg_basebackup -h 172.19.0.23 -p 5434 -D /u01/postgres/pgdata_15/data -U repuser -Fp -Xs -P -R

🔹 Modify postgresql.conf on Node 4

listen_addresses = '*'
port = 5435
hot_standby = on
primary_conninfo = 'host=172.19.0.23 port=5434 user=repuser password=your_password application_name=node4'

🔹 Start Node 4

Generic:
pg_ctl -D /path/to/node4_data -o '-p 5435' -l logfile start

Actual:
pg_ctl -D /u01/postgres/pgdata_15/data -o '-p 5435' -l logfile start

🔹 Verify Replication on Node 3

sql
SELECT * FROM pg_stat_replication;

✅ Final Verification & Monitoring

🔹 Check Replication Status

On Node 1:
sql
SELECT * FROM pg_stat_replication;

On Nodes 2 and 3:
sql

SELECT * FROM pg_stat_wal_receiver;
SELECT * FROM pg_stat_replication;

On Node 4:

sql
SELECT pg_is_in_recovery();
SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS byte_lag;

🔹 Confirm Listening Ports

bash
sudo netstat -plnt | grep postgres
You should see ports 5432, 5433, 5434, and 5435 active.

🔹 Monitor WAL Activity

sql
SELECT * FROM pg_stat_wal;

🔹 Measure Replication Lag

On Primary:
sql

SELECT application_name, client_addr,pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS byte_lag FROM pg_stat_replication;

The Docker Copy Machine: How to Clone a Container and Save Hours of Setup Time

Have you ever spent precious time meticulously setting up a Docker container—installing packages, configuring software, and tuning the system—only to need an identical copy and dread starting from scratch?

If you’ve found yourself in this situation, there's a faster way. Docker provides a straightforward method to "clone" or "template" a fully configured container. This is perfect for creating development, staging, and testing environments that are perfectly identical, or for rapidly scaling out services like PostgreSQL without reinstalling everything each time.

In this guide, I’ll show you the simple, practical commands to create a master copy of your container and spin up duplicates in minutes, not hours.

The Problem: Why Not Just Use a Dockerfile?

A Dockerfile is fantastic for reproducible, version-controlled builds. But sometimes, you’ve already done the work inside a running container. You've installed software, run complex configuration scripts, or set up a database schema. Manually translating every step back into a Dockerfile can be tedious and error-prone.

The solution? Treat your perfectly configured container as a gold master image.

Our Practical, Command-Line Approach

Let's walk through the process. Imagine your initial container, named BOX01, is a CentOS system with PostgreSQL already installed and configured.

Step 1: Create Your Master Image with docker commit

The magic command is docker commit. It takes a running (or stopped) container and saves its current state—all the changes made to the filesystem—as a new, reusable Docker image.

# Format: docker commit [CONTAINER_NAME] [NEW_IMAGE_NAME]

docker commit BOX01 centos-postgres-master

What this does: It packages up everything inside BOX01 (except the volumes, more on that later) and creates a new local Docker image named centos-postgres-master.

Verify it worked:

docker images

You should see centos-postgres-master listed among your images.

Step 2: Spin Up Your Clones

Now, you can create as many copies as you need from your new master image. The key is to give each new container a unique name, hostname, and volume to avoid conflicts.

# Create your first clone

docker run -it -d --privileged=true --restart=always --network MQMNTRK -v my-vol102:/u01 --ip 172.19.0.21 -h HOST02 --name BOX02 centos-postgres-master /usr/sbin/init

# Create a second clone

docker run -it -d --privileged=true --restart=always --network MQMNTRK -v my-vol103:/u01 --ip 172.19.0.22 -h HOST03 --name BOX03 centos-postgres-master /usr/sbin/init

Repeat this pattern for as many copies as you need (BOX04, HOST04, etc.). In under a minute, you'll have multiple identical containers running.

⚠️ Important Consideration: Handling Data Volumes

This is the most critical caveat. The docker commit command does not capture data stored in named volumes (like my-vol101).

The Good: Your PostgreSQL software and configuration are baked into the image.

The Gotcha: The actual database data lives in the volume, which is separate.

Your options for data:

Fresh Data: Use a new, empty volume for each clone (as shown above). This is ideal for creating new, independent database instances.

Copy Data: If you need to duplicate the data from the original container, you must copy the volume contents. Here's a quick way to do it:

# Backup data from the original volume (my-vol101)

docker run --rm -v my-vol101:/source -v $(pwd):/backup alpine tar cvf /backup/backup.tar -C /source .

# Restore that data into a new volume (e.g., my-vol102)

docker run --rm -v my-vol102:/target -v $(pwd):/backup alpine bash -c "tar xvf /backup/backup.tar -C /target"

When Should You Use This Method?

Rapid Prototyping: Quickly duplicate a complex environment for testing.
Legacy or Complex Setups: When the installation process is complicated and not easily scripted in a Dockerfile.
Learning and Experimentation: Easily roll back to a known good state by committing before you try something risky.

When Should You Stick to a Dockerfile?

Production Builds: Dockerfiles are transparent, reproducible, and version-controlled.
CI/CD Pipelines: Automated builds from a Dockerfile are a standard best practice.
When Size Matters: Each commit can create a large image layer. Dockerfiles can be optimized to produce smaller images.

Conclusion

The docker commit command is Docker's built-in "copy machine." It’s an incredibly useful tool for developers who need to work quickly and avoid repetitive setup tasks. While it doesn't replace the need for well-defined Dockerfiles in a production workflow, it solves a very real problem for day-to-day development and testing.

So next time you find yourself with a perfectly configured container, don't rebuild—clone it!

Next Steps: Try creating your master image and a clone. For an even more manageable setup, look into writing a simple docker-compose.yml file to define all your container parameters in one place.

Simplifying PostgreSQL Restarts: How to Choose the Right Mode for Your Needs

Three Restart Modes in PostgreSQL

1. smart mode (-m smart) - Gentle & Safe

Waits patiently for all clients to disconnect on their own
Completes all transactions normally before shutting down
No data loss - everything commits properly
Best for production during active hours
Slowest method but most graceful

2. fast mode (-m fast) - Quick & Controlled

Rolls back active transactions immediately
Disconnects all clients abruptly but safely
Restarts quickly without waiting
No data corruption - maintains database integrity
Perfect for maintenance windows
Medium risk - some transactions get aborted

3. immediate mode (-m immediate) - Emergency Only

Forceful shutdown like a crash
No waiting - kills everything immediately
May require recovery on next startup
Risk of temporary inconsistencies
Only for emergencies when nothing else works
PostgreSQL will automatically recover using WAL logs

💡 Key Points to Remember:

All three modes are safe for the database itself
Data integrity is maintained through WAL (Write-Ahead Logging)
Choose based on your situation: gentle vs fast vs emergency
Fast mode is completely normal for planned maintenance
Always warn users before using fast or immediate modes

🚀 When to Use Each:

Use smart mode when:
Database is in production use
You can afford to wait
No transactions should be interrupted

Use fast mode when:
You need to restart quickly
During maintenance windows
Some transaction rollback is acceptable

Use immediate mode when:
Database is unresponsive
Emergency situations only
You have no other choice

All modes will bring your database back safely - they just differ in how gently they treat connected clients and active transactions!