2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Craig's PlanetPostgreSQL3 / Ware Yosemite? Possible PostgreSQL upgrade issues in OS X 10.10
craig.ringer

Ware Yosemite? Possible PostgreSQL upgrade issues in OS X 10.10

October 21, 2014/10 Comments/in Craig's PlanetPostgreSQL /by craig.ringer

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.

Tags: 10.10, data loss, os x, pg_tblspc, PostgreSQL, psql, server.app, unix_socket_directories, unix_socket_directory, upgrade, yosemite
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
10 replies
  1. Abhinay
    Abhinay says:
    October 30, 2014 at 12:36 pm

    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

    Reply
    • craig.ringer
      craig.ringer says:
      October 30, 2014 at 1:13 pm

      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.

      Reply
  2. Nicolas
    Nicolas says:
    November 6, 2014 at 12:12 pm

    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

    Reply
  3. polarair
    polarair says:
    December 28, 2014 at 2:25 pm

    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

    Reply
    • craig.ringer
      craig.ringer says:
      December 28, 2014 at 2:35 pm

      I can’t imagine how that could be remotely related. Try http://apple.stackexchange.com/ or http://serverfault.com/

      Reply
  4. Florian
    Florian says:
    January 12, 2015 at 8:23 pm

    what does the file /Applications/Server.app/Contents/ServerRoot/user/bin/xpostgres tell us?

    Reply
    • craig.ringer
      craig.ringer says:
      January 14, 2015 at 1:47 am

      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 ?

      Reply
  5. senso
    senso says:
    March 23, 2015 at 12:34 pm

    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.

    Reply
  6. Jagannath R
    Jagannath R says:
    November 6, 2019 at 8:49 am

    Recently i have updated my Mac OS to Catalina version(10.15.1).

    Facing an issue–

    Connection refused
    Is the server running locally and accepting
    connections on Unix domain socket “/var/pgsql_socket/.s.PGSQL.5432”?

    Came to know that this is due to removal of some directories, So added them manually and on reboot i’m getting the issue again.

    current PG version — postgres (PostgreSQL) 9.2.23

    Reply

Trackbacks & Pingbacks

  1. PgConf.EU recap | the gabriellephant says:
    November 4, 2014 at 1:27 am

    […] Craig Ringer’s talk about usability started with a round of “Error Message Jeopardy”, and included a reminder that we were all new once, and have forgotten how much we know. I personally accidentally tried to run psql on a -FC pg_dump just last week, and really appreciate the addition of the HINT message! I also hadn’t heard about the update problems on Yosemite. […]

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Index Overhead on a Growing Table … and now for something completely different!
Scroll to top
×