I Cannot Access PostgreSQL At Times After Server App Upgrade

Question

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?

Answer 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/

Answer 2

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...