Setting up PostgreSQL environments: An overview

There are broadly three ways in which PostgreSQL source database can be made in sync with the staging Database:

1. Delphix Initiated Backup Ingestion + PostgreSQL Streaming Replication between Source and Staging Database.

2. Customer Initiated Backup Ingestion + PostgreSQL Streaming Replication between Source and Staging Database.

3. Customer Initiated Backup Ingestion + Source Database WAL logs application on Staging Server.

Each of the above approaches are described below:

Delphix for PostgreSQL architecture with Delphix initiated backup ingestion + PostgreSQL streaming replication between source and dSource

Below is the brief architecture of Delphix Engine support for PostgreSQL database with Delphix Initiated Backup + PostgreSQL Streaming Replication between Source and Staging Database.

Block Diagram of Linking Architecture Between PostgreSQL Environments and the Delphix Initiated Backup Platform.

Delphix for PostgreSQL architecture with customer initiated backup ingestion + PostgreSQL streaming replication between source and dSource

Below is the brief architecture of Delphix Engine support for the PostgreSQL database with External Backup + PostgreSQL Streaming Replication between Source and Staging Database.

Delphix for PostgreSQL architecture with customer initiated backup ingestion + WAL apply for resync between source and dSource

Below is the brief architecture of Delphix Engine support for the PostgreSQL database with External Backup + WAL application between Source and Staging Database.

Types of PostgreSQL environments

At a high level, the Delphix Engine maintains a logical representation of the source database files, from which one can provision virtual databases (VDBs). In order to link a data source and provision a VDB, the following types of environments are required:

A source environment is where the un-virtualized source database runs. The Delphix Engine uses the backup, restore, and replication features of the PostgreSQL DBMS to maintain its internal representation of the source database, to be used for provisioning VDBs. The Staging host must be able to connect to the source environment in order to orchestrate the backup, restore, and replication functionality necessary to keep its representation synchronized with the source database. The Delphix Engine is designed to have a minimal impact on the performance of the source database and the source environment.

A target environment is where virtualized databases run. PostgreSQL target environments serve two purposes:

1. Since PostgreSQL does not provide a native incremental backup API, a warm standby server (in other words, one in log-shipping mode) must be created where all database files are stored remotely on a Delphix Engine. We refer to the creation and maintenance of this staging database as validated sync. During validated sync, we retrieve data from the source, roll the staged database forward, ensure that all the components necessary for provisioning a VDB have been validated, and create a snapshot of that data state. The result of validated sync is both a TimeFlow with consistent points from which you can provision a VDB and a faster provisioning process because there is no need for any database recovery when provisioning a VDB. In order to create a staging database, you must designate a target environment for this task when linking a dSource. During the linking process, database files are exported over the network to the target environment, where the staging database instance runs as a warm standby server. A target environment that hosts one or more staging databases is referred to as a staging target for validated sync.

2. Once a staging database has been set up, you can provision virtual databases from any of the discreet snapshots along the TimeFlow mentioned above to any compatible target environment (for more information, see Requirements for PostgreSQL hosts and databases). Database files are exported over the network to the target environment, where the virtual database instance runs.

Workflow for PostgreSQL environments

Prior to provisioning a virtual database, a compatible target environment must be added to the Delphix Engine. This may be the same target environment as that used for the staging instance, or it may be a different target environment.

Once an environment is added to the Delphix Engine, environment discovery takes place. Environment discovery is the process of enumerating PostgreSQL installations and configurations when a source or target environment is added to the Delphix Engine. The discovery process is repeated during environment refresh in order to detect new PostgreSQL installations and clusters.