To: pgsql-admin@postgresql.org
From: "Dean Gibson (DB Administrator)" <postgresql@mailpen.com>
Subject: The trap when using pg_dumpall
Organization: UltiMeth Systems
Message-ID: <24285551-fa0c-0b38-5380-a58aca27a8a9@mailpen.com>
Date: Sat, 3 Jul 2021 13:18:57 -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
Content-Type: multipart/alternative;
 boundary="------------C545AFEB0B26780D9042DFCB"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/24285551-fa0c-0b38-5380-a58aca27a8a9%40mailpen.com>
Precedence: bulk

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

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)


--------------C545AFEB0B26780D9042DFCB
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>
    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/">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>
    <br>
    <br>
    <br>
    <br>
    <br>
  </body>
</html>

--------------C545AFEB0B26780D9042DFCB--