解决postgresql序列重复的问题

问题来源

在系统使用过程中出现了一个报错信息

错误：more than one owned sequence found (找到多个拥有的序列)

排查得知是数据库在迁移时生成了多个序列导致，如下图

现在数据库中基本每个序列都有三个

解决办法

背景介绍完毕，接下来开始删掉多余的序列

执行代码得到序列的删除语句

 SELECT 'DROP SEQUENCE "'||c.relname||'";' FROM pg_class c WHERE c.relkind ='S';

新建.sql文件，将语句复制到文件中
然后执行sql文件，选中“遇到错误时继续”

这里删掉的92个就是重复的序列，留下了54个序列

但是剩下的序列名都是以1或2结尾，我们把序列名进行一个修改

 UPDATE pg_class SET relname = substr(relname,0,char_length(relname)) WHERE relname like '%seq_'

至此，序列重复的问题就解决了
但是，因为序列混乱，而且在发现问题之前有些错误序列已经生成过数据。
导致现在有部分表的序列值比实际值小，插入数据时发生了主键冲突。
需要将序列的值更新为对应表的最大值

复制下面sql执行结果的第一列，然后执行sql

select 
 ( E'SELECT setval(\''||b.sequence_name||E'\', (select COALESCE(max('|| pg_attribute.attname || '),1) from '||pg_class.relname||'), true);'),
 pg_class.relname, pg_constraint.conname as pk_name,pg_attribute.attname as colname,pg_type.typname as typename  
 from 
pg_constraint  inner join pg_class 
on pg_constraint.conrelid = pg_class.oid 
inner join pg_attribute on pg_attribute.attrelid = pg_class.oid 
and  pg_attribute.attnum = pg_constraint.conkey[1]
inner join pg_type on pg_type.oid = pg_attribute.atttypid 
RIGHT JOIN (select seq_ns.nspname as sequence_schema, 
       seq.relname as sequence_name,
       tab_ns.nspname as table_schema,
       tab.relname as related_table
from pg_class seq
  join pg_namespace seq_ns on seq.relnamespace = seq_ns.oid
  JOIN pg_depend d ON d.objid = seq.oid 
  JOIN pg_class tab ON d.objid = seq.oid AND d.refobjid = tab.oid
  JOIN pg_namespace tab_ns on tab.relnamespace = tab_ns.oid
where seq.relkind = 'S' 
  and seq_ns.nspname = 'public') b on related_table=pg_class.relname
where 
-- pg_class.relname is null 
pg_constraint.contype = 'p'

执行SQL，问题解决