Logical Backups in PostgreSQL

• [email protected]
• 15 Jun, 2025
• 0 Comments
• 1 Min Read

Logical Backups in PostgreSQL

Logical Backups in PostgreSQL

What Are Logical Backups?

Logical backups Contain SQL commands or binary dumps that can recreate database objects and their data.  Logical backups are database-version independent .Allow selective backups (table-level, schema-level).It Can be used for partial or full restores.

pg_dump:

• Purpose: Backup a single database or selected objects (tables, schemas).

• Output formats: SQL script, custom, directory, tar.

• Common usage of pg_dump Create portable dumps, Backup specific tables ,Schema-only or data-only dumps

• It does not include roles, tablespaces, or other global objects.

Backup a Single Database using pg_dump

pg_dump -Fc -d mydb -f mydb_backup.backup

pg_restore:

• Purpose: Restore backups created by pg_dump (custom or directory format).

• Common usages of pg_restore include: selective restoration of specific objects such as tables, schemas, and functions; parallel restoration when using directory format; and the ability to list the contents of a dump file before restoring.

• Works only with non-plain formats (like .backup or directory).

Restore the Database using pg_restore

createdb mydb_restore
pg_restore -d mydb_restore mydb_backup.backup

pg-dumpall:

• Purpose: Backup the entire PostgreSQL cluster, including:

  • All databases

  • Global objects (roles, tablespaces, configurations)

• Output format: Plain SQL only

• It can be Used for disaster recovery or server migrations.

Backup Global Objects and All Databases using pg_dumpall

pg_dumpall > full_cluster_backup.sql

At Learnomate Technologies, we provide the best training on PostgreSQL and other database technologies. For more insights, visit our YouTube channel. Don’t forget to check out our website for more details about our training programs. Follow my Medium account @ankush.thavali for more articles and updates.