Re: How to Monitor backups

Payal

Thanks for this:
schedule email to DBA if there is any error in the backup creation script itself (mail if there is anything in the stderr)

will the 2>&1 part of the crontab direct the stderr into the /cron/db_backup.out file ?

00 19 * * * /backup_scripts/db_backup.sh > /cron/db_backup.out 2>&1

If so I could check by cron if this file > 0 bytes and if so mail to DBA?

Austen

Austen Birchall Senior Database Administrator
Met Office FitzRoy Road Exeter EX1 3PB United Kingdom
Tel: +44 (0)1392 884481 Fax: +44 (0)1392 885681
E-mail: austen(dot)birchall(at)metoffice(dot)gov(dot)uk Website: http://www.metoffice.gov.uk

From: Payal Singh [mailto:payal(at)omniti(dot)com]
Sent: 14 October 2013 16:19
To: Birchall, Austen
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] How to Monitor backups

Hey,

Any automated monitoring job is based on the assumption that the tool automating it will work fine. With this assumption in mind, option 1 seems like the simplest and most efficient way.

Of course, yet another precaution you can take is to schedule email to DBA if there is any error in the backup creation script itself (mail if there is anything in the stderr), which will also take into account any error generated by pg_dump/dumpall commands.

Payal Singh,
OmniTi Computer Consulting Inc.
Junior Database Architect,
Phone: 240.646.0770 x 253

On Mon, Oct 14, 2013 at 11:08 AM, Birchall, Austen <austen(dot)birchall(at)metoffice(dot)gov(dot)uk<mailto:austen(dot)birchall(at)metoffice(dot)gov(dot)uk>> wrote:
Hi again

9.2.4 on RHEL 6

I have a backup that runs by cronjob every day which is basically this:

00 19 * * * /backup_scripts/locsng_backup.sh > /cron/locsng_backup.out 2>&1

View /backup_scripts/locsng_backup.sh:

# carry out schema only backup of the db database
/pg_dumpall -l db -U postgres -s > /backups/pg_db_schema_backups/db_ci_schema_dump$date_of_backup.sql 2>&1

# carry out backup of db database
/pg_dump -Fc db -U postgres > /backups/pg_db_db_backups/db_ci_backup$date_of_backup

# carry out cluster wide metadata backup
/pg_dumpall -g > /backups/pg_cluster_dump/cluster_ci_medadata$date_of_backup.sql

Is there a best practise way of how to monitor this sort of backup?

- my initial ideas are:

1. Check by cron if /cron/locsng_backup.out > 24 hours old (of course this will not work if cron fails) and if so notify the DBA by email?

2. As part of the script check if cron/locsng_backup.out > 0 bytes in size (of course this will not work if cron fails) and if so notify the DBA by email?

3. Does pg_dump, pg_dumpall return an error code if it is unsuccessful and can be trapped in same way and then used to notify the DBA by email?

Thanks in advance

Austen

Austen Birchall Senior Database Administrator
Met Office

--
Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org<mailto:pgsql-novice(at)postgresql(dot)org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice