Requirements for source databases

This topic outlines the requirements for the PostgreSQL source database. The requirements are divided based on the linking methods. For more information about these parameters, refer to the Important PostgreSQL settings section.

Externally Initiated backup with WAL shipping

Source system requirements

Description

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.

CODE

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.

CODE

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

PostgreSQL streaming replication with externally and Delphix initiated backup

Source system requirements	Description
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).
The staging environment must have access to a PostgreSQL role on the source database that has replication and login privileges.	Create a new or modify an existing user to stream transaction logs from the primary to the standby instance. Avoid using the superuser. CODE `CREATE USER delphix WITH REPLICATION ENCRYPTED [ PASSWORD 'password'];`
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. CODE `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. CODE `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. CODE `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 CODE `wal_keep_size = 20GB` Example: PostgreSQL 12 and lower CODE `# 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. CODE `archive_mode = 'on' # Enable Archiving # Ex: Copy completed WAL files to /u01/archive directory archive_command = 'cp %p /u01/archive/%f'`

Delphix initiated single database backup

Source system requirements

Description

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).

The staging environment must have access to a PostgreSQL role on the source database that has privileges to run pg_dumpall/pg_dump.

That pg_dumpall/pg_dump does not operate with special permissions.

In particular, it must have read access to all tables that you want to back up, so in order to back up the entire database you almost always have to run it as a database superuser.

CODE

 CREATE ROLE delphix SUPERUSER LOGIN PASSWORD 'password';

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.

Connection to the database (single_db) that we are taking the pg_dump off.
Connection to the postgres database, if it doesn’t exist then connection to the template1 database for pg_dumpall.

CODE

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.