Backup

Logical Backup

• Captures data at the schema/object level, such as tables, views, functions, and other database objects.

• Typically created using tools like pg_dump and pg_dumpall.

• Output is a SQL script or a custom-format file that can recreate the database objects and their data.

• Pros:

  • Allows for fine-grained restoration (e.g., single table or function).

  • Portable across different PostgreSQL versions and platforms.

  • Easy to inspect and modify.

• Cons:

  • Slower for large databases due to SQL-level reconstruction.

  • Does not include cluster-wide data like roles or tablespaces unless explicitly included.

Create backup

pg_dump -h localhost -p 5433 -U postgres -Fp postgres > mydb_$(date +%F).sql

Restore backup

psql -h localhost -p 5433 -U postgres -d my_new_database -f mydb_2025-09-26.sql

Physical Backup

• Captures the raw database files at the file system level, including all data files, transaction logs (WAL), and control files.

• Typically created using pg_basebackup or by directly copying the data directory (with the server stopped or using a filesystem snapshot).

• Used in conjunction with WAL archiving to support Point-In-Time Recovery (PITR).

• Pros:

  • Fast backup and restore, especially for large databases.

  • Full copy of the database cluster state at a point in time.

  • Enables recovery to any point in time when combined with WAL.

• Cons:

  • Not portable across different PostgreSQL versions or platforms.

  • Cannot restore individual tables or objects directly.

  • Requires more disk space and careful management of WAL files.