How to create a new instance of PostgresSQL on OS-X 10.8 Server

Question

After upgrading my MacMini home server from OS-X 10.7 (Lion) Server to OS-X 10.8 (ML) + Server.app, I saw PostgreSQL 9.1.4 was released.

Unfortunately, the setup process for PostgreSQL 9.1.4 has created these brand new databases: collab, webauth, postgres, tempalte0, template1; all the other databases and roles created by me seem to have been deleted!

To better understand what has happened:

> Which postgres

OSX 10.7.4: /usr/bin/postgres
OSX 10.8: /Applications/Server.app/Contents/ServerRoot/usr/bin/postgre

$PGDATA is now on:

OSX 10.7.4: "/var/pgsql"
OSX 10.8 "/Library/Server/PostgreSQL/Data"

After the update the /var/pgsql folder is gone.

Since I do not want to repeat this experience in future updates, I think it is a good idea to create my database on a separate cluster/instance.

How would advise me from here?

• Is there any other way to avoid losing my databases?
• Where and how should I create my own databases without interfering with Apple's databases?
• How can I configure and start a second instance of PostgreSQL on the same OS-X server?

Answer 1

"all others databases and roles created by my own are lost!"

I can't speak to the best practices for creating a new database, but as part of the Mountain Lion Server upgrade all of the "old" Postgres data should have been backed up to a subdirectory under /Library(/Server?) Unfortunately as soon as I discovered this I moved-and-renamed the directory for safekeeping so I don't have the exact name!

I have a completely separate install of Postgres that I built myself for webapp purposes, but I certainly wouldn't call what my approach a "best practice". Largely what I did was build a "stock" install using a very similar set of configure parameters as Apple did (discovered by running pg_config) but with a different install location and default port setting. Following that I coptied-and-tweaked /Applications/Server.app/Contents/ServerRoot/System/Library/LaunchDaemons/org.postgresql.postgres.plist and that's worked fine for me so far.