Linking a PostgreSQL dSource

Overview of the Linking Process

The PostgreSQL plugin version 1.1.0 and above provides the following ways to create a dSource:

• dSource created with Delphix Initiated Backup -  this keeps in sync through PostgreSQL Streaming Replication
• dSource created with External Backup - there are two ways to do this:
  • keep in sync with the source through WAL files
  • keep in sync with the source through PostgreSQL Streaming Replication

The following sections provide step by steps instructions on how to do this.

Creating a dSource with Delphix Initiated Backup

Prerequisites

• The source and staging instances must meet the host requirements

• Databases must meet container requirements 

  For more information refer to Requirements for PostgreSQL Source Hosts and Databases
  

Limitation

It is not possible to access the staging server with PostgreSQL 9.4 and PostgreSQL 9.5 versions.

Procedure

1. Login to the Delphix Management application.
2. Select Manage > Environments.
3. From the Databases tab,  select a repository from which you want to create your dSource and click the  icon.
4. In the Add Database dialog window enter the Name for your source config and click Add.
5. Select your source config and click the Add dSource link located to the right.

6. In the Source tab click on the +Add icon located next to Delphix Initiated Backup Parameter. 

   The Add Button provided for Delphix Initiated Backup/External Backup - Streaming Replication is clickable multiple times but should be used ONLY once since the solution is built only to support a single backup path. Providing Multiple Paths will error out the linking process.

    The following fields are mandatory for this process :

   • • Delphix Initiated Backup Flag - Checkbox should be selected.
     • PostgresDB Replication User
     • PostgresDB Replication User Password
     • Source Host Address
     • Source Instance Port Number
     • Staging Instance Port Number - must be unique for a given instance

    

7. Optionally, database configuration parameters can be defined during the linking operation in the Config Settings section. Click Next.
8. In the dSource Configuration tab, enter a dSource name and select a group for your dSource.
   Adding a dSource to a dataset group lets you set Delphix Domain user permissions for that database and its objects, such as snapshots.
9. In the Data Management tab specify your Staging Environment, User and Mount Base, then click Next.
10. If required apply policy details to the dSource, then click Next.
11. Users can provide bash scripts as Plugin defined hooks. These hooks are executed after data ingestion but before snapshot operation, click Next.
12. Review the dSource Configuration and Data Management information in the Summary section.

13. Click Submit.

The Delphix Engine initiates two jobs to create the dSource: DB_Link and DB_Sync. You can monitor these jobs by clicking Active Jobs from the top menu bar, or by selecting System > Event Viewer. When the jobs have successfully completed, the database icon will change to a dSource icon on the Environments > Host > Databases screen, and the dSource will also appear in the list of Datasets under its assigned group.

Creating a dSource with External Backups using WAL Logs

The following procedure provides details on creating a dSource with External Backup which uses WAL logs to keep in sync with the source.

Prerequisites

• The source and staging instances must meet the host requirements

• Databases must meet container requirements 

  For more information refer to Requirements for PostgreSQL Source Hosts and Databases

• PostgreSQL Backup Files Path  - must be accessible by the OS user

• PostgreSQL Backup WAL Log Files Path - must be accessible by the OS user
  

Limitation

It is not possible to access the staging server with PostgreSQL 9.4 and PostgreSQL 9.5 versions.

Procedure

1. Login to the Delphix Management application.
2. Select Manage > Environments.
3. From the Databases tab,  select a repository from which you want to create your dSource and click the  icon.
4. In the Add Database dialog window enter the Name for your source config and click Add.
5. Select your source config and click the Add dSource link located to the right.

6. In the Source tab click on the +Add icon located next to Delphix Initiated Backup Parameter. 

   The Add Button provided for External Backup is clickable multiple times but should be used ONLY once since the solution is built only to support a single backup path. Providing Multiple Paths will error out the linking process.

   
   

   • PostgreSQL Backup Files Path - the location of the backup file
     The command below is the pg_basebackup command to be used for taking backups of the PostgreSQL Database:
     • For PostgreSQL versions 9.4.x,9.5.x,9.6.x 
       <path to bin>/pg_basebackup -p<port number> -D<data dir location> -F t -v -w -P -x
     • For PostgreSQL versions 10.x and 11.x
       <path to bin>/pg_basebackup -p<port number> -D<data dir location> -F t -v -w -P -Xs

   • PostgreSQL Backup WAL Log Files Path - the location of the WAL log files 

      Once the dSource is up using the Full External Backup provided on the staging environment, the dSource will continue to run in recovery mode and will wait for the next  expected WAL file under the specified PostgreSQL backup WAL log Files path.
     When the expected WAL files are placed on this path, the dSource applies this WAL file and starts waiting for the next expected WAL files in series. If the WAL file provided is invalid or not in the sequence, then the dSource will shut down (INACTIVE) and will ONLY throw an error on UI with the information of expected WAL filename when next dSource snapshot is taken.
     In the case of a WAL chain break, users need to either resynchronize the dSource or provide the missing WAL file, then disable and enable the dSource database
   • Staging Instance Port Number  - must be unique for a given instance.
   
   
7. Optionally, users can set the database configuration parameters during the linking operation in the Config Settings section. Click Next.
8. In the dSource Configuration tab, enter a dSource name and select a group for your dSource.
   Adding a dSource to a dataset group lets you set Delphix Domain user permissions for that database and its objects, such as snapshots.
   
9. In the Data Management tab specify your Staging Environment, User and Mount Base, then click Next.
10. If required apply policy details to the dSource, then click Next.
11. Users can provide bash scripts as Plugin defined hooks. These hooks are executed after data ingestion but before snapshot operation, click Next.
12. Review the dSource Configuration and Data Management information in the Summary section.

13. Click Submit.

    The dSource Configuration Screen

    After you have created a dSource, the dSource Configuration tab allows you to view information about it and make modifications to its policies and permissions. In the Datasets panel, select the dSource you wish to examine. You can now choose the configuration tab to see information such as the Source files, Data Management configuration, and Hook Operations. For more information, refer to Advanced Data Management Settings for PostgreSQL Data Sources.

Creating a dSource with External Backups using Streaming Replication

The following procedure provides details on creating a dSource with External Backup which uses streaming replication to keep in sync with the source.

Prerequisites

• The source and staging instances must meet the host requirements

• Databases must meet container requirements 

  For more information refer to Requirements for PostgreSQL Source Hosts and Databases

• PostgreSQL Backup Files Path  - must be accessible by the OS user

Limitation

It is not possible to access the staging server with PostgreSQL 9.4 and PostgreSQL 9.5 versions.

Procedure

1. Login to the Delphix Management application.
2. Select Manage > Environments.
3. From the Databases tab,  select a repository from which you want to create your dSource and click the  icon.
4. In the Add Database dialog window enter the Name for your source config and click Add.
   
5. Select your source config and click the Add dSource link located to the right.

6. In the Source tab click on the +Add icon located next to Delphix Initiated Backup Parameter. 

   The Add Button provided for External Backup is clickable multiple times but should be used ONLY once since the solution is built only to support a single backup path. Providing Multiple Paths will error out the linking process.

   
   

   • PostgreSQL Backup Files Path - the location of the backup file 
   • dSource Creation through external backup - should be selected.
   • Delphix Initiated Backup/External Backup - Streaming Replication parameters :
     • PostgresDB Replication User
     • PostgresDB Replication User Password
     • Source Host Address
     • Source Instance Port Number
     • Staging Instance Port Number - must be unique for a given instance
   
7. Optionally, users can set the database configuration parameters during the linking operation in the Config Settings section. Click Next.
8. In the dSource Configuration tab, enter a dSource name and select a group for your dSource.
   Adding a dSource to a dataset group lets you set Delphix Domain user permissions for that database and its objects, such as snapshots.
   
9. In the Data Management tab specify your Staging Environment, User and Mount Base, then click Next.
10. If required apply policy details to the dSource, then click Next.
11. Users can provide bash scripts as Plugin defined hooks. These hooks are executed after data ingestion but before snapshot operation, click Next.
12. Review the dSource Configuration and Data Management information in the Summary section.

13. Click Submit.

The Delphix Engine initiates two jobs to create the dSource: DB_Link and DB_Sync. You can monitor these jobs by clicking Active Jobs from the top menu bar, or by selecting System > Event Viewer. When the jobs have successfully completed, the database icon will change to a dSource icon on the Environments > Host > Databases screen, and the dSource will also appear in the list of Datasets under its assigned group.

• Linking PostgreSQL Data Sources: Overview
• Advanced Data Management Settings for PostgreSQL Data Sources