Skip to main content

Backup database PostgreSQL sử dụng Barman

21,March 2018
Cấu hình barman backup database postgresql

Barman (Backup and Recovery Manager) là 1 chương trình open-source sử dụng backup và recovery PostgreSQL được viết bằng ngôn ngữ Python. Barman cho phép backup và recovey database multiple server 

I. Cài đặt barman

1. Cài đặt barman trên master db server 

$ echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' >> /etc/apt/sources.list.d/pgdg.list
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt-get update
$ sudo apt-get install barman

2. Cấu hình truy cập SSH giữa master và barman server cho account "postgres" & "barman"

#Trên master postgresql server 
$ sudo su postgres
$ ssh-keygen 

#Copy the publich key id_rsa.pub contents and add it to the authorized_keys file in the barman server user "barman" ssh directory
postgres@repmgr_node1:~$ cat ~/.ssh/id_rsa.pub #paste barman@backup:~$ vim ~/.ssh/authorized_keys
barman@backup:~$ cat ~/.ssh/id_rsa.pub #paste postgres@repmgr_node1:~$ vim ~/.ssh/authorized_keys

#Testing ssh access 
postgres@repmgr_node1:~$ ssh backup
barman@backup:~$ ssh repmgr_node1

 

II. Backup PostgreSQL sử dụng Barman

1.  Cấu hình barman backup qua "streaming protocol"

  • a standard connection to PostgreSQL, for management, coordination, and monitoring purposes
  • a streaming replication connection that will be used by both pg_basebackup (for base backup operations) and pg_receivexlog (for WAL streaming)
  • using PostgreSQL 9.4 or higher, no need ssh access
barman architecture sêcnario

 

+ Trên server postgresql master.

- Tạo user "barman" với quyền "superuser" cho phép tạo kết nối từ barman server tới PostgreSQL server

root@repmgr_node1:# sudo su -l postgresql
postgres@repmgr_node1:~$ psql
postgres=# create role barman with password '12345678' ; alter role barman login ; alter role barman superuser ;

- Tạo user "stream_barman" với quyền "replication" để stream/replicate xlog từ database server

postgres=# create role stream_barman with password '12345678'; alter role stream_barman login; alter role stream_barman replication;

- Sửa file /etc/postgresql/10/main/pg_hba.conf

root@repmgr_node1:# vim /etc/postgresql/10/main/pg_hba.conf

host    replication     stream_barman   192.168.10.10/32       md5
host    all             barman          192.168.10.10/32       md5

+ Trên barman server

- Cấu hình thông số global cho barman

root@backup:# vim /etc/barman.conf

[barman]
; System user
barman_user = barman

; Directory of configuration files. Place your sections in separate files with .conf extension
; For example place the 'main' server section in /etc/barman.d/main.conf
configuration_files_directory = /etc/barman.d

; Main directory
barman_home = /var/lib/barman

; Log location
log_file = /var/log/barman/barman.log

; Log level (see https://docs.python.org/3/library/logging.html#levels)
log_level = INFO

; Default compression level: possible values are None (default), bzip2, gzip, pigz, pygzip or pybzip2
compression = gzip

; Immediate checkpoint for backup command - default false
immediate_checkpoint = true

; Enable network compression for data transfers - default false
;network_compression = false

; Number of retries of data copy during base backup after an error - default 0
basebackup_retry_times = 3

; Number of seconds of wait after a failed copy, before retrying - default 30
basebackup_retry_sleep = 30

; Time frame that must contain the latest backup date.
; If the latest backup is older than the time frame, barman check
; command will report an error to the user.
; If empty, the latest backup is always considered valid.
; Syntax for this option is: "i (DAYS | WEEKS | MONTHS)" where i is an
; integer > 0 which identifies the number of days | weeks | months of
; validity of the latest backup for this check. Also known as 'smelly backup'.
last_backup_maximum_age = 1 DAYS

 

- Tạo file cấu hình cho PostgreSQL server

root@backup:# vim /etc/barman.d/pg.conf

[master]
description = "PostgreSQL Master server"
conninfo = host=192.168.10.101 user=barman dbname=postgres password=12345678
streaming_conninfo = host=192.168.10.101 user=stream_barman dbname=postgres password=12345678
backup_method = postgres
retention_policy_mode = auto
streaming_archiver = on
slot_name = barman

; PATH setting for this server
path_prefix = "/usr/lib/postgresql/10/bin"

 

- Chạy câu lệnh barman check "name-server" để kiểm tra cấu hình

barman@backup:~$ barman check master

Server master:
    WAL archive: FAILED (please make sure WAL shipping is setup)
    PostgreSQL: OK
    is_superuser: OK
    PostgreSQL streaming: OK
    wal_level: OK
    replication slot: FAILED (slot 'barman' not initialised: is 'receive-wal' running?)
    directories: OK
    retention policy settings: OK
    backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups)
    compression settings: OK
    failed backups: OK (there are 0 failed backups)
    minimum redundancy requirements: FAILED (have 0 backups, expected at least 3)
    pg_basebackup: OK
    pg_basebackup compatible: OK
    pg_basebackup supports tablespaces mapping: OK
    pg_receivexlog: OK
    pg_receivexlog compatible: OK
    receive-wal running: FAILED (See the Barman log file for more details)
    archiver errors: OK

 

barman@backup:~$ barman receive-wal --create-slot master

Creating physical replication slot 'barman' on server 'master'
Replication slot 'barman' created

 

barman@backup:~$ barman cron

Starting WAL archiving for server master

 

barman@backup:~$ barman switch-xlog --force --archive master

The WAL file 00000008000000000000001A has been closed on server 'master'
Waiting for the WAL file 00000008000000000000001A from server 'master' (max: 30 seconds)
Processing xlog segments from streaming for master
    00000008000000000000001A

 

- Kiểm tra lại cấu hình barman bằng câu lệnh sau

barman@backup:~$ barman check master

Server master:
    PostgreSQL: OK
    is_superuser: OK
    PostgreSQL streaming: OK
    wal_level: OK
    replication slot: OK
    directories: OK
    retention policy settings: OK
    backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups)
    compression settings: OK
    failed backups: OK (there are 0 failed backups)
    minimum redundancy requirements: FAILED (have 0 backups, expected at least 3)
    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

 

+ Câu lệnh backup barman

barman@backup:~$ barman backup master

Starting backup using postgres method for server master in /var/lib/barman/master/base/20180320T171820
Backup start at LSN: 0/1B000140 (00000008000000000000001B, 00000140)
Starting backup copy via pg_basebackup for 20180320T171820
WARNING: pg_basebackup does not copy the PostgreSQL configuration files that reside outside PGDATA. Please manually backup the following files:
    /etc/postgresql/10/main/postgresql.conf
    /etc/postgresql/10/main/pg_hba.conf
    /etc/postgresql/10/main/pg_ident.conf

Copy done (time: 1 second)
Finalising the backup.
This is the first backup for server master
WAL segments preceding the current backup have been found:
    00000008000000000000001A from server master has been removed
Backup size: 53.5 MiB
Backup end at LSN: 0/1D000000 (00000008000000000000001C, 00000000)
Backup completed (start time: 2018-03-20 17:18:20.703424, elapsed time: 1 second)
Processing xlog segments from streaming for master
    00000008000000000000001B

 

barman@backup:~$ barman list-backup master

master 20180320T171820 - Tue Mar 20 17:18:22 2018 - Size: 53.6 MiB - WAL Size: 0 B

 

+ Tạo crontab

$ crontab -e 
00 01 * * * /usr/bin/barman backup master

 

+ Recovery database

+ Trước khi recovery database, stop postgresql service trên server muốn recovery. Tôi sẽ remove folder chứa db postgresql trên master server và recovery data từ barman backup server

root@repmgr_node1:# rm -rf /var/lib/postgresql/10/main
root@repmgr_node1:# systemctl stop postgresql

 

+ Câu lệnh recovery

barman@backup:~$ barman list-backup master

$ barman recover --target-time "Begin time" --remote-ssh-command "ssh postgres@new_server" name_server backup_id Destination_dir
barman@backup:~$ barman recover --remote-ssh-command "ssh postgres@192.168.10.101" master 20180320T171820 /var/lib/postgresql/10/main

Starting remote restore for server master using backup 20180320T171820
Destination directory: /var/lib/postgresql/10/main
Copying the base backup.
Copying required WAL segments.
Generating archive status files
Identify dangerous settings in destination directory.

WARNING
The following configuration files have not been saved during backup, hence they have not been restored.
You need to manually restore them in order to start the recovered PostgreSQL instance:

    postgresql.conf
    pg_hba.conf
    pg_ident.conf

Your PostgreSQL server has been successfully prepared for recovery!

+ Kiểm tra. Login master postgresql

root@repmgr_node1:# systemctl start postgresql
root@repmgr_node1:# netstat -ntlp
root@repmgr_node1:# sudo su -l postgres
postgres@repmgr_node1:~$ psql
postgres=# \c test_db;
You are now connected to database "test_db" as user "postgres".
test_db=# select * from students;
  id  |    name    
------+------------
 1001 | Ngo Huy
 1002 | Vu Ha

 

2.  Cấu hình barman backup qua "rsync/ssh"

  • PostgreSQL servers version 8.3, 8.4, 9.0 or 9.1
  • PostgreSQL servers version 9.2 or 9.3 that are using tablespaces
  • incremental backup, parallel backup and deduplication
  • network compression during backups
  • finer control of bandwidth usage, including on a tablespace basis
kiến trúc barman backup postgresql

+ Trên server postgresql master.

- Tạo user "barman" với quyền "superuser" cho phép tạo kết nối từ barman server tới PostgreSQL server

root@repmgr_node1:# sudo su -l postgresql
postgres@repmgr_node1:~$ psql
postgres=# create role barman with password '12345678' ; alter role barman login ; alter role barman superuser ;

- Tạo user "stream_barman" với quyền "replication" để stream/replicate xlog từ database server

postgres=# create role stream_barman with password '12345678'; alter role stream_barman login; alter role stream_barman replication;

- Sửa file /etc/postgresql/10/main/pg_hba.conf

root@repmgr_node1:# /etc/postgresql/10/main/pg_hba.conf

#192.168.10.10 là địa chỉ barman server
archive_command = 'rsync -avp %p barman@192.168.10.10:/var/lib/barman/master/incoming/%f'

- Sửa file /etc/postgresql/10/main/pg_hba.conf

root@repmgr_node1:# vim /etc/postgresql/10/main/pg_hba.conf

host    replication     stream_barman   192.168.10.10/32       md5
host    all             barman          192.168.10.10/32       md5

+ Trên barman server

- Cấu hình thông số global cho barman

root@backup:# vim /etc/barman.conf

[barman]
; System user
barman_user = barman

; Directory of configuration files. Place your sections in separate files with .conf extension
; For example place the 'main' server section in /etc/barman.d/main.conf
configuration_files_directory = /etc/barman.d

; Main directory
barman_home = /var/lib/barman

; Log location
log_file = /var/log/barman/barman.log

; Log level (see https://docs.python.org/3/library/logging.html#levels)
log_level = INFO

; Default compression level: possible values are None (default), bzip2, gzip, pigz, pygzip or pybzip2
compression = gzip

; Immediate checkpoint for backup command - default false
immediate_checkpoint = true

; Enable network compression for data transfers - default false
;network_compression = false

; Number of retries of data copy during base backup after an error - default 0
basebackup_retry_times = 3

; Number of seconds of wait after a failed copy, before retrying - default 30
basebackup_retry_sleep = 30

; Time frame that must contain the latest backup date.
; If the latest backup is older than the time frame, barman check
; command will report an error to the user.
; If empty, the latest backup is always considered valid.
; Syntax for this option is: "i (DAYS | WEEKS | MONTHS)" where i is an
; integer > 0 which identifies the number of days | weeks | months of
; validity of the latest backup for this check. Also known as 'smelly backup'.
last_backup_maximum_age = 1 DAYS

 

- Tạo file cấu hình cho PostgreSQL server

root@backup:# vim /etc/barman.d/pg.conf

[master]
description = "PostgreSQL Master server"
ssh_command = ssh postgres@192.168.10.101
conninfo = host=192.168.10.101 user=barman dbname=postgres password=12345678
retention_policy_mode = auto
backup_method = rsync
reuse_backup = link
parallel_jobs = 2
retention_policy = RECOVERY WINDOW OF 7 days
wal_retention_policy = main
archiver = on

+ Setup WAL shipping

barman@backup:~$ barman switch-xlog --force --archive master

The WAL file 000000080000000000000023 has been closed on server 'master'
Waiting for the WAL file 000000080000000000000023 from server 'master' (max: 30 seconds)
ERROR: The WAL file 000000080000000000000023 has not been received in 30 seconds

 

+ Sử dụng barman check master server

barman@backup:~$ du -chs /var/lib/barman/master/*
barman@backup:~$ barman check master

Server master:
    PostgreSQL: OK
    is_superuser: OK
    wal_level: OK
    directories: OK
    retention policy settings: OK
    backup maximum age: OK (interval provided: 1 day, latest backup age: 5 hours, 8 minutes, 27 seconds)
    compression settings: OK
    failed backups: OK (there are 0 failed backups)
    minimum redundancy requirements: FAILED (have 2 backups, expected at least 3)
    ssh: OK (PostgreSQL server)
    not in recovery: OK
    archive_mode: OK
    archive_command: OK
    continuous archiving: OK
    archiver errors: OK

 

+ Câu lệnh backup barman

barman@backup:~$ tail -n100 /var/log/barman/barman.log -f
barman@backup:~$ barman backup master

Starting backup using rsync-exclusive method for server master in /var/lib/barman/master/base/20180321T154248
Backup start at LSN: 0/2E000028 (00000008000000000000002E, 00000028)
This is the first backup for server master
WAL segments preceding the current backup have been found:
    00000008000000000000002C from server master has been removed
Starting backup copy via rsync/SSH for 20180321T154248
Copy done (time: 4 seconds)
This is the first backup for server master
Asking PostgreSQL server to finalize the backup.
Backup size: 37.7 MiB. Actual size on disk: 37.7 MiB (-0.00% deduplication ratio).
Backup end at LSN: 0/2E0000F8 (00000008000000000000002E, 000000F8)
Backup completed (start time: 2018-03-21 15:42:48.357273, elapsed time: 5 seconds)
Processing xlog segments from file archival for master
    00000008000000000000002D
    00000008000000000000002E
    00000008000000000000002E.00000028.backup

 

barman@backup:~$ du -chs /var/lib/barman/master/*
barman@backup:~$ barman list-backup master

master 20180321T154248 - Wed Mar 21 15:42:53 2018 - Size: 37.7 MiB - WAL Size: 53.0 KiB

 

+ Tạo crontab

$ crontab -e 
00 01 * * * /usr/bin/barman backup master

 

+ Recovery database

+ Trước khi recovery database, stop postgresql service trên server muốn recovery. Tôi sẽ remove folder chứa db postgresql trên master server và recovery data từ barman backup server

root@repmgr_node1:# rm -rf /var/lib/postgresql/10/main
root@repmgr_node1:# systemctl stop postgresql

 

+ Câu lệnh recovery

barman@backup:~$ barman list-backup master

$ barman recover --target-time "Begin time" --remote-ssh-command "ssh postgres@new_server" name_server backup_id Destination_dir
barman@backup:~$ barman recover --remote-ssh-command "ssh postgres@192.168.10.101" master 20180320T171820 /var/lib/postgresql/10/main

Starting remote restore for server master using backup 20180321T154248
Destination directory: /var/lib/postgresql/10/main
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 220: archive_command = false

WARNING
You are required to review the following options as potentially dangerous

postgresql.conf line 40: data_directory = '/var/lib/postgresql/10/main'        # use data in another directory
postgresql.conf line 42: hba_file = '/etc/postgresql/10/main/pg_hba.conf'    # host-based authentication file
postgresql.conf line 44: ident_file = '/etc/postgresql/10/main/pg_ident.conf'    # ident configuration file
postgresql.conf line 48: external_pid_file = '/var/run/postgresql/10-main.pid'            # write an extra PID file
postgresql.conf line 83: ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
postgresql.conf line 84: ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
postgresql.conf line 650: include_dir = 'conf.d'            # include files ending in '.conf' from

 

+ Kiểm tra. Login master postgresql

root@repmgr_node1:# systemctl start postgresql
root@repmgr_node1:# netstat -ntlp
root@repmgr_node1:# sudo su -l postgres
postgres@repmgr_node1:~$ psql
postgres=# \c test_db;
You are now connected to database "test_db" as user "postgres".
test_db=# select * from students;
  id  |    name    
------+------------
 1001 | Ngo Huy
 1002 | Vu Ha