EDB - pgBackRest - Part III: RESTORE

 Theory

• The restore command automatically defaults to selecting the latest backup from the first repository where backups exist. The order in which the repositories are checked is dictated by the pgbackrest.conf (e.g. repo1 will be checked before repo2). To select from a specific repository, the --repo option can be passed (e.g. --repo=1). The --set option can be passed if a backup other than the latest is desired.
• Restoration Scenarios:
• Restoring the backup on a different host
• Restoring a single database from the Postgres cluster
• Restoring the backup to a specific point in time
• Restoring only the delta changes
• Restoring the backup on a different host to start as a streaming standby

1. Verify backup information

[enterprisedb@edb-nhatrang ~]$ pgbackrest info --stanza=employees

2. Restoring the backup on a different host (IP : 192.168.56.77)

• Setup Passwordless SSH Connection between Backup Server (192.168.56.79) and Database Server (192.168.56.77)
• Backup Server 
• ssh-keygen -t rsa
• ssh-copy-id enterprisedb@192.168.56.77

• Database Server
• ssh-keygen -t rsa
• ssh-copy-id enterprisedb@192.168.56.79

• Database Server - Setup pgBackRest Repository

        [root@edb-quynhon ~]# vi /etc/pgbackrest.conf 

        [global]

        repo1-host=192.168.56.79

        repo1-host-user=enterprisedb

        log-level-console=info

        log-level-file=debug

        [employees]

        pg1-path=/u01/edb/as16/data

        pg1-database=edb

        pg1-port=5444

        pg-version-force=16

2.1 Database Server - Restore the Lastest Backup 

[enterprisedb@edb-quynhon ~]$ pgbackrest --log-level-console=info --stanza=employees --process-max=2 restore

2024-06-25 15:47:07.509 P00   INFO: restore command begin 2.52: --exec-id=9675-76a8160f --log-level-console=info --log-level-file=debug --pg1-path=/u01/edb/as16/data --pg-version-force=16 --process-max=2 --repo1-host=192.168.56.79 --repo1-host-user=enterprisedb --stanza=employees

2024-06-25 15:47:08.481 P00   INFO: repo1: restore backup set 20240624-221733F_20240624-221842I, recovery will start at 2024-06-24 22:18:42

2024-06-25 15:47:17.598 P00   INFO: write updated /u01/edb/as16/data/postgresql.auto.conf

2024-06-25 15:47:17.625 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)

2024-06-25 15:47:17.628 P00   INFO: restore size = 740.5MB, file total = 2201

2024-06-25 15:47:17.628 P00   INFO: restore command end: completed successfully (10124ms)

==> The latest backup from the first repository will be restored

EDB - pgBackRest - Part II: Configuration & Backup

Theory

• A stanza is the configuration for a PostgreSQL database cluster that defines where it is located, how it will be backed up, archiving options, etc. Most db servers will only have one PostgreSQL database cluster and therefore one stanza, whereas backup servers will have a stanza for every database cluster that needs to be backed up.
• The stanza-delete command removes data in the repository associated with a stanza. Use this command with caution — it will permanently remove all backups and archives from the pgBackRest repository for the specified stanza.

A. CONFIGURATION

1. Create backup location

[root@edb-nhatrang /]# mkdir -p /backup/pgbackrest

[root@edb-nhatrang /]# chown -R enterprisedb: /backup/

2. Database Cluster Configuration

• Enable archive mode ON
• Set archive_command = ‘pgbackrest --stanza=employees archive-push %p’
• Get data_directory = ‘/u01/edb/as16/data’

edb=# show archive_mode ;

 archive_mode 

--------------

 on

(1 row)

edb=# show archive_command ;

                archive_command                

-----------------------------------------------

 pgbackrest --stanza=employees archive-push %p

(1 row)

edb=# show data_directory ;

   data_directory   

--------------------

 /u01/edb/as16/data

(1 row)

edb=# show wal_level ;

 wal_level 

-----------

 replica

(1 row)

edb=# show max_wal_senders ;

 max_wal_senders 

-----------------

 10

(1 row)

edb=# show log_filename ;

           log_filename           

----------------------------------

 enterprisedb-%Y-%m-%d_%H%M%S.log

(1 row)

3. Pgbackrest Repository Configuration

[root@edb-nhatrang ~]# vi /etc/pgbackrest.conf 

[global]

repo1-path=/backup/pgbackrest

repo1-retention-full=2

archive-async=y

log-level-console=info

log-level-file=debug

start-fast=y

[employees]

pg1-path=/u01/edb/as16/data

pg1-database=edb

pg1-port=5444

pg-version-force=16

EDB - pgBackRest - Part I: Installation

In this section, we will install pgBackRest using with EPAS (EDB Enterprise Advance Server). So we need to pay attention to something such as:

• User is enterprisedb, not postgres
• Grant right privileges to enterprisedb user

1. Configuring Postgres YUM repository

[root@edb-nhatrang ~]# dnf repolist 

[root@edb-nhatrang ~]# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

[root@edb-nhatrang ~]# dnf repolist 

2. Configuring EPEL Repository

[root@edb-nhatrang ~]# dnf install https://dl.fedoraproject.org/pub/epel/epel-release-latest-9.noarch.rpm

[root@edb-nhatrang ~]# dnf repolist 

3. Install pgBackRest

[root@edb-nhatrang ~]# dnf install pgbackrest

Using EDB Postgres Advanced Server, the enterprisedb system user will execute the pgbackrest command. The following commands will change the ownership of the pgBackRest directories:

[root@edb-nhatrang ~]# chown -R enterprisedb: /var/lib/pgbackrest

[root@edb-nhatrang ~]# chown -R enterprisedb: /var/log/pgbackrest

[root@edb-nhatrang ~]# chown -R enterprisedb: /var/spool/pgbackrest

4. Verify installation

[enterprisedb@edb-nhatrang ~]$ pgbackrest version

pgBackRest 2.52

[enterprisedb@edb-nhatrang ~]$ less /etc/pgbackrest.conf 

[global]

repo1-path=/var/lib/pgbackrest

#[main]

#pg1-path=/var/lib/pgsql/10/data

Ref:

• EDB Docs - pgBackRest (enterprisedb.com)
• pgBackRest - Reliable PostgreSQL Backup & Restore