欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

PostgreSQL數(shù)據(jù)DML誤操作恢復(fù)方法

 更新時間：2024年12月10日 10:28:04   作者：施嘉偉  
PostgreSQL是一種開源的對象關(guān)系型數(shù)據(jù)庫管理系統(tǒng),其DML（Data Manipulation Language）負(fù)責(zé)數(shù)據(jù)的操作和管理,那么如何DML誤操作了如何恢復(fù),所以本文介紹了PostgreSQL數(shù)據(jù)DML誤操作恢復(fù)方法,需要的朋友可以參考下

第一部分 文檔描述

本文檔適用數(shù)據(jù)表數(shù)據(jù)被DML類型的語句誤操作情況下的重置恢復(fù)，需要滿足數(shù)據(jù)庫或數(shù)據(jù)表未被vacuum或者vacuum full

第二部分 操作步驟

2.1 創(chuàng)建測試表

創(chuàng)建測試表novels，

dbtest=# create table novels (name varchar(200), id int);
CREATE TABLE
dbtest=# insert into novels select md5(random()::text),generate_series(1,10);
INSERT 0 10

2.2 安裝pageinspect擴展

安裝pageinspect擴展獲取元組記錄信息

dbtest=# create extension pageinspect ;
CREATE EXTENSION

2.3 查詢表的事務(wù)操作記錄

使用擴展pageinspect中的函數(shù)查詢當(dāng)前表的事務(wù)操作記錄

dbtest=# select * from heap_page_items(get_raw_page('novels','main', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |     
                                  t_data                                       
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+-----
-------------------------------------------------------------------------------
  1 |   8128 |        1 |     64 |    651 |      0 |        0 | (0,1)  |           2 |       2050 |     24 |        |       | \x43
613731663139343239303330323262313732613039383366633066396562663600000001000000
  2 |   8064 |        1 |     64 |    651 |      0 |        0 | (0,2)  |           2 |       2050 |     24 |        |       | \x43
386633363838333134386434323539306265613033636434386536393232383400000002000000
  3 |   8000 |        1 |     64 |    651 |      0 |        0 | (0,3)  |           2 |       2050 |     24 |        |       | \x43
363535323364663966616531663935666538383632646239383166633962623600000003000000
  4 |   7936 |        1 |     64 |    651 |      0 |        0 | (0,4)  |           2 |       2050 |     24 |        |       | \x43
333763383035386564303434316133366438656133333764613837626362616600000004000000
  5 |   7872 |        1 |     64 |    651 |      0 |        0 | (0,5)  |           2 |       2050 |     24 |        |       | \x43
663833356234353939623566336661336662376630323234363133663139663800000005000000
  6 |   7808 |        1 |     64 |    651 |      0 |        0 | (0,6)  |           2 |       2050 |     24 |        |       | \x43
626336623539373031316565396437333236363965313937323265373736333100000006000000
  7 |   7744 |        1 |     64 |    651 |      0 |        0 | (0,7)  |           2 |       2050 |     24 |        |       | \x43
646633323365396535356166376538386632376139666431643739303736356600000007000000
  8 |   7680 |        1 |     64 |    651 |      0 |        0 | (0,8)  |           2 |       2050 |     24 |        |       | \x43
393865616338366430323137363466626462616539333831636436646137333200000008000000
  9 |   7616 |        1 |     64 |    651 |      0 |        0 | (0,9)  |           2 |       2050 |     24 |        |       | \x43
333766383764653134633235666664643563663832313836643064326138653600000009000000
 10 |   7552 |        1 |     64 |    651 |      0 |        0 | (0,10) |           2 |       2050 |     24 |        |       | \x43
32333636613934363930393830316562376564366537376630366231626362610000000a000000
(10 rows)

2.4 模擬刪除表數(shù)據(jù)

刪除id為5的數(shù)據(jù)，再次查看該表的事務(wù)操作記錄，可以看到標(biāo)紅處id為5的記錄其刪除事務(wù)號為652

dbtest=# delete from novels where id = 5;
DELETE 1
dbtest=# select * from heap_page_items(get_raw_page('novels','main', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |     
                                  t_data                                       
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+-----
-------------------------------------------------------------------------------
  1 |   8128 |        1 |     64 |    651 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | \x43
613731663139343239303330323262313732613039383366633066396562663600000001000000
  2 |   8064 |        1 |     64 |    651 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x43
386633363838333134386434323539306265613033636434386536393232383400000002000000
  3 |   8000 |        1 |     64 |    651 |      0 |        0 | (0,3)  |           2 |       2306 |     24 |        |       | \x43
363535323364663966616531663935666538383632646239383166633962623600000003000000
  4 |   7936 |        1 |     64 |    651 |      0 |        0 | (0,4)  |           2 |       2306 |     24 |        |       | \x43
333763383035386564303434316133366438656133333764613837626362616600000004000000
  5 |   7872 |        1 |     64 |    651 |    652 |        0 | (0,5)  |        8194 |        258 |     24 |        |       | \x43
663833356234353939623566336661336662376630323234363133663139663800000005000000
  6 |   7808 |        1 |     64 |    651 |      0 |        0 | (0,6)  |           2 |       2306 |     24 |        |       | \x43
626336623539373031316565396437333236363965313937323265373736333100000006000000
  7 |   7744 |        1 |     64 |    651 |      0 |        0 | (0,7)  |           2 |       2306 |     24 |        |       | \x43
646633323365396535356166376538386632376139666431643739303736356600000007000000
  8 |   7680 |        1 |     64 |    651 |      0 |        0 | (0,8)  |           2 |       2306 |     24 |        |       | \x43
393865616338366430323137363466626462616539333831636436646137333200000008000000
  9 |   7616 |        1 |     64 |    651 |      0 |        0 | (0,9)  |           2 |       2306 |     24 |        |       | \x43
333766383764653134633235666664643563663832313836643064326138653600000009000000
 10 |   7552 |        1 |     64 |    651 |      0 |        0 | (0,10) |           2 |       2306 |     24 |        |       | \x43
32333636613934363930393830316562376564366537376630366231626362610000000a000000
(10 rows)

2.5 關(guān)閉表級別的autovacuum

解析被刪除的數(shù)據(jù)之前首先查詢表在刪除時間點之后是否被vacuum（包括手動vacuum和autovacuum）

dbtest=# \x
Expanded display is on.
dbtest=# select * from pg_stat_all_tables where relname = 'novels';
-[ RECORD 1 ]-------+------------------------------
relid               | 24783
schemaname          | public
relname             | novels
seq_scan            | 13
seq_tup_read        | 44
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 28
n_tup_upd           | 0
n_tup_del           | 22
n_tup_hot_upd       | 0
n_live_tup          | 6
n_dead_tup          | 18
n_mod_since_analyze | 50
n_ins_since_vacuum  | 24
last_vacuum         | 2022-02-09 11:27:30.501748+08
last_autovacuum     | 
last_analyze        | 
last_autoanalyze    | 
vacuum_count        | 1
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

如果沒有被vacuum，則關(guān)閉表級別的autovacuum并開始解析步驟

dbtest=# alter table novels set (autovacuum_enabled = off);

2.6 關(guān)閉數(shù)據(jù)庫重置事務(wù)id

關(guān)閉數(shù)據(jù)庫服務(wù)，使用pg_resetwal工具重置事務(wù)id，使得下一個事務(wù)id從652開始

[postgres@VM-4-13-centos ~]$ pg_ctl stop
[postgres@VM-4-13-centos data]$ pg_resetwal -x 652 -D $PGDATA
Write-ahead log reset

2.7 啟動數(shù)據(jù)庫

啟動數(shù)據(jù)庫，查看數(shù)據(jù)是否找回，并重建表導(dǎo)出導(dǎo)入數(shù)據(jù)

[postgres@VM-4-13-centos ~]$ pg_ctl start
waiting for server to start....2022-02-09 14:56:51.938 CST [27058] LOG:  redirecting log output to logging collector process
2022-02-09 14:56:51.938 CST [27058] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@VM-4-13-centos ~]$ psql -ddbtest
psql (13.4)
Type "help" for help.

dbtest=# 
dbtest=# select xmin,xmax,id from novels ;
 xmin | xmax | id 
------+------+----
  651 |    0 |  1
  651 |    0 |  2
  651 |    0 |  3
  651 |    0 |  4
  651 |  652 |  5
  651 |    0 |  6
  651 |    0 |  7
  651 |    0 |  8
  651 |    0 |  9
  651 |    0 | 10
(10 rows)

可以使用pg_dump/pg_restore重建表

[postgres@VM-4-13-centos ~]$ pg_dump -Fc -U postgres -t novels -f novels.sql -d dbtest

導(dǎo)出表數(shù)據(jù)后重命名原表

dbtest=# alter table novels rename to novelsbak;

pg_restore恢復(fù)數(shù)據(jù)

[postgres@VM-4-13-centos ~]$ pg_restore -d dbtest novels.sql -c --if-exists

最后，驗證數(shù)據(jù)完整性

dbtest=# select * from novels ;
               name               | id 
----------------------------------+----
 a63e2f63c5dbec9065a788f6e774b9ce |  1
 092465fb1c7b58adea3b35b4a5de5fd9 |  2
 1559907b48d5e8efaeddbdace55efad2 |  3
 3f47e858647d8ef838fbaa7e87b3bc07 |  4
 430f91a034857bf996f51cbe1dc6bef3 |  5
 56d65c982438ed56724e3dae4c5e7933 |  6
 4dbecc8fb3d3a8acca631ed359685011 |  7
 98023e6140984d125cc19ee4ba33608f |  8
 f2570c845be93fdf36e9d7c3f4d34ccf |  9
 7d9d3a65770df1573ec58c349d3216f0 | 10
(10 rows)

到此這篇關(guān)于PostgreSQL數(shù)據(jù)DML誤操作恢復(fù)方法的文章就介紹到這了,更多相關(guān)PostgreSQL DML誤操作恢復(fù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評論