PostgreSQL Restore
Before PostgreSQL Database Restore
Vinchin Backup & Recovery supports three recovery mode for PostgreSQL database: Original Instance Overwrite Recovery, New Instance Recovert and Restore to New Path.
Before starting to restore PostgreSQL database, there are some database configurations need DBA to check. The target recovery database server must have database backup agent installed, and the service ports: 23100 and 23101 needs to be opened for Vinchin backup server.
If Original Instance Overwrite Recovery:
The target PostgreSQL database instance needs to be shutdown
The path of data directory and archive log directory must be the same as original database server
The free storage space of the database server must be enough to save the full restore point data size.
If Restore to New Path:
You must specify a custom port number to run the database instance and the port number should not be used by any other services on the database server.
You need to specify new directories for data and the archive log, these 2 directories should be empty and should not be any directory which is being used by any other services on the database server.
For the free storage space required, it must be 2 times more than the full restore point data size.
If New Instance Recovery:
After selecting new database recovery, it is necessary to set database file path, archive log file path and instance port.
The database file path can be set arbitrarily and do not need to be consistent with the original machine.
The drive letter of the file path must be filled in correctly, and there must be sufficient remaining space on the disk.
The path behind the drive letter can be freely filled in, and the path will be automatically created according to the configuration when running the recovery task.
To create a PostgreSQL database restore job, please go to Data Resillience > Restore > Database page and click Restore , then follow the steps below.
Create Restore Job
Step 1: Restore Point
If you select a full restore point, you’ll be able to directly restore PostgreSQL database to the state of when the backup was taken. If you select an archive log restore point, you are able to roll back the database state to any time point between the first full backup timepoint and the selected archive log backup time point.
Step 2: Restore Destination
After selecting restore point, select Target Instance which you wish to restore.
The target database instance can be the original database server or a new database server.
Step 3: Restore Strategy
Recovery configuration:
Override Original Database: applies to restore the data to the production database server. Override the data of the original database instance.
Restore to New Path: applies to restore data to a new directory. The directory needs to be created by the PostgreSQL database user and has PostgreSQL user permissions.
New Instance Recovery: New Instance Recovery is suitable for verifying backup data and restoring to a different machine.
Rollback Time: if you had selected archive log backup restore point, you are able to rollback PostgreSQL database state within the given time range.
If you disable rollback time it will by default restore to the latest time point of when the backup has been taken.
Throttling Policy: Same as database backup, while restoring databases, you can also configure throttling policy to limit the database restore speed accordingly.
Common Strategy, Transfer strategy, Script configuration, Security strategy, Advanced configuration are same as database backup.
Step 4: Review & Confirm
After completing the above-mentioned settings, you are able to review and confirm the settings in one screen. Once the job has been created, you’ll be redirected to the Monitor Center > Jobs page.
As the database restore job is by default to be executed right after the creation of the job, so it will run automatically, when you see it in the current job list, it should be in running status already, and once completed, the job will be automatically deleted from the current job list.
During the database restore process, the full data size of the full backup will be transferred from Vinchin backup server to the database server, and the data will be written in to a temporary directory, after transmission is completed then it will perform restore/roll backup restore operations according to the job configurations.