PostgreSQL backup and restore tools comparison for PITR recovery

PostgreSQL backup and restore

When you are a DBA the most important part, in my opinion, of your job is backup and restore. You can fail on any other parts of your job but not if you are not able to restore and recover a database after a disaster then you will be the only one to blame (not to say fire).

postgresql_backup_restore
postgresql_backup_restore

Obviously being able to recover a disaster scenario is your number one priority ! Those disasters could be hardware (power failure, Datacenter issues, ..) or software (accidently dropped a table, corruption, file deleted, …). I have also written restore because you have countless stories on Internet of DBAs that have not been able to restore their precious backup often by lack of testing.

Restore and recover must be done within the agreed Recovery Time Objective (RTO) and within the agreed (most probably as small as you can) Recovery Point Objective (RPO). The recovery is ideally as close as possible to the time of the problem (just before an important table drop for example) and are called Point In Time Recovery (PITR).

In this blog post I plan to test few famous PostgreSQL 13 backup tools and check if they are usable in a production environment…

My test PostgreSQL instance is release 13.3 running on RedHat 7.8 (Maipo). I will add new tool in future if I find interesting candidates…

My personal preference so far:

  1. PgBackRest
  2. Pg_basebackup
  3. Barman
  4. EDB Backup and Recovery Tool (BART)

Pg_basebackup

Pg_basebackup is the default tool coming directly with your PostgreSQL installation. We cannot expect miracle from it, but if it does a decent job why would we need more ?

I set the obvious below parameter to activate WAL and WAL archiving of my instance. As you can see I have decided to put my backup in /postgres/backup and archive WAL in /postgres/backup/archivedir:

archive_mode = on
archive_command = 'test ! -f /postgres/backup/archivedir/%f && cp %p /postgres/backup/archivedir/%f'

I create my own database and my traditional test table. Idea is same as usual, insert one row, perform a backup, insert a second row and try to recover till last transaction:

postgres=# create database yjaquierdb;
CREATE DATABASE
postgres=# \c yjaquierdb
You are now connected to database "yjaquierdb" as user "postgres".
yjaquierdb=# create table test(id int,descr varchar(50));
CREATE TABLE
yjaquierdb=# insert into test values(1,'One');
INSERT 0 1

I have only one, current, WAL file not yet archived obviously:

[postgres@server data]$ ll pg_wal
total 16384
-rw------- 1 postgres postgres 16777216 Jul 20 12:17 000000010000000000000001
drwx------ 2 postgres postgres       96 Jul 20 12:16 archive_status
 
[root@server backup]# ll /postgres/backup/archivedir/

I perform a backup:

[postgres@server data]$ mkdir -p /postgres/backup/full_20jul2021
[postgres@server data]$ pg_basebackup --pgdata=/postgres/backup/full_20jul2021 --format=t --compress=9 --progress --verbose --host=localhost --port=5433 --username=postgres
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_19427"
24989/24989 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/3000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
 
[postgres@server data]$ ll /postgres/backup/full_20jul2021
total 3124
-rw------- 1 postgres postgres  135690 Jul 16 17:38 backup_manifest
-rw------- 1 postgres postgres 3044276 Jul 16 17:38 base.tar.gz
-rw------- 1 postgres postgres   17643 Jul 16 17:38 pg_wal.tar.gz

We can see that 2 WAL files have been archived and 000000010000000000000003 is current WAL file:

[postgres@server data]$ ll /postgres/backup/archivedir/
total 65537
-rw------- 1 postgres postgres 16777216 Jul 20 12:18 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Jul 20 12:19 000000010000000000000002
-rw------- 1 postgres postgres      339 Jul 20 12:19 000000010000000000000002.00000028.backup
[postgres@server data]$ ll pg_wal
total 49154
-rw------- 1 postgres postgres 16777216 Jul 20 12:19 000000010000000000000002
-rw------- 1 postgres postgres      339 Jul 20 12:19 000000010000000000000002.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 20 12:19 000000010000000000000003
drwx------ 2 postgres postgres     1024 Jul 20 12:19 archive_status

I insert a second row in my test table (we can see it does not change the current WAL, so if you loose this file that is not included in full backup you would have data loss):

[postgres@server data]$ psql --dbname=yjaquierdb
psql (13.3)
Type "help" for help.
 
yjaquierdb=# insert into test values(2,'Two');
INSERT 0 1
yjaquierdb=# select * from test;
 id | descr
----+-------
  1 | One
  2 | Two
(2 rows)
 
yjaquierdb=# \q
[postgres@server data]$ ll pg_wal
total 49154
-rw------- 1 postgres postgres 16777216 Jul 20 12:19 000000010000000000000002
-rw------- 1 postgres postgres      339 Jul 20 12:19 000000010000000000000002.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 20 12:21 000000010000000000000003
drwx------ 2 postgres postgres     1024 Jul 20 12:19 archive_status

To have this file archived I perform a WAL switch, we will see later that a more elegant option is available:

[postgres@server data]$ psql
psql (13.3)
Type "help" for help.
 
postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/3000170
(1 row)
 
postgres=# \q
[postgres@server data]$ ll pg_wal
total 71618
-rw------- 1 postgres postgres 16777216 Jul 20 12:19 000000010000000000000002
-rw------- 1 postgres postgres      339 Jul 20 12:19 000000010000000000000002.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 20 12:22 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Jul 20 12:22 000000010000000000000004
drwx------ 2 postgres postgres     1024 Jul 20 12:22 archive_status
[postgres@server data]$ ll /postgres/backup/archivedir/
total 81921
-rw------- 1 postgres postgres 16777216 Jul 20 12:18 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Jul 20 12:19 000000010000000000000002
-rw------- 1 postgres postgres      339 Jul 20 12:19 000000010000000000000002.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 20 12:22 000000010000000000000003

I kill my PostgreSQL instance and clean my PGDATA directory:

[postgres@server data]$ kill -9 `ps -ef | grep /usr/pgsql-13/bin/postgres | grep -v grep | awk '{print $2}'`
[postgres@server data]$ rm -rf *

Pg_basebackup restore is simply two gtar commands:

[postgres@server data]$ ll /postgres/backup/full_20jul2021/
total 4148
-rw------- 1 postgres postgres  178001 Jul 20 12:19 backup_manifest
-rw------- 1 postgres postgres 4050202 Jul 20 12:19 base.tar.gz
-rw------- 1 postgres postgres   17662 Jul 20 12:19 pg_wal.tar.gz
[postgres@server data]$ gtar xvf /postgres/backup/full_20jul2021/base.tar.gz
.
.
[postgres@server data]$ gtar xvf /postgres/backup/full_20jul2021/pg_wal.tar.gz --directory pg_wal
000000010000000000000002
archive_status/000000010000000000000002.done

I update restore_command in my postgresql.conf file:

restore_command = 'cp /postgres/backup/archivedir/%f %p'

To start recover I touch the recovery.signal file and start the instance. We can see that latest inserted row is there:

[postgres@server data]$ touch recovery.signal
[postgres@server data]$ pg_start
waiting for server to start.... done
server started
[postgres@server data]$ psql --dbname=yjaquierdb
psql (13.3)
Type "help" for help.
 
yjaquierdb=# select * from test;
 id | descr
----+-------
  1 | One
  2 | Two
(2 rows)

Of course if you don’t execute the pg_switch_wal() procedure the WAL 000000010000000000000003 file contains le latest insert (the second row of my test table for example) and in case of crash you would need to cross your finger to recover this latest WAL file with your latest database transactions. Same as other product, by default, PostgreSQL offer a streaming of WAL files with pg_receivewal binary.

I execute pg_receivewal in the background:

[postgres@server data]$ nohup pg_receivewal --directory=/postgres/backup/archivedir/ --verbose --port=5433 --username=postgres &
[1] 4661
 
[postgres@server data]$ ll -rt pg_wal
total 49154
-rw------- 1 postgres postgres 16777216 Jul 20 12:29 000000020000000000000006
-rw------- 1 postgres postgres       41 Jul 20 12:29 00000002.history
-rw------- 1 postgres postgres 16777216 Jul 20 15:21 000000020000000000000004
drwx------ 2 postgres postgres     1024 Jul 20 15:21 archive_status
-rw------- 1 postgres postgres 16777216 Jul 20 15:22 000000020000000000000005
[postgres@server data]$ ll /postgres/backup/archivedir/
total 105650
-rw------- 1 postgres postgres 16777216 Jul 20 12:18 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Jul 20 12:19 000000010000000000000002
-rw------- 1 postgres postgres      339 Jul 20 12:19 000000010000000000000002.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 20 12:22 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Jul 20 15:21 000000020000000000000004
-rw------- 1 postgres postgres 16777216 Jul 20 15:26 000000020000000000000005.partial
-rw------- 1 postgres postgres       41 Jul 20 12:29 00000002.history

I perform a second full instance backup:

[postgres@server data]$ pg_basebackup --pgdata=/postgres/backup/full_20jul2021_2 --format=t --compress=9 --progress --verbose --host=localhost --port=5433 --username=postgres
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/6000028 on timeline 2
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_4923"
33131/33131 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/6000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
[postgres@server data]$ ll -rt pg_wal
total 49155
-rw------- 1 postgres postgres       41 Jul 20 12:29 00000002.history
-rw------- 1 postgres postgres 16777216 Jul 20 15:28 000000020000000000000005
-rw------- 1 postgres postgres 16777216 Jul 20 15:28 000000020000000000000006
drwx------ 2 postgres postgres     1024 Jul 20 15:28 archive_status
-rw------- 1 postgres postgres      339 Jul 20 15:28 000000020000000000000006.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 20 15:28 000000020000000000000007
[postgres@server data]$ ll /postgres/backup/archivedir/
total 171186
-rw------- 1 postgres postgres 16777216 Jul 20 12:18 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Jul 20 12:19 000000010000000000000002
-rw------- 1 postgres postgres      339 Jul 20 12:19 000000010000000000000002.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 20 12:22 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Jul 20 15:21 000000020000000000000004
-rw------- 1 postgres postgres 16777216 Jul 20 15:28 000000020000000000000005
-rw------- 1 postgres postgres 16777216 Jul 20 15:28 000000020000000000000006
-rw------- 1 postgres postgres 16777216 Jul 20 15:28 000000020000000000000007.partial
-rw------- 1 postgres postgres       41 Jul 20 12:29 00000002.history

I insert a third row in my test table:

[postgres@server data]$ psql --dbname=yjaquierdb
psql (13.3)
Type "help" for help.
 
yjaquierdb=# insert into test values(3,'Three');
INSERT 0 1
yjaquierdb=# select * from test;
 id | descr
----+-------
  1 | One
  2 | Two
  3 | Three
(3 rows)

I kill my PostgreSQL instance and purge PGDATA directory, I noticed that pg_receiwal command is surviving to instance shutdown: to be safe I have decided to kill it !

I restore my second full backup:

[postgres@server data]$ gtar xvf /postgres/backup/full_20jul2021_2/base.tar.gz
.
.
.
[postgres@server data]$ gtar xvf /postgres/backup/full_20jul2021_2/pg_wal.tar.gz --directory pg_wal
00000002.history
archive_status/00000002.history.done
000000020000000000000006
archive_status/000000020000000000000006.done

You have to modify restore_command paramleter in postgresql.conf or you get below error (I’m not sure why this is required to do so):

restore_command = 'cp /postgres/backup/archivedir/%f %p'
2021-07-20 15:59:27.897 CEST [9188] FATAL:  must specify restore_command when standby mode is not enabled

You have to copy latest partial WAL file and rename it to make PostgreSQL understand it:

[postgres@server data]$ ll -rt pg_wal
total 16385
-rw------- 1 postgres postgres       41 Jul 20 15:28 00000002.history
-rw------- 1 postgres postgres 16777216 Jul 20 15:28 000000020000000000000006
drwx------ 2 postgres postgres       96 Jul 20 15:37 archive_status
[postgres@server data]$ ll /postgres/backup/archivedir/
total 163842
-rw------- 1 postgres postgres 16777216 Jul 20 12:18 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Jul 20 12:19 000000010000000000000002
-rw------- 1 postgres postgres      339 Jul 20 12:19 000000010000000000000002.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 20 12:22 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Jul 20 15:21 000000020000000000000004
-rw------- 1 postgres postgres 16777216 Jul 20 15:28 000000020000000000000005
-rw------- 1 postgres postgres 16777216 Jul 20 15:28 000000020000000000000006
-rw------- 1 postgres postgres 16777216 Jul 20 15:30 000000020000000000000007.partial
-rw------- 1 postgres postgres       41 Jul 20 12:29 00000002.history
[postgres@server data]$ cp /postgres/backup/archivedir/000000020000000000000007.partial pg_wal/000000020000000000000007

Touch recovery.signal file and start the instance and you get latest transaction on your database:

[postgres@server data]$ touch recovery.signal
[postgres@server data]$ pg_start
waiting for server to start.... done
server started
[postgres@server data]$ psql --dbname=yjaquierdb
psql (13.3)
Type "help" for help.
 
yjaquierdb=# select * from test;
 id | descr
----+-------
  1 | One
  2 | Two
  3 | Three
(3 rows)

Overall pg_basebackup associated, or not, with pg_receivewal are low level tools but as an old monkey I really enjoyed them. Obviously you need a better understanding of PostgreSQL to use them but if you have it there is no surprise: they do what they sell. What’s missing currently is incremental backup…

PgBackRest

On my RedHat test server installation og PgBackRest is as simple as (postgresql-libs is also required but I had it already):

yum install pgbackrest-2.34-1.rhel7.x86_64.rpm

I set below parameter, the archive_command is coming from pgBackRest docuementation:

archive_mode = on
archive_command = 'pgbackrest --stanza=localhost archive-push %p'

I have customized the /etc/pgbackrest/pgbackrest.conf file as:

[localhost]
pg1-path=/postgres/13/data
pg1-port=5433
 
[global]
repo1-path=/postgres/backup
repo1-retention-full=2
 
[global:archive-push]
compress-level=3

I create the stanza with:

[postgres@server data]$ pgbackrest --stanza=localhost --log-level-console=info stanza-create
2021-07-20 16:32:28.091 P00   INFO: stanza-create command begin 2.34: --exec-id=13449-af55e788 --log-level-console=info --pg1-path=/postgres/13/data --pg1-port=5433 --repo1-path=/postgres/backup --stanza=localhost
2021-07-20 16:32:28.695 P00   INFO: stanza-create for stanza 'localhost' on repo1
2021-07-20 16:32:28.706 P00   INFO: stanza-create command end: completed successfully (616ms)
[postgres@server data]$ pgbackrest --stanza=localhost --log-level-console=info check
2021-07-20 16:34:08.856 P00   INFO: check command begin 2.34: --exec-id=13677-eadf8ac5 --log-level-console=info --pg1-path=/postgres/13/data --pg1-port=5433 --repo1-path=/postgres/backup --stanza=localhost
2021-07-20 16:34:09.466 P00   INFO: check repo1 configuration (primary)
2021-07-20 16:34:09.668 P00   INFO: check repo1 archive for WAL (primary)
2021-07-20 16:34:09.769 P00   INFO: WAL segment 000000030000000000000009 successfully archived to '/postgres/backup/archive/localhost/13-1/0000000300000000/000000030000000000000009-fbaa8cde71f2165ceb75365b34f3b515fe8675c6.gz' on repo1
2021-07-20 16:34:09.769 P00   INFO: check command end: completed successfully (914ms)

I have my test table:

yjaquierdb=# select * from test;
 id | descr
----+-------
  1 | One
(1 row)

Perform a backup with:

[postgres@server data]$ pgbackrest --stanza=localhost --log-level-console=info backup
2021-07-20 16:39:43.658 P00   INFO: backup command begin 2.34: --exec-id=14313-31cfa7cf --log-level-console=info --pg1-path=/postgres/13/data --pg1-port=5433 --repo1-path=/postgres/backup --repo1-retention-full=2 --stanza=localhost
WARN: no prior backup exists, incr backup has been changed to full
2021-07-20 16:39:44.364 P00   INFO: execute non-exclusive pg_start_backup(): backup begins after the next regular checkpoint completes
2021-07-20 16:39:44.864 P00   INFO: backup start archive = 00000003000000000000000B, lsn = 0/B000028
.
.
[postgres@server data]$ pgbackrest info
stanza: localhost
    status: ok
    cipher: none
 
    db (current)
        wal archive min/max (13): 000000030000000000000007/00000003000000000000000B
 
        full backup: 20210720-163944F
            timestamp start/stop: 2021-07-20 16:39:44 / 2021-07-20 16:39:47
            wal start/stop: 00000003000000000000000B / 00000003000000000000000B
            database size: 31.8MB, database backup size: 31.8MB
            repo1: backup set size: 3.9MB, backup size: 3.9MB

I insert a second row in my test table and objective will be to rstore it with no data loss:

[postgres@server data]$ psql --dbname=yjaquierdb
psql (13.3)
Type "help" for help.
 
yjaquierdb=# select * from test;
 id | descr
----+-------
  1 | One
  2 | Two
(2 rows)

PgBackRest is currently not capable of WAL streaming like you would do with barman or low level with pg_receivewal so the only option not to loose anything is, for example, to perform an incremental backup:

[postgres@server data]$ pgbackrest --stanza=localhost --log-level-console=info --type=incr backup
.
.
[postgres@server data]$ pgbackrest info
stanza: localhost
    status: ok
    cipher: none
 
    db (current)
        wal archive min/max (13): 00000003000000000000000C/000000030000000000000011
 
        full backup: 20210720-164748F
            timestamp start/stop: 2021-07-20 16:47:48 / 2021-07-20 16:47:51
            wal start/stop: 00000003000000000000000E / 00000003000000000000000E
            database size: 31.8MB, database backup size: 31.8MB
            repo1: backup set size: 3.9MB, backup size: 3.9MB
 
        incr backup: 20210720-164748F_20210720-170531I
            timestamp start/stop: 2021-07-20 17:05:31 / 2021-07-20 17:05:33
            wal start/stop: 000000030000000000000010 / 000000030000000000000010
            database size: 31.8MB, database backup size: 24.3KB
            repo1: backup set size: 3.9MB, backup size: 634B
            backup reference list: 20210720-164748F

I kill and erase PGDATA directory to simulate a disaster !

Restore your instance with something like (PITR possible with multiple option):

[postgres@server data]$ pgbackrest --stanza=localhost --log-level-console=info --target-timeline=latest restore
.
.

Simply start the instance (pgbackrest has create recovery.signal and set required option for you) and check everything is there:

[postgres@server data]$ pg_start
waiting for server to start.... done
server started
[postgres@server data]$ psql --dbname=yjaquierdb
psql (13.3)
Type "help" for help.
 
yjaquierdb=# select * from test;
 id | descr
----+-------
  1 | One
  2 | Two
(2 rows)

PgBackRest is closer from what I know with Oracle RMAN. You can work local or remote. Except WAL streaming that is not there you can do full, incremental or differential backups. Ease of use is there and I had no pain in implementing it: my preferred backup tool so far !

EDB Backup and Recovery Tool

EDB Backup and Recovery Tool (BART) is the commercial product from EnterpriseDB, once you have configured the EDB repository with your (trial) account and password installation is as simple as:

yum -y install edb-bart
[postgres@server ~]$ cp /usr/edb/bart/etc/bart.cfg.sample /postgres/backup/bart.cfg

Cutomized the BART configuration to map your environment:

[BART]
bart_host= postgres@127.0.0.1
backup_path = /postgres/backup
pg_basebackup_path = /usr/bin/pg_basebackup
logfile = /postgres/backup/bart.log
scanner_logfile = /postgres/backup/bart_scanner.log
thread_count = 5
 
[localhost]
host = 127.0.0.1
port = 5433
user = postgres
cluster_owner = postgres
description = "PostgreSQL 13 Community"
allow_incremental_backups = enabled

I start my test PostgreSQL insatnce with:

[postgres@server log]$ pg_ctl -l logfile start
waiting for server to start.... stopped waiting
pg_ctl: could not start server

I had a startup issue:

21-07-06 12:22:23.090 CEST [18106] LOG:  invalid primary checkpoint record
2021-07-06 12:22:23.090 CEST [18106] PANIC:  could not locate a valid checkpoint record
2021-07-06 12:22:23.090 CEST [18104] LOG:  startup process (PID 18106) was terminated by signal 6: Aborted
2021-07-06 12:22:23.090 CEST [18104] LOG:  aborting startup due to startup process failure
2021-07-06 12:22:23.092 CEST [18104] LOG:  database system is shut down

Which I solved with:

[postgres@server log]$ pg_resetwal /postgres/13/data
Write-ahead log reset
[postgres@server log]$ pg_ctl -l logfile start
waiting for server to start.... done
server started

As asked you need to configure ssh passwordless access for connection with no password of the form of account@ip_address (this is because BART will configure archive_command by default to scp %p postgres@127.0.0.1:/postgres/backup/localhost/archived_wals/%f):

[postgres@server ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/postgres/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/postgres/.ssh/id_rsa.
Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:vMu4U0BRmKPgFDigdQ570ehg6/7lzwFsYGY7VtQWEzo postgres@server
The key's randomart image is:
+---[RSA 2048]----+
|o.+.ooo*=o       |
|+.==.o*.o.       |
|.=.=BoEo         |
|  o=o=.o         |
| .  + +.S        |
|  .. o ...       |
| .    ..o        |
|  .  o.+ o       |
|   .. ++=        |
+----[SHA256]-----+
[postgres@server ~]$ cd .ssh
[postgres@server .ssh]$ ll
total 12
-rw------- 1 postgres postgres 1679 Jul  7 12:02 id_rsa
-rw-r----- 1 postgres postgres  400 Jul  7 12:02 id_rsa.pub
-rw-r--r-- 1 postgres postgres  716 Jul  7 12:01 known_hosts
[postgres@server .ssh]$ cat id_rsa.pub >> authorized_keys
[postgres@server .ssh]$ chmod 600 authorized_keys
[postgres@server .ssh]$ ssh postgres@127.0.0.1
Last failed login: Wed Jul  7 12:03:05 CEST 2021 from 127.0.0.1 on ssh:notty
Last login: Wed Jul  7 11:44:05 2021
[postgres@server ~]$

As I have already configured my PostgreSQL cluster for continuous WAL archiving with below parameter I might use the –no-configure option of BART initialization. If you decide to keep your own configuration you must mimic BART way of working i.e. the target directory for WAL archiving will be located inside your BART root directory (/postgres/backup for me) and configured server name (localhost for me) sub-directory:

checkpoint_timeout = 30s
max_wal_size = 1GB
min_wal_size = 80MB
archive_mode = on
archive_command = 'test ! -f /postgres/backup/localhost/archived_wals/%f && cp %p /postgres/backup/localhost/archived_wals/%f'
#restore_command = 'cp /postgres/backup/localhost/archived_wals/%f %p'

Initialiaz BART with (my BART root directory is /postgres/backup). If you want to override your configuration you have the –override option:

[postgres@server backup]$ bart init --server=localhost --no-configure

If you decide to let BART configure your PostgreSQL instanced for you check the postgresql.auto.conf file to see what has been changed. There is an interesting view called pg_file_settings to see from which file the setting is coming. To reset setting of postgresql.auto.conf file use ALETER SYSTEM SET parameter TO DEFAULT command:

postgres=# select sourcefile,count(*) from pg_file_settings where sourcefile is not null group by sourcefile;
               sourcefile               | count
----------------------------------------+-------
 /postgres/13/data/postgresql.conf      |    24
 /postgres/13/data/postgresql.auto.conf |     2
(2 rows)

Perform a backup with:

[postgres@server backup]$ bart backup --server=localhost --gzip --compress-level=9 --backup-name=full_6jul2021 --with-pg_basebackup --thread-count=4
INFO:  DebugTarget - getVar(checkDiskSpace.bytesAvailable)
INFO:  creating full backup using pg_basebackup for server 'localhost'
INFO:  creating backup for server 'localhost'
INFO:  backup identifier: '1625567515978'
INFO:  backup completed successfully
INFO:
BART VERSION: 2.6.2
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1625567515978
BACKUP NAME: full_6jul2021
BACKUP PARENT: none
BACKUP LOCATION: /postgres/backup/localhost/1625567515978
BACKUP SIZE: 4.19 MB
BACKUP FORMAT: tar.gz
BACKUP TIMEZONE: Europe/Paris
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 0
TABLESPACE(s): 0
START WAL LOCATION: 00000003000000010000007F
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2021-07-06 12:31:55 CEST
STOP TIME: 2021-07-06 12:32:01 CEST
TOTAL DURATION: 6 sec(s)

List existing backups:

[postgres@server backup]$ bart show-backups --server=localhost
 SERVER NAME   BACKUP ID       BACKUP NAME     BACKUP PARENT   BACKUP TIME                BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS
 
 localhost     1625567515978   full_6jul2021   none            2021-07-06 12:32:01 CEST   4.19 MB       0.00 bytes    0           active

Delete backups:

[postgres@server backup]$ bart delete --server=localhost --backupid=1625567515978
INFO:  deleting backup '1625567515978' of server 'localhost'
INFO:  deleting backup '1625567515978'
INFO:  WALs of deleted backup(s) will belong to prior backup(if any), or will be marked unused
WARNING: not marking any WALs as unused WALs, the WAL file '/postgres/backup/localhost/archived_wals/00000003000000010000007F' is required, yet not available in archived_wals directory
INFO:  backup(s) deleted
[postgres@server backup]$ bart show-backups --server=localhost
 SERVER NAME   BACKUP ID   BACKUP NAME   BACKUP PARENT   BACKUP TIME   BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS

As usual I create a test table where I will insert rows after backup to see if I can do a PITR recovery and get the latest inserted rows:

yjaquierdb=# create table test(id int, descr varchar(50));
CREATE TABLE
yjaquierdb=# insert into test values(1,'One');
INSERT 0 1
yjaquierdb=# select * from test;
 id | descr
----+-------
  1 | One
(1 row)

I create a full backup with:

[postgres@server backup]$ bart backup --server=localhost --gzip --compress-level=9 --backup-name=full_7jul2021 --with-pg_basebackup --thread-count=4
.
.
.
[postgres@server backup]$ bart show-backups --server=localhost
 SERVER NAME   BACKUP ID       BACKUP NAME     BACKUP PARENT   BACKUP TIME                BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS
 
 localhost     1625662500032   full_7jul2021   none            2021-07-07 14:55:05 CEST   4.23 MB       0.00 bytes    0           active

I insert a new row in my test table with:

yjaquierdb=# insert into test values(2,'Two');
INSERT 0 1
yjaquierdb=# select * from test;
id | descr
----+-------
  1 | One
  2 | Two
(2 rows)

I kill my PostgreSQL instance with:

[postgres@server ~]$ kill -9 `ps -ef | grep /usr/pgsql-13/bin/postgres | grep -v grep | awk '{print $2}'`

In a disaster scenario you might not be able to copy the latest WAL file that have not been archived and so your recovery will be incomplete and you will have data loss. My test scenario is favorable and I can copy latest unarchived WAL file with:

[postgres@server data]$ cp pg_wal/* /postgres/backup/localhost/archived_wals/
cp: omitting directory ‘pg_wal/archive_status’

Then before BART restore I crash everything with:

[postgres@server ~]$ rm -rf /postgres/13/data
[postgres@server ~]$ ll /postgres/13/data
total 0

BART restore:

[postgres@server backup]$ bart restore --server=localhost --backupid=1625662500032 --restore-path=/postgres/13/data --target-tli=latest
INFO:  restoring backup '1625662500032' of server 'localhost'
INFO:  base backup restored
INFO:  writing recovery settings to postgresql.auto.conf
INFO:  WAL file(s) will be streamed from the BART host
INFO:  archiving is disabled
INFO:  permissions set on $PGDATA
INFO:  restore completed successfully

Remark:
The –target-tli (or any other –target_xx parameters) is highly important because if you omit it then PostgreSQL will perform a recovery until the first consistent state of the database and so WILL NOT perform a PITR recovery and you desperately won’t see the latest inserted rows in your test table. –target-tli BART option is equivalent to PostgreSQL recovery_target_timeline parameter.

Everything restored and recovery.signal file automatically created for you:

[postgres@server data]$ ll /postgres/13/data/
total 62
-rw------- 1 postgres postgres   227 Jul  7 14:55 backup_label
-rw------- 1 postgres postgres   227 Jul  6 18:00 backup_label.old
drwx------ 6 postgres postgres    96 Jul  7 14:55 base
-rw------- 1 postgres postgres    30 Jul  7 12:33 current_logfiles
drwx------ 2 postgres postgres  2048 Jul  7 14:57 global
drwx------ 2 postgres postgres  1024 Jul  7 12:06 log
-rw------- 1 postgres postgres  1249 Jul  7 12:33 logfile
drwx------ 2 postgres postgres    96 Jun 17 17:21 pg_commit_ts
drwx------ 2 postgres postgres    96 Jun 17 17:21 pg_dynshmem
-rw------- 1 postgres postgres  4831 Jun 17 11:54 pg_hba.conf
-rw------- 1 postgres postgres  1636 Jun 14 16:42 pg_ident.conf
drwx------ 4 postgres postgres    96 Jul  7 14:55 pg_logical
drwx------ 4 postgres postgres    96 Jun 17 17:21 pg_multixact
drwx------ 2 postgres postgres    96 Jun 17 17:21 pg_notify
drwx------ 2 postgres postgres    96 Jun 17 17:21 pg_replslot
drwx------ 2 postgres postgres    96 Jun 17 17:21 pg_serial
drwx------ 2 postgres postgres    96 Jun 17 17:21 pg_snapshots
drwx------ 2 postgres postgres    96 Jul  7 12:33 pg_stat
drwx------ 2 postgres postgres    96 Jul  7 14:55 pg_stat_tmp
drwx------ 2 postgres postgres    96 Jul  6 18:07 pg_subtrans
drwxr-x--- 2 postgres postgres    96 Jul  7 14:57 pg_tblspc
drwx------ 2 postgres postgres    96 Jun 17 17:21 pg_twophase
-rw------- 1 postgres postgres     3 Jun 14 16:42 PG_VERSION
drwx------ 3 postgres postgres  1024 Jul  7 14:57 pg_wal
drwx------ 2 postgres postgres    96 Jun 17 17:21 pg_xact
-rw-r----- 1 postgres postgres    92 Jul  7 14:57 postgresql.auto.conf
-rw------- 1 postgres postgres 28256 Jul  7 14:57 postgresql.conf
-rw-r----- 1 postgres postgres     1 Jul  7 14:57 recovery.signal
-rw-r----- 1 postgres postgres  4648 Jun 17 16:40 server.crt
-rw------- 1 postgres postgres  1675 Jun 17 16:40 server.key
-rw-r----- 1 postgres postgres  3610 Jun 17 16:38 server.req

And the postgresql.auto.conf contains the required recovery commands:

[postgres@server data]$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
restore_command = 'cp /postgres/backup/localhost/archived_wals/%f %p'
recovery_target_timeline = latest

Once you have started your instance you can query your test table and see latest insertion is there:

[postgres@server data]$ psql --dbname=yjaquierdb
psql (13.3)
Type "help" for help.
 
yjaquierdb=# select * from test;
 id | descr
----+-------
  1 | One
  2 | Two
(2 rows)
 
yjaquierdb=#

Please not that you need to edit postgresql.conf file and restart your instance to correct this strange behavior of BART:

The BART RESTORE operation stops WAL archiving by adding an archive_mode = off parameter at the very end of the postgresql.conf file. This last parameter in the file overrides any other previous setting of the same parameter in the file. Delete the last setting and restart the database server to start WAL archiving.

# Add settings for extensions here
archive_mode = off

Barman

With the Enterprise Linux (or EPEL) YUM repository configured on your server installation is as simple as:

[root@server ~]# yum install barman-2.12-1.el7.noarch.rpm
.
.
[root@server ~]# su - barman
-bash-4.2$ id
uid=991(barman) gid=496(barman) groups=496(barman)
-bash-4.2$ barman -v
2.12
 
Barman by 2ndQuadrant (www.2ndQuadrant.com)

To test full feature (barman switch-wal) I have decided to create a PostgreSQL superuser on my test instance:

[postgres@server data]$ createuser --superuser --pwprompt --port=5433 barman
Enter password for new role:
Enter it again:
[postgres@server data]$ createuser -P --replication --port=5433 streaming_barman
Enter password for new role:
Enter it again:

Control everything is fine with:

postgres=# \du
                                       List of roles
 Role name        |                         Attributes                         | Member of
------------------+------------------------------------------------------------+-----------
 barman           | Superuser, Create role, Create DB                          | {}
 postgres         | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 streaming_barman | Replication                                                | {}

In /etc/barman.conf main configuration file I have just changed (make sure this directory is accessible in read/write by barman Linux account):

barman_home = /postgres/backup/barman

You need to create a configuration for your PostgreSQL server you plan to backup. I have decided to call it localhost as I have only one server for barman server and my PostgreSQL instance. Overall, same a Oralce RMAN, I am not a pure fan of this central backup server and I rather prefer to have one management per PostgreSQL server… So creating it from streaming template, check the official docuementation to determine if you prefer to do rsync/ssh or streaming:

[root@server ~]# cd /etc/barman.d/
[root@server barman.d]# ll
total 12
-rw-r--r-- 1 root root  947 Nov  4  2020 passive-server.conf-template
-rw-r--r-- 1 root root 1565 Nov  4  2020 ssh-server.conf-template
-rw-r--r-- 1 root root 1492 Nov  4  2020 streaming-server.conf-template
[root@server barman.d]# cp streaming-server.conf-template localhost.conf
 
[root@server barman.d]# cat localhost.conf | grep -v "^;"
 
[localhost]
description =  "My PostgreSQL 13 test instance"
 
conninfo = host=localhost user=barman dbname=postgres port=5433
 
streaming_conninfo = host=localhost user=streaming_barman dbname=postgres port=5433
 
backup_method = postgres
 
streaming_archiver = on
slot_name = barman
create_slot = auto
 
archiver = on

Remak:
archiver option is coming from a problem I had and automatic slot_name creation is to ease my life:

[barman@server ~]$ barman check localhost
Server localhost:
        empty incoming directory: FAILED ('/postgres/backup/barman/localhost/incoming' must be empty when archiver=off)

If you decide to manage slot by yourself, below caommands could be useful:

[barman@server ~]$ barman receive-wal --drop-slot localhost
Dropping physical replication slot 'barman' on server 'localhost'
Replication slot 'barman' dropped
 
[barman@server ~]$ barman receive-wal --create-slot localhost
Creating physical replication slot 'barman' on server 'localhost'
Replication slot 'barman' created
[barman@server ~]$ barman receive-wal --reset localhost
Resetting receive-wal directory status
Removing status file /postgres/backup/barman/localhost/streaming/00000001000000000000000C.partial
Creating status file /postgres/backup/barman/localhost/streaming/00000001000000000000000E.partial

Configure passwordless connection with barman and streaming_barman onto your test PostgreSQL instance with .pgpass file:

[barman@server ~]$ echo "localhost:5433:postgres:barman:barman" > ~/.pgpass
[barman@server ~]$ echo "localhost:5433:postgres:streaming_barman:barman" > ~/.pgpass
[barman@server ~]$ chmod 600 ~/.pgpass
[barman@server ~]$ psql --host=localhost --port=5433 --dbname=postgres --username=barman
psql (9.2.24, server 13.3)
WARNING: psql version 9.2, server version 13.0.
         Some psql features might not work.
Type "help" for help.
 
postgres=# \q

Check it works without prompting for password:

[barman@server ~]$ psql -U streaming_barman -h localhost --port=5433  -c "IDENTIFY_SYSTEM" replication=1
      systemid       | timeline |  xlogpos  | dbname
---------------------+----------+-----------+--------
  6982909595436191101 |        1 | 0/161E7E8 |
(1 row)

Configure authorized_keys for barman and postgres accounts… From barman you must be able to connect to your host passwordless and vice versa from postgres account…

Configure the PostgreSQL instance you plan to backup with barman with below option:

archive_mode = on
archive_command = 'barman-wal-archive localhost localhost %p'

To get barman-wal-archive command you must install Barma CLI package with:

yum install barman-cli

Remark:
The barmal-wal-archive command is a little weird, this is because I’m using the same host for barman and PostgreSQL instance. The command is: barman-wal-archive [-h] [-V] [-U USER] [-c CONFIG] [-t] BARMAN_HOST SERVER_NAME WAL_PATH. So the double localhost…

Activate streaming of WAL files, barman account must have access to pg_receivewal binary:

[barman@server .ssh]$ barman receive-wal localhost
Starting receive-wal for server localhost
ERROR: ArchiverFailure:pg_receivexlog not present in $PATH
[barman@server ~]$ export PATH=$PATH:/usr/pgsql-13/bin/
[barman@server ~]$ nohup barman receive-wal localhost &
[1] 22955
[barman@server ~]$ Starting receive-wal for server localhost
localhost: pg_receivewal: starting log streaming at 0/1000000 (timeline 1)
localhost: pg_receivewal: finished segment at 0/2000000 (timeline 1)

If your barman account is superuser on your target PostgreSQL instance you can even remotely switch WAL file with:

[barman@server ~]$ barman switch-wal --force --archive localhost
The WAL file 000000010000000000000006 has been closed on server 'localhost'
Waiting for the WAL file 000000010000000000000006 from server 'localhost' (max: 30 seconds)
localhost: pg_receivewal: finished segment at 0/7000000 (timeline 1)
Processing xlog segments from streaming for localhost
        000000010000000000000006

Perform a final check with:

[barman@server ~]$ barman check localhost
Server localhost:
        PostgreSQL: OK
        superuser or standard user with backup privileges: OK
        PostgreSQL streaming: OK
        wal_level: OK
        replication slot: 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)
        pg_basebackup: OK
        pg_basebackup compatible: OK
        pg_basebackup supports tablespaces mapping: OK
        systemid coherence: OK (no system Id stored on disk)
        pg_receivexlog: OK
        pg_receivexlog compatible: OK
        receive-wal running: OK
        archiver errors: OK
[barman@server ~]$ barman status localhost
Server localhost:
        Description: My PostgreSQL 13 test instance
        Active: True
        Disabled: False
        PostgreSQL version: 13.3
        Cluster state: in production
        pgespresso extension: Not available
        Current data size: 32.0 MiB
        PostgreSQL Data directory: /postgres/13/data
        Current WAL segment: 000000010000000000000006
        PostgreSQL 'archive_command' setting: barman-wal-archive localhost localhost %p
        Last archived WAL: 000000010000000000000005, at Fri Jul 16 11:05:16 2021
        Failures of WAL archiver: 0
        Server WAL archiving rate: 11.54/hour
        Passive node: False
        Retention policies: not enforced
        No. of available backups: 1
        First available backup: 20210716T110510
        Last available backup: 20210716T110510
        Minimum redundancy requirements: satisfied (1/0)

As usual I create a yjaquierdb database with a test table inside that contains only one row:

yjaquierdb=# select * from test;
 id | descr
----+-------
  1 | One
(1 row)

Time to perform our first backup:

[barman@server localhost]$ barman backup localhost --wait
Starting backup using postgres method for server localhost in /postgres/backup/barman/localhost/base/20210716T125713
Backup start at LSN: 0/4012760 (000000010000000000000004, 00012760)
Starting backup copy via pg_basebackup for 20210716T125713
Copy done (time: 4 seconds)
Finalising the backup.
This is the first backup for server localhost
WAL segments preceding the current backup have been found:
        000000010000000000000001 from server localhost has been removed
        000000010000000000000002 from server localhost has been removed
        000000010000000000000003 from server localhost has been removed
Backup size: 31.9 MiB
Backup end at LSN: 0/6000000 (000000010000000000000005, 00000000)
Backup completed (start time: 2021-07-16 12:57:13.538788, elapsed time: 4 seconds)
Waiting for the WAL file 000000010000000000000005 from server 'localhost'
Processing xlog segments from streaming for localhost
        000000010000000000000004
Processing xlog segments from file archival for localhost
        000000010000000000000004
        000000010000000000000005
        000000010000000000000005.00000028.backup

Confirm backup creation with:

[barman@server localhost]$ barman list-backup localhost
localhost 20210716T125713 - Fri Jul 16 12:57:18 2021 - Size: 47.9 MiB - WAL Size: 0 B
[barman@server localhost]$ barman show-backup localhost 20210716T125713
Backup 20210716T125713:
  Server Name            : localhost
  System Id              : 6985474264206038247
  Status                 : DONE
  PostgreSQL Version     : 130003
  PGDATA directory       : /postgres/13/data
 
  Base backup information:
    Disk usage           : 31.9 MiB (47.9 MiB with WALs)
    Incremental size     : 31.9 MiB (-0.00%)
    Timeline             : 1
    Begin WAL            : 000000010000000000000005
    End WAL              : 000000010000000000000005
    WAL number           : 1
    Begin time           : 2021-07-16 12:57:16+02:00
    End time             : 2021-07-16 12:57:18.404710+02:00
    Copy time            : 4 seconds
    Estimated throughput : 6.6 MiB/s
    Begin Offset         : 40
    End Offset           : 0
    Begin LSN           : 0/5000028
    End LSN             : 0/6000000
 
  WAL information:
    No of files          : 0
    Disk usage           : 0 B
    Last available       : 000000010000000000000005
 
  Catalog information:
    Retention Policy     : not enforced
    Previous Backup      : - (this is the oldest base backup)
    Next Backup          : - (this is the latest base backup)

I insert a new row in my test table and then I crash and delete entirely the PostgreSQL instance and objective will be to perform a PITR recovery and get the latest inserted row in the table.

yjaquierdb=# insert into test values(2,'Two');
INSERT 0 1
yjaquierdb=# select * from test;
 id | descr
----+-------
  1 | One
  2 | Two
(2 rows)

Latest modification go inside the streaming directory of your barman backup (barman receive-wal command):

[barman@server localhost]$ pwd
/postgres/backup/barman/localhost
[barman@server localhost]$ ll streaming
total 32768
-rw------- 1 barman barman 16777216 Jul 16 13:02 000000010000000000000006.partial
[barman@server localhost]$ ll wals/0000000100000000/
total 16385
-rw-r----- 1 barman barman 16777216 Jul 16 12:57 000000010000000000000005
-rw-r----- 1 barman barman      339 Jul 16 12:57 000000010000000000000005.00000028.backup

Confirmation in PostgreSQL instance:

[postgres@server data]$ ll -rt pg_wal/
total 49162
-rw------- 1 postgres postgres 16777216 Jul 16 12:57 000000010000000000000007
-rw------- 1 postgres postgres 16777216 Jul 16 12:57 000000010000000000000008
-rw------- 1 postgres postgres      339 Jul 16 12:57 000000010000000000000005.00000028.backup
drwx------ 2 postgres postgres     1024 Jul 16 13:02 archive_status
-rw------- 1 postgres postgres 16777216 Jul 16 13:02 000000010000000000000006

I have knocked my head on the wall for a long time before finding why the partial streamed WAL file are not included in restore command, this is a current (huge) limitation of barman:

IMPORTANT: A current limitation of Barman is that the recover command is not yet able to transparently manage .partial files. In such situations, users will need to manually copy the latest partial file from the server’s streaming_wals_directory of their Barman installation to the destination for recovery, making sure that the .partial suffix is removed. Restoring a server using the last partial file, reduces data loss, by bringing down recovery point objective to values around 0, or exactly 0 in case of synchronous replication.

Even if you move it to archive directory barman does not take it into account:

[barman@server localhost]$ barman get-wal --partial localhost 000000010000000000000006 --output-directory wals/0000000100000000/
Sending WAL '000000010000000000000006' for server 'localhost' into 'wals/0000000100000000/000000010000000000000006' file
[barman@server localhost]$ ll wals/0000000100000000/
total 16385
-rw-r----- 1 barman barman 16777216 Jul 16 12:57 000000010000000000000005
-rw-r----- 1 barman barman      339 Jul 16 12:57 000000010000000000000005.00000028.backup
-rw-r----- 1 barman barman        0 Jul 16 15:24 000000010000000000000006

I have killed the PostgreSQL instance, remove (rm -rf *) the PGDATA directory, now time to restore and recover.

Remark:
If you use the traditional “barman recover localhost 20210716T125713 /postgres/13/data” command then target directory will be owned by barman (and you might have error when barman will try to write to it). Once recovered, final directory is owned by barman account so you would have to change ownership with root account to postgres account before starting the instance. One nice trick is to use –remote-ssh-command “ssh postgres@localhost” option of barman recover and this will be postgres account that will write everything (and remember we configured passwordless access from barman).

To map PosgreSQL recovery I have first tried with:

[barman@server localhost]$ barman recover --remote-ssh-command "ssh postgres@localhost" --target-tli 'latest' localhost 20210716T110510 /postgres/13/data
usage: barman recover [-h] [--target-tli TARGET_TLI]
                      [--target-time TARGET_TIME] [--target-xid TARGET_XID]
                      [--target-lsn TARGET_LSN] [--target-name TARGET_NAME]
                      [--target-immediate] [--exclusive]
                      [--tablespace NAME:LOCATION]
                      [--remote-ssh-command SSH_COMMAND] [--bwlimit KBPS]
                      [--retry-times RETRY_TIMES] [--retry-sleep RETRY_SLEEP]
                      [--no-retry] [--jobs NJOBS] [--get-wal] [--no-get-wal]
                      [--network-compression] [--no-network-compression]
                      [--target-action TARGET_ACTION] [--standby-mode]
                      server_name backup_id destination_directory
barman recover: error: argument --target-tli: 'latest' is not a valid positive integer

So finally decided to use below command. I don’t know by heart any recovery time or LSN number and I just want to recover with smallest RPO:

[barman@server localhost]$ barman recover --remote-ssh-command "ssh postgres@localhost" --target-tli 99999999 localhost 20210716T125713 /postgres/13/data
Starting remote restore for server localhost using backup 20210716T125713
Destination directory: /postgres/13/data
Remote command: ssh postgres@localhost
Doing PITR. Recovery target timeline: 'True'
Using safe horizon time for smart rsync copy: 2021-07-16 12:57:16+02:00
Copying the base backup.
Copying required WAL segments.
Generating recovery configuration
Identify dangerous settings in destination directory.
 
IMPORTANT
These settings have been modified to prevent data losses
 
postgresql.conf line 237: archive_command = false
 
Recovery completed (start time: 2021-07-16 15:25:03.033741, elapsed time: 4 seconds)
 
Your PostgreSQL server has been successfully prepared for recovery!

Copy streamed .partial file to $PGDATA/barman_wal/ directory removing the .partial extention…

Change recovery_target_timeline from 99999999 to ‘latest’ in postgresql.auto.conf file:

[postgres@server data]$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
restore_command = 'cp barman_wal/%f %p'
recovery_end_command = 'rm -fr barman_wal'
recovery_target_timeline = latest

Just start the PosgreSQL instance and all should be there (you must bounce it again to reconfigure archive_command parameter that has been overwritten). And restart barman receive-wal command:

[postgres@server data]$ psql --dbname=yjaquierdb
psql (13.3)
Type "help" for help.
 
yjaquierdb=# select * from test;
 id | descr
----+-------
  1 | One
  2 | Two
(2 rows)

To be continued…

  1. pg_rman
  2. WAL-E

References

Yannick Jaquier on LinkedinYannick Jaquier on RssYannick Jaquier on Twitter
Yannick Jaquier
Find more about me on social media.

One thought on “PostgreSQL backup and restore tools comparison for PITR recovery

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>