Re: Force re-compression with lz4

[Michael Paquier]

On Mon, Oct 18, 2021 at 09:57:11AM +0300, Florents Tselai wrote:
> Oh, that’s good to know then. So besides ALTER COMPRESSION for
> future inserts there’s not much one can do for pre-existing values

The posting style of the mailing list is to not top-post, so if you
could avoid breaking the logic of the thread, that would be nice :)

> I think it makes sense to update/ add more info to the docs on this
> as well, since other people in the thread expected this to work that
> way too.

There is some documentation, as changing the compression for an
existing table is part of ALTER TABLE:
https://www.postgresql.org/docs/current/sql-altertable.html

"This does not cause the table to be rewritten, so existing data may
still be compressed with other compression methods. If the table is
restored with pg_restore, then all values are rewritten with the
configured compression method."

> Maybe at some point, even allow an explicit option to be defined during 
> VACUUM ? 

That's a part where we disagreed as it should not be VACUUM's work to
do that.  The option would have a limited impact as it comes to users
that would do a one-time operation most likely part of an upgrade, so
I don't think that this would be adapted to have anyway.
--
Michael


signature.asc
Description: PGP signature

Re: Force re-compression with lz4

[Magnus Hagander]

On Mon, Oct 18, 2021 at 7:18 AM Michael Paquier  wrote:

> On Sun, Oct 17, 2021 at 10:13:48PM +0300, Florents Tselai wrote:
> > I did look into VACUUM(full) for it’s PROCESS_TOAST option which
> > makes sense, but the thing is I already had a cron-ed VACUUM (full)
> > which I ended up disabling a while back; exactly because of the
> > double-space requirement.
>
> Please note that VACUUM FULL does not enforce a recompression on
> existing values.  See commit dbab0c0, that disabled this choice as it
> introduced a noticeable performance penality in some cases when
> looking at the compression type of the vacuumed table attributes:
>

Oh dang, I missed that this was reverted. Thanks for pointing that out!

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: PostgreSQL 14: pg_dump / pg_restore error: could not write to the communication channel: Broken pipe

[Nick Renders]

Thank you for all the feedback and suggestions.

It seems that the "-h localhost" parameter is triggering the issue. If I 
leave it out, pg_restore works without problems with multiple jobs. I 
have also tried specifying the IP number instead of "localhost", but 
that results in the same error.


I see now that our original pg_restore script does not include the -h 
parameter. Somehow, it has snuck in my commands when testing Postgres 
14. That might mean that the same issue exists in previous versions as 
well. I will investigate further.


Nick


On 15 Oct 2021, at 19:08, Tom Lane wrote:


Alvaro Herrera  writes:
Yeah, pg_dump in parallel mode uses a pipe to communicate between 
leader
and workers; the error you see is what happens when a write to the 
pipe

fails.  It sounds to me like something in the operating system is
preventing the pipes from working properly.


BTW, I think a more likely explanation is "one of the pg_dump or
pg_restore worker processes crashed".  Why that should be is still
a mystery though.

regards, tom lane

Re: Force re-compression with lz4

[Mladen Gogala]

On 10/18/21 01:07, Michael Paquier wrote:

CPU-speaking, LZ4 is*much*  faster than pglz when it comes to
compression or decompression with its default options.  The
compression ratio is comparable between both, still LZ4 compresses in
average less than PGLZ.
--
Michael


LZ4 works much better with deduplication tools like Data Domain or Data 
Domain Boost (client side deduplication). With zip or gzip compression, 
deduplication ratios are much lower than with LZ4. Most of the modern 
backup tools (DD, Veeam, Rubrik, Commvault) support deduplication. LZ4 
algorithm uses less CPU than zip, gzip or bzip2 and works much better 
with deduplication algorithms employed by the backup tools. This is 
actually a very big and positive change.


Disclosure:

I used to work for Commvault as a senior PS engineer. Commvault was the 
first tool on the market to combine LZ4 and deduplication.


Regards


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: PostgreSQL 14: pg_dump / pg_restore error: could not write to the communication channel: Broken pipe

[Tom Lane]

"Nick Renders"  writes:
> Thank you for all the feedback and suggestions.
> It seems that the "-h localhost" parameter is triggering the issue. If I 
> leave it out, pg_restore works without problems with multiple jobs. I 
> have also tried specifying the IP number instead of "localhost", but 
> that results in the same error.

Hmm ... do you have your firewall set up in any unusual way?
See System Preferences -> Security & Privacy -> Firewall ->
Firewall Options.  On mine, the only checked box is
"Automatically allow built-in software to receive incoming
connections".  I also have SSH enabled, though that doesn't
seem too relevant here.

regards, tom lane

Debug PostgreSQL logical apply process

[Ramnivas Chaurasia]

Hello,
We have configured "pglogical" in AWS Aurora. But the "subscription"
process keeps going down frequently. We don't get enough log in database
error log to know exactly what's causing the problem (we have tried setting
the "log_min_messages" and "pglogical.conflict_log_level" upto "debug5") .
>From the replication slot, we can get the SQL statement where apply process
breaks, but running that statement manually on the target side works just
fine. Replication process is configured with "postgres" (admin user). Is
there a way we can force the "apply" process to spill more info in the
error log file? Or some other way to troubleshoot?

PostgreSQL Version: 12.6
Pglogical Version: 2.3.2

Thanks in advance!
Ramniwas

Re: Force re-compression with lz4

[Adrian Klaver]

On 10/18/21 06:41, Mladen Gogala wrote:


On 10/18/21 01:07, Michael Paquier wrote:

CPU-speaking, LZ4 is*much*  faster than pglz when it comes to
compression or decompression with its default options.  The
compression ratio is comparable between both, still LZ4 compresses in
average less than PGLZ.
--
Michael


LZ4 works much better with deduplication tools like Data Domain or Data 
Domain Boost (client side deduplication). With zip or gzip compression, 
deduplication ratios are much lower than with LZ4. Most of the modern 
backup tools (DD, Veeam, Rubrik, Commvault) support deduplication. LZ4 
algorithm uses less CPU than zip, gzip or bzip2 and works much better 
with deduplication algorithms employed by the backup tools. This is 
actually a very big and positive change.


Not sure how much this applies to the Postgres usage of lz4. As I 
understand it, this is only used internally for table compression. When 
using pg_dump compression gzip is used. Unless you pipe plain text 
output through some other program.




Disclosure:

I used to work for Commvault as a senior PS engineer. Commvault was the 
first tool on the market to combine LZ4 and deduplication.


Regards





--
Adrian Klaver
adrian.kla...@aklaver.com

Unsynchronized parallel dumps from 13.3 replica produced by pg_dump

[Chris Williams]

Hi,

We have a script that runs a pg_dump off of an RDS PG13.3 replica several
times per day. We then load this dump using pg_restore into another
postgres RDS db in another AWS account, scrub some of the data, and then
take a snapshot of it. We then use these snapshots for testing and staging
servers. This script has been running fine for years without any issues.
However, a little less than a month ago, we changed the pg_dump command in
the script to use the "-j" flag to speed up our dumps. It was my
understanding that starting with postgres v10, this option should now be
supported on replicas. Since we recently upgraded to Postgres 13.3 from
9.6, we thought it would be a good idea to start using this feature to
speed up our pg_dumps. In order to use parallel dumps, we made two changes
to our script: 1) We swapped to this command: pg_dump -Fd -j9 -Z0 -x
-hreplica -Uuser -f dump_dir For reference, the previous command (which
never had any problems) was: pg_dump -Fc -Z0 -x -hreplica -Uuser > dump 2)
We added code to resize our replica db to a larger hardware size before
starting the pg_dump so we can utilize more parallelism. After resizing the
db, the script waits for our replica to go back to the "available" state
before running the pg_dump command. When the pg_dump finishes, we then
resize the db back down to a t3.medium for cost reasons. When we first
rolled this out, the speed improvement was fantastic, it brought our
pg_dump down from 30 minutes to 5. However, within a week or so of rolling
this out, we've noticed that sometimes the dumps produced by our pg_dump
command were not consistent/synchronized. Sometimes when we would restore
the backup using pg_restore to our intermediate db it would throw duplicate
key errors or other types of constraint errors. Sometimes, even when the
pg_restore fully restored without errors, we noticed that some of the
sequences were wrong (we saw some sequences with a value less than the max
id in the column, so new inserts would start failing on the table). The
errors were never consistent either. It appears to me that the dump
produced by our pg_dump is not actually synchronized. I checked the
postgres logs on the server and don't see anything noteworthy. We also are
not getting any errors from our pg_dump command. Everything we are using is
version 13.3 (all of the dbs involved and the server running the script is
using v13.3 of pg_dump/pg_restore commands) I was curious if anyone had any
thoughts on what could be causing this. From my reading of the pg_dump
manual, it seems like it should always be producing a consistent dump, but
please correct me if I'm misunderstanding. I've also opened a ticket with
AWS's support to see if they have any ideas. For now, we've had to go back
to using the single threaded pg_dump (which is disappointing because it's
25 minutes slower). We were really hoping to take advantage of the
parallelism.

Any ideas would be much appreciated.

Thanks,
Chris

Re: Unsynchronized parallel dumps from 13.3 replica produced by pg_dump

[Tom Lane]

Chris Williams  writes:
> We have a script that runs a pg_dump off of an RDS PG13.3 replica several
> times per day. We then load this dump using pg_restore into another
> postgres RDS db in another AWS account, scrub some of the data, and then
> take a snapshot of it.

Hmm ... I'm fairly sure that RDS Postgres is not Postgres at this level
of detail.  The info I've been able to find about their replication
mechanism talks about things like "eventually consistent reads", which
is not something community Postgres deals in.

In particular, what I'd expect from the community code is that a replica
could see a sequence as being *ahead* of the value that you might expect
from looking at related tables; but never behind.  (Also, that statement
is true regardless of whether you are doing parallel dump.)  And
non-sequence tables should always be consistent, period.

So I'm suspicious that this is an RDS-specific effect, and thus that
you should consult Amazon support first.  If they say "no, it's Postgres
all the way down", then we need to look closer.

regards, tom lane

Re: Unsynchronized parallel dumps from 13.3 replica produced by pg_dump

[Chris Williams]

Thanks Tom.  It's a strange one for sure.  Hopefully AWS support will shed
some light on it. I will clarify too that this is the regular RDS Postgres
version and not their other Aurora Postgres service.  I suspect the Aurora
Postgres probably differs from the community version by quite a bit, but
I'm unsure how much their regular Postgres offering differs, if at all.

Thanks,
Chris

On Mon, Oct 18, 2021 at 8:05 PM Tom Lane  wrote:

> Chris Williams  writes:
> > We have a script that runs a pg_dump off of an RDS PG13.3 replica several
> > times per day. We then load this dump using pg_restore into another
> > postgres RDS db in another AWS account, scrub some of the data, and then
> > take a snapshot of it.
>
> Hmm ... I'm fairly sure that RDS Postgres is not Postgres at this level
> of detail.  The info I've been able to find about their replication
> mechanism talks about things like "eventually consistent reads", which
> is not something community Postgres deals in.
>
> In particular, what I'd expect from the community code is that a replica
> could see a sequence as being *ahead* of the value that you might expect
> from looking at related tables; but never behind.  (Also, that statement
> is true regardless of whether you are doing parallel dump.)  And
> non-sequence tables should always be consistent, period.
>
> So I'm suspicious that this is an RDS-specific effect, and thus that
> you should consult Amazon support first.  If they say "no, it's Postgres
> all the way down", then we need to look closer.
>
> regards, tom lane
>

Where is the tsrange() function documented?

[Bryn Llewellyn]

I found my way to this by guesswork and good luck. (I happen to be using PG 
Version 13.5. But I don't suppose that this matters.)

Doing "\df tsrange()" gives this:

   Schema   |  Name   | Result data type |  Argument data 
types   | Type 
+-+--++--
 pg_catalog | tsrange | tsrange  | timestamp without time zone, 
timestamp without time zone   | func
 pg_catalog | tsrange | tsrange  | timestamp without time zone, 
timestamp without time zone, text | func

And doing this:

\x on
with c as (
  select
'2000-01-15'::timestamp as t1,
'2000-05-15'::timestamp as t2)
select
  tsrange(t1, t2, '[]') as r1,
  tsrange(t1, t2, '[)') as r2,
  tsrange(t1, t2, '(]') as r3,
  tsrange(t1, t2, '()') as r4
from c;
\x off

gives this:

r1 | ["2000-01-15 00:00:00","2000-05-15 00:00:00"]
r2 | ["2000-01-15 00:00:00","2000-05-15 00:00:00")
r3 | ("2000-01-15 00:00:00","2000-05-15 00:00:00"]
r4 | ("2000-01-15 00:00:00","2000-05-15 00:00:00")

It's exactly what I was looking for. Now I want to refer colleagues to the PG 
doc on the tsrange() function.

But I can't formulate a search that finds it using the doc site's intrinsic 
search.

And I can't even find a single example of it on any site using Google.

Where is it?

JSON query

[Scott Ribe]

What's a good way to query jsonb column for

"no keys other than those in this list of keys"

in other words

"containing only keys from this list of keys"

--
Scott Ribe
scott_r...@elevated-dev.com
https://www.linkedin.com/in/scottribe/

Re: Unsynchronized parallel dumps from 13.3 replica produced by pg_dump

[Tom Lane]

Chris Williams  writes:
> Thanks Tom.  It's a strange one for sure.  Hopefully AWS support will shed
> some light on it. I will clarify too that this is the regular RDS Postgres
> version and not their other Aurora Postgres service.  I suspect the Aurora
> Postgres probably differs from the community version by quite a bit, but
> I'm unsure how much their regular Postgres offering differs, if at all.

Yeah, Aurora is definitely a different beast at the storage level.
I'm not entirely sure about RDS.

regards, tom lane

Re: Where is the tsrange() function documented?

[David G. Johnston]

On Monday, October 18, 2021, Bryn Llewellyn  wrote:
>
>
> Where is it?
>

TOC - Data Types - Range Types:

 https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN

David J.

Re: Force re-compression with lz4

[Michael Paquier]

On Mon, Oct 18, 2021 at 08:01:04AM -0700, Adrian Klaver wrote:
> Not sure how much this applies to the Postgres usage of lz4. As I understand
> it, this is only used internally for table compression. When using pg_dump
> compression gzip is used. Unless you pipe plain text output through some
> other program.

More precisely, LZ4 applies to the compression of toastable values in
14~.  In 15~, we can already use it for WAL and the compression of
full-page writes.

It is worth noting that there are extra patches floating around to add
more LZ4 pluggability to pg_dump (I think this has not been published
yet), pg_receivewal (published) and base backups through the
replication protocol (published).  I have seen rather good numbers
when it came to WAL, FWIW.  Even if the compression ratio was a bit
less than pglz, it was much faster.
--
Michael


signature.asc
Description: PGP signature

Re: JSON query

[David G. Johnston]

On Monday, October 18, 2021, Scott Ribe  wrote:

>
> "containing only keys from this list of keys"
>
>
(jsonb - text[]) = ‘{}’::jsonb …?

Combine with (jsonb ?& text[]) if all tested keys need to be present as
well.

David J.

Re: Where is the tsrange() function documented?

[Adrian Klaver]

On 10/18/21 20:44, Bryn Llewellyn wrote:
I found my way to this by guesswork and good luck. (I happen to be using 
PG Version 13.5. But I don't suppose that this matters.)




But I can't formulate a search that finds it using the doc site's 
intrinsic search.


Where exactly did you search?

When I tried in either the overall site or doc site search with 
'tsrange()' it offered :


https://www.postgresql.org/docs/14/rangetypes.html

as the first choice. This encompasses the link David G. Johnston posted.



And I can't even find a single example of it on any site using Google.

Where is it?



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: JSON query

[Scott Ribe]

> On Oct 18, 2021, at 10:02 PM, David G. Johnston  
> wrote:
> 
> (jsonb - text[]) = ‘{}’::jsonb …?

Aha, thank you!

Re: Where is the tsrange() function documented?

[Bryn Llewellyn]

> David Johnston wrote:
> 
>> Bryn wrote:
>> 
>> Where is it?
> 
> TOC - Data Types - Range Types:
> 
> https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN 
> 

Thanks for the instant response, David. That's exactly what I wanted—and in 
particular "8.17.6. Constructing Ranges and Multiranges":

https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTRUCT

Re: Where is the tsrange() function documented?

[Bryn Llewellyn]

> Adrian wrote:
> 
>> Bryn  wrote:
>> 
>> I found my way to this by guesswork and good luck. (I happen to be using PG 
>> Version 13.5. But I don't suppose that this matters.)… But I can't formulate 
>> a search that finds it using the doc site's intrinsic search.
> 
> Where exactly did you search? When I tried in either the overall site or doc 
> site search with 'tsrange()' it offered :
> 
> https://www.google.com/url?q=https://www.postgresql.org/docs/14/rangetypes.html&source=gmail-imap&ust=163522233600&usg=AOvVaw0uLLwEWap7ttpvbTC1bEb8
> 
> as the first choice. This encompasses the link David G. Johnston posted.

Yes, I did find that page, too. I see now that I should have search for 
"construct" or simply noticed "Constructing Ranges" in the page's ToC.

But I got to where I was with "\df tsrange" because there's usually a 
system-defined function for every typecast. When I saw there there was a 
three-argument overload I guessed that the text arg might accept '[)' and so 
on. So I tried to find the doc for the tsrange() function.

I could've searched in the page for '[)' — but I'm afraid that it didn't occur 
to me.

Or I might have read enough to find this

… constructor function with the same name as the range type… the three-argument 
form constructs a range with bounds of the form specified by the third 
argument. The third argument must be one of the strings “()”, “(]”, “[)”, or 
“[]”.

But I searched only for "tsrange". This gave me no relevant hit. And that's 
when I lost patience.

By the way, I was surprised when I tried this:

with c as (
  select
'2000-01-01'::timestamp as t1,
'2000-01-10'::timestamp as t2,
'2000-01-20'::timestamp as t3)
select
  ( tsrange(t1, t3, '[)') && tsrange(t2, t2, '[)') )::text as "range result",
  ((t1, t3)overlaps (t2, t2)   )::text as "overlaps result"
from c;

and got this:

 range result | overlaps result 
--+-
 false| true

I can't find anything, neither on the page in question here on Range Types nor 
in the doc on the overlaps operator, about the semantics for when a duration 
collapses to an instant. Am I missing this too?

It seems to me that overlaps" does it right (also in all the other scenarios 
like instant at the start of a duration, instant at the end of a duration, and 
two coinciding instants. And it seems, therefore, that && does it wrong. (It 
always gives false when at least one of the durations is an instant.)

What's the rationale for this asymmetry in semantics?

Where is the tsrange() function documented?

[David G. Johnston]

On Monday, October 18, 2021, Bryn Llewellyn  wrote:

>
> I can't find anything, neither on the page in question here on Range Types
> nor in the doc on the overlaps operator, about the semantics for when a
> duration collapses to an instant. Am I missing this too?
>
>
Same page:

https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-IO
 (Examples)

- includes no points (and will be normalized to 'empty'
SELECT '[4,4)'::int4range;

The asymmetry stems from the definition of the same-valued half-open
interval being normalized to an instant by the sql standard when dealing
with overlaps but normalizing to empty for our range specification. Neither
choice seems wrong on its face. It is what it is at this point - I defer to
the commit message and mailing list history for any relevant “why”
discussion.

David J.