Recover droped column from PostgreSQL
背景
PostgreSQL中使用alter table tbl drop column 删除列的操作，实际上只是标记列为删除状态。并不是里面就回收该列占用的空间。
只有当对原来存在的行发生UPDATE时或新插入行的时候才会影响真实的列记录。
恢复测试一：
对于执行alter table tbl drop column后没有发生UPDATE的操作的表，恢复比较简单如下：
首先当然要记录下原来的表结构。
digoal=> \d tbl_user
Table "digoal.tbl_user"
Column | Type | Modifiers
-----------+-----------------------+-----------
id | integer | not null
firstname | character varying(32) |
lastname | character varying(32) |
corp | character varying(32) |
age | integer |
Indexes:
"tbl_user_pkey" PRIMARY KEY, btree (id)
"idx_user_age" btree (age)
digoal=> select count(*) from tbl_user;
count
-------
0
(1 row)
插入测试记录
digoal=> insert into tbl_user select generate_series(1,1000),'zhou','digoal','sky-mobi',27;
INSERT 0 1000
删除列
digoal=> alter table tbl_user drop column corp;
ALTER TABLE
准备修复列
digoal=> select oid from pg_class where relname='tbl_user';
oid
---------
2016624
(1 row)
查看得出被删除的列只是把attisdropped ,atttypid ,attname 的值改掉了。
digoal=> select * from pg_attribute where attrelid=2016624;
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyva
l | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount | attacl | attoptions
----------+------------------------------+----------+---------------+--------+--------+----------+-------------+-----------+--------
--+------------+----------+------------+-----------+--------------+------------+-------------+--------+-----------------------------
-----------
2016624 | tableoid | 26 | 0 | 4 | -7 | 0 | -1 | -1 | t
| p | i | t | f | f | t | 0 | |
2016624 | cmax | 29 | 0 | 4 | -6 | 0 | -1 | -1 | t
| p | i | t | f | f | t | 0 | |
2016624 | xmax | 28 | 0 | 4 | -5 | 0 | -1 | -1 | t
| p | i | t | f | f | t | 0 | |
2016624 | cmin | 29 | 0 | 4 | -4 | 0 | -1 | -1 | t
| p | i | t | f | f | t | 0 | |
2016624 | xmin | 28 | 0 | 4 | -3 | 0 | -1 | -1 | t
| p | i | t | f | f | t | 0 | |
2016624 | ctid | 27 | 0 | 6 | -1 | 0 | -1 | -1 | f
| p | s | t | f | f | t | 0 | |
2016624 | id | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t
| p | i | t | f | f | t | 0 | |
2016624 | firstname | 1043 | -1 | -1 | 2 | 0 | -1 | 36 | f
| x | i | f | f | f | t | 0 | | {n_distinct=2,n_distinct_inh
erited=10}
2016624 | lastname | 1043 | -1 | -1 | 3 | 0 | -1 | 36 | f
| x | i | f | f | f | t | 0 | |
2016624 | ........pg.dropped.4........ | 0 | 0 | -1 | 4 | 0 | -1 | 36 | f
| x | i | f | f | t | t | 0 | |
2016624 | age | 23 | -1 | 4 | 5 | 0 | -1 | -1 | t
| p | i | f | f | f | t | 0 | |
(11 rows)
digoal=# insert into digoal.tbl_user values (1001,'zhou','digoal',27);
INSERT 0 1
插入一条测试记录，并且执行以下SQL恢复删除列。
digoal=# update pg_attribute set attisdropped='f',atttypid=1043,attname='corp' where attrelid=2016624 and attnum=4;
UPDATE 1
查看列删除后插入的记录，CORP为空。
digoal=# select * from digoal.tbl_user where id=1001 ;
id | firstname | lastname | corp | age
------+-----------+----------+------+-----
1001 | zhou | digoal | | 27
(1 row)
查看列删除前插入的记录CORP已经恢复。
digoal=# select * from digoal.tbl_user where id=1000 ;
id | firstname | lastname | corp | age
------+-----------+----------+----------+-----
1000 | zhou | digoal | sky-mobi | 27
(1 row)
恢复测试二：
对于执行 alter table tbl drop column 后发生了UPDATE的表。
digoal=# alter table digoal.tbl_user drop column corp;
ALTER TABLE
digoal=# update digoal.tbl_user set firstname='abc' where id=1000;
UPDATE 1
digoal=# update pg_attribute set attisdropped='f',atttypid=1043,attname='corp' where attrelid=2016624 and attnum=4;
UPDATE 1
digoal=# select * from digoal.tbl_user where id=1000;
id | firstname | lastname | corp | age
------+-----------+----------+------+-----
1000 | abc | digoal | | 27
(1 row)
列删除后被UPDATE的行,CORP的记录被抹掉。