pg_rman備份恢復(fù)測(cè)試

環(huán)境描述

我們提供的服務(wù)有：網(wǎng)站制作、成都網(wǎng)站制作、微信公眾號(hào)開(kāi)發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、新縣ssl等。為上千多家企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的新縣網(wǎng)站制作公司

1.OS

CentOS Linux release 7.2.1511 (Core) X64

2.PostgreSQL

PostgreSQL 9.6.1

3.pg_rman

pg_rman-1.3.3-pg96.tar.gz v1.3.3

注意：請(qǐng)下載版本對(duì)應(yīng)的源碼包。

https://github.com/ossc-db/pg_rman/releases/download/v1.3.3/pg_rman-1.3.3-pg96.tar.gz

pg_rman-1.3.3.tar.gz（此源碼編譯過(guò)程中報(bào)錯(cuò)）

系統(tǒng)包

zlib-devel

二、pg_rman安裝

1.安裝pg_rman

root用戶登錄

export PATH=/opt/pgsql/9.6.1/bin:$PATH

export LD_LIBRARY_PATH=/opt/pgsql/9.6.1/lib

export MANPATH=/opt/pgsql/9.6.1/share/man:$MANPATH

# tar zxvf pg_rman-9_6_STABLE.tar.gz

# cd pg_rman-9_6_STABLE/

# make

......

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 backup.o catalog.o data.o delete.o dir.o init.o parray.o pg_rman.o restore.o show.o util.o validate.o xlog.o pgsql_src/pg_ctl.o pgut/pgut.o pgut/pgut-port.o -L/opt/pgsql/9.6.1/lib -lpgcommon -lpgport -L/opt/pgsql/9.6.1/lib -lpq -L/opt/pgsql/9.6.1/lib -Wl,--as-needed -Wl,-rpath,'/opt/pgsql/9.6.1/lib',--enable-new-dtags -lpgcommon -lpgport -lz -lreadline -lrt -lcrypt -ldl -lm -o pg_rman

# make install

/usr/bin/mkdir -p '/opt/pgsql/9.6.1/bin'

/usr/bin/install -c pg_rman '/opt/pgsql/9.6.1/bin'

2.安裝驗(yàn)證

su - postgres

$ pg_rman --version

pg_rman 1.3.3

3.配置數(shù)據(jù)庫(kù)參數(shù)

wal_level = replica

archive_mode = on

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

--- root user

mkdir /backup_pg_rman /pg_arclog

chown -R postgres:postgres /backup_pg_rman

chown -R postgres:postgres /pg_arclog

--- postgresql

# pg_rman init -B $backup_dir

三、備份恢復(fù)測(cè)試

1.備份數(shù)據(jù)(full<0> + incremental<1>)

# full

export PGDATA=/pgdata96

export BACKUP_PATH=/backup_pg_rman

$ echo $PGDATA

/pgdata96

$ echo $BACKUP_PATH

/backup_pg_rman

--- init backup dir: pg_rman init -B $backup_dir -D $PGDATA(當(dāng)不配置環(huán)境變量時(shí)，手工指定，注意路徑末尾不添加'/'結(jié)束符)

$ pg_rman init

INFO: ARCLOG_PATH is set to '/pg_arclog'

INFO: SRVLOG_PATH is set to '/pgdata96/pg_log'

$ cat $BACKUP_PATH/pg_rman.ini

ARCLOG_PATH='/pg_arclog'

SRVLOG_PATH='/pgdata96/pg_log'

--- full backup

$ pg_rman backup --backup-mode=full --with-serverlog --progress

INFO: copying database files

Processed 1172 of 1172 files, skipped 0

INFO: copying archived WAL files

Processed 3 of 3 files, skipped 0

INFO: copying server log files

Processed 4 of 4 files, skipped 0

INFO: backup complete

INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

--- validate backup

$ pg_rman validate, status: done

INFO: validate: "2017-03-06 16:43:39" backup, archive log files and server log files by CRC

INFO: backup "2017-03-06 16:43:39" is valid

--- show backup, status: ok

$ pg_rman show

==========================================================

StartTime Mode Duration Size TLI Status

==========================================================

2017-03-06 16:43:39 FULL 0m 58MB 1 OK

--- incremental

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

INFO: copying database files

Processed 1172 of 1172 files, skipped 1115

INFO: copying archived WAL files

Processed 48 of 48 files, skipped 3

INFO: copying server log files

Processed 4 of 4 files, skipped 3

INFO: backup complete

INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

--- validate backup

$ pg_rman validate

INFO: validate: "2017-03-06 17:04:45" backup, archive log files and server log files by CRC

INFO: backup "2017-03-06 17:04:45" is valid

--- show, status: ok

$ pg_rman show detail

============================================================================================================

StartTime Mode Duration Data ArcLog SrvLog Total Compressed CurTLI ParentTLI Status

============================================================================================================

2017-03-06 17:04:45 INCR 0m 401MB 738MB 27kB 1136MB false 1 0 OK

2017-03-06 16:43:39 FULL 0m 30MB 33MB 206kB 58MB false 1 0 OK

2.模擬災(zāi)難恢復(fù)

1).刪除PGDATA 目錄下所有文件

安全停止數(shù)據(jù)庫(kù),刪除文件

$ pg_ctl stop -m immediate -D /pgdata96/

$ cd /pgdata96

$ rm -rf *.*

2).恢復(fù)備份

--- postgres user

$ export PGDATA=/pgdata96

$ export BACKUP_PATH=/backup_pg_rman

$ pg_rman restore

WARNING: pg_controldata file "/pgdata96/global/pg_control" does not exist

INFO: the recovery target timeline ID is not given

INFO: use timeline ID of latest full backup as recovery target: 1

INFO: calculating timeline branches to be used to recovery target point

INFO: searching latest full backup which can be used as restore start point

INFO: found the full backup can be used as base in recovery: "2017-03-06 16:43:39"

INFO: copying online WAL files and server log files

INFO: clearing restore destination

INFO: validate: "2017-03-06 16:43:39" backup, archive log files and server log files by SIZE

INFO: backup "2017-03-06 16:43:39" is valid

INFO: restoring database files from the full mode backup "2017-03-06 16:43:39"

INFO: searching incremental backup to be restored

INFO: validate: "2017-03-06 17:04:45" backup, archive log files and server log files by SIZE

INFO: backup "2017-03-06 17:04:45" is valid

INFO: restoring database files from the incremental mode backup "2017-03-06 17:04:45"

INFO: searching backup which contained archived WAL files to be restored

INFO: backup "2017-03-06 17:04:45" is valid

INFO: restoring WAL files from backup "2017-03-06 17:04:45"

INFO: restoring online WAL files and server log files

INFO: generating recovery.conf

INFO: restore complete

HINT: Recovery will start automatically when the PostgreSQL server is started.

3).啟動(dòng)數(shù)據(jù)庫(kù)驗(yàn)證數(shù)據(jù)

# /etc/init.d/postgresql start

Starting PostgreSQL: ok

切換至postgres用戶,然后驗(yàn)證數(shù)據(jù)

基于時(shí)間點(diǎn)恢復(fù)

建立測(cè)試數(shù)據(jù)

testdb=# create table tbl(id int primary key, first varchar(20),second varchar(20));

CREATE TABLE

testdb=# INSERT INTO tbl VALUES(generate_series(1,1000000), 'first'||(random()*(10^3))::integer, 'second'||(random()*(10^3))::integer);

INSERT 0 1000000

testdb=#

建立全備份

--- postgres user

$ pg_rman backup --backup-mode=full --with-serverlog --progress

INFO: copying database files

Processed 1172 of 1172 files, skipped 0

INFO: copying archived WAL files

Processed 27 of 27 files, skipped 0

INFO: copying server log files

Processed 1 of 1 files, skipped 0

INFO: backup complete

INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

$ pg_rman show

==========================================================

StartTime Mode Duration Size TLI Status

==========================================================

2017-03-07 16:57:33 FULL 0m 433MB 4 DONE

$ pg_rman validate

INFO: validate: "2017-03-07 16:57:33" backup, archive log files and server log files by CRC

INFO: backup "2017-03-07 16:57:33" is valid

[postgres@localhost ~]$ pg_rman show

==========================================================

StartTime Mode Duration Size TLI Status

==========================================================

2017-03-07 16:57:33 FULL 0m 433MB 4 OK

drop 表

testdb=# drop table tbl;

DROP TABLE

testdb=# \q

停止數(shù)據(jù)庫(kù)

--- root user

# /etc/init.d/postgresql stop

恢復(fù)數(shù)據(jù)庫(kù)到指定時(shí)間

$ pg_rman restore --recovery-target-time '2017-03-07 16:58:33'

INFO: the recovery target timeline ID is not given

INFO: use timeline ID of current database cluster as recovery target: 4

INFO: calculating timeline branches to be used to recovery target point

INFO: searching latest full backup which can be used as restore start point

INFO: found the full backup can be used as base in recovery: "2017-03-07 16:57:33"

INFO: copying online WAL files and server log files

INFO: clearing restore destination

INFO: validate: "2017-03-07 16:57:33" backup, archive log files and server log files by SIZE

INFO: backup "2017-03-07 16:57:33" is valid

INFO: restoring database files from the full mode backup "2017-03-07 16:57:33"

INFO: searching incremental backup to be restored

INFO: searching backup which contained archived WAL files to be restored

INFO: backup "2017-03-07 16:57:33" is valid

INFO: restoring WAL files from backup "2017-03-07 16:57:33"

INFO: restoring online WAL files and server log files

INFO: generating recovery.conf

INFO: restore complete

HINT: Recovery will start automatically when the PostgreSQL server is started.

啟動(dòng)數(shù)據(jù)庫(kù)

--- root user

# /etc/init.d/postgresql start

驗(yàn)證數(shù)據(jù)

--- postgres user

$ psql testdb

psql (9.6.1)

Type "help" for help.

testdb=# \dt

List of relations

Schema | Name | Type | Owner

--------+------+-------+----------

public | tbl | table | postgres

(1 row)

testdb=# select count(*) from tbl;

count

---------

1000000

(1 row)

testdb=# \q

異常停止數(shù)據(jù)恢復(fù)

描述：當(dāng)數(shù)據(jù)庫(kù)沒(méi)有成功執(zhí)行檢查點(diǎn)完成,恢復(fù)時(shí)可能會(huì)丟失數(shù)據(jù),錯(cuò)誤排查

現(xiàn)象：?jiǎn)?dòng)數(shù)據(jù)庫(kù)失敗時(shí)

$ more postgresql-Mon.log

2017-03-06 17:20:47 CST [3240]: [1-1] user=,db= LOG: database system was interrupted; last known up at 2017-03-06 17:04:51 CST

2017-03-06 17:20:47 CST [3240]: [2-1] user=,db= LOG: starting archive recovery

2017-03-06 17:20:47 CST [3240]: [3-1] user=,db= LOG: invalid primary checkpoint record

2017-03-06 17:20:47 CST [3240]: [4-1] user=,db= LOG: invalid secondary checkpoint record

2017-03-06 17:20:47 CST [3240]: [5-1] user=,db= PANIC: could not locate a valid checkpoint record

2017-03-06 17:20:47 CST [3238]: [3-1] user=,db= LOG: startup process (PID 3240) was terminated by signal 6: Aborted

2017-03-06 17:20:47 CST [3238]: [4-1] user=,db= LOG: aborting startup due to startup process failure

2017-03-06 17:20:47 CST [3238]: [5-1] user=,db= LOG: database system is shut down

2017-03-06 17:21:23 CST [3269]: [1-1] user=,db= LOG: database system was interrupted; last known up at 2017-03-06 17:04:51 CST

2017-03-06 17:21:23 CST [3269]: [2-1] user=,db= LOG: starting archive recovery

2017-03-06 17:21:23 CST [3269]: [3-1] user=,db= LOG: invalid primary checkpoint record

2017-03-06 17:21:23 CST [3269]: [4-1] user=,db= LOG: invalid secondary checkpoint record

2017-03-06 17:21:23 CST [3269]: [5-1] user=,db= PANIC: could not locate a valid checkpoint record

2017-03-06 17:21:23 CST [3267]: [3-1] user=,db= LOG: startup process (PID 3269) was terminated by signal 6: Aborted

2017-03-06 17:21:23 CST [3267]: [4-1] user=,db= LOG: aborting startup due to startup process failure

2017-03-06 17:21:23 CST [3267]: [5-1] user=,db= LOG: database system is shut down

處理步驟說(shuō)明：

重置事務(wù)日志

僅保留備份時(shí)數(shù)據(jù)

$ pg_resetxlog -f /pgdata96

Transaction log reset

然后啟動(dòng)數(shù)據(jù)庫(kù),驗(yàn)證部分?jǐn)?shù)據(jù)

當(dāng)前標(biāo)題：pg_rman備份恢復(fù)測(cè)試
網(wǎng)頁(yè)網(wǎng)址：http://muchs.cn/article36/iegppg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站建設(shè)、手機(jī)網(wǎng)站建設(shè)、ChatGPT、品牌網(wǎng)站制作、自適應(yīng)網(wǎng)站、品牌網(wǎng)站設(shè)計(jì)

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

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