关于我们

技术分享

技术分享 postgresql 物理备份 barman 之 streaming backup

postgresql 物理备份 barman 之 streaming backup

2021-06-02

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 方式备份完成。