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 1l1yI7-0005Z6-Ad for pgsql-admin@arkaria.postgresql.org; Tue, 19 Jan 2021 21:11:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l1yI6-0002kO-43 for pgsql-admin@arkaria.postgresql.org; Tue, 19 Jan 2021 21:11:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l1yI5-0002kG-MI for pgsql-admin@lists.postgresql.org; Tue, 19 Jan 2021 21:11:53 +0000 Received: from mail-ot1-x333.google.com ([2607:f8b0:4864:20::333]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1l1yI1-0005vs-3t for pgsql-admin@postgresql.org; Tue, 19 Jan 2021 21:11:52 +0000 Received: by mail-ot1-x333.google.com with SMTP id 36so9707142otp.2 for ; Tue, 19 Jan 2021 13:11:48 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=liyPKWwPzfeEeFSs++naDAXuRtzMkSEUwKXcVW31UEU=; b=AZYF0yzmdcMhHNIr7uSWUGryhc9ZNn1xtazXIXzj9Ql/CLrJMeat+XHhwuJhdiOFa9 +faFwcNjHQIUB5BrJFl0wBMpJF5glcCvsK6lzntYjQJSVHnDzvnXKNA4s/meyxXHAVNU ov69s3Qu4QfpJJQyJqVr4IkFjMyK0KuC6rBbNorkd0eeYXvuX+4RcWB1Ls4dr1x8E089 Kh7LLHgdvYmjAhTKjSpe8ypM7PuU9Dty1E7x1XQj8N3j4ht5U9kXH+1ZOZyX9UI/iE3L 3UK/2JsyDRICWkxSOXgMNCgZC4FFYZOjFiFUBWRyUGJgv0M9+al9ISgeKDfMameRXGLn PzFQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=liyPKWwPzfeEeFSs++naDAXuRtzMkSEUwKXcVW31UEU=; b=gyV5dNUDHjmoWJF87UM111SxjgaB8lZ6yKtZBG2x1Oiih9HgrhHiG1ui51k8i6oe7M 8O5pwrg6Cypn8byPmPaCAacJ6fUHRsPjrEmxd1Oj7H9Rm98iN4UfZ3L6iLzDOmGAZHlH sGkvw1HHz/EDdJXJvwKfHrJ0Fc2vY1PkQcWUi+jB7gbQb95HGPYsIk+geAXZ2Rf3Eibs SAJcf2K0vXy5dxusBsUbZadkY4kqvt6LHXr6HPxnKYz2oNfM/ld+39tR8kghbPLhYOG8 uW3dmHikSWCHEEKR/TiDk1NL05fSacvwaoYRf46zxcQluMXTaBgBPv03M99AW3cpgvjh oNAQ== X-Gm-Message-State: AOAM530JJ9sX5i25FqJe94YBPxVFfgY13zCnO6nmqBhT+pAZsL6jYgqR aylfIpOYuHiHqyWT2UvT272w5Vfl+jU1ew== X-Google-Smtp-Source: ABdhPJxDwP5nqPZqXur+bEdCeS0mtqVTFAAzr9osfVXhz79mNvrQRRSvbh7wqP7aRqvalcW1NiFvIg== X-Received: by 2002:a05:6830:15d6:: with SMTP id j22mr4794378otr.34.1611090708049; Tue, 19 Jan 2021 13:11:48 -0800 (PST) Received: from [192.168.88.10] (ip174-67-141-204.no.no.cox.net. [174.67.141.204]) by smtp.googlemail.com with ESMTPSA id s204sm4056825oib.42.2021.01.19.13.11.47 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 19 Jan 2021 13:11:47 -0800 (PST) Subject: Re: TIL: In pg_dump, beware the combo of "-Fd" and "-Z" To: pgsql-admin References: <1ad81a44-b39e-5854-ba82-0bd1b61bef25@gmail.com> From: Ron Message-ID: <267c0d6d-6a02-4c3a-b928-75472ce66de0@gmail.com> Date: Tue, 19 Jan 2021 15:11:46 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------FD2B1A47DF042B1455D7F6B3" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------FD2B1A47DF042B1455D7F6B3 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 1/19/21 1:55 PM, David G. Johnston wrote: > On Tuesday, January 19, 2021, Ron > wrote: > > > For a project using 9.6, I have this command backing up a database: > So, for the 12 project, I copied it over, removing the "0", since I > want these backups compressed. > > > The versions in use doesn’t seem pertinent to the observation - this > behavior hasn’t changed (at least in the docs). > > > Since the -Z default is "6" > > > It is?  Where do you see this? https://www.postgresql.org/docs/12/app-pgdump.html "-Z 0..9" "the default is to compress at a moderate level." It doesn't say "6", but every other bit of software I've used defaults to level 6. What's also interesting is that this section doesn't mention what it does with directory format backups, though it does mention custom format. "For the custom archive format, this specifies compression of individual table-data segments, and the default is to compress at a moderate level." Since -Fc and -Fd are so similar, I worked off the notion that -Z works similarly for both. > >  I assumed that would compress the backup.  It didn't, acting instead > like "-Z0".  Removing "-Z" made it compress. > > This surprised me, and is very counter-intuitive.  A bug? > > > That we accept a -Z parameter without a number without complaint would be > a bug given the documentation doesn’t indicate the number as being > optional.  That “-Z” and “-Z 0” are equivalent makes sense if one allows “-Z”. As mentioned above, -Z6 is the default on all the other software I use, so worked of the notion that Postgres uses that default, too. -- Angular momentum makes the world go 'round. --------------FD2B1A47DF042B1455D7F6B3 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 1/19/21 1:55 PM, David G. Johnston wrote:
On Tuesday, January 19, 2021, Ron <ronljohnsonjr@gmail.com> wrote:

For a project using 9.6, I have this command backing up a database:
So, for the 12 project, I copied it over, removing the "0", since I want these backups compressed.

The versions in use doesn’t seem pertinent to the observation - this behavior hasn’t changed (at least in the docs).
 

Since the -Z default is "6"

It is?  Where do you see this?

https://www.postgresql.org/docs/12/app-pgdump.html

"-Z 0..9"
"the default is to compress at a moderate level."

It doesn't say "6", but every other bit of software I've used defaults to level 6.

What's also interesting is that this section doesn't mention what it does with directory format backups, though it does mention custom format.

"For the custom archive format, this specifies compression of individual table-data segments, and the default is to compress at a moderate level."

Since -Fc and -Fd are so similar, I worked off the notion that -Z works similarly for both.

 

 I assumed that would compress the backup.  It didn't, acting instead like "-Z0".  Removing "-Z" made it compress.

This surprised me, and is very counter-intuitive.  A bug?


That we accept a -Z parameter without a number without complaint would be a bug given the documentation doesn’t indicate the number as being optional.  That “-Z” and “-Z 0” are equivalent makes sense if one allows “-Z”.

As mentioned above, -Z6 is the default on all the other software I use, so worked of the notion that Postgres uses that default, too.

--
Angular momentum makes the world go 'round.
--------------FD2B1A47DF042B1455D7F6B3--