EDB Postgres Backup and Recovery Quick Start Guide

This tutorial demonstrates using yum to install EDB Backup and Recovery Tool (BART) 2.5.4 on a RHEL or CentOS 7 host.  The tutorial assumes that the user has some knowledge of installation and system administration procedures, and administrative privileges on the host. It provides shortcuts that allow you to install and configure BART with minimal configuration settings.

For detailed information about BART installation and configuration, see the BART Installation and Upgrade Guide.

  • BART is tested with the following database versions:

    • Advanced Server - 9.5, 9.6, 10, 11, and 12.
    • PostgreSQL - 9.5, 9.6, 10, 11, and 12.

Installing BART

The following steps describe installing BART on a CentOS 8.x OS.

  1. Assume superuser privileges and use yum to create the repository configuration file:

    yum install -y https://yum.enterprisedb.com/edbrepos/edb-repo-latest.noarch.rpm
    
  2. To request credentials to the EDB repository, visit the EnterpriseDB website.

  3. Use your choice of editor to open the repository configuration file (named edb.repo, located in /etc/yum.repos.d) and set the value of the enabled parameter to 1, and replace the username and password placeholders in the baseurl specification with the username and password of a registered EnterpriseDB user.

  4. Then, install an Advanced Server or PostgreSQL database server. Initialize the cluster and start the server. Note that the BART host server is not required to have a Postgres (Advanced Server or PostgreSQL) installation, but must include a copy of the Postgres libpq library, the pg_basebackup utility program, and Boost Libraries 1.48 version for RHEL/CentOS 6, 1.53 version for RHEL/CentOS 7, and 1.66 version for RHEL/CentOS 8.

  5. Optionally, if you do not already have the pg_basebackup program installed on the BART host, you can install a limited number of files that include the pg_basebackup program using the following command:

    yum install edb-asxx-server-client
    
  6. Then, use the following command to install the BART RPM package:

    yum install edb-bart
    

BART (the bart program and bart-scanner) is installed in the /usr/edb/bart directory, referred to as <BART_HOME>. Repeat the installation process described in this section to install BART on all remote hosts where incremental backups are to be restored.

Configuring BART

Before configuring BART, establish the BART user account (the operating system user) that will run the BART command line program.

To configure the BART host and each database server that is to be managed by BART, perform the following steps:

  1. Assume superuser privileges, create the directory that will hold the BART backup catalog, and assign its ownership (with restrictive privileges) to the BART user account:

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

In the example, bartuser is the BART user account and /opt/backup is the BART backup catalog.

  1. Navigate to the /usr/edb/bart/etc directory and copy the bart.cfg.sample file to create the bart configuration file (bart.cfg):
cp bart.cfg.sample bart.cfg
  1. Open the BART configuration file (bart.cfg) using an editor of your choice. Scroll through the BART configuration file and edit the sections as required; example settings are included for your reference. You must add the mandatory parameters to both the sections as described in the following table. Default values may be used for optional parameters; for detailed information about parameter settings, see the BART Installation and Upgrade Guide.

    Parameters set in the [BART] section are applicable to all BART managed database servers, and the parameters set in the [ServerName] section are applicable only to the specific server; the [ServerName] setting overrides the [BART] section setting.

Parameters/Placeholder Section Description
bart_host [BART] Use this field to specify the BART user and the IP address of the host on which the BART utility is installed. Specify the value in the form of <bart_user>@<bart_host_address>.
backup_path [BART] Use this field to specify the path where all BART backups and archived WAL files will be stored. Ensure the BART user account holds privileges to create subdirectories and files within the location specified in the backup_path parameter. The default backup_path is BART backup catalog (/opt/backup).
pg_basebackup_path [BART] Use this field to specify the path to the pg_basebackup utility (/usr/edb/as<xx>/bin/pg_basebackup).
[ServerName] [ServerName] Specify the name of the database server to be backed up (for example, [EPAS12]).
host [ServerName] Specify the IP address of the database server to be configured for backup.
port [ServerName]
Specify the port number identifying the database server instance to be backed up. The default port number for EPAS is 5444 and for Postgres it is 5432.
The port parameter setting is only required if the database server listens on a port other than the default value.
user [ServerName] Specify the replication database user name used by BART to establish the connection to the database server for full backups.
cluster_owner [ServerName] Specify the Linux operating system user account that owns the database cluster.

In the following example, only mandatory parameters are set:

[BART]
bart_host= bartuser@192.168.169.199
backup_path = /opt/backup
pg_basebackup_path = /usr/edb/as12/bin/pg_basebackup

[EPAS12]
host = 127.0.0.1
user = repuser
cluster_owner = enterprisedb
  1. As the BART user, navigate to the /usr/edb/bart/bin directory and invoke the following subcommand (omitting the -s option) to verify the [BART] section parameter settings in the BART configuration file:

    bart CHECK-CONFIG
    
  2. Authorize SSH/SCP access between the server and the BART host without a password prompt.

  3. Create a replication database user for each database server that BART manages.

  4. To enable continuous WAL archiving for any database server for which BART is to perform a backup, in the postgresql.conf file set the wal_level to archive for Postgres 9.5 or to replica for Postgres 9.6 or later, set the archive_mode to on, set the archive_command (if it is not set in the bart.cfg file), and set the max_wal_senders to a value high enough to leave at least one session available for the backup, and restart the database server.

  5. To start the WAL scanner, navigate to the /usr/edb/bart/bin directory as a BART user and execute the following command:

    ./bart-scanner
    
  6. If you are using the default archive_command, then navigate to the /usr/edb/bart directory as a BART user, run the INIT subcommand without the -o option, and restart the database server:

    bart INIT [ -s { <server_name> | all } ]
    

    Where <server_name> is the name of the database server to be backed up.

    If you have customized the archive_command setting in the bart.cfg file, run the INIT subcommand with the -o option to override any existing Postgresql archive_command setting in the postgresql.conf or the postgresql.auto.conf file, and restart the database server.

    bart INIT [ -s { <server_name> | all } ] [ -o ]
    
  7. To verify the parameter settings of the database server, as a BART user navigate to the /usr/edb/bart/bin directory and invoke the CHECK-CONFIG subcommand with the -s option:

bart CHECK-CONFIG [ -s <server_name> ]

BART is now configured successfully. For detailed information about using BART, see the EDB Backup and Recovery Tool User Guide.


Creating a Passwordless Connection

The following example enables SSH/SCP access on a CentOS 8.x host; similar (platform-specific) steps will apply to other platforms/versions. You must create a passwordless connection between the BART host (SSH/SCP client) and the database server (target SSH/SCP server), as well as a passwordless connection between the database server (SSH/SCP client) and the BART host (target SSH/SCP server).

  1. Log in as the user account on the BART host that will be initiating the SSH or SCP connection and navigate to the user account’s home directory and check for an existing .ssh subdirectory. If the .ssh directory does not exist, create one with the required privileges.

  2. As a root user navigate to the /usr/edb/bart directory, open the /etc/ssh/sshd_config file and set the PubkeyAuthentication parameter to yes.

  3. Reload the configuration file:

    service sshd reload
    
If you get any SSH or SCP errors, examine the log file (/var/log/secure).
  1. As a BART user, use the following command to generate the public key file; you can accept the default responses:

    ssh-keygen -t rsa
    

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

  2. Use SCP to make a temporary copy of the public key file on the target server:

    scp ~/.ssh/id_rsa.pub target_user@host_address:tmp.pub
    
  3. As a target_user, log into the target server using ssh target_user@host_address command and navigate to the user account’s home directory to check if there is an existing .ssh subdirectory. If it does not exist, create one with the required privileges.

  4. Append the temporary client’s public key file, tmp.pub, to the authorized keys file named authorized_keys:

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

    If an authorized keys file does not exist, create a new file, but be careful not completely replace any existing authorized keys file.

  5. Ensure the authorized_keys file is only accessible by the file owner, and not by groups or other users:

    chmod 600 ~/.ssh/authorized_keys
    
  6. Delete the temporary public key file:

    rm tmp.pub
    

    Now, when logged into the BART host as a user, there should be no prompt for a password when you are connecting to the target database server:

    ssh target_user@database_server_address
    

Creating a Passwordless Connection Between the Database Server and the BART Host

  1. On the database server, navigate into the target user account’s home directory to check for an existing .ssh subdirectory. If it does does not exist, create one in the user account’s home directory with the required privileges.

  2. As a database server user, generate the public key file:

    ssh-keygen -t rsa
    
  3. Create a temporary copy of the public key file:

    scp ~/.ssh/id_rsa.pub target_user@host_address:tmp.pub
    
  4. As a target user, log into the BART host and navigate to the user account’s home directory to check if there is an existing .ssh subdirectory. If it does not exist, create one with the required privileges:

    ssh target_user@host_address
    
  5. Append the temporary, client’s public key file to the authorized_keys file:

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

If an authorized keys file does not exist, create a new file, but do not completely replace any existing authorized keys file.

  1. Ensure the authorized_keys file is only accessible by the file owner and not by groups or other users (chmod 600):

    chmod 600 ~/.ssh/authorized_keys
    
  2. Delete the temporary public key file:

    rm tmp.pub
    

    Now, when logged into the database server as a user, there should be no prompt for a password when you are connecting to the BART host:

ssh bart_user@bartip_address
  • 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.
For examples of creating a passwordless connection, see the EDB Postgres Backup and Recovery Reference Guide
  • Even when the Advanced Server database is on the same host as BART, and the Advanced Server database cluster owner is also the BART user account, a passwordless SSH/SCP connection must be established from the same user account to itself.

Creating a Replication Database User

  1. To create a replication database user (a superuser), connect to the database server with the psql client, and invoke the following PostgreSQL command:

    CREATE ROLE <repuser> WITH LOGIN SUPERUSER PASSWORD '<password>';
    
  2. Specify this replication database user in the user parameter of the bart.cfg file.

  3. The pg_hba.conf file must minimally permit the replication database user to have access to the database. The IP address from which the replication database user has access to the database is the BART host location. The replication database user must also be included in the pg_hba.conf file as a replication database connection if pg_basebackup is to be used for taking any backups.

  4. To ensure there is no password prompt when connecting to the database server with the replication database user, a recommended method is to use the .pgpass file located in the BART user account’s home directory (if it does not exist, you need to create the .pgpass file with the required privileges). The .pgpass file must contain an entry for each BART managed database server, and its corresponding replication database user and password.

    The following is an example of an entry in the .pgpass file (192.168.2.24 is the IP address of the database server):

    192.168.2.24:5444::repuser:password