tabla - Eliminaciones en cascada en PostgreSQL

llaves primarias y foraneas en postgresql

Question

Tengo una base de datos con algunas docenas de tablas interconectadas con claves externas. En circunstancias normales, quiero que el comportamiento predeterminado BORRAR RESTRICCIÓN para esas restricciones. Pero cuando intentaba compartir una instantánea de la base de datos con un consultor, necesitaba eliminar algunos datos confidenciales. Desearía que mi memoria de un comando DELETE FROM Table CASCADE no hubiera sido pura alucinación.

Lo que terminé haciendo fue volcar la base de datos, escribir una secuencia de comandos para procesar el volcado añadiendo cláusulas ON DELETE CASCADE también todas las restricciones de clave externa, restaurar de eso, realizar mis eliminaciones, volcar nuevamente, eliminar ON DELETE CASCADE, y finalmente restaurar de nuevo. Eso fue más fácil que escribir la consulta de borrado que hubiera necesitado para hacer esto en SQL: eliminar partes enteras de la base de datos no es una operación normal, por lo que el esquema no está exactamente adaptado.

¿Alguien tiene una mejor solución para la próxima vez que surja algo como esto?

Answer 1

Es posible que desee examinar el uso de esquemas con PostgreSQL. Lo hice en proyectos anteriores para permitir que diferentes grupos de personas o desarrolladores tengan sus propios datos. Luego puede usar sus scripts para crear múltiples copias de su base de datos solo para tales situaciones.

Answer 2

@Tony: No, los esquemas pueden ser útiles, y de hecho, los usamos para dividir los datos en nuestra base de datos. Pero estoy hablando de tratar de eliminar datos confidenciales antes de permitir que un consultor tenga una copia de la base de datos. Quiero que esos datos se hayan ido.

Answer 3

No creo que necesites procesar el archivo de volcado de esa manera. Realice un volcado / restauración de transmisión y procese eso. Algo como:

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

donde almacena todos sus archivos SQL DELETE en delete-sensitive.sql. La base de datos / pasos de sanitizeddb se puede eliminar si no le importa que el asesor obtenga un archivo de base de datos con las claves externas CASCADE en lugar de las claves externas RESTRICT.

También podría haber mejores formas dependiendo de la frecuencia con la que necesite hacer esto, cuán grande es la base de datos y qué porcentaje de datos es confidencial, pero no puedo pensar en una forma más sencilla de hacerlo una o dos veces para un tamaño razonable base de datos. Necesitarás una base de datos diferente después de todo, así que, a menos que ya tengas un clúster slony, no puedes evitar el ciclo de volcado / restauración, que puede llevar mucho tiempo.

Answer 4

No necesita volcar y restaurar. Debería poder soltar la restricción, reconstruirla con cascada, hacer sus eliminaciones, soltarla de nuevo y reconstruirla con restricción.

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;

Answer 5

TRUNCATE simplemente elimina los datos de la tabla y abandona la estructura

Answer 6

TRUNCATE table CASCADE;

Soy un principiante de Postgres, así que no estoy seguro de cuál es la desventaja para TRUNCATE vs. DROP.

Answer 7

Puede crear las restricciones de clave externa como DEFERRABLE. Luego podrá deshabilitarlos temporalmente mientras friega los datos y los vuelve a habilitar cuando termina. Eche un vistazo a esta pregunta .