Installing and configuring PostgreSQL 9.1 on Ubuntu 12.04 for local Drupal development

I am constantly referring to several shell scripts to help with PostgreSQL, so this blog post is merely a brain dump on how I find it easiest to install and configure for local Drupal development. These settings should be tweaked moving to staging or production, they are mainly done to help the developers.

Install the packages

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql phppgadmin

Create the users and set permissions

Start by switching to the postgres superuser:

seanh@wiifmonthego ~  » sudo su - postgres

From here you can create new users:

postgres@wiifmonthego:~$ createuser drupal
Shall the new role be a superuser? (y/n) y

and change their passwords:

postgres=# alter user postgres with password 'crazylongpassword';
ALTER ROLE
postgres=# alter user drupal with password 'password';
ALTER ROLE
postgres=#\q

Relax PostgreSQL access control

When in development, I often find myself dumping, restoring, dropping and re-creating databases all the time, so I find having to remember the password to be frustrating. Luckily PostgreSQL lets us trust certain users when coming from certain domains.

First edit the main PostgreSQL access control file

sudo vim /etc/postgresql/9.1/main/pg_hba.conf

Add in your development user with the method 'trust'

# Database administrative login by Unix domain socket
local   all             postgres                                peer
local   all             drupal                                  trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD

Now restrict connections to allow only localhost

This is needed to stop any unwanted network traffic to your local development box

sudo vim /etc/postgresql/9.1/main/postgresql.conf

Uncomment the 'listen_addresses' section

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = 'localhost'    # what IP address(es) to listen on;

Restart the PostgreSQL cluster

In order to make the above changes take effect you need to restart the service

seanh@wiifmonthego ~  » sudo service postgresql restart
 * Restarting PostgreSQL 9.1 database server

Handy PostgreSQL command line examples

These gems I use a lot.

Create a database

createdb -p 5432 -O drupal -U drupal -E UTF8 testingsiteone -T template0

Dropping the database

dropdb -p 5432 -U drupal testingsiteone

Dumping the database

pg_dump -p 5432 -h localhost -Fc -U drupal --no-owner testingsiteone > /tmp/testingsiteone_$(date +"%Y-%m-%d_%s").pgdump

Restoring the database

pg_restore -p 5432 -h localhost -Fc -d testingsiteone -U drupal --no-owner < /tmp/path-to-the-file.pgdump

Comments

Are there any other steps you normally take when setting up PostgreSQL for Drupal development? If so, let me know in the comments.