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;

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!

Step-by-Step: Converting Postgre Sync Replication to Async in Production

Simple Steps

📋 Prerequisites

Postgre 9.1 or higher.
Superuser access to the primary database.
Existing replication setup.

🚀 Quick Change Steps (No Restart Required)

Step 1: Connect to Primary Database
p -U postgres -h your-primary-server

Step 2: Check Current Sync Status
SELECT name, setting FROM pg_settings WHERE name IN ('synchronous_commit', 'synchronous_standby_names');

Step 3: Change to Asynchronous Mode
-- Disable synchronous commit
ALTER SYSTEM SET synchronous_commit = 'off';

-- Clear synchronous standby names
ALTER SYSTEM SET synchronous_standby_names = '';

-- Reload configuration (no restart needed)

SELECT pg_reload_conf();

Step 4: Verify the Change
-- Confirm settings changed

SELECT name, setting FROM pg_settings WHERE name IN ('synchronous_commit', 'synchronous_standby_names');

-- Check replication status (should show 'async')

SELECT application_name, sync_state, state FROM pg_stat_replication;

📝 Configuration File Method (Alternative)

Edit postgre.conf on Primary:
sudo nano /etc/postgre/14/main/postgre.conf
Change these lines:
conf
synchronous_commit = off
synchronous_standby_names = ''

Reload Configuration:
sudo systemctl reload postgres
# or
sudo pg_ctl reload

🧪 Testing the Change

Test 1: Performance Check
-- Time an insert operation
\timing on
INSERT INTO test_table (data) VALUES ('async test');
\timing off

Test 2: Verify Async Operation
-- Check replication lag
SELECT application_name, sync_state, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_lag FROM pg_stat_replication;

Test 3: Data Consistency Check

# On primary:
p -c "SELECT COUNT(*) FROM your_table;"

# On standby:
p -h standby-server -c "SELECT COUNT(*) FROM your_table;"

⚠️ Important Considerations

Before Changing:

Understand the risks: Async replication may cause data loss during failover
Check business requirements: Ensure async meets your data consistency needs
Monitor performance: Async should improve write performance but may increase replication lag

After Changing:

Monitor replication lag regularly
Set up alerts for significant lag increases
Test failover procedures to understand potential data loss scenarios
Review application behavior - some apps may need adjustments

🔄 Reverting to Synchronous Mode

If you need to switch back:

ALTER SYSTEM SET synchronous_commit = 'on';
ALTER SYSTEM SET synchronous_standby_names = 'your_standby_name';

SELECT pg_reload_conf();

🎯 When to Use This Change

Good candidates for Async:

Read-heavy workloads where write performance matters
Non-critical data that can tolerate minor data loss
High-volume logging or analytics data
Geographically distributed systems with high latency

Keep Synchronous For:

Financial transactions.
User account data.
Critical business operations.
Systems requiring zero data loss.

💡 Pro Tips

Use a hybrid approach: Some systems support both sync and async simultaneously.
Monitor continuously: Use tools like pg_stat_replication to watch lag times.
Test thoroughly: Always test replication changes in a staging environment first.
Document the change: Keep records of why and when you changed replication modes.
The change from sync to async takes effect immediately and requires no downtime, making it a relatively low-risk operation that can significantly improve write performance for appropriate use cases.

The Real-Time Database Dilemma: When to Use Synchronous vs Asynchronous Replication in PostgreSQL

Synchronous Replication

✅ Benefits:

Zero Data Loss: Guarantees that data is safely written to both primary and standby before confirming success.
Strong Consistency: All servers have exactly the same data at all times.
Automatic Failover: Standby servers are always up-to-date and ready to take over instantly.
Perfect for Critical Data: Ideal for financial transactions, user accounts, and mission-critical information.

❌ Drawbacks:

Slower Performance: Write operations wait for standby confirmation, increasing latency.
Availability Risk: If standby goes down, primary may become unavailable or slow.
Higher Resource Usage: Requires more network bandwidth and standby server resources.
Complex Setup: More configuration and maintenance required.

⚡ Asynchronous Replication

✅ Benefits:

Faster Performance: Write operations complete immediately without waiting for standby.
Better Availability: Primary continues working even if standby servers disconnect.
Long-Distance Friendly: Works well across geographical distances with higher latency.
Simpler Management: Easier to configure and maintain.

❌ Drawbacks:

Potential Data Loss: Recent transactions may be lost if primary fails before replication completes.
Data Lag: Standby servers might be slightly behind the primary.
Weaker Consistency: Temporary mismatches between primary and standby possible.
Failover Complexity: May require manual intervention to ensure data consistency.

🏭 Production Environment Preferences

📊 For Most Production Systems:

Mixed Approach: Use synchronous for critical data, asynchronous for less critical data.
Tiered Architecture: Synchronous for primary standby, asynchronous for additional read replicas.
Monitoring Essential: Track replication lag and performance metrics continuously.

⚡ For Real-Time Systems:

Synchronous Preferred: When data consistency is more important than raw speed.
Financial Systems: Banking, payments, and transactions require synchronous replication.
E-commerce: Order processing and inventory management often need synchronous guarantees.

🚀 When to Choose Async:

High-Write Systems: Applications with heavy write workloads (logging, analytics).
Read-Heavy Applications: When you need multiple read replicas for performance.
Geographic Distribution: Systems spanning multiple regions with higher latency.
Non-Critical Data: Caching, session data, or temporary information.

🎯 Key Recommendations

Start with Async for most applications, then move to sync for critical components.
Monitor Replication Lag constantly - anything beyond few seconds needs attention.
Test Failover Regularly - ensure your standby can take over when needed.
Use Connection Pooling to help manage the performance impact of synchronous replication.
Consider Hybrid Approaches - some databases support both modes simultaneously.

🔍 Real-World Example

Social Media Platform:

Synchronous: User accounts, direct messages, financial transactions.
Asynchronous: Activity feeds, notifications, analytics, likes.

E-commerce Site:

Synchronous: Orders, payments, inventory updates.
Asynchronous: Product recommendations, user reviews, search indexing.

Step-by-Step Guide to PostgreSQL Streaming Replication (Primary-Standby Setup)

Here's the properly organized step-by-step guide for setting up PostgreSQL 12/15 streaming replication between Primary (172.19.0.20) and Standby (172.19.0.30):


✅ Pre-Requisites

Two servers with PostgreSQL 12/15.3 installed
Database cluster initialized on both using initdb (service not started yet)

IP configuration:

Primary: 172.19.0.20
Standby: 172.19.0.30

🔧 Primary Server Configuration

Backup config file:

bash

cd /u01/postgres/pgdata_15/data
cp postgresql.conf postgresql.conf_bkp_$(date +%b%d_%Y)


Edit postgresql.conf:

bash

vi postgresql.conf

Set these parameters:

listen_addresses = '*'
port = 5432
wal_level = replica
archive_mode = on
archive_command = 'cp %p /u01/postgres/pgdata_15/data/archive_logs/%f'
max_wal_senders = 10
wal_keep_segments = 50

Start PostgreSQL:

bash

/u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

Create replication user:

sql

CREATE USER repl_user WITH REPLICATION ENCRYPTED PASSWORD 'strongpassword';

Configure pg_hba.conf:

bash

cp pg_hba.conf pg_hba.conf_bkp_$(date +%b%d_%Y)

vi pg_hba.conf

Add:
host replication repl_user 172.19.0.30/32 md5

Reload config:

bash

/u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile reload

🔄 Standby Server Configuration

Clear data directory:

bash

rm -rf /u01/postgres/pgdata_15/data/*

Run pg_basebackup:

bash

pg_basebackup -h 172.19.0.20 -U repl_user -p 5432 -D /u01/postgres/pgdata_15/data -Fp -Xs -P -R

Start PostgreSQL:

bash

pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

Verify logs:

bash

tail -f /u01/postgres/pgdata_15/data/logfile

Look for:

text

started streaming WAL from primary at 0/3000000 on timeline 1

🔍 Verification Steps

On Primary:

sql

CREATE DATABASE reptest;

\c reptest

CREATE TABLE test_table (id int);

INSERT INTO test_table VALUES (1);

On Standby:

sql

\c reptest

SELECT * FROM test_table; -- Should return 1 row

Check processes:

Primary:

bash

ps -ef | grep postgres

Look for walsender process

Standby:

Look for walreceiver process

Network test:

bash

nc -zv 172.19.0.20 5432


📌 Key Notes

-Replace paths/versions according to your setup
-Ensure firewall allows port 5432 between servers
For Oracle Linux 6.8, adjust service commands if needed
-Monitor logs (logfile) on both servers for errors


Detailed steps:

✅ Pre-Requisites

Two servers with PostgreSQL 12 or PostgreSQL 15.3 installed.
Database cluster initialized on both using initdb (but do not start the service yet).

IPs configured:

Primary: 172.19.0.20
Standby: 172.19.0.30

Make some necessary changes in postgresql.conf file on the primary server.

[postgres@HOST01 data]$ pwd

/u01/postgres/pgdata_15/data

[postgres@HOST01 data]$ cp postgresql.conf postgresql.conf_bkp_Aug18_2025

[postgres@HOST01 data]$ vi postgresql.conf


listen_addresses = '*'
port = 5432
wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /u01/postgres/pgdata_15/data/archive_logs/%f'
max_wal_senders = 10

After making necessary changes in the postgresql.conf file. Let’s start the database server on primary server using below command.

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

Once the database server is started, invoke psql as super user i.e. postgres and create a role for replication.

CREATE USER repl_user WITH REPLICATION ENCRYPTED PASSWORD 'strongpassword';

Allow connection to created user from Standby Server by seting pg_hba.conf

[postgres@HOST01 data]$ pwd

/u01/postgres/pgdata_15/data

[postgres@HOST01 data]$ ls

archive_logs pg_commit_ts pg_ident.conf pg_replslot pg_stat_tmp PG_VERSION postgresql.conf

base pg_dynshmem pg_logical pg_serial pg_subtrans pg_wal postgresql.conf_old

global pg_hba.conf pg_multixact pg_snapshots pg_tblspc pg_xact postmaster.opts

logfile pg_hba.conf_bkp_Aug18_2025 pg_notify pg_stat pg_twophase postgresql.auto.conf postmaster.pid


[postgres@HOST01 data]$ cp pg_hba.conf pg_hba.conf_bkp_Aug18_2025

[postgres@HOST01 data]$ vi pg_hba.conf

host replication repl_user 172.19.0.30/32 md5

Reload the setting of the cluster.

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile stop

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

(OR)

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile reload

We made changes to the configuration of the primary server. Now we’ll use pg_basebackup to get the data directory i.e. /u01/postgres/pgdata_15/data to Standby Server.

Standby Server Configurations.

[postgres@HOST02 ~]$ pg_basebackup -h 172.19.0.20 -U repl_user -p 5432 -D /u01/postgres/pgdata_15/data -Fp -Xs -P -R

Password:

pg_basebackup: error: directory "/u01/postgres/pgdata_15/data" exists but is not empty

Navigate to data directory and empty it.

cd /u01/postgres/pgdata_15/data

rm -rf *

[postgres@HOST02 ~]$ pg_basebackup -h 172.19.0.20 -U repl_user -p 5432 -D /u01/postgres/pgdata_15/data -Fp -Xs -P -R

Password:

38761/38761 kB (100%), 1/1 tablespace

Now start the database server at standby side using below command.


[postgres@HOST02 ~]$ pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

waiting for server to start.... done

server started

Check the logfile to gather some more information.

[postgres@HOST02 data]$ tail -f logfile

2025-08-18 21:12:09.487 UTC [11349] LOG: listening on IPv4 address "0.0.0.0", port 5432
2025-08-18 21:12:09.487 UTC [11349] LOG: listening on IPv6 address "::", port 5432
2025-08-18 21:12:09.491 UTC [11349] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-08-18 21:12:09.498 UTC [11352] LOG: database system was interrupted; last known up at 2025-08-18 21:11:13 UTC
2025-08-18 21:12:09.513 UTC [11352] LOG: entering standby mode
2025-08-18 21:12:09.518 UTC [11352] LOG: redo starts at 0/2000028
2025-08-18 21:12:09.519 UTC [11352] LOG: consistent recovery state reached at 0/2000100
2025-08-18 21:12:09.520 UTC [11349] LOG: database system is ready to accept read-only connections
2025-08-18 21:12:09.557 UTC [11353] LOG: started streaming WAL from primary at 0/3000000 on timeline 1

Let’s now check the replication. Invoke psql and perform some operations.

Primary:

create database qadar_reptest;
\c qadar_reptest;
create table mqm_reptst_table (serial int);
insert into mqm_reptst_table values (1);

Standby:

\c qadar_reptest;
select count(*) from mqm_reptst_table;

Check the background process involved in streaming replication:

Primary:

[postgres@HOST01 data]$ ps -ef|grep postgres

root 11329 160 0 19:40 pts/1 00:00:00 su - postgres
postgres 11330 11329 0 19:40 pts/1 00:00:04 -bash
root 11608 11496 0 21:07 pts/2 00:00:00 su - postgres
postgres 11609 11608 0 21:07 pts/2 00:00:00 -bash
postgres 11635 11609 0 21:07 pts/2 00:00:00 /usr/bin/coreutils --coreutils-prog-shebang=tail /usr/bin/tail -f logfile
postgres 11639 1 0 21:08 ? 00:00:00 /u01/postgres/pgdata_15/bin/postgres -D /u01/postgres/pgdata_15/data
postgres 11640 11639 0 21:08 ? 00:00:00 postgres: checkpointer
postgres 11641 11639 0 21:08 ? 00:00:00 postgres: background writer
postgres 11643 11639 0 21:08 ? 00:00:00 postgres: walwriter
postgres 11644 11639 0 21:08 ? 00:00:00 postgres: autovacuum launcher
postgres 11645 11639 0 21:08 ? 00:00:00 postgres: archiver last was 000000010000000000000002.00000028.backup
postgres 11646 11639 0 21:08 ? 00:00:00 postgres: logical replication launcher
postgres 11663 11639 0 21:12 ? 00:00:00 postgres: walsender repl_user 172.19.0.30(38248) streaming 0/3000060
postgres 11671 11330 0 21:15 pts/1 00:00:00 ps -ef
postgres 11672 11330 0 21:15 pts/1 00:00:00 grep --color=auto postgres

Standby:

[postgres@HOST02 ~]$ ps -ef|grep postgres

root 11300 119 0 20:59 pts/1 00:00:00 su - postgres
postgres 11301 11300 0 20:59 pts/1 00:00:00 -bash
postgres 11349 1 0 21:12 ? 00:00:00 /u01/postgres/pgdata_15/bin/postgres -D /u01/postgres/pgdata_15/data
postgres 11350 11349 0 21:12 ? 00:00:00 postgres: checkpointer
postgres 11351 11349 0 21:12 ? 00:00:00 postgres: background writer
postgres 11352 11349 0 21:12 ? 00:00:00 postgres: startup recovering 000000010000000000000003
postgres 11353 11349 0 21:12 ? 00:00:00 postgres: walreceiver streaming 0/3000148
postgres 11356 11301 0 21:16 pts/1 00:00:00 ps -ef
postgres 11357 11301 0 21:16 pts/1 00:00:00 grep --color=auto postgres


Check the network connectivity betweeen the servers.

Standby:

[postgres@HOST02 ~] yum install nc
[postgres@HOST02 ~]$ nc -zv 172.19.0.20 5432
Ncat: Version 7.70 ( https://nmap.org/ncat )
Ncat: Connected to 172.19.0.20:5432.
Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

Comprehensive Guide to Installing PostgreSQL: Step-by-Step Methods for All Platforms

Comprehensive Guide to Installing PostgreSQL: Step-by-Step Methods for All Platforms

1. Binary Installation (Windows, macOS, Linux)

Windows:

1. Download PostgreSQL Installer:

Visit PostgreSQL official website.

Download the installer for Windows (it uses EDB's installer).

2. Run the Installer:

Launch the downloaded .exe file.

Follow the installation wizard: Choose the installation directory, the components to install (like pgAdmin, StackBuilder, etc.), and the data directory for your database.

3. Set PostgreSQL Password:

During installation, you'll be prompted to set a password for the postgres superuser account.

4. Port Configuration:

Default port 5432 is usually fine unless there’s a conflict. You can change it if needed.

5. Choose a Locale:

Select the locale (language/region) for your PostgreSQL installation.

6. Finish Installation:

Complete the installation process. PostgreSQL will start automatically.

7. Verify Installation:

Open pgAdmin or use psql from the command prompt:

psql -U postgres

If you are able to connect, the installation is successful.

macOS (Using Homebrew):

1. Install Homebrew (if not already installed):

Run the following command in your terminal:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

2. Install PostgreSQL:

Use Homebrew to install PostgreSQL:

brew install postgresql

3. Start PostgreSQL Service:

After installation, start PostgreSQL:

brew services start postgresql

4. Verify Installation:

Connect using the psql command:

psql postgres

If you are able to connect, the installation is successful.

Linux (Ubuntu/Debian):

1. Update the Package List:

Run the following command to update your package index:

sudo apt update

2. Install PostgreSQL:

Install PostgreSQL with:

sudo apt install postgresql postgresql-contrib

3. Start the PostgreSQL Service:

Start PostgreSQL:

sudo service postgresql start

4. Verify Installation:

Switch to the postgres user:

sudo -i -u postgres

Open PostgreSQL interactive terminal:

psql

If successful, you should be in the PostgreSQL prompt.

2. Source Installation (Linux/Unix-based)

Ubuntu/Debian Example:

1. Install Dependencies:

Before building from source, install the necessary dependencies:

sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison

2. Download the Source Code:

Visit PostgreSQL Downloads to get the latest source code or use wget:

wget https://ftp.postgresql.org/pub/source/v15.2/postgresql-15.2.tar.bz2

3. Extract the Source Code:

tar -xjf postgresql-15.2.tar.bz2

cd postgresql-15.2

4. Compile and Install:

Run the following commands to compile and install:

./configure

make

sudo make install

5. Create PostgreSQL User and Database:

Create a system user and set up the PostgreSQL database:

sudo useradd postgres

sudo mkdir /usr/local/pgsql/data

sudo chown postgres /usr/local/pgsql/data

6. Initialize Database:

sudo -u postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

7. Start PostgreSQL:

Start the PostgreSQL server:

sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

8. Verify Installation:

Access PostgreSQL:

/usr/local/pgsql/bin/psql

3. Docker Installation

1. Install Docker:

First, ensure Docker is installed on your machine. Follow the instructions on the Docker website.

2. Pull PostgreSQL Image:

Once Docker is set up, pull the official PostgreSQL image:

docker pull postgres

3. Run PostgreSQL in Docker:

Start the container:

docker run --name postgres-container -e POSTGRES_PASSWORD=mysecretpassword -d postgres

4. Access PostgreSQL:

Connect to PostgreSQL from the Docker container:

docker exec -it postgres-container psql -U postgres

4. Cloud-based Installation

Amazon RDS (AWS) Example:

1. Log in to AWS Management Console.

Go to the RDS service.

2. Create a New Database Instance:

Choose the PostgreSQL engine.

Configure instance specifications (e.g., DB instance class, storage size, etc.).

3. Configure Database Settings:

Set up the master username and password.

4. Launch the Instance:

Click on "Create Database" to launch your PostgreSQL instance.

5. Connect to the Database:

Use the endpoint provided by AWS to connect:

psql -h your-endpoint.amazonaws.com -U postgres -d yourdbname

5. Windows Subsystem for Linux (WSL) Installation (For Windows Users)

1. Install WSL:

Run the following command in PowerShell as Administrator:

wsl --install

2. Install PostgreSQL on WSL:

Inside WSL, run:

sudo apt update

sudo apt install postgresql postgresql-contrib

3. Start PostgreSQL:

Start the PostgreSQL service in WSL:

sudo service postgresql start

4. Verify Installation:

Connect using the psql command:

psql -U postgres

PostgreSQL Source-Based Installation on Centos

PostgreSQL Source-Based Installation On Centos

Pre-requisites:

sudo yum update -y
yum install sudo

vi /etc/sudoers
root ALL=(ALL) ALL

sudo yum groupinstall "Development Tools" -y
sudo yum install -y readline-devel zlib-devel


Download PostgreSQL:

yum install wget
wget https://ftp.postgresql.org/pub/source/v15.3/postgresql-15.3.tar.gz
tar -zxvf postgresql-15.3.tar.gz

cd postgresql-15.3

Configure & Compile:
./configure --prefix=/u01/postgres/pgdata_15
make
sudo make install

Create PostgreSQL User:

[root@HOST01 postgresql-15.3]# pwd

/u01/soft/postgresql-15.3
bash

sudo useradd postgres
sudo mkdir -p /u01/postgres
sudo chown -R postgres:postgres /u01/soft/postgresql-15.3
sudo chmod -R 0700 /u01/soft/postgresql-15.3

Set Environment Variables:

[postgres@HOST02 pgdata_15]$ pwd

/u01/postgres/pgdata_15
bash

su - postgres
export PATH=$PATH:/u01/postgres/pgdata_15/bin
export PGDATA=/u01/postgres/pgdata_15/data
export LD_LIBRARY_PATH=/u01/postgres/pgdata_15/lib:$LD_LIBRARY_PATH


Initialize Database Cluster:

[postgres@HOST02 bin]$ pwd

/u01/postgres/pgdata_15/bin

mkdir -p /u01/postgres/pgdata_15/data
sudo chown -R postgres:postgres /u01/postgres/pgdata_15/data
sudo chmod -R 0700 /u01/postgres/pgdata_15/data

bash
/u01/postgres/pgdata_15/bin/initdb -D /u01/postgres/pgdata_15/data

Start PostgreSQL Server:

bash

pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

Verify Installation:
bash
pg_ctl -D /u01/postgres/pgdata_15/data status

psql --version

Optional: Set PostgreSQL to Start on Boot:

If you want PostgreSQL to start automatically on boot, you can create a systemd service file.

Example:

ini

[Unit]

Description=PostgreSQL database server

Documentation=https://www.postgresql.org

After=network.target

[Service]

Type=forking

User=postgres

Group=postgres

Environment=PGDATA=/u01/postgres/pgdata_15/data

ExecStart=/u01/postgres/pgdata_15/bin/pg_ctl start -D ${PGDATA} -s -l /var/log/pgsql.log -o "-c config_file=/u01/postgres/pgdata_15/data/postgresql.conf"

ExecStop=/u01/postgres/pgdata_15/bin/pg_ctl stop -D ${PGDATA} -s -m fast

ExecReload=/u01/postgres/pgdata_15/bin/pg_ctl reload -D ${PGDATA} -s -c config_file=/u01/postgres/pgdata_15/data/postgresql.conf


[Install]

Save this as /etc/systemd/system/postgresql.service and then enable and start it:

bash

sudo systemctl enable postgresql

sudo systemctl start postgresql

This should cover all the steps for a source-based installation of PostgreSQL. If you encounter any issues, let me know and I'll help you troubleshoot!

Understanding the Differences: PostgreSQL Streaming Replication vs. Cascading Replication

Understanding PostgreSQL: Streaming Replication vs. Cascading Replication

In the realm of database management, ensuring data integrity and availability is paramount. PostgreSQL, a powerful open-source relational database system, offers several replication methods to meet these needs. Two prominent techniques are Streaming Replication and Cascading Replication. This blog post delves into the nuances of each method, helping you choose the right replication strategy for your needs.

What is Replication in PostgreSQL?

Replication in PostgreSQL is the process of copying and maintaining database objects in multiple database servers. This practice enhances data availability and accessibility, providing a solid disaster recovery solution. Replication can be synchronous or asynchronous and is crucial for load balancing, failover, and high availability.

Comparing Streaming vs. Cascading Replication

Key Terms:

• Synchronous Replication: Ensures data consistency by waiting for all copies to be updated before completing a transaction.
• Asynchronous Replication: Improves performance by allowing transactions to complete before all replicas are updated.

Streaming Replication: Real-Time Data Mirroring

Streaming Replication allows real-time copying of WAL (Write-Ahead Logging) records from a primary server to one or more standby servers. This method is highly efficient for achieving near-zero data loss and ensuring that the standby servers are always up-to-date.

Advantages of Streaming Replication:

• Real-Time Synchronization: Ensures high data consistency and availability.
• Failover Support: Automatic failover to a standby server in case of primary failure.
• Load Balancing: Read queries can be distributed among multiple standby servers.

Cascading Replication: The Hierarchical Approach

Cascading Replication extends the concept of streaming replication by allowing a standby server to act as a source of replication to other standbys. This hierarchical setup is beneficial for distributing the replication load and extending the replication chain without overburdening the primary server.

Advantages of Cascading Replication:

• Scalability: Efficiently scales the replication process to a large number of standby servers.
• Bandwidth Efficiency: Reduces bandwidth usage by localizing traffic to regional standbys.
• Flexible Hierarchies: Supports dynamic adjustments to the replication topology.

Choosing Between Streaming and Cascading Replication

The choice between streaming and cascading replication depends on your specific requirements:

• Use Streaming Replication if you need real-time data synchronization and high availability is a priority.
• Opt for Cascading Replication when scalability and efficient bandwidth usage are critical, especially in geographically distributed environments.

Implementing Effective Replication Strategies

Regardless of the chosen method, implementing a robust replication strategy involves careful planning and ongoing management. Monitoring replication lag, managing failovers, and ensuring data integrity across all servers are crucial components of a successful replication setup.

Conclusion

PostgreSQL's Streaming and Cascading Replication provide powerful tools for database redundancy, performance optimization, and high availability. By understanding the strengths and applications of each method, database administrators can tailor replication strategies to best suit their operational needs

Unlocking the Power of PostgreSQL: An In-depth Look at Its Major Features

PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance. It can handle workloads ranging from small single-machine applications to large internet-facing applications with many concurrent users. 

Here are some of the major features that make PostgreSQL stand out.

1.Extensibility: PostgreSQL allows you to define your own data types, operators, and functions. You can even write code in different programming languages without the need for wrappers.

2.ACID Compliance: PostgreSQL is fully ACID compliant (Atomicity, Consistency, Isolation, Durability), ensuring data integrity and consistency even in the event of system failures.

3.Comprehensive Indexing: PostgreSQL supports a wide array of indexing techniques, including B-tree, hash, and GIN (Generalized Inverted Index), to name a few.

4.Full-Text Search: PostgreSQL comes with built-in support for full-text search, a feature usually found only in dedicated search systems.

5.Security: PostgreSQL has robust security features that include strong access-control mechanisms, views, and granular permissions.

6.MVCC (Multi-Version Concurrency Control): PostgreSQL uses MVCC, which allows for high concurrency and performance by creating a “snapshot” of data that allows each transaction to work with a consistent view of the data.

7.Open-Source: PostgreSQL is open-source and managed by a vibrant and independent community.

8.Support for JSON: PostgreSQL offers advanced JSON processing and allows you to store, query, and process JSON data.

9.Spatial Database: PostgreSQL supports geographic objects allowing location queries to be run in SQL.

10.Replication: PostgreSQL supports master-slave replication and multi-master replication through various add-ons, enhancing the read performance and data redundancy.

11.Partitioning: PostgreSQL supports range, list, and hash partitioning, which helps improve the performance of large tables.

12.Stored Procedures: PostgreSQL supports stored procedures in multiple programming languages.

13.Triggers: PostgreSQL supports triggers, which are automatically invoked upon certain database operations.

14.Views: PostgreSQL supports views which are a way of representing the records in the database in a more meaningful way.

15.Foreign Data Wrappers: PostgreSQL supports foreign data wrappers which provide a way to query external databases directly from PostgreSQL.

16.Table Inheritance: PostgreSQL supports table inheritance, which allows database designers to create a hierarchical structure of tables to represent real-world 

relationships between different types of data.

17.Role-Based Authentication: PostgreSQL uses role-based authentication, where a role can represent a database user or a group of database users.

18.Support for Large Objects: PostgreSQL has a large object facility, which provides stream-style access to user data that is stored in a special large-object structure.

19.Transaction Savepoints: PostgreSQL supports transaction savepoints, allowing you to roll back part of a transaction without aborting the entire transaction.

20.Window Functions: PostgreSQL supports window functions, providing more complex analysis of data than can be achieved with standard SQL aggregation functions.

Reference: 

https://www.postgresql.org/about/featurematrix/

Exploring PostgreSQL 16.2: A Dive into New Features

Exploring PostgreSQL 16.2: A Dive into New Features

1. Parallelization of FULL and Internal Right OUTER Hash Joins

In PostgreSQL 16.2, join performance gets a significant boost. The parallelization of FULL and internal right OUTER hash joins ensures faster query execution. Whether you’re dealing with large datasets or complex queries, this enhancement will make your SQL statements more efficient.

2. Logical Replication from Standby Servers

Data distribution and availability are critical for any database system. With PostgreSQL 16.2, you can now set up logical replication from standby servers. This feature allows you to replicate data seamlessly, ensuring high availability and disaster recovery.

3. Parallel Application of Large Transactions

Handling large transactions can be challenging, especially in busy environments. PostgreSQL 16.2 introduces the ability to apply large transactions in parallel. This improvement significantly reduces the time required for transaction processing, enhancing overall system performance.

4. Monitoring I/O Statistics with pg_stat_io

I/O performance is crucial for database administrators. PostgreSQL 16.2 introduces the new pg_stat_io view, which provides detailed insights into I/O operations. Monitor read and write activity, identify bottlenecks, and optimize your storage subsystem effectively.

5. SQL/JSON Constructors and Identity Functions

Working with JSON data becomes more convenient in PostgreSQL 16.2. The expanded SQL/JSON syntax includes constructors and identity functions. Whether you’re building APIs or handling complex JSON structures, these additions simplify your code and improve readability.

6. Improved Vacuum Freezing Performance

Database maintenance is essential for long-term stability. PostgreSQL 16.2 enhances the performance of vacuum freezing. This process ensures that dead rows are efficiently removed, reclaiming space and preventing bloat. Your database will run smoother and require less manual intervention.

Reference:

https://www.postgresql.org/docs/current/release-16.html