🚧 Under construction — VoTechWorks is being built. A free tech program — complimentary, as our commitment to get America working.
voTechWorksGrade 13 · a vocational technology learning center, by urbanicity
newSchoolWorks.com › VoTechWorks › Technology & Design › Data Management / Database Administration › Bash Automation for Database Administration › Lesson 4
TEC-DATA86-09 · Lesson 4 · 12 clock hours · lab

Automating PostgreSQL Backup and Restore

Objective. Students will write and validate a production-quality Bash script that performs scheduled PostgreSQL logical backups using pg_dump, manages backup retention, and executes a test restore into a staging database.

⚠ Pending professional review

This lesson, its worked examples and its practice questions has not yet been verified by an industry-certified professional or by a certification or approval body. It is a study draft and may contain errors. Do not use it as instructions for work on a job site or on live equipment. Always follow your instructor, your supervising professional, and current official references and regulations.

VoTechWorks program approval and accreditation are still in progress. Credentials are issued only by the certifying bodies named in each program, after their own exams.

Why Automate PostgreSQL Backups?

Manual backups are error-prone and easy to forget. A production database must be backed up on a predictable schedule, with old backups rotated out automatically and every backup verified by a test restore. Bash scripting ties these steps together and lets you schedule the whole workflow with cron — a core skill tested on the EDB PostgreSQL CE exam and directly relevant to Oracle DBA CA and Microsoft DP-300 backup/recovery objectives.

pg_dump: Logical Backups Explained

`pg_dump` creates a logical backup of a single PostgreSQL database. A logical backup captures the SQL needed to recreate all objects and data; it is not a binary copy of the data directory. This makes it portable across PostgreSQL versions (with some restrictions) and easy to restore into a differently-named database.

Key flags you will use in this lab:

For this lab we use plain-text format piped through `gzip` so the restore path with `gunzip | psql` stays simple and visible.

Timestamped Filenames and Retention

Every backup file should include the date and time it was created. A reliable pattern is:

```bash

FILENAME="${PGDATABASE}_$(date +%Y%m%d_%H%M%S).sql.gz"

```

`date +%Y%m%d_%H%M%S` expands to something like `20240915_023001`, which sorts lexicographically in chronological order — useful when listing files.

Retention management removes backups older than a chosen number of days so disk space does not fill up. The POSIX `find` utility handles this cleanly:

```bash

find "${BACKUP_DIR}" -name "*.sql.gz" -mtime +"${RETENTION_DAYS}" -delete

```

`-mtime +N` matches files whose modification time is more than N days ago. Always test `find` commands without `-delete` first to confirm the file list is what you expect before deletions occur.

Authenticating Without Hardcoding Passwords

Hardcoding a password directly inside a script — for example `PGPASSWORD=secret` — is a serious security violation. Anyone who can read the file, view process environment variables (`/proc/<pid>/environ`), or access version control history can recover the credential. This violates DAMA CDMP data-governance principles around access control and least-privilege, and it typically violates organizational security policy.

The correct approach for non-interactive scripts is the `.pgpass` file. PostgreSQL reads `~/.pgpass` (or the path in `$PGPASSFILE`) when it needs a password. The file format is one record per line:

```

hostname:port:database:username:password

```

Wildcards (`*`) are allowed in any field. Critically, PostgreSQL ignores `.pgpass` unless its permissions are exactly `0600` (owner read/write only). Set this with:

```bash

chmod 0600 ~/.pgpass

```

In your script, export `PGPASSFILE` to point to the file, or rely on the default `~/.pgpass`. Never commit `.pgpass` to version control.

Writing the Backup Script

A production-quality script should:

1. Read all site-specific values from environment variables (`PGHOST`, `PGPORT`, `PGDATABASE`, `PGPASSFILE`, `BACKUP_DIR`, `RETENTION_DAYS`) — no hardcoded values.

2. Validate that required variables are set and exit with a non-zero code if any are missing.

3. Create the backup directory if it does not exist.

4. Run `pg_dump` and pipe output through `gzip`, redirecting to the timestamped file.

5. Check the exit code of each command and log success or failure.

6. Run the `find` retention cleanup after a successful backup.

Always add `set -euo pipefail` at the top of your script. `-e` exits on error, `-u` treats unset variables as errors, and `-o pipefail` catches failures in any stage of a pipeline — essential when `pg_dump | gzip` is your backup command.

Executing a Test Restore

A backup you have never tested is not a backup. The restore procedure for a plain-text gzip dump is:

```bash

gunzip -c "${BACKUP_FILE}" | psql -h "${PGHOST}" -p "${PGPORT}" \

-U "${PGUSER}" -d restore_test

```

`-c` tells `gunzip` to write to stdout rather than a file, keeping the pipeline intact. The target database `restore_test` must already exist (create it with `createdb restore_test` beforehand).

After the restore, run a row-count query on a known table to verify data integrity:

```bash

psql -d restore_test -c "SELECT COUNT(*) FROM orders;"

```

Compare the result to the same query run against the source database. Document both counts, whether they match, and the timestamp in your lab journal.

Safety Considerations

Database administration carries real risk of data loss. Apply these habits:

Key terms8

pg_dumpA PostgreSQL utility that produces a logical backup of a single database as SQL statements or a custom binary format, suitable for restore with psql or pg_restore.
logical backupA backup that captures database objects and data as SQL statements rather than raw data-file blocks, making it portable and human-readable.
.pgpassA plain-text file used by PostgreSQL client tools to supply passwords non-interactively; must have file permissions 0600 or PostgreSQL will ignore it.
PGPASSFILEAn environment variable that overrides the default ~/.pgpass path, pointing PostgreSQL client tools to an alternate password file.
retention policyA rule specifying how long backup files are kept before deletion; enforced in Bash using the find utility with the -mtime flag.
set -euo pipefailA Bash directive that makes scripts exit immediately on any error (-e), treat unset variables as errors (-u), and propagate failures through pipelines (-o pipefail).
test restoreThe practice of restoring a backup into a separate staging database and verifying data integrity, confirming the backup is actually usable.
data governanceAs defined by DAMA International, the exercise of authority and control over the management of data assets, including policies on access, security, and credential handling.
In class: Students build a backup script that accepts environment variables for PGHOST, PGPORT, PGDATABASE, and PGPASSFILE; uses pg_dump with a timestamped filename; compresses output with gzip; and deletes files older than a configurable RETENTION_DAYS value using find -mtime. · Students run a simulated restore by piping the compressed dump through gunzip into psql against a staging database named restore_test, then run a row-count query to verify data integrity, documenting pass/fail in the lab journal—directly addressing EDB PostgreSQL CE backup/recovery exam objectives. · Students configure .pgpass with correct 0600 permissions and confirm that running the script produces no password prompt, then write a paragraph explaining why plaintext passwords in scripts violate both security policy and DAMA CDMP data-governance principles.
Materials: Linux VM, PostgreSQL 15, pg_dump, psql, gzip, .pgpass file, lab journal, EDB PostgreSQL CE exam guide
Check: Script must complete a full backup-and-restore cycle with zero errors, the restored row count must match the source, .pgpass must be 0600 permissions, and the lab journal entry must be complete; all three conditions required to pass.

Practice check8

Answer each question, then check it. Nothing is recorded; this is your own practice.

EDB PostgreSQL CE — Backup and Recovery
1. Which pg_dump output format allows a restore using `gunzip -c dump.sql.gz | psql`?
EDB PostgreSQL CE — Security and Authentication
2. A DBA sets file permissions on ~/.pgpass to 0644. What will PostgreSQL client tools do when they encounter this file?
EDB PostgreSQL CE — Backup and Recovery
3. In a Bash backup script, which shebang line and option string best prevents silent failures in a pipeline such as `pg_dump | gzip > file`?
EDB PostgreSQL CE — Backup and Recovery
4. A backup script uses the find command: `find /backups -name '*.sql.gz' -mtime +7 -delete`. What does this command do?
EDB PostgreSQL CE — Backup and Recovery
5. After restoring a backup to the staging database restore_test, what is the most direct way to verify data integrity?
DAMA CDMP — Data Security and Governance
6. Why does storing a plaintext password inside a Bash script violate DAMA CDMP data-governance principles?
EDB PostgreSQL CE — Backup and Recovery
7. A DBA needs pg_dump to fail with an error rather than hang waiting for keyboard input if the password is not available. Which flag achieves this?
EDB PostgreSQL CE — Backup and Recovery
8. A backup script accepts the database name through the environment variable PGDATABASE and builds a filename with `$(date +%Y%m%d_%H%M%S)`. Which filename format would this produce for a database named sales backed up on September 15, 2024 at 02:30:01?
Record this lesson on my transcript. Sign in with your key and enrollment email to record lessons (enroll free if you have no key). This page stays open to read and practise.
← Previous lessonProgram planNext lesson →