postgresql 物理备份 barman 之 streaming backup
os: ubuntu 16.04
postgresql: 9.6.8
barman: 2.5
ip 规划
192.168.56.101 node1 barman
192.168.56.102 node2 postgresql
barman 是2ndquadrant推出的一款 postgresql 开源备份软件，官网介绍的非常强大。
barman 建议以一个 dedicated server 存在。尽量不要部署到 postgresql server 上，但不是不能部署到 postgresql server 主机上。
streaming
/etc/barman.d/dn1-streaming.conf
# egrep ^[^";"] /etc/barman.d/dn1-streaming.conf
[dn1_streaming]
description = "dn1 PostgreSQL Database (Streaming-Only)"
conninfo = host=192.168.56.102 user=barman dbname=postgres
streaming_conninfo = host=192.168.56.102 user=streaming_barman
backup_method = postgres
streaming_archiver = on
slot_name = barman
node1 上手动确认 streaming_barman 流复制是否正常
# psql -h 192.168.56.102 -U streaming_barman -c "IDENTIFY_SYSTEM" replication=1
Password for user streaming_barman:
systemid | timeline | xlogpos | dbname
---------------------+----------+-----------+--------
6606810159213842539 | 1 | 0/40007F8 |
(1 row)
node2 上确认 max_wal_senders，max_replication_slots 设置了较大值
postgres=# show max_wal_senders;
max_wal_senders
-----------------
10
(1 row)
postgres=# show max_replication_slots;
max_replication_slots
-----------------------
10
(1 row)
查看定义的 server_name
barman@nodex:~$ barman show-server all
barman@nodex:~$ barman check dn1_streaming
Server dn1_streaming:
PostgreSQL: OK
is_superuser: 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
pg_receivexlog: OK
pg_receivexlog compatible: OK
receive-wal running: OK
archiver errors: OK
确保check的结果都是OK的，有错误可以根据提示修正错误。
barman@nodex:~$ barman receive-wal --create-slot dn1_streaming
barman@nodex:~$ ps -ef|grep -i post
barman 3808 3805 0 10:36 ? 00:00:00 /usr/lib/postgresql/9.6/bin/pg_receivexlog --dbname=dbname=replication host=192.168.56.102 options=-cdatestyle=iso replication=true user=streaming_barman application_name=barman_receive_wal --verbose --no-loop --no-password --directory=/var/lib/barman/dn1_streaming/streaming --slot=barman
barman@nodex:~$ pwd
/var/lib/barman
barman@nodex:~$ tree ./dn1_streaming/
./dn1_streaming/
├── base
├── errors
├── incoming
├── streaming
│ └── 00000001000000000000000D.partial
└── wals
├── 0000000100000000
│ └── 00000001000000000000000C
└── xlog.db
6 directories, 3 files
pg_receivexlog 就是用来实时接收master产生的wal。
参考：http://postgres.cn/docs/9.6/app-pgreceivexlog.html
barman@nodex:~$ barman backup dn1_streaming
Starting backup using postgres method for server dn1_streaming in /var/lib/barman/dn1_streaming/base/20181008T105056
Backup start at LSN: 0/D000140 (00000001000000000000000D, 00000140)
Starting backup copy via pg_basebackup for 20181008T105056
WARNING: pg_basebackup does not copy the PostgreSQL configuration files that reside outside PGDATA. Please manually backup the following files:
/etc/postgresql/9.6/main/postgresql.conf
/etc/postgresql/9.6/main/pg_hba.conf
/etc/postgresql/9.6/main/pg_ident.conf
Copy done (time: 3 seconds)
Finalising the backup.
This is the first backup for server dn1_streaming
WAL segments preceding the current backup have been found:
00000001000000000000000C from server dn1_streaming has been removed
Backup size: 21.2 MiB
Backup end at LSN: 0/F000000 (00000001000000000000000E, 00000000)
Backup completed (start time: 2018-10-08 10:50:56.112068, elapsed time: 3 seconds)
barman@nodex:~$ barman status dn1_streaming
Server dn1_streaming:
Description: dn1 PostgreSQL Database (Streaming-Only)
Active: True
Disabled: False
PostgreSQL version: 9.6.8
Cluster state: in production
pgespresso extension: Not available
Current data size: 21.0 MiB
PostgreSQL Data directory: /data/pg9.6/main
Current WAL segment: 00000001000000000000000F
Retention policies: not enforced
No. of available backups: 1
First available backup: 20181008T105056
Last available backup: 20181008T105056
Minimum redundancy requirements: satisfied (1/0)
barman@nodex:~$ barman replication-status dn1_streaming
Status of streaming clients for server 'dn1_streaming':
Current LSN on master: 0/F000100
Number of streaming clients: 1
1. Async WAL streamer
Application name: barman_receive_wal
Sync stage : 3/3 Remote write
Communication : TCP/IP
IP Address : 192.168.56.101 / Port: 50208 / Host: -
User name : streaming_barman
Current state : streaming (async)
Replication slot: barman
WAL sender PID : 3238
Started at : 2018-10-08 11:06:02.110442+08:00
Sent LSN : 0/F000100 (diff: 0 B)
Write LSN : 0/F000100 (diff: 0 B)
Flush LSN : 0/F000000 (diff: -256 B)
至此，barman streaming 方式备份完成。
参考：
https://www.2ndquadrant.com/en/resources/barman/
https://sourceforge.net/projects/pgbarman/files/
https://sourceforge.net/projects/pgbarman/files/2.4/
https://www.pgbarman.org/about/
https://www.pgbarman.org/documentation/
http://docs.pgbarman.org/release/2.4/
