Postgresql:如何使用Barman进行增量备份

Question

我是Postgresql的新手，我找到了一个名为Barman的工具，用于对数据库进行增量备份。但是，这几个教程并没有起到多大的作用，因为它们不适合天真的用户。

有人能帮我简单地解释一下如何用Barman备份数据吗？那将是一个很大的帮助！我在Ubuntu18.04中使用Postgres 10.4。

Answer 1

我发现这个伟大的教程后来逐步解释了这个过程。到目前为止，这个是最有用的。我包括的内容，以防链接变得无效，如果页面改变。

Barman (备份和恢复管理器)是一个管理工具，用于PostgreSQL服务器的灾难恢复。Barman可以在业务关键环境中执行多台服务器的远程备份，并在恢复阶段帮助DBA。酒保最想要的功能包括:备份目录、增量备份、保留策略、远程恢复、WAL文件和备份的存档和压缩。

设置pgbarman的步骤：

步骤1:安装PostgreSQL9.4或9.5。

从下面的链接下载PostgreSQL

https://ftp.postgresql.org/pub/source/v9.5.1/postgresql-9.5.1.tar.bz2

步骤2:从下面的链接下载pgbarman。

https://sourceforge.net/projects/pgbarman/files/1.5.1/barman-1.5.1.tar.gz/download

Step3:在安装pgbarman之前先安装先决条件，下面是必要的先决条件。

Python2.6或2.7 Python模块：

• 议论文
• argh >= 0.21.2
• psycopg2
• < 2.0 (因为2.0版需要python3)
• 分发(可选)

PostgreSQL >= 8.3 rsync >= 3.0.4

Step4:解压缩pgbarman文件并按如下所示安装它

[root@localhost ~] tar -xvf barman-1.5.1.tar.gz
[root@localhost ~] cd barman-1.5.1
[root@localhost barman-1.5.1] python2.6 setup.py build
[root@localhost barman-1.5.1] python2.6 setup.py install

Step5:将barman.conf从doc复制到/etc/

[root@localhost barman-1.5.1] cp doc/barman.conf /etc/

Step6:创建用户酒吧并更改/etc/barman.conf的所有者

[root@localhost barman-1.5.1]# chown -R barman:barman /etc/barman.conf

Step7:使barman服务器和postgres服务器之间的密码认证更少。

[barman@localhost ~]$ ssh-keygen
[barman@localhost ~]$ ssh-copy-id -i .ssh/id_rsa.pub postgres@127.0.0.1

对postgres用户重复上述步骤。

[root@localhost barman-1.5.1]# su - postgres
[postgres@localhost ~]$ ssh-keygen
[postgres@localhost ~]$ ssh-copy-id -i .ssh/id_rsa.pub barman@127.0.0.1

Step8:在配置文件中编辑barman.conf并编辑下面的参数

[barman]
barman_home = /home/barman
barman_user = barman
log_file = /home/barman/barman.log
compression = gzip
reuse_backup = link
minimum_redundancy = 1
[main-db-server]
description = "Main DB Server"
ssh_command = ssh postgres@127.0.0.1
conninfo = host=127.0.0.1 user=postgres

Step9:编辑postgresql.conf并启用归档。

主服务器上还有最后一个配置，即打开备份(或存档)模式。首先，我们需要从barman找到传入备份目录的值，然后切换到用户barman：

         su - barman

运行下面的命令来定位传入的备份目录：

 barman show-server main | grep incoming_wals_directory  
incoming_wals_directory: /home/barman/main/incoming

Note down the value of incoming_wals_directory in my setup it's/home/barman/main/incoming

现在切换到postgres服务器上的用户postgres。

• 打开postgresql.conf并对文件进行以下更改:取消注释wal_level参数并将其值设置为存档。取消对archive_mode参数的注释并将其值设置为on。取消注释archive_command参数并将其值设置为'rsync -a %p -a‘使用Barman服务器的IP地址。如果您为incoming_wals_directory得到了不同的值。

Step10:重新启动postgres服务器或实例。

pg_ctl -D /home/postgres/master restart

Step11:现在去找酒吧男的(苏巴曼)，检查一下酒吧服务员是否可以连接到邮递员。

Barman check main

注意:这里'main‘是postgres实例的名称，它是在barman.conf中声明的。

[barman@localhost ~]$ barman check main
Server main:
PostgreSQL: OK
archive_mode: OK
wal_level: OK
archive_command: OK
continuous archiving: OK
directories: OK
retention policy settings: OK
backup maximum age: OK (no last_backup_maximum_age provided)
compression settings: OK
minimum redundancy requirements: FAILED (have 0 backups, expected at least 1)
ssh: OK (PostgreSQL server)
not in recovery: OK

Step12 :下面的命令提供了postgres服务器的信息

[barman@localhost ~]$ barman show-server main
Server main:
active: True
archive_command: false
archive_mode: on
archived_count: 0
backup_directory: /home/barman/main
backup_options: BackupOptions(['exclusive_backup'])
bandwidth_limit: None
basebackup_retry_sleep: 30
basebackup_retry_times: 0
basebackups_directory: /home/barman/main/base
compression: None
config_file: /home/postgres/data/postgresql.conf
conninfo: host=127.0.0.1 user=postgres port=5432
copy_method: rsync
current_archived_wals_per_second: 0.0
current_xlog: 000000010000000000000043
custom_compression_filter: None
custom_decompression_filter: None
data_directory: /home/postgres/data
description: Main PostgreSQL Database
disabled: False
failed_count: 0

Step13 :第一次完全备份主酒保

[barman@localhost ~]$ barman backup main
Starting backup for server main in /home/barman/main/base/20160226T134115
Backup start at xlog location: 0/48000028 (000000010000000000000048, 00000028)
Copying files.
Copy done.
Asking PostgreSQL server to finalize the backup.
Backup size: 480.8 MiB. Actual size on disk: 480.8 MiB (-0.00% deduplication ratio).
Backup end at xlog location: 0/480000C0 (000000010000000000000048, 000000C0)
Backup completed
Processing xlog segments for main
Older than first backup. Trashing file 000000010000000000000047 from server main
000000010000000000000048
000000010000000000000048.00000028.backup

Step14 :登录到postgres并创建数据库和表，将数据插入表中，然后使用barman(登录到barman)对main进行增量备份。

向postgres添加数据：

    [postgres@localhost ~]$ psql
    psql (9.5.1)
    Type "help" for help.
    postgres=# \dt
    List of relations
    Schema | Name | Type | Owner
    --------+------------------+-------+----------
    public | pgbench_accounts | table | postgres
    public | pgbench_branches | table | postgres
    public | pgbench_history | table | postgres
    public | pgbench_tellers | table | postgres
    public | test | table | postgres
    public | test1 | table | postgres
    public | test2 | table | postgres
    public | test4 | table | postgres
    public | test5 | table | postgres
    public | test6 | table | postgres
    (10 rows)
    postgres=# create database test;
    CREATE DATABASE
    postgres=# \c test
    You are now connected to database "test" as user "postgres".
    test=# create table test1(i int);
    CREATE TABLE
    test=# create table test2(i int);
    CREATE TABLE
    test=# insert into test1 values (generate_series(1,1000));
    INSERT 0 1000
    test=# insert into test2 values (generate_series(1,1000));
    INSERT 0 1000
    test=# \q

Now take incremental backup using barman :
[barman@localhost ~]$ barman backup --reuse=link main
Starting backup for server main in /home/barman/main/base/20160226T134400
Backup start at xlog location: 0/4A000028 (00000001000000000000004A, 00000028)
Copying files.
Copy done.
Asking PostgreSQL server to finalize the backup.
Backup size: 488.0 MiB. Actual size on disk: 7.3 MiB (-98.50% deduplication ratio).
Backup end at xlog location: 0/4A0000C0 (00000001000000000000004A, 000000C0)
Backup completed
Processing xlog segments for main
000000010000000000000049
00000001000000000000004A
00000001000000000000004A.00000028.backup

注意：-重用= link用于链接主完全备份。这将只备份main(postgres)中修改或更改的文件。

Step15:要列出backus，请执行以下命令

[barman@localhost ~]$ barman list-backup main
main 20160226T134400 - Fri Feb 26 13:44:07 2016 - Size: 504.0 MiB - WAL Size: 0 B
main 20160226T134115 - Fri Feb 26 13:41:29 2016 - Size: 496.8 MiB - WAL Size: 32.0 MiB

There are two backups listed 20160226T134400 is incremental backup and 20160226T134115 is full backup.

Step16:现在让我们从备份中恢复

[barman@localhost ~]$ barman recover main 20160226T134400 /tmp/data
Starting local restore for server main using backup 20160226T134400
Destination directory: /tmp/data
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 209: archive_command = false
Your PostgreSQL server has been successfully prepared for recovery!

备份在位置/tmp/data还原，在恢复用户时必须提供最新的备份ID

barman recover main latest /tmp/data

Step17:现在将/tmp/data的所有者更改为'postgres‘，并启动恢复的实例。

[root@localhost tmp]# chown -R postgres:postgres data
[root@localhost data]# ls -lrth
total 156K
drwx------. 2 postgres postgres 4.0K Feb 26 08:11 pg_twophase
drwx------. 2 postgres postgres 4.0K Feb 26 08:11 pg_tblspc
drwx------. 2 postgres postgres 4.0K Feb 26 08:11 pg_snapshots
drwx------. 2 postgres postgres 4.0K Feb 26 08:11 pg_serial
drwx------. 2 postgres postgres 4.0K Feb 26 08:11 pg_replslot
drwx------. 4 postgres postgres 4.0K Feb 26 08:11 pg_multixact
drwx------. 4 postgres postgres 4.0K Feb 26 08:11 pg_logical
drwx------. 2 postgres postgres 4.0K Feb 26 08:11 pg_dynshmem
drwx------. 2 postgres postgres 4.0K Feb 26 08:11 pg_commit_ts
-rw-------. 1 postgres postgres 4 Feb 26 08:11 PG_VERSION
-rw-------. 1 postgres postgres 88 Feb 26 08:11 postgresql.auto.conf.origin
drwx------. 2 postgres postgres 4.0K Feb 26 08:11 pg_subtrans
-rw-------. 1 postgres postgres 1.6K Feb 26 08:11 pg_ident.conf
drwx------. 2 postgres postgres 4.0K Feb 26 08:11 pg_clog
-rw-------. 1 postgres postgres 4.4K Feb 26 08:27 pg_hba.conf
-rw-------. 1 postgres postgres 22K Feb 26 13:40 postgresql.conf.origin
drwx------. 2 postgres postgres 4.0K Feb 26 13:40 pg_stat
drwx------. 7 postgres postgres 4.0K Feb 26 13:42 base
-rw-------. 1 postgres postgres 224 Feb 26 13:44 backup_label.old
-rw-------. 1 postgres postgres 22K Feb 26 13:45 postgresql.conf
-rw-------. 1 postgres postgres 88 Feb 26 13:45 postgresql.auto.conf
-rw-------. 1 postgres postgres 58 Feb 26 13:46 postmaster.pid
drwx------. 2 postgres postgres 4.0K Feb 26 13:46 pg_notify
-rw-------. 1 postgres postgres 40 Feb 26 13:46 postmaster.opts
drwx------. 2 postgres postgres 4.0K Feb 26 13:46 pg_log
drwx------. 3 postgres postgres 4.0K Feb 26 13:46 pg_xlog
drwx------. 2 postgres postgres 4.0K Feb 26 13:46 global
drwx------. 2 postgres postgres 4.0K Feb 26 13:53 pg_stat_tmp
[root@localhost tmp]# su - postgres
[postgres@localhost ~]$ pg_ctl -D master/ stop
waiting for server to shut down.... done
server stopped
[postgres@localhost ~]$ pg_ctl -D /tmp/data/ start
server starting
[postgres@localhost ~]$ LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "pg_log".

psql到新实例，该实例使用位置/tmp/data中的barman恢复。

[postgres@localhost ~]$ psql
psql (9.5.1)
Type "help" for help.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | pgbench_accounts | table | postgres
public | pgbench_branches | table | postgres
public | pgbench_history | table | postgres
public | pgbench_tellers | table | postgres
public | test | table | postgres
public | test1 | table | postgres
public | test2 | table | postgres
public | test4 | table | postgres
public | test5 | table | postgres
public | test6 | table | postgres
(10 rows)
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 466 MB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7129 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7129 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7265 kB | pg_default |
(4 rows)
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | test1 | table | postgres
public | test2 | table | postgres
(2 rows)
test=# select count(*) from test1;
count
-------
1000
(1 row)
test=# show data_directory ;
data_directory
----------------
/tmp/data
(1 row)
test=#