pg_dump and pg_restore are in the PostgreSQL bin directory. Make sure it is on your PATH, or use the full path:
C:\Program Files\PostgreSQL\18\bin\pg_dump.exepg_dump
Back up a database with pg_dump
# Dump one database to a SQL file:
C:\> pg_dump -U postgres -d mydb -f C:\backup\mydb.sql
# Compressed custom format (recommended for large DBs):
C:\> pg_dump -U postgres -d mydb -F c -f C:\backup\mydb.dump
# Dump all databases at once:
C:\> pg_dumpall -U postgres -f C:\backup\all_databases.sql
# Dump only schema (no data):
C:\> pg_dump -U postgres -d mydb --schema-only -f C:\backup\schema.sql
Dump formats
Choosing the right backup format
| Format | Flag | Size | Best for |
|---|---|---|---|
| Plain SQL | (default) | Largest | Human-readable, easy to inspect |
| Custom | -F c | Compressed | Recommended — supports parallel restore |
| Directory | -F d | Compressed | Parallel dump of large databases |
| Tar | -F t | Medium | Portable archive format |
Use the custom format (
-F c) for regular backups. It is compressed, supports selective restore, and allows parallel restore with -j flag.pg_restore
Restore a database with pg_restore
# Restore custom format dump:
C:\> pg_restore -U postgres -d mydb -F c C:\backup\mydb.dump
# Restore to a new database (create it first):
C:\> psql -U postgres -c "CREATE DATABASE mydb_restored;"
C:\> pg_restore -U postgres -d mydb_restored C:\backup\mydb.dump
# Parallel restore (faster for large DBs):
C:\> pg_restore -U postgres -d mydb -j 4 C:\backup\mydb.dump
# Restore plain SQL dump:
C:\> psql -U postgres -d mydb -f C:\backup\mydb.sql
Scheduled backups
Automate backups with Windows Task Scheduler
Create a batch script and schedule it to run nightly:
@echo off
set PGPASSWORD=your_password
set DATE=%date:~-4%%date:~3,2%%date:~0,2%
"C:\Program Files\PostgreSQL\18\bin\pg_dump.exe" -U postgres -d mydb -F c -f "C:\backup\mydb_%DATE%.dump"
REM Delete backups older than 30 days:
forfiles /p "C:\backup" /s /m *.dump /d -30 /c "cmd /c del @path"
Schedule it: Task Scheduler → Create Basic Task → Daily → Action: Start a Program → point to your backup.bat.
FAQ
Backup and restore questions
pg_dump vs pg_dumpall — what is the difference?
pg_dump backs up a single database. pg_dumpall backs up all databases plus global objects like roles and tablespaces. For a full server backup, use pg_dumpall. For individual database backup and restore, use pg_dump.How do I avoid entering a password every time?
Create a
pgpass.conf file in %APPDATA%\postgresql\pgpass.conf with the format: hostname:port:database:username:password. Set it to read-only. PostgreSQL reads it automatically.Restore fails with "already exists" errors
Add
--clean to pg_restore to drop existing objects before recreating them: pg_restore -U postgres -d mydb --clean C:\backup\mydb.dump. Or restore to a fresh empty database to avoid conflicts.How large will my backup file be?
Custom format dumps are typically 5-10x smaller than the actual database size due to compression. A 10 GB database usually produces a 1-3 GB dump file. Plain SQL dumps are roughly the same size as the data.