PostgreSQL 9.1 incremental backup to Amazon S3

Let's perform a little exercise, configure PostgreSQL 9.1 to incrementally back up to Amazon S3, and automatically test that backup.

We don't want to take any risks with our current development database, so let's create a few independent PostgreSQL servers, called "clusters", just for this test. We'll remove them afterwards.

$ sudo pg_createcluster 9.1 gold
Creating new cluster (configuration: /etc/postgresql/9.1/gold, data: /var/lib/postgresql/9.1/gold)...
Moving configuration file /var/lib/postgresql/9.1/gold/postgresql.conf to /etc/postgresql/9.1/gold...
Moving configuration file /var/lib/postgresql/9.1/gold/pg_hba.conf to /etc/postgresql/9.1/gold...
Moving configuration file /var/lib/postgresql/9.1/gold/pg_ident.conf to /etc/postgresql/9.1/gold...
Configuring postgresql.conf to use port 5433...

$ sudo pg_createcluster 9.1 silver
Creating new cluster (configuration: /etc/postgresql/9.1/silver, data: /var/lib/postgresql/9.1/silver)...
Moving configuration file /var/lib/postgresql/9.1/silver/postgresql.conf to /etc/postgresql/9.1/silver...
Moving configuration file /var/lib/postgresql/9.1/silver/pg_hba.conf to /etc/postgresql/9.1/silver...
Moving configuration file /var/lib/postgresql/9.1/silver/pg_ident.conf to /etc/postgresql/9.1/silver...
Configuring postgresql.conf to use port 5434...

$ sudo pg_createcluster 9.1 bronze
Creating new cluster (configuration: /etc/postgresql/9.1/bronze, data: /var/lib/postgresql/9.1/bronze)...
Moving configuration file /var/lib/postgresql/9.1/bronze/postgresql.conf to /etc/postgresql/9.1/bronze...
Moving configuration file /var/lib/postgresql/9.1/bronze/pg_hba.conf to /etc/postgresql/9.1/bronze...
Moving configuration file /var/lib/postgresql/9.1/bronze/pg_ident.conf to /etc/postgresql/9.1/bronze...
Configuring postgresql.conf to use port 5435...

$ sudo /etc/init.d/postgresql start 9.1
 * Starting PostgreSQL 9.1 database server             [ OK ] 
 
$ sudo /etc/init.d/postgresql status
Running clusters: 9.1/bronze 9.1/gold 9.1/main 9.1/silver 

As you can see, the original cluster is called main.

Edit the /etc/postgresql/9.1/{gold,silver,bronze}/pg_hba.conf file, and change the authentication method to trust.

--- a/pg_hba.conf	2011-12-16 21:54:34.062338553 +0200
+++ b/pg_hba.conf	2011-12-16 23:12:26.010781522 +0200
@@ -87,11 +87,11 @@
 # TYPE  DATABASE        USER            ADDRESS                 METHOD
 
 # "local" is for Unix domain socket connections only
-local   all             all                                     peer
+local   all             all                                     trust
 # IPv4 local connections:
-host    all             all             127.0.0.1/32            md5
+host    all             all             127.0.0.1/32            trust
 # IPv6 local connections:
-host    all             all             ::1/128                 md5
+host    all             all             ::1/128                 trust
 # Allow replication connections from localhost, by a user with the
 # replication privilege.
 #local   replication     postgres                                peer

Restart.

$ sudo /etc/init.d/postgresql restart 9.1
 * Restarting PostgreSQL 9.1 database server                

$ sudo -u postgres createuser -p 5433 -s $USER
$ sudo -u postgres createuser -p 5434 -s $USER
$ sudo -u postgres createuser -p 5435 -s $USER