PostgreSQL Logical Backups with pg_dump and pg_dumpall

PostgreSQL Logical Backups with pg_dump and pg_dumpall

What Is a Logical Backup?

A logical backup captures the data and structure of a database as SQL statements or a portable archive, rather than copying raw data files from disk. PostgreSQL provides two command-line tools for this purpose: `pg_dump` exports a single database, and `pg_dumpall` exports every database in a cluster, including global objects such as roles and tablespaces. Logical backups are essential in your DBA toolkit because they are portable across different PostgreSQL versions (within supported upgrade paths), can be restored into any accessible cluster, and allow you to recover individual schemas, tables, or even single objects without restoring an entire server.

pg_dump Output Formats

`pg_dump` supports several output formats, selected with the `-F` flag:

• Plain SQL (`-Fp` or default): Produces a human-readable `.sql` file of `CREATE` and `INSERT` (or `COPY`) statements. You restore it with `psql`. It is the easiest to inspect but typically the largest file, and you cannot use `pg_restore` with it.
• Custom format (`-Fc`): A compressed, non-text archive unique to PostgreSQL. It supports parallel restore, selective object restore, and is generally the most space-efficient. This is the format most DBAs reach for first.
• Tar format (`-Ft`): A standard `.tar` archive. Like custom format, it works with `pg_restore`, but it does not support parallel restore.
• Directory format (`-Fd`): Creates a directory with one file per table and a table of contents. It supports parallel dump (`-j`) and parallel restore.

For this lab you will work with custom, tar, and plain SQL formats.

Running pg_dump

The basic syntax is:

```

pg_dump [connection_options] [format_options] -f output_file dbname

```

Examples:

```bash

pg_dump -U postgres -Fc -f dvdrental_custom.dump dvdrental

pg_dump -U postgres -Ft -f dvdrental_tar.tar dvdrental

pg_dump -U postgres -Fp -f dvdrental_plain.sql dvdrental

```

You can restrict the dump to a single schema with `-n schema_name` or a single table with `-t table_name`.

Running pg_dumpall

`pg_dumpall` always produces plain SQL and is the correct tool when you need to capture roles, passwords, and tablespace definitions that exist at the cluster level, outside any single database. Typical use:

```bash

pg_dumpall -U postgres -f cluster_full.sql

```

Because `pg_dumpall` output is plain SQL, you restore it with `psql -f cluster_full.sql`.

Restoring with pg_restore

`pg_restore` works only with custom, tar, or directory formats. Key flags:

• `--list` prints the table of contents (TOC) of the archive — every object that would be restored, one per line.
• `--use-list=toc_file` tells `pg_restore` to restore only the objects listed in a filtered TOC file you supply.
• `-d dbname` specifies the target database.
• `-j N` runs N parallel worker processes (custom and directory formats only).
• `-s` restores schema only; `-a` restores data only.

Selective restore workflow:

```bash

pg_restore --list dvdrental_custom.dump > full_toc.txt

pg_restore -d dvdrental_new --use-list=filtered_toc.txt dvdrental_custom.dump

```

Restoring Plain SQL

For plain SQL dumps, use `psql`:

```bash

psql -U postgres -d dvdrental_new -f dvdrental_plain.sql

```

You cannot do selective object restoration directly from a plain SQL file without manually editing it — one reason custom format is preferred for production.

Verifying the Restore

After any restore, always verify that row counts match the source:

```sql

SELECT COUNT(*) FROM public.rental; -- run on source, then on restored DB

```

Mismatches indicate an incomplete restore. Check PostgreSQL logs and `pg_restore` output for errors.

Safety and Good Practice

Logical backups run as the PostgreSQL user, not as root. Never run `pg_dump` with elevated OS privileges unless your site policy requires it and the risks are understood. Store backup files on a separate volume or host — a backup on the same disk as the data it protects offers little protection against disk failure. Test restores regularly; an untested backup is not a backup. When working at the command line on shared lab workstations, confirm you are connected to the correct database and cluster before issuing destructive commands such as `DROP TABLE`. Verify your `$PGDATA` and connection string before any restore to avoid overwriting a production cluster with test data.

Connection to Your Credentials

Logical backup and recovery with `pg_dump` and `pg_restore` is a core topic in the PostgreSQL CE (EDB) exam. Understanding backup strategy — including format selection, granularity of recovery, and verification — also maps to DAMA CDMP Associate data protection competencies and is assumed knowledge for Oracle DBA CA and Microsoft DP-300 candidates who work in mixed-vendor environments.