PostgreSQL backup and recovery - online backup & Point-In-Time-Recovery

最新推荐文章于 2022-11-21 04:18:39 发布
最新推荐文章于 2022-11-21 04:18:39 发布
阅读量102 收藏
点赞数
文章标签： 数据库 操作系统
原文链接：https://yq.aliyun.com/articles/14579
版权
前面介绍的冷备份, 以及逻辑备份都是一个时间点的全量备份, 没有增量的概念. 
如果数据库在运行过程中发生故障, 使用逻辑备份只能将数据库还原到备份时刻, 无法恢复到故障发生前的那个时刻.
又或者在使用过程中由于误操作修改或删除了重要数据, 需要还原到误操作前的那个时刻怎么办呢?
使用冷备份加上有效的归档文件可以实现任意时间点的恢复. 但是冷备份需要停库操作, 所以实用性不大.
本文要介绍的是在线的数据库文件备份, 弥补了冷备份的缺陷, 同时又支持基于时间点的恢复.
postgresql.conf 参数配置 : 
1. wal日志级别 : 
wal_level=archive 或 hot_standby

查看当前数据库的wal level : 
 
pg93@db-172-16-3-33-> pg_controldata |grep wal_level
Current wal_level setting:            hot_standby

如果结果不为hot_standby或者archive, 那么需要调整一下postgresql.conf. 并重启数据库.
2. 归档模式 : 
    archive_mode = on

    如果原来是off的, 调整成on, 那么需要重启数据库.
3. 归档命令 : 
首先要创建用于存放wal归档文件目录, 数据库启动用户需要写权限. 
[root@db-172-16-3-33 postgresql-9.3beta1]# mkdir -p /pgdata/digoal/1921/data04/pg93archdir
[root@db-172-16-3-33 postgresql-9.3beta1]# chown pg93:pg93 /pgdata/digoal/1921/data04/pg93archdir
[root@db-172-16-3-33 postgresql-9.3beta1]# chmod 700 /pgdata/digoal/1921/data04/pg93archdir

然后在$PGDATA中创建归档脚本
 
[root@db-172-16-3-33 postgresql-9.3beta1]# su - pg93
pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> cat archive.sh 
#!/bin/bash

export LANG=en_US.utf8
export PGHOME=/opt/pgsql9.3
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d"`
export PATH=$PGHOME/bin:$PATH:.

BASEDIR="/pgdata/digoal/1921/data04/pg93archdir"

if [ ! -d $BASEDIR/$DATE ]; then
  mkdir -p $BASEDIR/$DATE
  if [ ! -d $BASEDIR/$DATE ]; then
    echo "error mkdir -p $BASEDIR/$DATE"
    exit 1
  fi
fi

cp $1 $BASEDIR/$DATE/$2
if [ $? -eq 0 ]; then
  exit 0
else
  echo -e "cp $1 $BASEDIR/$DATE/$2 error"
  exit 1
fi

echo -e "backup failed"
exit 1

给archive.sh添加可执行权限 : 
 
chmod 700 archive.sh

配置postgresql.conf中的归档命令 : 
 
archive_command = 'archive.sh %p %f'

修改完后reload配置文件即可 
pg_ctl reload

配置完后检查一下归档是否正常 : 
 
pg93@db-172-16-3-33-> psql
psql (9.3devel)
Type "help" for help.
digoal=# checkpoint;
CHECKPOINT
digoal=# select pg_switch_xlog();
 pg_switch_xlog 
----------------
 F/2095D8
(1 row)
digoal=# select pg_switch_xlog();
 pg_switch_xlog 
----------------
 F/1000000
(1 row)
digoal=# checkpoint;
CHECKPOINT
digoal=# select pg_switch_xlog();
 pg_switch_xlog 
----------------
 F/10000E8
(1 row)
digoal=# checkpoint;
^[[ACHECKPOINT
digoal=# select pg_switch_xlog();
 pg_switch_xlog 
----------------
 F/20000E8
(1 row)

查看归档文件 : 
 
pg93@db-172-16-3-33-> cd /pgdata/digoal/1921/data04/pg93archdir
pg93@db-172-16-3-33-> ll
total 4.0K
drwx------ 2 pg93 pg93 4.0K May 27 10:56 20130527
pg93@db-172-16-3-33-> cd 20130527/
pg93@db-172-16-3-33-> ll
total 48M
-rw------- 1 pg93 pg93 16M May 27 10:56 000000030000000F00000000
-rw------- 1 pg93 pg93 16M May 27 10:56 000000030000000F00000001
-rw------- 1 pg93 pg93 16M May 27 10:56 000000030000000F00000002


具备了这些前提条件之后, 就可以进行在线备份了.
在线备份可以使用拷贝, rsync, 或者pg_basebackup命令进行.
方法一, 拷贝
首先在数据库中以超级用户执行如下命令 : 
pg93@db-172-16-3-33-> psql
psql (9.3devel)
Type "help" for help.
digoal=# select pg_start_backup(now()::text);
 pg_start_backup 
-----------------
 F/3000028
(1 row)

这个命令执行的目的是在$PGDATA中记录一个标签文件, 包含标签名now()::text, 以及执行这条指令的启动时间和WAL位置.
标签文件信息如下 : 
[root@db-172-16-3-33 postgresql-9.3beta1]# su - pg93
pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> cat backup_label 
START WAL LOCATION: F/3000028 (file 000000030000000F00000003)
CHECKPOINT LOCATION: F/3000060
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2013-05-27 11:02:58 CST
LABEL: 2013-05-27 11:02:58.578713+08

接下来就可以拷贝数据文件了.
 
pg93@db-172-16-3-33-> cp -r -L $PGDATA /pgdata/digoal/1921/data04/pg93backup/20130527_onlinebackup

拷贝完后清除备份文件中pg_xlog和pg_log中的内容, 因为这些是不需要的.
 
cd /pgdata/digoal/1921/data04/pg93backup/20130527_onlinebackup/pg_xlog
rm -rf *
cd /pgdata/digoal/1921/data04/pg93backup/20130527_onlinebackup/pg_log
rm -rf *

接下来使用超级用户在数据库中执行如下命令 : 
 
pg93@db-172-16-3-33-> psql
psql (9.3devel)
Type "help" for help.
digoal=# select pg_stop_backup();
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup 
----------------
 F/3001A20
(1 row)

然后要备份这段时间产生的归档文件 : 
需要的归档文件是哪些呢?
备份开始是的XLOG文件在backup_label中已经指出了000000030000000F00000003, 结束时的XLOG文件也可以从pg_stop_backup()的输出中得到 : 
digoal=#  select pg_xlogfile_name('F/3001A20');
     pg_xlogfile_name     
--------------------------
 000000030000000F00000003
(1 row)

如果你的PostgreSQL版本没有这个函数, 那么就自己计算一下文件名 : 
参考 : 
因此本例中需要的文件只有1个就是 000000030000000F00000003
如果归档文件本来就不是放在本地文件系统中, 那么也不需要二次备份归档文件. 只要别删掉需要的归档文件就行了.

方法二, rsync
方法与拷贝类似, 也需要先执行pg_start_backup, 然后调用rsync, 最后调用pg_stop_backup.
命令举例 :  

pg93@db-172-16-3-33-> rsync -acvz -L --exclude "pg_xlog" --exclude "pg_log" $PGDATA /pgdata/digoal/1921/data04/pg93backup/20130527_backup

方法三, pg_basebackup
pg_basebackup是PostgreSQL 9.1 引入的一个数据库备份命令, pg_basebackup利用流复制协议传输数据库文件.
使用pg_basebackup备份数据库需要用到超级用户或者replication角色用户.
另外就是pg_basebackup不需要执行pg_start_backup和pg_stop_backup, 因为命令中已经封装了这些操作.
并且支持多个pg_basebackup并行备份同一个数据库集群.
例如 : 
pg93@db-172-16-3-33-> psql
psql (9.3devel)
Type "help" for help.
digoal=# create role replica login replication encrypted password 'replica';
CREATE ROLE

配置pg_hba.conf , 允许连接.
 
host    replication     replica        172.16.3.40/32            md5
pg_ctl reload

在另一台主机备份 : 
pg93@db-172-16-3-40-> pg_basebackup -D ./basebackup_20130527 -F p -X stream -h 172.16.3.33 -p 1999 -U replica
Password: 
WARNING:  skipping special file "./.s.PGSQL.1999"

需要注意, 以pg_basebackup备份, 如果表空间使用了软链接, 那么备份时在目标端也会创建软链接, 因此需要先保证目录存在并有写权限.
例如本例 : 
pg93@db-172-16-3-40-> cd basebackup_20130527/
pg93@db-172-16-3-40-> cd pg_tblspc/
pg93@db-172-16-3-40-> ll
total 0
lrwxrwxrwx 1 pg93 pg93 47 May 27 12:28 26425 -> /pgdata/digoal/1921/data03/pg93/1999/tbs_digoal

那么目标端的 /pgdata/digoal/1921/data03/pg93/1999 这个目录要先创建好, 并提供写权限.

PITR还原举例 : 
PostgreSQL 支持指定还原点的位置, 即数据库恢复到什么位置停下来. 
4个recovery.conf参数恢复停止在哪个位置. 
1. recovery_target_name, 指pg_create_restore_point(text)创建的还原点, 如果有重名的还原点, 那么在recovery过程中第一个遇到的还原点即停止.
2. recovery_target_time, 指XLOG中记录的recordXtime( xl_xact_commit_compact-> xact_time ), 配合 recovery_target_inclusive使用, 
    如果在同一个时间点有多个事务回滚或提交, 那么 recovery_target_inclusive=false则恢复到这个时间点第一个回滚或提交的事务(含),  recovery_target_inclusive=true则恢复到这个时间点最后一个回滚或提交的事务(含). 
    如果时间点上刚好只有1个事务回滚或提交, 那么 recovery_target_inclusive=true和false一样, 恢复将处理到这个事务包含的xlog信息(含). 
    如果时间点没有匹配的事务提交或回滚信息, 那么 recovery_target_inclusive=true 和false一样, 恢复将处理到这个时间后的下一个事务回滚或提交的xlog信息(含).
3. recovery_target_xid, 指 XLogRecord->xl_xid, 可以配合 recovery_target_inclusive使用, 但是 recovery_target_inclusive只影响日志的输出, 并不影响恢复进程截至点的选择, 截至都截止于这个xid的xlog位置. 也就是说无论如何都包含了这个事务的xlog信息的recovery. 
    这里需要特别注意xid的信息体现在结束时, 而不是分配xid时. 所以恢复到xid=100提交|回滚点, 可能xid=102已经先提交了. 那么包含xid=102的xlog信息会被recovery.
4. recovery_target_inclusive
还原点的详细介绍可参考 : 

在还原前, 对数据库做一些DML操作, 配合这些DML操作来讲解一下这几个参数的含义.
1. 
begin1;query1;commit1; begin2; query2_1;  pg_create_restore_point(text); query2_2; commit2;
按照以上描述, 使用 recovery_target_name 恢复数据库恢复后应该包含query1的变更, 但是不包含query2_1和query2_2的变更.
begin1; query1;commit1;  -- SESSION A : 
digoal=# create table pitr_test(id int, info text);
CREATE TABLE
digoal=# insert into pitr_test values (1,'test');
INSERT 0 1
begin2; query2_1; -- SESSION A : 
digoal=# begin;
BEGIN
digoal=# insert into pitr_test values (2,'test');
INSERT 0 1

pg_create_restore_point(text); -- SESSION B : 
 
digoal=# select pg_create_restore_point('pitr_test');
 pg_create_restore_point 
-------------------------
 F/B01F8C8
(1 row)

query2_2; commit2; --  SESSION B : 
digoal=# insert into pitr_test values (3,'test');
INSERT 0 1
digoal=# commit;
COMMIT

切换日志, 归档 : 
 
digoal=# checkpoint;
CHECKPOINT
digoal=# select pg_xlogfile_name(pg_switch_xlog());
     pg_xlogfile_name     
--------------------------
 000000030000000F0000000C
(1 row)
digoal=# checkpoint;
CHECKPOINT
digoal=# select pg_xlogfile_name(pg_switch_xlog());
     pg_xlogfile_name     
--------------------------
 000000030000000F0000000D
(1 row)

使用拷贝的备份以及归档日志还原 : 
关闭数据库 
pg93@db-172-16-3-33-> pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped

删除数据库目录以及表空间目录
 
pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> cd pg_tblspc/
pg93@db-172-16-3-33-> ll
total 0
lrwxrwxrwx 1 pg93 pg93 47 May 26 18:04 26425 -> /pgdata/digoal/1921/data03/pg93/1999/tbs_digoal
pg93@db-172-16-3-33-> rm -rf /pgdata/digoal/1921/data03/pg93/1999/tbs_digoal/*
pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> rm -rf *

还原备份以及表空间软链接
 
pg93@db-172-16-3-33-> cp -r /pgdata/digoal/1921/data04/pg93backup/20130527_onlinebackup/* $PGDATA/
pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> cd pg_tblspc/
pg93@db-172-16-3-33-> ll
total 4.0K
drwx------ 3 pg93 pg93 4.0K May 27 14:32 26425
pg93@db-172-16-3-33-> mv 26425/* /pgdata/digoal/1921/data03/pg93/1999/tbs_digoal/
pg93@db-172-16-3-33-> rm -rf 26425
pg93@db-172-16-3-33-> ln -s /pgdata/digoal/1921/data03/pg93/1999/tbs_digoal ./26425
pg93@db-172-16-3-33-> ll
total 0
lrwxrwxrwx 1 pg93 pg93 47 May 27 14:34 26425 -> /pgdata/digoal/1921/data03/pg93/1999/tbs_digoal

创建pg_log目录, 存放日志 : 
 
pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> mkdir -p pg_log

配置$PGDATA/recovery.conf
 
pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> cp $PGHOME/share/recovery.conf.sample ./recovery.conf
vi recovery.conf
restore_command = 'recovery.sh /pgdata/digoal/1921/data04/pg93archdir %f %p'            # e.g. 'cp /mnt/server/archivedir/%f %p'
recovery_target_name = 'pitr_test'      # e.g. 'daily backup 2011-01-26'
recovery_target_timeline = 'latest'

创建恢复脚本 : 
 
pg93@db-172-16-3-33-> cat recovery.sh
#!/bin/bash

export LANG=en_US.utf8
export PGHOME=/opt/pgsql9.3
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d"`
export PATH=$PGHOME/bin:$PATH:.

BASEDIR=$1

find $BASEDIR -name $2 -exec cp {} $3 \;

添加可执行权限 : 
 
pg93@db-172-16-3-33-> chmod 700 recovery.sh

启动数据库
 
pg93@db-172-16-3-33-> pg_ctl start
server starting
pg93@db-172-16-3-33-> LOG:  00000: loaded library "pg_stat_statements"
LOCATION:  load_libraries, miscinit.c:1296

查看还原点是否与预计匹配 : 
 
pg93@db-172-16-3-33-> psql
psql (9.3devel)
Type "help" for help.
digoal=# select ctid,* from pitr_test ;
 ctid  | id | info 
-------+----+------
 (0,1) |  1 | test
(1 row)
digoal=# insert into pitr_test values(2,'new');
INSERT 0 1
digoal=# select ctid,* from pitr_test ;
 ctid  | id | info 
-------+----+------
 (0,1) |  1 | test
 (0,3) |  2 | new
(2 rows)

新插入的数据ctid=3, 说明query2_1的xlog信息被恢复了, 但是回滚了. 如果没有执行info=new的ctid应该=2.
2. 
commit_xact1; time1; rollback_xact2; time2;  commit_xact3;  commit_xact4;
按照以上描述,   使用 recovery_target_time恢复, 
    如果recovery_target_time=time1, 不管 recovery_target_inclusive=true|false, 恢复后应该都只能看到 commit_xact1;的数据变更. 因为rollback_xact2回滚了, 不过使用ctid也能看出回滚的影响, 因为 rollback_xact2;的xlog是需要处理的.
     如果recovery_target_time=time2, 不管 recovery_target_inclusive=true|false, 恢复后应该都只能看到 commit_xact3;的数据变更. 但是绝对看不到 commit_xact4的变更, 即使 ctid也不会有影响, 因为 commit_xact4;的xlog不会被处理.
commit_xact1; -- SESSION A : 
digoal=# truncate pitr_test ;
TRUNCATE TABLE
digoal=# insert into pitr_test values (1,'test');
INSERT 0 1

time1; -- SESSION A : 
 
digoal=# select now();
              now              
-------------------------------
 2013-05-27 15:39:57.033134+08
(1 row)

rollback_xact2;   -- SESSION A : 
digoal=# begin;
BEGIN
digoal=# insert into pitr_test values (2,'rollback');
INSERT 0 1
digoal=# insert into pitr_test values (3,'rollback');
INSERT 0 1
digoal=# rollback;
ROLLBACK

time2; -- SESSION A : 
 
digoal=# select now();
              now              
-------------------------------
 2013-05-27 15:40:24.055161+08
(1 row)

commit_xact3;   -- SESSION A : 
digoal=# insert into pitr_test values (4,'new');
INSERT 0 1

commit_xact4; -- SESSION A : 
 
digoal=# insert into pitr_test values (5,'new');
INSERT 0 1

切换日志, 归档 : 
digoal=# checkpoint;
CHECKPOINT
digoal=# select pg_xlogfile_name(pg_switch_xlog());
     pg_xlogfile_name     
--------------------------
 000000010000000000000003
(1 row)
digoal=# checkpoint;
CHECKPOINT
digoal=# select pg_xlogfile_name(pg_switch_xlog());
     pg_xlogfile_name     
--------------------------
 000000010000000000000004
(1 row)

使用拷贝的备份以及归档日志还原 : 
关闭数据库 
pg93@db-172-16-3-33-> pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped

删除数据库目录以及表空间目录(为简化例子, 本例无新建的表空间.)
 
pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> rm -rf *

还原备份以及表空间软链接
 
pg93@db-172-16-3-33-> cp -r /pgdata/digoal/1921/data04/pg93backup/20130527_onlinebackup/* $PGDATA

创建pg_log目录, 存放日志 : 
 
pg93@db-172-16-3-33-> cd $PGDATA

配置$PGDATA/recovery.conf
 
pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> cp $PGHOME/share/recovery.conf.sample ./recovery.conf
vi recovery.conf
restore_command = 'recovery.sh /pgdata/digoal/1921/data04/pg93archdir %f %p'            # e.g. 'cp /mnt/server/archivedir/%f %p'
recovery_target_time = '2013-05-27 15:39:57.033134+08'
recovery_target_inclusive = false
recovery_target_timeline = 'latest'

创建恢复脚本 : 
 
pg93@db-172-16-3-33-> cat recovery.sh
#!/bin/bash

export LANG=en_US.utf8
export PGHOME=/opt/pgsql9.3
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d"`
export PATH=$PGHOME/bin:$PATH:.

BASEDIR=$1

find $BASEDIR -name $2 -exec cp {} $3 \;

添加可执行权限 : 
 
pg93@db-172-16-3-33-> chmod 700 recovery.sh

启动数据库
 
pg93@db-172-16-3-33-> pg_ctl start
server starting
pg93@db-172-16-3-33-> LOG:  00000: loaded library "pg_stat_statements"
LOCATION:  load_libraries, miscinit.c:1296

查看还原点是否与预计匹配 : 
 
pg93@db-172-16-3-33-> psql
psql (9.3devel)
Type "help" for help.
digoal=# select ctid,* from pitr_test ;
 ctid  | id | info 
-------+----+------
 (0,1) |  1 | test
(1 row)
digoal=# insert into pitr_test values(10,'new');
INSERT 0 1
digoal=# select ctid,* from pitr_test ;
 ctid  | id | info 
-------+----+------
 (0,1) |  1 | test
 (0,4) | 10 | new
(2 rows)

新插入的数据ctid=4, 说明rollback_xact2;的xlog信息被恢复了, 但是回滚了. 如果没有执行info=new的ctid应该=2.
其他几种搭配就不测试了, 原理已经介绍清楚了. 有兴趣的朋友可以自行测试.

3. 
commit_xact1;begin2;query2_1;commit_xact3;commit_xact4;query2_2;rollback2;commit_xact5;
recovery_target_xid= commit_xact1, 无论 recovery_target_inclusive=true|false, 恢复后数据库将包含 commit_xact1的数据变更;
recovery_target_xid= rollback2; 则包含xact1,xact3,xact4的变更以及xact2的回滚;
recovery_target_xid= xact5; 则包含所有xlog信息.
commit_xact1; -- SESSION A : 
digoal=# truncate pitr_test ;
TRUNCATE TABLE
digoal=# begin;
BEGIN
digoal=# select txid_current();
 txid_current 
--------------
         1691
(1 row)
digoal=# insert into pitr_test values (1,'test');
INSERT 0 1
digoal=# end;
COMMIT

begin2;query2_1; -- SESSION A : 
 
digoal=# begin;
BEGIN
digoal=# select txid_current();
 txid_current 
--------------
         1692
(1 row)
digoal=# insert into pitr_test values (1692,'test');
INSERT 0 1

commit_xact3;commit_xact4; -- SESSION B : 
 
digoal=# begin;
BEGIN
digoal=# select txid_current();
 txid_current 
--------------
         1693
(1 row)
digoal=# insert into pitr_test values (1693,'teset');
INSERT 0 1
digoal=# end;
COMMIT
digoal=# begin;
BEGIN
digoal=# select txid_current();
 txid_current 
--------------
         1694
(1 row)
digoal=# insert into pitr_test values (1693,'teset');
INSERT 0 1
digoal=# insert into pitr_test values (1694,'teset');
INSERT 0 1
digoal=# end;
COMMIT

query2_2;rollback2; -- SESSION A : 
 
digoal=# insert into pitr_test values (1692,'new_test');
INSERT 0 1
digoal=# rollback;
ROLLBACK

commit_xact5; -- SESSION A : 
 
digoal=# begin;
BEGIN
digoal=# select txid_current();
 txid_current 
--------------
         1695
(1 row)
digoal=# insert into pitr_test values (1695,'test');
INSERT 0 1
digoal=# commit;
COMMIT

切换日志, 归档 : 
 
digoal=# checkpoint;
CHECKPOINT
digoal=# select pg_xlogfile_name(pg_switch_xlog());
     pg_xlogfile_name     
--------------------------
 000000020000000000000003
(1 row)
digoal=# checkpoint;
CHECKPOINT
digoal=# select pg_xlogfile_name(pg_switch_xlog());
     pg_xlogfile_name     
--------------------------
 000000020000000000000004
(1 row)

使用拷贝的备份以及归档日志还原 : 
关闭数据库 
pg93@db-172-16-3-33-> pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped

删除数据库目录以及表空间目录(为简化例子, 本例无新建的表空间.)
 
pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> rm -rf *

还原备份以及表空间软链接
 
pg93@db-172-16-3-33-> cp -r /pgdata/digoal/1921/data04/pg93backup/20130527_onlinebackup/* $PGDATA

创建pg_log目录, 存放日志 : 
 
pg93@db-172-16-3-33-> cd $PGDATA

配置$PGDATA/recovery.conf
 
pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> cp $PGHOME/share/recovery.conf.sample ./recovery.conf
vi recovery.conf
restore_command = 'recovery.sh /pgdata/digoal/1921/data04/pg93archdir %f %p'            # e.g. 'cp /mnt/server/archivedir/%f %p'
recovery_target_xid = '1692'
recovery_target_inclusive = false
recovery_target_timeline = 'latest'

创建恢复脚本 : 
 
pg93@db-172-16-3-33-> cat recovery.sh
#!/bin/bash

export LANG=en_US.utf8
export PGHOME=/opt/pgsql9.3
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d"`
export PATH=$PGHOME/bin:$PATH:.

BASEDIR=$1

find $BASEDIR -name $2 -exec cp {} $3 \;

添加可执行权限 : 
 
pg93@db-172-16-3-33-> chmod 700 recovery.sh

启动数据库
 
pg93@db-172-16-3-33-> pg_ctl start
server starting
pg93@db-172-16-3-33-> LOG:  00000: loaded library "pg_stat_statements"
LOCATION:  load_libraries, miscinit.c:1296

查看还原点是否与预计匹配 : 
 
pg93@db-172-16-3-33-> psql
psql (9.3devel)
Type "help" for help.
digoal=# select ctid,* from pitr_test ;
 ctid  |  id  | info  
-------+------+-------
 (0,1) |    1 | test
 (0,3) | 1693 | teset
 (0,4) | 1693 | teset
 (0,5) | 1694 | teset
(4 rows)
digoal=# insert into pitr_test values(10,'new');
INSERT 0 1
digoal=# select ctid,* from pitr_test ;
 ctid  |  id  | info  
-------+------+-------
 (0,1) |    1 | test
 (0,3) | 1693 | teset
 (0,4) | 1693 | teset
 (0,5) | 1694 | teset
 (0,7) |   10 | new
(5 rows)

因为只有当record_info等于XLOG_XACT_ABORT,XLOG_XACT_COMMIT或者XLOG_XACT_COMMIT_COMPACT, 或者自建还原点时才可以被作为截至点, 
所以本例使用xid=1692作为截至点, xlog会应用到query2_2;rollback2; -- SESSION A : 这个位置(并且包含这个xlog信息).
因此在此之前应该插入了6条记录, 所以新插入的数据ctid=7, 再次说明query2_2;rollback2;的xlog信息被恢复了, 但是回滚了. 
其他几种搭配就不测试了, 原理已经介绍清楚了. 有兴趣的朋友可以自行测试.

自动备份
这个可以结合操作系统的定时脚本来实现, 在Linux中可以使用crontab.
例如 : 
1. 首先规划好存放数据库数据文件的目录. 
mkdir -p /pgdata/digoal/1921/data04/pg93backup
chown -R pg93:pg93 /pgdata/digoal/1921/data04/pg93backup
chmod 700 /pgdata/digoal/1921/data04/pg93backup

2. 编写备份脚本
 
pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> vi backup.sh
#!/bin/bash

export LANG=en_US.utf8
export PGHOME=/opt/pgsql9.3
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d"`
export PATH=$PGHOME/bin:$PATH:.
export PGDATA=/pgdata1999

BASEDIR="/pgdata/digoal/1921/data04/pg93backup"

date +%F%T

if [ ! -d $BASEDIR/$DATE ]; then
  mkdir -p $BASEDIR/$DATE
  if [ $? -eq 0 ]; then
    psql -h 127.0.0.1 -p 1999 -U postgres postgres -c "select pg_start_backup(now()::text)"
    if [ $? -eq 0 ]; then
      cp -r -L $PGDATA $BASEDIR/$DATE
    else
      echo -e "select pg_start_backup(now()::text) error"
      exit 1
    fi
    psql -h 127.0.0.1 -p 1999 -U postgres postgres -c "select pg_stop_backup()"
    date +%F%T
    echo -e "backup successed"
    exit 0
  else
    echo -e "mkdir -p $BASEDIR/$DATE error"
    exit 1
  fi
else
  echo -e "$DATE backuped, don't backup repeated"
  exit 1
fi

修改权限
 
pg93@db-172-16-3-33-> chmod 700 backup.sh

3. 测试
 
pg93@db-172-16-3-33-> ./backup.sh 
 pg_start_backup 
-----------------
 0/6000028
(1 row)

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup 
----------------
 0/60000F0
(1 row)
pg93@db-172-16-3-33-> 再次执行则退出, 因为今天已经备份了.
pg93@db-172-16-3-33-> ./backup.sh 
20130527 backuped, don't backup repeated

4. 配置crontab
首先删除当天备份 
rm -rf /pgdata/digoal/1921/data04/pg93backup/20130527

配置自动备份
 
su - pg93
crontab -e
40 16 * * * /pgdata1999/backup.sh >>/tmp/backup.log 2>&1

5. 检查是否自动备份.
 
pg93@db-172-16-3-33-> cat /tmp/backup.log 
2013-05-2716:40:01
 pg_start_backup 
-----------------
 0/8000028
(1 row)

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup 
----------------
 0/80000F0
(1 row)

2013-05-2716:40:02
backup successed

检查备份成功后把crontab调整为你需要的备份时间即可. 例如每天都凌晨2点开始备份.
 
0 2 * * * /pgdata1999/backup.sh >>/tmp/backup.log 2>&1



[参考]
weixin_34297704
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
postgres物理备份与恢复
treacle田 --love work and life......
04-11 3688
postgres物理备份与恢复 学习环境:centos 6.5 64  数据库:postgres 9.5.1 1冷备份与恢复 文件系统级别的备份是冷备份，需要停止数据库。 1.1操作步骤 (1)停止数据库 pgdb_ctl -D /pgdb/data  stop -m   fast            (2)备份数据库  tar  -jcvf /pgdb/backup/
PostgreSQL 备份与恢复
snail_aaa的专栏
05-17 2251
http://www.wohedb.com/db_html_doc/administrator_guide/adm_9_backup_and_restore.htmwww.wohedb.com    中文数据库 第九章   备份与恢复    PostgreSQL提供了三种备份和恢复的方式:SQL dump、文件系统复制和联机热备份。 每一种备份方式都有自己的优点和缺点，
postgresql的时间点还原。
weixin_44236346的博客
11-21 1642
实验目的，模拟假设每天凌晨2点做一次数据库的完全备份，但是在第二天上午10点的时候出现故障，凌晨2点到上午10点之间产生的数据不丢失，可以正常恢复。recovery_target_xid = '752' 表示指定到故障操作语句之前的一个点的编号故障操作时753。在要还原的服务器上还原数据 停服 清空dada和archive目录 还原刚刚完全备份的数据。复制主数据库的最新的归档日志到备份数据上。在备份服务上对主数据库进行完全备份。模拟出现故障，删库！在主数据库上继续写数据。
PostgreSQL的 PITR实战---运用 recovery_target_time
weixin_34319374的博客
08-07 189
磨砺技术珠矶，践行数据之道，追求卓越价值 回到上一级页面： PostgreSQL基础知识与基本操作索引页     回到顶级页面：PostgreSQL索引页 看了很多的例子，没有发现具体讲 recovery_target_time的例子，于是自己作一个吧 在开始之前，先把postgresql.conf的配置设置好： ...
don't shutdown before pg_stop_backup()
postgrechina的博客
10-14 774
在创建备份或STANDBY时，我们通常可以有两种方式一种是通过pg_basebackup，另一种是使用pg_start_backup然后COPY文件的方式。 在使用第二种方式时，正确的流程应该是： pg_start_backup COPY pg_stop_backup 但是我们试想一下，如果在COPY完后，调用pg_stop_backup前，主库关闭了，然后又起来了。
酒保：酒保-PostgreSQL备份和恢复管理器
02-05
酒保支持多种备份类型，包括基于时间点的备份（Point-in-Time Recovery, PITR）和物理备份。基于时间点的备份允许用户选择任意时间点的数据状态进行恢复，极大地提高了数据恢复的灵活性。物理备份则直接复制数据库的...
postgresql-12.4-1-windows-x64.exe
09-10
postgresql-12.4-1-windows-x64.exe
PostgreSQL DBA认证 PGCE-E-092-中级SQL题目
08-26
"PostgreSQL DBA认证 PGCE-E-092-中级SQL题目" PostgreSQL DBA认证 PGCE-E-092-中级SQL题目 PostgreSQL DBA认证PGCE-E-092-中级SQL题目是一个测试SQL掌握程度的题目，涵盖了PostgreSQL数据库管理系统的各种知识点...
PostgreSQL（postgresql-13.5-1-osx.dmg）
11-20
PostgreSQL（postgresql-13.5-1-osx.dmg）适用于Mac OS X PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES，4.2版本为基础的对象关系型数据库...
barman:Barman-PostgreSQL备份和恢复管理器
05-11
PostgreSQLBarman，备份和恢复管理器Barman（备份和恢复管理器）是一个开源管理工具，用于以Python编写的PostgreSQL服务器的灾难恢复。 它使您的组织可以在关键业务环境中对多台服务器执行远程备份，以降低风险并在...
PostgreSQL基于时间点故障恢复PITR（ point-in-time recovery ）
dimawei2225的博客
08-31 943
PostgreSQL在使用过程中经常会发生一些失误的操作，但往往是可以弥补的。但是如果真遇到了无法挽回的误操作，只能寄希望于有备份了。 接下来的故障恢复也是基于有备份的情况，没有备份的情况，目前还没有想到怎么做。 1.首先在数据库中配置日志归档 1）创建归档目录 mkdir -p /var/lib/pgsql/pg10/archive/ 2)修改p...
postgresql recovery.conf改变需要重启吗
dimawei2225的博客
06-28 885
之前在研究pgpoll时，发现trigger_file参数指定的文件存在后，会自动将standby节点提升为可写节点。不需要手动执行pg_ctl promote，但是这个时间一般有延迟，因为进程会定期检查复制过程。 今天发现primary_conninfo信息改变，复制过程不会变化，需要重启standby节点。 .................................
recovery PITR 即时恢复
weixin_30781775的博客
05-30 190
http://blog.163.com/digoal@126/blog/static/16387704020131410250983/ http://www.cnblogs.com/gaojian/archive/2013/08/07/3243045.html PITR有三种方式： 1：recovery_target_time 2：recovery_target_xid 3：recove...
Postgresql - 恢复数据库到指定时间 Point-in-Time Recovery (PITR)
chuckchen1222的博客
06-20 3620
在有些时候需要用备份数据恢复到任意的时间点，该如何操作。###################################CentOS 7 + pg 10.4###################################1. 初始化数据库参数# 创建archive目录，并授权到postgres用户mkdir /data/archive/chown -R postgres:postgre...
pg 简单备份和恢复
weixin_33672400的博客
05-13 270
pg的备份和恢复pg_dump 把一个数据库转储为纯文本文件或者是其它格式.用法: pg_dump [选项]... [数据库名字]一般选项: -f, --file=FILENAME output file or directory name -F, --format=c|d|t|p output file f...
pg_restore及psql恢复数据的用法
热门推荐
lk的窝
09-13 2万+
一、使用psql恢复SQL文本格式的数据备份（即一个包含SQL脚本的文本文件） 恢复一个SQL备份文件并忽略过程中可能发生的所有错误： psql -U postgres -f myglobals.sql 恢复一个SQL备份文件，如遇任何错误则立即停止恢复： psql -U postgres --set ON_ERROR_STOP=ON  -f myglobals.sql 将SQL
【postgresql】备份恢复2-恢复到还原点
05-07 2682
前言 上一节（https://blog.csdn.net/bendywu/article/details/124628719?csdn_share_tail=%7B%22type%22%3A%22blog%22%2C%22rType%22%3A%22article%22%2C%22rId%22%3A%22124628719%22%2C%22source%22%3A%22bendywu%22%7D&ctrtid=4iBKUhttps://blog.csdn.net/bendywu/article/d
ERD-ONLINE docker
最新发布
08-30
对于ERD-ONLINE的Docker部署，你可以按照以下步骤进行操作： 1. 首先，你需要确保在你的机器上已经安装了Docker和Docker Compose。你可以在命令行中运行`docker --version`和`docker-compose --version`来验证它们...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值