hesela.devLogical database backup
A logical database backup represents selected schema objects and data as an export that a compatible engine can reconstruct instead of copying native storage pages.
Logical describes the representation, not the packaging. pg_dump produces SQL scripts for psql or archives for pg_restore. A binary or compressed archive is still logical. It covers one database; definitions of cluster-wide roles and tablespaces need separate coverage. [1]
PostgreSQL dumps generally support migration to newer server versions and different architectures. This is not a promise of compatibility with every engine or version. [2]
A pg_dump export cannot serve as a base for WAL replay. Continuous archiving needs a suitable physical base and the required WAL sequence. [3]
SQLite's .dump exports SQL for reconstruction. Neither an export's label nor successful import proves that application identities and dependencies survived; test the required behavior. [4] [5]
A stable export snapshot is not automatically serializable. For a reporting copy of a changing PostgreSQL database, pg_dump --serializable-deferrable waits for a suitable snapshot. PostgreSQL documents no benefit for dumps used only for disaster recovery. With no read-write transactions active at startup it makes no difference; otherwise startup may be delayed for an indeterminate time. [1]
Relations
- related
- Physical database backup, Backup, Full backup, Backup verification, Restore test, Point-in-time recovery, SQLite Online Backup API, Snapshot isolation, Serializability
Advantages
- PostgreSQL archive dumps support selecting objects for restoration. [1]
Limitations
- PostgreSQL pg_dump uses a consistent snapshot of one database, but pg_dumpall invokes it separately for each database; those snapshots are not synchronized. Recovery dependencies spanning databases need a separate coordination plan. [2]