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.

2013 Quickbooks Pro "matching" bug persists

Having participated in a number of beta tests for Intuit products, at the request of an Intuit VP after finding a significant calculation error in TurboTax a few years ago, I've had the privilege of working with both Mac- and Windows-based products before they are shipped.

One bug I found—and reported—for the 2012 Quickbooks Pro software continues to be a problem. I wasn't chosen for the 2013 Quickbooks Pro beta tests for either operating system platform, but I've since confirmed that the bug is persistent in 2013.

The error stems from the "Downloaded Transactions" matching feature in Quickbooks. For those unfamiliar with "Downloaded Transactions" this feature allows credit card (or other bank account transactions) to be downloaded into Quickbooks and then matched against entries in the linked account.

For example, if I eat at McDonald's and enter the McDonald's transaction into my AMEX card account in Quickbooks, then later download all the most recent AMEX charges, the matching feature will note that I have a dollar amount and date matching between the "Downloaded Transactions" and the linked QuickBooks account.

It almost works like magic, with the matched charges automatically highlighted by the presence of an orange dot in the Downloaded Transaction window (as opposed to those that aren't matched being represented by a white dot).

I say "almost works like magic" because it turns out that the bug will automatically match transactions with the same amount, even if they are from a date weeks before and from a completely different vendor. This might not be a big deal for some transactions, but it leads to mass confusion when multiple wrong "matches" are registered, and can even lead to doubling up on erroneous entries, since the intended matches are left unmatched in the Downloaded Transactions window.

Here's the real-world example that shows the bug, present in the 2012 Quickbooks Pro beta, is still present in the 2013 shipping versions of QuickBooks Pro.

Having taken an extended trip, where I didn't have time to download transactions every few days, but did have downtime to enter the credit card receipts into QuickBooks (on a few train rides in India and Europe where I didn't have always-on data connectivity), I felt good about keeping up with the expenses for various expense reports.

When I finally reached a place where I could pull down transactions from the past three weeks of multi-continent travel, I noticed that the Downloaded Transactions window had "matched" several earlier transactions with entries in my QuickBooks credit card account register. But rather than matching the ones closest to the date of the transaction, it was matching to transactions from several weeks prior.

In other words, it was a bug around first-in vs last-in (FIFO and LIFO, to use two old computer and Finance 101 terms). Where QuickBooks should have matched to older transactions (FIFO) it was actually matching to the more recent transactions (LIFO).

I was able to repeat this a few weeks later, with a new set of data, where the downloaded transactions contained two pairs of transactions that were both for the same dollar amount ($1.10 for the first set and $2.19 for the second set).

The earliest transactions, one each for $1.10 and $2.19,  had both been for McDonald's purchases, one day apart (12/4 and 12/5, respectively, for the $1.10 and $2.19 amounts). In the credit card account register, those two transactions had been entered, splitting out the tax and food, but equaling the total amounts noted above (although you'll never find those using the Find function in QuickBooks, but that's another bug story for another day).

In addition to the 12/4 and 12/5 McDonald's purchases, there were two other transactions already entered into the credit card account register, a 12/21 McDonald's purchase for $1.10 and a 12/23 Subway purchase for $2.19. The McDonald's is a repeat of an exact same order two weeks later, and the Subway was a promotional deal. Each were clearly entered as McDonald's and Subway, respectively.

When I checked the automated matching for the 12/4 and 12/5 McDonald's transactions, they did not show as marked in the credit card register, but they did show matched in the Downloaded Transactions.

On further inspection, QuickBooks had once again marked off two more recent charges, the 12/21 and 12/23 McDonald's and Subway duo.

To see the "matched" transactions side by side, note the check marks and orange dots in each of the next two images:

The average user of QuickBooks isn't going to pick this up, but will be seriously confused when automated matching mis-matches transactions, leaving the intended transactions without a mate and perhaps causing a QucikBooks user to assume a large check or credit card amount has cleared when in fact it's just's Intuit's ongoing matching error that's at fault.

I sense a liability issue for Intuit if someone's set of matching $9,013.42 transactions are inadvertently mis-matched, especially since the issue has been present for at least two shipping versions of QuickBooks Pro on both Mac and Windows platforms.