今天看了德哥的一篇日志，讲的是表上被 drop 的列还能恢复，很受启发，原文链接 http://blog.163.com/digoal@126/blog/static/163877040201112251058216/ ，根据德哥的BLOG，下面也来学习下。
PostgreSQL 在执行 Alter table table_name drop column 命令后，并没有在物理上删除这个列，而只是改下这个列的标志，可以通过修改 pg_attribute 属性进行恢复，下面是详细步骤：
先看 pg_attribute 几个关键的列
| Name | Type | References | Description |
|---|---|---|---|
| attrelid | oid | pg_class.oid | The table this column belongs to |
| attname | name | The column name | |
| atttypid | oid | pg_type.oid | The data type of this column |
| attnum | int2 | The number of the column. Ordinary columns are numbered from 1 up. System columns, such as oid, have (arbitrary) negative numbers | |
| attisdropped | bool | This column has been dropped and is no longer valid. A dropped column is still physically present in the table, but is ignored by the parser and so cannot be accessed via SQL |
测试场景一: 删除列后恢复
创建测试表并插入数据1
2
3
4 create table test_26 (id integer,name varchar(32),machine varchar(32));
CREATE TABLE
insert into test_26 select generate_series(1,10000),'francs','sky';
INSERT 0 10000
查看表结构1
2
3
4
5
6
7skytf=> \d test_26
Table "skytf.test_26"
Column | Type | Modifiers
---------+-----------------------+-----------
id | integer |
name | character varying(32) |
machine | character varying(32) |
查看一条测试数据1
2
3
4
5skytf=> select * from test_26 limit 1;
id | name | machine
----+--------+---------
1 | francs | sky
(1 row)
查看表信息1
2
3
4
5
6
7
8
9
10
11skytf=> select oid,relname from pg_class where relname='test_26';
oid | relname
----------+---------
14280890 | test_26
(1 row)
skytf=> select attrelid,attname,atttypid,attisdropped ,attnum from pg_attribute where attrelid=14280890 and attnum=2;
attrelid | attname | atttypid | attisdropped | attnum
----------+---------+----------+--------------+--------
14280890 | name | 1043 | f | 2
(1 row)
删除列1
2skytf=> alter table test_26 drop column name;
ALTER TABLE
再次查看 pg_attribute 列信息1
2
3
4
5skytf=> select attrelid,attname,atttypid,attisdropped ,attnum from pg_attribute where attrelid=14280890 and attnum=2;
attrelid | attname | atttypid | attisdropped | attnum
----------+------------------------------+----------+--------------+--------
14280890 | ........pg.dropped.2........ | 0 | t | 2
(1 row)
查看表结构1
2
3
4
5
6skytf=> \d test_26
Table "skytf.test_26"
Column | Type | Modifiers
---------+-----------------------+-----------
id | integer |
machine | character varying(32) |
恢复删除的列1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23skytf=> \c skytf postgres
You are now connected to database "skytf" as user "postgres".
skytf=# update pg_attribute set attname='name' ,atttypid=1043,attisdropped='f' where attrelid=14280890 and attnum=2;
UPDATE 1
skytf=# \d skytf.test_26;
Table "skytf.test_26"
Column | Type | Modifiers
---------+-----------------------+-----------
id | integer |
name | character varying(32) |
machine | character varying(32) |
skytf=# select * From skytf.test_26 limit 5;
id | name | machine
----+--------+---------
1 | francs | sky
2 | francs | sky
3 | francs | sky
4 | francs | sky
5 | francs | sky
(5 rows)
备注：通过修改系统表 pg_attribute 列 的 attname, atttypid, attisdropped 值, 列上数据可以恢复。
测试场景二: 删除列之后插入数据
查看表信息1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19skytf=> select max(id) from test_26;
max
-------
10000
(1 row)
skytf=> \d test_26;
Table "skytf.test_26"
Column | Type | Modifiers
---------+-----------------------+-----------
id | integer |
name | character varying(32) |
machine | character varying(32) |
skytf=> select * from test_26 limit 1;
id | name | machine
----+--------+---------
1 | francs | sky
(1 row)
删除列1
2skytf=> alter table test_26 drop column name;
ALTER TABLE
查看数据1
2
3
4
5skytf=> select * from test_26 limit 1;
id | machine
----+---------
1 | sky
(1 row)
插入一条数据1
2 insert into test_26(id,machine) values (10001,'sky10001');
INSERT 0 1
恢复删除的列1
2
3
4
5
6
7
8
9
10
11
12
13
14skytf=> \c skytf postgres
You are now connected to database "skytf" as user "postgres".
skytf=# update pg_attribute set attname='name' ,atttypid=1043,attisdropped='f' where attrelid=14280890 and attnum=2;
UPDATE 1
skytf=# \c skytf skytf
You are now connected to database "skytf" as user "skytf".
skytf=> select * from test_26 where id=10001;
id | name | machine
-------+------+----------
10001 | | sky10001
(1 row)
备注：对于删除的列恢复后，后面接着的 insert 记录的该列为空。测试场景三，删除列后，后面接着有数据 update 的场景
测试场景三: 删除列之后更新数据
查看表信息1
2
3
4
5
6
7
8
9
10
11
12
13skytf=> \d test_26
Table "skytf.test_26"
Column | Type | Modifiers
---------+-----------------------+-----------
id | integer |
name | character varying(32) |
machine | character varying(32) |
skytf=> select * From test_26 where id=1;
id | name | machine
----+--------+---------
1 | francs | sky
(1 row)
删除列1
2skytf=> alter table test_26 drop column name;
ALTER TABLE
修改一条记录1
2
3
4
5
6
7
8skytf=> update test_26 set machine ='sky_001' where id=1;
UPDATE 1
skytf=> select * from test_26 where id=1;
id | machine
----+---------
1 | sky_001
(1 row)
恢复删除的列1
2
3
4
5
6
7
8
9
10
11skytf=> \c skytf postgres
You are now connected to database "skytf" as user "postgres".
skytf=# update pg_attribute set attname='name' ,atttypid=1043,attisdropped='f' where attrelid=14280890 and attnum=2;
UPDATE 1
skytf=# select * from skytf.test_26 where id=1;
id | name | machine
----+------+---------
1 | | sky_001
(1 row)
备注: 在删除列后，对于后面有 update 操作的行，则在列恢复后，被update的行的此列数据丢失。
测试场景四: 删除列之后 VACUUM FULL 表
查看表信息1
2
3
4
5
6
7skytf=> \d test_26
Table "skytf.test_26"
Column | Type | Modifiers
---------+-----------------------+-----------
id | integer |
name | character varying(32) |
machine | character varying(32) |
查看表大小1
2
3
4
5 select pg_size_pretty(pg_relation_size('test_26'));
pg_size_pretty
----------------
440 kB
(1 row)
删除列1
2skytf=> alter table test_26 drop column name;
ALTER TABLE
再次查看表大小，表大小没变化1
2
3
4
5
6
7
8
9 select pg_size_pretty(pg_relation_size('test_26'));
pg_size_pretty
----------------
440 kB
(1 row)
vacuum full
vacuum full verbose test_26;
INFO: vacuuming "skytf.test_26"
VACUUM
再次查看表大小，发现变小了1
2
3
4
5 select pg_size_pretty(pg_relation_size('test_26'));
pg_size_pretty
----------------
360 kB
(1 row)
列恢复1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20skytf=> \c skytf postgres
You are now connected to database "skytf" as user "postgres".
skytf=# select attrelid,attname,atttypid,attisdropped ,attnum from pg_attribute where attrelid=14280890 and attnum=2;
attrelid | attname | atttypid | attisdropped | attnum
----------+------------------------------+----------+--------------+--------
14280890 | ........pg.dropped.2........ | 0 | t | 2
(1 row)
skytf=# update pg_attribute set attname='name' ,atttypid=1043,attisdropped='f' where attrelid=14280890 and attnum=2;
UPDATE 1
skytf=# select attrelid,attname,atttypid,attisdropped ,attnum from pg_attribute where attrelid=14280890 and attnum=2;
attrelid | attname | atttypid | attisdropped | attnum
----------+---------+----------+--------------+--------
14280890 | name | 1043 | f | 2
(1 row)
skytf=# \c skytf skytf
You are now connected to database "skytf" as user "skytf".
再次查看数据1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16skytf=> \d test_26;
Table "skytf.test_26"
Column | Type | Modifiers
---------+-----------------------+-----------
id | integer |
name | character varying(32) |
machine | character varying(32) |
skytf=> select * From test_26 limit 5;
id | name | machine
----+------+---------
2 | | sky
3 | | sky
4 | | sky
5 | | sky
6 | | sky
(5 rows)
备注，在列被删除后，如果后面执行了 vacuum full 操作，被删除的列名能恢复，但列的数据丢失。
测试场景五: 删除列之后 VACUUM 表
环境准备1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18skytf=> update test_26 set name='francs';
UPDATE 10001
skytf=> select * From test_26 limit 5;
id | name | machine
----+--------+---------
2 | francs | sky
3 | francs | sky
4 | francs | sky
5 | francs | sky
6 | francs | sky
(5 rows)
查看表大小
skytf=> select pg_size_pretty(pg_relation_size('test_26'));
pg_size_pretty
----------------
792 kB
(1 row)
删除列1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19skytf=> alter table test_26 drop column name;
ALTER TABLE
skytf=> select pg_size_pretty(pg_relation_size('test_26'));
pg_size_pretty
----------------
792 kB
(1 row)
VACUUM
skytf=> vacuum verbose test_26;
INFO: vacuuming "skytf.test_26"
INFO: "test_26": removed 0 row versions in 45 pages
INFO: "test_26": found 0 removable, 10001 nonremovable row versions in 99 out of 99 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
再次查看表大小1
2
3
4
5 select pg_size_pretty(pg_relation_size('test_26'));
pg_size_pretty
----------------
792 kB
(1 row)
恢复列1
2
3
4
5skytf=> \c skytf postgres
You are now connected to database "skytf" as user "postgres".
skytf=# update pg_attribute set attname='name' ,atttypid=1043,attisdropped='f' where attrelid=14280890 and attnum=2;
UPDATE 1
查看表数据1
2
3
4
5
6skytf=# select * from skytf.test_26 limit 2;
id | name | machine
----+--------+---------
2 | francs | sky
3 | francs | sky
(2 rows)
备注：删除列后，如果些表被 vacuum, 被 drop 的列依然能完全恢复。
总结
- 在删除列后，可以通过修改系统表pg_attribute 的 attname, atttypid, attisdropped值, 对删除的列进行恢复。
- 在删除列后，对于后面有 insert 操作的行，则在列恢复后，后面接着的 insert 记录的该列为空。
- 在删除列后，对于后面有 update 操作的行，则在列恢复后，后面接着的 update 的行的此列的数据为空。
- 在删除列后，如果后面执行了 vacuum full 操作，被删除的列名能恢复，但数据丢失。
- 在删除列后，如果此表被 vacuum, 被 drop 的列依然能完全恢复。
