注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

Postgresql: 恢复删除的列  

2011-03-21 17:58:51|  分类: PG备份与恢复 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |


         今天看了德哥的一篇日志,讲的是表上被 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 
   

测试场景一:  删除列后恢复
--创建测试表并插入数据
skytf=> create table test_26 (id integer,name varchar(32),machine varchar(32));
CREATE TABLE

skytf=> insert into test_26 select generate_series(1,10000),'francs','sky';
INSERT 0 10000

--查看表结构
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)
 
--查看表信息
skytf=> 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)

--删除列
skytf=> alter table test_26 drop column name;
ALTER TABLE


--再次查看 pg_attribute 列信息
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=> \d test_26
            Table "skytf.test_26"
 Column  |         Type          | Modifiers
---------+-----------------------+-----------
 id      | integer               |
 machine | character varying(32) |

--列恢复
skytf=> \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 值, 列上数据
                可以恢复。


测试场景二,删除列后,后面接着有数据 insert 的场景
--查看表信息
skytf=> 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)

--删除列
skytf=> alter table test_26 drop column name;
ALTER TABLE

--查看数据
skytf=> select * from test_26 limit 1;
 id | machine
----+---------
  1 | sky
(1 row)

--插入一条数据
skytf=> insert into test_26(id,machine) values (10001,'sky10001');
INSERT 0 1

--恢复删除的列
skytf=> \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 的场景
--查看表信息
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 where id=1;
 id |  name  | machine
----+--------+---------
  1 | francs | sky
(1 row)

--删除列
skytf=> alter table test_26  drop column name;
ALTER TABLE

--修改一条记录
skytf=> 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)

--恢复删除的列
skytf=> \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 的场景
--查看表信息
skytf=> \d test_26
            Table "skytf.test_26"
 Column  |         Type          | Modifiers
---------+-----------------------+-----------
 id      | integer               |
 name    | character varying(32) |
 machine | character varying(32) |

--查看表大小
skytf=> select pg_size_pretty(pg_relation_size('test_26'));
 pg_size_pretty
----------------
 440 kB
(1 row)

--删除列
skytf=> alter table test_26 drop column name;
ALTER TABLE

--再次查看表大小,表大小没变化
skytf=> select pg_size_pretty(pg_relation_size('test_26'));
 pg_size_pretty
----------------
 440 kB
(1 row)

--vacuum full
skytf=> vacuum full verbose test_26;
INFO:  vacuuming "skytf.test_26"
VACUUM

--再次查看表大小,发现变小了
skytf=> select pg_size_pretty(pg_relation_size('test_26'));
 pg_size_pretty
----------------
 360 kB
(1 row)

--列恢复
skytf=> \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".

--再次查看数据
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 5;
 id | name | machine
----+------+---------
  2 |      | sky
  3 |      | sky
  4 |      | sky
  5 |      | sky
  6 |      | sky
(5 rows)

    备注,在列被删除后,如果后面执行了 vacuum full 操作,被删除的列名能恢复,但列的数据丢失。
   
   
测试场景五,删除列后,后面接着做 vacuum  的场景   
--环境准备
skytf=> 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)

--删除列
skytf=> 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

--再次查看表大小
skytf=> select pg_size_pretty(pg_relation_size('test_26'));
 pg_size_pretty
----------------
 792 kB
(1 row)

--恢复列
skytf=> \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 limit 2;
 id |  name  | machine
----+--------+---------
  2 | francs | sky
  3 | francs | sky
(2 rows)

   备注:删除列后,如果些表被 vacuum, 被 drop 的列依然能完全恢复。

总结:
       1  在删除列后,可以通过修改系统表pg_attribute 的 attname, atttypid, attisdropped 值, 对删除的列进行恢复。
       2  在删除列后,对于后面有 insert 操作的行,则在列恢复后,后面接着的 insert 记录的该列为空。
       3  在删除列后,对于后面有 update 操作的行,则在列恢复后,后面接着的 update 的行的此列的数据为空。
       4  在删除列后,如果后面执行了 vacuum full  操作,被删除的列名能恢复,但数据丢失。
       5  在删除列后,如果此表被 vacuum, 被 drop 的列依然能完全恢复。

  评论这张
 
阅读(26963)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016