Subject: Re: The trap when using pg_dumpall
From: "Dean Gibson (DB Administrator)" <postgresql@mailpen.com>
To: pgsql-admin@postgresql.org
References: <24285551-fa0c-0b38-5380-a58aca27a8a9@mailpen.com>
Organization: UltiMeth Systems
Message-ID: <eb726473-ca94-472e-134d-616339bf7576@mailpen.com>
Date: Sat, 3 Jul 2021 14:13:18 -0700
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: <24285551-fa0c-0b38-5380-a58aca27a8a9@mailpen.com>
Content-Type: multipart/alternative;
 boundary="------------BCD97BBEDFE06583359A1C1F"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/eb726473-ca94-472e-134d-616339bf7576%40mailpen.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------BCD97BBEDFE06583359A1C1F
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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.


--------------BCD97BBEDFE06583359A1C1F
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 2021-07-03 13:18, Dean Gibson (DB
      Administrator) wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:24285551-fa0c-0b38-5380-a58aca27a8a9@mailpen.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      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.<br>
      <br>
      Today I thought:<br>
      <br>
      <blockquote><i>Why not stop the dumps of individual groups?  Why
          not use continue to dump with pg_dumpall, &amp; then use
          pg_restore to restore <b>all or just portions</b> of the
          database, as needed?</i><br>
      </blockquote>
      <br>
      That sounded good until I did a bit of research.  Despite this
      site <a class="moz-txt-link-freetext"
        href="https://www.postgresqltutorial.com/postgresql-restore-database/"
        moz-do-not-send="true">https://www.postgresqltutorial.com/postgresql-restore-database/</a>
      saying that you could use pg_restore with pg_dumpall, the
      authoritative documentation says that you can't.<br>
      <br>
      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:<br>
      <br>
      <ol>
        <li>Using pg_restore, which has the capability of filtering the
          restore.</li>
        <li>Using pg_dump with archive output.</li>
      </ol>
      <br>
      Unfortunately, as far as I know, pg_dump doesn't dump roles &amp;
      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.<br>
      <br>
      So, here's my ultimate question(s):<br>
      <br>
      <ol>
        <li>Can I do a pg_dumpall to backup just the parts that pg_dump
          omits, &amp; then be able to do a complete restore by
          restoring the non-data with psql, &amp; then restoring the
          data with pg_restore?</li>
        <li>If so, what are the appropriate options to pg_dumpall?</li>
      </ol>
      <br>
      I'm thinking the following will work, but an authoritative answer
      would be nice:<br>
      <br>
      pg_dumpall  -rs  (I don't use tablespaces)<br>
    </blockquote>
    <br>
    Turns out "-rs" is a mistake.  "-r" &amp; "-g" override "-s", &amp;
    "-s" contains everything that "-g" does, &amp; more.<br>
    <br>
  </body>
</html>

--------------BCD97BBEDFE06583359A1C1F--