Audience: IT Support Staff

When used: The database is backed up once a day using pg_dump. This process also runs a vacuum to reclaim white space and updates table statistics. The purge process that removes results records also runs once a day before the backup.

Database Backup Processes: The database back up process is as follows.

1. Database exports are taken at 8:30AM GMT on dbcoma01bprd and at 2:30AM GMT on dbcoma01bprd.

The backup files are located in /var/lib/pgsql/current/backups and prod1 will also rsync the latest copies nightly to /backups/pgsql-prod on its local filesystem. These backups are created with the script and are formatted custom so that they can be restored using pg_restore.

The command to restore one of these exports would be:

$ pg_restore -Fc -c -d gnw -n dvpointp /var/lib/pgsql/current/backups/gnw_prodmigration.pgdump 

The process is run by system cron file /etc/crontab

2. Results older than 21 days (configurable) are purged from the database nightly, just before the backup on dbcoma01aprd. This process will remove 50000 (configurable) at a time to reduce the affect on replication. This is done by the script and its parameters are configured via command line switches.

When the process is configured to delete 50000 rows at a time, it will copy 50000 targeted rows to a temp table in the public schema then run a stored procedure to delete the rows in the temp table and source table. This targeted deletion reduces locking on the production table.

These processes are critical to the maintenance of the GNW database.

Database Backups and Purging (last edited 2011-08-11 20:13:31 by Bryce Camp)