A while ago I published an article on PostgreSQL user administration. Typically, things have changed since I wrote that article. I thought I'd detail a couple of the differences since I wrote that guide.

The major difference is that you now have roles rather than users and you use the CREATE ROLE command to create them instead of CREATE USER, although the latter command still works. The command line options for the createuser command have changed as a result too. Before superuser and the ability to create new users were the same thing. Now you can give a role permissions to create new roles without giving them superuser powers. The options are now -s for superuser and -S for not superuser, -d to allow them to create databases and -D to disallow database creation and -r to allow the new role to create other roles and -R to prevent them. for a standard user you probably want somethig like:

createuser -S -D -R -P user

The -P makes createuser ask you for a password for the new role.

You can find out more information about the new role system in PostgreSQL in the user management and CREATE ROLE reference sections of the manual.

I decided it was probably time that I started backing up my PostgreSQL databases and ended up writing a quick script to do just that. I did have a look at pg_dumpall, but it appears to only write to stdout, meaning I couldn't have a file per database like I wanted.

#!/bin/bash

set -u
set -e

dumpdir=/var/backups/postgresql 

date=$(date +"%Y-%m-%d")
mkdir -p $dumpdir
find $dumpdir -name "*.sql" -mtime +10 -exec rm {} \; 
for database in $(su - postgres -c "psql -l -t" | cut -d'|' -f 1 | grep -v template0); 
do 
        su - postgres -c "pg_dump $database" > $dumpdir/$database-$date.sql; 
done

su - postgres -c "pg_dumpall -g" > $dumpdir/global-$date.sql

Just stick that in /etc/cron.daily/postgresql-backups and you should end up with 10 days worth of backups. I'm not sure if postgreSQL can have spaces in database names. I'm assuming it can't. The only other issue to deal with is not storing it on the same machine as the database server, let alone on the same physical hardware or even worse, the same partition.

Update: Corrected typo and added dumping global objects to the script.

Sorry to anyone that got spammed by my change to pyblosxom earlier today. I changed the base url, which changed the link of the entries. I should sort out having proper guids.

Just a quick one. If you've ever created a table using the number of seconds since 1970 and realised, after populating it with data, that you really need it in a TIMESTAMP type? If so, you can quickly convert it using this SQL:

ALTER TABLE entries 
   ALTER COLUMN created TYPE TIMESTAMP WITH TIME ZONE
      USING TIMESTAMP WITH TIME ZONE 'epoch' + created *interval '1 second';

With thanks to the PostgreSQL manual for saving me hours working out how to do this.