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) |
\l | List all databases |
\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 -qt -c \"\\timing false\" \ -c \"SELECT pid 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';