昨天有个日志库有个需求，需要将日志表的一个 varchar 字段扩长，在数据库中给字段增长是一个非常普遍的需求，但在 PostgreSQL 里，这却是件蛋疼的事，因为 PostgreSQL 在给字段增长的场景，大多数据情况下需要重写表，这里可以参考我以前写的 blog： https://postgres.fun/20110215200654.html
考虑到直接修改原表字段类型会对所有表数据进行重写，这个耗时是非常长的，而且在 ALTER TABLE 过程中会锁表，表上所有的查询，插入等操作都不能进行，于是想到了个釜底抽薪的方法，可以解决这个问题，接下来往下看。
场景介绍
父表： tbl_tmp_log
子表： 1158 张日表，如 tbl_tmp_log_20111203 ，日表大小在 23 G左右
需求： 修改 tbl_tmp_log 表的 refer 字段类型，从 character varying(2000) 扩容到 character varing(5000)
环境信息
父表表定义
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
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"
|
子表信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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_20111207 | table | skytf | 0 bytes |
...省略
|
备注：为了便于显示，只列出少数表，实际上有 1158 张子表。
实施过程
清理历史数据
由于这是日志库，数据仓库会实时抽取数据，那么只要是同步到仓库里的数据，在生产环境下是可以清除的，所以接下来向仓库部门请求核对 2011-11-08 到 2011-12-01 的数据，核对无误后，这些数据都可以清除了。
数据清理后的表情况
1
2
3
4
5
6
7
8
9
10
|
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 |
|
备份表 tbl_tmp_log_20111202 表结构
表 tbl_tmp_log_20111202 的表结构和权限信息可以通 pg_dump 导出 如
1
|
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
|
重命名日表 tbl_tmp_log_20111202 的表名和索引
1
2
3
|
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;
|
取消日表 tbl_tmp_log_20111202 和父表的继承关系
1
|
alter table tbl_tmp_log_20111202_bak no inherit public.tbl_tmp_log;
|
备注：这里取消表 tbl_tmp_log_20111202_bak 和父表的继承关系是因为接下来打算给父表 tbl_tmp_log 扩字段长度，如果不取消这张表的继承关系，那么这张表的数据是需要重写的。
创建同名表 tbl_tmp_log_20111202
在步骤 3.3,3.4 结束后，迅速执行步骤 3.2 导出的建表脚本, 脚本中包括表 tbl_tmp_log_20111202 的建表语句，索引创建语句和权限信息，所以执行后，和原表的信息是一样的。只不过是一张空表而已。
给父亲表字段扩容
1
2
|
skytf=> alter table public.tbl_tmp_log alter column refer type character varying(5000);ALTER TABLE
Time: 83031.807 ms
|
备注：在步骤 3.5 完成后，接下来终于可以给字段扩容了，语句如上，耗时 83 秒，这个时间已经算很少了。这个字段扩容语句可以同时给了 1000 多张字表同样字段也扩容了。
将备份表数据插回新表
1
|
insert into tbl_tmp_log_20111202 select * from tbl_tmp_log_20111202_bak;
|
删除备份表
1
|
drop table tbl_tmp_log_20111202_bak;
|
备注：到了这步，就算完成了字段扩容操作。
总结
- 以上是实现给大表 varchar 字段扩长的一个方法，当然方法也有很多。
- 这种方法对生产库的影响非常小的，对生产库的影响在一分钟左右。
- 上面方法的一个重要步骤是恰当地拿掉了备份表和父表的继承关系，正因为这样，避免了重写 6 G的日表数据。
- 由于这次的场景是日志库，如果碰到了业务库的分区表需要扩字段长度，还得另外考虑方案。
原创文章，作者：745907710，如若转载，请注明出处：https://blog.ytso.com/237811.html
