Pg_rman备份与恢复

1     Pg_rman备份与恢复

1.1  配置归档

/opt/pgsql/13/data/postgresql.conf

# 设置归档目录
archive_mode = on
archive_command = 'cp %p /opt/pgsql/13/archive_wals/%f' 

1.2  备份

30 04 * * 0 /bin/sh /home/postgres/script/pg_rman_full_backup.sh >> /home/postgres/script/pg_rman.log&

#!/bin/bash
/usr/pgsql-13/bin/pg_rman  --backup-path /home/postgres/pg_rman  --pgdata /opt/pgsql/13/data --arclog-path /opt/pgsql/13/archive_wals  backup -b full --progress -Z --keep-data-days=7 --keep-arclog-days=7
/usr/pgsql-13/bin/pg_rman validate --backup-path /home/postgres/pg_rman

30 04 * * 1-6 /bin/sh /home/postgres/script/pg_rman_incr_backup.sh >> /home/postgres/script/pg_rman.log&

#!/bin/bash
/usr/pgsql-13/bin/pg_rman  --backup-path /home/postgres/pg_rman  --pgdata /opt/pgsql/13/data --arclog-path /opt/pgsql/13/archive_wals  backup -b incremental --progress -Z --keep-data-days=7 --keep-arclog-days=7
/usr/pgsql-13/bin/pg_rman validate --backup-path /home/postgres/pg_rman

1.3  检查备份

/usr/pgsql-13/bin/pg_rman show --backup-path /home/postgres/pg_rman

1.4  数据恢复

1.4.1  将数据推到备份机上

[postgres@db1 ~]$ rsync -avz /home/postgres/pg_rman root@10.0.0.5:/home/postgres/
[postgres@db1 ~]$ rsync -avz /opt/pgsql/13/archive_wals root@10.0.0.5:/opt/pgsql/13/

1.4.2  恢复数据

/usr/pgsql-13/bin/pg_rman restore -D /opt/pgsql/13/data -B /home/postgres/pg_rman --recovery-target-time="2025-04-03 09:10:00" -hard-copy

1.4.3  启动数据库

注意：注销掉所有涉及的资源配置项

/opt/pgsql/13/data/postgresql.conf

shared_buffers = 12GB

……所有的

启动数据库

pg_ctl start -D /opt/pgsql/13/data

　　

1.4.4  数据检查

select count(1) from ceshi.ce.login where login_time > to_timestamp('2025-04-03 9:00:00', 'yyyy-MM-dd hh24:mi:ss');