postgresql - Postgresql：如何使用 Barman 进行增量备份

后来我发现了这个很棒的教程，逐步解释了过程。这是迄今为止最有帮助的一个。如果页面发生更改，我将包含内容以防链接失效。

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

设置 pgbarman 的步骤：

第 1 步：安装 PostgreSQL-9.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之前安装先决条件是必要的先决条件。

Python 2.6 或 2.7 Python 模块：

参数完成
啊 >= 0.21.2
心理咨询师2
python-dateutil < 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：创建用户barman并更改/etc/barman.conf的所有者

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

Step7：在barman server和postgres server之间进行无密码认证。

[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 barman@127.0.0.1:/home/barman/main/incoming/%f'。使用 Barman 服务器的 IP 地址。如果您的入站沃尔斯目录有不同的值。

Step10：重启 postgres 服务器或实例。

pg_ctl -D /home/postgres/master restart

Step11：现在登录到酒保（su - barman）并检查酒保是否可以连接到postgres。

Barman check main

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

[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 对 main 进行第一次完整备份

[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对main进行增量备份（登录到barman）。

向 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

注意：--reuse=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

步骤 17：现在将 /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=#

postgresql - Postgresql：如何使用 Barman 进行增量备份

问题描述

解决方案

推荐阅读