Chat now with support
Chat with Support

NetVault Plug-in for PostgreSQL 12.3.1 - 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

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, With PostgreSQL versions 11 and earlier, PostgreSQL requires that you use the recovery.conf file name and that the file is located in the database cluster Data directory. With PostgreSQL 12 and later, for recovery to take place, PostgreSQL requires a file named recovery.signal to be present in the database cluster Data directory.
 
* 
IMPORTANT: For PostgreSQL versions 12 and later, when you select Recovery in Plug‑in for PostgreSQL Restore options, recovery.signal file will be placed in the PostgreSQL database cluster Data directory. The recovery.signal file indicates to PostgreSQL Server that recovery has been requested. NetVault Backup writes to recovery.signal file the pertinent recovery commands. PostgreSQL versions 12 and later require the recovery commands to be present in PostgreSQL postgresql.conf file. When you configure Plug‑in for PostgreSQL, if you select the option Allow File Level Data Copy Recovery to Modify postgresql.conf file. then the plug-in appends the recovery commands in recovery.signal to the postgresql.conf file. If you do not select Allow File Level Data Copy Recovery to Modify postgresql.conf file, then during recovery, before restarting the PostgreSQL Server, ensure to manually copy the contents of recovery.signal into the postgresql.conf file.
•
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.

Finalizing and submitting the job

The final steps include setting additional options on the Schedule, Source Options, and Advanced Options pages, submitting the job, and monitoring the progress through the Job Status and View Logs pages. These pages and options are common to all NetVault Backup Plug-ins. For more information, see the Quest NetVault Backup Administrator’s Guide.

1
To save the settings, click Ok, and then click Next.
2
In Job Name, specify a name for the job if you do not want to use the default setting.
Assign a descriptive name that lets you easily identify the job when monitoring its progress. The job name can contain alphanumeric and nonalphanumeric characters, but it cannot contain non-Latin characters. On Linux, the name can have a maximum of 200 characters. On Windows, there is no length restriction. However, a maximum of 40 characters is recommended on all platforms.
3
In the Target Client list, select the machine on which you want to restore the data.
 
* 
TIP: You can also click Choose, and then locate and select the applicable client in the Choose the Target Client dialog box.
4
Use the Schedule, Source Options, and Advanced Options lists to configure any additional required options.
5
Click Save or Save & Submit, whichever is applicable.
You can monitor progress on the Job Status page and view the logs on the View Logs page. For more information, see the Quest NetVault Backup Administrator’s Guide.
6
If you restored a file-level data copy as a part of a recovery procedure intended to build or rebuild the database cluster Data directory, complete the following steps to ensure that recovery is completed.
a
Restart the PostgreSQL service.
b
After the PostgreSQL service has restarted, run the following command:
select pg_wal_replay_resume();
If the recovery has completed, a recovery is not in progress message is displayed. Otherwise, recovery is completed.
For more information, see the example in Example of restoring an archived-based backup sequence
Example of restoring an archived-based backup sequence

For PostgreSQL versions 11 and earlier, in the standard recovery scenario, you restore the Base Backup to the original location, and then restore the sequence of Incremental Backups to the staging directory for WAL files. The next step is to generate a Recovery Command file, “recovery.conf,” and place it in the database cluster Data directory. For the final step, you start the PostgreSQL Server, which then reads the Recovery Command file and applies the recovery.

PostgreSQL versions 12 and later do not use the recovery.conf file. In its place a file named recovery.signal may be placed in the PostgreSQL database cluster Data directory, to indicate that recovery is requested. In addition, for PostgreSQL 12 onward, the recovery commands are placed in the PostgreSQL postgresql.conf file.

For PostgreSQL versions 12 and later, in the standard recovery scenario, you restore the Base Backup to the original location, and then restore the sequence of Incremental Backups to the staging directory for WAL files. The next step is to generate the set of recovery commands and write them in the postgresql.conf file. Then, in the PostgreSQL database cluster Data directory, place a file named recovery.signal to indicate that recovery is requested. For the final step, you start the PostgreSQL Server, which then checks for the recovery.signal file, and if present then reads the recovery commands from the postgresql.conf file, and applies the recovery.

When you configured Plug‑in for PostgreSQL, if you selected the option Allow File Level Data Copy Recovery to Modify postgresql.conf file, then the plug-in appended the recovery commands in recovery.signal to the postgresql.conf file automatically. If you did not select Allow File Level Data Copy Recovery to Modify postgresql.conf file, then during recovery, before restarting the PostgreSQL Server, ensure to manually copy the contents of recovery.signal into postgresql.conf file.

 
* 
IMPORTANT: If selected, Plug‑in for PostgreSQL places the recovery commands at the end of the postgresql.conf file. After recovery, the recovery commands placed in postgresql.conf file can be removed.

 

 
* 
NOTE: PostgreSQL Server might replay additional transactions beyond the selected stop time. If you prefer that the recovery only has access to the WAL file transactions that were included in the sequence of Base and Incremental Backups, before you start the recovery process, delete the contents of the PostgreSQL Database Cluster pg_wal or pg_xlog directory and the contents of the staging directory for WAL files. Transactions committed after the last Base or Incremental Backup of the restore sequence are not replayed.

If you prefer to achieve a rollforward up to the latest available consecutive transaction, in addition to excluding a stop time, keep the existing contents in the pg_wal or pg_xlog directory and in the staging directory for WAL files.

The following outlines the steps involved in restoring the Base and Incremental Backups for a file-based Base Backup and associated Incremental Backups.

1
Make sure that all PostgreSQL Server services are stopped.
2
Generate an offline backup to savage the contents of the database cluster Data directory and the contents of the WAL files directory.
When creating the offline backup job, select the Perform Offline Backup option on the PostgreSQL Backup Options tab. For more information, see Setting backup options for a File Level Data Copy.
3
Create a restore job, and select the applicable Base File Level Data Copy saveset to restore.
If the files in the database cluster Data directory are damaged, or you intend to replace them, restore the Base Backup to the original location. To do so, on the Restore Options tab, clear the Relocate all restored files to the directory option. On the Base Data Files tab, select the Include Backup Label File with Restore option.
For more information, see Selecting data for a restore and Setting restore options for a File-Level Data Copy.
 
* 

If the pg_wal or pg_xlog directory is empty and you want to store the current WAL files in a separate filesystem, you can modify pg_wal or pg_xlog to be a symbolic link to the location of your choice. To do so, delete pg_wal or pg_xlog and replace it with a symbolic link.

Examples of creating the link:

Linux or UNIX: ln -s /pg_wal_desired_location /opt/postgres/11/data/pg_wal

Windows: mklink /J "C:\Program Files\PostgreSQL\11\data\pg_wal" "Q:\pg_wal_desired_location"

4
Create a restore job, and select the first Incremental File Level Data Copy saveset to restore to the staging directory.
5
Repeat the previous step for each Incremental File Level Data Copy saveset to restore.
6
For the last Incremental File Level Data Copy saveset that you restore, select the Include Recover Command File with Restore option, and any other applicable options, on the Point in Time Recovery tab.
7
For PostgreSQL versions 12 and later, if you did not configure Plug‑in for PostgreSQL with the option Allow File Level Data Copy Recovery to Modify postgresql.conf file, then append the contents of the recovery.signal file to the PostgreSQL postgresql.conf file.
8
Restart the PostgreSQL service.
9
After the PostgreSQL service has restarted, use the PostgreSQL psql tool to run the following command and complete the recovery:
select pg_wal_replay_resume();
Example:
bash$ psql
postgres=# select pg_wal_replay_resume();
pg_wal_replay_resume
----------------------
(1 row)
postgres=#

Performing advanced restore procedures

You can complete the following advanced procedures:

•
Restoring Global Objects
•
Restoring configuration files
•
Renaming a database during restore
•
Restoring data to a different database cluster
•
Restoring to a database cluster on an alternate server

Restoring Global Objects

You can restore Global Objects from a Plain-Text SQL Script File version of a Full Database Cluster or Global Objects Only backup.

1
Select the Global Objects node from the backup saveset by following the instructions outlined in Selecting data for a restore.
2
Set the Restore Options by following the instructions outlined in Setting restore options and completing the following parameters:
•
Stop on Error: Select this option if you want the restore to stop immediately when an error occurs.
•
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.
•
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.”
 
* 
IMPORTANT: PostgreSQL does not allow detailed restores of individual Global Objects, nor does it allow you to delete global objects before restoring. Therefore, when restoring Global Objects, all the Global Objects are restored. As a result, you encounter error messages about duplicate items during restore. Quest recommends that you restore Global Objects to an empty database cluster during disaster recovery or use the Continue Restore with Warnings option. If the Global Objects are being restored to recover dropped roles or users and the Continue Restore With Warnings option has been selected, warnings are logged in the NetVault Backup binary logs for roles or users that exist.
3
Continue with the restore procedure as explained in Finalizing and submitting the job.

To restore Global Objects from a TAR Archive File or Custom Archive File backup, no additional Restore Options are available. Perform Step 1 and Step 3 to restore Global Objects from these backup formats.

For restoring the data to a different database cluster, see Restoring data to a different database cluster.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating