Server 3.2.1 (10.9.5) – Postgres won't start

Also, I don't see that listen_address is specified in your serveradmin settings -- I don't know if this is a requirement. Try:

sudo serveradmin set postgres:listen_address = "127.0.0.1,::1"

sudo serveradmin start postgres

Thanks to Rusty and Erik, I managed to go through this fix. Now I get

postgres:state = "RUNNING"

But mail is still down despite indicating "on" in the server. For posterity, this is what I did after restoring everything:

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

sh-3.2# cd /Library/Server/PostgreSQL

sh-3.2# ls -l

total 0

drwxr-xr-x 3 _postgres _postgres 102 Sep 19 12:44 Config

drwx------ 18 _postgres _postgres 612 Sep 19 12:44 Data

sh-3.2# ditto /Library/Server/PostgreSQL/Data /Library/Server/PostgreSQL/DataOld

sh-3.2# ls -l

total 0

drwxr-xr-x 3 _postgres _postgres 102 Sep 19 12:44 Config

drwx------ 18 _postgres _postgres 612 Sep 19 12:44 Data

drwx------ 18 _postgres _postgres 612 Sep 19 12:44 DataOld

sh-3.2# rm -rf /Library/Server/PostgreSQL/Data/*

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

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 locales

COLLATE: C

CTYPE: C

MESSAGES: en_US.UTF-8

MONETARY: en_US.UTF-8

NUMERIC: en_US.UTF-8

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

your web application.

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

Checking for presence of required libraries ok

Checking database user is a superuser ok

Checking for prepared transactions ok

If pg_upgrade fails after this point, you must re-initdb the

new cluster before continuing.

Performing Upgrade

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

Analyzing all rows in the new cluster ok

Freezing all rows on the new cluster ok

Deleting files from new pg_clog ok

Copying old pg_clog to new server ok

Setting next transaction ID for new cluster ok

Setting oldest multixact ID on new cluster ok

Resetting WAL archives ok

Setting frozenxid counters in new cluster ok

Restoring global objects in the new cluster ok

Adding support functions to new cluster ok

Restoring database schemas in the new cluster

ok

Removing support functions from new cluster ok

Copying user relation files

ok

Setting next OID for new cluster ok

Sync data directory to disk ok

Creating script to analyze new cluster ok

Creating script to delete old cluster ok

Upgrade Complete

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

Optimizer statistics are not transferred by pg_upgrade so,

once you start the new server, consider running:

analyze_new_cluster.sh

Running this script will delete the old cluster's data files:

delete_old_cluster.sh

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

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

sh-3.2#

All,

It gets even more interesting if you simply install a new Mavericks host with Server from scratch. postgreSQL is essentially missing and there are various instances of it in each "service" that needs it. They fundamentally changed where it is installed for net new installations. The migration is hosed for sure, but for a real eye opener, do a fresh install.

I followed the postgres reinitialization steps, though unfortunately, I didn't have a handy DB backup, so a few steps didn't apply to me. The other gotcha I had though, was my calendar/contacts directory was completely corrupted and unable to start or stop, even surviving reboots.

Moving the "/Library/Server/Calendar and Contacts" directory to the side was required to clear the problem. Now the joy of reinstalling data.

Repairing postgres may not be enough.

Trying to follow the steps suggested by Rusty and Erik, I hit the wall with attempt to use sudo -u _postgress .....

command. The system outputs:

sudo: unable to change to runas uid (216, 216): Operation not permitted

Any suggestions what might be the problem?

Many thanks

Daniel

Strange, I just upgraded (maybe downgraded?) my server to the latest versions of Maverick and Server.app and postgres seems to be working fine.

sh-3.2# serveradmin status postgres

postgres:state = "RUNNING"

Is there something specific I should check to make sure?

just to confirm. i have the same issue after the upgrade to 3.2.1

Running and responding are two different things.

Rusty Ross, thanks for your suggestion. I tried psql and here is what I got:

psql: could not connect to server: No such file or directory

Is the server running locally and accepting

connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

So I assume I suffer from the described problem.

One more stupid question, if you do not mind. Is there anyway to tell if postgres is used in my system? I mean I haven't seen the system misbehaving in any way with postgres not working correctly, so shall I assume that I am not using it anywhere?

Again, thanks.

The command that the (plist corrected) serveradmin runs is:

sudo -u _postgres /Applications/Server.app/Contents/ServerRoot/usr/bin/postgres -D /Library/Server/PostgreSQL/Data -c listen_addresses=127.0.0.1,::1 -c log_connections=on -c log_directory=/Library/Logs/PostgreSQL -c log_filename=PostgreSQL.log -c log_line_prefix=%t -c log_lock_waits=on -c log_statement=ddl -c logging_collector=on -c unix_socket_directories=/private/var/pgsql_socket -c unix_socket_group=_postgres -c unix_socket_permissions=0770

cd to /Library/Server/PostgreSQL/Data as root and run this command. If it works, then the serveradmin start postgres command should work. If it doesn't, look at the log file to see why postgres isn't starting.

The old PostgreSQL 9.2 binaries are located at "/Applications/Server.app/Contents/ServerRoot/usr/libexec/postgresql9.2/" for OS X Server 3.2.1.