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

pg_dump

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

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

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

createdb

We are installing on 127.1.0.203:

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

globals.sql

Get a copy of globals from the older server:

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

Load that up into the new server:

psql  -h 127.1.0.203 template1 < globals.sql

Restore the data

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

move IP address

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

listen_addresses = '127.1.0.203,127.1.54.32'

The production server is always on 127.1.54.32.

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

ansible

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 }}, 127.1.54.32'

Then I update the servers with this command:

ansible-playbook jail-postgresql.yml --tags=pg_hba,postgresql.conf \
   --limit=x8dtu-pg02.vpn.unixathome.org,x8dtu-pg01.vpn.unixathome.org

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="10.100.0.200,127.1.0.200" x8dtu-pg01
sudo iocage set ip4_addr="10.100.0.203,127.1.0.203,127.1.54.32" 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

Migrating FreshPorts from one db server to another