如何备份与恢复使用pgsodium加密的PostgreSQL数据库

针对你遇到的恢复时加密触发器重复加密数据的问题，下面是几个可行的解决方案：

1. 恢复时临时禁用触发器（需超级权限） #

如果直接用pg_restore的--disable-triggers参数无效，可能是执行权限不足或恢复顺序问题，可以手动分步操作：

• 先连接到目标数据库，禁用目标表的所有触发器：

  -- 单个表
  ALTER TABLE your_encrypted_table DISABLE TRIGGER ALL;
  -- 若有多个加密表，批量执行
  DO $$
  DECLARE
      rec RECORD;
  BEGIN
      FOR rec IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE '%your_encrypted_prefix%' LOOP
          EXECUTE 'ALTER TABLE ' || quote_ident(rec.tablename) || ' DISABLE TRIGGER ALL;';
      END LOOP;
  END $$;
  

• 执行恢复操作（比如pg_restore -d target_db backup_file.dump）
• 恢复完成后重新启用触发器：

  ALTER TABLE your_encrypted_table ENABLE TRIGGER ALL;
  -- 批量启用对应上面的批量禁用
  DO $$
  DECLARE
      rec RECORD;
  BEGIN
      FOR rec IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE '%your_encrypted_prefix%' LOOP
          EXECUTE 'ALTER TABLE ' || quote_ident(rec.tablename) || ' ENABLE TRIGGER ALL;';
      END LOOP;
  END $$;
  

2. 先删除加密触发器，恢复后重建 #

pgsodium的加密触发器通常是通过pgsodium.create_encrypted_trigger创建的，你可以先删除这些触发器再恢复：

• 源数据库中查询触发器名称：

  SELECT tgname, tablename FROM pg_trigger WHERE tgname LIKE '%pgsodium%';
  

• 目标数据库中删除对应触发器：

  DROP TRIGGER pgsodium_encrypt_trigger_your_table ON your_table;
  

• 执行恢复操作
• 恢复完成后重新创建加密触发器：

  SELECT pgsodium.create_encrypted_trigger('your_table', 'encrypted_column');
  

3. 使用COPY命令导入（跳过触发器） #

利用PostgreSQL的COPY命令的DISABLE_TRIGGER选项，直接导入已加密的数据而不触发加密逻辑：

• 备份时导出加密表数据：

  COPY your_encrypted_table TO '/path/to/encrypted_data.csv' WITH (FORMAT csv, HEADER);
  

• 恢复时先确保目标表结构和源表一致（可以用pg_dump --schema-only导出结构），然后执行：

  COPY your_encrypted_table FROM '/path/to/encrypted_data.csv' WITH (FORMAT csv, HEADER, DISABLE_TRIGGER);
  

关键注意事项 #

• 确保目标数据库的pgsodium密钥与源数据库完全一致，否则恢复的加密数据无法解密。可以从源数据库导出密钥后导入目标库：

  -- 源库导出
  SELECT * FROM pgsodium.key;
  -- 目标库插入（替换为源库的密钥记录）
  INSERT INTO pgsodium.key (id, key, nonce, created_at) VALUES (...);
  

• 所有操作需使用超级用户权限执行，因为修改触发器、禁用触发器都需要高权限。

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