Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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:

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.