Re: The trap when using pg_dumpall

Well, I never store the output of pg_dumpall directly; I pipe it through
gzip, & the resultant size differs by about 1% from the size from
pg_dump in custom-archive format.

I also found that pg_dumpall -g doesn't get the triggers; pg_dumpall -s
does.  I don't know if pg-dump gets the triggers.

On 2021-07-03 17:42, MichaelDBA wrote:
> 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:
>>>
>>> 1. Using pg_restore, which has the capability of filtering the restore.
>>> 2. 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):
>>>
>>> 1. 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?
>>> 2. 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.
>>
>