Opinionated OS X
From PostgreSQL wiki
There are many different ways to install and use PostgreSQL on OS X. That variety is good in that the different installations have advantages and disadvantages, but it makes starting with PostgreSQL on OS X much more complex than it needs to be, particularly for beginners who just want to get a database up and running. The different installations are subtly incompatible - the clients from one won't connect to the server from another without additional configuration - and which server will "win" and start up correctly on a reboot isn't terribly well defined. If the user installs two conflicting installations that can lead to PostgreSQL mysteriously breaking, or appearing to lose data after a reboot.
This document intends to make that process simpler, by offering a single good solution rather than many alternatives.
Who is this for?
- You want to learn SQL using PostgreSQL
- You're developing web applications that use PostgreSQL as a database
- You're developing desktop applications intended to connect to PostgreSQL
- You want a solid database running on your laptop for data analysis
- You want to use PostGIS for Geographical Information work
If any of these describe you, and you use OS X, keep reading.
Who is this not for?
- You're deploying a production (as opposed to development or QA) database
- You need your database to be available when you're not logged in
- You want to modify core PostgreSQL code
- You're developing extensions to PostgreSQL
- You want to use third party extensions
If you've installed PostgreSQL previously, uninstall it. If you installed via homebrew, run brew remove postgresql. If via macports, run sudo port uninstall postgres. If you used the EnterpriseDB installer, find and run uninstall-postgres.app.
Make sure that there are no existing installations of PostgreSQL running. Open a terminal window and run:
ps auxw | grep postgres
The result should look something like this:
satsuke:~$ ps auxw | grep postgres dyld: DYLD_ environment variables being ignored because main executable (/bin/ps) is setuid or setgid steve 16241 0.0 0.0 2432768 624 s000 R+ 7:04PM 0:00.00 grep postgres
If there are more results than that, you need to find the existing PostgreSQL installation and make sure it is shut down.
If you've ever installed PostgreSQL before you might want to reboot and check again, to make sure it's not starting at boot. If something is starting at boot, it's probably located in a LaunchAgents folder you can see with the `ps auxw | grep postgres`. Navigate to that folder and manually delete the Postgres apps in it. Restart your computer to kill other processes.
Download Postgres.app from http://postgresapp.com/ , double-click on it to unzip it, then drag Postgres.app to your Applications folder.
To start the PostgreSQL server run Postgres.app from your Applications folder. Not much will seem to happen, but a small elephant will appear in your menu bar. The elephant opens a menu that lets you shut down the server, or configure it to start automatically when you log in.
Use the right clients
Next you need to configure your PATH such that you use the Postgres.app provided client tools (such as psql or createdb) rather than the ones that are installed as part of OS X. Open or create the file .profile in your home directory with your favorite text editor. To the end of the file add this:
Open a new terminal and check that your path is correct by running which psql. It should show something like /Applications/Postgres.app/Contents/Versions/latest/bin/psql.
Create and access a database
To create a new database called mydb, open a terminal and run
To log in to the database using psql, open a terminal and run
From there you can follow the postgresql tutorial.
Accessing the database from other applications
There are some connection string examples in the Installing Postgres.app and How to connect sections but for applications that are running under your user id all you need to enter is the hostname ("localhost"), the username (the username you're logged in as) and your database name.
If you need to connect from applications not running as you, you'll need to set up authentication as discussed in the PostgreSQL manual.
Developing with Postgres.app
To build database access libraries (such as DBD::Pg for perl) you may need to know the directories where include files and shared libraries are installed.
Include files: /Applications/Postgres.app/Contents/MacOS/include
Shared libraries: /Applications/Postgres.app/Contents/MacOS/lib
If you're building C or C++ code a more portable way to configure include and library directories is using pg_config - "pg_config --includedir" and "pg_config --libdir".
Backup and restore
To back up a single database mydb to a file run this:
pg_dump --format=custom --file=mydb.dump mydb
To restore the dump, destroying any existing content of the mydb database run this:
pg_restore --dbname=mydb --clean mydb.dump
For more details about backups, see the PostgreSQL manual.
(If you're repeatedly restoring the same data as part of your development or QA environment you might want to look at the TEMPLATE option to the CREATE DATABASE command as an alternative.)