In this article you will move your database over to a new server, with only a few seconds of downtime.
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.
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.
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:
old_database_address with the address of the old database server.
Then edit the file
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
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
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_addressshould be the IP address of the old database.
new_database_addressshould 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';\""
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
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.
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:
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
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:
--pgdataswitch 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-confcreates a default
recovery.conffile when finished that makes setting up the server as a Hot Standby node easier.
--usernamelets you set the user name you created when creating the new role.
--hostallows you to specify the hostname of the original server.
--xlog-methodensures 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.
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
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.
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:
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.
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.