Moving a PostgreSQL server
Preface
At some point you may need to upsize your PostgreSQL server, particular if you have implemented one on a old Raspberry Pi. This article discusses moving your server to a new home.
Create the new server
Before you can move the old data to a new server, you need a new server. The PiSQL article is mostly applicable (although you will do your initial base OS configuration different than from a Raspberry Pi setup) if you are using Debian or Ubuntu to host the new server, so use that, or the guide of your choice, to stand up a new PostgreSQL server.
Transfer the old server’s data to the new
Stop all services that use the old server
This could involve multiple hosts, but we don’t want new data to be written to server as it will be lost.
Dump the old data
On the old server:
sudo su -- Change to a directory with a large amount of space.
sudo -u postgres pg_dumpall -c --if-exists >oldpgdata.sql
Copy the old data to the new server
Copy the file oldpgdata.sql to the new server.
‘Restore’ the data into the new server
sudo -u postgres psql -f oldpgdata.sql postgres 2>&1 | tee restore.log
Check the restore.log for any relevant errors (non-existing databases to drop, ‘postgres’, and ’template1’ already existing are expected). If there are none, you should be able use the databases.
Use the new server
Point your existing services at the new server instead of the old.
Configure backups
As usual, configure backups of the databases (see PiSQL guide, for example).
Decommission the old server
You should now be able to shut down the older server for good.