Skip to content
DBAExperts
PostgreSQL Backups & recovery

How to take a PostgreSQL backup with pg_dump

A consistent logical backup lets you recover a database or migrate it without depending on the physical state of the disk. pg_dump exports a single database; pg_dumpall exports global roles and tablespaces.

  1. 1

    Back up a database in custom format (recommended)

    The custom format (-Fc) is compressed and lets you restore selectively with pg_restore. It is the preferred format for most cases.

    pg_dump -h localhost -U postgres -Fc -f ventas.dump ventas
  2. 2

    Back up only the schema or only the data

    Use --schema-only for structure without data, or --data-only for data without structure. Handy for versioning the DDL separately.

    pg_dump -U postgres --schema-only -f esquema.sql ventas
  3. 3

    Back up global objects with pg_dumpall

    pg_dump does NOT include roles, passwords, or tablespaces. Export those separately with pg_dumpall --globals-only to get a complete backup of the cluster.

    pg_dumpall -U postgres --globals-only -f globales.sql
  4. 4

    Verify that the backup can be read

    A backup that is never tested is not a backup. List the contents of the custom dump to confirm it came out intact.

    pg_restore --list ventas.dump

// common mistake

The most common mistake is backing up with pg_dump alone and assuming everything is covered: roles and passwords live outside the database and are only captured by pg_dumpall --globals-only. Without them, the restore will fail on permissions.

// when it's worth an expert

If this is a production database with data you cannot afford to lose, a manual pg_dump is not enough: you want a strategy with physical backups (pg_basebackup + WAL), retention, and periodic restore tests. At dba.mx we set up and test that scheme for you at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.