Rusty Ross

Q: 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

Close

Q: Server 3.2.1 (10.9.5) – Postgres won't start

  • All replies
  • Helpful answers

Page 1 of 7 last Next
  • by Rusty Ross,

    Rusty Ross Rusty Ross Sep 18, 2014 4:19 PM in response to Rusty Ross
    Level 2 (175 points)
    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



  • by Rusty Ross,Solvedanswer

    Rusty Ross Rusty Ross Sep 18, 2014 6:50 PM in response to Rusty Ross
    Level 2 (175 points)
    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.

  • by Ali Kaylan,

    Ali Kaylan Ali Kaylan Sep 18, 2014 8:35 PM in response to Rusty Ross
    Level 1 (5 points)
    Sep 18, 2014 8:35 PM in response to Rusty Ross

    I have this issue as well. Since I do not use postgres, I have no idea on how to execute the specific lines as the _postgres user. Would you kindly spell it out? Plus the specifics of pg_upgrade, if you could. Thanks in advance.

     

    -Ali

  • by Rusty Ross,

    Rusty Ross Rusty Ross Sep 18, 2014 8:41 PM in response to Ali Kaylan
    Level 2 (175 points)
    Sep 18, 2014 8:41 PM in response to Ali Kaylan

    You would do something like:

     

    sudo -u _postgres my_great_command

     

    ie:

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

  • by Ali Kaylan,

    Ali Kaylan Ali Kaylan Sep 19, 2014 4:40 AM in response to Rusty Ross
    Level 1 (5 points)
    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

  • by Rusty Ross,

    Rusty Ross Rusty Ross Sep 19, 2014 5:32 AM in response to Ali Kaylan
    Level 2 (175 points)
    Sep 19, 2014 5:32 AM in response to Ali Kaylan

    Maybe post the results of:


    ls -l /Library/Server/PostgreSQL



  • by essandess,

    essandess essandess Sep 19, 2014 6:04 AM in response to Rusty Ross
    Level 1 (28 points)
    Applications
    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

  • by Rusty Ross,

    Rusty Ross Rusty Ross Sep 19, 2014 6:09 AM in response to essandess
    Level 2 (175 points)
    Sep 19, 2014 6:09 AM in response to essandess

    Yes, I have filed Apple Bug 18389860.

     

    Please feel free to file your own and reference mine.

  • by Erik Hagemeijer,

    Erik Hagemeijer Erik Hagemeijer Sep 19, 2014 6:18 AM in response to Rusty Ross
    Level 1 (0 points)
    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!

  • by Rusty Ross,

    Rusty Ross Rusty Ross Sep 19, 2014 6:27 AM in response to Erik Hagemeijer
    Level 2 (175 points)
    Sep 19, 2014 6:27 AM in response to Erik Hagemeijer

    In your case, I think you can fix this by adding --lc-collate=C to your initdb process.

     

    Some context:

     

    http://serverfault.com/questions/527879/upgrading-postgres-app-9-1-to-9-2-gettin g-lc-collate-values-do-not-match-error/527930


    https://www.madboa.com/geek/utf8/


    Rusty

  • by Ali Kaylan,

    Ali Kaylan Ali Kaylan Sep 19, 2014 7:42 AM in response to Rusty Ross
    Level 1 (5 points)
    Sep 19, 2014 7:42 AM in response to Rusty Ross

    Rusty;

     

    The results are as follows:

     

    sh-3.2# ls -l /Library/Server/PostgreSQL

    total 0

    drwxr-xr-x   3 root  wheel  102 Oct 25  2013 Config

    drwx------   2 root  wheel   68 Sep 18 21:48 Data

    drwx------  18 root  wheel  612 Nov 11  2013 DataOld

  • by Rusty Ross,

    Rusty Ross Rusty Ross Sep 19, 2014 7:47 AM in response to Ali Kaylan
    Level 2 (175 points)
    Sep 19, 2014 7:47 AM in response to Ali Kaylan

    Looks like you have some ownership issues on that directory. I would:

     

    chown -R _postgres:_postgres /Library/Server/PostgreSQL

    cd /Library/Server/PostgreSQL

     

    and try the initdb and upgrade again.

  • by Ali Kaylan,

    Ali Kaylan Ali Kaylan Sep 19, 2014 8:25 AM in response to Rusty Ross
    Level 1 (5 points)
    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

  • by Erik Hagemeijer,

    Erik Hagemeijer Erik Hagemeijer Sep 19, 2014 10:24 AM in response to Rusty Ross
    Level 1 (0 points)
    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"

Page 1 of 7 last Next