MIME-Version: 1.0
References: <CAAcmDX_zrfbQ2ixpGH_92SqeM=OJs5wPuW2_F-P+SefjoQGUOQ@mail.gmail.com>
In-Reply-To: <CAAcmDX_zrfbQ2ixpGH_92SqeM=OJs5wPuW2_F-P+SefjoQGUOQ@mail.gmail.com>
From: Justin Swanhart <greenlion@gmail.com>
Date: Sun, 1 Mar 2026 07:22:26 -0500
Message-ID: <CAJM9iN3ejHXEJU8mZ-V+DH_2MQB2XoRrEuAQDVTo4YMYh62+Vg@mail.gmail.com>
Subject: Re: Can "on delete cascade" dependency be used in pgdump or similar ?
To: dfgpostgres <dfgpostgres3@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a06588064bf58601"
Archived-At: <https://www.postgresql.org/message-id/CAJM9iN3ejHXEJU8mZ-V%2BDH_2MQB2XoRrEuAQDVTo4YMYh62%2BVg%40mail.gmail.com>
Precedence: bulk

--000000000000a06588064bf58601
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Feb 27, 2026 at 5:40=E2=80=AFPM 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 u=
se
> 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. B=
ut
> 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

--000000000000a06588064bf58601
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote gmail=
_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Feb 27, 202=
6 at 5:40=E2=80=AFPM dfgpostgres &lt;<a href=3D"mailto:dfgpostgres3@gmail.c=
om">dfgpostgres3@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex"><div dir=3D"ltr">pg=C2=A015.15 on linux<div><br><=
/div><div>I have a DB with a=C2=A0table called &quot;projects&quot; which h=
as a primary key column called &quot;project&quot;.=C2=A0 Other child table=
s are linked=C2=A0in a primary/foreign key relationship to &quot;projects&q=
uot; with &quot;on delete cascade&quot;.=C2=A0 Each of those may have grand=
child tables linked in via other primary/foreign key relationships, all wit=
h the &quot;on delete cascade&quot;.=C2=A0 Etc... .=C2=A0 If I delete proje=
ct &quot;a&quot;, it&#39;ll cascade delete the children,=C2=A0 grandchild, =
etc...=C2=A0 recs.=C2=A0 All traces of project &quot;a&quot; will be gone.<=
/div><div><br></div><div>But I don&#39;t really want to lose the data from =
project &quot;a&quot;, I want to archive it in another DB (same DB server, =
different DB).=C2=A0 The brain-numb method I&#39;ve been using thus far is =
to copy the whole DB via pgdump=C2=A0then use that to create the DB &quot;a=
_archive&quot;.=C2=A0 Then (here&#39;s the wasteful part) basically delete =
all the projects in &quot;a-archive&quot; EXCEPT for project &quot;a&quot;.=
=C2=A0 Then, when that&#39;s done, go to the main DB and delete project &qu=
ot;a&quot;. In effect, I just archived all the data for project &quot;a&quo=
t; and put it in the DB called &quot;a_archive&quot;.while relieving the ma=
in DB of the project &quot;a&quot; data. But what would be really neat is t=
o leverage that cascade on delete stuff to just pgdump=C2=A0project &quot;a=
&quot; and use that to create &quot;a_archive&quot;.=C2=A0=C2=A0</div><div>=
<br></div><div>Can pgdump=C2=A0do something like that ?</div><div>Is there =
a better way to approach this problem of archiving one project (remembering=
 that we do have the &quot;on delete cascade&quot; set up) ?</div><div><br>=
</div><div>Thanks in Advance !</div><div><br></div><div>=C2=A0</div></div><=
/blockquote><div><br></div><div>Hi,</div><div><br></div><div>I think a bett=
er way to approach this problem is using &quot;change data capture&quot; us=
ing the WAL.=C2=A0 Projects such as Debezium [<a href=3D"http://debezium.io=
">debezium.io</a>] will allow you to process the changes from the server an=
d capture the deletions from tables.=C2=A0 You can archive the deleted rows=
 however you like.</div><div><br></div><div>--Justin=C2=A0</div></div></div=
>

--000000000000a06588064bf58601--