Skip to main content
Skip table of contents

Requirements for PostgreSQL hosts and databases

In order to begin using PostgreSQL environments with Delphix, you will need to configure the source and target with the requirements described on this page.

PostgreSQL hosts and databases 

On each host with Postgres, there must be an operating system user configured to the required specifications for Delphix, as explained in the table below. These requirements apply to both source and target environments. However, target environments have additional requirements which are detailed in the Target Host Requirements section below.

The PostgreSQL plugin requires the use of the default user which gets created during the Postgres DB installation process. Usually, 'postgres'. In case you want to use a low-privileged OS user to add the environment and create datasets with the same, make sure that the additional requirements for Privilege elevation are met, see ​Prerequisites for Privilege Elevation using DLPX_DB_EXEC Script​​​

Source database requirements

Database Requirement

Explanation

The source database can be in READ-WRITE or READ-only mode. It can be a standby database(streaming site) as well.


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:

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

You must make the following changes to postgresql.conf:

modifying the parameter listen_addresses, which specifies the TCP/IP addresses on which the DBMS is to listen for connections from client applications.

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:

CODE
listen_addresses = '*'    # Default is 'localhost'

(for more information, see the Server Configuration chapter in the PostgreSQL documentation)

The value of max_wal_senders, which specifies the maximum number of concurrent connections from standby servers or streaming base backup clients, must be increased from its desired value by two. That is, in addition to the allowance of connections for consumers other than the Delphix Engine, there must be an allowance for two additional connections from consumers set up by the Delphix Engine.

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:

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

CODE
wal_level = archive       # Default is minimal

You must configure PostgreSQL to allow PostgreSQL client connections and replication client connections from the staging target environment.

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

CODE
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>

<auth-method> can be set to trust, scram-sha-256, or md5 on source. Delphix inherits the same authentication method from the source while dSource creation and VDB provisioning. For more information on how to configure, pg_hba.conf see the Client Authentication section in the PostgreSQL documentation.


wal_keep_segments parameter in postgresql.conf file on the Source environment should be large enough to support the WAL sync process once the dSource is building up.

Postgres 13 onwards, the configuration parameter wal_keep_segments is changed to wal_keep_size. It is specified in megabytes rather than the number of files as with the wal_keep_segments parameter. If you previously used wal_keep_segments, the following formula will give you an approximate equivalent setting:
wal_keep_size = wal_keep_segments * wal_segment_size (typically 16MB).


It is mandatory to have "port" (can be commented or not commented) in postgresql.conf file.


Target Host Requirements

Host Requirement

Explanation

The operating system and architecture of the target environment must match those of the source environment. 

It is recommended that the source and the target environments should be identical and hardware configurations should match.

Compatible Operating System supported by the Plugin is CentOS 7.3/7.4/7.5/7.6/7.7/7.8/7.9 and RHEL 7.3/7.4/7.5/7.6/7.7/7.8/7.9/8.3/8.6.

The underlying Operating System for both the Source and Staging environment should be among these versions.

There must be an installation of PostgreSQL on the target environment that is compatible with the installation of PostgreSQL on the source environment.

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.

See Sudo Privilege Requirements for PostgreSQL Environments for further explanation of the commands, and Sudo File Configuration Examples for PostgreSQL Environments for examples of the /etc/sudoers file on different operating systems.

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.


TCP/IP connectivity to and from the source environment must be configured as described in General Network and Connectivity Requirements.


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:

CODE
[postgres@source postgres]$ hostname -i
10.110.207.113

When using the External Backup method for ingestion the directories being used for keeping Backup file and WAL file should be accessible by OS User.


Ensure that either the netstat or ss utility is installed on the Staging/Target Host.

Checking if netstat utility is installed:

CODE
which netstat

Checking if ss utility is installed:

CODE
which ss

Zip must be installed on the Staging/Target Host for External Backup Support.

Checking if zip utility is installed:

CODE
which zip # or
which unzip

From PostgreSQL Plugin 3.2.0 onwards, the backups can be provided in three ways:

1. Database full Backup in tar format can be placed inside organized folders named PostgreSQL_T<YYYYMMDDhhmmss>, created within the backup path.

For example, PostgreSQL_T20230224033939.

2.   Database full Backup in tar format can be directly placed within the backup path. 3. Providing the full backup encapsulated in a ZIP file in the format PostgreSQL_T<YYYYMMDD>.zip (supported by previous plugin versions). Make sure that the backup files are available directly at the root level of the ZIP.

  1. Providing the full backup encapsulated in a ZIP file in the format
    PostgreSQL_T<YYYYMMDD>.zip
    (supported by previous plugin versions).Make sure that the backup files are available directly at the root level of the ZIP.

For example, PostgreSQL_T20230224.zip.

Precedence Rules:

  • When all types of backups are provided within the same backup path, the plugin will choose the organized folders over other backups. The backup stored in organized folders has the highest precedence.

  • If backups are placed directly within the backup path and the ZIP files are also present, the plugin will choose the direct backups over ZIP files.

  • The ZIP files have the least precedence in the hierarchy of all backup types.

Note: YYYY denotes year, MM denotes month, DD denotes date, hh denotes hours, mm denotes minutes and ss denotes seconds.


Useful commands:

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

# For method 2 & 3
DLPXPGBACKUPDIR=<path_to_empty_directory>
mkdir -p $DLPXPGBACKUPDIR

# For PostgreSQL versions 9.4.x,9.5.x,9.6.x
<path_to_bin>/pg_basebackup -D $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 $DLPXPGBACKUPDIR -F t -v -w -P -Xs [-h <source_ip_domain_name>] [-p <source_port>] [-U <source_user>]

# For method 3, zipping the backup
cd $DLPXPGBACKUPDIR
DLPXPGBACKUPZIP=PostgreSQL_T$(date +"%Y%m%d").zip
zip $DLPXPGBACKUPZIP *

Checking if backups are available at the root of the ZIP file:

CODE
# As an example
# The backup files are available at the root of the ZIP file
$ zipinfo PostgreSQL_T20230224.zip 
Archive:  PostgreSQL_T20230224.zip
Zip file size: 6883130 bytes, number of entries: 5
-rw-------  3.0 unx  9093632 bx defN 23-Feb-24 02:12 24578.tar
-rw-------  3.0 unx  9093632 bx defN 23-Feb-24 02:12 24579.tar
-rw-------  3.0 unx  9093632 bx defN 23-Feb-24 02:12 24580.tar
-rw-------  3.0 unx 26959360 bx defN 23-Feb-24 02:12 base.tar
-rw-------  3.0 unx 16778752 bx defN 23-Feb-24 02:12 pg_wal.tar
5 files, 71019008 bytes uncompressed, 6882378 bytes compressed:  90.3%

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:

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

For example:

CODE
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;"

The variable "DELPHIX_PG_PATH" must be available to the environment user in a non-interactive way. You can test this variable for non-interactive logins using ssh <your_username>@<target_host> "env | grep DELPHIX_PG_PATH".

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.