An afternoon setting up Postgres (9.2) on OS X (10.7, perhaps & higher)

Some notes I jotted down after spinning up Postgres this afternoon. Nothing fancy about the setup here, should be just about enough to get you up and running with the basics.

Installation (ala Homebrew)
$ brew install postgresql
To do anything with it, you actually need to create a ‘database system’ with:
$ initdb /usr/local/var/postgres -E utf8
this directory is basically where the database system lives and is generally used by all control commands (as seen below)

Enable access via /usr/local/var/postgres/pg_hba.conf and incoming connections on tcp via (database system path above)/postgres.conf. These files are heavily commented and worth browsing thru to see how. Ultimately I didn’t need to edit pg_hba.conf at all, but did uncomment the localhost & port variables from postgres.conf to accept incoming tcp connections.

Control & Start-up
start single threaded:
$ postgres -D /usr/local/var/postgres
pg_ctl is a handy controller for postgres
start in the background:
$ pg_ctl -D /usr/local/var/postgres -l postgres_server.log start
check the status:
$ pg_ctl -D /usr/local/var/postgres status
stop the (presumably background) job:
$ pg_ctl -D /usr/local/var/postgres stop -s -m fast
Interact and create Database(s) and User(s)
Postgres’s CLI for interaction is psql. It should already be on your path. Connect psql to the running instance (make sure the Postgres db server is running using the status command above)
$ psql -h 127.0.0.1 -p 5432
(b/c in this setup case, we’re using tcp to connect i.e., in configs steps) and might as well add port param to be doubly sure this all works before wiring into a project). At psql’s prompt…
  • \du   lists users and roles
  • \l    shows all databases
  • \?    shows more psql commands
Create a database and user, and give the user perms on the db
CREATE USER [username] WITH ENCRYPTED PASSWORD '[password]';
CREATE DATABASE [database] ENCODING 'UTF8' OWNER [username];

Wire into a project, django today so in settings.py, remember to set host to “127.0.0.1” and might as well set port to “5432” for good measure (assuming that’s the right one from the postgres configs.

Update: great list of “PostgreSQL Basics by Example” on github from darthdeus to help from here! http://darthdeus.github.io/blog/2013/08/19/postgresql-basics-by-example