Postgres Database Upgrade With New Server.app

The recent Server.app upgrade also upgrades postgres. For those using Server.app's postgres to host a local postgres server, you'll have to update the old database by hand. There are two methods: (1) pg_upgrade; (2) dump/init/restore a pg backup. The second option is often easier and quicker, especially if you keep dump backups of your databases. Here are the commands.

# Backup database (pre-update)

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

# Post Server.app upgrade

sudo mv /Library/Server/PostgreSQL/Data /Library/Server/PostgreSQL/Data-9.3

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

sudo diff /Library/Server/PostgreSQL/Data/pg_hba.conf /Library/Server/PostgreSQL/Data-9.3/pg_hba.conf

sudo vi /Library/Server/PostgreSQL/Data/pg_hba.conf # add diffs if desired

sudo launchctl load -w /Library/LaunchDaemons/org.postgresql.postgres.plist # launch the local postgres server; see below

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

Here's how to configure your own local postgres server using the Server.app infrastructure:

Local postgres management is no longer supported through the serveradmin command. There are two options:

1. Adapt Server.app's existing postgres infrastructure to run your own local postgres service.
2. Install and manage your own postgres server through macports or homebrew.

I prefer Option 1 because Server.app depends on postgres for other Server services and will rely on Apple to upgrade PostgreSQL, rather than adding an extra independent service I have to remember to maintain and manage. If Apple ever irretrievably breaks postgres in Server.app, it's just postgres, so Option 2 is always on the table.

To adapt Server.app's existing postgres infrastructure, you just need to configure your own local PostgreSQL instance (which probably already exists in /Library/Server/PostgreSQL), add a launch daemon for Server.app's postgres binary that uses this configuration, and make sure you have regular backups of both the configuration and the database.

Do these steps:

# copy PostgreSQL configuration

sudo rsync -va /path/to/old/PostgreSQL/ /Library/Server/PostgreSQL

# ensure that pg_hba.conf is configured as you want. Here's a permissive example.

cat /Library/Server/PostgreSQL/Data/pg_hba.conf

...

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only

local all all trust

# IPv4 local connections:

host all all 127.0.0.1/32 trust

# IPv6 local connections:

host all all ::1/128 trust

# Allow replication connections from localhost, by a user with the

# replication privilege.

#local replication _postgres trust

#host replication _postgres 127.0.0.1/32 trust

#host replication _postgres ::1/128 trust

# Admin added dbs

host snort snort 127.0.0.1/32 password

Don't confuse these next two plist files, which have the same name but live in different directories.

# postgres configuration plist org.postgresql.postgres.plist

# postgres configuration plist

sudo cp org.postgresql.postgres.plist /Library/Server/PostgreSQL/Config/

org.postgresql.postgres.plist (postgres configuration plist)

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">

<plist version="1.0">

<dict>

<key>ProgramArguments</key>

<array>

<string>-D</string>

<string>/Library/Server/PostgreSQL/Data</string>

<string>-c</string>

<string>listen_addresses=127.0.0.1,::1</string>

<string>-c</string>

<string>log_connections=on</string>

<string>-c</string>

<string>log_directory=/Library/Logs/PostgreSQL</string>

<string>-c</string>

<string>log_filename=PostgreSQL.log</string>

<string>-c</string>

<string>log_line_prefix=%t </string>

<string>-c</string>

<string>log_lock_waits=on</string>

<string>-c</string>

<string>log_statement=ddl</string>

<string>-c</string>

<string>logging_collector=on</string>

<string>-c</string>

<string>unix_socket_directories=/private/var/pgsql_socket</string>

<string>-c</string>

<string>unix_socket_group=_postgres</string>

<string>-c</string>

<string>unix_socket_permissions=0770</string>

</array>

</dict>

</plist>

# launch daemon org.postgresql.postgres.plist

sudo cp org.postgresql.postgres.plist /Library/LaunchDaemons

sudo launchctl load -w /Library/LaunchDaemons/org.postgresql.postgres.plist

org.postgresql.postgres.plist (launch daemon)

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">

<plist version="1.0">

<dict>

<key>Label</key>

<string>org.postgresql.postgres</string>

<key>UserName</key>

<string>_postgres</string>

<key>GroupName</key>

<string>_postgres</string>

<key>ProgramArguments</key>

<array>

<string>/Applications/Server.app/Contents/ServerRoot/usr/bin/postgres</string>

<string>--apple-configuration</string>

<string>/Library/Server/PostgreSQL/Config/org.postgresql.postgres.plist</string >

</array>

<key>KeepAlive</key>

<dict>

<key>PathState</key>

<dict>

<key>/Library/Server/PostgreSQL/Data</key>

<true/>

</dict>

</dict>

<key>StandardErrorPath</key>

<string>/Library/Logs/PostgreSQL/PostgreSQL.log</string>

<key>StandardOutPath</key>

<string>/Library/Logs/PostgreSQL/PostgreSQL.log</string>

</dict>

</plist>

Finally, see this thread for how to independently backup Server.app and OD data. The specific postgres commands are:

# 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