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 ?
No, it cannot.
Is there a better way to approach this problem of archiving one project (remembering that we do have the "on delete cascade" set up) ?
Nothing built into core. The only additional feature you could leverage is on delete triggers. But you'd need some custom code for maintenance to ensure all relevant tables have the cascade delete and on delete triggers.
