postgres的增量恢復(fù)

DBA童鞋對(duì)增量恢復(fù)的概念一定很熟悉，與MySQL的增量恢復(fù)類似，使用“t1時(shí)刻的全備”+“t1至t2時(shí)刻的wal日志”，即可將postgres恢復(fù)至t2時(shí)刻。

創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)、紫云網(wǎng)絡(luò)推廣、小程序設(shè)計(jì)、紫云網(wǎng)絡(luò)營銷、紫云企業(yè)策劃、紫云品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎(jiǎng)；創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供紫云建站搭建服務(wù)，24小時(shí)服務(wù)熱線：028-86922220，官方網(wǎng)址：muchs.cn

前期準(zhǔn)備：
配置postgres.conf：
wal_level=archive 或 hot_standby 或更高級(jí)別
archive_mode = on
archive_command='DATE=date +%Y%m%d;DIR="/paic/pg6666/pg_archlog/$DATE";(test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f'

備份腳本backup.sh：

#!/bin/bash 

export LANG=en_US.utf8 
export PGHOME=/paic/postgres/base/9.4.0 
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=/paic/pg6666/data

BASEDIR="/paic/postgres/home/postgres/pg_bak" 

date +%F-%T 

if [ ! -d $BASEDIR/$DATE ]; then 
  mkdir -p $BASEDIR/$DATE 
  if [ $? -eq 0 ]; then 
    psql -h 127.0.0.1 -p 6666 -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 6666 -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

恢復(fù)腳本recovery.sh:

#!/bin/bash 
export LANG=en_US.utf8 
export PGHOME=/paic/postgres/base/9.4.0 
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH 
export PATH=$PGHOME/bin:$PATH:. 
export PGDATA=/paic/pg6666/data
export DATE=`date +"%Y%m%d"`

if [ -z "$1" ]; then
  echo "1st argument is empty!"
else
  if [ -z "$2" ]; then
    echo "2nd argument is empty!"
  else
    if [ -f $PGDATA/postmaster.pid ]; then
      echo "shutdown database first!"
    else 
      cd $PGDATA
      rm -rf *
      cp -r /paic/postgres/home/postgres/pg_bak/$DATE/data/* $PGDATA/
      cd $PGDATA/pg_xlog
      rm -rf *
      cd $PGDATA
      cp $PGHOME/share/recovery.conf.sample ./recovery.conf
      echo restore_command = \'cp /paic/pg6666/pg_archlog/$DATE/%f %p\' >> ./recovery.conf
      echo recovery_target_time = \'$1 $2\' >> ./recovery.conf
      pg_ctl start
    fi
  fi
fi

backup.sh和recovery.sh中的目錄請自行修改。

模擬故障恢復(fù)：
1.準(zhǔn)備階段
-bash-4.1$ psql
psql (9.4.0)
Type "help" for help.
postgres=# \c mydb alex
You are now connected to database "mydb" as user "alex".
確認(rèn)數(shù)據(jù)庫初始狀態(tài)
mydb=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | test | table | alex
(1 row)
此時(shí)mydb數(shù)據(jù)庫只有test表

2.現(xiàn)在嘗試回滾數(shù)據(jù)庫至指定時(shí)間點(diǎn)
-bash-4.1$ pg_ctl stop -m fast
waiting for server to shut down..... done
server stopped
-bash-4.1$ cd --我的recovery.sh 文件放在home目錄，所以需切換目錄
-bash-4.1$ . recovery.sh 2017-12-22 13:52:00 --傳入時(shí)間參數(shù)$1:2017-12-22, $2:13:52:00 并執(zhí)行腳本
pg_ctl: another server might be running; trying to start server anyway
server starting
-bash-4.1$ 2017-12-22 13:54:26 HKT:undefined:[13323]: LOG: redirecting log output to logging collector process
2017-12-22 13:54:26 HKT:undefined:[13323]: HINT: Future log output will appear in directory "/paic/pg6666/data/pg_log".
-bash-4.1$
-bash-4.1$
-bash-4.1$ psql
psql (9.4.0)
Type "help" for help.
postgres=# \c mydb alex
You are now connected to database "mydb" as user "alex".
mydb=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | aaa | table | alex
public | test | table | alex
(2 rows)
mydb=# \q
可知目前數(shù)據(jù)庫已回滾至2017-12-22 13:52:00時(shí)，剛創(chuàng)建完表aaa狀態(tài)

繼續(xù)測試
-bash-4.1$ pg_ctl stop
waiting for server to shut down.... done
server stopped
-bash-4.1$ cd
-bash-4.1$ . recovery.sh 2017-12-22 13:53:10
pg_ctl: another server might be running; trying to start server anyway
server starting
-bash-4.1$ 2017-12-22 13:56:39 HKT:undefined:[13390]: LOG: redirecting log output to logging collector process
2017-12-22 13:56:39 HKT:undefined:[13390]: HINT: Future log output will appear in directory "/paic/pg6666/data/pg_log".
-bash-4.1$
-bash-4.1$ psql
psql (9.4.0)
Type "help" for help.
postgres=# \c mydb alex
You are now connected to database "mydb" as user "alex".
mydb=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | aaa | table | alex
public | bbb | table | alex
public | test | table | alex
(3 rows)
可知目前數(shù)據(jù)庫已回滾至2017-12-22 13:53:10時(shí)，剛創(chuàng)建完表bbb的狀態(tài)

執(zhí)行recovery.sh回滾數(shù)據(jù)庫
-bash-4.1$ pg_ctl stop
waiting for server to shut down.... done
server stopped
-bash-4.1$ cd
-bash-4.1$ . recovery.sh 2017-12-22 14:00:00
pg_ctl: another server might be running; trying to start server anyway
server starting
-bash-4.1$ 2017-12-22 14:02:09 HKT:undefined:[13583]: LOG: redirecting log output to logging collector process
2017-12-22 14:02:09 HKT:undefined:[13583]: HINT: Future log output will appear in directory "/paic/pg6666/data/pg_log".
-bash-4.1$
-bash-4.1$ psql
psql (9.4.0)
Type "help" for help.
postgres=# \c mydb alex
You are now connected to database "mydb" as user "alex".
mydb=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | aaa | table | alex
public | bbb | table | alex
public | test | table | alex
(3 rows)
可知目前數(shù)據(jù)庫已回滾至2017-12-22 13:14:00時(shí)，表test未被刪除的狀態(tài)

本實(shí)驗(yàn)僅限在測試環(huán)境模擬，有助于理解postgres的備份恢復(fù)機(jī)制，禁止用于生產(chǎn)！

當(dāng)前題目：postgres的增量恢復(fù)
當(dāng)前路徑：http://muchs.cn/article0/iehjoo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供Google、品牌網(wǎng)站建設(shè)、定制開發(fā)、小程序開發(fā)、自適應(yīng)網(wǎng)站、商城網(wǎng)站

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容