Thread: TIL: In pg_dump, beware the combo of "-Fd" and "-Z"
PostgreSQL 12.1-2.module+el8.1.1+4794+c82b6e09 from the RHEL 8.3
repository. (Installing software outside of the RHEL is Against Policy.)
For a project using 9.6, I have this command backing up a database:
pg_dump -d ${DB} -j ${THREADS} -Fd -Z0 -v -f $DB 2> ${DB}_pgdump.log
So, for the 12 project, I copied it over, removing the "0", since I want
these backups compressed.
pg_dump -d ${DB} -j ${THREADS} -Fd -Z -v -f $DB 2> ${DB}_pgdump.log
Since the -Z default is "6", 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?
--
Angular momentum makes the world go 'round.
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?
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”.
David J.
On 1/19/21 1:55 PM, David G. Johnston wrote:
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.
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.
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.
Angular momentum makes the world go 'round.
On Tue, Jan 19, 2021 at 2:11 PM Ron <ronljohnsonjr@gmail.com> wrote:
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."
This sentence fragment does not exist in isolation, defaults are relative to the specific format and, apparently, only apply when this option is omitted.
What's also interesting is that this section doesn't mention what it does with directory format backups, though it does mention custom format.
Agreed, figured that directory is basically custom so they share the same default unless this parameter overrides it.
The whole thing reads:
"Specify the compression level to use. Zero means no compression. For the custom archive format, this specifies compression of individual table-data segments, and the default is to compress at a moderate level. For plain text output, setting a nonzero compression level causes the entire output file to be compressed, as though it had been fed through gzip; but the default is not to compress. The tar archive format currently does not support compression at all."
Nowhere in that is an omitted value for -Z given a default value (nor is value omission noted as being allowed).
In short, the observed behavior seems fine and the documentation just needs to better match reality.
David J.
> On Jan 19, 2021, at 2:11 PM, Ron <ronljohnsonjr@gmail.com> wrote: > > It doesn't say "6", but every other bit of software I've used defaults to level 6. That's because the underlying library that everybody uses defaults to 6 unless it's specified.
On 20/1/21 5:11 am, Ron wrote:
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.
A quick look at the `pg_dump` source code indicates that:
* `-Z` requires an argument, and
* the argument is converted to an integer with the `atoi()` C function
This means that `-Z` actually took `-v` as its argument, which `atoi()` converts to integer 0. That explains what you saw, and I'm also guessing you did NOT get verbose output from your second command.
Best Regards,
Adrian
On 1/19/21 10:54 PM, Adrian Ho wrote:
That's right.
On 20/1/21 5:11 am, Ron wrote: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.A quick look at the `pg_dump` source code indicates that:
* `-Z` requires an argument, and
* the argument is converted to an integer with the `atoi()` C function
This means that `-Z` actually took `-v` as its argument, which `atoi()` converts to integer 0. That explains what you saw, and I'm also guessing you did NOT get verbose output from your second command.
That's right.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
Ron <ronljohnsonjr@gmail.com> writes:
> On 1/19/21 10:54 PM, Adrian Ho wrote:
>> A quick look at the `pg_dump` source code indicates that:
>>
>> * `-Z` requires an argument, and
>>
>> * the argument is converted to an integer with the `atoi()` C function
>>
>> This means that `-Z` actually took `-v` as its argument, which `atoi()`
>> converts to integer 0. That explains what you saw, and I'm also guessing
>> you did NOT get verbose output from your second command.
> That's right.
Using atoi or siblings without any error checking is not unusual in our
programs (or lots of others, I imagine). I recall some past discussion
of tightening up parsing of integer arguments to complain if they
didn't look like integers. But evidently nobody's pushed that forward.
regards, tom lane