Table of Contents Previous Next


4 Configuration : 4.2 Configuring a Database Server for BART Management

A password-less connection is accomplished by the use of authorized public keys, which is a list of public keys of client user accounts that are to be allowed to connect to the target server.
Section 4.2.1.1 provides an example of how you may first have to enable public key authentication usage on the server running the SSH server daemon.
Section 4.2.1.2 provides general instructions on how to set up the authorized public keys file.
Section 4.2.1.3 then describes the combination of hosts for BART usage on which a connection must be established without a password prompt.
In the SSH server daemon configuration file, /etc/ssh/sshd_config, check that the following parameter is set to yes and is not commented:
Note: For any SSH or SCP errors or problems, examine the following log file:
The target server to which a password-less SSH or SCP connection is to be made must contain an authorized public keys file. The file is named authorized_keys and is located under the USER_HOME/.ssh directory where USER_HOME is the home directory of the user account on the target server that is to be used to establish the remote session.
Note: The public key should be appended onto the end of any existing authorized_keys file. Any existing authorized_keys file should not be replaced in its entirety.
Step 1: On the client system, log in as the user account that will be initiating the SSH or SCP connection.
Step 2: Change to the user account’s home directory and check if there is an existing .ssh subdirectory. If not, create one as follows:
chown user .ssh
chgrp usergroup .ssh
Where user is the user account name and usergroup is the associated group of the user.
Step 3: Generate the public key file with the following command. Accept all prompted defaults and do not specify a passphrase when prompted for one.
The public key file named id_rsa.pub is created in the .ssh subdirectory.
Step 4: By whatever means is available in your system environment, create a copy of file id_rsa.pub on the target server.
scp ~/.ssh/id_rsa.pub target_user@host_address:tmp.pub
Step 5: Log into the target server as target_user, again using whatever means is possible in your system environment.
ssh target_user@host_address
Step 6: Change to the target user account’s home directory and check if there is an existing .ssh subdirectory. If not, create one as shown in Step 2.
Step 7: Append the temporary, client’s public key file, tmp.pub, to the authorized keys file named authorized_keys. If an existing authorized keys file does not exist, create a new file, but do not completely replace any existing authorized keys file.
Make sure the authorized_keys file is only accessible by the file owner and not by groups or other users. If the authorized_keys file does not have the required permission setting (600) or it was newly created, change the file permissions as follows:
Step 8: Delete the temporary public key file, tmp.pub.
Now, when logged into the client system as user there should be no prompt for a password when commands such as the following are given:
ssh target_user@host_address
scp file_name target_user@host_address:directory_path
scp target_user@host_address:directory_path/file file_name
2.
From the BART host (SSH/SCP client) to each BART managed database server (target SSH/SCP server) for taking incremental backups and for supporting restoration of the full backup, the archived WAL files, and the modified blocks, which occurs when the BART RESTORE subcommand is given. Note: If backups are to be taken from a given database server host, but restored to a different database server host, the password-less SSH/SCP connections must be configured from the BART host to the database server host from which the backup is to be taken as well as from the BART host to the database server host to which the backup is to be restored.
For scenario 1, the SSH client in which the public key file (id_rsa.pub) is generated with the ssh-keygen –t rsa command is the database server. The public key file is generated by the user account running the database server.
The target SSH server in which the public key file is to be appended onto the ~/.ssh/authorized_keys file is the BART host. The authorized_keys file is in the BART user account’s home directory.
For scenario 2, the SSH client in which the public key file (id_rsa.pub) is generated with the ssh-keygen –t rsa command is the BART host. The public key file is generated by the BART user account.
The target SSH server in which the public key file is to be appended onto the ~/.ssh/authorized_keys file is the database server. The authorized_keys file is in the home directory of the user account owning the directory where the database backup is to be restored.
See Section 6.2 for examples of each scenario.
Sets the Postgres archive_command configuration parameter when the INIT subcommand in invoked.
Creates backups when the BACKUP subcommand is invoked.
The pg_hba.conf file must minimally permit the replication database user to have access to the template1 database as shown for repuser in the following example.
For pg_basebackup only: The replication database user must also be included in the pg_hba.conf file as a replication database connection as shown by the last entry in the example if pg_basebackup is to be used for taking any backups such as for standby servers.
The replication database user must be specified with the user parameter of the BART configuration file for the database server as shown by the following:
For example, if bartuser is the BART user account, then the .pgpass file located in /home/bartuser/.pgpass must contain the following entry.
Thus, when bartuser runs BART BACKUP, the password for the replication database user, repuser, is obtained from the .pgpass file of bartuser to connect to the database server running at 192.168.2.24 on port 5444.
The .pgpass file must contain an entry for each BART managed database server and its corresponding replication database user and password.
For background information about WAL archiving see the PostgreSQL Core Documentation available at:
Section 4.2.3.1 describes the general, manual configuration process.
Section 4.2.3.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 archive for Postgres 9.5 or to replica for Postgres 9.6 or later.
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.1 and 4.2.5 for 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 5.4.2 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.
Note: If you set the Postgres archive_command parameter in the postgresql.conf file as described in Section 4.2.3.1 and wish to use that particular setting, do not specify the -o option when running the INIT subcommand, otherwise the Postgres archive_command setting is overridden according to the auto configuration process described in this section.
You may use an archive command other than the default by setting the BART archive_command parameter with the desired command string in the server section of the BART configuration file.
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 archive for Postgres 9.5 or to replica for Postgres 9.6 or later.
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 sections 4.1 and 4.2.5 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:
Either the tar (-F t) or plain text (-F p) backup file format may be used for full backups with the BACKUP subcommand. The plain text (-F p) backup file format must be used for incremental backups.
Transaction log streaming (xlog_method = stream in the BART configuration file) for taking full backups with pg_basebackup can be used as long as the -F p option is specified with the BACKUP subcommand. Thus, transaction log streaming can be used on database clusters containing user-defined tablespaces.
If the particular database cluster you plan to back up contains tablespaces created by the CREATE TABLESPACE command, be sure to set the tablespace_path parameter in the BART configuration file before you perform a BART RESTORE operation.
The tablespace_path parameter specifies the directory paths to which you want the tablespaces to be restored.
The tablespace_path parameter takes the following format:
OID_1=tablespace_path_1;OID_2=tablespace_path_2 ...
OID_1, OID_2, … are the Object Identifiers of the tablespaces. You can find the OIDs of the tablespaces and their corresponding soft links to the directories by listing the contents of the POSTGRES_INSTALL_HOME/data/pg_tblspc subdirectory as shown in the following example:
The OIDs are 16644 and 16645 to directories /mnt/tablespace_1 and /mnt/tablespace_2, respectively. If you later wish to restore the tablespaces to the same locations as indicated in the preceding example, the BART configuration file must contain the following entry:
In either case, the directories specified in the tablespace_path parameter must exist and be empty at the time you perform the BART RESTORE operation.
If the database server is running on a remote host (in other words you are also using the remote_host configuration parameter or will specify the --remote-host option with the RESTORE subcommand), the specified tablespace directories must exist on the specified remote host.
Note: This example emphasizes the steps that are affected by tablespace usage. See Chapter 5 for the complete process required for backing up and restoring a database cluster.
The BART configuration file contains the following settings. Note that the tablespace_path parameter does not have to be set at this point.
Within the backup subdirectory 1447709811516 of the BART backup catalog, the tablespace data is stored with file names 16587.tar.gz and 16588.tar.gz as shown by the following:
On the remote host, directories /opt/restore_tblspc_1 and /opt/restore_tblspc_2 are created and assigned the proper ownership and permissions as shown by the following. The main database cluster is to be restored to /opt/restore.
Set the tablespace_path parameter in the BART configuration file to specify the tablespace directories.
The symbolic links in the pg_tblspc subdirectory point to the restored directory location:
Queries within psql also show the restored tablespaces:
[ServerName]. Identifies an entry for a database server in the server section of the configuration file. This is the name by which you refer to the database server using BART. The name is case-insensitive when referenced with BART subcommand options. A lowercase conversion of this name is used to create a subdirectory in the BART backup catalog for storing the backups and WAL files for this database server. This parameter is required.
backup_name. Template for user-defined, friendly names to be assigned to the backups of the database server. The template is an alphanumeric string that may include the following variables to be substituted by the timestamp values when the backup is taken: 1) %year – 4-digit year, 2) %month – 2-digit month, 3) %day – 2-digit day, 4) %hour – 2-digit hour, 5) %minute – 2-digit minute, and 6) %second – 2-digit second. To include the percent sign (%) as a character in the backup name, specify %% in the template. Do not enclose the template string in quotes even if you want the template to include space characters, otherwise the enclosing quotes are stored as part of the backup name. Use of space characters in the backup name, however, then requires enclosing the backup name in quotes when referenced with the -i option by BART subcommands. The maximum permitted length of backup names is 49 characters. This parameter can be overridden by the --backup-name option of the BACKUP subcommand. If this parameter is omitted from the BART configuration file, and the --backup-name option with a user-defined name is not specified with the BACKUP subcommand, then the backup can only be referenced in BART subcommands by the BART assigned, integer backup identifier.
host. IP address of the database server to be configured for backup. This parameter is required.
port. Port number identifying the database server instance (that is, the relevant database cluster) to be backed up. This parameter is optional. If omitted, the default is port 5444.
user. Replication database user name used by BART: 1) to establish the connection to the database server for full backups, 2) to set the Postgres archive_command configuration parameter when running the INIT subcommand, 3) to take incremental backups. This database user must be a superuser. Note: While running as the BART user account, the connection to the database server using this database user must not prompt for a password. Also, the pg_hba.conf file must contain a replication connection entry for this database user name. See Section 4.2.2 for more information. This parameter is required.
archive_command. This is the BART archive_command parameter. The content and variables specified in the BART archive_command result in the archive command string to be generated into the Postgres archive_command configuration parameter in the postgresql.auto.conf file when the INIT subcommand is used. Note that this BART archive_command parameter in the BART configuration file and the Postgres archive_command parameter in the postgresql.conf file and the postgresql.auto.conf file refer to two separate, distinct parameters that are to be set in different manners and have different resulting outcomes. Be sure to carefully observe the distinction between the BART archive_command and the Postgres archive_command when configuring these parameters according to the instructions. The following information applies only to the BART archive_command parameter. Enclose the command string within single quotes ('). Even if the archive_command parameter is omitted, it still results in its usage by the INIT subcommand as if it were actually specified with a setting of 'scp %p %h:%a/%f'. Variables are the following: 1) %p – path of the file to archive used by the Postgres archiving process, 2) %h – replaced by the bart_host parameter setting, 3) %a – replaced by the BART archive path, and 4) %f – archived file name used by the Postgres archiving process. See Section 4.2.3.2 for additional information.
cluster_owner. Linux operating system user account that owns the database cluster. This is typically enterprisedb for Advanced Server database clusters installed in the mode compatible with Oracle databases, or postgres for PostgreSQL database clusters and for Advanced Server database clusters installed in the mode compatible with PostgreSQL databases. This parameter is required.
remote_host. IP address of the remote server to which a backup is to be restored. The value for this parameter must be specified in the form remote_user@remote_host_address where remote_user is the user account on the target database server host that accepts a password-less SSH/SCP login connection from the BART user account, and is the owner of the directory where the backup is to be restored. remote_host_address is the IP address of the remote host. For restoring a backup to a remote host or for restoring any backup where remote_user and the BART user account are not the same users, either this parameter must be set, or it may be specified with the -r option with the BART RESTORE subcommand.
tablespace_path. Paths to which tablespaces are to be restored specified in format OID=tablespace_path;OID=tablespace_path … If the backup is to be restored to a remote host such as specified by the remote_host parameter, then the tablespace paths must exist on the remote host. This parameter is optional.
retention_policy. Determines when an active backup should be marked as obsolete, and hence, be a candidate for deletion. The setting can be either max_number BACKUPS, max_number DAYS, max_number WEEKS, or max_number MONTHS where max_number is a positive integer. If all of the keywords BACKUPS, DAYS, WEEKS, and MONTHS are omitted, the specified integer is interpreted as max_number BACKUPS by default. The setting of the retention_policy parameter in the server section of the BART configuration file overrides the setting of retention_policy in the global section. If omitted in the server section, the setting of retention_policy in the global section is used. If the retention_policy parameter is not specified in either section, then no additional backups are marked as obsolete when the MANAGE subcommand is used. See Section 5.2 for information on managing backups using a retention policy.
xlog_method. Determines how the transaction log is collected during execution of pg_basebackup through the BACKUP subcommand. Set to fetch to collect the transaction log files after the backup has completed. Set to stream to stream the transaction log in parallel with the full backup creation. If stream is used, the max_wal_senders configuration parameter in the postgresql.conf file for this database server must account for an additional session for the streaming of the transaction log, (that is, the setting must be a minimum of 2). The setting of the xlog_method parameter in the server section of the BART configuration file overrides the setting of xlog_method in the global section. If omitted in the server section, the setting of xlog_method in the global section is used. If the xlog_method parameter is not specified in either section, the default is fetch.
wal_compression. Enables the compression of archived WAL files in the BART backup catalog when the MANAGE subcommand is invoked. Set to enabled to compress the archived WALS files in gzip format. Set to disabled to leave the files uncompressed. Note: The gzip compression program must be in the BART user account’s PATH. Note: Compression of archived WAL files is not permitted for database servers on which incremental backups are to be taken. The setting of the wal_compression parameter in the server section of the BART configuration file overrides the setting of wal_compression in the global section. If omitted in the server section, the setting of wal_compression in the global section is used. If the wal_compression parameter is not specified in either section, the default is disabled. See Section 5.4.7 for information on using the MANAGE subcommand for WAL compression.
copy_wals_during_restore. Determines how the archived WAL files are collected when invoking the RESTORE subcommand. Set to enabled to copy the archived WAL files from the BART backup catalog to the restore_path/archived_wals directory prior to the database server archive recovery. Set to disabled to retrieve the archived WAL files directly from the BART backup catalog during the database server archive recovery. The BART generated restore_command parameter in the recovery.conf file reflects which of the two options is used. If the RESTORE subcommand is invoked with the -c option, then the archived WAL files are copied from the BART backup catalog to the restore_path/archived_wals directory, thus overriding any setting of the copy_wals_during_restore parameter in the BART configuration file. If the RESTORE subcommand is invoked without the -c option, then the following determines how the archived WAL files are restored: The setting of the copy_wals_during_restore parameter in the server section of the BART configuration file overrides the setting of copy_wals_during_restore in the global section. If omitted in the server section, the setting of copy_wals_during_restore in the global section is used. If the copy_wals_during_restore parameter is not explicitly set in either section, the default is disabled. See Section 5.4.8 for additional information.
allow_incremental_backups. Enables usage of the WAL scanner for incremental backups. Permits taking of an incremental backup when the BACKUP subcommand is invoked with the --parent option. Set to enabled to permit incremental backups. Set to disabled to disallow incremental backups and thus permit only full backups. If the allow_incremental_backups parameter is not specified, the default is disabled. See Section 5.4.3 for information on using the BACKUP subcommand for taking incremental backups. See Section 5.5 for information on running the WAL scanner. See Section 2.2 for general information on incremental backups.
thread_count. Specifies the number of worker threads for copying blocks from the database server to the BART backup catalog when the BACKUP subcommand is invoked for incremental backups. The same approach applies to full backups where the worker threads copy data files from the database server to the BART backup catalog. The same set of processes are used for the compression operation when taking full backups in order to provide parallel, compressed backups when the BACKUP subcommand is specified with the -z or -c options. Note: The compression operation does not apply to incremental backups. If the BACKUP subcommand is invoked with the --thread-count option, then the number of worker threads specified by this option overrides any setting of the thread_count parameter in the BART configuration file. If the BACKUP subcommand is invoked without the --thread-count option, then the following determines the number of worker threads used: The setting of the thread_count parameter in the server section of the BART configuration file overrides the setting of thread_count in the global section. If omitted in the server section, the setting of thread_count in the global section is used. If the thread_count parameter is not specified in either section, the default is 1. Note: When taking a full backup, if the thread count in effect is only 1, then the pg_basebackup utility is used to take the full backup unless the --no-pg_basebackup option is specified with the BACKUP subcommand.
batch_size. Specifies the number of blocks of memory used for copying modified blocks from the database server to the BART backup catalog when the BACKUP subcommand is invoked for incremental backups. Each block is 8192 bytes. The maximum permitted value is 131072 (131072 * 8192 = 1 GB). The minimum permitted value is 1 (1 * 8192 = 8192 bytes). Reduce the setting if the server complains that it has run out of memory while executing pg_read_binary_file(). The setting of the batch_size parameter in the server section of the BART configuration file overrides the setting of batch_size in the global section. If omitted in the server section, the setting of batch_size in the global section is used. If the batch_size parameter is not specified in either section, the default value of 49142 (49142 * 8192 = 3/8 GB) is used.
scan_interval. Specifies the number of seconds before forcing a scan of the WAL files in the archive directory of the BART backup catalog. The setting of the scan_interval parameter in the server section of the BART configuration file overrides the setting of scan_interval in the global section. If omitted in the server section, the setting of scan_interval in the global section is used. If the scan_interval parameter is not specified in either section, the default value of 0 is used, which means no brute-force scanning will be started.
description. Description of the database server. This parameter is optional.

4 Configuration : 4.2 Configuring a Database Server for BART Management

Table of Contents Previous Next