PostgreSQL恢复表中被删除的列

IT那活儿发布于2023-01-11 13:19 / 2705人阅读

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恢复数据库删除恢复

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/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的文章

消息中间件故障分析一例

阅读 1347·2023-01-11 13:20
RAC双节点crash回复一例

阅读 1685·2023-01-11 13:20
ORA-600处理一例

阅读 1133·2023-01-11 13:20
双节点RAC实例2 HANG 故障分析一例

阅读 1860·2023-01-11 13:20
RAC集群节点1重启分析一例

阅读 4101·2023-01-11 13:20
CRS启动报错CRS-1656处理分享

阅读 2705·2023-01-11 13:20
oracle 12CR2打补丁报错处理一例

阅读 1386·2023-01-11 13:20
分布式缓存组件故障分析及监控优化

阅读 3599·2023-01-11 13:20

资讯专栏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的文章

最新活动

前言

本文作者：魏强（上海新炬王翦团队）