I NEVER use pg_dumpall for my databases. No
way I want to dump everything in text format. For big databases that
is excruciatingly slow.
I use pg_dumpall -g to get the
globals and then use the -Fd directive
of pg_dump/pg_restore to take advantage of parallel processing of dumps
and loads for the databases. This is SOOOOoooo much faster and takes
up much less disk space.
When clusters were relatively small a couple decades ago, I reckon
pg_dumpall sufficed, but not today in my real-world experiences.
Regards,
Michael Vitale
Dean Gibson (DB Administrator) wrote on 7/3/2021 5:13 PM:
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.
|
