Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Server 3.2.1 (10.9.5) – Postgres won't start

After upgrading from Server 3.1.2 (Mavericks 10.9.3) to Server 3.2.1 (Mavericks 10.9.5), I cannot start Postgres:


# serveradmin start postgres

postgres:error = "CANNOT_START_SERVICE_TIMEOUT_ERR"


Here is what I see in system.log:


Sep 18 18:36:20 server com.apple.serverd[78]: ERROR: SMJobSubmit: The operation couldn’t be completed. (kSMErrorDomainLaunchd error 9 - The job dictionary specifies that it is disabled.)

Sep 18 18:36:20 server serveradmin[2249]: servermgr_postgres: waiting for postgres to respond

Sep 18 18:36:24 server serveradmin[2249]: servermgr_postgres: Timed out trying to start postgres.

Sep 18 18:36:24 server serveradmin[2249]: posting dist not


Not sure what to do from here. Postgres was running fine under 10.9.3.



Rusty

Posted on Sep 18, 2014 3:38 PM

Reply
91 replies

Sep 18, 2014 4:19 PM in response to Rusty Ross

Hmm, I am suspecting a possible bug in Server 3.2.1.


It looks like Postgres has been upgraded from 9.2.8 to 9.3.4:


# serveradmin fullstatus postgres

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 = "STOPPED"


And here's what I see in /Library/Logs/PostgreSQL/PostgreSQL.log:


# tail /Library/Logs/PostgreSQL/PostgreSQL.log

FATAL: unrecognized configuration parameter "unix_socket_directory"

FATAL: unrecognized configuration parameter "unix_socket_directory"

FATAL: unrecognized configuration parameter "unix_socket_directory"

FATAL: unrecognized configuration parameter "unix_socket_directory"

FATAL: unrecognized configuration parameter "unix_socket_directory"

FATAL: unrecognized configuration parameter "unix_socket_directory"


Looks like unix_socket_directory may have been changed to unix_socket_directories in 9.3:

http://dba.stackexchange.com/questions/50135/pg-upgrade-unrecognized-configurati on-parameter-unix-socket-directory

Sep 18, 2014 6:50 PM in response to Rusty Ross

Indeed, I think this is a bug.


I worked around it for now by changing unix_socket_directory to unix_socket_directories in /Library/Server/PostgreSQL/Config/org.postgresql.postgres.plist.


Afterward, I also had to pg_upgrade my data directory to 9.3 (I am guessing this was missed by the Server.app migrator because postgres wouldn't launch during migration due to this bug.)


That was somewhat involved, but I did it like this (in my example, I have a clone of the previous system with the previous postgres version on a volume called OlderClone):


ditto /Library/Server/PostgreSQL/Data /Library/Server/PostgreSQL/DataOld
rm -rf /Library/Server/PostgreSQL/Data/*


[as the _postgres user]:
/Applications/Server.app/Contents/ServerRoot/usr/bin/initdb /Library/Server/PostgreSQL/Data -E utf8


mv /Volumes/OlderClone/Applications/Server.app/Contents/ServerRoot/usr/bin/postgres /Volumes/OlderClone/Applications/Server.app/Contents/ServerRoot/usr/bin/postgres_off
mv /Volumes/OlderClone/Applications/Server.app/Contents/ServerRoot/usr/bin/postgres_real /Volumes/OlderClone/Applications/Server.app/Contents/ServerRoot/usr/bin/postgres


[as the _postgres user]:
/Applications/Server.app/Contents/ServerRoot/usr/bin/pg_upgrade -b /Volumes/OlderClone/Applications/Server.app/Contents/ServerRoot/usr/bin -B /Applications/Server.app/Contents/ServerRoot/usr/bin -d /Library/Server/PostgreSQL/DataOld -D /Library/Server/PostgreSQL/Data


mv /Volumes/OlderClone/Applications/Server.app/Contents/ServerRoot/usr/bin/postgres /Volumes/OlderClone/Applications/Server.app/Contents/ServerRoot/usr/bin/postgres_real
mv /Volumes/OlderClone/Applications/Server.app/Contents/ServerRoot/usr/bin/postgres_off /Volumes/OlderClone/Applications/Server.app/Contents/ServerRoot/usr/bin/postgres



Anyway, I have postgres running, but I think I'll file this as a radar.

Sep 19, 2014 4:40 AM in response to Rusty Ross

Thank you so much Rusty. Seems like I am not successful running the _postgres commands... (TinTin is the name of my old clone.) The non _postgres commands are fine naturally.


sh-3.2# sudo -u _postgres /Applications/Server.app/Contents/ServerRoot/usr/bin/initdb /Library/Server/PostgreSQL/Data -E utf8

could not identify current directory: Permission denied

could not identify current directory: Permission denied

could not identify current directory: Permission denied

The program "postgres" is needed by initdb but was not found in the

same directory as "initdb".

Check your installation.


sh-3.2# sudo -u _postgres /Applications/Server.app/Contents/ServerRoot/usr/bin/pg_upgrade -b /Volumes/TinTin/Applications/Server.app/Contents/ServerRoot/usr/bin -B /Applications/Server.app/Contents/ServerRoot/usr/bin -d /Library/Server/PostgreSQL/DataOld -D /Library/Server/PostgreSQL/Data


cannot write to log file pg_upgrade_internal.log

Failure, exiting

Sep 19, 2014 6:04 AM in response to Rusty Ross

I can confirm this postgres bug in the latest Server.app. After a call to Enterprise Support that went nowhere I also arrived at the same fix as the OP and confirm that it works. I also had to delete the files:

# double check that Postgres for users isn't running:

sudo serveradmin stop postgres

sudo serveradmin status postgres

lsof -i ':5432'

nmap -p 5432 localhost

ps -ef | grep postgres | grep -v grep | grep listen | grep 127.0.0.1

# Delete these files if postgres isn't listening on localhost

sudo rm /Library/Server/PostgreSQL/Data/postmaster.pid

sudo rm /private/var/pgsql_socket/.s.PGSQL.5432

sudo rm /private/var/pgsql_socket/.s.PGSQL.5432.lock


Has anyone filed a bug report yet?

I was unable to to get pg_upgrade to work, but regularly backup my tables with the command:


# Backup database

sudo sh -c '/Applications/Server.app/Contents/ServerRoot/usr/bin/pg_dumpall -U _postgres | bzip2 -c > /private/var/backups/postgres.dumpall.bz2'


# Restore database


sudo bzcat /private/var/backups/postgres.dumpall.bz2 | sudo /Applications/Server.app/Contents/ServerRoot/usr/bin/psql -q -d postgres _postgres --password


Fortunately, this restored my tables without the use of pg_upgrade.


Finally, I learned how to start OS X Server's Postgres for users at launch:


sudo launchctl load -w /Applications/Server.app/Contents/ServerRoot/System/Library/LaunchDaemons/org.p ostgresql.postgres.plist

Sep 19, 2014 6:18 AM in response to Rusty Ross

Thanks Rusty for your apparent solution.


While converting the database I get this message:

Performing Consistency Checks

-----------------------------

Checking cluster versions ok

Checking database user is a superuser ok

Checking for prepared transactions ok

Checking for reg* system OID user data types ok

Checking for contrib/isn with bigint-passing mismatch ok

Creating dump of global objects ok

Creating dump of database schemas

ok



lc_collate cluster values do not match: old "C", new "nl_NL.UTF-8"

Failure, exiting


I've found that you can set the locale when create the default db with initdb -E, but what is "C"?


Is it possible to completely replace Server.app and the data directories and continue with the old version, or are there a lot more changes made by starting the new Server.app?


Thanks!

Sep 19, 2014 8:25 AM in response to Rusty Ross

Ok making progress here, but the utf8 options seems to be causing some issue...


sh-3.2# chown -R _postgres:_postgres /Library/Server/PostgreSQL

sh-3.2# cd /Library/Server/PostgreSQL

sh-3.2# sudo -u _postgres /Applications/Server.app/Contents/ServerRoot/usr/bin/initdb /Library/Server/PostgreSQL/Data -E utf8

The files belonging to this database system will be owned by user "_postgres".

This user must also own the server process.


The database cluster will be initialized with locale "en_US.UTF-8".

The default text search configuration will be set to "english".


Data page checksums are disabled.


fixing permissions on existing directory /Library/Server/PostgreSQL/Data ... ok

creating subdirectories ... ok

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

creating configuration files ... ok

creating template1 database in /Library/Server/PostgreSQL/Data/base/1 ... ok

initializing pg_authid ... ok

initializing dependencies ... ok

creating system views ... ok

loading system objects' descriptions ... ok

creating collations ... ok

creating conversions ... ok

creating dictionaries ... ok

setting privileges on built-in objects ... ok

creating information schema ... ok

loading PL/pgSQL server-side language ... ok

vacuuming database template1 ... ok

copying template1 to template0 ... ok

copying template1 to postgres ... ok

syncing data to disk ... ok


WARNING: enabling "trust" authentication for local connections

You can change this by editing pg_hba.conf or using the option -A, or

--auth-local and --auth-host, the next time you run initdb.


Success. Since you appear to be using the default database,

the system-wide instance of postgres will be launched on-demand by

the various services which use it.


If necessary, you can now start the database server using:


serveradmin start postgres


See webappctl(8) to learn how to launch postgres on-demand for

sh-3.2# mv /Volumes/TinTin/Applications/Server.app/Contents/ServerRoot/usr/bin/postgres /Volumes/TinTin/Applications/Server.app/Contents/ServerRoot/usr/bin/postgres_of f

sh-3.2# mv /Volumes/TinTin/Applications/Server.app/Contents/ServerRoot/usr/bin/postgres_re al /Volumes/TinTin/Applications/Server.app/Contents/ServerRoot/usr/bin/postgres

sh-3.2# sudo -u _postgres /Applications/Server.app/Contents/ServerRoot/usr/bin/pg_upgrade -b /Volumes/TinTin/Applications/Server.app/Contents/ServerRoot/usr/bin -B /Applications/Server.app/Contents/ServerRoot/usr/bin -d /Library/Server/PostgreSQL/DataOld -D /Library/Server/PostgreSQL/Data

Performing Consistency Checks

-----------------------------

Checking cluster versions ok

Checking database user is a superuser ok

Checking for prepared transactions ok

Checking for reg* system OID user data types ok

Checking for contrib/isn with bigint-passing mismatch ok

Creating dump of global objects ok

Creating dump of database schemas

ok


lc_collate cluster values do not match: old "C", new "en_US.UTF-8"

Failure, exiting

Sep 19, 2014 10:24 AM in response to Rusty Ross

Thanks Rusty,


That worked after I also added --lc-ctype=C.


But postgres still won't start: after serveradmin start postgres I still get postgres:error = "CANNOT_START_SERVICE_TIMEOUT_ERR"

serveradmin fullstatus postgres gives:

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"

Sep 19, 2014 10:35 AM in response to Erik Hagemeijer

Have a look at your logs in /Library/Logs/PostgreSQL/PostgreSQL.log


What I did to troubleshoot was to pull out the actual command that serveradmin forms from the plists /Applications/Server.app/Contents/ServerRoot/System/Library/LaunchDaemons/org.po stgresql.postgres.plist and /Library/Server/PostgreSQL/Config/org.postgresql.postgres.plist, then run that command as _postgres from the directory /Library/Server/PostgreSQL/Data:


su -

cd /Library/Server/PostgreSQL/Data

sudo -u _postgres <long command>.


The fix to this problem should be a simple three-step fix of (1) using "unix_socket_directories" in /Library/Server/PostgreSQL/Config/org.postgresql.postgres.plist, (2) creating a new directory /Library/Server/PostgreSQL/Data with the correct permissions, (3) running initdb, and then using serveradmin to start postgres.

Server 3.2.1 (10.9.5) – Postgres won't start

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.