public inbox for [email protected]
help / color / mirror / Atom feedDoes pg_dump custom format only compress the table data?
7+ messages / 3 participants
[nested] [flat]
* Does pg_dump custom format only compress the table data?
@ 2026-02-02 15:55 Ron Johnson <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2026-02-02 15:55 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
PG 17.7
Zlvl="--compress=zstd:long"
db=mumble
pg_dump -Fc $Zlvl -v -d${db} -f ${db}.dump 2> ${db}.log
Out of curiosity, I opened that dump file in vim, and saw not only DDL
strings but cleartext table data.
Why isn't it compressed?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Does pg_dump custom format only compress the table data?
@ 2026-02-03 06:06 Laurenz Albe <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2026-02-03 06:06 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; Pgsql-admin <[email protected]>
On Mon, 2026-02-02 at 10:55 -0500, Ron Johnson wrote:
> Out of curiosity, I opened that dump file in vim, and saw not only DDL strings but cleartext table data.
>
> Why isn't it compressed?
Are your DDL statement large enough that you would expect that compressing
them would offer any real advantage?
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Does pg_dump custom format only compress the table data?
@ 2026-02-03 07:51 Holger Jakobs <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Holger Jakobs @ 2026-02-03 07:51 UTC (permalink / raw)
To: [email protected]
Am 03.02.26 um 07:06 schrieb Laurenz Albe:
> On Mon, 2026-02-02 at 10:55 -0500, Ron Johnson wrote:
>> Out of curiosity, I opened that dump file in vim, and saw not only DDL strings but cleartext table data.
>>
>> Why isn't it compressed?
> Are your DDL statement large enough that you would expect that compressing
> them would offer any real advantage?
>
> Yours,
> Laurenz Albe
That wasn't the question. It was about why table DATA aren't compressed,
but in clear text.
Yours
Holger
--
Holger Jakobs, Bergisch Gladbach
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Does pg_dump custom format only compress the table data?
@ 2026-02-03 08:13 Ron Johnson <[email protected]>
parent: Holger Jakobs <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Ron Johnson @ 2026-02-03 08:13 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Tue, Feb 3, 2026 at 2:51 AM Holger Jakobs <[email protected]> wrote:
> Am 03.02.26 um 07:06 schrieb Laurenz Albe:
> > On Mon, 2026-02-02 at 10:55 -0500, Ron Johnson wrote:
> >> Out of curiosity, I opened that dump file in vim, and saw not only DDL
> strings but cleartext table data.
> >>
> >> Why isn't it compressed?
> > Are your DDL statement large enough that you would expect that
> compressing
> > them would offer any real advantage?
> >
> > Yours,
> > Laurenz Albe
>
> That wasn't the question. It was about why table DATA aren't compressed,
> but in clear text.
>
Exactly. Does the compression algorithm have some sort of threshold under
which it ignores the --compress switch?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Does pg_dump custom format only compress the table data?
@ 2026-02-03 12:47 Laurenz Albe <[email protected]>
parent: Holger Jakobs <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2026-02-03 12:47 UTC (permalink / raw)
To: Holger Jakobs <[email protected]>; [email protected]
On Tue, 2026-02-03 at 08:51 +0100, Holger Jakobs wrote:
> Am 03.02.26 um 07:06 schrieb Laurenz Albe:
> > On Mon, 2026-02-02 at 10:55 -0500, Ron Johnson wrote:
> > > Out of curiosity, I opened that dump file in vim, and saw not only DDL strings but cleartext table data.
> > >
> > > Why isn't it compressed?
> > Are your DDL statement large enough that you would expect that compressing
> > them would offer any real advantage?
>
> That wasn't the question. It was about why table DATA aren't compressed,
> but in clear text.
Ah, I see.
By default, you wouldn't see any table data, because they get compressed.
If a custom format dump is not compressed, the reason could be:
- PostgreSQL was built without support for compression (--without-zlib)
- pg_dump was called with --compress=0
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Does pg_dump custom format only compress the table data?
@ 2026-02-03 16:16 Ron Johnson <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2026-02-03 16:16 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Holger Jakobs <[email protected]>; [email protected]
On Tue, Feb 3, 2026 at 7:48 AM Laurenz Albe <[email protected]>
wrote:
> On Tue, 2026-02-03 at 08:51 +0100, Holger Jakobs wrote:
> > Am 03.02.26 um 07:06 schrieb Laurenz Albe:
> > > On Mon, 2026-02-02 at 10:55 -0500, Ron Johnson wrote:
> > > > Out of curiosity, I opened that dump file in vim, and saw not only
> DDL strings but cleartext table data.
> > > >
> > > > Why isn't it compressed?
> > > Are your DDL statement large enough that you would expect that
> compressing
> > > them would offer any real advantage?
> >
> > That wasn't the question. It was about why table DATA aren't compressed,
> > but in clear text.
>
> Ah, I see.
>
> By default, you wouldn't see any table data, because they get compressed.
>
> If a custom format dump is not compressed, the reason could be:
>
> - PostgreSQL was built without support for compression (--without-zlib)
>
> - pg_dump was called with --compress=0
>
I always install RPMs from postgresql.org, and in this case specified
--compress=zstd:long.
$ psql -V
psql (PostgreSQL) 17.7
dba=# insert into do_not_delete (node_name) values ('foo');
INSERT 0 1
dba=# insert into do_not_delete (node_name) values ('bar');
INSERT 0 1
dba=# insert into do_not_delete (node_name) values ('blarge');
INSERT 0 1
dba=#
$ pg_dump -V
pg_dump (PostgreSQL) 17.7
db=dba
$ pg_dump -Fc --compress=zstd:long -v -d${db} -f ${db}.dump 2> ${db}.log
$ dir ${db}.dump
-rw-r--r-- 1 postgres postgres 2205102870 2026-02-03 11:07:34 dba.dump
$ strings ${db}.dump | grep -E 'foo|bar|blarge'
foo \N
bar \N
blarge \N
\Afoo
barYD
[snip]
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Does pg_dump custom format only compress the table data?
@ 2026-02-03 16:33 Ron Johnson <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Ron Johnson @ 2026-02-03 16:33 UTC (permalink / raw)
To: pgsql-admin
On Tue, Feb 3, 2026 at 11:16 AM Ron Johnson <[email protected]> wrote:
> On Tue, Feb 3, 2026 at 7:48 AM Laurenz Albe <[email protected]>
> wrote:
>
>> On Tue, 2026-02-03 at 08:51 +0100, Holger Jakobs wrote:
>> > Am 03.02.26 um 07:06 schrieb Laurenz Albe:
>> > > On Mon, 2026-02-02 at 10:55 -0500, Ron Johnson wrote:
>> > > > Out of curiosity, I opened that dump file in vim, and saw not only
>> DDL strings but cleartext table data.
>> > > >
>> > > > Why isn't it compressed?
>> > > Are your DDL statement large enough that you would expect that
>> compressing
>> > > them would offer any real advantage?
>> >
>> > That wasn't the question. It was about why table DATA aren't
>> compressed,
>> > but in clear text.
>>
>> Ah, I see.
>>
>> By default, you wouldn't see any table data, because they get compressed.
>>
>> If a custom format dump is not compressed, the reason could be:
>>
>> - PostgreSQL was built without support for compression (--without-zlib)
>>
>> - pg_dump was called with --compress=0
>>
>
> I always install RPMs from postgresql.org, and in this case specified
> --compress=zstd:long.
>
> $ psql -V
> psql (PostgreSQL) 17.7
>
> dba=# insert into do_not_delete (node_name) values ('foo');
> INSERT 0 1
> dba=# insert into do_not_delete (node_name) values ('bar');
> INSERT 0 1
> dba=# insert into do_not_delete (node_name) values ('blarge');
> INSERT 0 1
> dba=#
>
> $ pg_dump -V
> pg_dump (PostgreSQL) 17.7
>
> db=dba
> $ pg_dump -Fc --compress=zstd:long -v -d${db} -f ${db}.dump 2> ${db}.log
> $ dir ${db}.dump
> -rw-r--r-- 1 postgres postgres 2205102870 2026-02-03 11:07:34 dba.dump
>
> $ strings ${db}.dump | grep -E 'foo|bar|blarge'
> foo \N
> bar \N
> blarge \N
> \Afoo
> barYD
> [snip]
>
I tested more. The default gzip compression does in fact compress
everything. This must be a quirk of zstd.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-02-03 16:33 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-02 15:55 Does pg_dump custom format only compress the table data? Ron Johnson <[email protected]>
2026-02-03 06:06 ` Laurenz Albe <[email protected]>
2026-02-03 07:51 ` Holger Jakobs <[email protected]>
2026-02-03 08:13 ` Ron Johnson <[email protected]>
2026-02-03 12:47 ` Laurenz Albe <[email protected]>
2026-02-03 16:16 ` Ron Johnson <[email protected]>
2026-02-03 16:33 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox