Requirements for PostgreSQL Hosts and Databases

The Staging host must have access to a PostgreSQL role on the source side that has replication, and login privileges. This can be the built-in PostgreSQL role or a newly-created role (for example, delphix).

Creating a Role for Use with the Delphix Engine

To create a new role for use with the Delphix Engine, use the following command:

SQL> CREATE USER delphix WITH REPLICATION ENCRYPTED [ PASSWORD 'password'];

You must make the following changes to postgresql.conf:

Note: TCP/IP connectivity must be configured to allow the role mentioned above to connect to the source database from the Delphix Engine and from the standby DBMS instance set up by the Delphix Engine on the staging environment. 

listen_addresses configuration

The simplest way to configure PostgresSQL is so that it listens on all available IP interfaces:

listen_addresses = '*'    # Default is 'localhost'

max_wal_senders Configuration

The default value max_wal_senders is zero, meaning replication is disabled. In this configuration, the value of max_wal_senders must be increased to two for the Delphix Engine:

max_wal_senders = 2       # Default is 0

The value of, wal_level which determines how much information is written to the write-ahead log (WAL), must be set to archive or hot_standby to allow connections from standby servers. The value "logical" for wal_level (introduced in PostgreSQL 9.4) is also supported.

In releases prior to PostgreSQL 9.6.x, parameter 'wal_level' allowed the values 'archive' and 'hot_standby'. These values are still accepted but mapped to 'replica'.
For example,
The plugin will work with ALTER SYSTEM SET wal_level TO 'replica' and ALTER SYSTEM SET wal_level TO 'hot_standby'.

wal_level Configuration

The default value of wal_level is minimal, which writes only the information needed to recover from a crash or immediate shutdown to the WAL archives. In this configuration, you must add the logging required for WAL archiving as follows:

wal_level = archive       # Default is minimal

To configure appropriately, add the following entries to pg_hba.conf:

pg_hba.conf Configuration
host    all       <role>    <ip-address_of_delphix_engine>/32    <auth-method>

host    all       <role>    <ip-address_of_staging_target>/32    <auth-method>

host    replication    <role>    <ip-address_of_staging_target>/32    <auth-method>

Two installations of PostgreSQL are compatible if and only if:

1. They share the same vendor (for example, PostgreSQL is incompatible with EnterpriseDB Postgres Plus Advanced Server).
2. They share the same major version number (for example, 9.5.4 is compatible with 9.5, 9.5.3;  however, it is incompatible with 9.3, 9.3.24, or 9.2).
3. They are compiled against the same architecture (in other words, 32-bit and 64-bit installations of Postgres are incompatible).
4. They are compiled with the same WAL segment size. The default WAL segment size of 16 MB is rarely changed in practice, so almost all installations of PostgreSQL are compatible with each other in terms of WAL segment size.

There must be an operating system user (e.g postgres) with the following privileges:

1. The Delphix Engine must be able to make an SSH connection to the target environment using the operating system user.
2. The operating system user must have read and execute privileges on the PostgreSQL binaries installed on the target environment.
3. The operating system user must have permission to run mount and unmount as the superuser via sudo with neither a password nor a TTY.

There must be a directory on the target environment where the Delphix Engine Plugin can be installed (for example,  /var/tmp ) with the following properties:

1. The Plugin directory must be writable by the operating system user mentioned above.
2. The Plugin directory must have at least 1.5 GB  of available storage.

There must be a mount point directory (for example, /mnt) that will be used as the base for mount points that are created when provisioning a VDB with the following properties:

1. The mount point directory must be writable by the operating system user mentioned above.
2. The mount point directory should be empty.

Hostname and IP must be correctly set in /etc/hosts file, for example [postgres@source ~]$ hostname -i

The output of “hostname -i” command should produce the correct result as the IP address of the server.

For example:

[postgres@source postgres]$ hostname -i

10.110.207.113

The backup command and File should be in the below format:

PostgreSQL_T<YYYYMMDD>.zip where YYYY

denotes Year, MM denotes month, DD denotes date. For Example PostgreSQL_T20190314.zip.

The above zip file should contain a Database Full Backup from the Source created in tar format.

The below flags can be used with pg_basebackup for taking Source Database backup:

For PostgreSQL versions 9.4.x,9.5.x,9.6.x

<PATH_TO_PG_BIN>/pg_basebackup -p <PORT> -D <EMPTY_BACKUP_DIR> -F t -v -w -P -x

For PostgreSQL versions 10.x and 11.x

<PATH_TO_PG_BIN>/pg_basebackup -p <PORT> -D <EMPTY_BACKUP_DIR> -F t -v -w -P -Xs

If there's no PostgreSQL instance running on the Staging/Target host, however, we have a PostgreSQL installation, even then the plugin relies on the "DELPHIX_PG_PATH" variable to discover the environment.

In the absence of "DELPHIX_PG_PATH" variable or if the value of "DELPHIX_PG_PATH" variable is NULL then Linux "find" command will be used for the Environment discovery which may impact the overall performance. Hence, it is preferred to create this Environment Variable with correct entries.

In order to optimize the performance, it is preferred to create an Environment Variable "DELPHIX_PG_PATH" which should be accessible by OS users.

Below should be the syntax for "DELPHIX_PG_PATH" variable:

DELPHIX_PG_PATH="binary_path:data_path1;binary_path:data_path2;"

For example:

DELPHIX_PG_PATH="/usr/pgsql-9.6/bin:/var/lib/pgsql/9.6/data;/opt/edb/as9.6/bin:/opt/edb/as9.6/data;/usr/pgsql-9.6/bin:/tmp/TESING/data;"