migrate postgres db from one node to another

descr

we are here migrating a database from psql v15.12 to psql v17.11

I suppose it’s less error-prone to use the target client version, as it matches the target server version. this is why we proceed with the migration on the new server, storing the dump locally.

otherwise it’s probably best to use same client version as target server version anyhow.

requirements

check the version of source and destination

psql --version

connect to the new node and make sure you can reach old node’s databases

user=netbox
db=netbox-dev

old_srv=x.x.x.x

# standalone server
new_srv=127.0.0.1

# on a cluster, you need to point to the lbs anyway
new_srv=x.x.x.x

nmap -p 5432 $old_srv
psql -U $user -h $old_srv -d $db

\dt

^D

prepare user and database

# standalone
#sudo -u postgres psql
su - postgres -c psql

# cluster
nmap -p 5432 $new_srv
psql -U postgres -h $new_srv 

-- drop database "netbox-dev";
create database "netbox-dev";

-- drop user netbox;
create user netbox with password 'PASSWORD-HERE';
grant all on database "netbox-dev" to netbox;
alter database "netbox-dev" owner to netbox;

^D

check you can now connect as this user

psql -U $user -h $new_srv -d $db

^D

migrate

df -hT
cd /opt/

mkdir MIGRATE-$db-`date +%F`/
cd MIGRATE-$db-`date +%F`/

backup

time nice pg_dump -U $user -h $old_srv -d $db -F c -f $db-`date +%F`.dump

restore

time nice pg_restore -U $user -h $new_srv -d $db -F c $db.dump

clean-up

eventually erase the old database

ssh OLD_PSQL_SERVER

su - postgres -c psql

drop database "netbox-dev";