March 22, 2018

Test PostgreSQL streaming archiving with Barman

We decide build a near real time backup mechanism for PostgreSQL with Barman. So I need test for some scenarios.

Environment & importants

  • I tested these config on Ubuntu 16.04.4 with PostgreSQL 10.3 and Barman 2.3

  • Barman delete obsolete backups if you configure retention policy, but you need backup your DB yourself, because only you know when should be the suitable time to backup

  • Barman ignored partial WAL during recovering, if there is a partial WAL in streaming directory, copy it to recovered pg_wal directory and rename it without .partial suffix!

  • If you changed barman default running user(barman) to another, you should change the running user in /etc/cron.d/barman accordingly

Building Environment

mkdir pgtest
cd pgtest
/usr/lib/postgresql/10/bin/initdb -D pgdata -E utf-8 -U dc
/usr/lib/postgresql/10/bin/pg_ctl -D pgdata -l logfile start


psql -h localhost -U dc -d postgres -c "CREATE USER barman WITH SUPERUSER"
psql -h localhost -U dc -d postgres -c "CREATE USER repluser WITH REPLICATION"

pg_hba.conf (optional)

md5 or trust authentication for barman and repluser

test connection

psql -h localhost -U barman -d postgres -c "SELECT version()"
psql -h localhost -U repluser -c "IDENTIFY_SYSTEM" replication=1

Barman global configuration

barman_user = dejavu
configuration_files_directory = /path/to/another/barman.d
barman_home = /path/to/another/barman/home
log_file = /path/to/another/barman.log
log_level = INFO

Barman server configuration

active = true
description = "Streaming archive and backup for pg"
basebackup_retry_times = 3
immediate_checkpoint = true
last_backup_maximum_age = 3 DAYS
retention_policy = RECOVERY WINDOW OF 1 WEEKS
retention_policy_mode = auto
wal_retention_policy = main
conninfo = host=localhost user=barman dbname=postgres
streaming_conninfo = host=localhost user=repluser dbname=postgres
backup_method = postgres
streaming_archiver = on
slot_name = b_slot
path_prefix = /usr/lib/postgresql/10/bin
basebackups_directory = /path/to/spg/base
errors_directory = /path/to/spg/errors
incoming_wals_directory = /path/to/spg/incoming
streaming_wals_directory = /path/to/spg/streaming
wals_directory = /path/to/spg/wals

check & resolve issues

sudo barman check spg

WAL archive and replication slot status are FAILED

Server spg:
    WAL archive: FAILED (please make sure WAL shipping is setup)
    PostgreSQL: OK
    is_superuser: OK
    PostgreSQL streaming: OK
    wal_level: OK
    replication slot: FAILED (replication slot 'b_slot' doesn't exist. Please execute 'barman receive-wal --create-slot spg')
    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)
    pg_basebackup: OK
    pg_basebackup compatible: OK
    pg_basebackup supports tablespaces mapping: OK
    pg_receivexlog: OK
    pg_receivexlog compatible: OK
    receive-wal running: FAILED (See the Barman log file for more details)
    archiver errors: OK

barman cron resolves some FAILED status, but if you changed barman default running user like me, you should modify running user in /etc/cron.d/barman

Create replication slot via Barman

sudo barman receive-wal --create-slot spg

Fix WAL archive

sudo barman switch-wal --force --archive spg

The WAL file 000000010000000000000001 has been closed on server 'spg'
Waiting for the WAL file 000000010000000000000001 from server 'spg' (max: 30 seconds)
Processing xlog segments from streaming for spg

Backup via Barman

sudo barman backup spg

Starting backup using postgres method for server spg in /var/lib/barman/spg/base/20180322T153237
Backup start at LSN: 0/2000060 (000000010000000000000002, 00000060)
Starting backup copy via pg_basebackup for 20180322T153237
Copy done (time: less than one second)
Finalising the backup.
This is the first backup for server spg
WAL segments preceding the current backup have been found:
        000000010000000000000001 from server spg has been removed
Backup size: 38.7 MiB
Backup end at LSN: 0/4000000 (000000010000000000000003, 00000000)
Backup completed (start time: 2018-03-22 15:32:37.525494, elapsed time: less than one second)
Processing xlog segments from streaming for spg

Simulate normal database operations

pgbench -h localhost -U dc -d postgres -i
pgbench -h localhost -U dc -d postgres -t 10000
pgbench -h localhost -U dc -d postgres -t 10000

Barman stores partial WALs to BARMAN_HOME/<SERVER_NAME>/streaming, a cron worker (wal_archiver) move WALs to BARMAN_HOME/<SERVER_NAME>/wals/<NO?>/


Before test I make a backup for database, there are 32 Mib WALs after the backup, we will recover this backup to a new PostgreSQL data directory and start from it.

barman list-backup spg

spg 20180322T153237 - Thu Mar 22 15:32:38 2018 - Size: 54.7 MiB - WAL Size: 32.0 MiB

barman recover spg latest /home/dejavu/pgtest/pgdata-recovered

Starting local restore for server spg using backup 20180322T153237
Destination directory: /home/dejavu/pgtest/pgdata-recovered
Copying the base backup.
Copying required WAL segments.
Generating archive status files
Identify dangerous settings in destination directory.

Your PostgreSQL server has been successfully prepared for recovery!

Running recovered DB

/usr/lib/postgresql/10/bin/pg_ctl -D pgdata-recovered -l logfile start

Powered by Hugo & Kiss.