Chat now with support
Chat with Support

NetVault Plug-in for PostgreSQL 12.3 - User Guide

Introducing NetVault Backup Plug-in for PostgreSQL Installing and removing the plug-in Configuring the plug-in Backing up data Restoring data Troubleshooting

Restoring data

•
Selecting data for a restore
•
Setting restore options
•
Finalizing and submitting the job
•
Performing advanced restore procedures

Selecting data for a restore

1
In the Navigation pane, click Create Restore Job.
2
On the Create Restore Job — Choose Saveset page, select Plug‑in for PostgreSQL from the Plugin Type list.
3
To filter the items displayed in the saveset table further, use the Client, Date, and Job ID lists.
The table displays the saveset name (job title and saveset ID), creation date and time, and size. By default, the list is sorted by creation date.
4
In the saveset table, select the applicable saveset to display the database cluster that was the target of the backup.
When you select a saveset, the following details are displayed in the Saveset Information area: Job ID, job title, server name, client name, plug-in name, saveset date and time, retirement setting, Incremental Backup or not, Archive or not, saveset size, and snapshot-based backup or not.
The following table outlines the backup type identifiers:
 
Backup type identifier
Backup type and format

CA FULL FILESYSTEM
•
Base File Level Data Copy

CA INCREMENTAL WAL
•
Incremental File Level Data Copy

SQL FULL DB CLUSTER ARCHIVE
•
Full Database Cluster
•
TAR/Custom Archive File

SQL FULL DB CLUSTER PLAIN
•
Full Database Cluster
•
Plain-text SQL Script File

SQL INDIVIDUAL ARCHIVE
•
Individual Database/Table
•
TAR/Custom Archive File

SQL INDIVIDUAL PLAIN
•
Individual Database/Table
•
Plain-text SQL Script File

SQL GLOBAL OBJECT ONLY
•
Global Objects Only
5
Click Next.
6
On the Create Selection Set page, select the data that you want to restore.
Based on the backup type, opening the database cluster displays the following:
•
Full Database Cluster: The following nodes are displayed for this type:
•
Configuration Files, if included in the backup
•
Global Objects
•
All Databases
•
Individual Database Cluster: The following nodes are displayed for this type:
•
Configuration Files, if included in the backup
•
All Databases
7
To view the databases included in the backup, open All Databases.
8
For next-level selections, drill down further.
9
Select the database cluster, databases, or tables as required.
 
* 
IMPORTANT: BLOBs included in the backup are displayed as a separate node, and not beneath the associated table. Selecting this node restores all BLOBs contained in the saveset and not just objects associated with the selected table. Also note that, to restore BLOBs, this node must be selected explicitly, that is, either this node or the parent database node must be selected. Selecting only the table does not restore the associated BLOBs.

Setting restore options

Select the applicable topic:

•
If you are restoring a SQL Logical Data Dump, skip to Setting restore options for a SQL Logical Data Dump.
•
If you are restoring a File-Level Data Copy, skip to Setting restore options for a File-Level Data Copy.
Setting restore options for a SQL Logical Data Dump
1
On the Create Selection Set page, click Edit Plugin Options, and configure the applicable parameters on the Options tabs.
2
If you are restoring a Plain-Text SQL Script File backup, select the applicable options in the Pre-Restore Options section to specify what actions should be taken before the plug-in begins the restoration of a database:
•
Drop and Recreate Database: Select this option if you want to drop an existing database before restoring it from the backup. The database is created using the template specified in the Configure dialog box, Template Database Used during CREATE Database option. All existing data is discarded if this option is selected.
 
* 
IMPORTANT: Regardless of whether the Drop and Recreate Database option is selected, the plug-in performs this action if you are restoring Template Databases.
•
Create Database Only: Select this option if you want to create a nonexistent database or to write data to the existing database. If the database is not found during restore, a new database is created. If the database is present, tables that are not part of the current restore job are not modified in any manner. If the selected tables are present in the database, the action is based on whether the Include Drop Object Commands option was selected during backup:
•
Include Drop Object Commands was selected: The table is dropped and re-created. The restored table only contains records that were present at the time of backup.
•
Include Drop Object Commands was not selected: For existing tables, you can use the Truncate Table(s) option to truncate existing records before restoring.
If Truncate Table(s) is not enabled, the plug-in attempts to insert all the backed-up records into the existing table. For tables on which the unique or primary keys have been defined, this action might result in constraint errors.
•
Truncate Table(s): Select this option if you want to delete all existing records from the tables and restore the records present in the backup saveset. After restoration, the tables only contain records that were present at the time of backup. Be aware of the following:
•
The database must exist.
•
For existing tables, the “create table” command during psql execution fails. If the Stop on Error option is selected, the restore stops immediately on encountering this error. Select the Continue with Warnings option if the selected tables are present to ignore these errors and continue with table truncation and data restoration.
•
No errors are encountered for tables that do not exist.
3
If you are restoring a Plain-Text SQL Script File backup, select the applicable options in the Restore Options section:
•
Stop on Error: Select this option if you want the restore to stop immediately when an error occurs. Changes made before the error are not rolled back, which leaves the database in an inconsistent state.
The entire restore job is not canceled; only the restoration of the current database stops. Restoration of other databases included in the job continues normally.
If the Disable Triggers option was enabled for backup and the restoration of the database stops in between, the plug-in does not enable triggers defined for the last table that caused the failure.
•
Restore as Single Transaction: Select this option if all commands in the database backup must complete successfully; otherwise, no changes are applied. The changes made are rolled back if an error occurs.
The commit or roll-back action applies to a specific database and not the entire restore job. The databases for which restoration has completed successfully are not affected. Similarly, this process does not affect restoration of remaining databases in the job.
•
Continue Restore with Warnings: Select this option if you want the plug-in to ignore the errors and continue with the restore. The errors encountered during job execution are recorded as warning messages in the NetVault Backup logs and the job status is reported as “Completed with Warnings.”
You can use this option to restore nonexistent or nonduplicate records into existing tables without truncating the existing records.
 
* 
IMPORTANT: If you select this option, review the logs after the job is complete to confirm that they only contain warnings pertaining to the following:

Primary or Unique Key Constraints Database Object (like table, language, user, role, and others) Already Exists

You can ignore these errors. However, if any other type of error message is noted, take corrective action, such as restoring the database again by selecting Drop and Recreate Database or Truncate Table(s).
4
If you are restoring a TAR Archive File or Custom Archive File backup, select the applicable options:
•
Stop on Error: Select this option if you want the restore to stop immediately when an error occurs. Changes made before the error are not rolled back, which leaves the database in an inconsistent state.
The entire restore job is not canceled; only the restoration of the current database stops. Restoration of other databases included in the job continues normally.
 
* 
IMPORTANT: If this option is not selected, the plug-in ignores the errors and continues the job. The errors encountered during job execution are recorded as warning messages in the NetVault Backup logs and the job status is reported as “Completed with Warnings.” Review the logs after the job is complete to confirm that they only contain warnings pertaining to the following:

Primary or Unique Key Constraints Database Object (like table, language, user, role, and others) Already Exists

You can ignore these errors. However, if any other type of error message is noted, take corrective action, such as restoring the database again by selecting Drop and Recreate Database or Do Not Restore Data for Failed Create Table Commands.
•
Drop and Recreate Database: Select this option if you want to drop an existing database before restoring it from the backup. The database is created using the template specified in the Configure dialog box, Template Database Used during CREATE Database option.
You can also use the Exclude Access Privileges option with Create Database Only. To prevent restoration of access privileges, grant and revoke commands, select this option. Without this option selected, the restoration fails if the required users or roles do not exist.
 
* 
IMPORTANT: Regardless of whether the Drop and Recreate Database option is selected, the plug-in performs this action if you are restoring Template Databases.
•
Create Database Only: Select this option if you want to create a nonexistent database or to write data to the existing database. If the database is not found during restore, a new database is created. If the database is present, tables that are not part of the current restore job are not modified in any manner. If the selected tables are present in the database, the action is based on the following options:
•
Drop Database Objects: To drop the existing table and restore it from the backup, select this option. If you select this option, you lose the existing records. The restored table only contains records that were present at the time of backup.
•
Restore Data for Failed Create Table Commands: If you want the plug-in to insert all the backed-up records into the existing table, select this option. For tables on which the unique or primary keys have been defined, this action might result in constraint errors.
•
If Stop on Error option is selected, the restoration of the database stops.
•
Do Not Restore Data for Failed Create Table Commands: With this option enabled, the existing tables are not restored.
You can also use the Exclude Access Privileges option with Create Database Only. To prevent restoration of access privileges, grant and revoke commands, select this option. Without this option selected, the restoration fails if the required users or roles do not exist.
•
Restore Data Only (No Schema Definitions): Select this option if you want to restore only the data from the backup and exclude the schema definitions. The restore fails if the target database or table does not exist.
 
* 
IMPORTANT: Template Databases are not restored if you select this option.
You can also use the Disable Triggers option with Restore Data Only. Select this option if there are referential integrity checks or other triggers on the tables. This option temporarily disables triggers on the target tables during restore. After successful data restoration, the triggers are enabled. If Stop on Error is selected and the restoration of the database stops in between, the plug-in does not enable triggers defined for the last table that caused the failure.
5
Click OK.

Setting restore options for a File-Level Data Copy

1
On the Create Selection Set page, click Edit Plugin Options.
2
On the Restore Options tab, if you want to select a different location to restore the files to, select the Relocate all restored files to the directory check box, and enter the new path in the text box.
If you want to obtain access to a specific set of files without compromising your current environment, you can use this option.
 
* 
IMPORTANT: The recovery process for a PostgreSQL Server requires that all the physical files are restored to their original locations; that is, PostgreSQL does not support recovery to alternate paths. However, you can restore files to alternate locations to retrieve specific files manually, before overwriting the original files in the database cluster Data directory. You can also restore files to an alternate location for testing and auditing purposes.

For Incremental Backups, restore the WAL files to the staging directory. During recovery, PostgreSQL Server retrieves WAL files from the staging directory.
3
If you selected a new location and files already exist in the new location for any reason and you want to overwrite them, select the If existing, overwrite files option.
4
On the Base Data Files tab, select the applicable options:
•
Include Backup Label File with Restore: If you want to generate a file named “backup_label” in the target directory during the restore of a Base Backup, select this option. This file contains information generated by PostgreSQL during the backup, which is useful during recovery. The predefined contents of the “backup_label” file are shown in the Backup Label field. If you select this option and the Tablespace Map field has content, an additional file, “tablespace_map,” is also generated during the restore of a Base Backup.
•
Do not Restore WAL Directory Contents (recommended): If you want to exclude the contents of the WAL directory during a restore of a Base Backup, select this option. The WAL directory is pg_wal for PostgreSQL 10.x and later and pg_xlog for PostgreSQL Server 9.x and earlier.
•
Do not Restore pg_replslot Directory Contents: If you want to exclude the contents of the replication slots directory, pg_replslot, during a restore of a Base Backup, select this option.
•
Do not Restore postmaster.pid and postmaster.opts Files: If you want to exclude the “postmaster.pid” and “postmaster.opts” files, during a restore of a Base Backup, select this option.
The following fields are displayed for information and do not require selection or updating:
•
Backup Label: This field displays information related to a Base Backup that is included in the “backup_label” file described previously. For Offline Backups, this field does not display anything. Unless directed to do so by Quest, do not modify the contents of this field.
•
Stop Log Sequence Number: This field shows the log sequence number when the stop backup command was issued.
•
Tablespace Map: This field displays information related to a Base Backup. If you select the Include Backup Label File with Restore option described previously, this information is included in the “tablespace_map” file. For Offline Backups, this field is null. Unless directed to do so by Quest, do not modify the contents of this field.
 
* 
IMPORTANT: Tablespace Map contains tuples of symbolic link names and directories. Before starting a recovery process, if this field displays a tuple, ensure that the database cluster data pg_tblspc directory contains a symbolic link with the listed name that points to the directory listed in the tuple.

Examples of creating the link:

Linux or UNIX: ln -s /postgresdata/my_postgre_tbs_01 /opt/postgres/11/data/pg_tblspc/16405

Windows: mklink /J "C:\Program Files\PostgreSQL\11\data\pg_tblspc\16405" "P:\postgresdata\my_postgre_tbs_01"

5
On the Point in Time Recovery tab, select the applicable options:
•
Include Recover Command File with Restore: If you want to generate a Recovery Command file, select this option. In the Recovery Command File Location box, enter the complete path to the location where the Recovery Command file should be placed. If you plan to perform a recovery procedure, PostgreSQL requires that you use the “recovery.conf” filename and that the file is located in the database cluster Data directory.
•
Restore Command: In this box, enter the WAL Log restore command to use when PostgreSQL needs to access a specific log during the recovery process. Use a restore command that copies WAL Log files from the staging directory, You can use the wildcards %f, which represents the filename, and %p, which represents the full path.
•
Include Recovery Target Time: If you want to identify a specific point in time from which recovery be captured, select this option. In the text box, use the recovery_target_time='<DateAndTime>' command. For example, you might enter recovery_target_time='2019-05-30 13:22:00 PDT'.
•
Recovery Target Inclusive: If the Recovery Command file contains the recovery_target_inclusive command, select this option. This command attempts to recover all data up to the specified time. If cleared, the process recovers data to the nearest instant prior to the targeted time, which might be a specified time or the current time.
 
* 
IMPORTANT: You must use a restore command in the Recovery Command file. The other commands are optional.
6
Click OK.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating