Backup¶

Backup procedure¶

The PostgreSQL command pg_dump is used to extract the database into a script file every single hour.
In the master server heplnm072.pp.rl.ac.uk and in the slave configdb(a).micenet.rl.ac.uk there are scripts (attached below) running every hour, day, month and year from the user postgres

bash-3.2$ crontab -l

1 0 * * * /var/lib/pgsql/backups/backup_daily.sh
3 0 1 * * /var/lib/pgsql/backups/backup_monthly.sh
5 0 1 1 * /var/lib/pgsql/backups/backup_yearly.sh
7 * * * * /var/lib/pgsql/backups/backup.sh

(NB: order does matter) creating on configdb:/var/lib/pgsql/backups several snapshots with the following granularity

cdb_pg_dump-hour[00..23].backup.gz	created every new hour
cdb_pg_dump-day[1..31].backup.gz	created at midnight
cdb_pg_dump-month[1..12].backup.gz	created the first day of the month
cdb_pg_dump-year[..].backup.gz	created the first day of the year

more or less 10 Gb per year. All the files are copied from configdb:/var/lib/pgsql/backups into backup1:/backup/configdba every night; backup1 sends an email every week with the status of all the backups stored on it.

Restore from a backup¶

In case you need to recreate the CDB from a snapshot, you should find the dumps on

heplnm072:/var/lib/pgsql/backups

or

configdb:/var/lib/pgsql/backups

or

backup1:/backup/configdba/var/lib/pgsql/backups

and to recreate the CDB

su postgres
gunzip cdb_pg_dump-???.backup.gz
dropdb cdb
pg_restore -C -d cdb cdb_pg_dump-???.backup

Another backup¶

The script

[root@configdb cron.d]# cat /etc/cron.d/pg-archive
*/1 * * * * postgres /var/lib/pgsql/scripts/pg-archive-current-wal-cron.sh &> /tmp/pg-archive-cron.log

creates a file /var/lib/pgsql/data/pg_xlog/archive/current from the last chunk in /var/lib/pgsql/data/pg_xlog/archive/.

---