Server 3.2.1 (10.9.5) – Postgres won't start

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

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

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'

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

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!

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

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

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"

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.