Accessing Postgres On The Command-Line on Mac OS X

I simply wanted to restart the Postgres server. I'm trying to enable SSL/TLS, and such settings are checked only when Postgres starts. Postgres is set to auto-start when the Mac boots, if you install using EnterpriseDB's free Mac installer.  But I want to restart Postgres manually, without rebooting the Mac.

You'd think restarting Postgres would be easy, but no. The pgAdmin program is not built to stop or start the Postgres server, at least not that I could find. The command line tool "pg_ctl" can check the status of a server as well as start and stop. That command even has a handy "restart" option that effectively does both the stop and start. But I cannot run that pg_ctl from my own command-line in the Terminal app. The command must be run as the 'postgres' unix user created by the Postgres installer.

So, the answer is simple, right? Run the command as "sudo", the superuser? That usually is the answer with Mac command-line limitations. But, no, not in this case. The command must be run as another user, not just with enhanced privileges. So a bit of googling might lead you to use the "su" command to act as another user, like this:

  su postgres

This fails though I don't know understand why. After much googling, I finally found the trick:

  sudo su - postgres

It takes the combination of "sudo" and "su". Also notice the hyphen with spaces.

Now I can (I mean 'postgres user can) run Postgres utility programs. The next problem is that those utilities are not found automatically from the command line. So instead of just typing "pg_ctl" in the Terminal, type the entire path to the program, such as:
  /Library/PostgreSQL/9.0/bin/pg_ctl

Tip: Rather than type, just find the programs in the Finder (or the very handy Path Finder app), then drag-and-drop to the Terminal app.

Yet another problem to solve. Some of those commands need to know where the Postgres "data" folder is. Again, you can drag and drop the path, or use Path Finder's Copy Path > Unix menu item.

So for example, to check the status of the Postgres server, run these two lines in the Terminal. Run them separately, one after the other.

  sudo su - postgres
  /Library/PostgreSQL/9.0/bin/pg_ctl status -D /Library/PostgreSQL/9.0/data

To avoid including the full paths, you can tell your Mac to include those folders when looking for programs. But that’s a topic for another day.

I'm using Postgres 9.0.4 on Mac OS X 10.6.7.