Каскадное удаление в PostgreSQL

Вы можете изучить возможность использования схемы с 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

где вы храните все свои SQL-запросы DELETE в delete-sensitive.sql. База данных / шаги sanitizeddb могут быть удалены, если вы не возражаете, если консультант получит базу данных с внешними ключами CASCADE вместо внешних ключей RESTRICT.

Также могут быть лучшие способы в зависимости от того, как часто вам нужно это делать, насколько велика база данных и какой процент данных является конфиденциальным, но я не могу придумать более простого способа сделать это один или два раза для базы данных разумного размера. В конце концов, вам понадобится другая база данных, поэтому, если у вас еще нет slony-кластера, не избежать цикла дампа / восстановления, который может занять много времени.

Сбрасывать и восстанавливать не нужно. Вы должны иметь возможность просто отбросить ограничение, перестроить его каскадом, выполнить удаление, снова отбросить и перестроить с помощью restrict.

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 просто удаляет данные из таблицы и оставляет структуру