FreeBSD 10.3 & PostgreSQL 9.4 on the old server, and FreeBSD 11.1 & PostgreSQL 9.6 on the new server.
As part of the move from the old FreshPorts server (photos) to the new FreshPorts server, I’ll need to dump the database and copy it over.
What? No zfs send?
There is no ZFS on the old server.
The rule of thumb when upgrading a PostgreSQL database is to dump with the pg_dump client from the newer version.
How do you get that? You install it.
But:
Installed packages to be REMOVED: postgresql94-contrib-9.4.14 New packages to be INSTALLED: postgresql96-client: 9.6.5 [local]
While this may be fine, I would rather not risk it in production.
Instead, I will create a jail on the old server, install postgresql96-client into there, and proceed with pg_dump.
Creating the jail
I’m using ezjail for this.
sudo ezjail-admin create pg01 10.30.0.10
Starting the jail
This is also easy:
[dan@supernews:~] $ sudo ezjail-admin start pg01 Cannot 'start' ezjail. Set ezjail_enable to YES in /etc/rc.conf or use 'onestart' instead of 'start'. Error: Could not start pg01. You need to start it by hand.
Oh, let’s try this instead:
[dan@supernews:~] $ sudo ezjail-admin onestart pg01 Starting jails: pg01. /etc/rc.d/jail: WARNING: Per-jail configuration via jail_* variables is obsolete. Please consider to migrate to /etc/jail.conf. [dan@supernews:~] $
Installing PostgreSQL client
pkg install postgresql96-client
Running the dump
I started this in a tmux session, in case my ssh session is disconnected.
time pg_dump -h 10.0.0.1 -Fc -U dan freshports.org > freshports.org.9.6.dump
Then I waited.
No, I didn’t. I went out for beer & pizza chilli and fries.
The next morning
The next morning, I found this waiting for me:
root@pg96:~ # time pg_dump -h 206.127.23.226 -Fc -U dan freshports.org > freshports.org.9.6.dump 1330.866u 40.923s 33:52.41 67.4% 463+178k 67+24970io 1pf+0w
OK, it takes 34 minutes to dump the database
