How to Backup a PostgreSQL Database with pg_dump and pg_restore

Your backup job runs every night, and nobody has restored a single file from it. I ran the dump and restore commands below on PostgreSQL 18.6 on Windows 11 and kept the errors I hit.

Quick answer: to backup PostgreSQL database contents you can restore, run pg_dump in custom format, create an empty database, and load the file with pg_restore:

pg_dump -U postgres -Fc -f pg_backup_demo.dump pg_backup_demo
createdb -U postgres -T template0 pg_backup_restore
pg_restore -U postgres -d pg_backup_restore pg_backup_demo.dump

What You Need Before You Start a PostgreSQL Backup

You need C:\Program Files\PostgreSQL\18\bin on PATH and a backup folder outside the data directory. My sample (see how to create a database in PostgreSQL):

CREATE DATABASE pg_backup_demo TEMPLATE template0 LOCALE 'en-US';
\c pg_backup_demo
CREATE SCHEMA sales;
CREATE TABLE sales.customers (
    customer_id int PRIMARY KEY,
    full_name   text NOT NULL,
    state       char(2) NOT NULL);
INSERT INTO sales.customers VALUES
    (1, 'Megan Bowen', 'WA'), (2, 'Alex Wilber', 'TX'), (3, 'Adele Vance', 'CO'),
    (4, 'Lee Gu', 'IL'), (5, 'Diego Siciliani', 'FL');
CREATE TABLE sales.orders (
    order_id    int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id int NOT NULL REFERENCES sales.customers,
    amount_usd  numeric(10,2) NOT NULL);
INSERT INTO sales.orders (customer_id, amount_usd)
SELECT 1 + g % 5, 20.99 + g % 480 FROM generate_series(1, 50000) AS g;

Those 50,000 orders total $13,008,580.00. We’ll check that after every restore.

Step 1: Back Up a PostgreSQL Database with pg_dump

One script, four formats, all written to C:\DatabaseFAQsDemo\pg_backups:

.\backup-formats.cmd

@echo off
set OUT=C:\DatabaseFAQsDemo\pg_backups
set DB=pg_backup_demo
pg_dump -U postgres -Fp -f %OUT%\%DB%.sql %DB%
pg_dump -U postgres -Fc -f %OUT%\%DB%.dump %DB%
pg_dump -U postgres -Fd -j 4 -f %OUT%\%DB%_dir %DB%
pg_dump -U postgres -Ft -f %OUT%\%DB%.tar %DB%
for %%f in (%OUT%\%DB%.*) do @echo %%~zf bytes  %%~nxf

Output:

196712 bytes  pg_backup_demo.dump
733307 bytes  pg_backup_demo.sql
742400 bytes  pg_backup_demo.tar

Custom is about a quarter of the plain size; directory dumps are compressed too. -j only works with -Fd. pg_dump reads one snapshot, so your app keeps writing. I’d default to -Fc:

Format	Flag	Restore with	Parallel	Compressed
Plain	-Fp	psql -f	No	No
Custom	-Fc	pg_restore	Restore (-j)	Yes
Directory	-Fd	pg_restore	Dump + restore	Yes
Tar	-Ft	pg_restore	No	No

Step 2: Back Up One Table, a Schema, or Roles with pg_dumpall

-t picks a table and -n a PostgreSQL schema. pg_dump never saves roles; pg_dumpall –globals-only does:

.\backup-parts.cmd

@echo off
set OUT=C:\DatabaseFAQsDemo\pg_backups
pg_dump -U postgres -Fc -t sales.orders -f %OUT%\orders.dump pg_backup_demo
pg_dump -U postgres -Fc -n sales -f %OUT%\sales_schema.dump pg_backup_demo
pg_dumpall -U postgres --globals-only -f %OUT%\globals.sql
findstr /c:"CREATE ROLE nightly_backup" %OUT%\globals.sql

Output:

CREATE ROLE nightly_backup;

globals.sql holds password hashes, so guard it. Plain pg_dumpall dumps every database to one script.

Step 3: Restore with pg_restore and Verify Row Counts

createdb runs first because pg_restore won’t create the target. verify.sql then checks both:

.\restore.cmd

@echo off
set DUMP=C:\DatabaseFAQsDemo\pg_backups\pg_backup_demo.dump
createdb -U postgres -T template0 -l en-US pg_backup_restore
pg_restore -U postgres -d pg_backup_restore -j 4 %DUMP%
psql -U postgres -d pg_backup_demo -At -f verify.sql
psql -U postgres -d pg_backup_restore -At -f verify.sql

Output:

pg_backup_demo: 5 customers, 50000 orders, $13008580.00
pg_backup_restore: 5 customers, 50000 orders, $13008580.00

SELECT format('%s: %s customers, %s orders, $%s', current_database(),
       (SELECT count(*) FROM sales.customers),
       (SELECT count(*) FROM sales.orders),
       (SELECT sum(amount_usd) FROM sales.orders));

That match is the only proof a backup works.

To overwrite an existing copy, add –clean –if-exists:

pg_restore -U postgres -d pg_backup_restore --clean --if-exists C:\DatabaseFAQsDemo\pg_backups\pg_backup_demo.dump

Plain .sql dumps go through psql, like when you import a SQL file into PostgreSQL. Identical counts again:

psql -U postgres -d pg_backup_plain -q -v ON_ERROR_STOP=1 -f C:\DatabaseFAQsDemo\pg_backups\pg_backup_demo.sql

Step 4: Schedule a Nightly Postgres Backup on Windows

Task Scheduler can’t type passwords. I gave a nightly_backup role pg_read_all_data (PostgreSQL 14+) and put its login in pgpass.conf:

CREATE ROLE nightly_backup LOGIN PASSWORD '<password>';
GRANT pg_read_all_data TO nightly_backup;

-- C:\DatabaseFAQsDemo\pg_backup_secrets\pgpass.conf
localhost:5432:pg_backup_demo:nightly_backup:<password>

Task Scheduler skips your PATH, hence the full pg_dump path. -w fails instead of prompting; forfiles deletes dumps older than 14 days:

.\nightly-backup.cmd

@echo off
set PGPASSFILE=C:\DatabaseFAQsDemo\pg_backup_secrets\pgpass.conf
set PGBIN=C:\Program Files\PostgreSQL\18\bin
set OUT=C:\DatabaseFAQsDemo\pg_backups
for /f %%d in ('powershell -c "Get-Date -f yyyy-MM-dd"') do set D=%%d
"%PGBIN%\pg_dump.exe" -h localhost -U nightly_backup -w -Fc ^
  -f %OUT%\nightly_%D%.dump pg_backup_demo
if errorlevel 1 (echo Backup FAILED & exit /b 1)
forfiles /p %OUT% /m nightly_*.dump /d -14 /c "cmd /c del @path" 2>nul
echo Backup OK: nightly_%D%.dump

Output:

Backup OK: nightly_2026-10-28.dump

Register it (I didn’t keep the task). Here’s how to connect to a PostgreSQL database with pgpass:

schtasks /create /tn "PostgreSQL nightly backup" /sc daily /st 01:30 /tr "C:\DatabaseFAQsDemo\pg_backup_demo\nightly-backup.cmd"

Keep one copy offsite, too (the 3-2-1 rule).

What About pg_basebackup and Incremental Backups in PostgreSQL 17+?

pg_basebackup copies the whole cluster, never one database. PostgreSQL 17 added incremental backups: set summarize_wal = on, then pass the last manifest:

pg_basebackup -U postgres -D base_full -P
pg_basebackup -U postgres -D base_incr1 --incremental=base_full\backup_manifest
pg_combinebackup base_full base_incr1 -o base_restored

I didn’t run these; summarize_wal is off on my server.

Common Mistakes to Avoid When You Back Up PostgreSQL

pg_dump: error: permission denied for schema sales

pg_dump: error: query failed: ERROR:  permission denied for schema sales
pg_dump: detail: Query was: LOCK TABLE sales.customers, sales.orders IN ACCESS SHARE MODE
Backup FAILED

pg_dump locks every table first, so one missing grant kills the dump. With schema USAGE but no SELECT, it says permission denied for table customers. Grant pg_read_all_data, then list PostgreSQL users.

The other errors I hit:

Message	Cause	Fix
fe_sendauth: no password supplied	-w, no pgpass match	Fix PGPASSFILE
password authentication failed for user “postgres”	Stale PGPASSWORD	set PGPASSWORD= or fix the password
database “pg_backup_restore” does not exist	No target database	createdb first
input file appears to be a text format dump. Please use psql.	Plain -Fp file	psql -f

“aborting because of server version mismatch” means the server is newer than pg_dump. Use the newer pg_dump; I couldn’t reproduce it here.

Next Steps After Your First PostgreSQL Backup

If it still fails, read the first line: connection errors mean the login, permission errors mean grants. Running MariaDB too? See MariaDB backup and restore.

We’d keep these close for restore day:

• Drop a stale restore database
• List the restored databases
• Back up MongoDB with mongodump

Frequently Asked Questions