PostgreSQL Log表主键LogId达上限后重对齐主键值的技术问询

表结构详情 #

创建语句 #

CREATE TABLE Log
(
    LogId             serial      not null,
    JobId             integer     not null,
    Time              timestamp   without time zone,
    LogText           text        not null,
    primary key (LogId)
);
create index log_name_idx on Log (JobId);

表结构查询结果 #

bacula=> \d log
                  Table "public.log"
 Column  |           Type           |                      Modifiers
---------+--------------------------+------------------------------------------------------
 logid   | integer                  | not null default nextval('log_logid_seq'::regclass)
 jobid   | integer                  | not null
 time    | timestamp without time zone |
 logtext | text                     | not null
Indexes:
    "log_pkey" PRIMARY KEY, btree (logid)
    "log_name_idx" btree (jobid)

问题背景 #

LogId采用serial类型（对应PostgreSQL的serial4，本质是integer类型，最大值为2147483647），已达到上限，导致写入时触发「ERROR: integer out of range」错误。手动删除旧数据并执行vacuum后，序列当前值仍停留在2147483647，现有剩余数据共953811856行，需要将这些数据的LogId重新从1开始递增对齐，最新数据的LogId设为953811856，同时确保后续写入能正常使用序列。

注：曾在测试环境尝试直接将序列重置为1，但因未清空所有旧LogId条目导致主键重复；当前表无外键关联，使用PostgreSQL 9.2版本。

操作步骤 #

1. 全量备份数据 #

执行任何结构或数据修改前，务必对整个表或数据库做全量备份，防止操作失误导致数据丢失。

2. 移除主键约束与默认值 #

要修改主键字段LogId，需先移除主键约束和序列默认值：

-- 移除主键约束
ALTER TABLE Log DROP CONSTRAINT log_pkey;
-- 移除LogId的序列默认值
ALTER TABLE Log ALTER COLUMN LogId DROP DEFAULT;

3. 按时间顺序更新LogId #

以Time字段排序（确保最旧数据分配LogId=1），使用窗口函数为每行分配新的递增ID：

WITH ranked_logs AS (
    SELECT LogId, row_number() OVER (ORDER BY Time ASC) AS new_logid
    FROM Log
)
UPDATE Log
SET LogId = r.new_logid
FROM ranked_logs r
WHERE Log.LogId = r.LogId;

注意：数据量达9亿+，此操作会占用大量CPU、内存及磁盘IO，建议在业务低峰期执行，且确保服务器有足够资源支撑。

4. 重建主键约束 #

更新完成后，重新添加主键约束：

ALTER TABLE Log ADD CONSTRAINT log_pkey PRIMARY KEY (LogId);

5. 重置序列并恢复默认值 #

将序列log_logid_seq重置为当前最大LogId+1，确保后续写入的ID不会重复：

-- 方式1：自动获取当前最大LogId并设置序列
SELECT setval('log_logid_seq', (SELECT MAX(LogId) FROM Log));

-- 方式2：直接指定起始值（已知剩余数据共953811856行，下一个ID为953811857）
ALTER SEQUENCE log_logid_seq RESTART WITH 953811857;

恢复LogId的序列默认值，让后续写入自动使用序列生成ID：

ALTER TABLE Log ALTER COLUMN LogId SET DEFAULT nextval('log_logid_seq'::regclass);

6. 验证操作结果 #

执行以下语句确认序列和数据是否正常：

-- 查看序列当前值
SELECT currval('log_logid_seq');
-- 查看最大LogId
SELECT MAX(LogId) FROM Log;
-- 插入测试数据验证
INSERT INTO Log (JobId, Time, LogText) VALUES (1, now(), 'test log');
SELECT * FROM Log WHERE LogId = currval('log_logid_seq');

内容的提问来源于stack exchange，提问作者bill saplam