Full & Incremental Backup and Restore for PostgreSQL using pgBackRest

PgBackRest is a powerful backup and restore tool for PostgreSQL. Below are the steps to perform full and incremental backups and restores using pgBackRest.

Installation

yum install https://rpmfind.net/linux/epel/9/Everything/x86_64/Packages/l/libssh2-1.11.0-1.el9.x86_64.rpm
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf install -y postgresql12-contrib
yum install -y pgbackrest

Configuration

1. Create a directory for pgBackRest:

mkdir -p /mnt/pgbackrest/

1. Edit PostgreSQL configuration:

vi /var/lib/pgsql/12/data/postgresql.conf

Add or modify the following lines:

# Archiving
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'



# For patroni 
# Patronictl -c /etc/patroni/patroni.yml edit-config
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  parameters:
    archive_command: pgbackrest --stanza=cbs_backup archive-push %p
    archive_mode: true
    restore_command: pgbackrest --stanza=cbs_backup archive-get %f "%p"
  use_pg_rewind: true
retry_timeout: 10
ttl: 30

1. Edit pgBackRest configuration:

vi /etc/pgbackrest.conf

Add the following configuration:

[global]
repo1-path=/mnt/pgbackrest/
repo1-cipher-pass=test123
repo1-retention-full-type=count
process-max=2
log-level-console=info
log-level-file=debug

[main]
db-path=/var/lib/pgsql/12/data/

1. Set permissions for the pgBackRest directory:

chown -R postgres:postgres /mnt/pgbackrest && chmod -R 700 /mnt/pgbackrest

1. Restart PostgreSQL:

systemctl restart postgresql-12

Backup Operations

1. Switch to the postgres user:

su - postgres

1. Create a pgBackRest stanza:

pgbackrest --stanza=main stanza-create

1. Check information about the stanza:

pgbackrest --stanza=main info

1. Perform a full backup:

pgbackrest --stanza=main --type=full backup

1. Perform an incremental backup:

pgbackrest --stanza=main --type=incr backup

Restore Operations

1. Stop PostgreSQL and remove the data directory:

rm -r /var/lib/pgsql/12/data
systemctl stop postgresql-12

1. Create a new data directory and set permissions:

mkdir /var/lib/pgsql/12/data
chown postgres:postgres /var/lib/pgsql/12/data/
chmod 700 /var/lib/pgsql/12/data/

1. Switch to the postgres user and perform the restore:

su - postgres
pgbackrest --stanza=main restore

Additional Commands

• To delete a stanza, first stop it:

pgbackrest --stanza=main stop

Then delete it:

pgbackrest --stanza=main stanza-delete

And finally, start it:

pgbackrest --stanza=main start

Useful pgBackRest Commands

• Restore to a specific point in time:

pgbackrest --stanza=main --delta --set=20220307-122315F --type=time --target="2022-03-07 12:30:17" restore

• Perform a full backup with compression:

sudo -u postgres pgbackrest --stanza=main --compress-level=6 --type=full backup

• Copy the backup to a remote server using scp:

scp -r /mnt/ root@10.6.128.94:/a/

• Logical backup using pg_dumpall:

nohup pg_dumpall -v -U postgres -h 10.52.10.62 -p 9998 -f /pg_data/fullbackup/9998.dump > /pg_data/fullbackup/9998_eror.txt 2>&1 &

Note: This guide provides an overview of using pgBackRest for full and incremental backups as well as restores for PostgreSQL. Always refer to the official documentation for detailed instructions and best practices. For more detailed and technical articles like this, keep following our blog on Medium. If you have any questions or need further assistance, feel free to reach out in the comments below and directly.