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
  • old_database_address should be the IP address of the old database.
  • new_database_address should be the IP address of your new database server
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
  • --pgdata switch specifies the directory where you want the data to be saved, the value has been set to the default directory where PostgreSQL databases are stored.
  • --write-recovery-conf creates a default recovery.conf file when finished that makes setting up the server as a Hot Standby node easier.
  • --username lets you set the user name you created when creating the new role.
  • --host allows you to specify the hostname of the original server.
  • --xlog-method ensures that all the Write Ahead Log files are copied along with the backup.

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