PostgreSQL backup tool with Point-In-Time-Recovery and restore verification
postgresql-logical-backup-scope Specification
Purpose
Defines which tables a PostgreSQL logical backup leaves out, either completely or by their rows only, what a restore of such a backup contains, which privileges the backup still requires and how the estimated database size accounts for the tables left out.
Requirements
Requirement: A whole-excluded table is missing from the backup and the restore
A PostgreSQL logical database SHALL have a list of whole-excluded tables. A table matched by an entry of this list SHALL be left out of the backup together with its structure, indexes, constraints and rows, so a restore of that backup SHALL NOT create the table. An entry of the form schema.table SHALL match by schema and table name. An entry with no schema part SHALL match only tables that the connecting role sees by their bare name under its default search path, which is usually only public; a same-named table in another schema SHALL NOT be matched by it. Either part MAY be a glob pattern, such as logs_*, and a double-quoted part SHALL match that name literally. Tables not matched by any entry SHALL be backed up and restored with their rows.
Scenario: Whole-excluded table by name
- WHEN a database lists
audit_tmpas a whole-excluded table, a backup completes and that backup is restored into an empty database - THEN the restored database has no
public.audit_tmptable, and every other table is restored with its rows
Scenario: Unqualified name leaves other schemas alone
- WHEN a database lists
audit_tmpas a whole-excluded table, the role's default search path is"$user", public, and the tablespublic.audit_tmpandarchive.audit_tmpexist - THEN a restore of its backup has no
public.audit_tmptable and hasarchive.audit_tmpwith its rows
Scenario: Whole-excluded tables by pattern
- WHEN a database lists
public.old_*as a whole-excluded table and has the tablespublic.old_ordersandreporting.old_orders - THEN a restore of its backup has no
public.old_orderstable and hasreporting.old_orderswith its rows
Requirement: A table whose data is excluded is restored empty
A PostgreSQL logical database SHALL have a second list, of tables whose data is excluded. Its entries SHALL match tables by the same rules as the list of whole-excluded tables. A table matched by an entry of this list SHALL be backed up without its rows, while its structure, column defaults, indexes, constraints and triggers SHALL stay in the backup. A restore of that backup SHALL create the table with all of these and with no rows. When the database also limits the backup to certain schemas, the list SHALL apply only within those schemas. An entry SHALL match only the tables whose own names it matches: naming a partitioned table SHALL NOT exclude the rows of its partitions, which need entries or a pattern of their own. Tables matched by neither list SHALL be backed up and restored with their rows.
Scenario: Naming a partitioned table keeps its partitions' rows
- WHEN a database has the partitioned table
public.eventswith the partitionspublic.events_2025andpublic.events_2026, both holding rows, and lists onlypublic.eventsas a table whose data is excluded - THEN a restore of its backup has
public.events_2025andpublic.events_2026with their rows
Scenario: Partitioned table needs its partitions matched
- WHEN a database has the partitioned table
public.eventswith the partitionspublic.events_2025andpublic.events_2026, and listspublic.events_*as tables whose data is excluded - THEN a restore of its backup has
public.eventsand both partitions with no rows
Scenario: Data-excluded table restored empty
- WHEN a database lists
public.logsas a table whose data is excluded,public.logsholds rows and has a primary key and a secondary index, a backup completes and that backup is restored into an empty database - THEN the restored database has the
public.logstable with the same columns, primary key and secondary index and with no rows, and every other table is restored with its rows
Scenario: Data-excluded tables by pattern
- WHEN a database lists
*.history_*as tables whose data is excluded and has the tablespublic.history_2024andarchive.history_2025 - THEN a restore of its backup has both tables with no rows
Scenario: Data exclusion together with included schemas
- WHEN a database limits the backup to the schema
publicand listslogsas a table whose data is excluded, and the tablespublic.logsandreporting.logsexist - THEN a restore of its backup has
public.logswith no rows and has noreportingschema
Scenario: Existing databases after the upgrade
- WHEN Databasus is upgraded and a PostgreSQL logical database created before the upgrade is backed up
- THEN the database has an empty list of tables whose data is excluded, and its backup contains the same tables and rows as before the upgrade
Requirement: A table in both lists is excluded entirely
When a table is matched by an entry of the whole-excluded list and by an entry of the data-excluded list, the backup SHALL leave out the whole table, as if only the whole-excluded entry existed. Saving a database whose lists overlap SHALL succeed.
Scenario: Same table in both lists
- WHEN a database lists
public.logsboth as a whole-excluded table and as a table whose data is excluded, and its backup is restored - THEN the save succeeds, and the restored database has no
public.logstable
Requirement: Excluding a table's data does not lift the read-privilege check
When a PostgreSQL logical database is created, updated or has its connection tested, Databasus SHALL refuse it if its connecting role cannot read every table and sequence the backup will contain. A table whose data is excluded SHALL count as a table the backup contains, because the backup still has to lock it, so the role SHALL still need read access to it. Only a whole-excluded table SHALL be skipped by this check, matched by the same rules the backup uses, so a same-named table in a schema an unqualified entry does not reach SHALL still be checked. The error returned when the check fails SHALL name the tables and schemas the role cannot read, SHALL suggest whole-table exclusion as a way to narrow the backup, and SHALL state that excluding only a table's data still requires read access to that table.
Scenario: Role cannot read a data-excluded table
- WHEN a database lists
public.logsas a table whose data is excluded and its connecting role has no read access topublic.logs - THEN testing the connection fails with an error that names
public.logsand says that excluding only its data still requires read access
Scenario: Role cannot read a same-named table in another schema
- WHEN a database lists
logsas a whole-excluded table, the role's default search path is"$user", public, and the role cannot readarchive.logs - THEN testing the connection fails with an error that names
archive.logs
Scenario: Role cannot read a whole-excluded table
- WHEN a database lists
public.logsas a whole-excluded table, its connecting role has no read access topublic.logsand can read every other table and sequence in the backup - THEN testing the connection succeeds
Requirement: The estimated database size leaves out excluded rows
The estimated size of a PostgreSQL logical database SHALL leave out the size of whole-excluded tables and the size of tables whose data is excluded, including the size of their indexes. Tables SHALL be matched by the same rules the backup uses, so a same-named table that an unqualified entry does not reach SHALL still be counted.
Scenario: Large data-excluded table
- WHEN a database lists a table whose data is excluded, and that table has rows and a secondary index
- THEN the estimated database size is smaller than the estimate without any exclusion by at least that table's total size, its indexes included
Scenario: Same-named table in another schema is still counted
- WHEN a database lists
logsas a table whose data is excluded, the role's default search path is"$user", public, and the tablespublic.logsandarchive.logshold rows - THEN the estimated database size leaves out
public.logsand includesarchive.logs
Requirement: Both lists are saved, shown and copied
Both lists SHALL be accepted when a PostgreSQL logical database is created or updated, SHALL be returned when the database is read and SHALL be carried over when the database is copied. Each list SHALL accept entries pasted as one value separated by commas or new lines, SHALL drop surrounding whitespace and empty entries, and SHALL keep each entry once. Updating a database without the list of tables whose data is excluded SHALL clear that list, as it does for the whole-excluded list.
Scenario: Saving and reading the lists
- WHEN the user saves a database with
audit_tmpas a whole-excluded table and withlogs, history_*pasted as tables whose data is excluded - THEN reading the database returns
audit_tmpin the whole-excluded list andlogsandhistory_*as two entries in the data-excluded list
Scenario: Copying a database
- WHEN the user copies a database whose data-excluded list holds
logs - THEN the copy has
logsin its data-excluded list
Requirement: The form tells the two exclusions apart
The backup settings of a PostgreSQL logical database SHALL show the two lists as separate fields, one directly below the other. The whole-excluded list SHALL be labeled "Exclude whole tables" and its tooltip SHALL say that the table will be missing after a restore. The data-excluded list SHALL be labeled "Exclude table data" and its tooltip SHALL say that the table is restored empty with its structure kept, that a table in both lists is excluded entirely, that the partitions of a partitioned table must be matched by their own names or a pattern, and that a restore fails to recreate a foreign key when another backed-up table references rows of an emptied table. Both tooltips SHALL describe the accepted name and pattern forms, including that a name without a schema matches only tables on the default search path, usually public, and that *.name matches it in every schema. The database page SHALL show each list under the same label when it is not empty. The section holding these fields SHALL open on its own when either list is not empty. The labels and tooltips SHALL be available in every interface language. The forms of other database engines SHALL keep their current labels.
Scenario: Editing a database with data-excluded tables
- WHEN the user opens the edit form of a PostgreSQL logical database whose data-excluded list holds
logsand whose other advanced settings are empty - THEN the advanced settings are open, and the form shows "Exclude whole tables" followed by "Exclude table data" with
logsin it
Scenario: Database page
- WHEN the user opens the page of a PostgreSQL logical database with
audit_tmpas a whole-excluded table andlogsas a table whose data is excluded - THEN the page shows
audit_tmpunder "Exclude whole tables" andlogsunder "Exclude table data"
Scenario: MySQL form keeps its label
- WHEN the user opens the edit form of a MySQL database
- THEN its table exclusion field is still labeled "Exclude tables"