PowerProtect Database Application Agent 19.13 Installation and Administration Guide

PostgreSQL backup considerations

Orchestrated Application Protection provides the PostgreSQL database backup operations through the database application agent program ddbmoappbackup, which leverages the native PostgreSQL backup utilities.

The full and transaction log backups are mapped to specific types of PostgreSQL backups, as shown in the following table. You must specify the correct backup level when you configure a PostgreSQL backup.

Table 1. Mapping of backup level to PostgreSQL backups
Backup level	Type of PostgreSQL backup
Full backup (full)	Dump or base backup
Transaction log backup (txnlog)	WAL archiving or archive backup

Ensure that you review the topic Orchestrated Application Protection backup considerations. All the backup considerations from that topic also apply to the configuration of PostgreSQL backups.

The following PostgreSQL backup topics require specific considerations:

PostgreSQL backup shell script
PostgreSQL full backup
PostgreSQL transaction log backup
Database authentication
Registering the PostgreSQL archive command
Completing the PostgreSQL backup configuration

PostgreSQL full and transaction log backups provides considerations for the supported levels of database application agent PostgreSQL backups.

PostgreSQL backup shell script

Select the correct PostgreSQL backup utility to include in the backup shell script. PostgreSQL provides two types of backup utilities:

The pg_dump and pg_dumpall utilities can work with the WAL archiving disabled environment. If you will not perform a WAL backup, use pg_dump or pg_dumpall as the full backup utility.
The pg_basebackup utility can work with the WAL archiving enabled environment. If you will perform a WAL backup, use pg_basebackup as the full backup utility.
NOTE:If you want to use the data that is backed up by the pg_basebackup utility directly without consulting the WAL archiving, you must use the pg_basebackup command with the -X option.

The PostgreSQL database online documentation provides more details about using the backup utilities pg_dump, pg_dumpall, and pg_basebackup, and details about WAL archiving.

You can create the backup shell script for the full level backup. To enable the backup of the WAL segment file, PostgreSQL requires you to register the ddbmoappbackup command in the postgresql.conf file. Registering the PostgreSQL archive command provides more details.

PostgreSQL full backup

Select the correct backup utility for the corresponding PostgreSQL environment setting, and create the full backup shell script file to use with that utility to perform the dump or base backup.

You must specify the backup shell script pathname in the FULL subsection of the BACKUP section in the configuration file. You must specify the full backup level by using the -l full option.

Orchestrated Application Protection backup considerations provides more details.

PostgreSQL transaction log backup

To enable the PostgreSQL WAL archiving, you must register the ddbmoappbackup program with its required command line options through the archive_command setting in the postgresql.conf file.

The transaction log backup (with backup level txnlog) can share the same configuration file with a full backup because the parameters DATABASE_TYPE, INSTANCE_NAME, and BACKUP_NAME have same values for both levels of backup.

For example, the postgresql.conf file includes the following command settings for the transaction log backups:

archive_command = '/opt/dpsapps/dbappagent/bin/ddbmoappbackup -o pg_p_opt="%p" -o pg_f_opt="%f" -l txnlog -z configuration_file_path'

NOTE:

Ensure that the PostgreSQL operations are inside the transactions.

The backup behavior of PostgreSQL WAL archiving is predefined by the ddbmoappbackup program. You do not need to specify a backup shell script for this type of backup.

The PostgreSQL server schedules the PostgreSQL WAL archiving. In the online PostgreSQL document at www.postgresql.org, the "Continuous Archiving and Point-in-Time Recovery (PITR)" section in the "Backup and Restore" chapter and the "Write Ahead Log" section in the "Server Configuration" chapter provide more details about how to configure the WAL archiving and schedule.

Database authentication

If you enabled authentication, the backup utility requires the username and password to connect to the database when the backup starts. Orchestrated Application Protection supports the storage of the encrypted password in the configuration file and the password transfer to the backup utility when Orchestrated Application Protection receives the password prompt.

Add the password in the configuration file by running the ddbmadmin command. Then run the backup command that you plan to include in the backup script file, to obtain the password prompt string. For example, run the following command at the command line:

/usr/bin/pg_basebackup -h localhost --pgdata=/tmp/basebackup29
Password:

Here, "Password:" is the password prompt string. In the configuration file, set the USER_PSWD_PROMPT parameter to this password prompt string. Orchestrated Application Protection parameters provides details on setting the backup parameters in the configuration file.