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.klaver@aklaver.com