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
