OS X Lion Server multiple postgresql installs

homebrew

I am using Homebrew to install PostgreSQL on my Mac mini server (Lion). However, I am unable to run the version I have installed, the only version I see running is the version installed with OS X:

216    83     1   0 12:17PM ??         0:00.47 /usr/bin/postgres -D /var/pgsql -c listen_addresses= -c log_connections=on -c log_directory=/Library/Logs -c log_filename=PostgreSQL.log -c log_lock_waits=on -c log_statement=ddl -c log_line_prefix=%t  -c logging_collector=on -c unix_socket_directory=/var/pgsql_socket -c unix_socket_group=_postgres -c unix_socket_permissions=0770
216   266    83   0 12:17PM ??         0:00.27 postgres: logger process                           
216   300    83   0 12:17PM ??         0:01.01 postgres: writer process                           
216   301    83   0 12:17PM ??         0:00.75 postgres: wal writer process                           
216   302    83   0 12:17PM ??         0:00.51 postgres: autovacuum launcher process                           
216   303    83   0 12:17PM ??         0:00.91 postgres: stats collector process                           
1025  2698   814   0  1:30PM ttys000    0:00.00 grep post

How do I go about starting another postgres instance and having it run on startup?

Best Answer

The Homebrew postgresql formula tells you exactly how to do this when you install it. Run brew info postgresql to have that information shown again.

Related Solutions

Homebrew installed PostgreSQL for use by multiple accounts

According to the postgres output, you need to run the postgres server as the same user that creates the database with initdb.

mr-clean:~ smurf$ initdb /usr/local/var/postgres -E utf8
The files belonging to this database system will be owned by user "smurf".
This user must also own the server process.

That means the same user will need to launch the postgres server process. Other users will still be able to launch the client and connect to it, though. So you just need to do the "admin" tasks - setup and daemon control - with that owner account.

If you want to do everything with the one mobile account, then just run initdb as mobile instead of as admin after doing the brew install postgresql as normal. (It's not who you brew install as that matters, it's who you initdb as.) This'll create the database, and rwx permissions on /usr/local/var (not /usr/local) will be enough to allow this. Then do all your PG work as mobile. The LaunchAgent should work with this approach, though I haven't tested it.

To allow multiple users to effectively manage the postgres server (launch and stop the daemon), set it up basically the same way, with a dedicated postgres (or mobile) account that both does the initial initdb and brings the server up or down. Then create a little script to ssh in to localhost as postgres and run the command to bring the server up or down with the same command you'd use in the single-user case. And set up ssh keys so that all the accounts you want to manage PG can ssh in as postgres (that is, cat their public keys to postgres' ~/.ssh/authorized_keys file). Now you can effectively have multiple users manage Postgres without deviating from the dedicated-daemon-user model that Postgres expects and fiddling with file permissions and so on.

This ssh technique works for any service that needs to be controlled by a dedicated account, and will generalize to the case where you're running your things on different machines.

How to restart PostgreSQL installed by Brew in OSX

Manually Start PostgreSQL:

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Manually Stop PostgreSQL:

pg_ctl -D /usr/local/var/postgres stop -s -m fast

This stackoverflow discussion really goes into some great detail, with many more options. I used it to create an alias to just run:

postgresql.server stop
postgresql.server start