Administering PostgreSQL in Mac OS X 10.8 Server (Server 2.2 and beyond?)

Those who are familiar with the Apple Mac OS X Server software product know that Apple recently deprecated the internal database (MySQL) in favor of the more robust, but lesser known PostgreSQL—or postgres to many, as that was the original name, which we'll use for the rest of this blog post.

For those using Mac OS X Server 10.6 and earlier, MySQL is still the default. From the emergence of Lion (10.7) forward to today's Mountain Lion (10.8) there's no trace of MySQL.

In addition, there's really no way to safely use postgres for custom content (read, non-Server databases) and there's really no way to remotely start and stop a postgres database engine short of turning on remote SSH login.

That's right: even the Server application itself, when used to remotely administer a running instance of Server, cannot start and stop a postgres database, at least of of this writing, via Server 2.2.1 (build 169).

Ok, so what's the best way to administer the postgres database on Mac OS X Server 2.2.1? Via SSH remote login.

Yes, it's clunky and requires the use of Terminal (and a bit of faith that SSH really will provide a secure connection) but it can be done.

Here are the steps for accessing postgres administration via a remote computer that is running a separate copy of the Server application FOR ADMINISTRATION PURPOSES ONLY.  For instance, we choose to administer one of our OS X Server installations from a remote machine (a Macbook Air with the Mac OS X 10.8.3 operating system).

Remember that enabling Server for administration from the remote device is of no benefit  when it comes to administering Server's built-in database engine (Postgres) . . . but at least the remote administration via the Server app does seem to have some limited benefits for those who are hesitant to use command line interface (CLI) to control Server. For those who want to use Server to control PostgreSQL, you're SOL.

1. Launch the Server app on the actual server unit (ours is a Mac Mini using an OS X 10.8.3 base operating system) and click the checkbox next to "Allow remote administration using Server"

2. Either from Server from either the Server app on the actual server unit or from the Server app on a remote Mac, click first on the name of the server (top left corner).

3. Check the check box next to "Allow remote login via SSH" to enable SSH remote login via Terminal. It can be found under the Settings tab in the middle top of the screen as shown in the picture below.

4. If you've installed Server on a remote device (like our Macbook Air) and want to control the postgres database engine, click on the arrow next to the "Allow remote login using SSH" text. This will launch Terminal on the remote machine.

5. You should now see a bit of heartwarming text, noting that  "The authenticity of host 'nun.nnn.nnn.nnn (nun.nnn.nnn.nnn)' can't be established". Don't panic

6. If that line is followed by an RSA key fingerprint, consisting of 16 pairs (32 alphanumeric characters, with each two characters separated by a colon (:) you SHOULD be OK to proceed.  In fact, Terminal will make you second-guess yourself by noting:

Are you sure you want to continue connecting (yes/no)? 

7. After typing "yes" and hitting the return key, you will be met by another ominous warning:

Warning: Permanently added 'nnn.nnn.nnn.nnn' (RSA) to the list of known hosts.

8. At this point, you should be able to proceed by responding to the password challenge. Once you've moved beyond this point, it's just like a standard SSH (or older, non-secure telnet) session. To take control of PostgreSQL, enter the following line:

sudo serveradmin start postgres

9. After another password challenge, the Terminal bash will return the state of PostgreSQL, with a line such as postgres:state = "RUNNING" to confirm postgres is indeed running.

This is a new feature in Server 2.2.1, as Apple has modified the way that it handles custom content. In fact, a recent Apple release note mentions that custom content in Server versions prior to 2.2 will not be migrated when 2.2 is installed.


OS X Server v2.2 now stores Postgres data needed by services in its own database (“/Library/Server/PostgreSQL For Server Services” by default). If you migrate from a previous version of OS X Server, system Postgres data will be migrated from /Library/Server/PostgreSQL to “/Library/Server/PostgreSQL For Server Services”.  Any custom content you may have created in /Library/Server/PostgreSQL will remain there after upgrading to OS X Server v2.2. If you wish to access this data, or if you’re installing OS X Server v2.2 for the first time and wish to create new Postgres data, you may need to start the service with this sudo serveradmin start postgres command. This will start a Postgres process separate from the one used by services, which can be used to serve your own content.


10. To stop postgres, issue the same command as above, but substitute the word stop for start and you should then receive, without a password challenge, the response postgres:state = "STOPPED"

11. Note that the requirement for a password challenge and response is only required ONCE per session, so subsequent start commands or other commands for postgres will not require a password. Be careful, since this means typing sudo serveradmin stop postgres will halt PostgreSQL immediately.

But what if you've opted not to install Server on a remote machine, and just want to hit a quick Terminal window to access the remote Server installation? We'll cover three simple steps in our next blog post.