`pg_dump` and `pg_restore` Are Still the Postgres Commands You Should Reach For Before You Touch Production Data

Why these commands matter: developers do risky things to production data when they do not have a calm backup and restore story.

If you use PostgreSQL seriously, pg_dump and pg_restore should not feel optional. They are the standard tools for exporting and restoring database contents and schema in a way that is scriptable, inspectable, and much safer than manual table-copy folklore.

A sane starting point

Create a custom-format dump:

pg_dump -Fc -d mydb -f mydb.dump

Why custom format? Because it works well with pg_restore, supports selective restore flows, and is generally a better operational format than dumping giant SQL text for every situation.

To restore into another database:

createdb mydb_restore
pg_restore -d mydb_restore mydb.dump

That is the clean baseline.

Why teams still mess this up

Common bad habits:

1. copying files directly and hoping
2. running destructive SQL without a backup
3. creating text dumps they never test restoring
4. assuming cloud snapshots solve every logical recovery problem

Snapshots are useful, but logical dump/restore workflows still matter when you need:

1. selective recovery
2. environment cloning
3. migration testing
4. safer pre-change backups

Add create/clean behavior when needed

If you want pg_restore to drop and recreate objects where appropriate, use flags deliberately, not casually:

pg_restore --clean --if-exists -d mydb_restore mydb.dump

Be careful here. These flags are powerful. They are great in disposable or intentionally overwritten environments and dangerous in the wrong target.

Schema-only and data-only are useful too

You do not always need everything.

Schema only:

pg_dump -Fc --schema-only -d mydb -f mydb_schema.dump

Data only:

pg_dump -Fc --data-only -d mydb -f mydb_data.dump

This is useful for:

1. migration testing
2. review of schema drift
3. moving selected kinds of state

Why restoring a test copy is part of the backup

A backup you never test is a story, not a recovery plan.

If the data matters, part of your workflow should be:

1. create dump
2. restore into disposable database
3. verify app can connect or core queries still work

That is how you find permission issues, extension mismatches, or restore surprises before you need the backup under stress.

A practical local restore check

One clean habit is restoring into a throwaway database before risky work:

createdb mydb_restore
pg_restore -d mydb_restore mydb.dump
psql -d mydb_restore -c "\\dt"

That final \\dt is not magic, but it is a fast sanity check that tables exist and the restore did not silently go sideways. If your app or migration depends on extensions, roles, or specific schemas, this is where you catch surprises while the blast radius is still zero.

Do not forget plain old connectivity checks

After restore, also run one or two app-relevant queries:

psql -d mydb_restore -c "select now();"
psql -d mydb_restore -c "select count(*) from your_important_table;"

That is not glamorous, but it confirms the restored database is not only present. It is reachable and structurally sane enough for the next step in your workflow.

Final recommendation

Before touching production data, migrations, or risky cleanup, create a real Postgres dump with pg_dump and know how to restore it with pg_restore. These commands are not glamorous, but they are still some of the highest-value insurance a developer can learn.

Sources

• PostgreSQL: pg_dump
• PostgreSQL: pg_restore