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
If you changed barman default running user(barman) to another, you should change the running user in
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"
md5 or trust authentication for
psql -h localhost -U barman -d postgres -c "SELECT version()" psql -h localhost -U repluser -c "IDENTIFY_SYSTEM" replication=1
Barman global configuration
[barman] 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
[spg] 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
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 000000010000000000000001
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 000000010000000000000002
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
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