Horton
Menu

Thread

  1. Can "on delete cascade" dependency be used in pgdump or similar ?

    dfgpostgres <dfgpostgres3@gmail.com> — 2026-02-27T22:39:39Z

    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 ?
Is there a better way to approach this problem of archiving one project
(remembering that we do have the "on delete cascade" set up) ?

Thanks in Advance !

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

    David G. Johnston <david.g.johnston@gmail.com> — 2026-02-27T22:57:54Z

    On Fri, Feb 27, 2026 at 3:40 PM dfgpostgres <dfgpostgres3@gmail.com> wrote:

>  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 ?
>

No, it cannot.

Is there a better way to approach this problem of archiving one project
> (remembering that we do have the "on delete cascade" set up) ?
>
>
Nothing built into core.  The only additional feature you could leverage is
on delete triggers.  But you'd need some custom code for maintenance to
ensure all relevant tables have the cascade delete and on delete triggers.

David J.

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2026-02-27T23:13:52Z

    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:

https://www.postgresql.org/docs/current/app-pgdump.html

--table=pattern

     Dump only tables with names matching pattern. Multiple tables can 
be selected by writing multiple -t switches.

Though it will be up to you to make sure all the related tables are 
present in the dump.

Also depending on how things are setup you might be able to use logical 
replication:

https://www.postgresql.org/docs/current/logical-replication.html

In either case you still have to clean out the original database.


> Is there a better way to approach this problem of archiving one project 
> (remembering that we do have the "on delete cascade" set up) ?
> 
> Thanks in Advance !
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2026-02-27T23:15:45Z

    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

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

    Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-28T22:39:37Z

    On Fri, Feb 27, 2026 at 5:40 PM dfgpostgres <dfgpostgres3@gmail.com> wrote:

> pg 15.15 on linux
>
[snip]

>  Is there a better way to approach this problem of archiving one project
> (remembering that we do have the "on delete cascade" set up) ?
>

Yes.  postgres_fdw is what you want.  It will let you:
INSERT INTO archive.projects SELECT * FROM projects WHERE project='a';
INSERT INTO archive.child1 SELECT * FROM child1 WHERE project='a';
INSERT INTO archive.child2 SELECT * FROM child2 WHERE project='a';
INSERT INTO archive.grandchild1 SELECT * FROM  archive.grandchild1 WHERE ...

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

    Justin Swanhart <greenlion@gmail.com> — 2026-03-01T12:22:26Z

    On Fri, Feb 27, 2026 at 5:40 PM dfgpostgres <dfgpostgres3@gmail.com> 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 ?
> Is there a better way to approach this problem of archiving one project
> (remembering that we do have the "on delete cascade" set up) ?
>
> Thanks in Advance !
>
>
>

Hi,

I think a better way to approach this problem is using "change data
capture" using the WAL.  Projects such as Debezium [debezium.io] will allow
you to process the changes from the server and capture the deletions from
tables.  You can archive the deleted rows however you like.

--Justin