Check date of a pg_dump

Question

One a couple of servers we use pg_dumpall to create backups of our databases. I'm just wondering if there is an easy way to check if the file created is okay, and to check the date that it was created (from the actual file rather than checking it's creating date on the fs).

Would save a lot of effort when testing/verifying our backups.

Answer 1

If the pg_dumpall command exit status is zero, the created file is OK.

There is no way to know exact timestamp of backup creation when you use pg_dumpall, because it creates output as a plain text file.

But dumps made with custom format (pg_dump -F c) have some extra metadata.

So here's a basic script I usually recommend to make backup of PostgreSQL cluster:

#!/bin/sh
pg_dumpall -g > pg-globals.sql # for global stuff like usernames/passwords
for db in db1 db2 db3; do
  pg_dump -F c $db > pg-$db.backup # separate file for every database in the cluster
done

This way you have separate file for each database. Each of them can be inspected with pg_restore to see actual date and time of creation as well as other info:

filip@srv:~$ pg_restore -l pg-inspire.backup |head -n 15
;
; Archive created at Thu Dec  1 07:28:27 2011
;     dbname: inspire
;     TOC Entries: 714
;     Compression: -1
;     Dump Version: 1.12-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 9.0.5
;     Dumped by pg_dump version: 9.0.5
;
;
; Selected TOC Entries:
;

I highly recommend reading these:

• http://www.postgresql.org/docs/9.0/static/app-pgdump.html
• http://www.postgresql.org/docs/9.0/static/app-pg-dumpall.html

Answer 2

In addition to what @filiprem wrote-- one way to be able to check when a pg_dumpall file was created is to embed the date/time in the filename.