public inbox for [email protected]  

help / color / mirror / Atom feed

From: Justin Swanhart <[email protected]>
To: dfgpostgres <[email protected]>
Cc: [email protected]
Subject: Re: Can "on delete cascade" dependency be used in pgdump or similar ?
Date: Sun, 1 Mar 2026 07:22:26 -0500
Message-ID: <CAJM9iN3ejHXEJU8mZ-V+DH_2MQB2XoRrEuAQDVTo4YMYh62+Vg@mail.gmail.com> (raw)
In-Reply-To: <CAAcmDX_zrfbQ2ixpGH_92SqeM=OJs5wPuW2_F-P+SefjoQGUOQ@mail.gmail.com>
References: <CAAcmDX_zrfbQ2ixpGH_92SqeM=OJs5wPuW2_F-P+SefjoQGUOQ@mail.gmail.com>

On Fri, Feb 27, 2026 at 5:40 PM dfgpostgres <[email protected]> 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

view thread (4+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Can "on delete cascade" dependency be used in pgdump or similar ?
  In-Reply-To: <CAJM9iN3ejHXEJU8mZ-V+DH_2MQB2XoRrEuAQDVTo4YMYh62+Vg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox