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 14:13:18 -0700
Message-ID: <eb726473-ca94-472e-134d-616339bf7576@mailpen.com> (raw)
In-Reply-To: <24285551-fa0c-0b38-5380-a58aca27a8a9@mailpen.com>
References: <24285551-fa0c-0b38-5380-a58aca27a8a9@mailpen.com>
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: <eb726473-ca94-472e-134d-616339bf7576@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