How to Back Up PostgreSQL in PostgreSQL

Galaxy Glossary

How do I back up a PostgreSQL database without downtime?

Back up PostgreSQL by exporting data and schema with pg_dump or creating a physical copy with pg_basebackup.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why back up PostgreSQL regularly?

Hardware failure, human error, or software bugs can corrupt data. Regular backups let you restore service quickly and meet compliance standards.

Which tools can I use to back up PostgreSQL?

pg_dump creates logical backups (SQL or custom format) per database. pg_dumpall handles global objects. pg_basebackup copies the entire data directory for point-in-time recovery (PITR).

How do I back up a single database with pg_dump?

Run pg_dump from the OS shell. Specify the database, output file, format, and credentials. Logical backups are portable across major versions.

Code example

pg_dump -h localhost -U admin -F c -Z 9 -f /backups/ecommerce_$(date +%F).dump ecommerce_db

How do I back up the entire cluster with pg_basebackup?

Use pg_basebackup as the postgres superuser. It streams WAL files and data files, making a binary copy that supports PITR.

Code example

pg_basebackup -h 127.0.0.1 -U replica -D /backups/cluster_$(date +%F) -Ft -z -P --wal-method=stream

What are best practices for scheduling backups?

Automate nightly logical dumps for small DBs and weekly physical backups for large clusters. Store files offsite and test restores monthly.

How can I restore from a backup?

For pg_dump files, run pg_restore into a clean database. For pg_basebackup, start PostgreSQL on the copied data directory or use it as a standby.

Can I compress and encrypt backups?

Use -Z in pg_dump for compression and pipe output to gpg or openssl to encrypt. Store keys securely.

Why How to Back Up PostgreSQL in PostgreSQL is important

How to Back Up PostgreSQL in PostgreSQL Example Usage


-- Backup all customer and order data only
echo "Backing up Customers & Orders…"
pg_dump -h localhost -U admin -F c -Z 9 \
  --table=Customers --table=Orders --table=OrderItems \
  -f /backups/sales_$(date +%F).dump ecommerce_db

How to Back Up PostgreSQL in PostgreSQL Syntax


pg_dump [connection-opts] [--schema=schema] [--table=table]
        -F {c|d|t|p} -f OUTPUT FILE DBNAME

pg_basebackup [connection-opts] -D DIRECTORY
              -F {p|t} [-z]
              --wal-method={fetch|stream}

Connection options include:
  -h host           # e.g., localhost
  -p port           # default 5432
  -U user           # e.g., admin
  -W                # prompt for password

Example (logical):
pg_dump -h localhost -U admin -F c -Z 9 -f /backups/ecommerce.dump ecommerce_db

Example (physical):
pg_basebackup -h 127.0.0.1 -U replica -D /backups/cluster -Ft -z -P --wal-method=stream

Common Mistakes

Relying only on pg_dump for large, high-traffic databases. Logical dumps can take hours and miss in-flight WAL. Fix: use pg_basebackup with WAL archiving for near-zero downtime backups.
Skipping restore tests. A backup you cannot restore is useless. Fix: Automate periodic restore into a staging server and verify critical queries.

Frequently Asked Questions (FAQs)

Does pg_dump lock tables?

In PostgreSQL ≥9.3, pg_dump uses MVCC and takes no exclusive locks; writes continue during the dump.

How large will my backup be?

Custom format (-F c) compresses data; expect 30-70% of on-disk size depending on table contents.

Can I back up only specific schemas?

Yes. Use --schema=sales repeatedly to include chosen schemas in the dump.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie