PostgreSQL恢復表中被刪除的列

IT那活兒發布于2023-01-11 13:19 / 2704人閱讀

PostgreSQL恢復表中被刪除的列

點擊上方“IT那活兒”公眾號，關注后了解更多內容，不管IT什么活兒，干就完了！！！

前言

在PostgreSQL中很多實用的小技巧，有效的利用這技巧，有時候可以幫助我們在遇到意外的情況時，及時的恢復。本文借助PG MVCC實現機制，來介紹以下在PG中如果一個表的列被意外刪除了怎么進行快速恢復。

在PG中當一個表的列被刪除時，其實并沒有真正的刪除而是在系統表中將該列標記為刪除，在表不經過VACUUM FULL和UPDATE操作的情況下，通過修該pg_attribute表的attname、atttypid、attisdropped列的值是可以對被刪除的列的值進行恢復。

attname：表示表中列的名字。
atttypid：表示表中列的字段類型。
attisdropped：表示表中的列是否被刪除；
f表示未被刪除；
t表示被刪除。

測試一：刪除表列

user:postgres@db:postgres[[local]:5432]#create table test(id int,name varchar(32),sex char(1));
CREATE TABLE
Time: 12.366 ms
user:postgres@db:postgres[[local]:5432]#insert into test values(1,Jack,F);
INSERT 0 1
Time: 1.577 ms
user:postgres@db:postgres[[local]:5432]#select * from test;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | Jack | F |
+----+------+-----+
(1 row)

Time: 0.432 ms
user:postgres@db:postgres[[local]:5432]#select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attrelid=test::regclass and attnum >0;
+----------+---------+----------+--------+--------------+
| attrelid | attname | atttypid | attnum | attisdropped |
+----------+---------+----------+--------+--------------+
| 90543 | id | 23 | 1 | f |
| 90543 | name | 1043 | 2 | f |
| 90543 | sex | 1042 | 3 | f |
+----------+---------+----------+--------+--------------+
(3 rows)
Time: 0.948 ms
user:postgres@db:postgres[[local]:5432]#alter table test drop column sex;
ALTER TABLE
Time: 1.450 ms
user:postgres@db:postgres[[local]:5432]#select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attrelid=test::regclass and attnum >0;
+----------+------------------------------+----------+--------+--------------+
| attrelid | attname | atttypid | attnum | attisdropped |
+----------+------------------------------+----------+--------+--------------+
| 90543 | id | 23 | 1 | f |
| 90543 | name | 1043 | 2 | f |
| 90543 | ........pg.dropped.3........ | 0 | 3 | t |
+----------+------------------------------+----------+--------+--------------+
(3 rows)

Time: 0.420 ms
user:postgres@db:postgres[[local]:5432]#set allow_system_table_mods to on;
SET
Time: 0.330 ms
user:postgres@db:postgres[[local]:5432]#update pg_attribute set attname=sex,atttypid=1042,attisdropped=f where attrelid=test::regclass and attnum=3;
UPDATE 1
Time: 1.465 ms
user:postgres@db:postgres[[local]:5432]#select * from test;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | Jack | F |
+----+------+-----+
(1 row)

Time: 0.628 ms
user:postgres@db:postgres[[local]:5432]#

結論：數據可以正常恢復。

測試二：表列刪除后，執行VACUUM FULL操作

user:postgres@db:postgres[[local]:5432]#select * from test;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | 1 | M |
| 2 | 2 | F |
| 3 | 3 | M |
| 4 | 4 | F |
| 5 | 5 | M |
| 6 | 6 | F |
| 7 | 7 | M |
| 8 | 8 | F |
| 9 | 9 | M |
| 10 | 10 | F |
+----+------+-----+
(10 rows)

Time: 0.356 ms
user:postgres@db:postgres[[local]:5432]#alter table test drop column sex;
ALTER TABLE
Time: 1.365 ms
user:postgres@db:postgres[[local]:5432]#vacuum full test;
VACUUM
Time: 25.565 ms
user:postgres@db:postgres[[local]:5432]#select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attrelid=test::regclass and attnum >0;
+----------+------------------------------+----------+--------+--------------+
| attrelid | attname | atttypid | attnum | attisdropped |
+----------+------------------------------+----------+--------+--------------+
| 90543 | id | 23 | 1 | f |
| 90543 | name | 1043 | 2 | f |
| 90543 | ........pg.dropped.3........ | 0 | 3 | t |
+----------+------------------------------+----------+--------+--------------+
(3 rows)

Time: 0.425 ms
user:postgres@db:postgres[[local]:5432]#update pg_attribute set attname=sex,atttypid=1042,attisdropped=f where attrelid=test::regclass and attnum=3;
UPDATE 1
Time: 1.496 ms
user:postgres@db:postgres[[local]:5432]#select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attrelid=test::regclass and attnum >0;
+----------+---------+----------+--------+--------------+
| attrelid | attname | atttypid | attnum | attisdropped |
+----------+---------+----------+--------+--------------+
| 90543 | id | 23 | 1 | f |
| 90543 | name | 1043 | 2 | f |
| 90543 | sex | 1042 | 3 | f |
+----------+---------+----------+--------+--------------+
(3 rows)

Time: 0.500 ms
user:postgres@db:postgres[[local]:5432]#select * from test;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | 1 | NULL |
| 2 | 2 | NULL |
| 3 | 3 | NULL |
| 4 | 4 | NULL |
| 5 | 5 | NULL |
| 6 | 6 | NULL |
| 7 | 7 | NULL |
| 8 | 8 | NULL |
| 9 | 9 | NULL |
| 10 | 10 | NULL |
+----+------+------+
(10 rows)

結論：數據不能恢復。

測試三：表列被刪除后，執行VACUUM操作

user:postgres@db:postgres[[local]:5432]#select * from test;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | 1 | M |
| 2 | 2 | F |
| 3 | 3 | M |
| 4 | 4 | F |
| 5 | 5 | M |
| 6 | 6 | F |
| 7 | 7 | M |
| 8 | 8 | F |
| 9 | 9 | M |
| 10 | 10 | F |
+----+------+-----+
(10 rows)

Time: 0.466 ms
user:postgres@db:postgres[[local]:5432]#
user:postgres@db:postgres[[local]:5432]#
user:postgres@db:postgres[[local]:5432]#
user:postgres@db:postgres[[local]:5432]#alter table test drop column sex;
ALTER TABLE
Time: 1.698 ms
user:postgres@db:postgres[[local]:5432]#vacuum test;
VACUUM
Time: 15.082 ms
user:postgres@db:postgres[[local]:5432]#select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attrelid=test::regclass and attnum >0;
+----------+------------------------------+----------+--------+--------------+
| attrelid | attname | atttypid | attnum | attisdropped |
+----------+------------------------------+----------+--------+--------------+
| 90543 | id | 23 | 1 | f |
| 90543 | name | 1043 | 2 | f |
| 90543 | ........pg.dropped.3........ | 0 | 3 | t |
+----------+------------------------------+----------+--------+--------------+
(3 rows)

Time: 0.463 ms
user:postgres@db:postgres[[local]:5432]#update pg_attribute set attname=sex,atttypid=1042,attisdropped=f where attrelid=test::regclass and attnum=3;
UPDATE 1
Time: 1.094 ms
user:postgres@db:postgres[[local]:5432]#select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attrelid=test::regclass and attnum >0;
+----------+---------+----------+--------+--------------+
| attrelid | attname | atttypid | attnum | attisdropped |
+----------+---------+----------+--------+--------------+
| 90543 | id | 23 | 1 | f |
| 90543 | name | 1043 | 2 | f |
| 90543 | sex | 1042 | 3 | f |
+----------+---------+----------+--------+--------------+
(3 rows)

Time: 0.599 ms
user:postgres@db:postgres[[local]:5432]#select * from test;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | 1 | M |
| 2 | 2 | F |
| 3 | 3 | M |
| 4 | 4 | F |
| 5 | 5 | M |
| 6 | 6 | F |
| 7 | 7 | M |
| 8 | 8 | F |
| 9 | 9 | M |
| 10 | 10 | F |
+----+------+-----+
(10 rows)

Time: 0.802 ms

結論：數據可以正常恢復。

測試四：刪除列后，執行INSERT

user:postgres@db:postgres[[local]:5432]#select * from test;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | 1 | M |
| 2 | 2 | F |
| 3 | 3 | M |
| 4 | 4 | F |
| 5 | 5 | M |
| 6 | 6 | F |
| 7 | 7 | M |
| 8 | 8 | F |
| 9 | 9 | M |
| 10 | 10 | F |
+----+------+-----+
(10 rows)

Time: 0.396 ms
user:postgres@db:postgres[[local]:5432]#alter table test drop column sex;
ALTER TABLE
Time: 1.541 ms
user:postgres@db:postgres[[local]:5432]#insert into test values(11,11);
INSERT 0 1
Time: 1.380 ms
user:postgres@db:postgres[[local]:5432]#select * from test;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
| 11 | 11 |
+----+------+
(11 rows)

Time: 0.417 ms
user:postgres@db:postgres[[local]:5432]#select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attrelid=test::regclass and attnum >0;
+----------+------------------------------+----------+--------+--------------+
| attrelid | attname | atttypid | attnum | attisdropped |
+----------+------------------------------+----------+--------+--------------+
| 90543 | id | 23 | 1 | f |
| 90543 | name | 1043 | 2 | f |
| 90543 | ........pg.dropped.3........ | 0 | 3 | t |
+----------+------------------------------+----------+--------+--------------+
(3 rows)

Time: 0.427 ms
user:postgres@db:postgres[[local]:5432]#update pg_attribute set attname=sex,atttypid=1042,attisdropped=f where attrelid=test::regclass and attnum=3;
UPDATE 1
Time: 1.720 ms
user:postgres@db:postgres[[local]:5432]#select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attrelid=test::regclass and attnum >0;
+----------+---------+----------+--------+--------------+
| attrelid | attname | atttypid | attnum | attisdropped |
+----------+---------+----------+--------+--------------+
| 90543 | id | 23 | 1 | f |
| 90543 | name | 1043 | 2 | f |
| 90543 | sex | 1042 | 3 | f |
+----------+---------+----------+--------+--------------+
(3 rows)

Time: 0.474 ms
user:postgres@db:postgres[[local]:5432]#select * from test;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | 1 | M |
| 2 | 2 | F |
| 3 | 3 | M |
| 4 | 4 | F |
| 5 | 5 | M |
| 6 | 6 | F |
| 7 | 7 | M |
| 8 | 8 | F |
| 9 | 9 | M |
| 10 | 10 | F |
| 11 | 11 | NULL |
+----+------+------+
(11 rows)

結論：表在刪除列后新增的列，在刪除的列恢復后，新增列無值，原始行該列的值被恢復。

測試五：刪除列后，執行UPDATE

user:postgres@db:postgres[[local]:5432]#select * from test;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | 1 | M |
| 2 | 2 | F |
| 3 | 3 | M |
| 4 | 4 | F |
| 5 | 5 | M |
| 6 | 6 | F |
| 7 | 7 | M |
| 8 | 8 | F |
| 9 | 9 | M |
| 10 | 10 | F |
| 11 | 11 | NULL |
+----+------+------+
(11 rows)

Time: 0.312 ms
user:postgres@db:postgres[[local]:5432]#alter table test drop column sex;
ALTER TABLE
Time: 1.442 ms
user:postgres@db:postgres[[local]:5432]#select * from test;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
| 11 | 11 |
+----+------+
(11 rows)

Time: 0.406 ms
user:postgres@db:postgres[[local]:5432]#update test set name=XXXKXKX where id % 3=0;
UPDATE 3
Time: 1.481 ms
user:postgres@db:postgres[[local]:5432]#select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | 1 |
| 2 | 2 |
| 4 | 4 |
| 5 | 5 |
| 7 | 7 |
| 8 | 8 |
| 10 | 10 |
| 11 | 11 |
| 3 | XXXKXKX |
| 6 | XXXKXKX |
| 9 | XXXKXKX |
+----+---------+
(11 rows)

Time: 0.306 ms
user:postgres@db:postgres[[local]:5432]#select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attrelid=test::regclass and attnum >0;
+----------+------------------------------+----------+--------+--------------+
| attrelid | attname | atttypid | attnum | attisdropped |
+----------+------------------------------+----------+--------+--------------+
| 90543 | id | 23 | 1 | f |
| 90543 | name | 1043 | 2 | f |
| 90543 | ........pg.dropped.3........ | 0 | 3 | t |
+----------+------------------------------+----------+--------+--------------+
(3 rows)

Time: 0.400 ms
user:postgres@db:postgres[[local]:5432]#update pg_attribute set attname=sex,atttypid=1042,attisdropped=f where attrelid=test::regclass and attnum=3;
UPDATE 1
Time: 1.414 ms
user:postgres@db:postgres[[local]:5432]#select attrelid,attname,atttypid,attnum,attisdropped from pg_attribute where attrelid=test::regclass and attnum >0;
+----------+---------+----------+--------+--------------+
| attrelid | attname | atttypid | attnum | attisdropped |
+----------+---------+----------+--------+--------------+
| 90543 | id | 23 | 1 | f |
| 90543 | name | 1043 | 2 | f |
| 90543 | sex | 1042 | 3 | f |
+----------+---------+----------+--------+--------------+
(3 rows)

Time: 0.848 ms
user:postgres@db:postgres[[local]:5432]#select * from test;
+----+---------+------+
| id | name | sex |
+----+---------+------+
| 1 | 1 | M |
| 2 | 2 | F |
| 4 | 4 | F |
| 5 | 5 | M |
| 7 | 7 | M |
| 8 | 8 | F |
| 10 | 10 | F |
| 11 | 11 | NULL |
| 3 | XXXKXKX | NULL |
| 6 | XXXKXKX | NULL |
| 9 | XXXKXKX | NULL |
+----+---------+------+
(11 rows)

結論：表在刪除列后UPDATE，在刪除的列恢復后，被UPDATE的行的列的值無法恢復。

本文作者：魏強（上海新炬王翦團隊）

本文來源：“IT那活兒”公眾號

GPU云服務器云服務器 postgresql恢復數據庫 postgresql數據庫恢復免費postgresql恢復數據庫刪除恢復

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129246.html

想熟悉PostgreSQL？這篇就夠了

摘要：它在其他開放源代碼數據庫系統和專有系統之外，為用戶又提供了一種選擇。將插入空間以填補任何額外的空間。始終被視為唯一值上述兩個約束的組合。表范圍的約束可以是，，或。如何在中創建表我們將創建一個名為的表，它定義了各種游樂場設備。歡迎大家前往騰訊云+社區，獲取更多騰訊海量技術實踐干貨哦~ 本文由angel_郁發表于云+社區專欄什么是PostgreSQL？ PostgreSQL是自由...

DTeam 2019-07-25 12:36 評論0 收藏0
Postgresql 備份與恢復

摘要：指定要用于查找的口令文件的名稱。前四個字段可以是確定的字面值，也可以使用通配符匹配所有。利用環境變量引用的文件權限也要滿足這個要求，否則同樣會被忽略。在上，該文件被假定存儲在一個安全的目錄中，因此不會進行特別的權限檢查。 pg_dump pg_dump 把一個數據庫轉儲為純文本文件或者是其它格式. 用法: pg_dump [選項]... [數據庫名字] 一般選項: -f, --fi...

阿羅 2019-07-25 12:34 評論0 收藏0
【Flink實時計算 UFlink】UFlink SQL 開發指南

摘要：開發指南是為簡化計算模型，降低用戶使用實時計算的門檻而設計的一套符合標準語義的開發套件。隨后，將為該表生成字段，用于記錄并表示事件時間。UFlink SQL 開發指南UFlink SQL 是 UCloud 為簡化計算模型，降低用戶使用實時計算的門檻而設計的一套符合標準 SQL 語義的開發套件。接下來，開發者可以根據如下內容，逐漸熟悉并使用 UFlink SQL 組件所提供的便捷功能。1 ...

Tecode 2022-06-28 19:00 評論0 收藏0

發表評論

登陸后可評論

0條評論

IT那活兒

男|高級講師

我要關注我要私信

TA的文章

消息中間件故障分析一例

閱讀 1346·2023-01-11 13:20
RAC雙節點crash回復一例

閱讀 1684·2023-01-11 13:20
ORA-600處理一例

閱讀 1132·2023-01-11 13:20
雙節點RAC實例2 HANG 故障分析一例

閱讀 1860·2023-01-11 13:20
RAC集群節點1重啟分析一例

閱讀 4100·2023-01-11 13:20
CRS啟動報錯CRS-1656處理分享

閱讀 2704·2023-01-11 13:20
oracle 12CR2打補丁報錯處理一例

閱讀 1385·2023-01-11 13:20
分布式緩存組件故障分析及監控優化

閱讀 3597·2023-01-11 13:20

国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！

PostgreSQL恢復表中被刪除的列

前言

測試一：刪除表列

測試二：表列刪除后，執行VACUUM FULL操作

測試三：表列被刪除后，執行VACUUM操作

測試四：刪除列后，執行INSERT

測試五：刪除列后，執行UPDATE

本文作者：魏強（上海新炬王翦團隊）

本文來源：“IT那活兒”公眾號

相關文章

**想熟悉PostgreSQL？這篇就夠了**

Postgresql 備份與恢復

【Flink實時計算 UFlink】UFlink SQL 開發指南

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

消息中間件故障分析一例

RAC雙節點crash回復一例

ORA-600處理一例

雙節點RAC實例2 HANG 故障分析一例

RAC集群節點1重啟分析一例

CRS啟動報錯CRS-1656處理分享

oracle 12CR2打補丁報錯處理一例

分布式緩存組件故障分析及監控優化

最新活動

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！

PostgreSQL恢復表中被刪除的列

前 言

測試一：刪除表列

測試二：表列刪除后，執行VACUUM FULL操作

測試三：表列被刪除后，執行VACUUM操作

測試四：刪除列后，執行INSERT

測試五：刪除列后，執行UPDATE

本文作者：魏 強（上海新炬王翦團隊）

本文來源：“IT那活兒”公眾號

相關文章

發表評論

0條評論

男|高級講師

TA的文章

最新活動

前言

本文作者：魏強（上海新炬王翦團隊）