PostgreSQL streaming replication with externally-initiated backup

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

Perform the following steps to create a dSource with External Backup that uses streaming replication to keep the staging WAL files 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.
The environment user or the privileged user must have the following permissions:
1. access to the Postgres commands
2. read+write+execute permissions on the unix_socket_directories configured in postgresql.conf file (The default directories are /var/run/postgresql & /tmp).
To use privilege elevation
1. You must meet the Privilege Elevation Requirements.
2. The high-privilege user must have the following permissions:
  1. read+execute permissions on the PostgreSQL Backup File Path.
  2. read permission on the backup file(s)

Starting with connector versions 3.2.0 and greater, the pg_basebackup database full backup can be provided in three ways, ranked in the order of high-to-low precedence:

The backup files are placed within the organized folder(s) inside the PostgreSQL Backup File Path named PostgreSQL_T<YYYYMMDDhhmmss>. During the dSource creation/resync operation, backups from the folder with the latest timestamp in the name will be selected.

Run the following command to create a folder(s) on the staging server:

CODE

DLPXPGBACKUPDIR=PostgreSQL_T$(date "+%Y%m%d%H%M%S")
mkdir -p <backup_path>/$DLPXPGBACKUPDIR

Run the following command to take the source backup on the staging server using pg_basebackup:

CODE

# For PostgreSQL versions 9.4.x,9.5.x,9.6.x
<path_to_bin>/pg_basebackup -D <backup_path>/$DLPXPGBACKUPDIR -F t -v -w -P -x -h <source_ip_domain_name> -p <source_port> -U <source_user>
# For PostgreSQL versions 10.x and above
<path_to_bin>/pg_basebackup -D <backup_path>/$DLPXPGBACKUPDIR -F t -v -w -P -Xs -h <source_ip_domain_name> -p <source_port> -U <source_user>

The backup files are stored directly within the PostgreSQL Backup File Path.

Make sure to redirect the pg_basebackup output to an empty directory and then move it to <backup_path>.

CODE

# For PostgreSQL versions 9.4.x,9.5.x,9.6.x
<path_to_bin>/pg_basebackup -D <path_to_empty_directory> -F t -v -w -P -x -h <source_ip_domain_name> -p <source_port> -U <source_user>
# For PostgreSQL versions 10.x and above
<path_to_bin>/pg_basebackup -D <path_to_empty_directory> -F t -v -w -P -Xs -h <source_ip_domain_name> -p <source_port> -U <source_user>
# Move files
mv <path_to_empty_directory>/* <backup_path>

The backup files are compressed and stored within a ZIP file named PostgreSQL_T<YYYYMMDD>.zip and placed within PostgreSQL Backup File Path.
1. Run the following command to take the source backup on the source server using pg_basebackup:
  CODE
```
# For PostgreSQL versions 9.4.x,9.5.x,9.6.x
<path_to_bin>/pg_basebackup -D <destination_path>  -F t -v -w -P -x
# For PostgreSQL versions 10.x and above
<path_to_bin>/pg_basebackup -D <destination_path> -F t -v -w -P -Xs
```
2. Run the following command to create a ZIP file that can be moved to the staging server (ensure that the backup files are stored at the root of the ZIP file):
  CODE
```
cd <destination_path>
# The back files should be at the root of the ZIP file
DLPXPGBACKUPZIP=PostgreSQL_T$(date +"%Y%m%d").zip
zip $DLPXPGBACKUPZIP *
```

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 PostgreSQL streaming replication with externally initiated backup ingestion.

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.
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. Click on the +Add icon next to External Backup Parameter.
4. Specify the PostgreSQL Backup File Path. This is the location of the backup file.
5. Select the 'Enable streaming replication if WalLogs are not provided' checkbox.
  - This checkbox will take precedence over the PostgreSQL Backup WAL Log Files Path and ignore any value provided.

f. Click on the +Add icon next to the Delphix Initiated Backup/External Backup - Streaming Replication Parameters option.
Note: You must click the “Add Button” for Delphix Initiated Backup/External Backup - Streaming Replication only once as the solution supports a single backup path. Providing multiple paths will result in an error during the linking process.

g. Provide the values in the following fields.

i. PostgresDB Replication User

ii. PostgresDB Replication User Password

iii. Source Host Address

iv. Source Instance Port Number

v. Specify the Staging Instance Port Number. You must provide a port number that is available for the staging instance.

vi. Optionally, you can define database configuration parameters during the linking operation in the "Config Settings" section if needed.

Note: During dSource creation, you now 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 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.

vii. 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 the progress of these jobs by clicking Active Jobs from the top menu bar, or by navigating to System > Event Viewer. Upon successful completion of the 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 PostgreSQL streaming replication with Externally Initiated backup, the 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.