PostgreSQL High Availability with Patroni & etcd

Part 5: Security & Advanced

xdev.asia

Goal

After this lesson, you will:

• Plan and execute PostgreSQL major version upgrades
• Upgrade Patroni with zero downtime
• Use pg_upgrade for in-place upgrades
• Implement logical replication for upgrades
• Rollback failed upgrades safely

1. Upgrade Planning

1.1. Pre-upgrade checklist

☐ Review PostgreSQL release notes
☐ Check extension compatibility
☐ Test upgrade in staging environment
☐ Backup all data (full + WAL archive)
☐ Document current versions
☐ Schedule maintenance window
☐ Notify stakeholders
☐ Prepare rollback plan
☐ Verify disk space (need 2x current data size)
☐ Check for deprecated features in new version
☐ Update monitoring/alerting
☐ Prepare downtime communication

1.2. Version compatibility matrix

___HTMLTAG_97 ___Risk

From → To	Method	Downtime
17 → 18	pg_upgrade	Minutes___HTMLTAG _108___	Low
15 → 18	pg_upgrade	Minutes___HTMLTAG_ 118___	Medium
12 → 18	Logical replication	None	Medium
9.6 → 18	Dump/restore	Hours	High

1.3. Document current state

# PostgreSQL version
psql -c "SELECT version();"
Installed extensions
psql -c "\dx"
Database sizes
psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;"
Patroni version
patronictl version
etcd version
etcdctl version

2. PostgreSQL Minor Version Upgrade

2.1. Minor upgrade process (e.g., 18.0 → 18.1)

# Minor upgrades are easy - just update packages

On each node (one at a time):
1. Update packages
sudo apt-get update
sudo apt-get install --only-upgrade postgresql-18
2. Restart Patroni (will restart PostgreSQL)
sudo systemctl restart patroni
3. Verify new version
psql -c "SELECT version();"
Patroni handles failover automatically during restart

2.2. Rolling minor upgrade

# Upgrade replicas first
for node in node2 node3; do
echo "Upgrading $node..."
ssh $node "sudo apt-get update && sudo apt-get install -y --only-upgrade postgresql-18"
ssh $node "sudo systemctl restart patroni"
sleep 30  # Wait for replica to catch up
done

Switchover to upgraded replica
patronictl switchover postgres-cluster --leader node1 --candidate node2
Upgrade old leader (now replica)
ssh node1 "sudo apt-get update && sudo apt-get install -y --only-upgrade postgresql-18"
ssh node1 "sudo systemctl restart patroni"

3. PostgreSQL Major Version Upgrade with pg_upgrade

3.1. Architecture

Before (PostgreSQL 17):
node1 (17, Leader)
node2 (17, Replica)
node3 (17, Replica)

During upgrade:
node1 (17, Leader) ← Still serving traffic
node2 (18, NEW) ← Upgrading
node3 (17, Replica)
After upgrade:
node1 (18, Leader) ← Upgraded
node2 (18, Replica)
node3 (18, Replica)

3.2. Install new PostgreSQL version

# Install PostgreSQL 18 alongside 17
sudo apt-get install -y postgresql-18 postgresql-18-contrib

Both versions now installed:
/usr/lib/postgresql/17/
/usr/lib/postgresql/18/

3.3. Prepare for upgrade (node2 - first replica)

# 1. Stop Patroni on node2
sudo systemctl stop patroni

2. Create new data directory for v18
sudo mkdir -p /var/lib/postgresql/18/data
sudo chown postgres:postgres /var/lib/postgresql/18/data
3. Initialize new cluster
sudo -u postgres /usr/lib/postgresql/18/bin/initdb 

-D /var/lib/postgresql/18/data 

--encoding=UTF8 

--data-checksums
4. Run pg_upgrade
sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade 

--old-datadir=/var/lib/postgresql/17/data 

--new-datadir=/var/lib/postgresql/18/data 

--old-bindir=/usr/lib/postgresql/17/bin 

--new-bindir=/usr/lib/postgresql/18/bin 

--check  # Dry run first!
If check passes, run actual upgrade:
sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade 

--old-datadir=/var/lib/postgresql/17/data 

--new-datadir=/var/lib/postgresql/18/data 

--old-bindir=/usr/lib/postgresql/17/bin 

--new-bindir=/usr/lib/postgresql/18/bin 

--link  # Use hard links (faster)
Expected output:
Performing Consistency Checks
-----------------------------
...
Upgrade Complete
----------------

3.4. Update Patroni configuration for v18

# /etc/patroni/patroni.yml
postgresql:
bin_dir: /usr/lib/postgresql/18/bin  # Changed from 17
data_dir: /var/lib/postgresql/18/data  # Changed from 17

... rest of config

# Start Patroni with new version
sudo systemctl start patroni

Verify node2 is now running v18
psql -h node2 -U postgres -c "SELECT version();"

3.5. Upgrade remaining nodes

# Repeat process for node3
ssh node3 "sudo systemctl stop patroni"
ssh node3 "# ... same pg_upgrade steps ..."
ssh node3 "sudo systemctl start patroni"

Finally, upgrade node1 (current leader)
Switchover to node2 first
patronictl switchover postgres-cluster --leader node1 --candidate node2
Now upgrade node1
ssh node1 "sudo systemctl stop patroni"
ssh node1 "# ... same pg_upgrade steps ..."
ssh node1 "sudo systemctl start patroni"
All nodes now on v18!
patronictl list

3.6. Post-upgrade tasks

# Run generated optimize scripts
sudo -u postgres ./analyze_new_cluster.sh
sudo -u postgres ./reindex_hash.sh  # If upgrading from < 10

Update extensions
psql -c "ALTER EXTENSION pg_stat_statements UPDATE;"
Vacuum analyze all databases
vacuumdb --all --analyze-in-stages
Remove old cluster (after verifying everything works!)
sudo -u postgres ./delete_old_cluster.sh

4. Zero-Downtime Upgrade with Logical Replication

4.1. Architecture

Production (v17):
node1 (v17, Leader) ← Serving traffic
↓ Logical replication
New cluster (v18):
node4 (v18, Leader) ← Receiving changes
node5 (v18, Replica)

After cutover:
Application → node4 (v18) ← New primary

4.2. Setup new v18 cluster

# Install PostgreSQL 18 on new servers

Setup Patroni cluster (node4, node5, node6)
See previous lessons for installation
Verify new cluster
patronictl -c /etc/patroni/patroni-v18.yml list

4.3. Create publication on v17 (source)

-- On node1 (v17 leader)
CREATE PUBLICATION pg17_to_pg18 FOR ALL TABLES;

-- Or specific tables:
-- CREATE PUBLICATION pg17_to_pg18 FOR TABLE users, orders, products;
-- Verify
SELECT * FROM pg_publication;

4.4. Create subscription on v18 (target)

-- On node4 (v18 leader)
CREATE SUBSCRIPTION pg18_from_pg17
CONNECTION 'host=node1 port=5432 dbname=myapp user=replicator password=rep_pass'
PUBLICATION pg17_to_pg18
WITH (copy_data = true, create_slot = true);

-- Monitor initial sync
SELECT * FROM pg_stat_subscription;
-- Wait for initial data copy to complete
-- subname     | pg18_from_pg17
-- received_lsn | 0/3000000
-- ...

4.5. Monitor replication lag

-- On v17 (source)
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_name LIKE '%pg18%';

-- On v18 (target)
SELECT subname,
received_lsn,
latest_end_lsn,
pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn, received_lsn)) AS lag
FROM pg_stat_subscription;

4.6. Cutover procedure

# 1. Stop writes to v17 (put app in maintenance mode)

Or set database to read-only:
psql -h node1 -U postgres -c "ALTER SYSTEM SET default_transaction_read_only = on;"
psql -h node1 -U postgres -c "SELECT pg_reload_conf();"
2. Wait for replication to catch up
psql -h node4 -U postgres -c "SELECT pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn, received_lsn)) FROM pg_stat_subscription;"
Should be 0 bytes
3. Disable subscription on v18
psql -h node4 -U postgres -c "ALTER SUBSCRIPTION pg18_from_pg17 DISABLE;"
4. Drop subscription (optional, after confirming everything works)
psql -h node4 -U postgres -c "DROP SUBSCRIPTION pg18_from_pg17;"
5. Update application connection strings to point to node4
6. Enable writes on v18
psql -h node4 -U postgres -c "ALTER SYSTEM SET default_transaction_read_only = off;"
psql -h node4 -U postgres -c "SELECT pg_reload_conf();"
7. Verify application works on v18
8. Keep v17 cluster running for rollback (1-2 weeks)

5. Patroni Version Upgrade

5.1. Check compatibility

# Check current Patroni version
patronictl version

Check PostgreSQL compatibility
Patroni 3.2.0+ supports PostgreSQL 18
See: https://github.com/zalando/patroni/releases

5.2. Upgrade Patroni (Python package)

# On each node:

1. Upgrade via pip
sudo pip3 install --upgrade patroni[etcd]
Or specific version:
sudo pip3 install patroni[etcd]==3.2.2
2. Verify new version
patronictl version
3. Restart Patroni service
sudo systemctl restart patroni
No downtime - Patroni handles failover automatically

5.3. Rolling Patroni upgrade

# Upgrade replicas first
for node in node2 node3; do
echo "Upgrading Patroni on $node..."
ssh $node "sudo pip3 install --upgrade patroni[etcd]"
ssh $node "sudo systemctl restart patroni"
sleep 10
done

Switchover to upgraded replica
patronictl switchover postgres-cluster --leader node1 --candidate node2
Upgrade old leader
ssh node1 "sudo pip3 install --upgrade patroni[etcd]"
ssh node1 "sudo systemctl restart patroni"

6. etcd Upgrade

6.1. etcd minor upgrade

# On each etcd node:
sudo systemctl stop etcd
sudo apt-get update
sudo apt-get install --only-upgrade etcd
sudo systemctl start etcd

Verify cluster health
etcdctl endpoint health

6.2. etcd major upgrade (e.g., 3.4 → 3.5)

# Follow official etcd upgrade guide

https://etcd.io/docs/latest/upgrades/
Key steps:
1. Backup etcd data
etcdctl snapshot save backup.db
2. Upgrade one member at a time
3. Verify cluster health after each upgrade
4. Update Patroni to use new etcd API version

7. Rollback Strategies

7.1. Rollback pg_upgrade

# Before deleting old cluster, you can rollback

1. Stop Patroni
sudo systemctl stop patroni
2. Restore old configuration
/etc/patroni/patroni.yml
postgresql:
bin_dir: /usr/lib/postgresql/17/bin  # Back to 17
data_dir: /var/lib/postgresql/17/data
3. Start Patroni
sudo systemctl start patroni
Old cluster resumes operation

7.2. Rollback logical replication

# If cutover to v18 fails, rollback to v17

1. Stop application
2. Set v17 to read-write
psql -h node1 -U postgres -c "ALTER SYSTEM SET default_transaction_read_only = off;"
psql -h node1 -U postgres -c "SELECT pg_reload_conf();"
3. Update application connection strings to v17
4. Resume normal operations
Note: Any writes to v18 during cutover will be LOST!
Consider setting up reverse replication v18 → v17 if needed

7.3. Rollback Patroni upgrade

# Downgrade Patroni if upgrade causes issues

sudo pip3 install patroni[etcd]==3.1.2  # Previous version
sudo systemctl restart patroni

8. Testing Upgrades

8.1. Staging environment test

# 1. Clone production to staging
pg_basebackup -h prod-leader -D /var/lib/postgresql/staging -X stream

2. Perform upgrade in staging
... follow upgrade procedures ...
3. Run application tests
... smoke tests, integration tests ...
4. Benchmark performance
pgbench -i -s 100 myapp
pgbench -c 10 -j 2 -t 1000 myapp
5. Document issues and timings

8.2. Upgrade rehearsal

# Practice upgrade multiple times

Time each step
Identify bottlenecks
Refine procedures
Example timing log:
Step 1: Stop Patroni - 5 seconds
Step 2: pg_upgrade --check - 30 seconds
Step 3: pg_upgrade - 10 minutes
Step 4: Start Patroni - 15 seconds
Step 5: Replication catchup - 2 minutes
Total: ~13 minutes

9. Best Practices

✅ DO

1. Test in staging first - Multiple times
2. Backup everything - Full backup + WAL archive_
3. Use pg_upgrade --check - Catch issues early
4. Document procedures - Step-by-step runbook_
5. Schedule maintenance window - Off-peak hours
6. Monitor closely - During and after upgrade
7. Keep old version - Don't delete for 1-2 weeks
8. Use logical replication - For zero-downtime
9. Upgrade extensions - After PostgreSQL upgrade
10. Vacuum analyze - After major upgrade

❌ DON'T

1. Don't skip backups - Critical safety net
2. Don't upgrade all at once - Rolling upgrades
3. Don't delete old cluster immediately - Keep for rollback_
4. Don't ignore release notes - Breaking changes
5. Don't skip testing - Staging is essential_
6. Don't upgrade during peak hours - Plan maintenance window
7. Don't forget about extensions_ - May need updates

10. Lab Exercises

Lab 1: Minor version upgrade

Tasks:

1. Check current PostgreSQL version
2. Update packages on replica
3. Restart Patroni on replica_
4. Switchover to upgraded replica_
5. Upgrade old leader

Lab 2: Major version upgrade with pg_upgrade

Tasks:

1. Install PostgreSQL 18 on all nodes
2. Run pg_upgrade --check on replica
3. Perform pg_upgrade on replica
4. Update Patroni configuration
5. Complete rolling upgrade

Lab 3: Zero-downtime upgrade with logical replication

Tasks:

1. Setup new v18 cluster
2. Create publication on v17_
3. Create subscription on v18
4. Monitor replication lag_
5. Perform cutover_
6. Verify application functionality

Lab 4: Rollback procedure

Tasks:

1. Simulate failed upgrade
2. Stop Patroni on all nodes
3. Restore old configuration
4. Restart old cluster_
5. Verify rollback successful_

11. Summary

Upgrade Methods Comparison

__ _HTMLTAG_387___Minutes___HTMLTAG_411__ _Hours

Method	Downtime_ __HTMLTAG_374___	Complexity	Risk	Use Case
pg_upgrade	Low	Low_	Minor version jumps
Logical replication	None	High	Medium	Zero-downtime required
Dump/restore	Low	Low	Ancient versions
pg_upgrade --link	Seconds	Medium	Medium	Same server upgrade

Typical Timeline

Week 1-2: Planning and preparation
  - Review release notes
  - Test in staging
  - Document procedures
Week 3: Dress rehearsal

Full upgrade test in staging
Time each step
Identify issues

Week 4: Production upgrade

Execute during maintenance window
Monitor closely
Be ready to rollback

Week 5-6: Stabilization

Monitor for issues
Performance tuning
Keep old cluster for safety

Week 7+: Cleanup

Delete old cluster
Update documentation
Post-mortem review

Upgrade Checklist_

Pre-upgrade:
☐ Full backup completed
☐ Staging test successful
☐ Release notes reviewed
☐ Maintenance window scheduled
☐ Rollback plan documented
☐ Stakeholders notified

During upgrade:
☐ Backups verified
☐ pg_upgrade --check passed
☐ Upgrade completed
☐ Replication working
☐ Application connectivity verified
Post-upgrade:
☐ Extensions updated
☐ Vacuum analyze completed
☐ Performance validated
☐ Monitoring updated
☐ Documentation updated

Next Steps_

Lesson 25 will cover Real-world Case Studies:

• Production architecture examples
• Scaling to 1000+ queries/second
• Cost optimizationtechniques
• Lessons learned from failures
• Industry-specific implementations