From: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
To: pgsql-admin@postgresql.org
Subject: Re: The trap when using pg_dumpall
Date: Sat, 3 Jul 2021 23:04:26 -0700
Message-ID: <13043c13-e8ec-bc93-7a50-0da4a15b1431@mailpen.com> (raw)
In-Reply-To: <c0ad847f-7afd-da62-3a4d-453ba4345d7b@sqlexec.com>
References: <24285551-fa0c-0b38-5380-a58aca27a8a9@mailpen.com>
<eb726473-ca94-472e-134d-616339bf7576@mailpen.com>
<c0ad847f-7afd-da62-3a4d-453ba4345d7b@sqlexec.com>
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.
>>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: postgresql@mailpen.com
Subject: Re: The trap when using pg_dumpall
In-Reply-To: <13043c13-e8ec-bc93-7a50-0da4a15b1431@mailpen.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox