Skip to main content
Skip table of contents

Configure backup for PostgreSQL

Learn how to take powerful backups of the PostgreSQL database server with the pgBackRest backup tool.

About pgBackRest

pgBackRest is a powerful backup and restore tool for PostgreSQL. It is effective for all database sizes and performs backups internally, reducing limitations associated with external tools such as tar and rsync for backup. This process also strengthens security.

Instead of relying on traditional backup tools such as tar and rsync, pgBackRest implements all backup features internally and uses a custom protocol for communicating with remote systems.

Check for the latest version of pgBackRest tool and in-depth knowledge on the official site of pgBackRest:

http://www.pgbackrest.org

Essential pgBackRest features

pgBackRest is a simple backup and restore system that can seamlessly scale up to the largest databases and workloads. Primary pgBackRest features:

  • Local or remote backup

  • Multi-threaded backup/restore for performance

  • Checksums

  • Full, differential, and incremental backups

  • Backup rotation and minimum retention rules with optionally separate retention for archiving

  • In-stream compression/decompression

  • Archiving and retrieval of logs for replicas/restores 

  • Asynchronous archiving for very busy systems (including space limits)

  • Tablespace support

  • Restore using timestamp/size or checksum

  • Restore remapping base/tablespaces

Install pgBackRest

Install pgBackRest tool through yum as below:

CODE
yum install -y pgbackrest


By default, pgBackRest tool configuration file(pgbackrest.conf) location is /etc and add below entry into /etc/pgbackrest.conf file:

CODE
vi /etc/pgbackrest.conf

[global] repo-path=/var/lib/pgbackrest retention-full=2 [data] db-path=/var/lib/pgsql/9.6/data

Configure PostgreSQL:

This is automated by ansible.

Edit the 'postgresql.conf' file:

CODE
vi /var/lib/pgsql/9.6/data/postgresql.conf

Add the following lines to the 'postgresql.conf' file:

CODE
archive_command = 'pgbackrest --stanza=data archive-push %p' 
archive_mode = on 
listen_addresses = '*' 
log_line_prefix = '' 
max_wal_senders = 3 
wal_level = hot_standby

Restart the database (root user)

CODE
systemctl restart postgresql-9.6 systemctl status postgresql-9.6

Configure pgbackrest config

First, define the stanza on the backup server. data is the DB cluster name here:

CODE
su - postgres -c 'pgbackrest --stanza=data --log-level-console=info stanza-create'

A stanza defines the backup configuration for a specific PostgreSQL database cluster. The stanza section must define the database cluster path and host/user if the database cluster is remote. Also, any global configuration sections can be overridden to define stanza-specific settings).

Now, check the stanza configuration by executing the below command. data is the DB cluster name here:

CODE
su - postgres -c 'pgbackrest --stanza=data --log-level-console=info check'

Create database backup

The first time, there is a full backup. After that, incremental backups are taken. data is the DB cluster name here:

CODE
su - postgres -c 'pgbackrest --stanza=data --log-level-console=info backup'

If you run the above command again, the tool automatically figures out which backup is required, either incremental or full backup.

List out backups

Use the below command to list out all the available backups. data is the DB cluster name here:

CODE
pgbackrest --stanza data info


Backup directory location

CODE
-bash-4.2$ pwd 
/var/lib/pgbackrest/backup/data 
-bash-4.2$ ls -ltrh 
total 3.9G 
-rw-r--r-- 1 root root 3.9G Dec 1 09:16 f 
drwxr-x--- 3 postgres postgres 4.0K Jan 7 04:52 20180107-043001F 
drwxr-x--- 4 postgres postgres 4.0K Jan 7 04:52 backup.history 
drwxr-x--- 3 postgres postgres 4.0K Jan 8 04:50 20180107-043001F_20180108-043001D 
drwxr-x--- 3 postgres postgres 4.0K Jan 9 04:50 20180107-043001F_20180109-043001D 
drwxr-x--- 3 postgres postgres 4.0K Jan 10 04:49 20180107-043001F_20180110-043001D 
drwxr-x--- 3 postgres postgres 4.0K Jan 11 04:51 20180107-043001F_20180111-043001D 
drwxr-x--- 3 postgres postgres 4.0K Jan 12 04:51 20180107-043001F_20180112-043002D 
drwxr-x--- 3 postgres postgres 4.0K Jan 13 04:51 20180107-043001F_20180113-043002D 
drwxr-x--- 3 postgres postgres 4.0K Jan 14 04:52 20180114-043001F 
drwxr-x--- 3 postgres postgres 4.0K Jan 15 04:48 20180114-043001F_20180115-043001D 
drwxr-x--- 3 postgres postgres 4.0K Jan 16 04:51 20180114-043001F_20180116-043001D 
drwxr-x--- 3 postgres postgres 4.0K Jan 17 04:50 20180114-043001F_20180117-043001D 
drwxr-x--- 3 postgres postgres 4.0K Jan 18 04:50 20180114-043001F_20180118-043001D 
lrwxrwxrwx 1 postgres postgres 33 Jan 18 04:50 latest -> 20180114-043001F_20180118-043001D 
-rw-r----- 1 postgres postgres 8.3K Jan 18 04:50 backup.info 
-rw-r----- 1 postgres postgres 8.3K Jan 18 04:50 backup.info.copy

Sample crontab from docs

To schedule cron job from Postgres user:

CODE
#m h dom mon dow command 
30 06 * * 0 pgbackrest --type=full --stanza=data backup 
30 06 * * 1-6 pgbackrest --type=diff --stanza=data backup

Restore database backup

Use the below command to restore the full database cluster, i.e. 'data'.

First, delete all the contents of the 'data' (cluster) directory. data is the DB cluster directory here:

CODE
sudo -u postgres find /var/lib/pgsql/9.6/data -mindepth 1 -delete


CODE
# Full restore 
sudo -u postgres pgbackrest --stanza=data restore 
... 
# ... or recovery to specific time 
sudo -u postgres pgbackrest --stanza=data --delta --recovery-option='recovery_target_time=2018-01-15 13:00' restore



JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.