Re: how to detect the backup database every day

Hello all

I use a crontab job to backup and restore my database every midnight every
day (using pg_dump and restore...)

My cron job run well for a while. However, today I discovered that my
database was not restored for one month.

I also fix the problem as there is corruption in the script.

My question is how I can detect whether the backup or restore processes is
corrupted. I donot want that my backup database is one month old -:(

Is there a solution around this.

If you have any experience about this please help...

Regards

son(at)raider(dot)co(dot)nz wrote:
> Hello all
>
> I use a crontab job to backup and restore my database every midnight every
> day (using pg_dump and restore...)
>
> My cron job run well for a while. However, today I discovered that my
> database was not restored for one month.
>
> I also fix the problem as there is corruption in the script.
>
> My question is how I can detect whether the backup or restore processes is
> corrupted. I donot want that my backup database is one month old -:(

Well, if the script failed with an error, cron should have sent you an
email (or the user the script runs as, anyway).

If you didn't have an error, you could run a separate cron that checks
whether there are recent backup files and emails you if not (find ...
-mtime -1).

Or, if you want to make sure the restore has worked check the database
for a row with a recent timestamp.

--
Richard Huxton
Archonet Ltd

On 8/7/07, son(at)raider(dot)co(dot)nz <son(at)raider(dot)co(dot)nz> wrote:
> Hello all
>
> I use a crontab job to backup and restore my database every midnight every
> day (using pg_dump and restore...)

Backup from one machine, restore to another, right?

> My cron job run well for a while. However, today I discovered that my
> database was not restored for one month.

> I also fix the problem as there is corruption in the script.
>
> My question is how I can detect whether the backup or restore processes is
> corrupted. I donot want that my backup database is one month old -:(

You can detect whether backup failed from a bash script like so:

#!/bin/bash
if ( pg_dump dbname ); then
echo "good";
else
echo "bad";
fi;

Same thing for pg_restore or psql

> If you have any experience about this please help...

Some. There are a lot of angles you can approach this from. You can
have a simple cronjob that runs every day that checks the size / age
of the latest backup and sends an alarm if it's smaller than the last
one, or isn't there, etc... You can use the find command to look for
files that are less than x seconds / minutes / hours / days old.

Richard Huxton wrote:
> son(at)raider(dot)co(dot)nz wrote:
>
>> Hello all
>>
>> I use a crontab job to backup and restore my database every midnight
>> every
>> day (using pg_dump and restore...)
>>
>> My cron job run well for a while. However, today I discovered that my
>> database was not restored for one month.
>>
>> I also fix the problem as there is corruption in the script.
>>
>> My question is how I can detect whether the backup or restore
>> processes is
>> corrupted. I donot want that my backup database is one month old -:(
>
>
> Well, if the script failed with an error, cron should have sent you an
> email (or the user the script runs as, anyway).
>

To expand on that, have a look at your crontab and ensure that the call
to your script does not end with ">/dev/null 2>&1". That will cause the
script to run silently, regardess of any errors. If you want it to be
silent yet have errors emailed to you, change it to ">/dev/null"
(without the quotes).

If the emails have been sent but this is an account on a remote server
you might want to investigate having the emails for that remote account
sent to your regular email address.

brian