Provisioning a PostgreSQL virtual database Point-in-Time (PiT) with external logs
Starting with PostgreSQL connector version 4.0.0 and later, you can leverage Point-in-Time recovery to provision a VDB by providing the WAL files on the target environment. PostgreSQL allows you to perform a Point-in-Time (PiT) recovery by replaying archived WAL files from a base backup. PiT recovery relies on three components.
Base Backup: This represents the file system backup, commonly referred to as a dataset snapshot.
Archived WAL Files: These are the archived Write-Ahead Logging (WAL) files that contain all transaction changes.
Recovery Target Timestamp: The specific timestamp at which you intend to recover your database. This value is greater than the backup’s timestamp and has been recorded by the provided WAL files.
For more information about WAL file archiving and Point-in-Time (PiT) Recovery, refer to the PostgreSQL Documentation.
Prerequisites
Ensure that you meet the following requirements before you begin creating a dSource.
Create a dSource snapshot from one of the following ingestion methods:
Other snapshot sources are not supported, such as VDBs, Staging Push ingestion dSources, or Delphix Initiated Single Database backup ingestion dSources.
Set up a PostgreSQL target environment that satisfies all the necessary requirements described in PostgreSQL support and requirements.
The target environment must have the required WAL Logs (at least from the REDO WAL Log captured in the snapshot metadata) to perform point-in-time recovery. For more information, refer to Copy WAL logs to the target environment.
Identify a target timestamp value with the following criteria:
Greater than the checkpoint timestamp available in the dSource’s snapshot metadata.
Recorded by the WAL files.
Follows ISO 8601 basic format.
For more information, refer to Understanding the target timestamp value.
To use privilege elevation
You must meet the Privilege Elevation Requirements.
The high-privilege user must have the following permissions:
access to the directory that contains the WAL logs and read+execute permissions on the same.
read permission on individual WAL Logs.
If the VDB PiT recovery is configured to use the same Write-Ahead Logging (WAL) path as provided to the dSource created using Log Shipping with Externally initiated Backup (in connector versions below 4.0.0), the provisioning of the VDB PiT may fail. This failure can manifest with the error message "cp: cannot stat," which can be observed in the PostgreSQL logs. The reason for the error is that during the restore process, the dSource renames the WAL Logs with a ".done" suffix (e.g., 000000010000000000000020.done). To prevent this error from occurring, it is advisable to proactively execute the Resynchronize dSource operation on the dSource and then provision the VDB PiT.
Procedure
Perform the following steps to perform a PiT recovery.
Login to the Delphix Management application.
Navigate to the Manage > Datasets page.
Select the VDB PiT supported dSource and the snapshot that contains the data you want to provision from. Click the provision VDB icon.
On the Target Environment page, perform the following actions:
From the Environment dropdown list, select a target environment. This will dynamically populate the other properties.
From the Installation dropdown list, select the specific PostgreSQL installation that you want to use.
From the User dropdown list, select the environment user that matches the PostgreSQL Instance Owner as configured in the prerequisites.
Note: The ability to select different instance owners is only applicable when you have multiple users configured in that environment.
On the Target Configuration page, perform the following actions:
Specify the target environment’s NFS Mount Location. The specified path must not exist or be empty.
(Optional) Enable Privilege Elevation by providing the username of the Privileged OS Account.
Specify the Virtual Postgres Port Number of the target database.
Select the VDB PiT with External Logs Details section’s + Add button and provide the following:
Note: Users must click the “Add Button” for VDB PiT with External Logs details only once as the solution supports a single backup path. Providing multiple paths will result in an error during the linking process.Path to WAL Logs for PiT: Provide an absolute path to the archived WAL files available on the target environment.
PiT timestamp: Provide the target timestamp.
Optionally, you can define database configuration parameters during the linking operation in the "Config Settings" section if needed.
Note: During dSource creation, you have the capability to configure the parameters and their values in the postgresql.conf file using the "Config Settings" section in the UI. For example, if you wish to disable SSL via UI, you can specify the "ssl" parameter and its value through the UI. This action will update the existing parameter value in the postgresql.conf file with the value provided.
Similarly, if you want to disable a parameter then you can specify the parameter name and then select the Comment Property check box. This action will comment out the parameter in the postgresql.conf configuration file.
Click Next.
On the Configuration page, provide the name of the PostgreSQL VDB that will be displayed on the UI.
Select a Target Group for the VDB to select an existing group or click the green Plus icon to add a new group, if necessary.
In the Policies tab, apply policy details to the dSource if needed, and then click Next to continue.
In the Hooks tab, select a Hook Point and then click + to add a script that you want to run at that hook point. You have the flexibility to define scripts to run at multiple hook points in the process.
In the Summary tab, review the configuration profile for your dSource.
Click Submit. The VDB provisioning must complete and create a new VDB timeflow.
For more information on VDB Database permissions, refer to Database authentication for PostgreSQL datasets.
After a successful VDB provision, two snapshot metadata values can be used to identify the operation’s results:
User-Provided Recovery Timestamp. This timestamp records the timestamp specified by the user.
PiT recovery Restore Timestamp. This timestamp records the timestamp of the last committed transaction that the recovery process has reached up to.
Understanding the target timestamp value
In PostgreSQL, a recovery point is defined by the transaction commit timestamp. During the recovery process, the database is restored to a state where the commit timestamp is less than or equal to the provided target time. Follow the requirements above in the Prerequisite section and the additional following details to ensure a successful provision.
ISO 8601 string format
The connector accepts timestamps in ISO 8601 basic format. The permitted timestamp formats can be summarized as follows:
Timezone inherited from the postgresql.conf file:
YYYY-mm-dd HH:MM:SS
YYYY-mm-dd HH:MM:SS.ssssss
Time Zone is defined and a UTC offset:
YYYY-mm-dd HH:MM:SS(+/-)HHMM
YYYY-mm-dd HH:MM:SS.ssssss(+/-)HHMM
Here YYYY represents the year, mm represents the month, dd represents the day, HH represents the hour in 24-hour format, MM represents the minutes, SS represents the seconds and ssssss represents the microseconds. If a timezone is not specified, it will be obtained from the virtual database’s postgresql.conf file.
Additional details
Starting with PostgreSQL version 13 and higher, performing a PiT recovery will fail if there have been no transactions or activity occurring after the requested target time.
You can use pg_waldump (or pg_xlogdump for PostgreSQL 9.x versions) as a tool to analyze WAL files and identify commit timestamps.