Ware Yosemite? Possible PostgreSQL upgrade issues in OS X 10.10
I’m seeing reports of a number of issues with PostgreSQL after upgrades of OS X machines to Yosemite (OS X 10.10) that I’m concerned about, so I’m seeking more information about the experiences of PostgreSQL users who’ve done OS X 10.10 upgrades.
I can’t confirm anything yet, but back up all your databases before any upgrade to OS X 10.10. Just in case. (Of course, you do that before any upgrade, but just in case it slipped your mind this time…).
I don’t have access to a Mac because Apple’s policy prevents developers from running OS X for testing and development (or anything else) without buying physical Apple hardware and finding somewhere to run it. So I can’t test most of this myself, and I really need reports from users, or if possible, results of proactive testing by OS X users. I’ve also submitted a bug to Apple (#18719563) but don’t anticipate any action from that alone.
Meanwhile, I strongly recommend that Apple users install their own PostgreSQL – Homebrew, Macports, Postgres.app or the EDB installer – and use that instead of Apple’s built-in PostgreSQL. This was always the preferred option, and it’s what most users already did. If you’re one of the ones who was using the built-in PostgreSQL, switch now.
OS X built-in PostgreSQL deleted on update
Some OS X users appear to use the PostgreSQL version built-in to OS X for their own data, rather than installing a new PostgreSQL. Some of them, in addition to using the binaries, also use a PostgreSQL cluster (database instance) that’s created by OS X for the use of Server.app, instead of initdbing their own.
On releases prior to Yosemite the PostgreSQL provided by Apple was on the default PATH, though not necessarily running by default. It seems that on Yosemite it’s been removed; there’s no longer any /usr/bin/psql, etc. As far as I can tell Server.app now bundles PostgreSQL within the application bundle instead.
Some user reports suggest that on upgrade, the Apple-controlled databases in the new release are migrated into the new cluster managed by Server.app then the old cluster is stopped or possibly deleted – a colleage checked the upgrade script and found rm -rf /var/pgsql in it.
The PostgreSQL data directory in prior releases was /private/var/pgsql (and /var is a symlink to /private/var) or /Library/Server/PostgreSQL/Data.
The main symptom you’ll see is:
Connection refused Is the server running locally and accepting connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?
… but this issue is only one of many, many possible causes of that message.
OS X updater may be removing empty directories
I’m seeing a number of reports that suggest that the OS X updater may be removing empty directories. This causes problems with PostgreSQL, which expects to have an empty pg_twophase, pg_tblspc, and often pg_stat_tmp as a part of normal operation.
- Yosemite (OSX 10.0) problems with Postgresql
- Mac OS X Yosemite Upgrade & PostgreSQL
- `pg_tblspc` missing after installation of OS X Yosemite
- … and so, so many posts on Holdem Manager and Poker Tracker forums.
It looks like working around this is a simple matter of mkdiring the directories and setting appropriate permissions, but it’s a concern that it’s happening at all.
Server.app 3.2.1 upgrade issues
I’m also seeing reports that the patch release 3.2.1 for Server.app upgrades its private bundled PostgreSQL from 9.2.8 to 9.3.4, which seems to be causing some users issues.
If you’ve used the PostgreSQL bundled in Server.app to initdb a new cluster, this will render it inaccessible until you find and install a compatible PostgreSQL 9.2.
- I Cannot Access PostgreSQL At Times After Server App Upgrade
- OS X Server 3.2.1 update breaks PostgreSQL
If you’ve used Server.app‘s own install then it may not preserve your databases when it upgrades. I can’t confirm the upgrade process it uses yet, and really need user tests reports for this.
Test reports and more information needed
Given these concerns, I would value reports and test results from OS X users who’re still on pre-Yosemite versions and planning to update soon.
If you’re an Apple customer, please also contact Apple support and ask them to investigate this.
So far I don’t know for sure if there’s data loss involved and I don’t have the access to investigate properly but I’m quite concerned about the preliminary indications I’m able to find.
can you tell me where I can find these files
~/src/backend/storage/buffer/buf_init.c
~/src/backend/storage/buffer/bufmgr.c
~/src/backend/storage/buffer/freelist.c
Sure: see http://git.postgresql.org/gitweb/?p=postgresql.git;a=summary or https://github.com/postgres/postgres . I don’t see what that has to do with this post though.
I have performed the upgrade to Yosemite yesterday and install the new Server.app version.
After the upgrade, postgresql is no longer running but the binaries are still here.
Note: I was using the built-in version.
The simple solution is to restore from backup of the org.postgresql.postgres.plist file.
Then :
# cp org.postgresql.postgres.plist /Library/LaunchDaemons/
# launchctl load -F /Library/LaunchDaemons/org.postgresql.postgres.plist
Nicolas
Just a question that might be related. I upgraded the Mav server to Yosemite and upgraded the Sever from 3 to 4, all running but one thing, no mail arriving at the server and the log indicates that the full hostname is beeing used. Any ideas. I would not allow me to do sudo postmap /etc/aliases without lots of errors.
Any help is welcom
I can’t imagine how that could be remotely related. Try http://apple.stackexchange.com/ or http://serverfault.com/
what does the file /Applications/Server.app/Contents/ServerRoot/user/bin/xpostgres tell us?
I have no idea, partly as I don’t have access to a Mac.
Are the contents the same as http://www.opensource.apple.com/source/PostgreSQL/PostgreSQL-97/Support/backup_restore/xpostgres ?
I was using Postgres on OS X 10.9 for Atlassian products, using the built-in Postgres.
I upgraded to OS X 10.10 and sure enough I got the ‘Connection refused’ error you describe in the article.
Reading your post I got alarmed that my data may have been deleted, but my worry was unfounded.
OS X 10.9 included Postgres 9.2.8 whereas OS X 10.10 includes Postgres 9.3.5 and so the Postgres 9.2.8 data is incompatible with Postgres 9.3.5 This is a Postgres restriction. The data must be upgraded with pg_upgrade, or pg_dump
I actually tried both methods. Once by installing Postgres 9.2.8 on OS X 10.10 and using pg_upgrade and again by restoring using Time Machine to 10.9 and using pg_dump, then restoring back to 10.10 (Time Machine again), and restoring from the dump. Might I say both worked, so in one of those cases, no backup was required. (Although why you’d run a server without backing up I don’t know. Also Time Machine is so simple and easy, I don’t know why you wouldn’t at least do that). I can confirm also that Time Machine worked flawlessly (although I tried restoring using Time Machine to a VM Ware Fusion Virtual Machine and I can confirm that does not work).
My postgres data directory on 10.9 was /Library/Server/PostgreSQL/Data so if someone thinks their data is deleted they could look there. If they search for postgres data the directory may be owned by the postgres user, so they should search as root or using sudo. The postgres user I have does not have a login shell, so you can’t su -l
In summary the OS X upgrade left my data completely alone. So alone in fact that it didn’t attempt to upgrade it to the new version of Postgres which is 9.3.5
I don’t really see this as a criticism of the upgrade. I wouldn’t expect they upgrade my data as part of the OS X upgrade. In fact they could be criticised for upgrading the data, because I didn’t explicitly specify this. And in fact it is more about postgres administration and knowing that an upgrade from 9.2 to 9.3 will require a pg_dump or pg_upgrade. For someone who is configuring their own postgres instances, that responsibility is theirs.
Those who weren’t using the built-in postgres on OS X 10.9 will only not be affected then if they had installed a postgres version >= 9.3 before upgrading. When they did that they will have had to upgrade their data anyway.