PostgreSQL Upgrade and Backup Guide

Step 1: Pre-Upgrade Checks

Before upgrading PostgreSQL, ensure all dependencies are checked:

Verify storage capacity to accommodate the upgrade.
Check all dependencies.
Check replica status.
Check all installed extensions for compatibility with the new version.
Stop any monitoring scripts to prevent unnecessary alerts during the upgrade.

Step 2: Install PostgreSQL 17

Run the following commands to update the package list and install PostgreSQL 17:

sudo apt-get update
sudo apt-get install postgresql-17 -y

When installing on the same server as PostgreSQL 14, it might auto-create a cluster. If not, create one manually:

sudo pg_createcluster 17 main --start

Check status of PostgreSQL 17:

sudo systemctl status postgresql@17

Verify version:

psql -p 5433
SELECT version();

Step 3: Update PostgreSQL Configuration

Modify postgresql.conf to reflect required changes from v14 to v17.
Update pg_hba.conf to carry forward authentication settings.

Restart PostgreSQL 17 after changes:

sudo systemctl restart postgresql@17
psql -p 5433 -c "SELECT version();"

Set consistent password:

psql -p 5433 -c "ALTER USER postgres WITH PASSWORD 'Ihpeef3d@|fq~2J7FKnD8';"

Step 4: Change PostgreSQL 14 Port and Restart

To prevent new transactions on v14:

Change port from 5432 to 5434 in postgresql.conf and pgbackrest.conf.
Inform the team about 5-minute downtime.
Remove replica from backend and promote if necessary.
Copy old config files to a safe location.
Restart PostgreSQL 14:

sudo systemctl restart postgresql.service

Verify it is listening on 5434.

Step 5: Start Backup Script

Use the following script to run global + per-database backups:

cd Upgradation_Backup/
nano pg_global_plus_all_DB_backup.sh

#!/bin/bash
export PGPASSWORD="password"
BACKUP_DIR="/home/ubuntu/Upgradation_Backup"
LOG_FILE="$BACKUP_DIR/pg_global_plus_all_DB_backup.log"
PG_USER="postgres"
PG_HOST="localhost"
PG_PORT="5434"
PG_VERSION="14"
mkdir -p "$BACKUP_DIR"
echo "===== PostgreSQL Backup Started: $(date) =====" | tee -a "$LOG_FILE"
echo "Starting Global Dump..." | tee -a "$LOG_FILE"
pg_dumpall -U $PG_USER -h $PG_HOST -p $PG_PORT --globals-only > "$BACKUP_DIR/global_dump.sql" 2>>"$LOG_FILE"
if [ $? -eq 0 ]; then
    echo "Global Dump Completed." | tee -a "$LOG_FILE"
else
    echo "Global Dump Failed!" | tee -a "$LOG_FILE"
fi
DATABASES=$(psql -U $PG_USER -h $PG_HOST -p $PG_PORT -d postgres -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template1', 'template0');")
for DB in $DATABASES; do
    echo "Starting backup for database: $DB" | tee -a "$LOG_FILE"
    DB_BACKUP_DIR="$BACKUP_DIR/$DB"
    mkdir -p "$DB_BACKUP_DIR"
    pg_dump -U $PG_USER -h $PG_HOST -p $PG_PORT -d "$DB" -F d -j 8 -v -f "$DB_BACKUP_DIR" 2>>"$LOG_FILE"
    if [ $? -eq 0 ]; then
        echo "Backup completed for database: $DB" | tee -a "$LOG_FILE"
    else
        echo "Backup failed for database: $DB" | tee -a "$LOG_FILE"
    fi
done
echo "===== PostgreSQL Backup Completed: $(date) =====" | tee -a "$LOG_FILE"

Check all backup directories and log file to verify success and prevent data loss.

Step 6: Restore Data to PostgreSQL 17

Restore global roles, users, and permissions:

export PGPASSWORD="Ihpeef3d@"
psql -U postgres -h localhost -p 5433 -f /home/ubuntu/Upgradation_Backup/global_dump.sql

Restore individual databases:

export PGPASSWORD="Ihpeef3d@"
pg_restore -U postgres -h localhost -p 5433 --create -j 8 -Fd -d postgres /home/ubuntu/Upgradation_Backup/db_name -v

Run this for each database.

Step 7: Verify Data Integrity

After restoration:

Check all databases and data.
Verify database sizes and object counts.
Confirm users, roles, and permissions.
Validate extensions.
Compare data between v14 and v17.

Step 8: Switch PostgreSQL 17 to Port 5432

Update PostgreSQL 17 to use port 5432:

sudo systemctl restart postgresql@17

Check that connections route to v17:

psql
SELECT version();

All traffic now shifts to v17.

Step 9: Setup Replica-A and Hyderabad Replica

Change old version port to 5434 and restart service.
Install PostgreSQL 17, copy config files from v14.
Modify postgresql.conf and pg_hba.conf for v17.
Stop PostgreSQL 17, rename main to main_old.

Take base backup on replica:

Replica_A:

pg_basebackup -p 5432 -U hyd_primary_dr -h 10.0.0.1 -D /var/lib/postgresql/17/main -Xs -R -P -v

Hyderabad-DR:

pg_basebackup -p 5432 -U hyd_primary_dr -h 10.0.0.1 -D /var/lib/postgresql/17/main -Xs -R -P -v

Change application_name in postgresql_auto.conf
Restart replica.
Enable replication status check script in crontab.

Step 10: Update Backup and Monitoring Scripts

pgBackRest:

Update configuration to change port for v14 to 5434.
Create a new stanza for v17 (e.g., main-17).
Remove old stanza/archive_command entries.
Update automated scripts accordingly.

pgBadger:

Modify report script to match PostgreSQL 17 settings.
Ensure logs and metrics are captured correctly.
Re-enable all required cron jobs.

Step 11: Stop and Remove PostgreSQL 14

Since PostgreSQL 14 is no longer needed, stop and remove it.

Stop PostgreSQL 14:

sudo systemctl stop postgresql@14-main

Stop pgBackRest for the stanza:

sudo -u postgres pgbackrest --stanza=main --log-level-console=info stop

Delete the stanza from one repository:

sudo -u postgres pgbackrest --stanza=main --repo=3 --log-level-console=info stanza-delete

Disable it from starting on boot:

sudo systemctl disable postgresql@14-main

Check if PostgreSQL 14 is still running:

pg_lsclusters

Uninstall PostgreSQL 14:

sudo apt-get remove --purge postgresql-14 postgresql-client-14 -y
sudo dpkg --purge postgresql-14

Verify PostgreSQL 14 packages are removed:

dpkg -l | grep postgresql

Ensure that only PostgreSQL 17 remains installed.

Remove PostgreSQL 14 Data Directory:

sudo rm -rf /var/lib/postgresql/14
sudo rm -rf /etc/postgresql/14
sudo rm -rf /var/log/postgresql/postgresql-14-main.log