Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to recover the corruption of PostgreSQL data files". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

When the data file is damaged and the database cannot be started normally, Oracle can realize the complete recovery of the database through database backup + archived log + online log. Similarly, PostgreSQL can also restore the database completely through database backup + archived WAL log + online WAL log.

I. scene

1. Perform a backup

two。 Construct test data

3. Delete data files (keep WAL log files)

4. Full restore using backup + online WAL log files

Second, simulated complete recovery

Parameter configuration

Archive_mode = on archive_command ='/ home/xdb/archive.sh'wal_level = replicamax_wal_size = 4GBmin_wal_size = 1024MB

For more information, please refer to Backup&Recovery#1 (basic Operation)

Perform a backup

View the current LSN

Testdb=# select pg_current_wal_lsn (); pg_current_wal_lsn-0/39A63C78 (1 row)

Use the tool pg_basebackup to back up the database

Testdb=#\ Q [xdb@localhost testdb] $pg_basebackup-D / data/backup/0312-1 /-l 0312-1-v-F tar-zpg_basebackup: initiating basebackup Waiting for checkpoint to completepg_basebackup: checkpoint completedpg_basebackup: write-ahead log start point: 0/3A000108 on timeline 15pg_basebackup: starting background WAL receiverpg_basebackup: created temporary replication slot "pg_basebackup_2978" pg_basebackup: write-ahead log end point: 0/3A0001D8pg_basebackup: waiting for background process to finish streaming... pg_basebackup: basebackup completed [xdb@localhost testdb] $[xdb@localhost testdb] $ll / data/backup/0312-1/total 44384 Murray. 1 xdb xdb 45427619 Mar 12 17:30 base.tar.gz-rw-. 1 xdb xdb 18927 Mar 12 17:30 pg_ wal.tar.gz [XDB @ localhost testdb] $# [xdb@localhost ~] $psql-d testdbpsql Type "help" for help.testdb=# checkpoint;CHECKPOINTtestdb=#

Construct test data

Create a data table and insert data

Testdb=# create table tbl01 (id int,c1 char (20000), c2 char (200); CREATE TABLEtestdb=# insert into tbl01 select FLI f | | 'c2' from generate_series (100000) f insert 0 100000testdb=# select pg_current_wal_lsn (); pg_current_wal_lsn-0/3DD39618 (1 row) testdb=# create table tbl02 (id int,c1 char (200), c2 char (200)) CREATE TABLEtestdb=# insert into tbl02 select pg_current_wal_lsn 0/40A62F20 (1 row) testdb=# | | 'c2' from generate_series (1m 100000) f / bot insert 0 row ()

The current online log file is 0000000F0000000000000040

[xdb@localhost testdb] $ll $PGDATA/pg_waltotal 196632 Murray RWMI. 1 xdb xdb 42 Mar 12 17:10 00000008.Murray RWMI. 1 xdb xdb 85 Mar 12 17:10 0000000C.Murray RWMI. 1 xdb xdb 16777216 Mar 12 17:10 0000000E000000000039. 1 xdb xdb 129 Mar 12 17:10 0000000E.Murray RWMI. 1 xdb xdb 16777216 Mar 12 17:30 0000000F0000000000003A Murray. 1 xdb xdb 323 Mar 12 17:30 0000000F00000000003A.00000108.backupMurray. 1 xdb xdb 16777216 Mar 12 17:32 0000000F0000000000003B Murray. 1 xdb xdb 16777216 Mar 12 17:32 0000000F00000000003C Murray. 1 xdb xdb 16777216 Mar 12 17:32 0000000F00000000003D Murrwmuri. 1 xdb xdb 16777216 Mar 12 17:32 0000000F0000000000003E Murray. 1 xdb xdb 16777216 Mar 12 17:32 0000000F00000000003F Murray. 1 xdb xdb 16777216 Mar 12 17:32 0000000F000000000040Murray. 1 xdb xdb 16777216 Mar 12 17:10 0000000F00000000000041Murrwmuri. 1 xdb xdb 16777216 Mar 12 17:10 0000000F00000000000042Murrwmuri. 1 xdb xdb 16777216 Mar 12 17:10 0000000F000000000043Murray. 1 xdb xdb 16777216 Mar 12 17:30 0000000F000000000044Murray. 1 xdb xdb 173 Mar 12 17:11 0000000F.FunydrwxMurray. 2 xdb xdb 4096 Mar 12 17:32 archive_ status [XDB @ localhost testdb] $

Archive log file information

[xdb@localhost testdb] $ll / data/archivelog/20190312/total 245772 Murray RWMI. 1 xdb xdb 16777216 Mar 12 17:06 0000000E000000000032Murray. 1 xdb xdb 16777216 Mar 12 17:06 0000000E000000000033 Murray. 1 xdb xdb 323 Mar 12 17:06 0000000E0000000033.000028.backupMurray. 1 xdb xdb 16777216 Mar 12 17:07 0000000E000000000034Murray. 1 xdb xdb 16777216 Mar 12 17:07 0000000E000000000035 Murray. 1 xdb xdb 16777216 Mar 12 17:07 0000000E000000000036Murray. 1 xdb xdb 16777216 Mar 12 17:07 0000000E000000000037Murray. 1 xdb xdb 16777216 Mar 12 17:07 0000000E000000000038Murray. 1 xdb xdb 16777216 Mar 12 17:11 0000000E000000000039. 1 xdb xdb 16777216 Mar 12 17:30 0000000F000000000039Murray. 1 xdb xdb 16777216 Mar 12 17:30 0000000F0000000000003A Murray. 1 xdb xdb 323 Mar 12 17:30 0000000F00000000003A.00000108.backupMurray. 1 xdb xdb 16777216 Mar 12 17:32 0000000F0000000000003B Murray. 1 xdb xdb 16777216 Mar 12 17:32 0000000F00000000003C Murray. 1 xdb xdb 16777216 Mar 12 17:32 0000000F00000000003D Murrwmuri. 1 xdb xdb 16777216 Mar 12 17:32 0000000F0000000000003E Murray. 1 xdb xdb 16777216 Mar 12 17:32 0000000F00000000003F Murray. 1 xdb xdb 173 Mar 12 17:11 0000000F.history [xdb@localhost testdb] $

Simulated data file corruption

Delete data file directory, kill postgres process

[xdb@localhost testdb] $rm-rf. / base [xdb@localhost testdb] $psql-d testdbpsql: FATAL: database "testdb" does not existDETAIL: The database subdirectory "base/16384" is missing. [xdb@localhost testdb] $ps-ef | grep postgresxdb 2914 10 17:11 pts/2 00:00:00 / appdb/xdb/pg11.2/bin/postgresxdb 2915 2914 0 17:11? 00:00:00 postgres: logger xdb 2918 2914 0 17:11? 00:00:00 postgres: checkpointer xdb 2919 2914 0 17:11? 00:00:00 postgres: background writer xdb 2921 2914 0 17:11? 00:00:00 postgres: stats collector xdb 2925 2914 0 17:11? 00:00:00 postgres: walwriter xdb 2926 2914 0 17:11? 00:00:00 postgres: autovacuum launcher xdb 2927 2914 0 17:11? 00:00:00 postgres: archiver last was 0000000F000000000000003Fxdb 2928 2914 0 17:11? 00:00:00 postgres: logical replication launcher xdb 2977 2914 0 17:30? 00:00:00 postgres: xdb testdb [local] idlexdb 3014 2519 0 17:33 pts/2 00:00:00 grep-- color=auto postgres [xdb@localhost testdb] $kill-9 2914 [xdb@localhost testdb] $ps-ef | grep postgresxdb 3016 2519 0 17:34 pts/2 00:00:00 grep-color=auto postgres

Perform recovery

Back up the online log

[xdb@localhost] $mkdir / data/backup/wal [xdb@localhost testdb] $cp-R. / pg_wal/* / data/backup/wal/ [xdb@localhost testdb] $[xdb@localhost testdb] $ll / data/backup/wal/total 196632 Murray. 1 xdb xdb 42 Mar 12 17:34 00000008.Murray RWMI. 1 xdb xdb 85 Mar 12 17:34 0000000C.Murray RWMI. 1 xdb xdb 16777216 Mar 12 17:34 0000000E000000000039. 1 xdb xdb 129 Mar 12 17:34 0000000E.Murray RWMI. 1 xdb xdb 16777216 Mar 12 17:34 0000000F0000000000003A Murray. 1 xdb xdb 323 Mar 12 17:34 0000000F00000000003A.00000108.backupMurray. 1 xdb xdb 16777216 Mar 12 17:34 0000000F0000000000003B Murray. 1 xdb xdb 16777216 Mar 12 17:34 0000000F00000000003C Murray. 1 xdb xdb 16777216 Mar 12 17:34 0000000F00000000003D Murrwmuri. 1 xdb xdb 16777216 Mar 12 17:34 0000000F0000000000003E Murray. 1 xdb xdb 16777216 Mar 12 17:34 0000000F00000000003F Murray. 1 xdb xdb 16777216 Mar 12 17:34 0000000F000000000040Murray. 1 xdb xdb 16777216 Mar 12 17:34 0000000F00000000000041Murrwmuri. 1 xdb xdb 16777216 Mar 12 17:34 0000000F00000000000042Murrwmuri. 1 xdb xdb 16777216 Mar 12 17:34 0000000F000000000043Murray. 1 xdb xdb 16777216 Mar 12 17:34 0000000F000000000044Murray. 1 xdb xdb 173 Mar 12 17:34 0000000F.FunydrwxMurray. 2 xdb xdb 4096 Mar 12 17:34 archive_ status [XDB @ localhost testdb] $

Restore from a database backup

[xdb@localhost testdb] $rm-rf * [xdb@localhost testdb] $cp / data/backup/0312-1/base.tar.gz. / [xdb@localhost testdb] $tar zxf base.tar.gz

Restore online logs

Cp-R / data/backup/wal/0000000F0000000000000040. / pg_ Wal [XDB @ localhost testdb] $ll. / pg_waltotal 16384 Murray RWMI. 1 xdb xdb 16777216 Mar 12 17:35 0000000F00000000000040drwxMel. 2 xdb xdb 6 Mar 12 17:30 archive_status

Create a recovery.conf file

[xdb@localhost testdb] $vim recovery.conf [xdb@localhost testdb] $cat recovery.conf # Recoveryrestore_command='cp / data/archivelog/20190312/%f% p'#restore_target=XX

Perform a restore and start the database

[xdb@localhost testdb] $pg_ctl startwaiting for server to start....2019-03-12 17 listening on IPv4 address 36 CST 21.310 CST [3030] LOG: listening on IPv4 address "0.0.0.0", port 54322019-03-12 1715 14 14 31 CST [3030] LOG: listening on IPv6 address ":" Port 54322019-03-12 17 LOG: listening on Unix socket "/ tmp/.s.PGSQL.5432" 2019-03-12 17 LOG 21.341 CST [3030] LOG: redirecting log output to logging collector process2019-03-12 17 LOG 21.341 CST [3030] HINT: Future log output will appear in directory "pg_log". Doneserver started

Log output

2019-03-12 17 ending log output to stderr 36 Future log output will go to log destination 21.341 CST,3030,5c877d95.bd6,1,2019-03-12 17:36:21 CST,0,LOG,00000, "ending log output to stderr", "Future log output will go to log destination"csvlog". "," 2019-03-12 17 ending log output to stderr 36 ending log output to stderr-03-12 17:36:21 CST,0,LOG,00000, "database system was interrupted" Last known up at 2019-03-12 17:30:44 CST "," 2019-03-12 17 CST,0,LOG,00000 21.358 CST,3032,5c877d95.bd8,2,2019-03-12 17:36:21 CST,0,LOG,00000, "starting archive recovery", "2019-03-12 17 CST,3032,5c877d95.bd8,2,2019 21.372 CST,3032,5c877d95.bd8,3,2019-03-12 17:36:21 CST,0,LOG,00000" "restored log file"0000000F.history"from archive", "2019-03-12 17 from archive 36 CST,3032,5c877d95.bd8,4,2019 21.486 CST,3032,5c877d95.bd8,4,2019-03-12 17:36:21 CST,0,LOG,00000," restored log file "0000000F000000000000003A"from archive", "2019-03-12 17 17 from archive 36 CST,3032,5c877d95.bd8,4,2019-03-12 17:36:21 CST,1/0,0 LOG,00000, "redo starts at 0/3A000108", "2019-03-12 17 CST,1/0,0,LOG,00000 21.696 CST,3032,5c877d95.bd8,6,2019-03-12 17:36:21 CST,1/0,0,LOG,00000,"consistent recovery state reached at 0/3A0001D8", "2019-03-12 17 CST,3032,5c877d95.bd8,6,2019 36 CST,1/0,0,LOG,00000 21.696 CST,3030,5c877d95.bd6,2,2019-03-12 17:36:21 CST,0,LOG,00000 "database system is ready to accept read only connections", "2019-03-12 17 CST,1/0,0,LOG,00000 21.826 CST,3032,5c877d95.bd8,7,2019-03-12 17:36:21 CST,1/0,0,LOG,00000,"restored log file"0000000F000000000000003B"from archive", "2019-03-12 17 CST,1/0,0,LOG,00000 36 restored log file-03-12 17:36:21 CST,1/0,0,LOG 00000, "restored log file"0000000F000000000000003C"from archive", "2019-03-12 17 from archive 3622. 614 CST,3032,5c877d95.bd8,9,2019-03-12 17:36:21 CST,1/0,0,LOG,00000," restored log file "" 0000000F000000000000003D "" from archive "," 2019-03-12 17 from archive 3623.039 CST,3032,5c877d95.bd8,10,2019-03-12 17:36:21 CST,1/0,0 LOG,00000, "restored log file", "0000000F000000000000003E", "from archive", "2019-03-12 17 CST,3032,5c877d95.bd8,11,2019 23.342 CST,3032,5c877d95.bd8,11,2019-03-12 17:36:21 CST,1/0,0,LOG,00000," restored log file "0000000F000000000000003F"from archive", "2019-03-12 17 0000000F000000000000003E 3623.874 CST,3032,5c877d95.bd8,12,2019-03-12 17:36:21 CST Invalid record length at 0/40A63B08: wanted 24, got 0 "," 2019-03-12 17 got 36 CST,3032,5c877d95.bd8,13,2019 23.874 CST,3032,5c877d95.bd8,13,2019-03-12 17:36:21 CST,1/0,0,LOG,00000, "redo done at 0/40A63AD0", "2019-03-12 17 17 got 3623.874 2019-03-12 17:36:21 CST,1/0,0,LOG,00000, "last completed transaction was at log time 2019-03-12 17CST,1/0,0,LOG,00000 32.7604921408", "2019-03-12 17CST,3032,5c877d95.bd8,15,2019 3623.879 CST,3032,5c877d95.bd8,15,2019-03-12 17:36:21 CST,1/0,0,LOG,00000, selected new timeline ID: 16", "2019-03-12 17purl 3624.773 CST" 3032 CST,1/0,0,LOG,00000 5c877d95.bd8 CST,1/0,0,LOG,00000, "archive recovery complete", "2019-03-12 17 CST,3032,5c877d95.bd8,17,2019-03-12 17:36:21 CST,1/0,0,LOG,00000," restored log file "0000000F.history"from archive", "2019-03-12 1736 CST,1/0,0,LOG,00000 25.589 CST,3030" 5c877d95.bd6 CST,0,LOG,00000, "database system is ready to accept connections", ""

Validate data

[xdb@localhost testdb] $psql-d testdbpsql Type "help" for help.testdb=# testdb=# select count (*) from tbl01; count-100000 (1 row) testdb=# select count (*) from t02; count-100000 (1 row) testdb=#

Timeline history file. There is a history named after the current timeline in both the archive directory and the pg_wal directory. This file describes the history of the Cluster.

[xdb@localhost ~] $cat / data/archivelog/20190312/00000010.history7 0PGDATA/pg_wal/00000010.history 27000000 no recovery target specified8 0/2A0012E8 no recovery target specified12 032000000 no recovery target specified14 0/39A63BD0 no recovery target specified15 0/40A63B08 no recovery target specified [xdb@localhost ~] $cat $PGDATA/pg_wal/00000010.history 70 / 27000000 no recovery target specified8 0/2A0012E8 no recovery target specified12 0Let32000000 no recovery target specified14 0/39A63BD0 no recovery target specified15 0 / 40A63B08 no recovery target specified [xdb@localhost ~] $"how to recover from PostgreSQL data file corruption" ends here. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.