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

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL: 给大表 varchar 字段扩长的方法  

2011-12-03 16:43:49|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |


            昨天有个日志库有个需求,需要将日志表的一个 varchar 字段扩长,在数据库中给字段增长
是一个非常普遍的需求,但在 PostgreSQL 里,这却是件蛋疼的事,因为 PostgreSQL 在给字段增长
的场合,大多数据情况下需要重写表,这里可以参考我以前写的 blog
http://francs3.blog.163.com/blog/static/4057672720111158654916/ 

 

     考虑到直接修改原表字段类型会对所有表数据进行重写,这个耗时是非常长的,而且在 " ALTER TABLE"
过程中会锁表,表上所有的查询,插入等操作都不能进行,于是想到了个釜底抽薪的方法,可以解决这个问题,
接下来往下看。


一 场景介绍
父表: tbl_tmp_log
子表: 1158 张日表,如 tbl_tmp_log_20111203 ,日表大小在 23 G左右
需求: 修改 tbl_tmp_log 表的 refer 字段类型,从 character varying(2000)
            扩容到  character varing(5000)


二 基础信息
--2.1 父表表定义
skytf=> \d tbl_tmp_log
                                     Table "public.tbl_tmp_log"
  Column   |            Type             |                          Modifiers                         
-----------+-----------------------------+-------------------------------------------------------------
 xxx       | bigint                      | not null default nextval('tbl_tmp_log_id_seq'::regclass)
 xxx       | character varying(50)       |
 xxx       | character varying(50)       |
 xxx       | timestamp without time zone | not null default now()
 xxx       | character varying(2000)     |
 xxx       | character varying(10)       |
 xxx       | integer                     |
 refer     | character varying(2000)     |
 xxx       | character varying(50)       |
 xxx       | character varying           |
 xxx       | character varying(50)       |
 xxx       | character varying(50)       |
 xxx       | boolean                     |
 xxx       | integer                     |
 xxx       | character varying(50)       |
 xxx       | integer                     |
Indexes:
    "pk_tbl_tmp_log_id" PRIMARY KEY, btree (id)
    "idx_tbl_tmp_log_ntime" btree (ntime), tablespace "tbs_skytf_idx"
Number of child tables: 1158 (Use \d+ to list them.)
Tablespace: "tbs_skytf_idx"


--2.2 子表情况
skytf=> \dt+
                                  List of relations
 Schema  |             Name              | Type  |  Owner  |    Size    | Description
---------+-------------------------------+-------+---------+------------+-------------
 skytf | tbl_tmp_log_20111108       | table | skytf | 26 GB      |
 skytf | tbl_tmp_log_20111109       | table | skytf | 26 GB      |
 skytf | tbl_tmp_log_20111110       | table | skytf | 24 GB      |
 skytf | tbl_tmp_log_20111111       | table | skytf | 25 GB      |
 skytf | tbl_tmp_log_20111112       | table | skytf | 28 GB      |
 skytf | tbl_tmp_log_20111113       | table | skytf | 26 GB      |
 skytf | tbl_tmp_log_20111114       | table | skytf | 23 GB      |
 skytf | tbl_tmp_log_20111115       | table | skytf | 23 GB      |
 skytf | tbl_tmp_log_20111116       | table | skytf | 23 GB      |
 skytf | tbl_tmp_log_20111117       | table | skytf | 23 GB      |
 skytf | tbl_tmp_log_20111118       | table | skytf | 24 GB      |
 skytf | tbl_tmp_log_20111119       | table | skytf | 25 GB      |
 skytf | tbl_tmp_log_20111120       | table | skytf | 23 GB      |
 skytf | tbl_tmp_log_20111121       | table | skytf | 20 GB      |
 skytf | tbl_tmp_log_20111122       | table | skytf | 20 GB      |
 skytf | tbl_tmp_log_20111123       | table | skytf | 20 GB      |
 skytf | tbl_tmp_log_20111124       | table | skytf | 20 GB      |
 skytf | tbl_tmp_log_20111125       | table | skytf | 21 GB      |
 skytf | tbl_tmp_log_20111126       | table | skytf | 23 GB      |
 skytf | tbl_tmp_log_20111127       | table | skytf | 21 GB      |
 skytf | tbl_tmp_log_20111128       | table | skytf | 19 GB      |
 skytf | tbl_tmp_log_20111129       | table | skytf | 20 GB      |
 skytf | tbl_tmp_log_20111130       | table | skytf | 21 GB      |
 skytf | tbl_tmp_log_20111201       | table | skytf | 25 GB      |
 skytf | tbl_tmp_log_20111202       | table | skytf | 6805 MB    |
 skytf | tbl_tmp_log_20111203       | table | skytf | 0 bytes    |
 skytf | tbl_tmp_log_20111204       | table | skytf | 0 bytes    |
 skytf | tbl_tmp_log_20111205       | table | skytf | 0 bytes    |
 skytf | tbl_tmp_log_20111206       | table | skytf | 0 bytes    |
 skytf | tbl_tmp_log_20111207       | table | skytf | 0 bytes    |
 
      备注:为了便于显示,只列出少数表,实际上有 1158 张子表。
 
 
 三 实施过程
 --3.1 清理历史数据
       由于这是日志库,数据仓库会实时抽取数据,那么只要是同步到仓库里的数据,在生产环境下是可以清除的,
 所以接下来向仓库部门请求核对 2011-11-08 到 2011-12-01 的数据,核对无误后,这些数据都可以清除了。
 
--3.1.1 数据清理后的表情况
skytf=> \dt+
                                  List of relations
 Schema  |             Name              | Type  |  Owner  |    Size    | Description
---------+-------------------------------+-------+---------+------------+-------------
 skytf | tbl_tmp_log_20111202       | table | skytf | 6805 MB    |
 skytf | tbl_tmp_log_20111203       | table | skytf | 0 bytes    |
 skytf | tbl_tmp_log_20111204       | table | skytf | 0 bytes    |
 skytf | tbl_tmp_log_20111205       | table | skytf | 0 bytes    |
 skytf | tbl_tmp_log_20111206       | table | skytf | 0 bytes    |
 skytf | tbl_tmp_log_20111207       | table | skytf | 0 bytes    |
 

--3.2 备份表  tbl_tmp_log_20111202 表结构
       表 tbl_tmp_log_20111202 的表结构和权限信息可以通 pg_dump 导出 如
       pg_dump -h 127.0.0.1 -p 1921 -E UTF8 -t "skytf.tbl_tmp_log_20111202" -s -v skytf > tbl_tmp_log_20111202.ddl
 
 
--3.3 重命名日表 tbl_tmp_log_20111202 的表名和索引
alter table tbl_tmp_log_20111202 rename  to tbl_tmp_log_20111202_bak;
alter index tbl_tmp_log_20111202_pkey rename to tbl_tmp_log_20111202_pkey_bak;
alter index idx_tbl_tmp_log_20111202_ntime rename to idx_tbl_tmp_log_20111202_ntime_bak;


--3.4 取消日表 tbl_tmp_log_20111202 和父表的继承关系
alter table tbl_tmp_log_20111202_bak  no inherit public.tbl_tmp_log;

   备注:这里取消表 tbl_tmp_log_20111202_bak 和父表的继承关系是因为接下来打算给父表
             tbl_tmp_log 扩字段长度,如果不取消这张表的继承关系,那么这张表的数据是需要重写的。
        

--3.5 创建同名表 tbl_tmp_log_20111202
         在步骤 3.3,3.4 结束后,迅速执行步骤 3.2 导出的建表脚本, 脚本中包括表 tbl_tmp_log_20111202
    的建表语句,索引创建语句和权限信息,所以执行后,和原表的信息是一样的。只不过是一张空表而已。

 

--3.6 给父亲表字段扩容
skytf=> alter table public.tbl_tmp_log alter column refer type character varying(5000);
ALTER TABLE
Time: 83031.807 ms

  备注:在步骤 3.5 完成后,接下来终于可以给字段扩容了,语句如上,耗时 83 秒,这个时间已经算很少了。
            这个字段扩容语句可以同时给了 1000 多张字表同样字段也扩容了。
       

--3.7 将备份表数据插回新表
insert into tbl_tmp_log_20111202 select * from tbl_tmp_log_20111202_bak;


--3.8 删除备份表
drop table tbl_tmp_log_20111202_bak;

      备注:到了这步,就算完成了字段扩容操作。
  

四 总结:
        1   以上是实现给大表 varchar 字段扩长的一个方法,当然方法也有很多。
        2   这种方法对生产库的影响非常小的,对生产库的影响在一分钟左右。
        3   上面方法的一个重要步骤是恰当地拿掉了备份表和父表的继承关系 ,正因为这样,避免了重写 6 G的日表数据。  
        4   由于这次的场景是日志库,如果碰到了业务库的分区表需要扩字段长度, 还得另外考虑方案。

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

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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