3

I am running the latest version of Mavericks 10.9.5 on my Mac Mini Server. Before the upgrade to the latest version of the Server App 3.2.1 I was able to access PostgreSQL in my Ruby on Rails Applications and through the Web Service. After I installed the Server App I implemented Phusion Passenger again in /Library/Server/Web/Config/apache2/httpd_server_app.conf. I then opened the Server App and verified that the Web Service was turned on.

My Ruby on Rails applications stopped working stating I had the following error:

PG::ConnectionBad (could not connect to server: Connection refused
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

I checked the plist file /Library/Server/PostgreSQL/Config/ org.postgresql.postgres.plist. The two settings I usually modify to get rid of this error are the following. This was the code that worked before I updated the Server App. I checked to make sure if the code in the plist was modified after the update of Mavericks and the Server App.

    <string>listen_addresses=127.0.0.1,::1</string>
    <string>unix_socket_permissions=0777</string>

When I execute the sudo serveradmin fullstatus postgres command here is the output.

postgres:dataDirHasBeenInitialized = yes
postgres:PG_VERSION = "9.3.4"
postgres:dataDir = "/Library/Server/PostgreSQL/Data"
postgres:postgresIsResponding = no
postgres:dataDirIsDirectory = yes
postgres:PGserverVersion = 0
postgres:dataDirExists = yes
postgres:setStateVersion = 1
postgres:state = "RUNNING"

When I was running 9.2.4 postgresIsResponding was set to yes and PGserverVersion was 90204. I'm not sure if something changed between PostgreSQL version 9.2 and 9.3.

I have done web searches but cannot find any recent posts about this using the latest versions of Mavericks and the Server app.

This is also posted in Server Fault.

Here is what happens when I do ps -ef | grep postgres

  220   124     1   0 Mon07AM ??         2:30.39 /usr/bin/python /Applications/Server.app/Contents/ServerRoot/usr/bin/xpostgres -a /Library/Server/ProfileManager/Config/PostgreSQL_config.plist
  220   361   124   0 Mon07AM ??         0:08.36 /Applications/Server.app/Contents/ServerRoot/usr/bin/postgres_real -D /Library/Server/ProfileManager/Config/ServiceData/Data/PostgreSQL -c unix_socket_directories=/Library/Server/ProfileManager/Config/var/PostgreSQL -c logging_collector=on -c log_rotation_size=10MB -c log_connections=on -c log_lock_waits=on -c log_statement=ddl -c log_line_prefix=%t -c listen_addresses= -c log_directory=/Library/Logs/ProfileManager -c log_filename=PostgreSQL-%F.log -c log_min_messages=WARNING -c log_min_error_statement=WARNING -c unix_socket_group=_devicemgr -c unix_socket_permissions=0770 -c max_connections=200
  220   388   361   0 Mon07AM ??         0:00.01 postgres: logger process                                   
  220   401   361   0 Mon07AM ??         0:00.91 postgres: checkpointer process                                   
  220   402   361   0 Mon07AM ??         0:04.07 postgres: writer process                                   
  220   403   361   0 Mon07AM ??         0:03.88 postgres: wal writer process                                   
  220   404   361   0 Mon07AM ??         0:11.83 postgres: autovacuum launcher process                                   
  220   405   361   0 Mon07AM ??         0:01.08 postgres: archiver process                                   
  220   406   361   0 Mon07AM ??         0:33.03 postgres: stats collector process                                   
  220   407   361   0 Mon07AM ??         0:05.01 postgres: wal sender process _devicemgr [local] streaming 0/802DC88                                
  220   484   361   0 Mon07AM ??         0:00.73 postgres: _devicemgr devicemgr_v2m0 [local] idle                                
  220   495   361   0 Mon07AM ??         0:14.02 postgres: _devicemgr devicemgr_v2m0 [local] idle                                
  220   535   361   0 Mon07AM ??         0:00.00 postgres: _devicemgr devicemgr_v2m0 [local] idle                                
  501 75088 74999   0  7:14PM ttys000    0:00.00 grep postgres

Here is the output when I execute psql -h localhost

psql: could not connect to server: Connection refused
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Connection refused
    Is the server running on host "localhost" (fe80::1) and accepting
    TCP/IP connections on port 5432?
  • Reposted to stackoverflow.com/q/26265993/398670 – Craig Ringer Oct 8 '14 at 23:48
  • So you're using the PostgreSQL instance that's built in to Mac OS X? Not a 3rd party one? What does ps -ef | grep postgres show? If you try connecting over tcp/ip with psql -h localhost what happens? – Craig Ringer Oct 8 '14 at 23:51
  • I'm using the one built in to Mac OS X. I just added the output for ps -ef | grep postgres. – Pamela Cook - LightBe Corp Oct 9 '14 at 0:16
  • I just added the output for psql -h localhost – Pamela Cook - LightBe Corp Oct 9 '14 at 0:24
1

There is a bug in the update process, where it doesn't migrate the previous databases because of an updated config option that prevents postgresql from launching.

Here are the steps to resolve it:

  1. Stop the postgres service:

    sudo su
    serveradmin stop postgres
    
  2. Update the configuration field:

    vim /Library/Server/PostgreSQL/Config/org.postgresql.postgres.plist
    

    replace unix_socket_directory with unix_socket_directories then save & exit

  3. Move the PostgreSQL data folder:

    mv /Library/Server/PostgreSQL/Data /Library/Server/PostgreSQL/DataOld
    
  4. Recreate the standard PostgreSQL Data folder and initialise a blank database:

    sudo -u _postgres /Applications/Server.app/Contents/ServerRoot/usr/bin/initdb /Library/Server/PostgreSQL/Data -E utf8 --lc-collate=C --lc-ctype=C
    
  5. Update the data in the blank database with data from the DataOld folder:

    sudo -u _postgres /Applications/Server.app/Contents/ServerRoot/usr/bin/pg_upgrade -b /Applications/Server.app/Contents/ServerRoot/usr/libexec/postgresql9.2 -B /Applications/Server.app/Contents/ServerRoot/usr/bin -d /Library/Server/PostgreSQL/DataOld -D /Library/Server/PostgreSQL/Data
    
  6. Restart the PostgreSQL service:

    launchctl load -w /Applications/Server.app/Contents/ServerRoot/System/Library/LaunchDaemons/org.postgresql.postgres.plist
    serveradmin start postgres
    

source: http://www.macambulance.co.uk/os-x-server-3-2-1-update-breaks-postgresql/

  • I currently use the Web Service to host a website in addition to using their PostgreSQL instance for databases used in my Ruby on Rails applications. I saw this solution before. I was reluctant to do it because of concerned that it would mess up any Apple functions using the database. From what I remember several options in the Server app use PostgreSQL including the Web Service. Please add info about if this solution was successful for people using the Web Service or other services in the Server app that use PostgreSQL. – Pamela Cook - LightBe Corp Oct 14 '14 at 11:29
  • I use Web Service and after the migration it all still works for me. All this does is migrate the custom databases (you can look at the schema), it does not touch any Apple created databases (they seem to be kept in other paths). – lkraider Oct 14 '14 at 20:24
  • Glad to know that your solution did not break the Web Service. However I decided to restore my servers to the backup right before I upgraded the Server App. I lost very little data thankfully. After this experience I have decided to figure out how to install another version of PostgreSQL to hopefully prevent this from happening again. Craig Ringer posted a link in his response which gives three ways to accomplish this. codefellows.org/blog/…. – Pamela Cook - LightBe Corp Oct 19 '14 at 16:24
  • If you are looking to run your own PostgreSQL on OSX, I cannot recommend postgresapp.com enough. – lkraider Oct 20 '14 at 17:17
  • I will try it. It looks like it may be easier to implement then using Homebrew. – Pamela Cook - LightBe Corp Oct 23 '14 at 0:10
0

Your issue is that Apple seem to have changed the unix socket directories and tcp port used.

Note these options passed to the postmaster (which they've renamed postgres_real):

  • -c unix_socket_directories=/Library/Server/ProfileManager/Config/var/PostgreSQL
  • -c listen_addresses= (Yes, that's equals nothing)

In other words, it's listening on a private socket in a non-default location and not listening on TCP/IP. They've also changed the socket permissions to deny access to other users:

  • -c unix_socket_group=_devicemgr
  • -c unix_socket_permissions=0770

I applaud Apple for finally doing this - though they seem to have done it in about the worst way they could do it. Their PostgreSQL install has been a major pain for a long time because it's tended to conflict with other installs. I reported a bug about it years ago. It's a major issue if their upgrade process is discarding user data, as it seems to be, though.

I suggest installing a PostgreSQL server for your development use. Popular choices are Homebrew and Heroku's Postgres.app . See https://www.codefellows.org/blog/three-battle-tested-ways-to-install-postgresql .

If you want to try to integrate your app into the Server app like you did before, you'll need to set host: /Library/Server/ProfileManager/Config/var/PostgreSQL in database.yml. But really, I'm pretty sure Apple's trying to make that a private PostgreSQL instance for just their use...

  • So anyone that has built databases using their built-in PostgreSQL has to redo all their databases using something else. It was nice only having to deal with one installation of PostgreSQL. I'm up a creek since I cannot take a final backup of my databases since I cannot connect to them. What do I do now? – Pamela Cook - LightBe Corp Oct 9 '14 at 0:55
  • I was using pgAdmin to take backups of my databases. It will not allow me to remove the port number in the properties. Maybe I can get to the database in database.yml. How can I take backups of those databases? It looks like I may have lost all my data at this point if I cannot create backups. Can I go back to the previous version of Server app from my Time Machine? – Pamela Cook - LightBe Corp Oct 9 '14 at 1:10
  • @LightBeCorp First see if the data's there in the upgraded DB, don't panic yet. Start with psql 'host=/Library/Server/ProfileManager/Config/var/PostgreSQL dbname=mydatabase' . You might need to sudo to the user server.app runs as; I can't tell you what that is as your ps listing above shows only numeric userids, not usernames. If you can connect, you can pg_dump -Fc the database and restore it to a new PostgreSQL instance. The only way the data would actually be lost is if the server.app upgrade deleted/discarded it because it didn't know it was there; we don't know that happened. – Craig Ringer Oct 9 '14 at 1:57
  • @LightBeCorp That said, this is an object lesson in why you should avoid storing your data in some app's database that it thinks it "owns". Fun things can happen when you upgrade. At minimum, you should really take a backup before any upgrade - and of course be doing regular (preferably daily) automated backups anyway. – Craig Ringer Oct 9 '14 at 2:00
  • @LightBeCorp Do not attempt to roll back with Time Machine. You might just make things worse. – Craig Ringer Oct 9 '14 at 2:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .