4.4. Partial Restore #
You can restore particular databases using partial restore options with the restore command. The sections below describe all supported partial restore methods.
Important
The following specifics apply to all partial restore methods:
After the Postgres Pro cluster is successfully started, drop the excluded databases using the
DROP DATABASEcommand.To decouple a single cluster containing multiple databases into separate clusters with minimal downtime, run partial restore of the cluster as a standby using the
--restore-as-replicaoption for specific databases.The
template0andtemplate1databases are always restored.
4.4.1. Partial Restore by Name #
To perform partial restore using the --db-exclude-name or --db-include-name options, grant the read-only access to pg_catalog.pg_database to the backup role only in the database used for connection to Postgres Pro server:
GRANT SELECT ON TABLE pg_catalog.pg_database TO backup;
Important
To restore databases by name, the backup must contain a database map. Without a map, filtering is possible only by OID.
To restore only the specified databases, run the restore command with the following options:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--db-include-name=dbname
To exclude one or more databases from restore, use the --db-exclude-name option:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--db-exclude-name=dbname
The --db-include-name and --db-exclude-name options can be specified multiple times, but cannot be used together. For example, to restore the db1 and db2 databases, run the following command:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--db-include-name=db1 --db-include-name=db2
To exclude the same databases as in the previous example, run:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--db-exclude-name=db1 --db-exclude-name=db2
4.4.2. Partial Restore by OID #
You can restore particular databases without any special preparations (unlike partial restore by name) using the --db-include-oid and --db-exclude-oid options.
To restore the specified databases only, run the restore command with the following options:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--db-include-oid=dboid
Note
Information about all included databases is written to the recovery_target_db configuration parameter. Therefore, during restore, only WAL records for the included databases are processed, while others are ignored, which speeds up the operation.
To exclude one or more databases from restore, use the --db-exclude-oid option:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--db-exclude-oid=dboid
The --db-include-oid and --db-exclude-oid options can be specified multiple times, but cannot be used together. For example, to restore only the db1 database with the dboid1 OID and db2 with dboid2, run the following command:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--db-include-oid=dboid1 --db-include-oid=dboid2
To exclude the same databases as in the previous example, run:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--db-exclude-oid=dboid1 --db-exclude-oid=dboid2