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
[email protected]
