本篇内容介绍了“怎么恢复PostgreSQL数据文件损坏”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

在数据文件损坏,数据库无法正常启动的时候,Oracle可通过数据库备份+归档日志+在线日志实现数据库的完整恢复,与之类似,PostgreSQL也可以通过数据库备份+归档WAL日志+在线WAL日志进行完整恢复.

1.执行备份
2.构造测试数据
3.删除数据文件(保留WAL日志文件)
4.使用备份+在线WAL日志文件进行完整恢复

参数配置

archive_mode=onarchive_command='/home/xdb/archive.sh'wal_level=replicamax_wal_size=4GBmin_wal_size=1024MB

详细请参考Backup&Recovery#1(基本操作)

执行备份
查看当前的LSN

testdb=#selectpg_current_wal_lsn();pg_current_wal_lsn--------------------0/39A63C78(1row)

使用工具pg_basebackup对数据库进行备份

testdb=#\q[xdb@localhosttestdb]$pg_basebackup-D/data/backup/0312-1/-l0312-1-v-Ftar-zpg_basebackup:initiatingbasebackup,waitingforcheckpointtocompletepg_basebackup:checkpointcompletedpg_basebackup:write-aheadlogstartpoint:0/3A000108ontimeline15pg_basebackup:startingbackgroundWALreceiverpg_basebackup:createdtemporaryreplicationslot"pg_basebackup_2978"pg_basebackup:write-aheadlogendpoint:0/3A0001D8pg_basebackup:waitingforbackgroundprocesstofinishstreaming...pg_basebackup:basebackupcompleted[xdb@localhosttestdb]$[xdb@localhosttestdb]$ll/data/backup/0312-1/total44384-rw-------.1xdbxdb45427619Mar1217:30base.tar.gz-rw-------.1xdbxdb18927Mar1217:30pg_wal.tar.gz[xdb@localhosttestdb]$####[xdb@localhost~]$psql-dtestdbpsql(11.2)Type"help"forhelp.testdb=#checkpoint;CHECKPOINTtestdb=#

构造测试数据
创建数据表,插入数据

testdb=#createtabletbl01(idint,c1char(200),c2char(200));CREATETABLEtestdb=#insertintotbl01selectf,f||'c1',f||'c2'fromgenerate_series(1,100000)f;INSERT0100000testdb=#selectpg_current_wal_lsn();pg_current_wal_lsn--------------------0/3DD39618(1row)testdb=#createtabletbl02(idint,c1char(200),c2char(200));CREATETABLEtestdb=#insertintotbl02selectf,f||'c1',f||'c2'fromgenerate_series(1,100000)f;INSERT0100000testdb=#selectpg_current_wal_lsn();pg_current_wal_lsn--------------------0/40A62F20(1row)testdb=#

当前在线日志文件为0000000F0000000000000040

[xdb@localhosttestdb]$ll$PGDATA/pg_waltotal196632-rw-------.1xdbxdb42Mar1217:1000000008.history-rw-------.1xdbxdb85Mar1217:100000000C.history-rw-------.1xdbxdb16777216Mar1217:100000000E0000000000000039.partial-rw-------.1xdbxdb129Mar1217:100000000E.history-rw-------.1xdbxdb16777216Mar1217:300000000F000000000000003A-rw-------.1xdbxdb323Mar1217:300000000F000000000000003A.00000108.backup-rw-------.1xdbxdb16777216Mar1217:320000000F000000000000003B-rw-------.1xdbxdb16777216Mar1217:320000000F000000000000003C-rw-------.1xdbxdb16777216Mar1217:320000000F000000000000003D-rw-------.1xdbxdb16777216Mar1217:320000000F000000000000003E-rw-------.1xdbxdb16777216Mar1217:320000000F000000000000003F-rw-------.1xdbxdb16777216Mar1217:320000000F0000000000000040-rw-------.1xdbxdb16777216Mar1217:100000000F0000000000000041-rw-------.1xdbxdb16777216Mar1217:100000000F0000000000000042-rw-------.1xdbxdb16777216Mar1217:100000000F0000000000000043-rw-------.1xdbxdb16777216Mar1217:300000000F0000000000000044-rw-------.1xdbxdb173Mar1217:110000000F.historydrwx------.2xdbxdb4096Mar1217:32archive_status[xdb@localhosttestdb]$

归档日志文件信息

[xdb@localhosttestdb]$ll/data/archivelog/20190312/total245772-rw-------.1xdbxdb16777216Mar1217:060000000E0000000000000032-rw-------.1xdbxdb16777216Mar1217:060000000E0000000000000033-rw-------.1xdbxdb323Mar1217:060000000E0000000000000033.00000028.backup-rw-------.1xdbxdb16777216Mar1217:070000000E0000000000000034-rw-------.1xdbxdb16777216Mar1217:070000000E0000000000000035-rw-------.1xdbxdb16777216Mar1217:070000000E0000000000000036-rw-------.1xdbxdb16777216Mar1217:070000000E0000000000000037-rw-------.1xdbxdb16777216Mar1217:070000000E0000000000000038-rw-------.1xdbxdb16777216Mar1217:110000000E0000000000000039.partial-rw-------.1xdbxdb16777216Mar1217:300000000F0000000000000039-rw-------.1xdbxdb16777216Mar1217:300000000F000000000000003A-rw-------.1xdbxdb323Mar1217:300000000F000000000000003A.00000108.backup-rw-------.1xdbxdb16777216Mar1217:320000000F000000000000003B-rw-------.1xdbxdb16777216Mar1217:320000000F000000000000003C-rw-------.1xdbxdb16777216Mar1217:320000000F000000000000003D-rw-------.1xdbxdb16777216Mar1217:320000000F000000000000003E-rw-------.1xdbxdb16777216Mar1217:320000000F000000000000003F-rw-------.1xdbxdb173Mar1217:110000000F.history[xdb@localhosttestdb]$

模拟数据文件损坏
删除数据文件目录,kill postgres进程

[xdb@localhosttestdb]$rm-rf./base[xdb@localhosttestdb]$psql-dtestdbpsql:FATAL:database"testdb"doesnotexistDETAIL:Thedatabasesubdirectory"base/16384"ismissing.[xdb@localhosttestdb]$ps-ef|greppostgresxdb29141017:11pts/200:00:00/appdb/xdb/pg11.2/bin/postgresxdb29152914017:11?00:00:00postgres:loggerxdb29182914017:11?00:00:00postgres:checkpointerxdb29192914017:11?00:00:00postgres:backgroundwriterxdb29212914017:11?00:00:00postgres:statscollectorxdb29252914017:11?00:00:00postgres:walwriterxdb29262914017:11?00:00:00postgres:autovacuumlauncherxdb29272914017:11?00:00:00postgres:archiverlastwas0000000F000000000000003Fxdb29282914017:11?00:00:00postgres:logicalreplicationlauncherxdb29772914017:30?00:00:00postgres:xdbtestdb[local]idlexdb30142519017:33pts/200:00:00grep--color=autopostgres[xdb@localhosttestdb]$kill-92914[xdb@localhosttestdb]$ps-ef|greppostgresxdb30162519017:34pts/200:00:00grep--color=autopostgres

执行恢复
备份在线日志

[xdb@localhost~]$mkdir/data/backup/wal[xdb@localhosttestdb]$cp-R./pg_wal/*/data/backup/wal/[xdb@localhosttestdb]$[xdb@localhosttestdb]$ll/data/backup/wal/total196632-rw-------.1xdbxdb42Mar1217:3400000008.history-rw-------.1xdbxdb85Mar1217:340000000C.history-rw-------.1xdbxdb16777216Mar1217:340000000E0000000000000039.partial-rw-------.1xdbxdb129Mar1217:340000000E.history-rw-------.1xdbxdb16777216Mar1217:340000000F000000000000003A-rw-------.1xdbxdb323Mar1217:340000000F000000000000003A.00000108.backup-rw-------.1xdbxdb16777216Mar1217:340000000F000000000000003B-rw-------.1xdbxdb16777216Mar1217:340000000F000000000000003C-rw-------.1xdbxdb16777216Mar1217:340000000F000000000000003D-rw-------.1xdbxdb16777216Mar1217:340000000F000000000000003E-rw-------.1xdbxdb16777216Mar1217:340000000F000000000000003F-rw-------.1xdbxdb16777216Mar1217:340000000F0000000000000040-rw-------.1xdbxdb16777216Mar1217:340000000F0000000000000041-rw-------.1xdbxdb16777216Mar1217:340000000F0000000000000042-rw-------.1xdbxdb16777216Mar1217:340000000F0000000000000043-rw-------.1xdbxdb16777216Mar1217:340000000F0000000000000044-rw-------.1xdbxdb173Mar1217:340000000F.historydrwx------.2xdbxdb4096Mar1217:34archive_status[xdb@localhosttestdb]$

从数据库备份中恢复

[xdb@localhosttestdb]$rm-rf*[xdb@localhosttestdb]$cp/data/backup/0312-1/base.tar.gz./[xdb@localhosttestdb]$tarzxfbase.tar.gz

恢复在线日志

cp-R/data/backup/wal/0000000F0000000000000040./pg_wal[xdb@localhosttestdb]$ll./pg_waltotal16384-rw-------.1xdbxdb16777216Mar1217:350000000F0000000000000040drwx------.2xdbxdb6Mar1217:30archive_status

创建recovery.conf文件

[xdb@localhosttestdb]$vimrecovery.conf[xdb@localhosttestdb]$catrecovery.conf#Recoveryrestore_command='cp/data/archivelog/20190312/%f%p'#restore_target=XX

执行恢复,启动数据库

[xdb@localhosttestdb]$pg_ctlstartwaitingforservertostart....2019-03-1217:36:21.310CST[3030]LOG:listeningonIPv4address"0.0.0.0",port54322019-03-1217:36:21.310CST[3030]LOG:listeningonIPv6address"::",port54322019-03-1217:36:21.328CST[3030]LOG:listeningonUnixsocket"/tmp/.s.PGSQL.5432"2019-03-1217:36:21.341CST[3030]LOG:redirectinglogoutputtologgingcollectorprocess2019-03-1217:36:21.341CST[3030]HINT:Futurelogoutputwillappearindirectory"pg_log".doneserverstarted

日志输出

2019-03-1217:36:21.341CST,,,3030,,5c877d95.bd6,1,,2019-03-1217:36:21CST,,0,LOG,00000,"endinglogoutputtostderr",,"Futurelogoutputwillgotologdestination""csvlog"".",,,,,,,""2019-03-1217:36:21.350CST,,,3032,,5c877d95.bd8,1,,2019-03-1217:36:21CST,,0,LOG,00000,"databasesystemwasinterrupted;lastknownupat2019-03-1217:30:44CST",,,,,,,,,""2019-03-1217:36:21.358CST,,,3032,,5c877d95.bd8,2,,2019-03-1217:36:21CST,,0,LOG,00000,"startingarchiverecovery",,,,,,,,,""2019-03-1217:36:21.372CST,,,3032,,5c877d95.bd8,3,,2019-03-1217:36:21CST,,0,LOG,00000,"restoredlogfile""0000000F.history""fromarchive",,,,,,,,,""2019-03-1217:36:21.486CST,,,3032,,5c877d95.bd8,4,,2019-03-1217:36:21CST,,0,LOG,00000,"restoredlogfile""0000000F000000000000003A""fromarchive",,,,,,,,,""2019-03-1217:36:21.693CST,,,3032,,5c877d95.bd8,5,,2019-03-1217:36:21CST,1/0,0,LOG,00000,"redostartsat0/3A000108",,,,,,,,,""2019-03-1217:36:21.696CST,,,3032,,5c877d95.bd8,6,,2019-03-1217:36:21CST,1/0,0,LOG,00000,"consistentrecoverystatereachedat0/3A0001D8",,,,,,,,,""2019-03-1217:36:21.696CST,,,3030,,5c877d95.bd6,2,,2019-03-1217:36:21CST,,0,LOG,00000,"databasesystemisreadytoacceptreadonlyconnections",,,,,,,,,""2019-03-1217:36:21.826CST,,,3032,,5c877d95.bd8,7,,2019-03-1217:36:21CST,1/0,0,LOG,00000,"restoredlogfile""0000000F000000000000003B""fromarchive",,,,,,,,,""2019-03-1217:36:22.245CST,,,3032,,5c877d95.bd8,8,,2019-03-1217:36:21CST,1/0,0,LOG,00000,"restoredlogfile""0000000F000000000000003C""fromarchive",,,,,,,,,""2019-03-1217:36:22.614CST,,,3032,,5c877d95.bd8,9,,2019-03-1217:36:21CST,1/0,0,LOG,00000,"restoredlogfile""0000000F000000000000003D""fromarchive",,,,,,,,,""2019-03-1217:36:23.039CST,,,3032,,5c877d95.bd8,10,,2019-03-1217:36:21CST,1/0,0,LOG,00000,"restoredlogfile""0000000F000000000000003E""fromarchive",,,,,,,,,""2019-03-1217:36:23.342CST,,,3032,,5c877d95.bd8,11,,2019-03-1217:36:21CST,1/0,0,LOG,00000,"restoredlogfile""0000000F000000000000003F""fromarchive",,,,,,,,,""2019-03-1217:36:23.874CST,,,3032,,5c877d95.bd8,12,,2019-03-1217:36:21CST,1/0,0,LOG,00000,"invalidrecordlengthat0/40A63B08:wanted24,got0",,,,,,,,,""2019-03-1217:36:23.874CST,,,3032,,5c877d95.bd8,13,,2019-03-1217:36:21CST,1/0,0,LOG,00000,"redodoneat0/40A63AD0",,,,,,,,,""2019-03-1217:36:23.874CST,,,3032,,5c877d95.bd8,14,,2019-03-1217:36:21CST,1/0,0,LOG,00000,"lastcompletedtransactionwasatlogtime2019-03-1217:33:32.760492+08",,,,,,,,,""2019-03-1217:36:23.879CST,,,3032,,5c877d95.bd8,15,,2019-03-1217:36:21CST,1/0,0,LOG,00000,"selectednewtimelineID:16",,,,,,,,,""2019-03-1217:36:24.773CST,,,3032,,5c877d95.bd8,16,,2019-03-1217:36:21CST,1/0,0,LOG,00000,"archiverecoverycomplete",,,,,,,,,""2019-03-1217:36:24.777CST,,,3032,,5c877d95.bd8,17,,2019-03-1217:36:21CST,1/0,0,LOG,00000,"restoredlogfile""0000000F.history""fromarchive",,,,,,,,,""2019-03-1217:36:25.589CST,,,3030,,5c877d95.bd6,3,,2019-03-1217:36:21CST,,0,LOG,00000,"databasesystemisreadytoacceptconnections",,,,,,,,,""

验证数据

[xdb@localhosttestdb]$psql-dtestdbpsql(11.2)Type"help"forhelp.testdb=#testdb=#selectcount(*)fromtbl01;count--------100000(1row)testdb=#selectcount(*)fromt02;count--------100000(1row)testdb=#

时间线历史文件,在归档目录和pg_wal目录下均存在以当前时间线命名的history,该文件说明了该Cluster的历史.

[xdb@localhost~]$cat/data/archivelog/20190312/00000010.history70/27000000norecoverytargetspecified80/2A0012E8norecoverytargetspecified120/32000000norecoverytargetspecified140/39A63BD0norecoverytargetspecified150/40A63B08norecoverytargetspecified[xdb@localhost~]$cat$PGDATA/pg_wal/00000010.history70/27000000norecoverytargetspecified80/2A0012E8norecoverytargetspecified120/32000000norecoverytargetspecified140/39A63BD0norecoverytargetspecified150/40A63B08norecoverytargetspecified[xdb@localhost~]$

“怎么恢复PostgreSQL数据文件损坏”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！