重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: pgbackrest备份与恢复 [打印本页]

---

作者: mahan    时间: 2025-5-25 15:30
标题: pgbackrest备份与恢复
机器准备
10.1.8.16 node01 primary
10.1.8.4 node03 standby

NFS
yum install -y nfs-utils rpcbind
mkdir -p /data/nfs
chmod 777 /data/nfs
chown nobody:nobody /data/nfs
cat > /etc/exports <<EOF
/data/nfs *(rw,sync,no_subtree_check,no_root_squash)
EOF
exportfs -r

systemctl enable nfs-server
systemctl start  nfs-server
systemctl retart nfs-server
pgbackrest依据ssh 互信来拷贝wal文件

getent group | grep 1001
groupadd -g 1001 postgres
useradd -g 1001 -u 1001 postgres
echo "pg@654@aJk" | passwd --stdin postgres
编辑文件 /etc/ssh/sshd_config 确保PasswordAuthentication设置yes

vi /etc/ssh/sshd_config
PasswordAuthentication yes
service sshd restart
安装
依赖包

yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
percona-release setup ppg-16  
yum -y install percona-pgbackrest
ssh互信

su - postgres
ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@node03
修改数据库归档参数

vi $PGDATA/postgresql.conf
archive_command = 'pgbackrest --stanza=cluster01 archive-push %p'
archive_mode = on
pg_ctl -D $PGDATA restart
pgbackrest配置文件
pgBackRest log

mkdir -p -m 770 /var/log/pgbackrest
chown postgres:postgres /var/log/pgbackrest
mkdir -p -m 770 /var/log/pgbackrest
mkdir -p -m 770 /var/spool/pgbackrest
chown postgres:postgres /var/log/pgbackrest
chown postgres:postgres /var/spool/pgbackrest
mkdir -p /etc/pgbackrest
touch /etc/pgbackrest/pgbackrest.conf
chmod 640 /etc/pgbackrest/pgbackrest.conf
chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
备份目录

mkdir -p /backup
chmod 750 /backup
chown postgres:postgres /backup
standby primary共享模式的目录

  mkdir /pgbackrest-nfs
  mount -t nfs 10.1.8.16:/data/nfs /pgbackrest-nfs
  chmod 750 /pgbackrest-nfs
  chown postgres:postgres /pgbackrest-nfs
pgbackrest版本

pgbackrest version
pgBackRest 2.54.2

远程备份配置
主库node01

vi /etc/pgbackrest/pgbackrest.conf
[node01]
pg1-path=/data/pgdata
pg1-socket-path=/tmp
[global]
repo1-host=node03
archive-async=y
spool-path=/var/spool/pgbackrest
log-path=/var/log/pgbackrest
repo1-path=/backup
repo1-host-user=postgres
repo1-retention-full=2
[global:archive-push]
compress-level=3
process-max=2
从库node02

vi /etc/pgbackrest/pgbackrest.conf
[node01]
pg1-path=/data/pgdata
pg1-port=5432
pg1-socket-path=/tmp
pg1-user=postgres
pg1-host-config-path=/etc/pgbackrest
pg1-host=node01
pg1-host-port=22
pg1-host-user=postgres

[global]
process-max=3
start-fast=y
repo1-path=/backup
repo1-retention-full=2
backup-user=postgres
log-level-console=info
log-level-file=debug
buffer-size=16MiB
compress-type=gz

[global:archive-push]
compress-level=3
standby database 备份配置
主库node01

vi /etc/pgbackrest/pgbackrest.conf
[node01]
pg1-path=/data/pgdata
pg1-socket-path=/tmp
[global]
repo1-host=node03
archive-async=y
spool-path=/var/spool/pgbackrest
log-path=/var/log/pgbackrest
repo1-path=/backup
repo1-host-user=postgres
repo1-retention-full=2
[global:archive-push]
compress-level=3
process-max=2
从库node02

vi /etc/pgbackrest/pgbackrest.conf
[node01]
pg1-path=/data/pgdata
pg1-port=5432
pg1-socket-path=/tmp
pg1-user=postgres
pg1-host-config-path=/etc/pgbackrest
pg1-host=node01
pg1-host-port=22
pg1-host-user=postgres

[global]
process-max=3
start-fast=y
repo1-path=/backup
repo1-retention-full=2
backup-user=postgres
log-level-console=info
log-level-file=debug
buffer-size=16MiB
compress-type=gz

[global:archive-push]
compress-level=3
共享文件式primary/standby备份配置
主库node01

vi /etc/pgbackrest/pgbackrest.conf
[node01]
pg1-path=/data/pgdata
pg1-socket-path=/tmp
[global]
#repo1-host=node03
archive-async=y
spool-path=/var/spool/pgbackrest
log-path=/var/log/pgbackrest
repo1-path=/pgbackrest-nfs
repo1-host-user=postgres
repo1-retention-full=2
[global:archive-push]
compress-level=3
process-max=2
从库node02

vi /etc/pgbackrest/pgbackrest.conf
[node01]
pg1-path=/data/pgdata
pg1-port=5432
pg1-socket-path=/tmp
pg1-user=postgres
pg1-host-config-path=/etc/pgbackrest
pg1-host=node01
pg1-host-port=22
pg1-host-user=postgres
pg2-path=/data/pgdata
pg2-port=5432
pg2-socket-path=/tmp
pg2-user=postgres
pg2-host-config-path=/etc/pgbackrest
#pg2-host=node03
pg2-host-port=22
pg2-host-user=postgres
[global]
process-max=3
start-fast=y
repo1-path=/pgbackrest-nfs
repo1-retention-full=2
backup-user=postgres
log-level-console=info
log-level-file=debug
buffer-size=16MiB
compress-type=gz

[global:archive-push]
compress-level=3
备份恢复
创建

pgbackrest --stanza=node01 --log-level-console=info stanza-create
全量备份

pgbackrest --stanza=node01 --log-level-console=info --type=full backup
增量备份

create table test1(id int,rq date);
insert into test1 select generate_series(1,10000),now();
pgbackrest --stanza=node01 --log-level-console=info --type=incr backup
差异备份

create table test2(id int,rq date);
insert into test2 select generate_series(1,10000),now();
pgbackrest --stanza=node01 --log-level-console=info --type=diff backup
查看备份信息

pgbackrest --stanza=node01 info
stanza: node01
    status: ok
    cipher: none

    db (current)
        wal archive min/max (16): 00000001000000000000004E/000000020000000000000055

        full backup: 20250520-160026F
            timestamp start/stop: 2025-05-20 16:00:26+08 / 2025-05-20 16:00:31+08
            wal start/stop: 00000001000000000000004E / 00000001000000000000004E
            database size: 28.9MB, database backup size: 28.9MB
            repo1: backup set size: 3.1MB, backup size: 3.1MB

        full backup: 20250520-160236F
            timestamp start/stop: 2025-05-20 16:02:36+08 / 2025-05-20 16:02:42+08
            wal start/stop: 000000010000000000000050 / 000000010000000000000050
            database size: 28.9MB, database backup size: 28.9MB
            repo1: backup set size: 3.1MB, backup size: 3.1MB

        incr backup: 20250520-160236F_20250520-160326I
            timestamp start/stop: 2025-05-20 16:03:26+08 / 2025-05-20 16:03:34+08
            wal start/stop: 000000010000000000000052 / 000000010000000000000052
            database size: 29.3MB, database backup size: 8.3MB
            repo1: backup set size: 3.2MB, backup size: 408.5KB
            backup reference total: 1 full

        diff backup: 20250520-160236F_20250520-160415D
            timestamp start/stop: 2025-05-20 16:04:15+08 / 2025-05-20 16:04:28+08
            wal start/stop: 000000010000000000000054 / 000000010000000000000054
            database size: 29.8MB, database backup size: 8.9MB
            repo1: backup set size: 3.2MB, backup size: 501.5KB
            backup reference total: 1 full
恢复

pg_ctl -D $PGDATA stop
rm -rf $PGDATA/*
默认恢复

pgbackrest --stanza=node01 --log-level-console=info  \
restore --type=default
注：restore backup set 20250520-160236F_20250520-160415D，恢复到最新

指定增量恢复

pgbackrest --stanza=node01 --log-level-console=info  \
--set=20250520-160236F_20250520-160326I --type=immediate --delta \
restore  
PITR

pgbackrest --stanza=node01 --log-level-console=info  \
--delta --type=time "--target=2025-05-20 16:04:29+08" restore  
恢复到2025-05-20 16:04:29+08之前的备份20250520-160236F_20250520-160415D

恢复搭建从库

修改从库配置

vi /etc/pgbackrest/pgbackrest.conf
[node01]
pg1-path=/data/pgdata
pg1-port=5432
pg1-socket-path=/tmp
recovery-option=primary_conninfo=application_name=node03 host=10.1.8.16 port=5432 user=replicator password=replicator
[global]
process-max=3
start-fast=y
repo1-path=/pgbackrest-nfs
repo1-retention-full=2
backup-user=postgres
log-level-console=info
log-level-file=debug
buffer-size=16MiB
compress-type=gz

[global:archive-push]
compress-level=3
恢复

pgbackrest --stanza=node01 --log-level-console=info  \
--target-timeline=current --delta --type=standby  restore
编辑standby.signal文件

cat >> $PGDATA/standby.signal <<EOF
standby_mode = 'on'
EOF
启动

pg_ctl -D $PGDATA start
编辑配置文件postgresql.auto.conf

vi postgresql.auto.conf
#restore_command = 'pgbackrest --stanza=node01 archive-get %f "%p"'
启动

pg_ctl -D $PGDATA start
常见报错
1.从库备份报错：WAL segmen was not archived before the 60000ms timeout,如果ssh互信配置无问题，通常是主库配置的repo1-host 未指向 repo所在IP，非共享文件夹备份，主库配置文件需要repo1-host，且主库不能执行备份，从库配置pg1_host,pg2_host

2.pgbackrest将自动确定哪个是primary，哪个是standby

3.如果是共享文件系统，则可用在主库执行备份

4.从库备份/或者远程备份，需要在主库配置中指定repo1-host，且主库本身不能执行备份，远程或者从库执行备份则需ssh到主库备份归档文档，从库备份数据文件

5.ERROR: [072]: restore command must be run on the PostgreSQL host注释掉： #pg1-host,只能在本地还原

6.如恢复多次则会推高backup repo中的timeline,恢复时需要指定–target-timeline=current

---

欢迎光临 重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)

Powered by Discuz! X3.2