DigitalOcean
SnapShooter

Incremental Backup in PostgreSQL

Database backups play an important role in an effective database recovery strategy in the production environment. It is very difficult for any database administrator to implement an effective backup strategy for real-time mission critical databases. There are multiple ways to take file system level backups on PostgreSQL Databases.

Barman is an open-source and very useful tool to perform PostgreSQL Database backups in the production environment. This tool is written in Python and allows you to list, keep, delete, archive, recover a full and incremental backup. You can manage the backup and recovery of multiple database servers from a single server. Barman uses Rsync and SSH to handle the full and incremental backups.

In this post, we will show you how to do an incremental backup in PostgreSQL with Barman.

Prerequisites

  • Two servers running Ubuntu 20.04 server.

  • A root password is configured on both server.

For the purpose of this tutorial, we will use the following setup:

Name IP Address Hostname PostgreSQL 45.58.44.108 pghost Barman 69.87.220.70 bmhost

Setup Hostname Resolution

Before starting, you will need to set up a hostname resolution on both servers so each server can communicate with each other by hostname.

To do so, edit the /etc/hosts file on both servers:

nano /etc/hosts

Add the following lines:

45.58.44.108 pghost 69.87.220.70 bmhost

Save and close the file when you are finished.

Install PostgreSQL

First, you will need to install the PostgreSQL server on pghost server. You can install it using the following command:

apt-get install postgresql rsync

Once the PostgreSQL is installed, log in to the PostgreSQL shell with the following command:

sudo -u postgres psql

Once you are log in, set a password to postgres user with the following command:

postgres=# ALTER USER postgres PASSWORD 'newpassword';

Next, exit from the PostgreSQL shell and proceed to the next step.

Install Barman

Next, you will need to install the Barman on the bmhost server. You can install it with the following command:

apt-get install barman -y

Once the Barman server is installed, verify it with the following command:

barman --version

You should get the following output:

2.10 Barman by 2ndQuadrant (www.2ndQuadrant.com)

Next, set the password for Barman user with the following command:

passwd barman New password: Retype new password: passwd: password updated successfully

Configure SSH Key-based Authentication on Both Servers

Next, you will need to configure an SSH key-based authentication on both servers so both servers can connect using SSH without providing a password.

Configure SSH Key-based Authentication on PostgreSQL Server

On the PostgreSQL server, log in to Postgres user with the following command:

su - postgres

Next, generate RSA key with the following command:

ssh-keygen -q -t rsa -N '' -f ~/.ssh/id_rsa

Next, copy the generated key to the Barman server:

ssh-copy-id -i ~/.ssh/id_rsa.pub barman@bmhost

Next, set proper permission to authorized_keys on Barman server.

ssh barman@bmhost "chmod 600 ~/.ssh/authorized_keys"

Next, exit from the Postgres user with the following command:

exit

Configure SSH Key-based Authentication on Barman Server

On the Barman server, log in as a barman user with the following command:

su - barman

Next, generate RSA key with the following command:

ssh-keygen -q -t rsa -N '' -f ~/.ssh/id_rsa

Next, copy the generated key to the PostgreSQL server:

ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@pghost

Next, set proper permission to authorized_keys on PostgreSQL server.

ssh postgres@pghost "chmod 600 ~/.ssh/authorized_keys"

Next, exit from the barman user with the following command:

exit

Configure PostgreSQL Server

By default, the PostgreSQL server listens only on localhost. So you will need to configure it to listen on the public-ip. You can set it by editing the PostgreSQL main configuration file:

nano /etc/postgresql/12/main/postgresql.conf

Change the following line to your server-ip:

listen_addresses = '45.58.44.108'

Next, enable the archive mode and define the path of a backup location on the Barman server.

wal_level = replica archive_mode = on archive_command = 'rsync -a %p barman@bmhost:/var/lib/barman/pghost/incoming/%f'

Save and close the file. Then, edit the PostgreSQL host-based authentication file and allow Barman server IP to connect to the PostgreSQL server.

nano /etc/postgresql/12/main/pg_hba.conf

Add the following lines:

host all all 69.87.220.70/32 md5 host replication all 69.87.220.70/32 md5

Save and close the file then restart the PostgreSQL service to apply the changes:

systemctl restart postgresql

Configure Barman Server

Next, you will need to configure the Barman server on the bmhost. First, edit the Barman main configuration file with the following command:

nano /etc/barman.conf

Change the following lines as per your requirements:

[barman] barman_user = barman configuration_files_directory = /etc/barman.d barman_home = /var/lib/barman log_file = /var/log/barman/barman.log log_level = INFO compression = gzip

Save and close the file then create a new configuration file to define your PostgreSQL host.

nano /etc/barman.d/pghost.conf

Add the following lines:

[pghost] description = "Postgres Server Configuration" ssh_command = ssh postgres@pghost conninfo = host=pghost user=postgres dbname=postgres port=5432 backup_method = rsync reuse_backup = link backup_options = concurrent_backup parallel_jobs = 2 archiver = on

Save and close the file when you are finished.

Next, log in as a barman user with the following command:

su - barman

Next, create a file to store the PostgreSQL user credentials:

nano ~/.pgpass

Add the following lines:

pghost:5432:*:postgres:newpassword

Save and close the file then set proper permission to ~/.pgpass file:

chmod 0600 ~/.pgpass

Where:

  • pghost is the hostname of the PostgreSQL server.

  • 5432 is the port number of the PostgreSQL server.

  • postgres is the super user of the PostgreSQL server.

  • newpassword is the password of the postgres user.

How to Backup PostgreSQL Database From Barman Server

At this point, the Barman server is configured to handle the PostgreSQL backup.

On the Barman server, log in as a barman user with the following command:

Now, list the backup server configured on the Barman server with the following command:

barman list-server

You should get the following output:

pghost - Postgres Server Configuration

Now, check the configuration of both servers:

barman check pghost

If every thing is fine, you should get the following output:

Server pghost: PostgreSQL: OK is_superuser: OK wal_level: OK directories: OK retention policy settings: OK backup maximum age: OK (no last_backup_maximum_age provided) compression settings: OK failed backups: OK (there are 0 failed backups) minimum redundancy requirements: OK (have 0 backups, expected at least 0) ssh: OK (PostgreSQL server) systemid coherence: OK (no system Id stored on disk) archive_mode: OK archive_command: OK continuous archiving: OK archiver errors: OK

You can also check the configuration of the pghost with the following command:

barman status pghost

You should get the following output:

Server pghost: Description: Postgres Server Configuration Active: True Disabled: False PostgreSQL version: 12.7 Cluster state: in production pgespresso extension: Not available Current data size: 23.9 MiB PostgreSQL Data directory: /var/lib/postgresql/12/main Current WAL segment: 000000010000000000000003 PostgreSQL 'archive_command' setting: rsync -a %p barman@bmhost:/var/lib/barman/pghost/incoming/%f Last archived WAL: No WAL segment shipped yet Failures of WAL archiver: 0 Passive node: False Retention policies: not enforced No. of available backups: 0 First available backup: None Last available backup: None Minimum redundancy requirements: satisfied (0/0)

Now, create your first backup with the following command:

barman backup pghost

You should get the following output:

Starting backup using rsync-concurrent method for server pghost in /var/lib/barman/pghost/base/20210727T101338 Backup start at LSN: 0/4000028 (000000010000000000000004, 00000028) This is the first backup for server pghost WAL segments preceding the current backup have been found: 000000010000000000000001 from server pghost has been removed Starting backup copy via rsync/SSH for 20210727T101338 (2 jobs) Copy done (time: 2 seconds) This is the first backup for server pghost Asking PostgreSQL server to finalize the backup. Backup size: 23.6 MiB. Actual size on disk: 23.6 MiB (-0.00% deduplication ratio). Backup end at LSN: 0/4000100 (000000010000000000000004, 00000100) Backup completed (start time: 2021-07-27 10:13:38.531348, elapsed time: 9 seconds) Processing xlog segments from file archival for pghost 000000010000000000000003 000000010000000000000004 000000010000000000000004.00000028.backup

To list your backup, run the following command:

barman list-backup pghost

You should see the following output:

pghost 20210727T101338 - Tue Jul 27 10:13:42 2021 - Size: 23.6 MiB - WAL Size: 0 B

To check the information of your backup, run the following command:

barman show-backup pghost 20210727T101338

You should see the following output:

Backup 20210727T101338: Server Name : pghost System Id : 6989524953855731927 Status : DONE PostgreSQL Version : 120007 PGDATA directory : /var/lib/postgresql/12/main Base backup information: Disk usage : 23.6 MiB (23.6 MiB with WALs) Incremental size : 23.6 MiB (-0.00%) Timeline : 1 Begin WAL : 000000010000000000000004 End WAL : 000000010000000000000004 WAL number : 1 WAL compression ratio: 99.90% Begin time : 2021-07-27 10:13:38.426340+00:00 End time : 2021-07-27 10:13:42.425332+00:00 Copy time : 2 seconds + 1 second startup Estimated throughput : 9.1 MiB/s (2 jobs) Begin Offset : 40 End Offset : 256 Begin LSN : 0/4000028 End LSN : 0/4000100 WAL information: No of files : 0 Disk usage : 0 B Last available : 000000010000000000000004 Catalog information: Retention Policy : not enforced Previous Backup : - (this is the oldest base backup) Next Backup : - (this is the latest base backup)

You can list the files base backup and required WAL files for a specific backup with the following command:

barman list-files pghost 20210727T101338

If you want to schedule a cron job to backup your server once a day at 10 PM, create a cron job with the following command:

crontab -e

Add the following line:

0 22 * * * barman /usr/bin/barman backup pghost

Save and close the file when you are finished.

Conclusion

In the above guide, we explained how to take incremental backup with Barman in PostgreSQL. You can now use Barman in the production environment to manage backups of multiple PostgreSQL servers. For more information, read the Barman official documentation.

Was this page helpful?

Thank you for helping us improve!