PostgreSQL 修改字段类型从int到bigint

由于现在pg的版本,修改int到bigint仍然需要rewrite表,会导致表阻塞,无法使用。但可以考虑其他方式来做。
此问题是排查现网pg使用序列的情况时遇到的。

由于int的最大值只有21亿左右,而且自增列多为主键,当达到最大值时,数据就会无法插入。一般情况是修改类型为bigint,但直接做会锁表,影响现网使用。

这里分两块来看:

1、分区表(修改序列):
对于分区表可以直接修改序列为循环形式,而且最大值设置为int的最大值,因为单个分区表很少会将int值用完。

alter sequence seq_name MAXVALUE 2147483647  CYCLE;

注意这里适用于按日或按月分区的表,对于hash分区表,只能修改字段类型。 

 

2、非分区表(修改为bigint)

由于创建表时,可能使用的是serial,所以此时就需要新建一个序列,不然字段id在删除时,之前的序列也会跟着一同被删除。
下面的步骤中要注意,添加主键约束部分,如果new_id上没有not null约束,则此时会进行全表扫描检查有无not null的记录。虽然pg检查记录是否为not null的操作比较快,但这一步还是会锁较长时间(看记录数多少而定)。

alter table table_name add  column new_id bigint;

---循环更新new_id
do language plpgsql $$
declare
i int;
begin
for i in 0..1000 loop
update table_name set new_id = id where id >= min(id)+ (max(id)-min(id))/1000*i and id< min(id) + max(id)-min(id))/1000*(i+1);
end loop;
end $$;

create unique index CONCURRENTLY on table_name(new_id);

BEGIN; ALTER TABLE table_name DROP CONSTRAINT table_name_pkey; CREATE SEQUENCE table_name_new_id_seq; ALTER TABLE table_name ALTER COLUMN new_id SET DEFAULT nextval('table_name_new_id_seq'::regclass); UPDATE table_name SET new_id = id WHERE new_id IS NULL; ALTER TABLE table_name ADD CONSTRAINT table_name_pkey PRIMARY KEY USING INDEX table_name_pk_idx; ALTER TABLE table_name DROP COLUMN id; ALTER TABLE table_name RENAME COLUMN new_id to id; ALTER SEQUENCE table_name_new_id_seq RENAME TO table_name_id_seq; SELECT setval('table_name_id_seq', (SELECT max(id) FROM table_name)); COMMIT;

 

还有可以通过修改数据字典来实现同样操作的,不过不推荐使用,有可能引起元数据损坏。

 

posted on 2018-09-12 18:18 Still water run deep 阅读(...) 评论(...) 编辑 收藏

导航

公告