Moving a PostgreSQL Server
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.
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.
This could involve multiple hosts, but we don’t want new data to be written to server as it will be lost.
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 file
oldpgdata.sql to 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.
Point your existing services at the new server instead of the old.
As usual, configure backups of the databases (see PiSQL guide, for example).
You should now be able to shut down the older server for good.