How to check if postgresql backup was successful?

postgresql backup restore pg-dump
Dragan Matic picture Dragan Matic · Jun 14, 2011 · Viewed 18.6k times · Source

We have a postgresql database that is backed up nightly from a cron job with a following command:

su postgres -c "pg_dump our_database | gzip > /home/smb/shared/database_backup.bak.gz"

recently we had a disk failure that started with a few bad sectors and during that time pg_dump exited with the following errors

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: catalog is missing 17 attribute(s) from relid 20158
pd_dump: The command was: LOCK TABLE public.obvez IN ACCESS SHARE MODE

Now, since it was in cron job, nobody noticed error messages, the backup was interrupted but it wasn't zero sized, everything seemed ok and the error went unnoticed until final disk failure when we realized we didn't have backup.

We managed to restore data from an older backup but now I would like to know what would be the proper way to check if pg_dump finished its job with success or not?

Answer

Berry Langerak picture Berry Langerak · Jun 14, 2011

I write the outcome into a logfile, and at the end of the cronjob, I send the content of the logfile to my e-mail address. That way, I'll know when something went wrong.

su postgres "pg_dump our_database 2>> $LOG_FILE | gzip > /home/smb/shared/database_backup.bak.gz"
cat $LOG_FILE | mailx $MAINTAINERS -s "Postgresql backup"

ADDENDUM: if you want to send the e-mail only if anything went wrong, you can check the return code of pg_dump:

LOG_FILE=/tmp/pgdump.err

if ! pg_dump -U backupuser "our_database" 2> $LOG_FILE 
then 
    cat $LOG_FILE | mailx 'youremailaddress' -s "Postgresql backup failure!"
fi

Related questions

Restoring the data from pg_dump doesn't overwrite the data but it appends the data to the original database

I am taking the dump of postgres database using "pg_dump database_name > backup.sql". Later on I am doing some modifications in the original database(database_name) and then I am restoring the data from the backup file(…

Read More
Restore a postgres backup file using the command line?

I'm new to postgresql, and locally, I use pgadmin3. On the remote server, however, I have no such luxury. I've already created the backup of the database and copied it over, but, is there a way to restore a backup …

Read More
PostgreSQL: improving pg_dump, pg_restore performance

When I began, I used pg_dump with the default plain format. I was unenlightened. Research revealed to me time and file size improvements with pg_dump -Fc | gzip -9 -c > dumpfile.gz. I was enlightened. When it came …

Read More