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

資訊專欄INFORMATION COLUMN

PostgreSQL恢復(fù)表中被刪除的列

IT那活兒 / 2707人閱讀
PostgreSQL恢復(fù)表中被刪除的列

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

前  言

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

在PG中當(dāng)一個表的列被刪除時，其實并沒有真正的刪除而是在系統(tǒng)表中將該列標(biāo)記為刪除，在表不經(jīng)過VACUUM FULL和UPDATE操作的情況下，通過修該pg_attribute表的attname、atttypid、attisdropped列的值是可以對被刪除的列的值進(jìn)行恢復(fù)。

  • 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]#

結(jié)論：數(shù)據(jù)可以正常恢復(fù)。

測試二：表列刪除后，執(zhí)行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)

結(jié)論：數(shù)據(jù)不能恢復(fù)。

測試三：表列被刪除后，執(zhí)行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

結(jié)論：數(shù)據(jù)可以正常恢復(fù)。

測試四：刪除列后，執(zhí)行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)

結(jié)論：表在刪除列后新增的列，在刪除的列恢復(fù)后，新增列無值，原始行該列的值被恢復(fù)。

測試五：刪除列后，執(zhí)行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)
結(jié)論：表在刪除列后UPDATE，在刪除的列恢復(fù)后，被UPDATE的行的列的值無法恢復(fù)。

本文作者：魏 強（上海新炬王翦團(tuán)隊）

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

文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址：http://m.specialneedsforspecialkids.com/yun/129246.html

相關(guān)文章

  • 想熟悉PostgreSQL？這篇就夠了

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

    DTeam 評論0 收藏0

  • Postgresql 備份與恢復(fù)

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

    阿羅 評論0 收藏0

  • 【Flink實時計算 UFlink】UFlink SQL 開發(fā)指南

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

    Tecode 評論0 收藏0

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<