Date: Thu, 25 Nov 2021 11:09:03 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Daulat <daulat.dba@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: pg_dump backup verification
Message-ID: <20211125170903.GD17618@telsasoft.com>
References: 
 <CALfOM6LXntkeeyYgDwRgu-=E7n83Nu5KVjMMX0h0cx8y8xLZ-w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CALfOM6LXntkeeyYgDwRgu-=E7n83Nu5KVjMMX0h0cx8y8xLZ-w@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20211125170903.GD17618%40telsasoft.com>
Precedence: bulk

On Thu, Nov 25, 2021 at 02:41:34PM +0530, Daulat wrote:
> Please suggest how I can ensure pg_dump backup has completed successfully ?
> I don't think there is any view like Oracle which helps with
> dba_datampump_jobs etc.

1) Check its exit status.  If it's nonzero, then surely there's a problem
(typically detailed indicated by output to stderr).

2) You can also run pg_restore -l to output a TOC for the backup.  From
experience, this can be a good secondary test.  You could add to your backup
script "pg_restore -l ./thebackup >/dev/null" to check that pg_restore itself
exits with a zero exit status.

3) If your backup job is a shell script, you should use "set -e", to be sure
that a command which fails causes the script to exit rather than plowing ahead
as if it had succeeded.  This is important for any shell script that's more
than 1 line long.

4) It's usually a good idea to write first to a "*.new" file, and then rename
it only if the pg_dump succeeds.  Avoid "clobbering" a pre-existing file (or
else you have no backup at all until the backup finishes, successfully).  Avoid
piping pg_dump to another command, since pipes only preserve the exit status of
the final command in the pipeline.

For example:

#! /bin/sh
set -e
f=/srv/otherfs/thebackup
rm -f "$f.new" # Remove a previous, failed backup, if any
pg_dump -Fc -d ourdatabase >"$f.new"
pg_restore -l "$f.new" >/dev/null
mv "$f.new" "$f"
exit 0 # In case the previous line is a conditional like "if" or "&&" or "||".

5) You can monitor the age of ./thebackup.

6) Log the output of the script; do not let its output get redirected to
/var/mail/postgres, or somewhere else nobody looks at.

7) It's nice to dump to a separate filesystem; not only because FS corruption
would affect both the live DB but also its backup.  But also because the
backups could overflow the FS, causing the main DB to fail queries or crash.

8) Keep a few backups rotated weekly and a few rotated monthly.  Even if it's
never needed to restore a 2 month old backup, it can be valuable to help
diagnose issues to see when some data changed.

9) Also save output from pg_dumpall -g, or else your backup will probably spew
out lots of errors, which are themselves important, but might also obscure
other, even more important problems.

10) Perhaps most importantly, test your backups.  Having backups is of little
use if you don't know how to restore them.  This should be a periodic
procedure, not something you do once to be able to say that you did.
Are you confident you can run using the restored DB ?  

-- 
Justin