pg_rman备份实施文档

本次实施是用pg的pg_rman备份工具进行备份,制定的备份计划是每周日全被，周一，周三进行增量备份，每10分钟备份一次归档

1实施备份之前的准备工作；

先查看要备份的数据库名称

postgres=# \l

-----------------------------------

 distributedb | distributedbuser | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/distributedbuser                 +

              |                  |          |             |             | distributedbuser=CTc/distributedbuser

 postgres     | postgres         | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |

 template0    | postgres         | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                          +

              |                  |          |             |             | postgres=CTc/postgres

 template1    | postgres         | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres                          +

              |                  |          |             |             | postgres=CTc/postgres

用pg_dump先对数据库进行逻辑备份

pg_dump distributedb > /var/lib/pgsql/distributedb0701.dmp

pg_dumpall>/var/lib/pgsql/pgall0701.dmp

2安装pg_rman

文件名称pg_rman-1.2.11-1.pg94.rhel6.x86_64.rpm

（1）建立备份目录和归档目录/backup /arclog并把所有者改为postgres

（2）配置环境变量

-bash-4.1$ cat .bashrc

export PATH=/usr/pgsql-9.4/bin:/opt/pgsql/bin:$PATH:$HOME/bin

export PGDATA=/var/lib/pgsql/9.4/data

export PGUSER=postgres

export PGPORT=5432

export LD_LIBRARY_PATH=/var/lib/pgsql/9.4:$LD_LIBRARY_PATH

export BACKUP_PATH=/backup

（3）修改postgresql.conf文件，并且重启数据库

wal_level=archive

archive_mode = on

archive_command = 'test ! -f /arclog/%f && cp %p /arclog/%f

（4）初始化，初始化备份目录：

开始初始化备份目录：pg_rman init -B /backup

当显示：

INFO: ARCLOG_PATH is set to '/arclog'

INFO: SRVLOG_PATH is set to '/opt/pgsql/data/pg_log'

代表备份目录初始化成功。查看pg_rman.ini：cat /backup/pg_rman.ini显示：

ARCLOG_PATH='/arclog'

SRVLOG_PATH='/opt/pgsql/data/pg_log'

至此，pg_rman安装完毕。

修改pg_rman.ini 文件为：

ARCLOG_PATH='/arclog'

SRVLOG_PATH='/opt/pgsql/data/pg_log'

COMPRESS_DATA = YES

KEEP_ARCLOG_FILES = 10

KEEP_ARCLOG_DAYS = 10

KEEP_DATA_GENERATIONS = 3

KEEP_DATA_DAYS = 120

KEEP_SRVLOG_FILES = 10

KEEP_SRVLOG_DAYS = 10

3根据需求用shell编写了三个备份脚本

（1）全被脚本

-bash-4.1$ cat in0.sh

#!/bin/bash

export PATH=/usr/pgsql-9.4/bin:/opt/pgsql/bin:$PATH:$HOME/bin

export PGDATA=/var/lib/pgsql/9.4/data

export PGUSER=postgres

export PGPORT=5432

export LD_LIBRARY_PATH=/var/lib/pgsql/9.4:$LD_LIBRARY_PATH

export BACKUP_PATH=/backup

pg_rman backup --backup-mode=full

pg_rman validate

 

（2）增量脚本

-bash-4.1$ cat in1.sh

#!/bin/bash

export PATH=/usr/pgsql-9.4/bin:/opt/pgsql/bin:$PATH:$HOME/bin

export PGDATA=/var/lib/pgsql/9.4/data

export PGUSER=postgres

export PGPORT=5432

export LD_LIBRARY_PATH=/var/lib/pgsql/9.4:$LD_LIBRARY_PATH

export BACKUP_PATH=/backup

pg_rman backup --backup-mode=incremental --with-serverlog

pg_rman validate

（3）备份归档的脚本

-bash-4.1$ cat in2.sh

#!/bin/bash

export PATH=/usr/pgsql-9.4/bin:/opt/pgsql/bin:$PATH:$HOME/bin

export PGDATA=/var/lib/pgsql/9.4/data

export PGUSER=postgres

export PGPORT=5432

export LD_LIBRARY_PATH=/var/lib/pgsql/9.4:$LD_LIBRARY_PATH

export BACKUP_PATH=/backup

export BACKUP_PATH=/backup

pg_rman backup --backup-mode= archive --with-serverlog

pg_rman validate

-bash-4.1$ crontab -e

10,20,30,40,50 * * * * sh /var/lib/pgsql/in2.sh >> /var/lib/pgsql/in22.log 2>&1

30 2 * * 1,3  sh /var/lib/pgsql/in1.sh >> /var/lib/pgsql/in11.log 2>&1

30 2 * *  0  sh /var/lib/pgsql/in0.sh >> /var/lib/pgsql/in00.log 2>&1

pg_rman restore --recovery-target-time "2015-06-12 17:21:18"

pg_rman 的命令详解

 

执行全库备份：pg_rman backup --backup-mode=full

当显示：

INFO: database backup start

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

全库备份成功。

备份验证：pg_rman validate

查看备份集:pg_rman show

执行增量备份；pg_rman backup --backup-mode=incremental --with-serverlog

验证：pg_rman validate

执行备份：pg_rman backup --backup-mode= archive --with-serverlog

验证：pg_rman validate

执行指定时间点的恢复。

pg_rman restore --recovery-target-time "2015-06-12 17:21:18"

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29898569/viewspace-1719014/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29898569/viewspace-1719014/