Table of Contents Previous Next


4 Configuration : 4.3 Configuring the Database Server : 4.3.4 Enabling WAL Archiving

For background information about WAL archiving see the PostgreSQL Core Documentation available at:
Section 4.3.4.1 describes the general, manual configuration process.
Section 4.3.4.2 describes a more automated alternative approach.
The following configuration parameters must be set in the postgresql.conf file to enable WAL archiving:
Set wal_level to replica or higher for Postgres versions.
Set archive_mode to on.
Set the archive_command to copy the WAL files to the BART backup catalog.
Set max_wal_senders to a value high enough to leave at least one session available for the backup. If the xlog_method=stream parameter setting is to be used by this database server as determined in the BART configuration file, the max_wal_senders setting must account for an additional session for the transaction log streaming (that is, the setting must be a minimum of 2). See sections 4.2 and 4.3for information on the xlog_method parameter.
Note: The archive_command configuration parameter discussed in this section is located in the postgresql.conf file. This Postgres archive_command parameter is used in a different manner than the BART archive_command parameter, which may be set in the server sections of the BART configuration file, but only under certain conditions.
The ARCHIVE PATH field displayed by the BART SHOW-SERVERS subcommand shows the full directory path where the WAL files should be copied as specified in the Postgres archive_command configuration parameter in the postgresql.conf file:
In the following example, SCP copies the WAL files to directory /opt/backup/acctg/archived_wals at the BART host located at 192.168.2.22 as the bartuser user account.
Using the bartuser account allows the operation to copy to the BART backup catalog owned by bartuser.
The Postgres archive_command parameter can be automatically configured with the INIT subcommand. The INIT subcommand invokes the Postgres ALTER SYSTEM command to set the Postgres archive_command configuration parameter in the postgresql.auto.conf file located in the managed database server’s POSTGRES_INSTALL_HOME/data directory. See Section 3.4.2 of the EDB Postgres Backup and Recovery Guide for additional information on the INIT subcommand.
The archive command string that the INIT subcommand generates into the postgresql.auto.conf file is determined by the parameter setting of the BART archive_command parameter located in the BART configuration file.
The server section of the BART configuration file can contain a BART archive_command parameter to specify the desired format of the archive command string to be generated into the Postgres archive_command parameter in the postgresql.auto.conf file. If the BART archive_command parameter is not set in the server section for a given database server, the command string that is configured uses the following default format:
Replaced by the setting of the bart_host parameter located in the global section of the BART configuration file
Replaced by the archive path of where the WAL files are to be stored. The archive path takes the form backup_path/server_name/archived_wals where backup_path is the BART backup catalog parent directory specified in the global section of the BART configuration file and server_name is the lowercase conversion of the database server name specified for this database server in the server section of the BART configuration file.
The placeholders %h and %a are replaced by the INIT subcommand when creating the archive command string. The placeholders %p and %f are not replaced by the INIT subcommand, but are kept as given to be used by the Postgres archiving process.
The INIT subcommand is invoked by BART user account bartuser as follows:
The resulting Postgres archive command string in the postgresql.auto.conf file located in the managed database server’s POSTGRES_INSTALL_HOME/data directory appears as follows:
Note: Run the INIT subcommand with the -o option to take advantage of the auto configuration process. This option overrides any existing Postgres archive_command setting in the postgresql.conf or the postgresql.auto.conf file. In addition, the -o option must be used to generate the command string if the archive_mode configuration parameter is set to off even if there are no existing settings of the Postgres archive_command in the postgresql.conf or postgresql.auto.conf files.
The INIT subcommand is invoked by BART user account enterprisedb as follows:
The resulting Postgres archive_command parameter in the postgresql.auto.conf file appears as follows:
After generating the desired command string in the postgresql.auto.conf file, complete the required WAL archive settings in the postgresql.conf file:
Set wal_level to replica or higher for Postgres versions
Set archive_mode to on.
Set max_wal_senders to a value high enough to leave at least one session available for the backup. If the xlog_method=stream parameter setting is to be used by this database server as determined in the BART configuration file, the max_wal_senders setting must account for an additional session for the transaction log streaming (that is, the setting must be a minimum of 2). See sections4.2 and 4.3 for information on the xlog_method parameter.
When the database server has been restarted, the ARCHIVE COMMAND field of the SHOW-SERVERS subcommand displays the active Postgres archive command as shown by the following example:
The CHECK-CONFIG subcommand with the –s option checks the parameter settings in the database server configuration for which the -s option is specified.
The CHECK-CONFIG subcommand displays an error message if the required configuration is not properly set. The following example shows successful checking of a database server:
In addition, certain postgresql.conf parameters for the database server must be properly set and the database server must be activated for certain processes. These requirements include the following:
The cluster_owner parameter must be set to the user account owning the database cluster directory.
The pg_hba.conf file must contain a replication entry for the database superuser specified by the BART user parameter.
The archive_mode parameter in the postgresql.conf file must be enabled.
The archive_command parameter in the postgresql.auto.conf or the postgresql.conf file must be set.
The allow_incremental_backups parameter in the BART configuration file must be enabled for database servers for which incremental backups are to be taken.

4 Configuration : 4.3 Configuring the Database Server : 4.3.4 Enabling WAL Archiving

Table of Contents Previous Next