Feedback-ID: i76984098:Fastmail
Message-ID: <fc310113-3c19-4c03-b608-ffec127f1cad@aklaver.com>
Date: Fri, 27 Feb 2026 15:15:45 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Can "on delete cascade" dependency be used in pgdump or similar ?
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: dfgpostgres <dfgpostgres3@gmail.com>, pgsql-general@lists.postgresql.org
References: <CAAcmDX_zrfbQ2ixpGH_92SqeM=OJs5wPuW2_F-P+SefjoQGUOQ@mail.gmail.com>
 <666bc91c-d5df-4a61-aa3a-30fb1e1c9fcb@aklaver.com>
Content-Language: en-US
In-Reply-To: <666bc91c-d5df-4a61-aa3a-30fb1e1c9fcb@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/fc310113-3c19-4c03-b608-ffec127f1cad%40aklaver.com>
Precedence: bulk

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