Dumping PostgreSQL 9.4 to upgrade to PostgreSQL 9.6

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.


Installed packages to be REMOVED:

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

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 -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 -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

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive

Leave a Comment

Scroll to Top