FreeBSD 10.3 & PostgreSQL 9.4 on the old server, and FreeBSD 11.1 & PostgreSQL 9.6 on the new server.
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.
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 126.96.36.199 -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