MIME-Version: 1.0
From: dfgpostgres <dfgpostgres3@gmail.com>
Date: Fri, 27 Feb 2026 17:39:39 -0500
Message-ID: <CAAcmDX_zrfbQ2ixpGH_92SqeM=OJs5wPuW2_F-P+SefjoQGUOQ@mail.gmail.com>
Subject: Can "on delete cascade" dependency be used in pgdump or similar ?
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003d57e6064bd5ea49"
Archived-At: <https://www.postgresql.org/message-id/CAAcmDX_zrfbQ2ixpGH_92SqeM%3DOJs5wPuW2_F-P%2BSefjoQGUOQ%40mail.gmail.com>
Precedence: bulk

--0000000000003d57e6064bd5ea49
Content-Type: text/plain; charset="UTF-8"

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 !

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

<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 has a primary key column ca=
lled &quot;project&quot;.=C2=A0 Other child tables are linked=C2=A0in a pri=
mary/foreign key relationship to &quot;projects&quot; with &quot;on delete =
cascade&quot;.=C2=A0 Each of those may have grandchild tables linked in via=
 other primary/foreign key relationships, all with the &quot;on delete casc=
ade&quot;.=C2=A0 Etc... .=C2=A0 If I delete project &quot;a&quot;, it&#39;l=
l cascade delete the children,=C2=A0 grandchild, etc...=C2=A0 recs.=C2=A0 A=
ll traces of project &quot;a&quot; will be gone.</div><div><br></div><div>B=
ut I don&#39;t really want to lose the data from project &quot;a&quot;, I w=
ant to archive it in another DB (same DB server, different DB).=C2=A0 The b=
rain-numb method I&#39;ve been using thus far is to copy the whole DB via p=
gdump=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 &quot;a&quot;. In effect, I j=
ust archived all the data for project &quot;a&quot; and put it in the DB ca=
lled &quot;a_archive&quot;.while relieving the main DB of the project &quot=
;a&quot; data. But what would be really neat is to leverage that cascade on=
 delete stuff to just pgdump=C2=A0project &quot;a&quot; and use that to cre=
ate &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 &quo=
t;on delete cascade&quot; set up) ?</div><div><br></div><div>Thanks in Adva=
nce !</div><div><br></div><div>=C2=A0</div></div>

--0000000000003d57e6064bd5ea49--