PostgreSQL

Documentation

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

Install:

sudo apt-get install postgresql-8.2 postgresql-client-8.2

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

Creating Users and Databases

With PostgreSQL this 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>

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

Reload the PostgreSQL server to pick up the new privileges.

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