PostgreSQL streaming replication with Delphix-initiated backup

The following diagram demonstrates the architecture of the Delphix Continuous Data Engine support for the PostgreSQL database with PostgreSQL streaming replication with Delphix-initiated backup between the source and staging database.

Perform the following steps to create a dSource using PostgreSQL streaming replication with Delphix-initiated backup to stay in sync with the source.

Prerequisites

Ensure that you meet the following requirements before you begin creating a dSource.

The environment requirements are met by the source and staging environments as described in Requirements for PostgreSQL Staging/Target environments.
To use privilege elevation, you must meet the Sudo Privilege Requirements.
The OS or privileged user must have the following permissions:
- access to the Postgres commands
- read+write+execute permissions on the unix_socket_directories configured in postgresql.conf file (The default directories are /var/run/postgresql & /tmp).

For more information, refer to Requirements and Prerequisites.

Limitation

With PostgreSQL versions 9.4 and 9.5, the connector creates the staging instance as a warm standby. This instance is not accessible because it is always in recovery mode and does not allow any connection.

Procedure

Perform the following steps to create a dSource using the externally initiated backup ingestion with the WAL log shipping method.

Login to the Delphix Management application.
Navigate to the Manage > Environments page.
On the Databases tab, choose a repository for your dSource and click the + icon.
In the Add Database dialog window, provide the name of your source configuration and click Add.

Note: Starting from PostgreSQL connector version 4.3.0 onwards, a new source config parameter Export Path for Kubernetes VDB on Delphix Engine is added. The value for this field is not to be filled manually and is only used to return the export path for any VDBs created using the Kubernetes provisioning workflow. This field is visible only in the Delphix Continuous Data Engine version below 14.0.0.0.

Select your source configuration and click the Add dSource link located on the right.
In the Source tab, do the following:
1. In the NFS Mount Location field, provide a mount location on the staging environment.
2. If using Privilege Elevation, provide the username of the Privileged OS Account.
  Note: Once the dSource is created, you will not be able to update the privileged OS account. Recreating the dSource account is the only way to make changes.
3. Select the Delphix Initiated Backup - Postgres Cluster Ingestion Flag checkbox. Enabling this checkbox allows Delphix to initiate the physical backup on the source database.
4. Click on the +Add icon next to the Delphix Initiated Backup/External Backup - Streaming Replication Parameters parameter.
  Note: You must click the “Add Button” for External Backup only once as the solution supports a single backup path. Providing multiple paths will result in an error during the linking process.
5. Provide the values in the following fields.
  1. Delphix Initiated Backup - Postgres Cluster Ingestion Flag - You must select this checkbox.
  2. PostgresDB Replication User
  3. PostgresDB Replication User Password
  4. Source Host Address
  5. Source Instance Port Number
  6. Specify the Staging Instance Port Number. This port number must be unique and the port must be free for a given instance.
  7. Optionally, you can define database configuration parameters during the linking operation in the Config Settings section if needed.
    Note: During dSource creation, you can 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 support replication slots via UI, you can specify the "primary_slot_name" 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, 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.
  8. Click Next.
In the dSource Configuration tab, provide a dSource name and then select a group for your dSource. Adding a dSource to a dataset group allows you to manage user permissions for that database and its objects, including snapshots. For more information, refer to the topics under Users and Groups.
In the Data Management tab, specify your staging environment, user, and snapshot parameters. Select the Resynchronize dSource checkbox if you want to resynchronize the dSource. This operation will force a non-incremental load of data from the source. This operation is similar to creating a new dSource, but prevents duplication of the storage requirements and maintains timeflow history. Click Next to proceed.
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 Delphix Continuous Data Engine will initiate two jobs, namely DB_Link and DB_Sync to create the dSource. You can monitor these jobs by selecting System>Jobs or by selecting System >Events in the top menu bar. Upon successful completion of the DB_link job jobs, the database icon will change to a dSource icon on the Environments > Host > Databases screen. The dSource will also be listed under Datasets within its assigned group.

In the case of a Delphix-initiated backup, the streaming replication process is continuously monitored by reading the PostgreSQL database logs every minute. If, for any reason, the streaming replication stops, a fault will be generated, and an error message will be prominently displayed under the "Status" tab. It's important to note that if the streaming replication encounters a fault and stops, the snapshot operation will also fail.