Add option --drop-cascade for pg_dump/restore

From: Haotian Wu <whtsky(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Add option --drop-cascade for pg_dump/restore
Date: 2021-04-08 06:24:42
Message-ID: CADHB_-Bk2zQdzi14gmjt=4k3+p1=TkibQwfeEaqAr1Zkd=T6AQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

I'd like to propose adding `--drop-cascade` option for pg_dump/restore

Usecase:

I'd like to be able to restore an old custom format database dump as a
single transaction ( so the current data won't lose if restore fails). The
database has added some new constraints after backup so a CASCADE DROP is
needed.

This allows for restoring an old backup after adding new constraints,

at the risk of losing new data.

There're already some requests for supporting cascade drop:

-
https://dba.stackexchange.com/questions/281384/pg-restore-clean-not-working-because-cascade-drop
-
https://www.postgresql.org/message-id/flat/Pine.LNX.4.33.0308281409440.6957-100000%40dev2.int.journyx.com
-
https://www.postgresql.org/message-id/flat/50EC9574.9060500%40encs.concordia.ca

Design & Implementation

Basically I'm following the changes in adding `--if-exists` patch:
https://github.com/postgres/postgres/commit/9067310cc5dd590e36c2c3219dbf3961d7c9f8cb
. pg_dump/restore will inject a CASCADE clause to each DROP command.

The attached patch has been tested on our old backup. I'm happy to get some
feedback.

Attachment Content-Type Size
0001-pg_dump-restore-add-drop-cascade-option.patch application/octet-stream 16.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2021-04-08 06:33:20 Re: SQL-standard function body
Previous Message Tom Lane 2021-04-08 06:16:30 Re: pgsql: autovacuum: handle analyze for partitioned tables