Note
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_diris the backup catalog that stores all backup files and meta information.instance_nameis the backup instance for the cluster to be restored.backup_idspecifies 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 the section called “Performing Point-in-Time (PITR) Recovery”.
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 without any special preparations using partial restore options with the restore command and OIDs of these databases.
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
Note
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.
Note
The template0 and template1 databases are always restored.
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-timeoption, in the timestamp format. For example:pg_probackup3 restore -B
backup_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-timeoption value tocurrentorlatest, respectively:pg_probackup3 restore -B
backup_dir--instance=instance_name--recovery-target-time="latest"To restore the cluster state up to a specific transaction ID, use the
--recovery-target-xidoption:pg_probackup3 restore -B
backup_dir--instance=instance_name--recovery-target-xid=687To restore the cluster state up to the specific LSN, use
--recovery-target-lsnoption:pg_probackup3 restore -B
backup_dir--instance=instance_name--recovery-target-lsn=16/B374D848To restore the cluster state up to the specific named restore point, use
--recovery-target-nameoption:pg_probackup3 restore -B
backup_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-stopoption withlatestvalue:pg_probackup3 restore -B
backup_dir--instance=instance_name--recovery-target-stop="latest"To restore the cluster to the earliest point of consistency, use
--recovery-target-stopoption with theimmediatevalue:pg_probackup3 restore -Bbackup_dir--instance=instance_name--recovery-target-stop='immediate'