SnapShooter Learning Center

Incremental Backup in PostgreSQL

Incremental Backup in PostgreSQL

Simon Bennett]
Simon Bennett
Last Updated: Aug 10, 2021
Table of Contents

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.


Did you find this article helpful?