Requirements for source databases

The value of wal_level must be set to replica (or archive).

Add an entry to the postgresql.conf file to set the wal_level value. 

This is required to have enough transactional information necessary for the staging PostgreSQL instance to support point-in-time recovery.

wal_level = 'replica'     # Default is minimal

(Optional) The archive_mode is set to on and the archive_command should be defined.

Add entries to the postgresql.conf file to set the archive_mode or archive_command values.

Enabling archiving is necessary so that WAL files can be shared with the staging environment. If the archive_mode is set to on, the completed WAL segments are sent to archive storage by setting archive_command.

archive_mode = 'on'  # Enable Archiving
# Ex: Copy completed WAL files to /u01/archive directory
archive_command = 'cp %p /u01/archive/%f' 

The source PostgreSQL cluster must not be in a shutdown state.

Configure the source database to be in a read-write,  read-only, or standby mode (streaming site).

PostgreSQL role with the REPLICATION attribute. 

This linking method streams transaction logs from the primary (aka source) to the standby (aka staging) instance.  

CREATE USER delphix WITH REPLICATION ENCRYPTED PASSWORD 'password'; 

This role’s credentials will be provided during the linking configuration process as the Database User. 

Review the Database User documentation for further details on other platforms. 

The PostgreSQL client and replication connection to the source PostgreSQL instance from the staging environment must be allowed. 

Add an entry to the pg_hba.conf file of the source PostgreSQL instance to allow replication connections from the staging environment.

host all <role> <ip-address_of_staging_target>/0 <auth-method>host replication <role> <ip-address_of_staging_target>/0 <auth-method>

The default location of the pg_hba.conf file is the data directory. However, you may modify the location of this file in the postgresql.conf file.

The <auth-method> can be set to trust, scram-sha-256, md5, etc. on the source, as allowed by PostgreSQL. For more information on how to configure pg_hba.conf, refer to Client authentication. 

The value of wal_level must be set to replica (or archive).

Add an entry to the postgresql.conf file to set the wal_level value. 

This is required to have enough transactional information necessary for the staging PostgreSQL instance to support point-in-time recovery.

wal_level = 'replica'     # Default is minimal

The minimum value of max_wal_senders must be set to 2.

Add an entry to the postgresql.conf file to set the max_wal_senders value.

The PostgreSQL connector triggers a pg_basebackup of the source PostgreSQL cluster and also sets up streaming replication, so a minimum value of “2” is required. Increase the value by 2 for each additional standby. 

max_wal_senders = 2    # Existing value plus (+) 2 for each standby, default is 0

The value of the wal_keep_size (known as wal_keep_segments in PostgreSQL 12 and lower) parameter should be large enough to support the WAL sync process.

Add an entry to the postgresql.conf file to set the wal_keep_size or wal_keep_segments values.

If using streaming replication without file-based continuous archiving, the server might recycle old WAL segments before the standby has received them. If it occurs, the standby will need to be resynchronized with a new base backup. 

To avoid this, you must set the wal_keep_size to a value large enough to ensure that WAL segments are not recycled too early, or by configuring a replication slot for the standby. 

Example: PostgreSQL 13 and greater

wal_keep_size = 20GB

Example: PostgreSQL 12 and lower

# Equivalent to 20GB of WAL files
wal_keep_segments = 1280 # In mb
wal_segment_size = 16 # Default value in mb

The value for wal_keep_segments can be estimated as the average number of new WAL segments per minute in the pg_wal directory, multiplied by the number of minutes across which protection is required. Therefore, wal_keep_size = wal_keep_segments * wal_segment_size.

(Optional) The archive_mode is set to on and the archive_command should be defined.

Add entries to the postgresql.conf file to set the archive_mode or archive_command values.

Enabling archiving is necessary so that WAL files can be shared with the staging environment. If the archive_mode is set to on, the completed WAL segments are sent to archive storage by setting archive_command. 

archive_mode = 'on'  # Enable Archiving
# Ex: Copy completed WAL files to /u01/archive directory
archive_command = 'cp %p /u01/archive/%f'

The source PostgreSQL cluster must not be in a shutdown state.

Configure the source database to be in a read-write,  read-only, or standby mode (streaming site).

PostgreSQL pg_read_all_data role 

This linking method calls PostgreSQL's pg_dumpall and pg_dump utilities upon the source database. These commands require select and read access to all tables to complete successfully.  

Access is typically granted using a pg_read_all_data role.  

Alternatively, a SUPERUSER role is good for early or test virtualization deployments.  

This role’s credentials will be provided during the linking configuration process as the Database User.  

Review the Database User documentation for further details on other platforms, such as AWS and Azure, and various examples.  

The PostgreSQL client connection to the source PostgreSQL instance from the staging environment must be allowed. 

Add entries to the pg_hba.conf file of the source PostgreSQL instance to allow connections from the staging environment.

1. Connection to the database (single_db) that we are taking the pg_dump off.

2. Connection to the postgres database, if it doesn’t exist then connection to the template1 database for pg_dumpall.

host <single_db> <role> <ip-address_of_staging_target>/0 <auth-method>host <postgres/template1> <role> <ip-address_of_staging_target>/0 <auth-method>

The default location of the pg_hba.conf file is the data directory. However, you may modify the location of this file in the postgresql.conf file.

The <auth-method> can be set to trust, scram-sha-256, md5, etc. on the source, as allowed by PostgreSQL. For more information on how to configure pg_hba.conf, refer to Client authentication.