成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

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

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

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

前  言

在PostgreSQL中很多實(shí)用的小技巧，有效的利用這技巧，有時(shí)候可以幫助我們?cè)谟龅揭馔獾那闆r時(shí)，及時(shí)的恢復(fù)。本文借助PG MVCC實(shí)現(xiàn)機(jī)制，來(lái)介紹以下在PG中如果一個(gè)表的列被意外刪除了怎么進(jìn)行快速恢復(fù)。

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

  • attname：表示表中列的名字。

  • atttypid：表示表中列的字段類型。

  • attisdropped：表示表中的列是否被刪除；

    f表示未被刪除；

    t表示被刪除。

測(cè)試一：刪除表列

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ù)。

測(cè)試二：表列刪除后，執(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ù)。

測(cè)試三：表列被刪除后，執(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ù)。

測(cè)試四：刪除列后，執(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ù)后，新增列無(wú)值，原始行該列的值被恢復(fù)。

測(cè)試五：刪除列后，執(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的行的列的值無(wú)法恢復(fù)。

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

本文來(lái)源：“IT那活兒”公眾號(hào)

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

轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/129246.html

相關(guān)文章

  • 想熟悉PostgreSQL？這篇就夠了

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

    DTeam 評(píng)論0 收藏0

  • Postgresql 備份與恢復(fù)

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

    阿羅 評(píng)論0 收藏0

  • 【Flink實(shí)時(shí)計(jì)算 UFlink】UFlink SQL 開(kāi)發(fā)指南

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

    Tecode 評(píng)論0 收藏0

發(fā)表評(píng)論

0條評(píng)論

最新活動(dòng)
閱讀需要支付1元查看
<