While backup files for restore can be retrieved from different sources
(the file system, S3, or SSH SFTP),
pg_probackup3 can only restore the
Postgres Pro server
PGDATA to a local file system.
To restore the database cluster from a backup, run the restore command with at least the following options:
pg_probackup3 restore -Bbackup_dir--instance=instance_name-ibackup_id
Where:
backup_dir is the backup catalog that
stores all backup files and meta information.
instance_name is the backup instance
for the cluster to be restored.
backup_id specifies the backup to
restore the cluster from.
If you restore ARCHIVE backups or
perform
PITR,
pg_probackup3 creates a recovery configuration
file once all data files are copied into the target directory. This file
includes the minimal settings required for recovery, except for the
password in the
primary_conninfo
parameter; you have to add the password manually or use
the --primary-conninfo option, if required.
pg_probackup3 writes recovery settings into
the probackup_recovery.conf file and then includes
it into postgresql.auto.conf.
If you are restoring a STREAM backup, the restore is complete
at once, with the cluster returned to a self-consistent state at
the point when the backup was taken. For ARCHIVE backups,
Postgres Pro replays all available archived WAL
segments, so the cluster is restored to the latest state possible
within the current timeline. You can change this behavior by using the
recovery target
options with the restore command,
as explained in Section 3.3.2.
If the cluster to restore contains tablespaces,
pg_probackup3
restores them to their original location by default. To restore
tablespaces to a different location, use the
--tablespace-mapping/-T option.
Otherwise, restoring the cluster on the same host will fail if tablespaces
are in use, because the backup would have to be written to the
same directories.
When using the --tablespace-mapping/-T
option, you must provide absolute paths to the old and new
tablespace directories. If a path happens to contain an equals
sign (=), escape it with a backslash. This option can be
specified multiple times for multiple tablespaces. For example:
pg_probackup3 restore -Bbackup_dir--instance=instance_name-Ddata_dir-j 4 -ibackup_id-T tablespace1_dir=tablespace1_newdir-T tablespace2_dir=tablespace2_newdir
You can restore particular databases using partial restore options with the restore command. The sections below describe all supported partial restore methods.
If you have enabled
partial restore
before taking backups, you can restore specific databases by name
using the --db-include-name and
--db-exclude-name options.
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
The --db-include-name option can be specified
multiple times. For example, to restore only the databases
db1 and db2, run the following
command:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--db-include-name=db1 --db-include-name=db2
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-exclude-name option can be specified
multiple times. For example, to exclude the databases db1
and db2, run the following command:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--db-exclude-name=db1 --db-exclude-name=db2
After the Postgres Pro cluster is
successfully started, drop the excluded databases using the
DROP DATABASE command.
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-replica
option for specific databases.
The template0 and
template1 databases are always restored.
Options --db-exclude-name and
--db-include-name cannot be used together.
You can restore particular databases
without any special preparations using the
--db-include-oid and --db-exclude-oid.
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
The --db-include-oid option can be specified
multiple times. For example, to restore only the db1
and db2 databases with OIDs dboid1
and dboid2, respectively, run the following command:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--db-include-oid=dboid1 --db-include-oid=dboid2
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-exclude-oid option can be specified
multiple times. For example, to exclude the db1
and db2 databases with OIDs
dboid1 and dboid2, respectively,
from restore, run the following command:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--db-exclude-oid=dboid1 --db-exclude-oid=dboid2
After the Postgres Pro cluster is
successfully started, drop the excluded databases using the
DROP DATABASE command.
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-replica
option for specific databases.
The template0 and
template1 databases are always restored.
Options --db-exclude-oid and
--db-include-oid cannot be used together.
If you have enabled continuous WAL archiving before taking backups, you can restore the cluster to its state at an arbitrary point in time (recovery target) using recovery target options with the restore command.
You can use both STREAM and ARCHIVE backups for point-in-time recovery as long as the WAL archive is available at least starting from the time the backup was taken.
To restore the cluster state at the exact time, specify the
--recovery-target-time option, in the
timestamp format. For example:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--recovery-target-time="2024-04-10 18:18:26+03"
To restore the current or latest cluster state, set the
--recovery-target-time option value to
current or latest, respectively:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--recovery-target-time="latest"
To restore the cluster state up to a specific transaction
ID, use the --recovery-target-xid option:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--recovery-target-xid=687
To restore the cluster state up to the specific LSN, use
--recovery-target-lsn option:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--recovery-target-lsn=16/B374D848
To restore the cluster state up to the specific named restore
point, use --recovery-target-name option:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--recovery-target-name="before_app_upgrade"
To restore the backup to the latest state available in
the WAL archive, use --recovery-target-stop option
with latest value:
pg_probackup3 restore -Bbackup_dir--instance=instance_name--recovery-target-stop="latest"
To restore the cluster to the earliest point of consistency,
use --recovery-target-stop option with the
immediate value:
pg_probackup3 restore -B backup_dir --instance=instance_name --recovery-target-stop='immediate'