Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
1  
Is it at all remotely possible that you have two instances running? –  swasheck Aug 6 '12 at 13:22
    
@swasheck definitely, only one instance running, checked by: ps -ef | grep postgress –  Franco Rondini Aug 7 '12 at 10:16
add comment

1 Answer

up vote 1 down vote accepted
"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.

share|improve this answer
    
Thanks you for the support, /Library/Server/Previous it's a very usefull hint, for example I found the old plists in /Library/Server/Previous/System/Library/LaunchDaemons and the old PGDATA in /Library/Server/Previous/private/var/pgsql/. Shame Library/Server/Previous/Library/Server/PostgresSQL/Backup is empty (btw: I had not activated TimeMachine). Now I'm wondering that maybe it would be possible to restart, even the old instance (9.0.4) possibly on another port. –  Franco Rondini Aug 7 '12 at 10:34
    
The idea of having a completely separate install of Postgres I think it's great, nevertheless a checklist or link to some pretty good story that explains how to do it step by step would be very welcome. The problem primarily is: how to determine which path on filesystem to use to be sure that the next system updates do not impact on our instance. –  Franco Rondini Aug 7 '12 at 10:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.