As part of the migration of my MacOS Server to Linux the next service to migrate is my PostgreSQL engine. Although PostgreSQL had already been hidden in MacOS Server for some time, it still was included (as internal services like ProfileManager and Calendar and Addressbook Server depend on it. Despite it being hidden, I had still enabled it (manually) and hosted my PostgreSQL databases on my MacOS Server for ages. Despite migrations sometimes being a pain (i.e. not automatic) this worked well so far, including integrating it with the MacOS Server way of using transaction logs for offline backups. (so I will also have to look for a new way to do this).
Modifying Debian’s PostgreSQL Systemd service for IPv6 DAD
Although the installation of PostgreSQL is something I won’t cover in this series (it’s as simple as
apt-get postgresql), I had to modify it slightly to work in my environment. On my internal network I prefer to use IPv6 addresses where possible and assign a separate IPv6 address to each service to make it easy to move services around. For this reason, I needed PostgreSQL to listen to a specific IPv6 address (and I want it to only listen to that address). In theory this is as easy as modifying
/etc/postgresql/9.6/main/postgresql.conf so that it contains:
listen_addresses = 2001:XXXX:YYYY:ZZZZ::db
This worked right away after restarting the service though after a reboot, PostgreSQL would not start by itself. In
/var/log/postgresql/postgresql-9.6-main.log I noticed:
2018-04-14 14:27:28.109 CEST  LOG: could not bind IPv6 socket: Cannot assign requested address 2018-04-14 14:27:28.109 CEST  HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2018-04-14 14:27:28.110 CEST  WARNING: could not create listen socket for "2001:XXXX:YYYY:ZZZZ::db" 2018-04-14 14:27:28.110 CEST  FATAL: could not create any TCP/IP sockets 2018-04-14 14:27:28.110 CEST  LOG: database system is shut down
After quite some searching I learned that although the network interface is up, the IPv6 addresses were not fully initiated and hence when PostgreSQL was started, the IPv6 address was not yet ready as the IPv6 protocol stack was still busy checking whether the IP address was not in use (IPv6 DAD – Dead Address Detection). Making the service dependant on the network being up with:
sudo systemctl add-wants postgresql\@.service network-online.target network.target
as suggested in some articles (beats me why it isn’t yet) did not resolve the issue. The only way I was able to work around this was to change the systemd service file
/lib/systemd/system/postgresql@.service and add the following statements in the
Restart = on-failure RestartSec = 10s StartLimitBurst = 3
Which will try to restart the PostgreSQL 3 times with 10 second intervals if it fails and ensured the engine would start automatically after a reboot despide the IPv6 DAD taking time. The service file did mention that
pg_ctlcluster ... stop may no longer work, but so far with my settings I have not been able to reproduce that during my tests (it behaved as expected).
Migrating the data with
Since there was a difference in Postgres versions between MacOS Server and Debian (9.4 vs. 9.6) I decided not to attempt any in-place upgrade but do a dump of my PostgreSQL data using the
pg_dumpall command and import that on the new server. To ensure that I was not carrying along legacy settings and data I did cleanup the import a bit along the way. The steps I performed were:
- First take down the current production database and change it’s external address so that it is no longer available to clients (just to ensure no changes occur after the dump is created.
- Next dump the database to a file on the MacOS Server side with:
sudo pg_dumpall -f `date +"\`hostname\`.postgresql.%Y%m%d.dmp"` -U _postgres
This is running as root to ensure the database could be accessed as the
_postgresqldatabase user (a left-over from the time that MacOS Server supported hosting PostgreSQL databases)
- Transfer the file to the target server
- Clean up the dump prior to loading it. In my case I had to:
- Removed statements to create/alter the users:
postgressa few other obsolete ones and related
- Change all rights / ownership of anything owned by
postgressto clean up the legacy mess
- Removed the creation of the
ALTERstatements of its access rights from the dump (as it already exists)
- Removed statements to create/alter the users:
- Load the database in the new environment with the following command:
sudo -u postgres psql -f *.postgresql.*.dmp
which loads the data, make sure there are no statements with
- Remove unnecessary databases with the necessary
DROP DATABASE ...(and
DROP USER ...) statements.
- At this moment all data has been transferred, but the database is not yet accessible. For this, the contents of the files
pg_ident.conffrom the MacOS Server must be merged into the corresponding files in
/etc/postgresql/9.6/main/on the new server.
- Next the PostgreSQL service must be restarted to pick up the new settings with:
sudo pg_ctlcluster 9.6 main restart
And after these steps the database migration is complete.
This post is part of a series on moving functionality removed in Fall 2018 from MacOS Server: