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::dbThis 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 [938] LOG: could not bind IPv6 socket: Cannot assign requested address 2018-04-14 14:27:28.109 CEST [938] 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 [938] WARNING: could not create listen socket for "2001:XXXX:YYYY:ZZZZ::db" 2018-04-14 14:27:28.110 CEST [938] FATAL: could not create any TCP/IP sockets 2018-04-14 14:27:28.110 CEST [938] 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.targetas 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 [Service] section:
Restart = on-failure
RestartSec = 10s
StartLimitBurst = 3Which 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 pg_dumpall
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 _postgresThis 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:
_devicemgr,_postgress,postgressa few other obsolete ones and relatedALTERstatements - Change all rights / ownership of anything owned by
_postgresstopostgressto clean up the legacy mess - Removed the creation of the
template1database andALTERstatements 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.*.dmpwhich loads the data, make sure there are no statements with
ERRORin it! - Remove unnecessary databases with the necessary
DROP DATABASE ...(andDROP 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_hba.confandpg_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: