From: hubert depesz lubaczewski <depesz@depesz.com>
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: The trap when using pg_dumpall
Date: Sun, 4 Jul 2021 10:35:27 +0200
Message-ID: <20210704083527.GA20249@depesz.com> (raw)
In-Reply-To: <13043c13-e8ec-bc93-7a50-0da4a15b1431@mailpen.com>
References: <24285551-fa0c-0b38-5380-a58aca27a8a9@mailpen.com>
<eb726473-ca94-472e-134d-616339bf7576@mailpen.com>
<c0ad847f-7afd-da62-3a4d-453ba4345d7b@sqlexec.com>
<13043c13-e8ec-bc93-7a50-0da4a15b1431@mailpen.com>
On Sat, Jul 03, 2021 at 11:04:26PM -0700, Dean Gibson (DB Administrator) wrote:
> 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.
Triggers are inside database, so normal pg_dump handles them.
Generally, I stand firmly in position that one should never use
pg_dumpall.
Size of dump is one thing, but inability to sanely filter what you will
load is deal breaker.
Plus - with modern pg_dump and -Fd, both dumping time, and loading time
can be significantly reduced thanks to parallelism.
Wrote about it:
https://www.depesz.com/2019/12/10/how-to-effectively-dump-postgresql-databases/
depesz
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: depesz@depesz.com, postgresql@mailpen.com
Subject: Re: The trap when using pg_dumpall
In-Reply-To: <20210704083527.GA20249@depesz.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