pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedThe trap when using pg_dumpall
6+ messages / 4 participants
[nested] [flat]
* The trap when using pg_dumpall
@ 2021-07-03 20:18 Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-07-03 20:18 UTC (permalink / raw)
To: pgsql-admin
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: The trap when using pg_dumpall
@ 2021-07-03 21:13 Dean Gibson (DB Administrator) <postgresql@mailpen.com>
parent: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-07-03 21:13 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: The trap when using pg_dumpall
@ 2021-07-04 00:42 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 1 reply; 6+ messages in thread
From: MichaelDBA @ 2021-07-04 00:42 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; +Cc: pgsql-admin
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.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: The trap when using pg_dumpall
@ 2021-07-04 06:04 Dean Gibson (DB Administrator) <postgresql@mailpen.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 2 replies; 6+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-07-04 06:04 UTC (permalink / raw)
To: pgsql-admin
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.
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: The trap when using pg_dumpall
@ 2021-07-04 08:35 hubert depesz lubaczewski <depesz@depesz.com>
parent: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
1 sibling, 0 replies; 6+ messages in thread
From: hubert depesz lubaczewski @ 2021-07-04 08:35 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: The trap when using pg_dumpall
@ 2021-07-04 21:32 Rui DeSousa <rui@crazybean.net>
parent: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
1 sibling, 0 replies; 6+ messages in thread
From: Rui DeSousa @ 2021-07-04 21:32 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; +Cc: pgsql-admin
> On Jul 4, 2021, at 2:04 AM, Dean Gibson (DB Administrator) <postgresql@mailpen.com> wrote:
>
> Well, I never store the
Well, as long as we’re stating I never’s — let me state mine; I never consider a logical backup (pg_dump) a proper backup. They have their use cases and are a very useful tool; however, I would considered a production database without a physical backup as not being backed up. Logical backups don’t allow for PITR recovery and are very slow in comparison. I only use logical backups for migrations, etc. - more as a tool rather than a backup strategy.
A good practice would be to have a separate instance that can instantiate a point in time recovery. This would allow for the reviewing/exporting required data needed to resolve a given issue.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-07-04 21:32 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-03 20:18 The trap when using pg_dumpall Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-07-03 21:13 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-07-04 00:42 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-07-04 06:04 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-07-04 08:35 ` hubert depesz lubaczewski <depesz@depesz.com>
2021-07-04 21:32 ` Rui DeSousa <rui@crazybean.net>
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