Create a PostgreSQL backup with pg_dump

pg_dump is a PostgreSQL utility for making a consistent logical export of one database. The database can remain in use during the export. The original article describes using it from Windows CMD and saving a log.

Benefits and limits

• Users can remain connected while a consistent export is taken.
• Verbose output can be redirected to a log for review.
• An archive can be transferred to another machine for restoration.
• The custom format used below supports compression and selective restoration.
• One invocation exports one database, not every database or all cluster-wide objects.

A logical export is not, by itself, a complete production recovery strategy. See the official pg_dump documentation for formats, version compatibility and limitations.

Parameters to prepare

Find the installed pg_dump.exe. The original Windows example uses PostgreSQL 13 under C:\Program Files\PostgreSQL\13\bin, or C:\Program Files (x86)\PostgreSQL\13\bin for its 32-bit example. These are historical paths, not a requirement to install that version. Use your actual installation and check its compatibility with the server.

• --host=: server address; use localhost for the local server where appropriate.
• --port=: database server port.
• --username=: the database role used for the export.
• --dbname=: the database to export. A database name and a schema name are different.
• --schema=: optionally limit the export to a schema pattern. Check case-sensitive names and dependencies before using this filter.
• --verbose: include detailed progress information.
• --format=c: use a custom archive, restored with pg_restore.
• --file=: the output folder and archive name.
• > file.log 2>&1: redirect command output and diagnostics to a log.

Adapted Windows CMD example

The original scenario uses a 64-bit Windows server at 10.0.0.1, port 5432, role pgusuario, schema DB1 and destination B:\Respaldos. It does not name the source database.

The corrected template below explicitly requires DATABASE_NAME and SCHEMA_PATTERN. Replace both, the executable path and all connection details before use. Do not assume that the database has the same name as the schema. Select a new output filename so an existing backup is not overwritten, and use an account with the required permissions rather than assuming that an administrator CMD window grants database access.

"C:\Program Files\PostgreSQL\YOUR_VERSION\bin\pg_dump.exe" --host=10.0.0.1 --port=5432 --username=pgusuario --dbname=DATABASE_NAME --schema=SCHEMA_PATTERN --verbose --format=c --file="B:\Respaldos\Backup.bak" > "B:\Respaldos\Backup.log" 2>&1

An export limited to selected schemas may omit dependencies outside those schemas. If the intention is to export the whole database, omit the schema filter after reviewing the scope. Enter credentials only at the expected PostgreSQL prompt if requested; do not embed a password in a published command.

Review the result

Check the exit status and read the entire log, including messages marked WARNING, ERROR or FATAL. A log without obvious errors is not a substitute for a test restore. Use an isolated destination to confirm that the archive contains what you need. See the restoration guide.

Editorial correction in this English edition: the executable path is quoted, typographic quotes are replaced with ordinary CMD quotes, and the missing database parameter is explicit. No database backup was executed to produce this page.