我有一个数据库，其中有几十个与外键链接的表。通常情况下，对于这些约束，我希望使用默认的" ON DELETE RESTRICT"行为。但是，当尝试与顾问共享数据库快照时，我需要删除一些敏感数据。我希望我对"从表CASCADE中删除"命令的记忆不是纯粹的幻觉。

我最终要做的是转储数据库，编写脚本以处理转储，方法是在所有外键约束中添加" ON DELETE CASCADE"子句，从中还原，执行删除，再次转储，然后删除" ON DELETE CASCADE"。 ，最后再次恢复。这比编写在SQL中执行此操作所需的删除查询要容易得多-删除数据库的整个部分不是正常操作，因此架构不完全适合它。

下次有人会遇到更好的解决方案吗？

解决方案

回答

我们可能想研究在PostgreSQL中使用模式。我在过去的项目中已经做到了这一点，以允许不同的人群或者开发人员拥有自己的数据。然后，我们可以使用脚本针对此类情况创建数据库的多个副本。

回答

@Tony：不，模式可能有用，实际上，我们使用它们来对数据库中的数据进行分区。但是我说的是在让顾问拥有数据库副本之前尝试清理敏感数据。我希望这些数据消失。

回答

我认为我们不需要像这样处理转储文件。执行流转储/还原，并进行处理。就像是：

createdb -h scratchserver scratchdb
createdb -h scratchserver sanitizeddb

pg_dump -h liveserver livedb --schema-only | psql -h scratchserver sanitizeddb
pg_dump -h scratchserver sanitizeddb | sed -e "s/RESTRICT/CASCADE/" | psql -h scratchserver scratchdb

pg_dump -h liveserver livedb --data-only | psql -h scratchserver scratchdb
psql -h scrachserver scratchdb -f delete-sensitive.sql

pg_dump -h scratchserver scratchdb --data-only | psql -h scratchserver sanitizeddb
pg_dump -Fc -Z9 -h scratchserver sanitizedb > sanitizeddb.pgdump

我们将所有DELETE sql存储在delete-sensitive.sql中的位置。如果我们不介意顾问使用CASCADE外键而不是RESTRICT外键获取数据库，则可以删除sanitizeddb数据库/步骤。

可能还有更好的方法，具体取决于我们需要执行此操作的频率，数据库的大小以及敏感数据的百分比，但是我想不出一种简单的方法来对合理大小的数据库执行一次或者两次数据库。毕竟，我们将需要一个不同的数据库，因此，除非我们已经拥有一个笨拙的集群，否则无法避免转储/还原周期，这可能会很耗时。

回答

我们不需要转储和还原。我们应该能够删除约束，使用级联重建约束，进行删除，再次删除约束，然后使用strict重建约束。

CREATE TABLE "header"
(
  header_id serial NOT NULL,
  CONSTRAINT header_pkey PRIMARY KEY (header_id)
);

CREATE TABLE detail
(
  header_id integer,
  stuff text,
  CONSTRAINT detail_header_id_fkey FOREIGN KEY (header_id)
      REFERENCES "header" (header_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
insert into header values(1);
insert into detail values(1,'stuff');
delete from header where header_id=1;
alter table detail drop constraint detail_header_id_fkey;
alter table detail add constraint detail_header_id_fkey FOREIGN KEY (header_id)
      REFERENCES "header" (header_id) on delete cascade;
delete from header where header_id=1;
alter table detail add constraint detail_header_id_fkey FOREIGN KEY (header_id)
      REFERENCES "header" (header_id) on delete restrict;

回答

我们可以将外键约束创建为DEFERRABLE。然后，我们可以在清理数据时临时禁用它们，并在完成后重新启用它们。看看这个问题。

回答

TRUNCATE table CASCADE;

我是Postgres的新手，所以我不确定TRUNCATE与DROP之间的权衡是什么。

回答

TRUNCATE只是从表中删除数据并保留结构