Sign in

How to Change PostgreSQL Server with Minimal Downtime

Introduction

Prerequisites

Step 1 — Creating a server

Step 2 — Preparing the old database for replication

ssh root@old_database_address
wal_level = hot_standby
hot_standby = on
max_wal_senders = 3
max_replication_slots = 3
host replication replica old_database_address/32 md5
host replication replica new_database_address/32 md5
su - postgres -c "psql -c \"CREATE ROLE replica REPLICATION LOGIN ENCRYPTED PASSWORD 'topsecretpassword';\""
systemctl restart postgresql.service
ssh root@new_database_address

Step 3 — Creating the Hot Standby node and transferring the database data

ssh root@new_database_address
systemctl stop postgresql.service
rm -rf /var/lib/postgresql/postgresql_version/main
su - postgres
pg_basebackup --pgdata=/var/lib/postgresql/postgresql_version/main/ --write-recovery-conf --username=replica --host=old_database_address --xlog-method=stream

Step 4 — Switching the primary database node to the new database server

ssh root@old_database_address
systemctl stop postgresql
ssh root@new_database_address
su - postgres
pg_ctlcluster postgresql_version main promote

Step 5 — Cleaning up

ssh root@new_database_address
local all postgres peer
local all all peer

Conclusion

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store