essandess

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

Mac mini, OS X El Capitan (10.11.4)

Posted on Mar 21, 2016 7:53 PM

Close

Q: Postgres Database Upgrade With New Server.app

  • All replies
  • Helpful answers