使用CREATE DATABASE WITH TEMPLATE复制库后表字段缺失求助

以下是导致CREATE DATABASE foo WITH TEMPLATE bar;未同步registration_participant表两个字段的常见原因及对应排查方式：

• 模板库bar的字段变更事务未提交
  PostgreSQL创建数据库时，会基于模板库的当前事务快照复制结构与数据。如果deregistration_requested和app字段是在未提交的事务中添加的，创建新库时的快照无法捕获该变更，导致新库缺失字段。
  排查操作：

  1. 连接到bar库：\c bar
  2. 检查未提交事务：SELECT * FROM pg_stat_activity WHERE datname = 'bar' AND state = 'idle in transaction';
  3. 若存在此类事务，提交或回滚后重新执行创建数据库命令。

• 创建新库时模板库存在活跃连接
  当模板库bar有其他非超级用户的活跃连接时，PostgreSQL会使用该连接建立时的快照创建新库，而非模板库的最新状态。如果字段是在活跃连接建立后添加的，新库就无法同步到该变更。
  排查操作：

  1. 查询bar库的活跃连接：SELECT * FROM pg_stat_activity WHERE datname = 'bar' AND pid != pg_backend_pid();
  2. 断开所有非当前连接后，重新执行创建数据库命令。

• 模板库中字段未被持久化创建
  可能存在字段添加后被意外回滚、或创建操作未成功执行的情况。可通过系统表确认字段是否真的存在于bar库：

  SELECT attname FROM pg_attribute 
  WHERE attrelid = 'registration_participant'::regclass 
  AND attname IN ('deregistration_requested', 'app');
  

  如果查询无结果，说明bar库中的字段并未成功创建，需重新添加后再执行复制操作。

内容的提问来源于stack exchange，提问作者John R