Re: Can "on delete cascade" dependency be used in pgdump or similar ?

On 2/27/26 3:13 PM, Adrian Klaver wrote:
> On 2/27/26 2:39 PM, dfgpostgres wrote:
>> pg 15.15 on linux
>>
>> I have a DB with a table called "projects" which has a primary key
>> column called "project".  Other child tables are linked in a primary/
>> foreign key relationship to "projects" with "on delete cascade".  Each
>> of those may have grandchild tables linked in via other primary/
>> foreign key relationships, all with the "on delete cascade".
>> Etc... .  If I delete project "a", it'll cascade delete the children,
>> grandchild, etc...  recs.  All traces of project "a" will be gone.
>>
>> But I don't really want to lose the data from project "a", I want to
>> archive it in another DB (same DB server, different DB).  The brain-
>> numb method I've been using thus far is to copy the whole DB via
>> pgdump then use that to create the DB "a_archive".  Then (here's the
>> wasteful part) basically delete all the projects in "a-archive" EXCEPT
>> for project "a".  Then, when that's done, go to the main DB and delete
>> project "a". In effect, I just archived all the data for project "a"
>> and put it in the DB called "a_archive".while relieving the main DB of
>> the project "a" data. But what would be really neat is to leverage
>> that cascade on delete stuff to just pgdump project "a" and use that
>> to create "a_archive".
>>
>> Can pgdump do something like that ?
>
> The best you can do, with pg_dump, is use:

Forget this idea.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com