Postgresql

Postgres is a popular open source database.

Postgresql backups

The following file will backup a given database to /var/log/.  It will create a different file for each day of the week - the %u option in the date command.  After that it will overwrite files.

cat /etc/cron.d/backupfsn.sh
#!/bin/bash
backup_dir="/var/log/"
timeslot=`date +%u`
i=dbnametobackup
/usr/bin/pg_dump -U postgres $i | gzip > "$backup_dir/postgresql-$i-$timeslot-database.gz"

Getting a list of databases

psql  -U postgres -q -c "\l" dbname | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}

Database is big and full dump takes a lot of time.

There are cases when the db is huge enough that dumping a full 2GB ore more takes time and may not be wise to do it hourly. The solution is incremental backup using WAL and a full base backup (a live filesystem backup of data directory) or Continuous Archiving and Point-In-Time Recovery a detailed explanation and steps are found here http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html however for the impatient you may begin asap.

everything in this command is assumed that you're in postgres account if not then execute su - postgres

step 1. edit postgresql.conf and enable the archiving parameters

vi /var/lib/pgsql/data/postgresql.conf

locate these lines and update it

archive_mode = on               # allows archiving to be done
                                # (change requires restart)
archive_command = 'cp %p /var/lib/pgsql/archive/%f'         # command to use to archive a logfile segment
archive_timeout = 0             # force a logfile segment switch after this
                                # time; 0 is off

then create archive directory and restart postgresql

mkdir /var/lib/pgsql/archive
pg_ctl restart

create base backup - this is a live filesystem backup of data but don't worry it won't corrupt the data cluster as long as pg_start_backup is used, it may take a while to finish this command because of data flush and etc.. just wait a few mins then quit psql and compress the data directory.

bash$ psql
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# select pg_start_backup('my base backup');
postgres=#\q

bash$ tar cjvf postgresql-data.tar.bz2 /var/lib/pgsql/data

bash$ psql
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# select pg_stop_backup();
postgres=#\q

That's it!  you're all set and you can copy the base backup postgresql-data.tar.bz2 and /var/lib/pgsql/data/archive
just don't forget to regularly run the base backup let's say weekly and store both base backup and archive wal files to remote safe storage. Refer to postgresql manual on how to start the recovery process using base backup and wals, keyword is recovery.conf