How to setup Postgres

Postgres is a transactional sql open source database that has some similarities with Oracle. For a production environment, Heroku Postgres is quite a great worry-free service.

Setup on OS X

To install on OS X, via brew:

brew install postgresql

After you’ve setup

initdb /usr/local/var/postgres
mkdir -p ~/Library/LaunchAgents
ln -sfv /usr/local/Cellar/postgresql/*/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

To start postgres on a homebrew install, run:

launchctl start homebrew.mxcl.postgresql

To stop on homebrew install:

launchctl stop homebrew.mxcl.postgresql

Tip: You can see a list of all services running in launchctl by running `launchctl list`

Configuration is stored in /usr/local/var/postgres/postgresql.conf.

Setup on Ubuntu

To install Postgres 9 packgages you need to add Martin Pitt’s PPA, via:

sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update

To install on Ubuntu, run:

sudo apt-get install postgresql-9.0 postgresql-contrib-9.1 libpq-dev

Configuration is stored at /etc/postgresql/9.0/main and data at /var/lib/postgresql/9.0/main.

For development, you can turn off ident user authentication strategy for the db users, this will trust all local connections. Replace all instances of ident with trust in /etc/postgres/9.0/main/pg_hba.conf.

Current pg_hba.conf file:

localhost   all   all   ident

changes to:

localhost   all   all   trust

To start postgres on ubuntu run:

sudo service postgresql start

Setting up a postgres user

To setup a postgres db user, you’ll need to run the following:

On Ubuntu, you’ll need to be postgres system user, by running sudo su postgres.

createuser -s -P appadmin
Enter password for new role: password
Enter it again: password

Obviously, you should pick a secure password for setting this up.

Tooling

To manage your postgres db, pgAdmin cross-platform tool comes in very handy. To install on OS X, grab the dmg from their site. Alteratively, you can just use the psql command line tool.