Barman恢复命令，不要将“所有”WAL文件从Barman服务器复制到备用数据库服务器。

Question

没有传输这些WAL文件的recover命令有什么问题？

barman recover --target-time "2017-05-16 16:39:02.235780+00:00" \
--remote-ssh-command "ssh postgres@52.212.62.91" \
main-db latest /var/lib/postgresql/9.4/main

这是我的过程..。

主数据库是关闭的(模拟故障)，但是最近有一个备份，WAL文件已经从主db服务器上发送。

barman@ip-172-30-2-77:~/main-db$ barman check main-db
Server main-db:
    PostgreSQL: FAILED
    directories: OK
    retention policy settings: OK
    backup maximum age: OK (interval provided: 1 day, latest backup age: 1 hour, 3 minutes, 46 seconds)
    compression settings: OK
    failed backups: OK (there are 0 failed backups)
    minimum redundancy requirements: OK (have 1 backups, expected at least 0)
    ssh: OK (PostgreSQL server)
    not in recovery: OK
    archiver errors: OK

在Barman服务器上，我们可以看到自上次barman backup main-db运行以来存档的6个WAL文件。

barman@ip-172-30-2-77:~/main-db$ ls -lah
total 22M
drwxrwxr-x 2 barman barman 4.0K May 16 17:08 .
drwxrwxr-x 3 barman barman 4.0K May 16 17:08 ..
-rw------- 1 barman barman  28K May 16 16:39 0000000100000001000000E2
-rw------- 1 barman barman  204 May 16 16:39 0000000100000001000000E2.00000090.backup
-rw------- 1 barman barman  84K May 16 16:44 0000000100000001000000E3
-rw------- 1 barman barman  37K May 16 16:49 0000000100000001000000E4
-rw------- 1 barman barman  30K May 16 16:54 0000000100000001000000E5
-rw------- 1 barman barman 8.9M May 16 16:58 0000000100000001000000E6
-rw------- 1 barman barman 9.1M May 16 16:59 0000000100000001000000E7
-rw------- 1 barman barman 2.6M May 16 17:04 0000000100000001000000E8
-rw------- 1 barman barman 543K May 16 17:07 0000000100000001000000E9

现在，我将运行recover命令，通过使用最新的backup + WAL文件来恢复备用数据库服务器，如下所示：

barman@ip-172-30-2-77:~/main-db$ barman list-server
main-db - Main DB Server
standby-db - Standby DB Server

barman@ip-172-30-2-77:~/main-db$ barman list-backup main-db
main-db 20170516T163617 - Tue May 16 16:39:02 2017 - Size: 4.0 GiB - WAL Size: 21.1 MiB

barman@ip-172-30-2-77:~/main-db$ barman show-backup main-db 20170516T163617
Backup 20170516T163617:
  Server Name            : main-db
  Status                 : DONE
  PostgreSQL Version     : 90411
  PGDATA directory       : /var/lib/postgresql/9.4/main

  Base backup information:
    Disk usage           : 4.0 GiB (4.0 GiB with WALs)
    Incremental size     : 4.0 GiB (-0.00%)
    Timeline             : 1
    Begin WAL            : 0000000100000001000000E2
    End WAL              : 0000000100000001000000E2
    WAL number           : 1
    WAL compression ratio: 99.83%
    Begin time           : 2017-05-16 16:36:17.369993+00:00
    End time             : 2017-05-16 16:39:02.235780+00:00
    Begin Offset         : 144
    End Offset           : 4912
    Begin XLOG           : 1/E2000090
    End XLOG             : 1/E2001330

  WAL information:
    No of files          : 7
    Disk usage           : 21.1 MiB
    WAL rate             : 16.91/hour
    Compression ratio    : 81.21%
    Last available       : 0000000100000001000000E9

  Catalog information:
    Retention Policy     : VALID
    Previous Backup      : - (this is the oldest base backup)
    Next Backup          : - (this is the latest base backup)

barman@ip-172-30-2-77:~/main-db$ barman recover --target-time "2017-05-16 16:39:02.235780+00:00" \
--remote-ssh-command "ssh postgres@52.212.62.91" \
main-db latest /var/lib/postgresql/9.4/main

Starting remote restore for server main-db using backup 20170516T163617
Destination directory: /var/lib/postgresql/9.4/main/
Doing PITR. Recovery target time: '2017-05-16 16:39:02.235780+00:00'
Copying the base backup.
Copying required WAL segments.
Generating recovery.conf
Your PostgreSQL server has been successfully prepared for recovery!

现在重点关注备用数据库服务器上的Postgresql数据目录(/var/lib/postgresql/9.4/main)。

postgres@ip-172-30-0-66:~/9.4/main$ pwd
/var/lib/postgresql/9.4/main
postgres@ip-172-30-0-66:~/9.4/main$ ls
backup_label  pg_hba.conf    pg_replslot   pg_tblspc            postgresql.conf
barman_xlog   pg_ident.conf  pg_serial     pg_twophase          postgresql.conf.origin
base          pg_log         pg_snapshots  PG_VERSION           recovery.conf
global        pg_logical     pg_stat       pg_xlog
pg_clog       pg_multixact   pg_stat_tmp   postgresql.auto.conf
pg_dynshmem   pg_notify      pg_subtrans   postgresql.auto.conf.origin
postgres@ip-172-30-0-66:~/9.4/main$ ls barman_xlog/
0000000100000001000000E2  0000000100000001000000E2.00000090.backup

我们可以看到，下面的WAL文件中没有一个是使用recover命令传输的。

• 00000001000001000000E3
• 00000001000001000000E4
• 00000001000001000000E5
• 00000001000001000000E6
• 00000001000001000000E7
• 00000001000001000000E8
• 00000001000001000000E9

但是，我可以使用barman-cli的barman-restore-wal命令来提取它们。因此，这告诉我，它们在酒吧服务员服务器上是绝对可用的。下面是我用来还原WAL文件的recovery.conf文件。

root@ip-172-30-0-66:/var/lib/postgresql/9.4/maincat recovery.conf
The 'barman-wal-restore' command is provided in the 'barman-cli' package
standby_mode = 'on'
trigger_file = '/var/lib/postgresql/9.4/trigger'
restore_command = 'barman-wal-restore 52.51.36.41 main-db %f %p'

现在我们可以看到所有的WAL文件都是从酒保服务器上提取出来的。

root@ip-172-30-0-66:/var/lib/postgresql/9.4/mainls -lah pg_xlog/
total 129M
drwx------  3 postgres postgres 4.0K May 16 17:59 .
drwx------ 19 postgres postgres 4.0K May 16 17:58 ..
-rw-------  1 postgres postgres  16M May 16 17:56 0000000100000001000000E2
-rw-rw-r--  1 postgres postgres  324 May 16 17:54 0000000100000001000000E2.00000090.backup
-rw-------  1 postgres postgres  16M May 16 17:56 0000000100000001000000E3
-rw-------  1 postgres postgres  16M May 16 17:56 0000000100000001000000E4
-rw-------  1 postgres postgres  16M May 16 17:56 0000000100000001000000E5
-rw-------  1 postgres postgres  16M May 16 17:56 0000000100000001000000E6
-rw-------  1 postgres postgres  16M May 16 17:56 0000000100000001000000E7
-rw-------  1 postgres postgres  16M May 16 17:56 0000000100000001000000E8
-rw-------  1 postgres postgres  16M May 16 17:56 0000000100000001000000E9
drwxrwxr-x  2 postgres postgres 4.0K May 16 17:56 archive_status
-rw-------  1 postgres postgres    0 May 16 17:59 RECOVERYXLOG

Answer 1

您的目标时间应该是"2017-05-16 17:08“(最后一次Wal文件从ls命令接收到的时间)，而不是恢复命令中最新备份的结束时间，以便在备份完成后恢复wal文件。目标时间是备份结束时间之后的时间。如果要在备份的开始时间和结束时间之间恢复任何时间，则应该使用前面的备份。酒保只恢复到恢复命令中指定的时间。