x8dtu contains two jails for database servers: x8dtu-pg01 and x8dtu-pg02. At any given time, one is the production database server, and contains the IP address 127.1.54.32.

Let’s assume x8dtu-pg02 is on PostgreSQL 10.6 and we are upgrading to PostgreSQL 11.1 on x8dtu-pg01

The new packages would be installed on x8dtu-pg01. pg_dump would be run on x8dtu-pg01 (so we are using the newer code for dumping, standard PostgreSQL procedure) and would connect to the other server to get the data. For example:

[postgres@x8dtu-pg01 /var/db/DUMPING]$ pg_dumpall --globals -h 127.1.0.203 > globals.sql
[postgres@x8dtu-pg01 /var/db/DUMPING]$ psql template1 < /var/db/DUMPING/globals.sql
[postgres@x8dtu-pg01 /var/db/DUMPING]$ time pg_dump -j 14 -Z0 -Fd -h 127.1.0.203 freshports.org -f freshports.dump                                                                 

real    10m14.185s
user    1m21.128s
sys     0m24.430s

We create the new database and restore the database:

[postgres@x8dtu-pg01 /var/db/DUMPING]$ createdb freshports.org -E SQL_ASCII -T template0                                                                                           
[postgres@x8dtu-pg01 /var/db/DUMPING]$ time pg_restore -j 14 -d freshports.org /var/db/DUMPING/freshports.dump                                                                     

real    27m32.382s
user    0m10.793s
sys     0m44.011s
[postgres@x8dtu-pg01 /var/db/DUMPING]$ 

NOTE: the following procedure is untested. When it was written, 127.1.54.32 was not in use by any jail. This procedure will be updated when the next database upgrade occurs.

Add the IP address as an alias on the host:

[dan@x8dtu:~] $ sudo ifconfig lo1 alias 127.1.54.32/32

This is typical:

[dan@x8dtu:~] $ ifconfig lo1
lo1: flags=8049 metric 0 mtu 16384
	options=600003
	inet6 fd00::201 prefixlen 8 
	inet6 fe80::1%lo1 prefixlen 64 scopeid 0x5 
	inet 127.1.0.200 netmask 0xffffffff 
	inet 127.1.0.201 netmask 0xffffffff 
	inet 127.1.0.202 netmask 0xffffffff 
	inet 127.1.0.203 netmask 0xffffffff 
	inet 127.1.0.204 netmask 0xffffffff 
	inet 127.1.54.32 netmask 0xffffffff 
	nd6 options=21
	groups: lo 
[dan@x8dtu:~] $ 

Once the database is ready, we need to move the IP address.

Get the existing IP addresses. We use iocage for managing jails.

[dan@x8dtu:~] $ iocage get ip4_addr x8dtu-pg01
10.100.0.200,127.1.0.200

Add the production IP address:

[dan@x8dtu:~] $ sudo iocage set ip4_addr="10.100.0.200,127.1.0.200,127.1.54.32" x8dtu-pg01
Property: ip4_addr has been updated to 10.100.0.200,127.1.0.200,127.1.54.32

The jail needs to be restarted to grab the new IP address:

[dan@x8dtu:~] $ sudo iocage restart x8dtu-pg01

In ansible, update postgresql_address in these files.

1. host_vars/x8dtu-pg02.vpn.unixathome.org
2. host_vars/x8dtu-pg01.vpn.unixathome.org

Deploy those changes:

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

Ensure PostgreSQL is listening on the right IP addresses:

[dan@x8dtu-pg01:~] $ sockstat -p 5432 -4
USER     COMMAND    PID   FD PROTO  LOCAL ADDRESS         FOREIGN ADDRESS      
postgres postgres   19935 3  tcp4   127.1.0.200:5432      *:*
postgres postgres   19935 5  tcp4   127.1.54.32:5432      *:*

Now that the database server is listening on the new addresses, we can adjust the clients to use that newly-assigned IP address. See Swapping database servers.