On 2021-07-03 13:18, Dean Gibson (DB Administrator) wrote:
Years ago, I started backing up my table data in individual groups (typically SCHEMAs). More recently, I have also been backing up the entire cluster using pg_dumpall.
Today I thought:
Why not stop the dumps of individual groups? Why not use continue to dump with pg_dumpall, & then use pg_restore to restore all or just portions of the database, as needed?
That sounded good until I did a bit of research. Despite this site https://www.postgresqltutorial.com/postgresql-restore-database/ saying that you could use pg_restore with pg_dumpall, the authoritative documentation says that you can't.
So, assuming that's true, what is the best way to accomplish what I want? I see no effective way of filtering out individual SCHEMAs when restoring from the output of a pg_dumpall. That means:
- Using pg_restore, which has the capability of filtering the restore.
- Using pg_dump with archive output.
Unfortunately, as far as I know, pg_dump doesn't dump roles & possibly other data that I need. I presently have script files that I keep updated (hopefully) in parallel, for the compute manual recreation of the database, but there's nothing like backing up up everything.
So, here's my ultimate question(s):
- Can I do a pg_dumpall to backup just the parts that pg_dump omits, & then be able to do a complete restore by restoring the non-data with psql, & then restoring the data with pg_restore?
- If so, what are the appropriate options to pg_dumpall?
I'm thinking the following will work, but an authoritative answer would be nice:
pg_dumpall -rs (I don't use tablespaces)
Turns out "-rs" is a mistake. "-r" & "-g" override "-s", & "-s" contains everything that "-g" does, & more.