批量修复PostgreSQL自增主键自增序列

背景

通过腾讯云dts迁移数据库后导致自增主键的自增值会被重置，导致无法新增数据

解决方法

使用SETVAL函数修复单表自增序列
SELECT SETVAL('table_name_id_seq', COALESCE(MAX(id), 1) ) FROM table_name;

表特别多时可使用如下sql批量生成修复sql

SELECT 'SELECT SETVAL(' ||
  quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
  ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
  quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
  pg_depend AS D,
  pg_class AS T,
  pg_attribute AS C,
  pg_tables AS PGT
WHERE S.relkind = 'S'
  AND S.oid = D.objid
  AND D.refobjid = T.oid
  AND D.refobjid = C.attrelid
  AND D.refobjsubid = C.attnum
  AND T.relname = PGT.tablename
ORDER BY S.relname;

使用Python批处理:

import psycopg2
def fix():
    try:
        conn = psycopg2.connect(
            database='', 
            user='',
            password='', 
            host='', 
            port=
        )
        cur = conn.cursor()
        cur2 = conn.cursor()
        cur.execute(""" 
            SELECT 'SELECT SETVAL(' ||
                quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
                ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
                quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
            FROM pg_class AS S,
                pg_depend AS D,
                pg_class AS T,
                pg_attribute AS C,
                pg_tables AS PGT
            WHERE S.relkind = 'S'
                AND S.oid = D.objid
                AND D.refobjid = T.oid
                AND D.refobjid = C.attrelid
                AND D.refobjsubid = C.attnum
                AND T.relname = PGT.tablename
            ORDER BY S.relname;
                            """)
        for r in cur.fetchall():
            print(r[0])
            cur2.execute(r[0])
    except Exception as e:
        print("error:", str(e))
    finally:
        conn.close()

if __name__ == "__main__":
    fix()

参考文档

(https://wiki.postgresql.org/wiki/Fixing_Sequences)