The trap when using pg_dumpall

From: "Dean Gibson (DB Administrator)" <postgresql(at)mailpen(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: The trap when using pg_dumpall
Date: 2021-07-03 20:18:57
Message-ID: 24285551-fa0c-0b38-5380-a58aca27a8a9@mailpen.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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)

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Dean Gibson (DB Administrator) 2021-07-03 21:13:18 Re: The trap when using pg_dumpall
Previous Message Ron Watkins 2021-07-02 18:46:19 Loading from Azure DataLake?