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

On Fri, Feb 27, 2026 at 5:40 PM dfgpostgres <dfgpostgres3(at)gmail(dot)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