How to Change PostgreSQL Server with Minimal Downtime

Introduction

In this article you will move your database over to a new server, with only a few seconds of downtime.

Prerequisites

Before you begin this guide you'll need the following:

• One Ubuntu 20.04 database server running PostgreSQL by following How To Install and Use PostgreSQL on Ubuntu 20.04.

Step 1 - Creating a server

The first step to migrating to a new server, is of course to create a new server instance. You can go through How To Install and Use PostgreSQL on Ubuntu 20.04 again in order to set up the new database server you just created.

Step 2 - Preparing the old database for replication

In order to transfer all the database data to the new server without any data loss or significant down time, you can configure the new server as a Hot Standby node and then shut down the old server and promote the new server to the primary node when ready.

To do this you must first SSH into the original server with the following command:

ssh root@old_database_address

Replace old_database_address with the address of the old database server.

Then edit the file /etc/postgresql/postgresql_version/main/postgresql.conf, replacing postgresql_version with the minor version number running on your server.

This can be found by running psql --version. For example, for psql (PostgreSQL) 9.6.17 postgresql_version will be replaced with 9.6

Append the following lines to the file:

wal_level = hot_standby
hot_standby = on
max_wal_senders = 3
max_replication_slots = 3

The first line will cause the Write Ahead Log to log information required to run read-only queries on a Hot Standby server. The second line will enable the server to act as a Hot Standby node. This isn't needed yet, but you will be making the same changes to the new server later and it is easier to copy the same configuration rather than making minor changes to each.

Next, you need to allow access to the new database server for backups, and for the web server by editing /etc/postgresql/postgresql_version/main/pg_hba.conf, replacing postgresql_version as before.

Add the following lines:

host replication replica old_database_address/32 md5
host replication replica new_database_address/32 md5

Replace the values as follows:

• 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

Finally, a new user needs to be created for the new server to be able to access:

su - postgres -c "psql -c \"CREATE ROLE replica REPLICATION LOGIN ENCRYPTED PASSWORD 'topsecretpassword';\""

Replace topsecretpassword with a secure password of your choice.

This will run an SQL command as the postgres user, which will create a new role.

Once these changes have been added, the PostgreSQL server should be restarted:

systemctl restart postgresql.service

You should then make the exact same changes to the new database server by repeating step 3 but instead of connecting to the old database you should connect to the new database server.

This can be done by running the following instead of ssh root@old_database_address:

ssh root@new_database_address

Replace new_database_address with the IP address of your new database server.

Now that both databases have been prepared to handle replication, it is time to actually replicate the data on the new database.

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

In this step you will set up the new database instance to become a replica of the old server, using the built in Hot Standby node in PostgreSQL. To begin you must log into the new server with the following command:

ssh root@new_database_address

Replace new_database_address with the IP address of the database server.

In order for a Hot Standby node to receive the data from the original database, first, all its data must be deleted. This can be done by stopping the postgres instance and deleting the /var/lib/postgres/postgresql_version/main/ directory:

systemctl stop postgresql.service
rm -rf /var/lib/postgresql/postgresql_version/main

As always, replace postgresql_version with your version of the postgres database.

The first command will stop the PostgreSQL service from running, and the second will delete all the database data so that it can load the data from the original system.

Now you can copy the original databases to the new server:

su - postgres
pg_basebackup --pgdata=/var/lib/postgresql/postgresql_version/main/ --write-recovery-conf --username=replica --host=old_database_address --xlog-method=stream

Don't forget to change the postgresql_version to your version of PostgreSQL and old_database_address to the address of the original database.

The first command will make you the postgres user, and the second command will copy the data from the original server.

This command has a few switches which need to be set:

• --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.

Once you execute this command you should be prompted for the password you created earlier when creating the replica role.

Note: If this command hangs it could be because there is an internal firewall rule set on the original server preventing access. This can be fixed by whitelisting the IP address added to the pg_hba.conf file earlier, depending on the original servers firewall settings.

Now that the new server has been set up as a replica you need to point the web app to the new database server and make the new database server the primary node.

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

Note: At this point you have to change the database address on any websites to the new database address and temporarily shut it down

Now, you must stop the original database server and promote the old one:

ssh root@old_database_address
systemctl stop postgresql

As always replace old_database_address with the address of your old database.

This will stop your original database server.

Next promote the new database server to master. This will make it the primary server so that it can start writing to the database:

ssh root@new_database_address
su - postgres
pg_ctlcluster postgresql_version main promote

Again, replace new_database_address with the IP address of the new database, and also replace postgresql_version with the version of postgres running on the server.

Now that the old database has been stopped and the new one has been promoted to master, all that needs to be done is to restart any sites which depend on the database.

Now you should be able to visit your websites and everything should be running as normal.

Step 5 - Cleaning up

Once you are satisfied that everything is working, you can shut down your servers on your old cloud provider and update the white listed addresses in PostgreSQL so that only your new server has access.

To begin, SSH into your database server again:

ssh root@new_database_address

Replace new_database_address with the IP address of your new database server.

Next you can edit the pg_hba.conf file and remove the old database and address. Once done editing the file should look something like this:

local all postgres peer
local all all peer

Now that everything is done, you can safely delete the original databases. Once that is done you have completely finished migrating to a new database server.

Conclusion

In this article you learned how to migrate your database from server to another with minimal down time. This process consisted of copying a database over to the new server using a Hot Standby node, then switching the primary database server, and shutting down the old database server.