EDB - Barman - Part IV: Remote Recovery Database

 Note:

• The recover command is used to recover a whole server.
• Remote recovery - Add the --remote-ssh-command option to the invocation of the recovery command. Doing this will allow Barman to execute the copy on a remote server, using the provided command to connect to the remote host. The SSH connection between Barman and the remote host must use the public key exchange authentication method.
• Barman wraps PostgreSQL's Point-in-Time Recovery (PITR), allowing you to specify a recovery target, either as a timestamp, as a restore label, or as a transaction ID.
• Barman 2.4 also adds the --standby-mode option for the recover command which, if specified, properly configures the recovered instance as a standby by creating a standby.signal file (from PostgreSQL versions lower than 12), or by adding standby_mode = on to the generated recovery configuration.
• Barman allows you to specify a target timeline for recovery using the --target-tli option. This can be set to a numeric timeline ID or one of the special values latest (to recover to the most recent timeline in the WAL archive) and current (to recover to the timeline which was current when the backup was taken). If this option is omitted then PostgreSQL versions 12 and above will recover to the latest timeline and PostgreSQL versions below 12 will recover to the current timeline.

1. Check backups

[barman@barman-server ~]$ barman list-backup employees4streaming
employees4streaming 20240614T102115 - Fri Jun 14 10:21:22 2024 - Size: 979.7 MiB - WAL Size: 0 B (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)
employees4streaming 20240613T161509 - Thu Jun 13 16:15:17 2024 - Size: 960.1 MiB - WAL Size: 9.8 MiB (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)
employees4streaming 20240613T153802 - Thu Jun 13 15:38:09 2024 - Size: 921.1 MiB - WAL Size: 19.2 MiB (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)
 
[barman@barman-server ~]$ ls -la /barman/backup/employees4streaming/incoming/
[barman@barman-server ~]$ ls -la /barman/backup/employees4streaming/streaming/
[barman@barman-server ~]$ ls -la /barman/backup/employees4streaming/wals/0000000100000001/
total 29768
drwxr-xr-x. 2 barman barman    4096 Jun 14 10:22 .
drwxr-xr-x. 3 barman barman      45 Jun 14 10:22 ..
-rw-------. 1 barman barman   16467 Jun 13 15:38 000000010000000100000004
-rw-------. 1 barman barman 5838855 Jun 13 15:50 000000010000000100000005
-rw-------. 1 barman barman 5812523 Jun 13 16:14 000000010000000100000006
-rw-------. 1 barman barman 5846212 Jun 13 16:14 000000010000000100000007
-rw-------. 1 barman barman 2635336 Jun 13 16:15 000000010000000100000008
-rw-------. 1 barman barman   16469 Jun 13 16:15 000000010000000100000009
-rw-------. 1 barman barman   16393 Jun 13 16:15 00000001000000010000000A
-rw-------. 1 barman barman   16570 Jun 13 16:48 00000001000000010000000B
-rw-------. 1 barman barman 5838885 Jun 14 09:58 00000001000000010000000C
-rw-------. 1 barman barman 4225404 Jun 14 10:16 00000001000000010000000D
-rw-------. 1 barman barman   16490 Jun 14 10:16 00000001000000010000000E
-rw-------. 1 barman barman   16486 Jun 14 10:17 00000001000000010000000F
-rw-------. 1 barman barman   16489 Jun 14 10:17 000000010000000100000010
-rw-------. 1 barman barman   16485 Jun 14 10:19 000000010000000100000011
-rw-------. 1 barman barman   16488 Jun 14 10:19 000000010000000100000012
-rw-------. 1 barman barman   16488 Jun 14 10:20 000000010000000100000013
-rw-------. 1 barman barman   16489 Jun 14 10:20 000000010000000100000014
-rw-------. 1 barman barman   16484 Jun 14 10:21 000000010000000100000015
-rw-------. 1 barman barman   16465 Jun 14 10:21 000000010000000100000016
 
[barman@barman-server ~]$ barman show-backup employees4streaming 20240613T161509
Backup 20240613T161509:
  Server Name            : employees4streaming
  System Id              : 7377775620992307471
  Status                 : DONE
  PostgreSQL Version     : 160003
  PGDATA directory       : /u01/edb/as16/data
 
  Tablespaces:
    tbs_ihrp_data        : /u01/ihrp/data (oid: 16385)
    tbs_ihrp_index       : /u01/ihrp/index (oid: 16386)
 
  Base backup information:
    Disk usage           : 960.1 MiB (960.1 MiB with WALs)
    Incremental size     : 960.1 MiB (-0.00%)
    Timeline             : 1
    Begin WAL            : 000000010000000100000009
    End WAL              : 00000001000000010000000A
    WAL number           : 2
    WAL compression ratio: 99.90%
    Begin time           : 2024-06-13 16:15:09.939720+07:00
    End time             : 2024-06-13 16:15:17.219746+07:00
    Copy time            : 7 seconds
    Estimated throughput : 132.1 MiB/s
    Begin Offset         : 40
    End Offset           : 96
    Begin LSN            : 1/9000028
    End LSN              : 1/A000060
 
  WAL information:
    No of files          : 12
    Disk usage           : 9.8 MiB
    WAL rate             : 0.77/hour
    Compression ratio    : 94.92%
    Last available       : 000000010000000100000016
 
  Catalog information:
    Retention Policy     : VALID
    Previous Backup      : 20240613T153802
    Next Backup          : 20240614T102115
 

2. Simulation for Demo

On Database Server (simulation - DO NOT EXCUTE on Production)
- Stop Database

[root@edb-saigon ~]# systemctl stop edb-as-16.service

 

- Delete data

[enterprisedb@edb-saigon ~]$ cd /u01/edb/as16

[enterprisedb@edb-saigon as16]$ mv data data.01

[enterprisedb@edb-saigon as16]$ mkdir data

[enterprisedb@edb-saigon as16]$ chmod -R 700 data

[enterprisedb@edb-saigon as16]$ ls -l

total 4
drwx------.  2 enterprisedb enterprisedb    6 Jun 14 10:59 data
drwx------. 21 enterprisedb enterprisedb 4096 Jun 14 10:58 data.01

3. Restore Database

On Barman Server

[barman@barman-server ~]$ barman recover --remote-ssh-command "ssh enterprisedb@edb-saigon" employees4streaming 20240614T102115  /u01/edb/as16/data
The authenticity of host 'edb-saigon (192.168.56.102)' can't be established.
ED25519 key fingerprint is SHA256:YUt/QBsk8CSX4T4E8D6J+qIowjM+hv0uxeCl2Cq+PPY.
This host key is known by the following other names/addresses:
    ~/.ssh/known_hosts:1: 192.168.56.102
    ~/.ssh/known_hosts:4: edb-saigon.taolaoxibup.com
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Starting remote restore for server employees4streaming using backup 20240614T102115
Destination directory: /u01/edb/as16/data
Remote command: ssh enterprisedb@edb-saigon
        16385, tbs_ihrp_data, /u01/ihrp/data
        16386, tbs_ihrp_index, /u01/ihrp/index
Copying the base backup.
Copying required WAL segments.
Generating archive status files
Identify dangerous settings in destination directory.
 
IMPORTANT
These settings have been modified to prevent data losses
 
postgresql.conf line 280: archive_command = false è Re-configure archive_command after start database

 
Recovery completed (start time: 2024-06-14 11:02:10.507329+07:00, elapsed time: 18 seconds)
Your PostgreSQL server has been successfully prepared for recovery!
 
On Database Server

[root@edb-saigon ~]# systemctl start edb-as-16.service
[root@edb-saigon ~]# systemctl status edb-as-16.service
● edb-as-16.service - EDB Postgres Advanced Server 16
     Loaded: loaded (/usr/lib/systemd/system/edb-as-16.service; enabled; preset: disabled)
     Active: active (running) since Fri 2024-06-14 11:03:30 +07; 5s ago
    Process: 14364 ExecStartPre=/usr/edb/as16/bin/edb-as-16-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 14369 (edb-postgres)
      Tasks: 9 (limit: 48917)
     Memory: 72.0M
        CPU: 329ms
     CGroup: /system.slice/edb-as-16.service
             ├─14369 /usr/edb/as16/bin/edb-postgres -D /u01/edb/as16/data
             ├─14370 "postgres: logger "
             ├─14371 "postgres: checkpointer "
             ├─14372 "postgres: background writer "
             ├─14374 "postgres: walwriter "
             ├─14375 "postgres: autovacuum launcher "
             ├─14376 "postgres: archiver "
             ├─14377 "postgres: dbms_aq launcher "
             └─14378 "postgres: logical replication launcher "
 
Jun 14 11:03:29 edb-saigon systemd[1]: Starting EDB Postgres Advanced Server 16...
Jun 14 11:03:29 edb-saigon edb-postgres[14369]: 2024-06-14 11:03:29 +07 LOG:  redirecting log output to logging collector process
Jun 14 11:03:29 edb-saigon edb-postgres[14369]: 2024-06-14 11:03:29 +07 HINT:  Future log output will appear in directory "log".
Jun 14 11:03:30 edb-saigon systemd[1]: Started EDB Postgres Advanced Server 16.
 
edb=# \c employees cherry
You are now connected to database "employees" as user "cherry".
employees=> set search_path = employees ;
SET
employees=> \dt+
                                             List of relations
  Schema   |        Name         | Type  | Owner  | Persistence | Access method |    Size    | Description
-----------+---------------------+-------+--------+-------------+---------------+------------+-------------
 employees | department          | table | cherry | permanent   | heap          | 8192 bytes |
 employees | department_employee | table | cherry | permanent   | heap          | 17 MB      |
 employees | department_manager  | table | cherry | permanent   | heap          | 8192 bytes |
 employees | employee            | table | cherry | permanent   | heap          | 20 MB      |
 employees | salary              | table | cherry | permanent   | heap          | 142 MB     |
 employees | temp01              | table | cherry | permanent   | heap          | 20 MB      |
 employees | temp02              | table | cherry | permanent   | heap          | 20 MB      |
 employees | temp03              | table | cherry | permanent   | heap          | 20 MB      |
 employees | temp04              | table | cherry | permanent   | heap          | 20 MB      |
 employees | temp05              | table | cherry | permanent   | heap          | 20 MB      |
 employees | temp06              | table | cherry | permanent   | heap          | 20 MB      |
 employees | title               | table | cherry | permanent   | heap          | 25 MB      |
(12 rows)
 
Table employees.temp06 has just created before the backup 20240614T102115. Therefore, we have restored database to the newest status.

Point in time recovery

The recovery target can be specified using one of the following mutually exclusive options:

• --target-time TARGET_TIME: to specify a timestamp
• --target-xid TARGET_XID: to specify a transaction ID
• --target-lsn TARGET_LSN: to specify a Log Sequence Number (LSN) - requires PostgreSQL 10 or higher
• --target-name TARGET_NAME: to specify a named restore point previously created with the pg_create_restore_point(name) function
• --target-immediate: recovery ends when a consistent state is reached (that is the end of the base backup process)

On Barman server

[barman@barman-server ~]$ barman list-backup employees4streaming
employees4streaming 20240614T131133 - Fri Jun 14 13:11:40 2024 - Size: 940.8 MiB - WAL Size: 16.1 KiB (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)
employees4streaming 20240614T130751 - Fri Jun 14 13:07:58 2024 - Size: 921.2 MiB - WAL Size: 19.3 MiB (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)
employees4streaming 20240614T125608 - Fri Jun 14 12:56:23 2024 - Size: 979.8 MiB - WAL Size: 19.1 MiB (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)
 
[barman@barman-server ~]$ barman recover --remote-ssh-command "ssh enterprisedb@edb-saigon" employees4streaming 20240614T131133  /u01/edb/as16/data --target-time '2024-11-14 13:10:00'
Starting remote restore for server employees4streaming using backup 20240614T131133
Destination directory: /u01/edb/as16/data
Remote command: ssh enterprisedb@edb-saigon
Doing PITR. Recovery target time: '2024-11-14 13:10:00+07:00'
        16385, tbs_ihrp_data, /u01/ihrp/data
        16386, tbs_ihrp_index, /u01/ihrp/index
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 280: archive_command = false
 
Recovery completed (start time: 2024-06-14 13:32:48.788391+07:00, elapsed time: 21 seconds)
Your PostgreSQL server has been successfully prepared for recovery!

On Darabase server

• Remove recovery.signal files
• Re-config archive_command

[root@edb-saigon ~]# systemctl start edb-as-16.service
[root@edb-saigon ~]# systemctl status edb-as-16.service
● edb-as-16.service - EDB Postgres Advanced Server 16
     Loaded: loaded (/usr/lib/systemd/system/edb-as-16.service; enabled; preset: disabled)
     Active: active (running) since Fri 2024-06-14 13:34:52 +07; 10s ago
    Process: 24382 ExecStartPre=/usr/edb/as16/bin/edb-as-16-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 24387 (edb-postgres)
      Tasks: 9 (limit: 48917)
     Memory: 72.3M
        CPU: 448ms
     CGroup: /system.slice/edb-as-16.service
             ├─24387 /usr/edb/as16/bin/edb-postgres -D /u01/edb/as16/data
             ├─24388 "postgres: logger "
             ├─24389 "postgres: checkpointer "
             ├─24390 "postgres: background writer "
             ├─24394 "postgres: walwriter "
             ├─24395 "postgres: autovacuum launcher "
             ├─24396 "postgres: archiver "
             ├─24397 "postgres: dbms_aq launcher "
             └─24398 "postgres: logical replication launcher "
 
Jun 14 13:34:49 edb-saigon systemd[1]: Starting EDB Postgres Advanced Server 16...
Jun 14 13:34:49 edb-saigon edb-postgres[24387]: 2024-06-14 13:34:49 +07 LOG:  redirecting log output to logging collector process
Jun 14 13:34:49 edb-saigon edb-postgres[24387]: 2024-06-14 13:34:49 +07 HINT:  Future log output will appear in directory "log".
Jun 14 13:34:52 edb-saigon systemd[1]: Started EDB Postgres Advanced Server 16.

EDB - Barman - Part I: Installation and Configuration
EDB - Barman - Part II: Backup via rsync/SSH

EDB - Barman - Part III: Backup via Streaming protocol

Ref:

• Barman Manual (pgbarman.org)
• EDB Docs - Barman (enterprisedb.com)