Failed database dump results in valid snapshot

Getting Help
1

Hi,

to create database backups I use mysql/pgdump commands and restic in stdin mode, running in a bash script with set -e and set -o pipefail [1]. In some situations the databases dumps fail but restic still result in a valid snapshot [2].

Any ideas how to catch these type of errors?

[1]

#!/bin/bash
set -e
set -o pipefail
printf ‘\n\n%s: Create postgresql backup…\n’ “$(date)”

pg_dump --host=“${BACKUP_DB_HOST}” --username=“${BACKUP_DB_USER}” --dbname=“${BACKUP_DB_NAME}” |
restic backup
–stdin --host “${BACKUP_INVENTORY_NAME}”
–stdin-filename “${BACKUP_DB_NAME}.sql”
–tag “database,${BACKUP_DB_NAME}”
echo “Done. Exit code $?.”

[2]

Wed Mar 1 02:30:18 UTC 2023: Create postgresql backup…
pg_dump: error: query failed: FATAL: terminating connection due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_dump: detail: Query was: COPY public.app_accounts (uid, data) TO stdout;

Files: 1 new, 0 changed, 0 unmodified
Dirs: 0 new, 0 changed, 0 unmodified
Added to the repository: 168.327 KiB (15.332 KiB stored)

processed 1 files, 168.015 KiB in 0:36
snapshot e4ad51ae saved

Cheerio
meise

1 Like
2

Some ideas on how to handle erros and pipes: shell - How to interrupt bash pipeline on error? - Stack Overflow

3

Hmm I am not sure if the receiving app can even differentiate if the stdin is broken or just finished.

I initially thought that might send a SIGPIPE or similar, at least on linux, so I made a test python script to accept data from stdin and print whatever signal reaches to the script itself when I kill the sending process (dd), nothing reached :disappointed_relieved:

But maybe devs have a magical way in the go-land, I am potato on programming.

4

When data is read from stdin, then there’s no way for restic to know why the input data stream ended. It just ends. The exit status isn’t visible to restic.

To be able to check that, restic would have to call pg_dump itself. That is, we’d need something similar to what borgbackup implements: borg create — Borg - Deduplicating Archiver 1.2.3 documentation . Does anyone want to open an issue on Github?

5

Thank you all for the posts and comments. I created a feature request on Github.

In the meantime I changed my backup script to handle return status with the bash special variable PIPESTATUS and consider backups without tags as incomplete:

printf '\n\n%s: Create postgresql backup...\n' "$(date)"

# disable exit on error for database dumps because we have to handle pipe errors manually
set +e

# use ~/.pgpass to get db password
pg_dump --host="${BACKUP_DB_HOST}" --username="${BACKUP_DB_USER}" --dbname="${BACKUP_DB_NAME}" | \
        restic backup \
             --stdin --host "${BACKUP_INVENTORY_NAME}" \
             --stdin-filename "${BACKUP_DB_NAME}.sql"

# copy PIPESTATUS results for later use
pipestatus=(${PIPESTATUS[@]})

# check return code of every wired command
if [ "${pipestatus[0]}" -ne "0" ] || [ "${pipestatus[1]}" -ne "0" ]; then
  printf '\n\n%s: Postgresql backup failed: pg_dump exit status %s, restic exit status %s\n' "$(date)" "${pipestatus[0]}" "${pipestatus[1]}"
  exit 2
else
  # enable exit on error again
  set -e

  printf '\n\n%s: Add tags to complete database dump snapshot…\n' "$(date)"
  restic tag \
      --set "database,${BACKUP_DB_NAME}" \
      --tag "" \
      --path "/${BACKUP_DB_NAME}.sql" \
      latest
fi
echo "Done. Exit code $?."
2 Likes
6

set -o pipefail might also help here. That option let’s commands that include pipes return a non-zero exit code if any part of the pipeline does so.