pg_restore未恢复序列值求助：生产库同步至开发库异常

问题背景 #

我有两个PostgreSQL数据库：生产库dbprod（对应用户uprod）和开发库dbdev（对应用户udev），需要将生产库的public模式同步到开发库。已完成数据恢复，但序列值未正确设置——导出文件明确包含序列创建及pg_catalog.setval语句，pg_restore执行无报错，怀疑是权限问题导致。

环境信息 #

PostgreSQL 11.17 (Debian 11.17-0+deb10u1)

已执行操作 #

• 导出生产库public模式：

  pg_dump --file '/home/punt/dump_db/prod.tar.gz' --format=t --schema 'public' 'dbprod'
  

• 重命名开发库原public模式：

  ALTER SCHEMA public RENAME TO public_old;
  

• 恢复导出文件到开发库：

  pg_restore --dbname='dbdev' --format=t --verbose /home/punt/dump_db/prod.tar.gz
  

• 后续处理了权限配置

解决办法 #

1. 确保恢复时使用有权限的用户 #

如果pg_restore执行用户（比如udev）没有序列的操作权限，setval语句会静默失败。可以：

• 先在dbdev中创建生产库用户uprod并授权，再用该用户执行恢复：

  CREATE USER uprod;
  GRANT ALL PRIVILEGES ON DATABASE dbdev TO uprod;
  

  pg_restore --dbname='dbdev' --format=t --verbose --role=uprod /home/punt/dump_db/prod.tar.gz
  

• 或者直接给udev赋予public模式下所有序列的权限：

  GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO udev;
  -- 设置默认权限，避免后续新增序列出现同样问题
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO udev;
  

2. 手动重新执行setval语句 #

从导出文件中提取所有setval命令，手动在dbdev中执行：

# 从tar包中提取SQL内容并过滤setval语句
tar -xf /home/punt/dump_db/prod.tar.gz -O | grep -E "pg_catalog.setval" > setval_commands.sql
# 在dbdev中执行这些语句
psql --dbname dbdev -f setval_commands.sql

如果只需要处理单个序列，可直接执行：

SELECT pg_catalog.setval('public.your_sequence_name', (SELECT MAX(id) FROM public.your_table), true);

替换your_sequence_name和your_table为实际的序列名和关联表名。

3. 检查并修正序列的所有者 #

如果序列的所有者不是有权限的用户，也会导致setval失效：

• 查看public模式下所有序列的所有者：

  SELECT relname AS sequence_name, usename AS owner
  FROM pg_class
  JOIN pg_user ON pg_class.relowner = pg_user.usesysid
  WHERE relkind = 'S' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');
  

• 批量修改所有序列的所有者为udev：

  DO $$
  DECLARE
      seq_record record;
  BEGIN
      FOR seq_record IN 
          SELECT relname FROM pg_class 
          WHERE relkind = 'S' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
      LOOP
          EXECUTE 'ALTER SEQUENCE public.' || seq_record.relname || ' OWNER TO udev;';
      END LOOP;
  END $$;
  

内容的提问来源于stack exchange，提问作者0xPunt