Jan 202020

FreshPorts runs on a FreeBSD server which hosts multiple jails. Two of these jails run PostgreSQL server. When upgrading from one version of PostgreSQL to another, we run pg_dump in the new jail, and load the backup into that database server.

I’m writing this blog post to keep track of this procedure so I do not have to remember it each time.

take website offline

sudo mv mv offline.conf.disabled offline.conf && \
sudo mv freshports.org.conf freshports.org.conf.disabled && \
sudo service nginx restart


Assuming we are dumping from the server on, I run this command on the other server, the destination:

time pg_dump -j 28 -h -Fd freshports.org -f freshports.dump
time pg_dump -j 28 -h -Fd fpphorum       -f fpphorum.dump
time pg_dump -j 28 -h -Fd fsphorum       -f fsphorum.dump

While waiting, get global.sql and the receiving database prepared.


We are installing on

createdb -h -T template0 -E SQL_ASCII freshports.org
createdb -h -T template0 -E SQL_ASCII fpphorum
createdb -h -T template0 -E SQL_ASCII fsphorum


Get a copy of globals from the older server:

pg_dumpall -h --globals-only > globals.sql

Load that up into the new server:

psql  -h template1 < globals.sql

Restore the data

time pg_restore -h -j 28 -d freshports.org freshports.dump
time pg_restore -h -j 28 -d fpphorum       fpphorum.dump
time pg_restore -h -j 28 -d fsphorum       fsphorum.dump

move IP address

The IP addresses have to be changed in postgresql.conf:

listen_addresses = ','

The production server is always on

I found it easier to move the IP address than it was to update it on the ingress (commit processing) and web server (website).


On ansible, I update these two files:

cd roles/postgresql-server/templates/hosts/
joe x8dtu-pg0*/postgresql.conf.j2

I swap between these two states:

listen_addresses = '{{ postgresql_address }}'
listen_addresses = '{{ postgresql_address }},'

Then I update the servers with this command:

ansible-playbook jail-postgresql.yml --tags=pg_hba,postgresql.conf \

Then I have to adjust the IP addresses on the jails:

sudo iocage stop x8dtu-pg01
sudo iocage stop x8dtu-pg02
sudo iocage set ip4_addr="," x8dtu-pg01
sudo iocage set ip4_addr=",," x8dtu-pg02
sudo iocage start x8dtu-pg01
sudo iocage start x8dtu-pg02

put website online

sudo mv mv offline.conf offline.conf.disabled && \
sudo mv freshports.org.conf.disabled freshports.org.conf && \
sudo service nginx restart

Enable the rsyncer user

The rsyncer user does the database backups and rsyncs it to the backup server.

For this, I create a read-only user and I documented that for future use. If I do not do this correctly, the backup will fail with this error:

pg_dumpall: error: query failed: ERROR:  permission denied for table pg_authid
Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive