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-8.2 postgresql-client-8.2

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/8.2/main/postgresql.conf. For a development environment, we can make life simple by making a change to /etc/postgresql/8.2/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.

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

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

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

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.

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