backup-postgres.sh.j2

#!/bin/bash
#
# Script to backup all postgres databases individually
#
# Requires local login with `postgres` user and either trusted or peer auth.

set -e

DBLIST=($(sudo -u postgres psql -d postgres -qt -c 'SELECT datname from pg_database'))
for db in "${DBLIST[@]}"; do
        if [[ $db =~ template[01] ]]; then
                continue;
        fi
        echo "Dumping $db to {{ backup_postgres_dir }}";
        sudo -u postgres pg_dump --serializable-deferrable -Fc "$db" > "{{ backup_postgres_dir }}/$db.dump"
done

echo "Dumping globals to {{ backup_postgres_dir }}"
sudo -u postgres pg_dumpall --globals-only > "{{ backup_postgres_dir }}/globals.sql.dump"