PostgreSQL streaming replication with externally-initiated backup | Ecosystem Hub

This ingestion method uses an external backup plus PostgreSQL streaming replication between the source and staging databases 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)

Providing the pg_basebackup full backup

In 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:

Backup in Organized Folder Structure: (highest precedence)

The backup files are placed within organized folders inside the PostgreSQL Backup File Path, each folder named PostgreSQL_T<YYYYMMDDhhmmss>. During dSource creation or resync, the backup from the folder with the latest timestamp is selected automatically. The backup files inside the folder must not be zipped.
1. Create the backup folder on the staging server:
```
DLPXPGBACKUPDIR=PostgreSQL_T$(date "+%Y%m%d%H%M%S")
mkdir -p <backup_path>/$DLPXPGBACKUPDIR
```
2. Take the source backup using pg_basebackup:
  
  For PostgreSQL versions 9.4.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>
```
Backup Stored Directly in the Backup Path: The backup files are stored directly inside the PostgreSQL Backup File Path (for example, /var/tmp/backup). Always redirect the pg_basebackup output to an empty directory first, and then move it to <backup_path>.
1. For PostgreSQL versions 9.4.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 the files:
```
mv <path_to_empty_directory>/* <backup_path>
```
Backup as a ZIP File: (lowest precedence)

The backup files are compressed into a single ZIP file named PostgreSQL_T<YYYYMMDD>.zip and placed directly inside the PostgreSQL Backup File Path. The backup files must be at the root level of the ZIP file (not in subfolders), and the archive must not be double-zipped.
1. Take the source backup using pg_basebackup:
  
  For PostgreSQL versions 9.4.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. Create a ZIP file that can be moved to the staging server (with backup files at the root of the archive):
```
cd <destination_path>
# The back up 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

For 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

After you add an environment and create a source configuration, continue with the following method-specific steps in the Create and Link dSource wizard.

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.
  
  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.
Click on the +Add icon next to theExternal Backup Parameter.
Specify the PostgreSQL Backup File Path. This is the location of the backup file.
Select Enable streaming replication if WalLogs are not provided. This checkbox takes precedence over the PostgreSQL Backup WAL Log Files Path and ignores any value provided there.
Click +Add next to the Delphix Initiated Backup/External Backup - Streaming Replication Parameters option. Click the +Add button for Delphix Initiated Backup / External Backup – Streaming Replication only once. The solution supports a single backup path; providing multiple paths will cause an error during linking.
Provide values for the following fields:
- PostgresDB Replication User: Replication user on the source database.
- PostgresDB Replication User Password: Password for the replication user.
- Source Host Address: Hostname or IP address of the PostgreSQL source.
- Source Instance Port Number: Port on which the source PostgreSQL instance listens.
- Staging Instance Port Number: A port number that is free and unique on the staging instance.
Optionally, define database configuration parameters in the Config Settings section as needed. Specify a parameter name and value to update an existing parameter in postgresql.conf. To disable a parameter, specify the parameter name and select the Comment Property check box.
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.
Specify your staging environment, user, and snapshot parameters. Select the Resynchronize dSource checkbox to force a non-incremental load of data from the source. This operation is similar to creating a new dSource, but prevents duplication of storage and maintains timeflow history. Click Next to proceed.
Apply policy details to the dSource if needed, and click Next.
Select a Hook Point and then click + to add a script that you want to run at that hook point. You can define scripts to run at multiple hook points.
Review the configuration profile for your dSource and click Submit.
The Delphix Continuous Data Engine will initiate two jobs, DB_Link and DB_Sync, to create the dSource. Monitor the jobs by selecting Admin > Operations in the top menu bar. On successful completion of the DB_Link job, the database icon changes to a dSource icon under Data > Data Management > dSources.

The dataset must not be disabled after failed or canceled RESYNC job. For more information, read the Best practices for the RESYNC job topic.
After the dSource is created successfully, open the dSource Configuration tab to view details and make modifications to policies and permissions.
In the Datasets panel, select the specific dSource and navigate to the Configuration tab to view source files, data management configuration, and hook operations.

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 the streaming replication stops for any reason, a fault is generated and an error message is displayed under the Status tab. If the streaming replication encounters a fault and stops, the snapshot operation will also fail.

Last updated

Oct 9, 2026