Configuring BART v2.6

To configure BART, you must establish the BART user account, configure the BART host, and configure the database server that will be backed up.

Establishing the BART User Account

The BART user account is an operating system user that will run the BART command line program. The BART user account must:

• own the BART backup catalog.
• be able to run the bart program and the bart-scanner program.
• be able to establish a SSH/SCP connection to and from each database server managed by BART.

You can optionally use the enterprisedb database user as the BART user account for an Advanced Server database and postgres database user as the BART user account for a PostgreSQL server. If you do not wish to use an existing database user as the BART user account, you must create an operating system user to assume the role.

Configuring BART and Database Server

As stated earlier, to configure BART, you must configure the BART host as well as the database server. The following table acts as a configuration parameter reference listing the mandatory and optional parameters with default values for [SERVER] as well as [BART] sections.

• Parameters set in the [BART] section are applicable to all BART managed database servers.
• Parameters set in the Server section are applicable only to the specific server; the Server parameter setting overrides the [BART] section setting.

For information about configuring BART host parameters, see the BART Host Parameter Reference and for information about configuring the database server parameters, see the Database Server Parameter Reference.

Configuring the BART Host

To configure the BART host, perform the following steps on the BART host as a root user:

Step 1. Navigate to the usr/edb/bart/etc directory and make a copy of the bart.cfg.sample file to create the bart.cfg file that will contain the parameter settings.

Step 2. Confirm that the Postgres pg_basebackup utility program is installed on the BART host. The pg_basebackup utility resides in the bin directory under your Postgres installation.

Step 3. Ensure the LD_LIBRARY_PATH environment variable includes the location of the libpq library. If your libpq library does not reside in the default location (POSTGRES_INSTALL_HOME/lib), you must add the library path to the LD_LIBRARY_PATH environment variable in the BART user account’s profile (bash_profile) located in /home/<bart user account>:

# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export LD_LIBRARY_PATH=/usr/edb/as11/lib:$LD_LIBRARY_PATH

Step 4. Create the BART backup catalog and ensure the BART user account holds privileges on the BART backup catalog. In the following example, the BART configuration file specifies /opt/backup as the parent directory for the BART backup catalog in the <backup_path> parameter:

[BART]

bart_host = bartuser@192.168.2.22
backup_path = /opt/backup
pg_basebackup_path = /usr/edb/as11/bin/pg_basebackup
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log

In the following example, bartuser is the BART user account. The example creates and sets the ownership and permissions on the BART backup catalog:

su root
mkdir /opt/backup
chown bartuser /opt/backup
chgrp bartuser /opt/backup
chmod 700 /opt/backup

If the subdirectory does not exist, BART creates a subdirectory for each database server listed in the configuration file when you invoke the bart command line program.

Step 5. Use your choice of editor to open the BART configuration file (located in the usr/edb/bart/etc directory) and edit the configuration as required. You must add the mandatory parameters to the [BART] section. Default values may be used for optional parameters.

The following table describes the [BART] host parameters.

Thread Count

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 for that particular database server.
• 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.
• 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.

<thread_count> will not be effective if the backup is taken on a standby server.

If parallel backup is run with N number of worker threads, then it will initiate N + 1 concurrent connections with the server.

Step 6 Invoke the CHECK-CONFIG subcommand, omitting the -s option to check the parameter settings in the BART configuration file. It should return the current BART version.

bart CHECK-CONFIG

The CHECK-CONFIG subcommand displays an error message if the required configuration is not properly set. You need to check the logfile to fix this.

Configuring the Database Server

To configure the database server, you must:

1. Authorize SSH/SCP access without a password prompt.
2. Create and configure a replication database user.
3. Adding the database server to the configuration file (server section).
4. Enable WAL archiving of the server.
5. Verify the server configuration settings.

The following section will walk you through the configuration process.

Authorizing SSH/SCP Access

BART uses the Secure Shell (ssh) and Secure Copy (scp) Linux utility programs to copy the backup and WAL files from the BART managed database servers to the BART host as well as to restore backups.

• The client/server ssh and scp commands must not prompt for a password when establishing a connection with the target server (the server to which a passwordless connection is being made).
• A passwordless connection uses authorized public keys (public key of a client user account) to authenticate with the target server.
• You must add the public key of each client user account to the target user account’s authorized public keys list on the target server.

For BART usage, there are two scenarios that require a passwordless SSH/SCP connection:

• When connecting from each BART managed database server (SSH/SCP client) to the BART host (target SSH/SCP server) to support WAL archiving as implemented by the archive_command parameter.
  • In this case, the database server user account should generate the public key file (id_rsa.pub) with the ssh-keygen –t rsa command on the database server host.
  • The public key file name should be appended to the ~/.ssh/authorized_keys file on the BART host. The authorized_keys file is in the BART user account’s home directory.
• When connecting 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.
  • In this case, the BART user account should generate the public key file (id_rsa.pub) with the ssh-keygen –t rsa command on the BART host.
  • The public key file name should be appended to the ~/.ssh/authorized_keys file on the database server host. The authorized_keys file is in the home directory of the user account that owns the directory where the database backup is to be restored.
• If backups are to be taken from a given database server host, but restored to a different database server host, the passwordless 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.

See the EDB Backup and Recovery Reference Guide available at the EDB website to view examples of creating a passwordless connection.

Enabling Public Key Authentication

The following example enables SSH/SCP access on a CentOS 7.x host; similar (platform-specific) steps will apply to other platforms/versions.

1. In the SSH server daemon configuration file (sshd_config) located in the /etc/ssh, set the PubkeyAuthentication parameter to yes.
2. Reload the configuration file:

service sshd reload

If you get any SSH or SCP errors, examine the /var/log/secure log file.

Creating a Passwordless Connection

The following general instructions will walk you through generating a client’s public key file, creating the target server’s authorized public keys file, and creating a passwordless connection.

Step 1. On the client system, log in as the user account that will be initiating the SSH or SCP connection.

Step 2. Navigate to the user account’s home directory and check for an existing .ssh subdirectory. If the .ssh directory does not exist, create one and assign the required privileges to 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.

ssh-keygen –t rsa

The public key file named id_rsa.pub is created in the .ssh subdirectory.

Step 4. While logged into the client where you just generated the public key file, use SCP to make a temporary copy of it on the target server:

scp ~/.ssh/id_rsa.pub <target_user>@<host_address>:tmp.pub

Step 5. Navigate into the target user account’s home directory and check for an existing .ssh subdirectory. If it does not exist, create one and assign the required privileges to the user.

Step 6. Append the temporary, client’s public key file, tmp.pub, to the authorized_keys file. If an authorized keys file does not exist, create a new file, but do not completely replace any existing authorized keys file.

cat tmp.pub >> ~/.ssh/authorized_keys

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 or it was newly created, change the file permissions as follows:

chmod 600 ~/.ssh/authorized_keys

Step 7. Delete the temporary public key file:

rm 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 is given:

ssh target_user@host_address

Setting up a Replication Database User

For each database server that is to be managed by BART, a database user must be chosen to serve as the replication database user. The replication database user sets the Postgres archive_command configuration parameter when the INIT subcommand in invoked and creates backups when the BACKUP subcommand is invoked. The replication database user must be a superuser.

When executed with the PSQL client, the following PostgreSQL command creates a superuser to be the replication database user:

CREATE ROLE repuser WITH LOGIN SUPERUSER PASSWORD 'password';

The pg_hba.conf file must minimally permit the replication database user to have access to the database.

In the following example, the pg_hba.conf file permits the repuser (replication database user) to have access to the template1 database. The IP address from which repuser has access to template1 database is the location of the BART host:

For pg_basebackup only: If pg_basebackup is to be used for taking any backups (such as for standby servers), 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 following example.

# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host template1 repuser 192.168.2.22/32 md5
host all enterprisedb 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host replication repuser 192.168.2.22/32 md5

The replication database user must be specified for the user parameter in the BART configuration file for the database server as shown in the following example:

[ACCTG]
host = 192.168.2.24
port = 5444
user = repuser
cluster_owner = enterprisedb
remote_host = enterprisedb@192.168.2.24
description = "Accounting"

There must be no password prompt when connecting to the database server with the replication database user. There are several ways to permit this; one recommended method is to use a .pgpass file located in the BART user account’s home directory.

For example, if bartuser is the BART user account, then the .pgpass file located in the /home/bartuser directory must contain the following entry:

192.168.2.24:5444::repuser:password

When bartuser invokes a 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.

Adding a Database Server to the BART Configuration File

To manage the backup and recovery of a database server, you must add entries to the [SERVER] section of the BART configuration file, which is located in <BART_HOME>/etc directory.

Database Server Parameter Reference

Set the following parameters in the [SERVER] section of the BART configuration file. The parameter setting in the [SERVER] section overrides the setting in the global [BART] section for that particular database server. If omitted, the default value will be used.

For each cluster serviced by BART, the following parameters are mandatory:

[HR]

host = 192.168.2.24
port = 5432
user = postgres
cluster_owner = postgres

The following table describes the database server parameters.

For information regarding the following parameters, see configuring the BART host.

• retention_policy
• xlog_method
• wal_compression
• copy_wals_during_restore.
• thread_count.
• batch_size.
• scan_interval.
• mbm_scan_timeout.
• workers

Backup Name Template

• The template is an alphanumeric string that may include the following variables that will be replaced with the timestamp values when the backup is taken:

  • %year to be replaced by 4-digit year
  • %month to be replaced by 2-digit month
  • %day to be replaced by 2-digit day
  • %hour to be replaced by 2-digit hour
  • %minute to be replaced by 2-digit minute
  • %second to be replaced by 2-digit second

• To include a 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. However, when referenced with the -i option by BART subcommands use of space characters in the backup name requires enclosing the backup name in quotes.

The following example shows the configuration settings of three database servers:

[ACCTG]

host = 127.0.0.1
port = 5444
user = enterprisedb
cluster_owner = enterprisedb
backup_name = acctg_%year-%month-%dayT%hour:%minute:%second
archive_command = 'cp %p %a/%f'
allow_incremental_backups = enabled
retention_policy = 8 BACKUPS
description = "Accounting"

[MKTG]

host = 192.168.2.24
port = 5444
user = repuser
cluster_owner = enterprisedb
remote_host = enterprisedb@192.168.2.24
allow_incremental_backups = enabled
description = "Marketing"

[HR]

host = 127.0.0.1
port = 5432
user = postgres
cluster_owner = postgres
retention_policy = 4 DAYS
description = "Human Resources"

Enabling WAL Archiving

WAL archiving must be enabled for the database server for which BART is to perform backup and recovery management.

• The WAL Archiving Configuration section describes the manual WAL archiving configuration process.
• The Archive Command Auto Configuration section describes an automated WAL archiving process.

WAL Archiving Configuration

Set the following configuration parameters in the postgresql.conf file to enable WAL archiving

• Set wal_level to replica or higher.
• Set archive_mode to on.
• Set the PostgreSQL archive_command parameter to copy the WAL files to the archive_path. The archive_command configuration parameter mentioned here is located in the postgresql.conf file; the PostgreSQL archive_command parameter is used in a different manner than the BART archive_command.
• 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, the max_wal_senders setting must account for an additional session for the transaction log streaming (the setting must be a minimum of 2). See Configuring the BART host for information about the xlog_method parameter.

For detailed information about WAL archiving, see the PostgreSQL Core Documentation.

The ARCHIVE PATH field displayed by the BART SHOW-SERVERS subcommand displays the full directory path where the WAL files should be copied as specified in the archive_command configuration parameter in the postgresql.conf file:

-bash-4.1$ bart SHOW-SERVERS -s acctg
SERVER NAME : acctg
HOST NAME : 192.168.2.24
USER NAME : repuser
PORT : 5444
REMOTE HOST :
RETENTION POLICY : none
DISK UTILIZATION : 0.00 bytes
NUMBER OF ARCHIVES : 0
ARCHIVE PATH : /opt/backup/acctg/archived_wals
ARCHIVE COMMAND : (disabled)
XLOG METHOD : fetch
WAL COMPRESSION : disabled
TABLESPACE PATH(s) :
INCREMENTAL BACKUP : DISABLED
DESCRIPTION : "Accounting"

The parameter settings in the following example will copy the WAL files to a directory named /opt/backup/acctg/archived_wals on the BART host located at 192.168.2.22 as the bartuser user account. Using the bartuser account ensures that the operation will have sufficient permissions to copy to the BART backup catalog owned by bartuser.

archive_mode = on                        # allows archiving to be done
                                         # (change requires restart)
archive_command = 'scp %p bartuser@192.168.2.22:/opt/backup/acctg/archived_wals/%f'
                                         # command to use to archive a logfile segment
                                         # placeholders: %p = path of file to archive
                                         # %f = file name only
...

max_wal_senders = 1                      # max number of walsender processes
                                         # (change requires restart)

The database server must be restarted in order to initiate WAL archiving, but do not do so until you have verified that the full path of the BART backup catalog has been created by a prior BART subcommand or the archive operation will fail.

Start the WAL scanner by executing the following command:

./bart-scanner

Archive Command Auto Configuration

To enable WAL archiving:

• In the postgresql.conf file, set the wal_level to replica or higher, archive_mode to on, and 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 Configuring the BART host for information on the xlog_method parameter.

• Configure the Postgres archive_command parameter automatically with the INIT subcommand and restart the database server when you are ready to initiate WAL archiving. 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. For additional information about the INIT subcommand, see the EDB Backup and Recovery User Guide available at the EDB website.

  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 in the server section of the BART configuration 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:

  'scp %p %h:%a/%f'

  The following table describes these variables:

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.

For example, to use the default archive command format, the BART configuration file contains the following settings where the BART archive_command parameter is omitted from the server section for ACCTG:

[BART]

bart_host= bartuser@192.168.2.22
backup_path = /opt/backup
pg_basebackup_path = /usr/edb/as11/bin/pg_basebackup
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log

[ACCTG]

host = 127.0.0.1
port = 5444
user = repuser
cluster_owner = enterprisedb
description = "Accounting"

The INIT subcommand is invoked by BART user account bartuser as follows:

[bartuser@localhost ~]$ bart INIT -s acctg -o
INFO: setting archive_command for server 'acctg'
WARNING: archive_command is set. server restart is required

If the BART backup catalog directory is not already complete, it will be completed.

The resulting archive command string in the postgresql.auto.conf file located in the managed database server’s POSTGRES_INSTALL_HOME/data directory appears as follows:

# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
archive_command = 'scp %p
bartuser@192.168.2.22:/opt/backup/acctg/archived_wals/%f'

Run the INIT subcommand with the -o option to override any existing 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 is set to off even if there are no existing settings of the archive_command in the postgresql.conf or postgresql.auto.conf files.

In this example, the following BART configuration file is used with an explicit setting of the BART archive_command parameter:

[BART]

bart_host= enterprisedb@192.168.2.22
backup_path = /opt/backup
pg_basebackup_path = /usr/edb/as11/bin/pg_basebackup
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log

[ACCTG]

host = 127.0.0.1
port = 5444
user = repuser
cluster_owner = enterprisedb
archive_command = 'cp %p %a/%f'
description = "Accounting"

The INIT subcommand is invoked by BART user account enterprisedb as follows:

-bash-4.1$ bart INIT -s acctg -o
INFO: setting archive_command for server 'acctg'
WARNING: archive_command is set. server restart is required

The resulting Postgres archive_command parameter in the postgresql.auto.conf file appears as follows:

# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
archive_command = 'cp %p /opt/backup/acctg/archived_wals/%f'

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:

-bash-4.1$ bart SHOW-SERVERS -s acctg
SERVER NAME : acctg
HOST NAME : 127.0.0.1
USER NAME : repuser
PORT : 5444
REMOTE HOST :
RETENTION POLICY : none
DISK UTILIZATION : 48.00 MB
NUMBER OF ARCHIVES : 0
ARCHIVE PATH : /opt/backup/acctg/archived_wals
ARCHIVE SCOMMAND : `cp %p /opt/backup/acctg/archived_wals/%f`
XLOG METHOD : fetch
WAL COMPRESSION : disabled
TABLESPACE PATH(s) :
INCREMENTAL BACKUP : DISABLED
DESCRIPTION : "Accounting"

Verifying Configuration Settings

To verify the parameter settings of the database server specified, execute tthe CHECK-CONFIG subcommand with the –s option:

bart CHECK-CONFIG [ –s server_name ]

The CHECK-CONFIG subcommand confirms the following:

• The cluster_owner parameter is set to the user account owning the database cluster directory.
• A passwordless SSH/SCP connection is set between the BART user and the user account specified by the cluster_owner parameter.
• The BART user parameter specifies a database superuser.
• The BART user has access to the backup directory catalog.
• The pg_hba.conf file contains a replication entry for the database superuser specified by the BART user parameter.
• The archive_mode parameter in the postgresql.conf file is enabled.
• The archive_command parameter in the postgresql.auto.conf or the postgresql.conf file is set.
• The allow_incremental_backups parameter in the BART configuration file is enabled for database servers for which incremental backups are to be taken.
• Archiving of WAL files to the archive_path is in process.
• The WAL scanner program is running.

After configuring the BART host and the database server(s), you can start using BART. For information about using BART, see the EDB Backup and Recovery User Guide available at the EDB website.

• On this page
• Establishing the BART User Account
• Configuring BART and Database Server