PostgreSQLSchema & Admin (DDL)

How to Backup and Restore a PostgreSQL Database with pg_dump & psql

Export compressed SQL dump archives and restore them cleanly to local or remote database instances.

Quick SQL Solution

Safe (Read / Non-destructive)PostgreSQLIntermediate

# Backup:
pg_dump -U postgres -d mydb -Fc -f mydb_backup.dump

# Restore:
pg_restore -U postgres -d mydb --clean --if-exists mydb_backup.dump

When to use this scenario:You need to take a snapshot backup before deploying a major schema migration or clone production data to your local development environment.

Step-by-Step Query Breakdown

1Create custom compressed binary dump (Recommended)

pg_dump -U postgres -h localhost -p 5432 -d production_db -Fc -f backup_2026.dump

-Fc produces a compressed custom format archive that supports parallel multi-threaded restores and selective table extraction.

2Create plain text SQL dump

pg_dump -U postgres -d production_db > backup.sql

Creates a human-readable SQL text script that can be inspected with any text editor.

3Restore custom dump with pg_restore

pg_restore -U postgres -d dev_db --clean --if-exists -j 4 backup_2026.dump

--clean drops existing database objects before recreating them. -j 4 uses 4 parallel CPU jobs for blazing fast restores.

4Restore plain text SQL dump with psql

psql -U postgres -d dev_db -f backup.sql

Executes the SQL script line-by-line using psql.

Critical SQL Pitfalls & Precautions

•Do not use "psql" to restore custom format (.dump) files. Always use "pg_restore" for -Fc archives and "psql" for plain text .sql files.

Frequently Asked Questions

PostgreSQL Backup & Restore (pg_dump) - Frequently Asked Questions

Common questions about transactional safety, indexing, and engine compatibility.

Use the "-t" flag: "pg_dump -U postgres -d mydb -t users -Fc -f users.dump".

Related SQL Query Guides

Browse All SQL Recipes

ANSI SQL / UniversalSafe

How to Find and Delete Duplicate Rows in SQL

Identify rows with duplicate column values using GROUP BY and HAVING count(*) > 1, and safely remove duplicates using ROW_NUMBER() CTEs.

ANSI SQL / UniversalDestructive

DELETE vs TRUNCATE vs DROP in SQL: Differences Explained

Compare row-by-row logging (DELETE), fast table emptying with auto-increment reset (TRUNCATE), and complete table removal (DROP).

PostgreSQLSafe

How to UPSERT in PostgreSQL Using ON CONFLICT DO UPDATE

Atomically insert a new row or update an existing row if a unique constraint or primary key conflict is encountered.

ANSI SQL / UniversalSafe

How to Calculate a Running Total (Cumulative Sum) in SQL

Compute cumulative running totals over time or partitioned groups using SUM() OVER (ORDER BY ...).

PostgreSQLSafe

How to Create an Index Concurrently in PostgreSQL (Zero Downtime)

Build a production database index on a table with millions of rows without locking out live INSERT, UPDATE, or DELETE operations.

ANSI SQL / UniversalReversible

How to UPDATE a Table From Another Table in SQL

Update values in one table based on matching columns or aggregations in a secondary table.