Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lzqDh-00040m-Br for pgsql-admin@arkaria.postgresql.org; Sun, 04 Jul 2021 00:42:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lzqDe-0006eI-43 for pgsql-admin@arkaria.postgresql.org; Sun, 04 Jul 2021 00:42:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lzqDd-0006d4-QE for pgsql-admin@lists.postgresql.org; Sun, 04 Jul 2021 00:42:45 +0000 Received: from mout.perfora.net ([74.208.4.197]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lzqDW-0003DJ-Fs for pgsql-admin@postgresql.org; Sun, 04 Jul 2021 00:42:45 +0000 Received: from [192.168.1.165] ([72.205.28.227]) by mrelay.perfora.net (mreueus004 [74.208.5.2]) with ESMTPSA (Nemesis) id 1Mcov8-1lRKPb0kiY-00Zv5w; Sun, 04 Jul 2021 02:42:33 +0200 Subject: Re: The trap when using pg_dumpall To: "Dean Gibson (DB Administrator)" Cc: pgsql-admin@postgresql.org References: <24285551-fa0c-0b38-5380-a58aca27a8a9@mailpen.com> From: MichaelDBA Message-ID: Date: Sat, 3 Jul 2021 20:42:29 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 PostboxApp/7.0.48 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------794531299DB77B139CEEEA75" Content-Language: en-US X-Provags-ID: V03:K1:WJAKhQzH9UxGhAqWnyXZ+Lwy2mROMY6TZ3jQvZFobS4I2fg+IQW PsPEt9ypzlP0ALC/1mHJK7G5Yn3TYjaE7hLhHkHXPw5YZa3tbWNWkUKxSoIPZo50ciJOHw9 kAJlLnGEplClpcxTle6BFXiJ9b9As8VEAilvYq8YxAAqaZE2jHT2xpZeF4fJZ90mhXlcUt/ iZCTZrktw0/eYL+UbLHaQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:UT3a5rA0tLo=:hrKuHAdzPJkyRVvyvQXc3d AVnJnt5IaHS0ozrBMPWaSe+Yzzb8+ZpkduK0P9Nj5vNrRA2Kpo1CBzccbtB3fYxqZ1akT7avi jz3MSHdaeoaZmurgfic/guW1W5H7QhTL/M6iffTxLqdcIX9IYOqhILM8ymlMVUm1gC2g/f9MM jYUzxwPh9J8BNESnMUE4icvzJ7GGO0jrlnGrZTX0kZXzqvRkVfKrQ3LRIs3D7UcVZqL0lGCLN N+SQYEW1yYG+1ftLDxxE2yb3XD6+MFKbGsqhc+O28F9Nh2bghiRxNsFAkFAA4Us9mO7Q0EG8z zzWjAzxnAzyGdBqcMVE0p3zDVRR7JpAhrbr6OsXhSzEulaEzksQSfntvTTyRca+HxwsRcuMEr uh5gzU9wi7NuBDJctFhsSUZCaXfOKEsnZ4F12D+554xQD4EnN3dh16WaJViWRroTpdMNNOoDu /d6ArKsbNwJpQXJSCg0rkF70q5qu5dKfg54n7S5l/V8Q/ODgGnwhc6Q2slRmtdTb8qfGCgnD3 jQGNVATvYb7Po4pwGb1XuUYatDOW4Ayyd0MO5M60pYLTJri9JGFwcOsfeOLq5ZVGq562CnMVX s83mKjVZb+OtQwjXyvCrErxb49V7hxeG0NgTv+clfAnMPDht8YBgyM0lnwVfyJLae86KjSid5 7xNT1bppalrQxQEkUeQie64KFS1MMWQu65eQYtslVkZRbOcKKFvgQH0QSaNo5FarzCGjRDMsT 2CUhK377dbnUPB9t2O1ym3EOKEWYc0bz2ABXGu7XrJKdeH8iyMDZFVs5F5dJcU16ZDhrRQ//Q 5UWPaIROsTb9+7ZBsDbeP40gL4fIRb8beB3MtXeIbBaWYDtv5ErYfhOCNAtUNXM73jiba5D List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------794531299DB77B139CEEEA75 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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. > --------------794531299DB77B139CEEEA75 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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.


--------------794531299DB77B139CEEEA75--