Backing Up a Remote PostgreSQL Database with pg_dump

calendar Mar 17, 2024 / · 9 min read · pg_dump postgresql database backup command-line automation  ·
Share on: linkedin copy

Backing Up a Remote PostgreSQL Database with pg_dump

The database lives on a managed host three networks away, the only backup is whatever the provider snapshots, and nobody has ever tried to restore from it. A logical dump taken from a laptop or a jump box fixes that in one command, because pg_dump is an ordinary client program: it needs a network path to the server and a role with read access, and nothing is installed on the database host. The plain-text form is a SQL script; the archive forms are smaller, selective on restore, and can be restored in parallel.

Purpose and Overview

pg_dump exports one database at a time and writes a consistent snapshot: the dump reflects the database as it was when pg_dump began, and it does not block readers or writers while it runs. The exceptions are operations that need an exclusive lock, such as most forms of ALTER TABLE. Because it connects like any other client, it can run from any host that can reach the server and pass client authentication.

A dump is a logical backup. It holds the statements or archive entries needed to rebuild the objects and data, not copies of the data files. That gives it two properties file-level backups lack: its output can generally be loaded into newer PostgreSQL versions, and it works when moving a database to a different machine architecture. The cost is time. A logical dump of a very large database takes longer to produce and to restore than a physical copy. Outside simple cases, pg_dump is generally not the right choice for regular backups of production databases. It is the right tool for migrations, upgrades, per-schema or per-table copies, and a portable second copy.

The examples below start with the minimal remote command, then add the options a scheduled job needs: a non-interactive password, an encrypted connection, an archive format, and a restore test.

Sample Code

Minimal remote dump to a plain SQL script:

1pg_dump -U username -h hostname databasename > dump.sql

Archive format with an encrypted connection and no password prompt:

1PGSSLMODE=require pg_dump -h db.example.com -p 5432 -U backup_user -w \
2    -Fc -f appdb.dump appdb

Directory format dumped with four parallel jobs:

1pg_dump -h db.example.com -U backup_user -w -Fd -j 4 -f appdb.dir appdb

One schema, excluding a table pattern:

1pg_dump -h db.example.com -U backup_user -w -Fc -n sales -T 'sales.audit_*' \
2    -f sales.dump appdb

Restore the plain script or the archive into a new database:

1psql -X -d newdb -f dump.sql
2pg_restore -d newdb appdb.dump

Notes: dump.sql from the first command is a script for psql; it is not an archive and pg_restore cannot read it. The -w flag stops password prompts, so the password must come from a .pgpass file or another source. Replace the host, role and database names with real values. The target database for a restore must already exist, as shown in the Practical Applications section.

Code Breakdown

Connection options

-h sets the host name of the server, or a Unix-socket directory if the value begins with a slash. -p sets the port, -U the role name, and the final argument the database. When omitted, the defaults come from PGHOST, PGPORT, PGUSER and PGDATABASE, and without a database name the role name is used. pg_dump also honors every other environment variable that libpq reads.

Output: redirect versus -f

> dump.sql is shell redirection of standard output, so the shell creates the file before pg_dump connects. A failed connection leaves an empty or partial dump.sql that looks like a backup. -f names the output file for pg_dump itself, and for the directory format it is required: it names a directory that pg_dump creates and that must not exist beforehand. Either way, check the exit status of the command before trusting the file.

Format with -F

-Fp is plain text and the default. -Fc writes a custom-format archive and -Fd a directory-format archive. -Ft writes a tar archive. All archive formats are restored with pg_restore. The custom and directory formats are compressed by default; tar does not support compression at all.

Compression with -Z

-Z selects the method (gzip, lz4, zstd or none) and level. For custom and directory archives it compresses each table-data segment, and the default is gzip at a moderate level. For plain text the default is no compression, but a nonzero level compresses the whole output file as if it were piped through a compressor.

Selection with -n, -N, -t and -T

-n dumps matching schemas with all their contents and -N excludes schemas. -t dumps matching tables and -T excludes them. Patterns follow the psql \d rules, so wildcards must be quoted against the shell, and mixed-case names need double quotes inside the shell quotes. When -n is used, pg_dump makes no attempt to dump other objects the schema depends on, so a schema dump is not guaranteed to restore by itself into an empty database. -s dumps definitions only, -a data only, and --exclude-table-data keeps a table's definition while skipping its rows.

Key Connection and Credential Settings

The .pgpass file

A .pgpass file in the home directory supplies passwords when a connection needs one and none was given another way. Each line has the format hostname:port:database:username:password. Each of the first four fields can be a literal or *, and the first matching line wins, so specific entries go first. On Unix the file is ignored unless group and world access are disallowed, for example with chmod 0600 ~/.pgpass. On Windows the file is %APPDATA%\postgresql\pgpass.conf. PGPASSFILE or the passfile parameter points to a different location.

Why not PGPASSWORD

PGPASSWORD works, but its use is not recommended because some operating systems let other users see a process's environment variables through ps. A password file avoids that. With -w, pg_dump never prompts and fails if no password is available by other means such as .pgpass, which is the correct behavior in a batch job where nobody can answer a prompt.

sslmode

The default sslmode is prefer, which tries an SSL connection first and silently falls back to a non-SSL one. For a dump that crosses a network, require allows only an SSL connection, but it does not verify the server's identity unless a root CA file is present. verify-ca and verify-full add certificate checks, and verify-full also checks that the host name matches. Set it through PGSSLMODE or in a connection string passed as the database argument.

The dump role

pg_dump has no special privileges: the role must have read access to everything it exports, so a complete dump usually needs a superuser. A less privileged role can still dump the parts it can read with -n or -t. --role=rolename runs SET ROLE after connecting, for sites that forbid direct superuser logins.

Practical Applications

Scheduled nightly dump

A job that runs from cron on Linux or macOS, or from Task Scheduler on Windows, should be a script rather than a one-line crontab entry, so the file name carries a timestamp and a failure is visible:

1#!/bin/sh
2set -eu
3STAMP=$(date +%Y%m%d-%H%M%S)
4PGSSLMODE=require pg_dump -h db.example.com -U backup_user -w -Fc \
5    -f "/backups/appdb-$STAMP.dump" appdb

set -e stops the script on the first failing command, so a failed dump exits non-zero for the scheduler to report.

Restoring and testing the restore

A plain script is loaded with psql -X -d newdb -f dump.sql, and an archive with pg_restore -d newdb appdb.dump. The destination database is not created by either command; create it from template0 so local additions in template1 do not collide with objects in the dump:

1CREATE DATABASE newdb WITH TEMPLATE template0;

Schedule a periodic restore into a scratch database and run a few row counts against it.

Selective and parallel restore

pg_restore -l appdb.dump > appdb.list writes the archive's table of contents. Edit the list, with a semicolon commenting out a line, and pass it back with -L appdb.list to restore only chosen items in the order given. -j restores in parallel for custom and directory archives, and -1 wraps the restore in a single transaction so a failure leaves no partial changes; the two cannot be combined. -e stops at the first error instead of continuing.

Migrating or refreshing a copy

For a database small enough to move in a maintenance window, a dump and restore is a straightforward way to build a new server, a staging copy, or a copy on a newer major version. pg_restore -d postgres --clean --create appdb.dump drops and recreates the database from the archive; use it only on a target whose contents you intend to overwrite.

Version Compatibility

The output of pg_dump can be expected to load into server versions newer than the pg_dump that wrote it. pg_dump can also dump from servers older than itself (the current release supports servers back to 9.2), but it cannot dump from a server with a newer major version, so run a pg_dump at least as new as the remote server. When the dump crosses major versions, --quote-all-identifiers is recommended to avoid reserved-word differences.

Parallel dumps with -j require the directory format and a server that supports synchronized snapshots: PostgreSQL 9.2 for primary servers and 10 for standbys. Parallel dumps also use extra connections, one for the leader and one per worker, and a conflicting exclusive lock taken by another client during the dump can make pg_dump abort. Options such as --exclude-table-and-children, --filter and the -Z method syntax appear in the current manual; check pg_dump --help on the exact version you run before putting them in a script.

pg_dump covers a single database. Roles and tablespaces belong to the whole cluster, so they are not in the dump. pg_dumpall --globals-only writes them, and a full cluster backup combines that with a pg_dump per database.

Best Practices

  • Prefer -Fc or -Fd over plain text — archives are compressed by default and let pg_restore pick, reorder and parallelize items.
  • Use .pgpass and -w for unattended runs — a password file with 0600 permissions keeps credentials out of scripts and process listings.
  • Require TLS — set PGSSLMODE=require at minimum, and use verify-full when the CA certificate is available.
  • Dump globals separately — run pg_dumpall --globals-only so roles and tablespaces can be recreated before the database restore.
  • Test the restore — restore into a scratch database on a schedule and compare row counts.
  • Keep the dump role narrow — use a dedicated login with read access, or --role, rather than a shared superuser account.
  • Mind the lock window — avoid DDL during parallel dumps, since an exclusive lock request can force the dump to fail.

References

Posts in this series