<p>I have a database with a few dozen tables interlinked with foreign keys. Under normal circumstances, I want the default <code>ON DELETE RESTRICT</code> behavior for those constraints. But when trying to share a snapshot of the database with a consultant, I needed to remove some sensitive data. I wish that my memory of a <code>DELETE FROM Table CASCADE</code> command hadn't been pure hallucination.</p>

<p>What I ended out doing was dumping the database, writing a script to process the dump by adding <code>ON DELETE CASCADE</code> clauses too all the foreign key constraints, restoring from that, performing my deletes, dumping again, removing the <code>ON DELETE CASCADE</code>, and finally restoring again. That was easier than writing the deletion query I'd have needed to do this in SQL -- removing whole slices of the database isn't a normal operation, so the schema isn't exactly adapted to it.</p>

<p>Does anyone have a better solution for the next time something like this comes up?</p>

[{'answer_id': 45893, 'author': 'Tony Lenzi', 'author_id': 4513, 'author_profile': 'https://Stackoverflow.com/users/4513', 'pm_score': 0, 'selected': False, 'text': '<p>You may want to look into using <a href="http://www.postgresql.org/docs/8.2/static/ddl-schemas.html" rel="nofollow noreferrer">schemas</a> with PostgreSQL.  I\'ve done this in past projects to allow different groups of people or developers to have their own data.  Then you can use your scripts to create multiple copies of your database for just such situations.</p>\n'}, {'answer_id': 47635, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': "<p>@Tony: No, schemas can be useful, and indeed, we use them to partition data in our database. But I'm talking about trying to scrub sensitive data before letting a consultant have a copy of the db. I want that data gone.</p>\n"}, {'answer_id': 56978, 'author': 'angch', 'author_id': 5386, 'author_profile': 'https://Stackoverflow.com/users/5386', 'pm_score': 0, 'selected': False, 'text': '<p>I don\'t think you\'d need to process the dump file like that. Do a streaming dump/restore, and process that. Something like:</p>\n\n<pre><code>createdb -h scratchserver scratchdb\ncreatedb -h scratchserver sanitizeddb\n\npg_dump -h liveserver livedb --schema-only | psql -h scratchserver sanitizeddb\npg_dump -h scratchserver sanitizeddb | sed -e "s/RESTRICT/CASCADE/" | psql -h scratchserver scratchdb\n\npg_dump -h liveserver livedb --data-only | psql -h scratchserver scratchdb\npsql -h scrachserver scratchdb -f delete-sensitive.sql\n\npg_dump -h scratchserver scratchdb --data-only | psql -h scratchserver sanitizeddb\npg_dump -Fc -Z9 -h scratchserver sanitizedb &gt; sanitizeddb.pgdump\n</code></pre>\n\n<p>where you store all your DELETE sqls in delete-sensitive.sql. The sanitizeddb database/steps can be removed if you don\'t mind the consultant getting a db with CASCADE foreign keys instead of RESTRICT foreign keys.</p>\n\n<p>There might also be better ways depending on how often you need to do this, how big the database is, and what percentage of data is sensitive, but I can\'t think of a simpler way to do it <strong>once or twice</strong> for a <strong>reasonably sized</strong> database. You\'d need a different database after all, so unless you already have a slony cluster, can\'t avoid the dump/restore cycle, which might be time consuming.</p>\n'}, {'answer_id': 155396, 'author': 'Grant Johnson', 'author_id': 12518, 'author_profile': 'https://Stackoverflow.com/users/12518', 'pm_score': 2, 'selected': False, 'text': '<p>You do not need to dump and restore.   You should be able to just drop the constraint, rebuild it with cascade, do your deletes, drop it again, and the rebuild it with restrict.</p>\n\n<pre><code>CREATE TABLE "header"\n(\n  header_id serial NOT NULL,\n  CONSTRAINT header_pkey PRIMARY KEY (header_id)\n);\n\nCREATE TABLE detail\n(\n  header_id integer,\n  stuff text,\n  CONSTRAINT detail_header_id_fkey FOREIGN KEY (header_id)\n      REFERENCES "header" (header_id) MATCH SIMPLE\n      ON UPDATE NO ACTION ON DELETE NO ACTION\n);\ninsert into header values(1);\ninsert into detail values(1,\'stuff\');\ndelete from header where header_id=1;\nalter table detail drop constraint detail_header_id_fkey;\nalter table detail add constraint detail_header_id_fkey FOREIGN KEY (header_id)\n      REFERENCES "header" (header_id) on delete cascade;\ndelete from header where header_id=1;\nalter table detail add constraint detail_header_id_fkey FOREIGN KEY (header_id)\n      REFERENCES "header" (header_id) on delete restrict;\n</code></pre>\n'}, {'answer_id': 158446, 'author': 'agnul', 'author_id': 6069, 'author_profile': 'https://Stackoverflow.com/users/6069', 'pm_score': 1, 'selected': False, 'text': '<p>You could create the foreign key constraints as DEFERRABLE. Then you would be able to temporarily disable them while you scrub the data and re-enable them when you are done. Have a look at <a href="https://stackoverflow.com/questions/139884/how-do-i-disable-referential-integrity-in-postgres-82">this question</a>.</p>\n'}, {'answer_id': 3010204, 'author': 'user362911', 'author_id': 362911, 'author_profile': 'https://Stackoverflow.com/users/362911', 'pm_score': 1, 'selected': False, 'text': "<pre><code>TRUNCATE table CASCADE;\n</code></pre>\n\n<p>I'm a Postgres novice, so I'm not sure what the trade-off is for TRUNCATE vs. DROP.</p>\n"}, {'answer_id': 3320975, 'author': 'Tim Davis', 'author_id': 400534, 'author_profile': 'https://Stackoverflow.com/users/400534', 'pm_score': 0, 'selected': False, 'text': '<p>TRUNCATE just removes the data from table and leaves the structure</p>\n'}]
['https://Stackoverflow.com/questions/45611', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']