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
