pg_probackup 之二 yum setup

os: centos 7.4
db: postgresql 9.6
pg_probackup: 2.1.5

pg_probackup 是 postgres pro 一款优秀的备份还原工具，目前兼容 PostgreSQL 9.5, 9.6, 10, 11;

pg_probackup 的 yum install

# rpm -ivh http://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-centos.noarch.rpm;
yum -y install pg_probackup-9.6;
yum -y install pg_probackup-9.6-debuginfo;

# rpm -qa |grep -i probackup
pg_probackup-9.6-2.1.5-1.b55bfe82becbbac6.x86_64
pg_probackup-repo-2.1.5-1.noarch
pg_probackup-9.6-debuginfo-2.1.5-1.b55bfe82becbbac6.x86_64

# rpm -ql pg_probackup-9.6-2.1.5-1.b55bfe82becbbac6.x86_64
/usr/bin/pg_probackup-9.6

创建个软链

# ln -s /usr/bin/pg_probackup-9.6 /usr/bin/pg_probackup

# ls -l /usr/bin |grep -i pg_probackup
lrwxrwxrwx    1 root root         25 Oct  7 15:16 pg_probackup -> /usr/bin/pg_probackup-9.6
-rwxr-xr-x    1 root root     301272 Jul 26 16:29 pg_probackup-9.6

Initializing the Backup Catalog

# su - postgres
$ which pg_probackup
/bin/pg_probackup

$ pg_probackup version

$ mkdir pgbackup

$ pg_probackup init -B /var/lib/pgsql/pgbackup

$ tree pgbackup
pgbackup
├── backups
└── wal

2 directories, 0 files

生成 backups 和 wal 文件夹

Adding a New Backup Instance

$ pg_probackup add-instance -B /var/lib/pgsql/pgbackup -D /var/lib/pgsql/9.6/data --instance test1

$ tree pgbackup
pgbackup
├── backups
│   └── test1
│       └── pg_probackup.conf
└── wal
    └── test1

4 directories, 1 file

$ cat ./pgbackup/backups/test1/pg_probackup.conf 
# Backup instance information
pgdata = /var/lib/pgsql/9.6/data
system-identifier = 6744955624417908339

$ /usr/pgsql-9.6/bin/pg_controldata  |grep -i "system identifier"
Database system identifier:           6744955624417908339

instance_name=test1 是在 /var/lib/pgsql/pgbackup/backups 和 /var/lib/pgsql/pgbackup/wal 的文件夹名

Configuring the Database Cluster

# su - postgres
$ psql

postgres=# BEGIN;
CREATE ROLE backup WITH LOGIN;
GRANT USAGE ON SCHEMA pg_catalog TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.current_setting(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_is_in_recovery() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_start_backup(text, boolean, boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup(boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_xlog() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_xlog_replay_location() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current_snapshot() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_snapshot_xmax(txid_snapshot) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_control_checkpoint() TO backup;
COMMIT;

postgres=# 
postgres=# 
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 backup    |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

For details, see the sections
Setting up STREAM Backups,
Setting up continuous WAL archiving,
Setting up Backup from Standby,
Configuring the Remote Mode
Setting up PTRACK Backups.

Setting up continuous WAL archiving

本次使用 continuous WAL archiving 方式做归档备份,有些参数需要修改

$ vi postgresql.conf

wal_level = logical

archive_mode = on
archive_command = 'pg_probackup archive-push -B /var/lib/pgsql/pgbackup --instance test1 --wal-file-path=%p --wal-file-name=%f '
archive_timeout = 600

max_wal_senders = 10
hot_standby = on
full_page_writes = on

当然也可以通过配置 ssh，来实现 pg_probackup archive-push 的远程投递.

# systemctl restart postgresql-9.6.service
# su - postgres
$ psql -c "select pg_switch_xlog();"
 pg_switch_xlog 
----------------
 0/7000078
(1 row)

postgresql 的日志有相关的日志

INFO: pg_probackup archive-push from /var/lib/pgsql/9.6/data/pg_xlog/000000010000000000000006 to /var/lib/pgsql/pgbackup/wal/test1/000000010000000000000006
INFO: pg_probackup archive-push completed successfully

INFO: pg_probackup archive-push from /var/lib/pgsql/9.6/data/pg_xlog/000000010000000000000007 to /var/lib/pgsql/pgbackup/wal/test1/000000010000000000000007
INFO: pg_probackup archive-push completed successfully

查看备份文件夹

$ tree pgbackup/
pgbackup/
├── backups
│   └── test1
│       ├── pg_probackup.conf
│       └── PYZTVQ
│           ├── backup.control
│           └── database
└── wal
    └── test1
        ├── 000000010000000000000006
        └── 000000010000000000000007

6 directories, 4 files

参考:
https://github.com/postgrespro/pg_probackup
https://postgrespro.com/products/extensions/pg_probackup

https://github.com/postgrespro/pg_probackup/blob/master/Documentation.md
https://postgrespro.github.io/pg_probackup/
https://postgrespro.com/docs/postgrespro/9.6/app-pgprobackup