PostgreSQL

Documentation

The online PostgreSQL Manual is actually quite awesome and worth bookmarking.

Install and Configure

Assuming Debian or Ubuntu, installation is:

sudo apt-get install postgresql postgresql-client

Check The Configuration

You may end up with (and may require) several different versions of PostgreSQL on one box. If this is so, check that they are running on separate ports. The port is specified in /etc/postgresql/9.1/main/postgresql.conf. For a development (NOT production!) environment, we can make life simple by making a change to /etc/postgresql/9.1/main/pg_hba.conf:

local  all     all                            trust 
host   all     all         127.0.0.1/32       trust

That is, changing the authentication from md5 to trust for local socket-based connections and TCP connections from localhost. On production servers, pg_hba.conf provides the means for a powerful and atomic user- and host-based authentication system to really lock the whole thing down nicely.

NOTE: If you have changed the port or anything else in postgresql.conf, you will need to stop then start the service, as reload and restart are not sufficient! This is a nasty trap for young players.

Creating Users and Databases

PostgreSQL system administration is somewhat different in approach to MySQL, but actually much less retarded once you get the hang of it.

With PostgreSQL creating users and databases is done with shell level commands createuser and createdb, rather than lame non-standard SQL commands (although you can also use SQL-ish statements like MySQL if you insist, but they are not covered here).

Make Yourself A Superuser

Out of the box, the PostgreSQL server runs as the postgres user, so to create yourself an administrator account, you need to run the createuser script as the postgres user:

sudo -u postgres createuser <yourusername>

Use your shell account name and answer Y to all questions to give yourself a superuser account. Unless told otherwise, PostgreSQL will try to use the shell account to save time. If you don't want question prompts, you can go

sudo -u postgres createuser -srdP <username>

NOTE: Reload the PostgreSQL server to pick up the new privileges.

Switches for createuser

Switch	Behaviour
-s	User will be a superuser
-S	User will not be a superuser
-r	User can create other roles (users)
-R	User cannot create other roles (users)
-d	User can create databases
-D	User cannot create databases
-P	Prompt to create a password for the user

Common Switches For All Commands

Switch	Behaviour
-h	Host - hostname of the PostgreSQL server (defaults to localhost)
-p	Port - PostgreSQL server port (defaults to 5432)
-U	User - specify the user to connect as (defaults to the shell user)
-W	Force PostgreSQL to ask you for a password before connecting

Create Non-Privileged Users

Usually, you give yourself admin rights, and then get your applications to use non-privileged accounts. For production boxes, that's usually one account per different application and/or database, but for a development box we can often just use a single dev account:

createuser -SDRP devuser

Drop Users

You can also drop a user with the dropuser shell command:

dropuser <username>

Create A Database

Now we can create a database. Being 2008, we like to use UTF8 so we need to specify that, since the default is still ASCII.

createdb -O devuser -E UTF8 <databasename>

Switches for createdb

Switch	Behaviour
-O	Owner - the user that owns the database
-E	Encoding - almost always UTF8

Drop A Database

You can also drop a database with the dropdb shell command:

dropdb <databasename>

Using The Client

The cli client is psql. To get a list of databases on the system, you can use the -l switch:

psql -l

Connect To A Database

If your database has been created, you should be able to use the PostgreSQL client:

psql <databasename> [<username>]

Since you are a superuser, you should be able to get amongst it without having to specify the username.

Backslash Commands

TODO: once at the psql prompt, type \? for help with the various backslash commands, and \h for help with SQL syntax.

Command	Behaviour
\dt	List the tables
\d <tablename>	Show table schema
\x	Toggle to horizontal format when displaying rows
\i <filename>	Execute a bunch of SQL in the file
\cd <path>	Change directory (useful for \i)
\c <database>	Connect to another database
\q	Quit

Backing Up And Restoring Databases

We use the pg_dump and pg_restore shell commands. Ownership of the database objects is probably the most common source of confusion here. Since most of the time ownership will not be more atomic than database-level, it is usually easiest to avoid the whole issue by not including ownership data in the dumps. It can then be safely determined by specifying the owner during the restore. This also means you can change the ownership if necessary, without unnecessary bloodshed.

Backup

To create a compressed format (-Fc) dump file (-f), that does not specify object ownerships (-O), execute the following:

pg_dump -Fc -O -f <dumpfile> <databasename>

Restore

During a restore, the ownership is set to whichever user the restore command connects to the database as. So to restore a dump file into an existing database (-d), without using ownerships from the file (-O), but specifying the connecting user (-U) as the owner:

pg_restore -O -U <username> -d <databasename> <dumpfile>

NOTE: You usually want the database to be empty, having (re)created a new one for the restore.

Troubleshooting

I can't drop the database!: You may need to stop Apache before you can drop a database, otherwise PostgreSQL will complain that it is in use.; Or, you can create a /usr/local/bin/pg_kill_connections script:

#!/bin/sh
DATABASE="$1"

if [ -z "${DATABASE}" ];then
    echo "Usage: pg_kill_connections dbname"
    exit 1;
fi

for i in $(su postgres -c \
       "psql -tc \"SELECT procpid FROM pg_stat_activity WHERE datname='${DATABASE}'\" \
        template1 | xargs -r kill"); do
    kill $i
done

How do I rename a database?: ALTER DATABASE "foo" RENAME TO "bar";

How do I concatenate strings?: SELECT 'Hello' || ' ' || 'World';