Recover your database with Barman

In the previous tutorial you should have been able to setup a regular backup of your database using barman. We will now recover that backup and apply it to your database.

List your backups

On your backup server, server-b, connect as user barman. By running the following command you should list the backups that are available on the backup server for server-a.

$ barman list-backup server-a
server-a 20180722T233002 — Sun Jul 22 23:30:05 2018 — Size: 31.1 MiB — WAL Size: 0 B
server-a 20180721T233002 — Sat Jul 21 23:30:05 2018 — Size: 36.8 MiB — WAL Size: 1.7 MiB
server-a 20180720T233002 — Fri Jul 20 23:30:05 2018 — Size: 35.9 MiB — WAL Size: 1.4 MiB
server-a 20180719T233001 — Thu Jul 19 23:30:05 2018 — Size: 34.9 MiB — WAL Size: 1.4 MiB
server-a 20180718T233001 — Wed Jul 18 23:30:05 2018 — Size: 34.0 MiB — WAL Size: 1.5 MiB
server-a 20180717T233002 — Tue Jul 17 23:30:05 2018 — Size: 33.0 MiB — WAL Size: 1.4 MiB
server-a 20180716T233002 — Mon Jul 16 23:30:05 2018 — Size: 32.1 MiB — WAL Size: 1.4 MiB
server-a 20180715T233002 — Sun Jul 15 23:30:05 2018 — Size: 31.1 MiB — WAL Size: 1.4 MiB

Choose a backup were you want to recover from. For this example, let’s pick the last one 20180722T233002 and display some information:

barman show-backup server-a 20180722T233002
Backup 20180722T233002:
Server Name : server-a
Status : DONE
PostgreSQL Version : 100004
PGDATA directory : /pg/data/directory
Base backup information:
Disk usage : 31.1 MiB (31.1 MiB with WALs)
Incremental size : 1.2 MiB (-96.27%)
Timeline : 1
Begin WAL : 00000001000000000000001E
End WAL : 00000001000000000000001E
WAL number : 1
WAL compression ratio: 99.84%
Begin time : 2018–07–22 23:30:02.581713+00:00
End time : 2018–07–22 23:30:05.588245+00:00
Copy time : 1 second + 1 second startup
Estimated throughput : 762.7 KiB/s
Begin Offset : 40
End Offset : 248
Begin LSN : 0/1E000028
End LSN : 0/1E0000F8
WAL information:
No of files : 0
Disk usage : 0 B
Last available : 00000001000000000000001E
Catalog information:
Retention Policy : VALID
Previous Backup : 20180721T233002
Next Backup : — (this is the latest base backup)

If we want to use this backup, note the begin and end time and you can recover from the any time in between. For this example will pick up the end time.

Start your recovery

Let’s start by simulating a shutdown. At this point recovering from a shutdown or a crash won’t be much difference because the recovery will erase your PostgreSQL data directory.

On server-a, stop the PostgreSQL service and run on server-b. This will erase the specified $PGDATAdirectory.

barman recover \
  --remote-ssh-command “ssh postgres@server-a” \
  --target-time=”2018–07–22 23:30:05.588245+00:00" \
  server-a 20180722T233002 $PGDATA

$PGDATAis your PostgreSQL data directory on server-a. Barman recovery will create a fully functional PostgreSQL data directory, copy the specified base backup and the required WAL files on to it. This will put the database in recovery mode. Connect onto server-a, barman has created a recovery.conf file that will be used by PostgreSQL for the recovery.

$ cat $PGDATA/recover.conf
restore_command = ‘cp barman_xlog/%f %p’
recovery_end_command = ‘rm -fr barman_xlog’
recovery_target_time = ‘2018–07–22 23:30:05.588245+00:00’

You can see the the WAL files (formerly called XLOG) are copied to the barman_xlog folder. The restore_command will reapply those WAL files on startup up to the recovery target time. Once this is finished, the server will run the recovery_end_command and rename the recovery.conf file to recovery.done so that you can start your database in normal mode. Otherwise the database will continuously start in recovery mode.

Don’t hesitate to play with the parameters, target time for instance or use other target method (e.g. transaction id).

Congratulations you have successfully recovered your database using Barman. In next tutorial we will explore how to implement replication with Barman and Repmgr.