Introduction
Upgrading PostgreSQL in production environments requires careful planning to avoid service disruptions. While traditional upgrade methods like pg_dump and pg_upgrade require downtime, logical replication enables zero-downtime PostgreSQL upgrades by maintaining a synchronized replica during the transition.
This comprehensive tutorial demonstrates how to upgrade from PostgreSQL 16 to PostgreSQL 17 on Ubuntu 24.04 LTS using logical replication with TLS encryption, complete cutover procedures, and failback capabilities. You’ll learn to minimize downtime while ensuring data integrity throughout the upgrade process.
Prerequisites
Before starting this tutorial, ensure you have:
- Two Ubuntu 24.04 LTS VPS instances (minimum 2 GB RAM, 2 vCPUs each)
- PostgreSQL 16 running on the primary server with existing data
- Root or sudo access on both servers
- Network connectivity between servers (ports 5432 and 5433)
- SSL certificates or ability to generate self-signed certificates
- Basic PostgreSQL administration knowledge
- Backup of existing data (see our PostgreSQL 16 backup tutorial)
Warning: Always test this procedure in a development environment before applying to production systems.
Step-by-Step Tutorial
Step 1: Configure PostgreSQL 16 for Logical Replication
On your primary server running PostgreSQL 16, enable logical replication:
sudo -u postgres psql -c "ALTER SYSTEM SET wal_level = 'logical';"
sudo -u postgres psql -c "ALTER SYSTEM SET max_replication_slots = 10;"
sudo -u postgres psql -c "ALTER SYSTEM SET max_wal_senders = 10;"
sudo systemctl restart postgresqlCreate a replication user with proper privileges:
sudo -u postgres psql << 'EOF'
CREATE ROLE replica_user WITH REPLICATION LOGIN PASSWORD 'secure_password_2025';
GRANT pg_read_all_data TO replica_user;
GRANT CREATE ON DATABASE your_database TO replica_user;
EOFConfigure SSL and update pg_hba.conf:
sudo -u postgres openssl req -new -x509 -days 365 -nodes -out /var/lib/postgresql/16/main/server.crt -keyout /var/lib/postgresql/16/main/server.key -subj "/CN=pg16-primary"
sudo chown postgres:postgres /var/lib/postgresql/16/main/server.*
sudo chmod 600 /var/lib/postgresql/16/main/server.key
echo "hostssl replication replica_user 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/16/main/pg_hba.conf
sudo systemctl reload postgresqlStep 2: Install PostgreSQL 17 on Secondary Server
On your secondary server, install PostgreSQL 17:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install -y postgresql-17 postgresql-client-17Configure PostgreSQL 17 with SSL:
sudo systemctl stop postgresql
# Configure PostgreSQL 17 on port 5432
sudo -u postgres openssl req -new -x509 -days 365 -nodes -out /var/lib/postgresql/17/main/server.crt -keyout /var/lib/postgresql/17/main/server.key -subj "/CN=pg17-secondary"
sudo chown postgres:postgres /var/lib/postgresql/17/main/server.*
sudo chmod 600 /var/lib/postgresql/17/main/server.key
sudo systemctl start postgresqlStep 3: Set Up Logical Replication
On the PostgreSQL 16 primary, create a publication:
sudo -u postgres psql your_database << 'EOF'
CREATE PUBLICATION pg16_to_pg17_pub FOR ALL TABLES;
SELECT * FROM pg_publication;
EOFCopy the database schema to PostgreSQL 17:
sudo -u postgres pg_dump --host=PRIMARY_SERVER_IP --port=5432 --username=replica_user --schema-only --no-owner --no-privileges your_database | sudo -u postgres psql -d your_databaseOn the PostgreSQL 17 secondary, create the subscription:
sudo -u postgres psql your_database << 'EOF'
CREATE SUBSCRIPTION pg16_to_pg17_sub
CONNECTION 'host=PRIMARY_SERVER_IP port=5432 dbname=your_database user=replica_user password=secure_password_2025 sslmode=require'
PUBLICATION pg16_to_pg17_pub
WITH (copy_data = true, create_slot = true);
EOFStep 4: Monitor Replication Progress
Monitor the initial data synchronization:
-- On PostgreSQL 17 (secondary)
sudo -u postgres psql your_database -c "
SELECT
subname,
pid,
received_lsn,
latest_end_lsn,
latest_end_time
FROM pg_stat_subscription;"
-- Check replication lag
sudo -u postgres psql your_database -c "
SELECT
CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
THEN 0
ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS lag_seconds;"
Step 5: Execute Zero-Downtime Cutover
When replication lag is minimal (< 1 second), perform the cutover:
#!/bin/bash
# Save as cutover.sh
echo "Starting PostgreSQL 16→17 cutover..."
# Step 1: Stop application traffic to PostgreSQL 16
echo "Stopping application connections..."
sudo -u postgres psql your_database -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'your_database' AND pid <> pg_backend_pid();"
# Step 2: Wait for final replication catch-up
echo "Waiting for replication sync..."
sleep 5
# Step 3: Promote PostgreSQL 17 to primary
echo "Promoting PostgreSQL 17..."
sudo -u postgres psql your_database -c "ALTER SUBSCRIPTION pg16_to_pg17_sub DISABLE;"
sudo -u postgres psql your_database -c "ALTER SUBSCRIPTION pg16_to_pg17_sub SET (slot_name = NONE);"
sudo -u postgres psql your_database -c "DROP SUBSCRIPTION pg16_to_pg17_sub;"
# Step 4: Update application connection strings
echo "Update your application to connect to PostgreSQL 17"
echo "Cutover completed successfully!"
Step 6: Configure Failback Capability
Prepare for potential failback by setting up reverse replication:
-- On PostgreSQL 17 (new primary)
sudo -u postgres psql your_database << 'EOF'
ALTER SYSTEM SET wal_level = 'logical';
SELECT pg_reload_conf();
CREATE PUBLICATION pg17_to_pg16_pub FOR ALL TABLES;
EOF
-- On PostgreSQL 16 (standby), create subscription for failback
sudo -u postgres psql your_database << 'EOF'
CREATE SUBSCRIPTION pg17_to_pg16_sub
CONNECTION 'host=SECONDARY_SERVER_IP port=5432 dbname=your_database user=replica_user password=secure_password_2025 sslmode=require'
PUBLICATION pg17_to_pg16_pub
WITH (copy_data = false, create_slot = true);
EOFStep 7: Post-Upgrade Validation
Verify the upgrade was successful:
-- Check PostgreSQL version
sudo -u postgres psql -c "SELECT version();"
-- Verify data integrity
sudo -u postgres psql your_database -c "SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables;"
-- Test application functionality
sudo -u postgres psql your_database -c "SELECT COUNT(*) FROM your_main_table;"
Best Practices
- Test thoroughly: Always perform complete testing in staging environments matching production
- Monitor replication lag: Keep lag under 1 second before cutover to minimize downtime
- Plan rollback procedures: Maintain reverse replication for quick failback if issues arise
- Application compatibility: Verify all applications work with PostgreSQL 17 features and syntax
- Security considerations: Use strong passwords, TLS encryption, and proper firewall rules
- Performance optimization: Update statistics and reindex after upgrade completion
- Backup strategy: Ensure comprehensive backup procedures are in place
Resource monitoring is crucial during upgrades. Consider implementing comprehensive observability with our observability stack tutorial for better insights into your database performance.
Conclusion
You’ve successfully performed a zero-downtime PostgreSQL upgrade using logical replication with TLS security, maintaining data integrity and service availability throughout the process. This method provides tremendous flexibility with built-in failback capabilities and minimal service disruption.
The logical replication approach offers superior control over the upgrade timeline compared to traditional methods, allowing you to verfiy data synchronization before committing to the cutover. With proper monitoring and testing, this technique can be applied to mission-critical production environments.
For hosting your upgraded PostgreSQL infrastructure, consider our high-performance VPS solutions in Singapore, Sydney, or Amsterdam with EPYC processors, NVMe storage, and advanced networking features designed for database workloads. Our platform provides the reliability and performance needed for demanding PostgreSQL deployments.
