Re: pgBackRest for a 50 TB database

[KK CHN]

Greetings,
Happy to hear you successfully performed pgBackRest for a 50TB DB. Out of
curiosity I would like to know your infrastructure settings.

1. The  connectivity protocoal and bandwidth you used for your backend
storage ?  Is it iSCSI, FC FCoE or GbE ? what's the exact reason for
the 26 Hours it took in the best case ? What factors may reduce 26 Hours to
much less time say 10 Hour or so for a 50 TB DB to  backup destination ??
What to  fine tune or deploy  for a better performance?

2. It has been said that  you are running the DB on a 2 slot 18 core
processor = 36 Physical cores ..  Is it a dedicated Server H/W entirely
dedicated for a 50 TB database alone ?
Why I asked, nowadays mostly we may run the DB servers on VMs in
virtualized environments..  So I would like to know  all 36 Physical cores
and associated RAM are all utilized by your 50 TB Database server ? or any
vacant CPU cores/Free RAM on those server machines?

3.  What kind of connectivity/bandwidth between DB server and Storage
backend you established ( I Want to know the server NIC card details,
Connectivity Channel protocol/bandwidth and Connecting Switch spec from DB
Server to Storage backend( NAS in this case right ?)

Could you share the recommendations / details as in your case , Becoz I'm
also in need to perform such a pgBackRest trial from a  production DB  to
a  suitable Storage Device( Mostly Unified storage  DELL Unity)

Any inputs are most welcome.

Thanks,
Krishane

On Tue, Oct 3, 2023 at 12:14 PM Abhishek Bhola <
abhishek.bh...@japannext.co.jp> wrote:

> Hello,
>
> As said above, I tested pgBackRest on my bigger DB and here are the
> results.
> Server on which this is running has the following config:
> Architecture:  x86_64
> CPU op-mode(s):32-bit, 64-bit
> Byte Order:Little Endian
> CPU(s):36
> On-line CPU(s) list:   0-35
> Thread(s) per core:1
> Core(s) per socket:18
> Socket(s): 2
> NUMA node(s):  2
>
> Data folder size: 52 TB (has some duplicate files since it is restored
> from tapes)
> Backup is being written on to DELL Storage, mounted on the server.
>
> pgbackrest.conf with following options enabled
> repo1-block=y
> repo1-bundle=y
> start-fast=y
>
>
> 1. *Using process-max: 30, Time taken: ~26 hours*
> full backup: 20230926-092555F
> timestamp start/stop: 2023-09-26 09:25:55+09 / 2023-09-27
> 11:07:18+09
> wal start/stop: 00010001AC0E0044 /
> 00010001AC0E0044
> database size: 38248.9GB, database backup size: 38248.9GB
> repo1: backup size: 6222.0GB
>
> 2. *Using process-max: 10, Time taken: ~37 hours*
>  full backup: 20230930-190002F
> timestamp start/stop: 2023-09-30 19:00:02+09 / 2023-10-02
> 08:01:20+09
> wal start/stop: 00010001AC0E004E /
> 00010001AC0E004E
> database size: 38248.9GB, database backup size: 38248.9GB
> repo1: backup size: 6222.0GB
>
> Hope it helps someone to use these numbers as some reference.
>
> Thanks
>
>
> On Mon, Aug 28, 2023 at 12:30 AM Abhishek Bhola <
> abhishek.bh...@japannext.co.jp> wrote:
>
>> Hi Stephen
>>
>> Thank you for the prompt response.
>> Hearing it from you makes me more confident about rolling it to PROD.
>> I will have a discussion with the network team once about and hear what
>> they have to say and make an estimate accordingly.
>>
>> If you happen to know anyone using it with that size and having published
>> their numbers, that would be great, but if not, I will post them once I set
>> it up.
>>
>> Thanks for your help.
>>
>> Cheers,
>> Abhishek
>>
>> On Mon, Aug 28, 2023 at 12:22 AM Stephen Frost 
>> wrote:
>>
>>> Greetings,
>>>
>>> * Abhishek Bhola (abhishek.bh...@japannext.co.jp) wrote:
>>> > I am trying to use pgBackRest for all my Postgres servers. I have
>>> tested it
>>> > on a sample database and it works fine. But my concern is for some of
>>> the
>>> > bigger DB clusters, the largest one being 50TB and growing by about
>>> > 200-300GB a day.
>>>
>>> Glad pgBackRest has been working well for you.
>>>
>>> > I plan to mount NAS storage on my DB server to store my backup. The
>>> server
>>> > with 50 TB data is using DELL Storage underneath to store this data
>>> and has
>>> > 36 18-core CPUs.
>>>
>>> How much free CPU capacity does the system have?
>>>
>>> > As I understand, pgBackRest recommends having 2 full backups and then
>>> > having incremental or differential backups as per requirement. Does
>>> anyone
>>> > have any reference numbers on how much time a backup for such a DB
>>> would
>>> > usually take, just for reference. If I take a full backup every Sunday
>>> and
>>> > then incremental backups for the rest of the week, I believe the
>>> > incremental backups should not be a problem, but the full backup every
>>> > Sunday might not finish in time.
>>>
>>> pgBackRest scales extremely well- what's going to matter here is how
>>> much you can 

Re: pgBackRest for a 50 TB database

[Abhishek Bhola]

Hi Stephen

No, I did not try that. Let me try that now and report the numbers here,
both in terms of size and time taken.
Thanks for the suggestion.


On Tue, Oct 3, 2023 at 10:39 PM Stephen Frost  wrote:

> Greetings,
>
> On Mon, Oct 2, 2023 at 20:08 Abhishek Bhola <
> abhishek.bh...@japannext.co.jp> wrote:
>
>> As said above, I tested pgBackRest on my bigger DB and here are the
>> results.
>> Server on which this is running has the following config:
>> Architecture:  x86_64
>> CPU op-mode(s):32-bit, 64-bit
>> Byte Order:Little Endian
>> CPU(s):36
>> On-line CPU(s) list:   0-35
>> Thread(s) per core:1
>> Core(s) per socket:18
>> Socket(s): 2
>> NUMA node(s):  2
>>
>> Data folder size: 52 TB (has some duplicate files since it is restored
>> from tapes)
>> Backup is being written on to DELL Storage, mounted on the server.
>>
>> pgbackrest.conf with following options enabled
>> repo1-block=y
>> repo1-bundle=y
>> start-fast=y
>>
>
> Thanks for sharing!  Did you perhaps consider using zstd for the
> compression..?  You might find that you get similar compression in less
> time.
>
> Thanks.
>
> Stephen
>

-- 
_This correspondence (including any attachments) is for the intended 
recipient(s) only. It may contain confidential or privileged information or 
both. No confidentiality or privilege is waived or lost by any 
mis-transmission. If you receive this correspondence by mistake, please 
contact the sender immediately, delete this correspondence (and all 
attachments) and destroy any hard copies. You must not use, disclose, copy, 
distribute or rely on any part of this correspondence (including any 
attachments) if you are not the intended 
recipient(s).本メッセージに記載および添付されている情報（以下、総称して「本情報」といいます。）は、本来の受信者による使用のみを意図しています。誤送信等により本情報を取得された場合でも、本情報に係る秘密、または法律上の秘匿特権が失われるものではありません。本電子メールを受取られた方が、本来の受信者ではない場合には、本情報及びそのコピーすべてを削除・破棄し、本電子メールが誤って届いた旨を発信者宛てにご通知下さいますようお願いします。本情報の閲覧、発信または本情報に基づくいかなる行為も明確に禁止されていることをご了承ください。_

Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones

[Steve Crawford]

On Tue, Oct 3, 2023 at 7:33 PM Lincoln Swaine-Moore 
wrote:

> Hi all,
>
> I'm attempting to generate some reports using user-driven timezones for
> UTC data, and I'm having trouble writing a query that meets the following
> criteria:
>
> 1) Data should be averaged at one of daily, hourly, or 15 minute
> granularities (user-driven).
> 2) Data over a given period should reflect the localized value of the UTC
> data for a user-provided time zone. E.g.:
> a) The 1 hour period starting at '2023-10-03 12:00:00.00-0400'
> (America/New_York) should reflect data with timestamps between '2023-10-03
> 16:00:00.00Z' and '2023-10-03 17:00:00.00Z'.
> b) The 1 day period starting at '2023-10-03 00:00:00.00-0400'
> should reflect data with timestamps between '2023-10-03 04:00:00.00Z'
> and '2023-10-04 04:00:00.00Z'.
> 3) When a period interacts with a DST change in the given timezone, the
> data should not be clumped together. E.g.:
> a) Data points occurring at 2023-11-05 05:30:00.00Z and 2023-11-05
> 06:30:00.00Z should be treated as falling into separate buckets when
> the time zone is America/New_York: (2023-11-05 01:30:00.00-0400 and
> 2023-11-05 01:30:00.00-0500, respectively). This should be true for
> either the 15 minute or 1 hour intervals.
> b) Some clumping for day resolution seems ok! E.g. the 1 day period
> starting at '2023-11-05 00:00:00.00-0400' can and probably should
> contain 25 hours' worth of data. Certainly it should not reflect the data
> falling between '2023-11-05 00:04:00.00Z' and '2023-11-05
> 00:04:00.00Z' + '24 hours'::interval (= '2023-11-06 00:04:00.00Z'),
> because that would be the local times of '2023-11-05 00:00:00.00-0400'
> and '2023-11-04 23:00:00.00-0500'.
> 4) It would be relatively simple to do 15 minute and 1 hour periods were
> all timezone offsets multiples of 1 hour (in that case, all operations
> could be done in UTC and then converted after the fact), but unfortunately
> some time zones have 30 min-based offsets, which interferes with this
> approach.
> 5) Ideally, the solution would not involve messing with the
> server/connection's value of timezone. (Though I would be interested if
> there was a solution that relaxed this constraint and was relatively
> safe/compatible with transactions and psycopg2.)
> 6) Ideally, my query would return periods that are missing data (though
> could plausibly fill these in in the layer above). This points toward
> generate_series but...
> 7) Sigh: I can't upgrade to 16. I gather that 16 has a timezone parameter
> for generate_series, which I believe might help. But tragically,
> Digitalocean doesn't yet support 16, and it's not practical to migrate
> elsewhere. Based on historical release -> support timing, I'd imagine they
> will not support it until Q2 2024, which is too late for this feature. If
> anyone had the inside scoop about when they'd likely support it, I'd
> welcome it!
>
> This looks pretty hairy written out as above, but I actually think it
> reflects most people's intuitions about what data a local period "should"
> correspond to (though I'd welcome feedback on this point).
>
> Here are some thoughts about approaches that I've tried, and what their
> drawbacks seem to be. For all these, I'll use the following CTE to
> demonstrate some data that crosses a DST boundary:
>
> ```
> with original_data as (
> select
> ('2023-11-05 00:00:00.00Z'::timestamptz) + (15 * x || '
> minutes')::interval as "t"
> from
> generate_series(0, 1000) as x
> )
> ```
>
> 1) date_trunc: it seems like as of v12, date_trunc accepts a third
> argument of timezone, which essentially plays the role of the server
> timezone setting for the scope of the function. This is very handy, and *I
> believe* solves my issues for the hour/day periods:
> ```
> [etc]
> select
> date_trunc('day', t, 'America/New_York'),
> min(t),
> max(t),
> count(*)
> from original_data
> group by 1
> order by 1;
>
>date_trunc   |  min   |  max
> | count
>
> +++---
>  2023-11-04 04:00:00+00 | 2023-11-05 00:00:00+00 | 2023-11-05 03:45:00+00
> |16
>  2023-11-05 04:00:00+00 | 2023-11-05 04:00:00+00 | 2023-11-06 04:45:00+00
> |   100
>  2023-11-06 05:00:00+00 | 2023-11-06 05:00:00+00 | 2023-11-07 04:45:00+00
> |96
>  2023-11-07 05:00:00+00 | 2023-11-07 05:00:00+00 | 2023-11-08 04:45:00+00
> |96
>  2023-11-08 05:00:00+00 | 2023-11-08 05:00:00+00 | 2023-11-09 04:45:00+00
> |96
>  2023-11-09 05:00:00+00 | 2023-11-09 05:00:00+00 | 2023-11-10 04:45:00+00
> |96
>
> [etc]
> ```
>
> This checks out, but unfortunately doesn't seem to work for 15 minutes. I
> think, by the way, that this behavior is identical to what I would've
> gotten if my server timezone was "America/New_York" and I ran it without
> the 3rd argument, though I'd be curious 

Strategies for converting UTC data to local windows for arbitrary resolutions and timezones

[Lincoln Swaine-Moore]

Hi all,

I'm attempting to generate some reports using user-driven timezones for UTC
data, and I'm having trouble writing a query that meets the following
criteria:

1) Data should be averaged at one of daily, hourly, or 15 minute
granularities (user-driven).
2) Data over a given period should reflect the localized value of the UTC
data for a user-provided time zone. E.g.:
a) The 1 hour period starting at '2023-10-03 12:00:00.00-0400'
(America/New_York) should reflect data with timestamps between '2023-10-03
16:00:00.00Z' and '2023-10-03 17:00:00.00Z'.
b) The 1 day period starting at '2023-10-03 00:00:00.00-0400'
should reflect data with timestamps between '2023-10-03 04:00:00.00Z'
and '2023-10-04 04:00:00.00Z'.
3) When a period interacts with a DST change in the given timezone, the
data should not be clumped together. E.g.:
a) Data points occurring at 2023-11-05 05:30:00.00Z and 2023-11-05
06:30:00.00Z should be treated as falling into separate buckets when
the time zone is America/New_York: (2023-11-05 01:30:00.00-0400 and
2023-11-05 01:30:00.00-0500, respectively). This should be true for
either the 15 minute or 1 hour intervals.
b) Some clumping for day resolution seems ok! E.g. the 1 day period
starting at '2023-11-05 00:00:00.00-0400' can and probably should
contain 25 hours' worth of data. Certainly it should not reflect the data
falling between '2023-11-05 00:04:00.00Z' and '2023-11-05
00:04:00.00Z' + '24 hours'::interval (= '2023-11-06 00:04:00.00Z'),
because that would be the local times of '2023-11-05 00:00:00.00-0400'
and '2023-11-04 23:00:00.00-0500'.
4) It would be relatively simple to do 15 minute and 1 hour periods were
all timezone offsets multiples of 1 hour (in that case, all operations
could be done in UTC and then converted after the fact), but unfortunately
some time zones have 30 min-based offsets, which interferes with this
approach.
5) Ideally, the solution would not involve messing with the
server/connection's value of timezone. (Though I would be interested if
there was a solution that relaxed this constraint and was relatively
safe/compatible with transactions and psycopg2.)
6) Ideally, my query would return periods that are missing data (though
could plausibly fill these in in the layer above). This points toward
generate_series but...
7) Sigh: I can't upgrade to 16. I gather that 16 has a timezone parameter
for generate_series, which I believe might help. But tragically,
Digitalocean doesn't yet support 16, and it's not practical to migrate
elsewhere. Based on historical release -> support timing, I'd imagine they
will not support it until Q2 2024, which is too late for this feature. If
anyone had the inside scoop about when they'd likely support it, I'd
welcome it!

This looks pretty hairy written out as above, but I actually think it
reflects most people's intuitions about what data a local period "should"
correspond to (though I'd welcome feedback on this point).

Here are some thoughts about approaches that I've tried, and what their
drawbacks seem to be. For all these, I'll use the following CTE to
demonstrate some data that crosses a DST boundary:

```
with original_data as (
select
('2023-11-05 00:00:00.00Z'::timestamptz) + (15 * x || '
minutes')::interval as "t"
from
generate_series(0, 1000) as x
)
```

1) date_trunc: it seems like as of v12, date_trunc accepts a third argument
of timezone, which essentially plays the role of the server timezone
setting for the scope of the function. This is very handy, and *I believe*
solves my issues for the hour/day periods:
```
[etc]
select
date_trunc('day', t, 'America/New_York'),
min(t),
max(t),
count(*)
from original_data
group by 1
order by 1;

   date_trunc   |  min   |  max   |
count
+++---
 2023-11-04 04:00:00+00 | 2023-11-05 00:00:00+00 | 2023-11-05 03:45:00+00 |
   16
 2023-11-05 04:00:00+00 | 2023-11-05 04:00:00+00 | 2023-11-06 04:45:00+00 |
  100
 2023-11-06 05:00:00+00 | 2023-11-06 05:00:00+00 | 2023-11-07 04:45:00+00 |
   96
 2023-11-07 05:00:00+00 | 2023-11-07 05:00:00+00 | 2023-11-08 04:45:00+00 |
   96
 2023-11-08 05:00:00+00 | 2023-11-08 05:00:00+00 | 2023-11-09 04:45:00+00 |
   96
 2023-11-09 05:00:00+00 | 2023-11-09 05:00:00+00 | 2023-11-10 04:45:00+00 |
   96

[etc]
```

This checks out, but unfortunately doesn't seem to work for 15 minutes. I
think, by the way, that this behavior is identical to what I would've
gotten if my server timezone was "America/New_York" and I ran it without
the 3rd argument, though I'd be curious to hear if there are discrepancies.

On this point, I've read up on some of the history around this feature, and
was a little puzzled by this assertion in this thread:
https://www.postgresql.org/message-id/87in1k73nr@news-spur.riddles.org.uk
:

> If you 

Re: Strange error trying to import with Ora2PG

[Adrian Klaver]

On 10/3/23 10:32, Johnson, Bruce E - (bjohnson) wrote:
I am trying to import an oracle schema with ora2pg and running into an 
odd error when the import_all.sh script gets to a specific view.


The error is:



However when I re-run the import_all.sh script it errors out at the same 
place with the same error (in fact the pasted error code is from running 
the script AFTER I fixed it). The sql isn’t being cached anywhere in the 
system is it?


I'm betting that every time you run import_all.sh it rewrites the 
scripts. Pretty sure if you look at the file the creation script will 
have been rewritten to its original from.




I know that I modified the correct file.

It happens even if I drop the database and recreate it.

--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs



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

Strange error trying to import with Ora2PG

[Johnson, Bruce E - (bjohnson)]

I am trying to import an oracle schema with ora2pg and running into an odd 
error when the import_all.sh script gets to a specific view.

The error is:

psql:schema/views/RESERVER_VISIBLE_RESTRICTION_view.sql:10: ERROR:  operator 
does not exist: character = integer
LINE 1: ...r.resource_id = rg.resource_id and r.requester_vis_only = 1;

A simple enough fix; Oracle happily accepts a number-like string as a number, 
and Postgres wants a string delimited,  so I went into the ./sources/views 
folder and edited the view creation to add the required single quotes:

-- Generated by Ora2Pg, the Oracle database Schema converter, version 24.0
-- Copyright 2000-2023 Gilles DAROLD. All rights reserved.
-- DATASOURCE: 
dbi:Oracle:host=dhboracle2.pharmacy.arizona.edu;sid=phmweb2;port=1521

SET client_encoding TO 'UTF8';

SET search_path = calendar3,oracle,public;
\set ON_ERROR_STOP ON

CREATE OR REPLACE VIEW reserver_visible_restriction (resource_id, 
resourcegroup_id, affil_id) AS select r.resource_id, rg.resourcegroup_id, 
a.affil_id FROM resources r, resourcegroupaffil rg, resourceaffil a where 
r.resource_id = a.resource_id and r.resource_id = rg.resource_id and 
r.requester_vis_only = '1’;

I tested the view creation with that code interactively, and it worked.

However when I re-run the import_all.sh script it errors out at the same place 
with the same error (in fact the pasted error code is from running the script 
AFTER I fixed it). The sql isn’t being cached anywhere in the system is it?

I know that I modified the correct file.

It happens even if I drop the database and recreate it.

--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Re: How to investigate deadlocks

[Andreas Kretschmer]

Am 02.10.23 um 13:27 schrieb Matthias Apitz:

Hello,

One of our clients running our LMS on top of PostgreSQL 13.1 created a
ticket with these messages:

2023-09-30 16:50:50.951 CEST [18117] ERROR:  deadlock detected
2023-09-30 16:50:50.951 CEST [18117] DETAIL:  Process 18117 waits for ShareLock 
on transaction 150396154; blocked by process 18187.
Process 18187 waits for ShareLock on transaction 150396155; blocked by 
process 18117.
Process 18117: fetch hc_d03geb
Process 18187: fetch hc_d02ben
2023-09-30 16:50:50.951 CEST [18117] HINT:  See server log for query details.
2023-09-30 16:50:50.951 CEST [18117] CONTEXT:  while locking tuple (38,57) in relation 
"d03geb"
2023-09-30 16:50:50.951 CEST [18117] STATEMENT:  fetch hc_d03geb



have you checked the server log?

See server log for query details.



Regards, Andreas

--
Andreas Kretschmer
Technical Account Manager (TAM)
www.enterprisedb.com

Re: Logical Replication vs. Free Replication Slots

[Don Seiler]

On Tue, Oct 3, 2023 at 10:27 AM Don Seiler  wrote:

> On the source (PG 12.15) instance, we have bumped max_replication_slots
> and max_wal_senders to 50, and max_sync_workers_per_subscription to 10.
>

Forgot to note that on the subscriber (PG 15.4) instance,
max_sync_workers_per_subscription is 4, and max_logical_replication_workers
is 20. Per the docs, max_sync_workers_per_subscription would have no effect
on the publisher side.

Don.
-- 
Don Seiler
www.seiler.us

Logical Replication vs. Free Replication Slots

[Don Seiler]

Good morning,

I'm trying to set up native logical replication from PG 12.15 (Ubuntu
18.04) to 15.4 (Ubuntu 22.04). I wanted to set up 3 publications to split
up the work. I initially created the three publications with a handful of
tables each. On the source (PG 12.15) instance, we have bumped
max_replication_slots and max_wal_senders to 50, and
max_sync_workers_per_subscription to 10.

When I create the subscriptions to start the initial table sync, the third
one failed to enable, with this error:

2023-09-29 21:07:45.861 UTC [1853362] ERROR: could not find free
replication state slot for replication origin with ID 11 2023-09-29
21:07:45.861 UTC [1853362] HINT: Increase max_replication_slots and try
again. 2023-09-29 21:07:45.861 UTC [1853362] LOG: subscription "sub03" has
been disabled because of an error

However when I look at the publication side, there are only 20 replication
slots created (out of 50 max), 3 of which are the subscriptions and the
rest are the tablesyncs workers.

So I'm trying to make sense of why it would tell me to increase
max_replication_slots when I don't appear to be anywhere near the max.
-- 
Don Seiler
www.seiler.us

Re: Problems starting slave

[Ron]

On 10/3/23 09:37, Douglas Reed wrote:

Hi the full pg_basebackup command was;

    pg_basebackup -h 172.110.6.5 -D /var/lib/pgsql/12/data -R -U postgres 
--wal-method=stream


I get consistently successful results in v15 using this set of commands 
(where the service options reference the primary server in .pg_service_conf):


psql service=primary -c "select pg_drop_replication_slot(slot_name) from 
pg_replication_slots where slot_name = 'pgstandby1';"


/usr/bin/rm -r $PGDATA/*

pg_basebackup --dbname=service=basebackup -D $PGDATA --progress 
--checkpoint=fast -v \
        --write-recovery-conf --wal-method=stream --create-slot 
--slot=pgstandby1 --compress=server-zstd


pg_ctl start -wt90

zstd compression might not be supported by v12.

--
Born in Arizona, moved to Babylonia.

Re: [EXT]Re: Connection not allowed because of an error 'Not in pg_hba.conf'

[Adrian Klaver]

On 10/3/23 02:59, Harry Green wrote:


Dear All,
I'd like to thank you for your excellent input on this problem. We have 
now resolved this issue and I thought I would mention how. The topic of 
the function being used as a check constraint had come up and I had 
rejected this because it was the name given to a function which does 
some checking. Since the function was used elsewhere I did not think it 
was itself used as a check constraint, but in fact it was (on a 
different table than the one that comes up in the error message). So you 
were right to highlight that fact. I should note, however, that it seems 
to me this is a bug in postgresql, if not in the narrower sense than in 
the wider: the issue is that nowhere in the process of creating a very 
simple function that references some table and then employed as a check 
constraint on a different table is the user warned or stopped from doing 
so. In many cases, doing this saves time over creating triggers or 
alternative mechanisms, so the user is naturally drawn to employing this 
technique. Yet when the pg_dump is attempted the process fails. 
Furthermore, it is not entirely clear why pg_dump cannot add the check 
constraints after all the tables are created, just as it does with 
triggers. This is why it is worth considering or treating this as a bug 
that may have a solution  - either by modifying pg_restore (or text 
equivalent process) or by preventing the user from employing certain 
types of functions as check constraints.


1) Hijacking another thread is not a good idea.

2) Per my first post on this issue:

https://www.postgresql.org/docs/current/sql-createtable.html

"Currently, CHECK expressions cannot contain subqueries nor refer to
variables other than columns of the current row (see Section 5.4.1). The
system column tableoid may be referenced, but not any other system column."

It is pretty plain you cannot refer to columns outside the table the 
CHECK is on. Trying to cheat by hiding that in a function, well you see 
what happens then.


3) The function bodies are opaque to the server. This means there will 
be no dependency tracking for the dump/restore.







The problem was resolved not by altering the functions in any way, but 
by creating triggers that employed them through wrap-up functions which 
used the NEW.column_name mechanism in the usual way on inserts and updates.


Thank you once again for your valuable feedback.


On Wednesday, 20 September 2023 at 22:16:32 BST, David G. Johnston 
 wrote:



On Wed, Sep 20, 2023 at 2:06 PM Harry Green > wrote:


I attach the text of the entire create function instruction, and
also of the create table instruction.


The restore is not going to execute functions on its own and you've only 
shown two create statements.  Somewhere else in your dump file the 
function check_account_from_bill_items must be referenced in order for 
it to be called.  You need to show that.  It is not the function 
creation that is going to be illegal, it will be, like Adrian said, 
something like using a volatile function in a check constraint that is 
going to be illegal.


We are working with version 10.23, and I cannot send you the entire
output of the pg_dump file because it is 3.3 GB, but am happy to
send you any parts that might help.


I doubt a schema-only dump is going to be that large...but you are right 
that you should be trying harder to isolate this down to a reproducible 
test case and thus be able to provide more information without it being 
too much.


David J.



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

Re: Problems starting slave

[Douglas Reed]

Hi the full pg_basebackup command was;
    pg_basebackup -h 172.110.6.5 -D /var/lib/pgsql/12/data -R -U postgres 
--wal-method=stream


Regards



Doug Reed dougreed...@yahoo.com 

Re: pgBackRest for a 50 TB database

[Stephen Frost]

Greetings,

On Mon, Oct 2, 2023 at 20:08 Abhishek Bhola 
wrote:

> As said above, I tested pgBackRest on my bigger DB and here are the
> results.
> Server on which this is running has the following config:
> Architecture:  x86_64
> CPU op-mode(s):32-bit, 64-bit
> Byte Order:Little Endian
> CPU(s):36
> On-line CPU(s) list:   0-35
> Thread(s) per core:1
> Core(s) per socket:18
> Socket(s): 2
> NUMA node(s):  2
>
> Data folder size: 52 TB (has some duplicate files since it is restored
> from tapes)
> Backup is being written on to DELL Storage, mounted on the server.
>
> pgbackrest.conf with following options enabled
> repo1-block=y
> repo1-bundle=y
> start-fast=y
>

Thanks for sharing!  Did you perhaps consider using zstd for the
compression..?  You might find that you get similar compression in less
time.

Thanks.

Stephen

Re: Peer authentication failed ???

[Peter J. Holzer]

On 2023-10-03 05:55:51 -0400, Amn Ojee Uw wrote:
> psql -U my_group_worker -d my_group_db
> 
> psql: error: connection to server on socket 
> "/var/run/postgresql/.s.PGSQL.5432"
> failed: FATAL:  Peer authentication failed for user "my_group_worker"
> 
> *** What am I doing wrong?

PostgreSQL uses the first matching rule from pg_hba.conf. Presumably
(because that's the default on Debian/Ubuntu) you have it set up to use
peer authentication on the unix socket and password authentication
(scram or md5) on everything else.

You are connecting via the Unix socket (/var/run/postgresql/.s.PGSQL.5432),
so the server will attempt peer authentication and nothing else. To get
it to attempt password authentication connect via a tcp socket:

psql -U my_group_worker -h localhost -d my_group_db

(More conveniently set up ident authentication for your users, then you
don't need a password.)

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: specifying multiple options in URI psql behaviour?

[Ron]

On 10/2/23 10:21, Wim Bertels wrote:

Tom Lane schreef op ma 02-10-2023 om 10:21 [-0400]:

Wim Bertels  writes:

* but if you put more than 1 option, then it doesn't?:
#psql
postgresql://myuser@myserver/mydb?connect_timeout=10_session
_attrs=any

Maybe you forgot to quote that?  Ampersand is a shell metacharacter.

yes indeed, & bg and fg, thank you Tom

# psql
'postgresql://myuser@myserver/mydb?connect_timeout=10_session_at
trs=any'

or just

# psql
postgresql://myuser@myserver/mydb?connect_timeout=10'&'target_session_a
ttrs=any


Escaping the & using \ should also work.

--
Born in Arizona, moved to Babylonia.

Re: Problems starting slave

[Ron]

On 10/2/23 09:38, Douglas Reed wrote:

Hi guys

The servers are virtual running on Nutanix

We are running Pg version 12 (12.10)

On Linux km-data1.rs.fsbtech.com 5.4.191-1.el7.elrepo.x86_64 #1 SMP Tue 
Apr 26 12:14:16 EDT 2022 x86_64 x86_64 x86_64 GNU/Linux


48G/16 x CPU (Master and slave)

Timeline

System had a number of issues due to kafka and slots, would not always 
shut down correctly. Several incidents where it had to be forced.
Found corruption on a number of indexes and tables. Decided to recover 
from a backup (barman). Due to missing wal file restored data up

to about three hours prior to expectation but it's working.

Attempts to build a slave;

On the slave at first we got error messages from pg_basebackup stating 
that the target directory was not empty;


    pg_basebackup: error: directory "/var/lib/pgsql/12/data" exists but is 
not empty


Although the directory /var/lib/pgsql/12/data was empty (using rm -r 
). Finally deleted the data directory and re-created ensuring that

perms were same. Restore started successfully and completed with error=0.



What was the full pg_basebackup command, including all options?

--
Born in Arizona, moved to Babylonia.

Re: Operating of synchronous master when no standby is available

[Sergey Cherukhin]

Thank you for your answer.

вт, 3 окт. 2023 г., 17:22 MATSUO Takatoshi :

> It's not normal behavior.
>
> Pgsql RA is developed here.
> https://github.com/ClusterLabs/resource-agents/blob/main/heartbeat/pgsql
> You may get better advice if you ask on this mailing list.
> https://oss.clusterlabs.org/mailman/listinfo/users
>
>
> 2023年10月3日(火) 18:03 Sergey Cherukhin :
>
>> I already have rep_mode set to "sync", but the primary node switches to
>> async mode only if the replica is shutted down correctly. When I shut down
>> the replica by powering it off to emulate power unit failure, primary
>> remains in sync mode until "pcs resource cleanup" is performed. I think it
>> is possible to run resource cleanup in script on cluster alert.
>>
>> вт, 3 окт. 2023 г. в 05:13, MATSUO Takatoshi :
>>
>>> Pgsql RA  has rep_mode=sync parameter.
>>> https://wiki.clusterlabs.org/wiki/PgSQL_Replicated_Cluster
>>>
>>> 2023年10月2日(月) 23:48 Sergey Cherukhin :
>>>
 Hello!

 I use Postgresql+Pacemaker+Corosync cluster with 2 Postgresql instances
 in synchronous replication mode. When one of the nodes is down, clients
 hang on INSERT operations because the primary server waits until standby
 confirms that it got the data, but the standby node is down.

 I need clients to feel no difference when they operate with a fully
 operational cluster or with a degraded cluster containing primary
 node alone.

 Is it possible to configure synchronous replication to operate normally
 when primary and replica are online, but primary no waits confirmation from
 replica if replica is not connected?

 Best regards,
 Sergey Cherukhin

Re: Peer authentication failed ???

[David G. Johnston]

On Tuesday, October 3, 2023, Amn Ojee Uw  wrote:
>
> Using the 'sudo -u postgres psql' command
>
> *psql -U my_group_worker -d my_group_db*
>
> *psql: error: connection to server on socket
> "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication
> failed for user "my_group_worker**"*
>
> *** What am I doing wrong?
>

postgres != my_group_worker and you haven’t setup an ident mapping to
associate the two

David J.

Cancelling "vacuum full" in single user mode?

[Colin 't Hart]

Hi,

I have a customer approaching transaction wraparound, about 3million
transaction IDs away at the moment.
Postgres 9.5 (yes, I know...)

Somewhat mislead by the message to vacuum the database in single user
mode, they are now in single user mode and are running "vacuum full"
on the "worst" database, which has 18.5 million tables, and is about
350GB on disk.

Now we want to cancel the "vacuum full" and run a "vacuum freeze"
instead... or preferably, start back up in multiuser mode and run
vacuum there, but that was running very slowly while autovacuum was
trying to run (so we would need to disable that too, for now).

So,

1. Can we cancel safely?
2. Any other suggestions? Upgrade to a newer version right now?

/Colin

Re: Operating of synchronous master when no standby is available

[MATSUO Takatoshi]

It's not normal behavior.

Pgsql RA is developed here.
https://github.com/ClusterLabs/resource-agents/blob/main/heartbeat/pgsql
You may get better advice if you ask on this mailing list.
https://oss.clusterlabs.org/mailman/listinfo/users


2023年10月3日(火) 18:03 Sergey Cherukhin :

> I already have rep_mode set to "sync", but the primary node switches to
> async mode only if the replica is shutted down correctly. When I shut down
> the replica by powering it off to emulate power unit failure, primary
> remains in sync mode until "pcs resource cleanup" is performed. I think it
> is possible to run resource cleanup in script on cluster alert.
>
> вт, 3 окт. 2023 г. в 05:13, MATSUO Takatoshi :
>
>> Pgsql RA  has rep_mode=sync parameter.
>> https://wiki.clusterlabs.org/wiki/PgSQL_Replicated_Cluster
>>
>> 2023年10月2日(月) 23:48 Sergey Cherukhin :
>>
>>> Hello!
>>>
>>> I use Postgresql+Pacemaker+Corosync cluster with 2 Postgresql instances
>>> in synchronous replication mode. When one of the nodes is down, clients
>>> hang on INSERT operations because the primary server waits until standby
>>> confirms that it got the data, but the standby node is down.
>>>
>>> I need clients to feel no difference when they operate with a fully
>>> operational cluster or with a degraded cluster containing primary
>>> node alone.
>>>
>>> Is it possible to configure synchronous replication to operate normally
>>> when primary and replica are online, but primary no waits confirmation from
>>> replica if replica is not connected?
>>>
>>> Best regards,
>>> Sergey Cherukhin
>>>
>>>

Re: [EXT]Re: Connection not allowed because of an error 'Not in pg_hba.conf'

[Harry Green]

 
Dear All,I'd like to thank you for your excellent input on this problem. We 
have now resolved this issue and I thought I would mention how. The topic of 
the function being used as a check constraint had come up and I had rejected 
this because it was the name given to a function which does some checking. 
Since the function was used elsewhere I did not think it was itself used as a 
check constraint, but in fact it was (on a different table than the one that 
comes up in the error message). So you were right to highlight that fact. I 
should note, however, that it seems to me this is a bug in postgresql, if not 
in the narrower sense than in the wider: the issue is that nowhere in the 
process of creating a very simple function that references some table and then 
employed as a check constraint on a different table is the user warned or 
stopped from doing so. In many cases, doing this saves time over creating 
triggers or alternative mechanisms, so the user is naturally drawn to employing 
this technique. Yet when the pg_dump is attempted the process fails. 
Furthermore, it is not entirely clear why pg_dump cannot add the check 
constraints after all the tables are created, just as it does with triggers. 
This is why it is worth considering or treating this as a bug that may have a 
solution  - either by modifying pg_restore (or text equivalent process) or by 
preventing the user from employing certain types of functions as check 
constraints.
The problem was resolved not by altering the functions in any way, but by 
creating triggers that employed them through wrap-up functions which used the 
NEW.column_name mechanism in the usual way on inserts and updates.
Thank you once again for your valuable feedback.

On Wednesday, 20 September 2023 at 22:16:32 BST, David G. Johnston 
 wrote:  
 
 On Wed, Sep 20, 2023 at 2:06 PM Harry Green  wrote:

 I attach the text of the entire create function instruction, and also of the 
create table instruction. 



The restore is not going to execute functions on its own and you've only shown 
two create statements.  Somewhere else in your dump file the function 
check_account_from_bill_items must be referenced in order for it to be called.  
You need to show that.  It is not the function creation that is going to be 
illegal, it will be, like Adrian said, something like using a volatile function 
in a check constraint that is going to be illegal.

We are working with version 10.23, and I cannot send you the entire output of 
the pg_dump file because it is 3.3 GB, but am happy to send you any parts that 
might help. 


I doubt a schema-only dump is going to be that large...but you are right that 
you should be trying harder to isolate this down to a reproducible test case 
and thus be able to provide more information without it being too much.
David J.
  

Peer authentication failed ???

[Amn Ojee Uw]

Hi there.

Using the 'sudo -u postgres psql' command I created a group and two 
accounts in the group, I also created a database for one of the 
accounts. Now, I'd like to login PG-15, using my new account and 
connecting to my new database, but PG-15 complains. See the example 
below, I think it is clear than my English.


*CREATE ROLE my_group; // Create a group**
**CREATE ROLE my_group_admin LOGIN PASSWORD 'AdminPassword' CREATEDB 
CREATEROLE; // Create admin**
**CREATE ROLE my_group_worker LOGIN PASSWORD 'WorkerPassword' CREATEDB; 
// Create Worker**

**GRANT my_group TO my_group_admin WITH INHERIT TRUE;**
**GRANT my_group TO my_group_worker WITH INHERIT TRUE;**
**
**CREATE DATABASE my_group_db WITH OWNER my_group_worker;**
**\q**
**psql -U my_group_worker -d my_group_db*

/psql: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication 
failed for user "my_group_worker/*"*


*** What am I doing wrong?


Thanks in advance.

Re: pg_stat_statements IN problem

[David Rowley]

On Tue, 3 Oct 2023 at 22:03,  wrote:
> So one last question, should I expect the patch to land in version 17 only or 
> is there chance that it will also be in lower versions right away?

It wouldn't ever be put into anything earlier than 17.

David

Re: pg_stat_statements IN problem

[byme]

Thank you very much Laurenz and David. Looking forward to it and unfortunatelly 
no, I am not in position to review that... 

So one last question, should I expect the patch to land in version 17 only or 
is there chance that it will also be in lower versions right away?

LJ



Sent with Proton Mail secure email.

--- Original Message ---
On Tuesday, October 3rd, 2023 at 10:54 AM, Laurenz Albe 
 wrote:


> On Tue, 2023-10-03 at 08:05 +, byme@byme.email wrote:
> 
> > "This obfuscates our monitoring because the same query with different 
> > amount of arguments gets translated into this:
> > IN ($1, $2)
> > and so on."
> > 
> > The questions are:
> > 1. Shouldnt IN behave so that the query in pg_stat_statements would look 
> > like this:
> > IN $1
> > 2. Shouldnt there be at least some flag to aggregate such queries into one?
> > 3. Is there any workaround how to aggregate those queries except the "= 
> > ANY"?
> > 4. How come no one is bothered by this if this makes pg_stat_statements 
> > unusable with lots of queries using IN, what others do with this problem?
> > 5. what do you mean by changing pg_stat_statements with another view/table?
> 
> 
> There is currently a patch for this very problem under review:
> 
> https://commitfest.postgresql.org/44/2837/
> 
> The discussion is here:
> 
> https://www.postgresql.org/message-id/flat/ca+q6zcwtubt_sxj0v6hy6ez89uv5wug5aefpe_9n0jr3vwn...@mail.gmail.com
> 
> You could comment on that patch or review it. Useful reviews and supporting
> comments help move the patch forward. That would best serve your interests.
> 
> Yours,
> Laurenz Albe

Re: pg_stat_statements IN problem

[Laurenz Albe]

On Tue, 2023-10-03 at 08:05 +, byme@byme.email wrote:
> "This obfuscates our monitoring because the same query with different amount 
> of arguments gets translated into this:
> IN ($1, $2)
> and so on."
> 
> The questions are:
> 1. Shouldnt IN behave so that the query in pg_stat_statements would look like 
> this:
> IN $1
> 2. Shouldnt there be at least some flag to aggregate such queries into one?
> 3. Is there any workaround how to aggregate those queries except the "= ANY"?
> 4. How come no one is bothered by this if this makes pg_stat_statements 
> unusable with lots of queries using IN, what others do with this problem?
> 5. what do you mean by changing pg_stat_statements with another view/table?

There is currently a patch for this very problem under review:

https://commitfest.postgresql.org/44/2837/

The discussion is here:

https://www.postgresql.org/message-id/flat/ca+q6zcwtubt_sxj0v6hy6ez89uv5wug5aefpe_9n0jr3vwn...@mail.gmail.com

You could comment on that patch or review it.  Useful reviews and supporting
comments help move the patch forward.  That would best serve your interests.

Yours,
Laurenz Albe

Re: Operating of synchronous master when no standby is available

[Sergey Cherukhin]

I already have rep_mode set to "sync", but the primary node switches to
async mode only if the replica is shutted down correctly. When I shut down
the replica by powering it off to emulate power unit failure, primary
remains in sync mode until "pcs resource cleanup" is performed. I think it
is possible to run resource cleanup in script on cluster alert.

вт, 3 окт. 2023 г. в 05:13, MATSUO Takatoshi :

> Pgsql RA  has rep_mode=sync parameter.
> https://wiki.clusterlabs.org/wiki/PgSQL_Replicated_Cluster
>
> 2023年10月2日(月) 23:48 Sergey Cherukhin :
>
>> Hello!
>>
>> I use Postgresql+Pacemaker+Corosync cluster with 2 Postgresql instances
>> in synchronous replication mode. When one of the nodes is down, clients
>> hang on INSERT operations because the primary server waits until standby
>> confirms that it got the data, but the standby node is down.
>>
>> I need clients to feel no difference when they operate with a fully
>> operational cluster or with a degraded cluster containing primary
>> node alone.
>>
>> Is it possible to configure synchronous replication to operate normally
>> when primary and replica are online, but primary no waits confirmation from
>> replica if replica is not connected?
>>
>> Best regards,
>> Sergey Cherukhin
>>
>>

Re: pg_stat_statements IN problem

[David Rowley]

On Tue, 3 Oct 2023 at 21:07,  wrote:
> P.S.: The only serious discussion I was able to find about it was from 2015 
> here, everyone basically stating that the improvement would be useful. 
> https://postgrespro.com/list/thread-id/1880012

There is some active discussion and a patch which aims to improve the
situation in [1].

Perhaps, if you're in a position to, you could help review that.

David

[1] 
https://postgr.es/m/flat/CA%2Bq6zcWtUbT_Sxj0V6HY6EZ89uv5wuG5aefpe_9n0Jr3VwntFg%40mail.gmail.com

Re: How to investigate deadlocks

[Andreas Kretschmer]

Am 02.10.23 um 13:27 schrieb Matthias Apitz:

Hello,

One of our clients running our LMS on top of PostgreSQL 13.1 created a
ticket with these messages:

2023-09-30 16:50:50.951 CEST [18117] ERROR:  deadlock detected
2023-09-30 16:50:50.951 CEST [18117] DETAIL:  Process 18117 waits for ShareLock 
on transaction 150396154; blocked by process 18187.
Process 18187 waits for ShareLock on transaction 150396155; blocked by 
process 18117.
Process 18117: fetch hc_d03geb
Process 18187: fetch hc_d02ben
2023-09-30 16:50:50.951 CEST [18117] HINT:  See server log for query details.
2023-09-30 16:50:50.951 CEST [18117] CONTEXT:  while locking tuple (38,57) in relation 
"d03geb"
2023-09-30 16:50:50.951 CEST [18117] STATEMENT:  fetch hc_d03geb

The shown PIDs for sure are the ones of the Pos backend proc (on Linux).
Is there any chance to investigate it further?


please also check 
https://www.cybertec-postgresql.com/en/postgresql-understanding-deadlocks/



Andreas

--
Andreas Kretschmer
Technical Account Manager (TAM)
www.enterprisedb.com

Re: pg_stat_statements IN problem

[byme]

Thank you for response. Unfortunately, I have to update one section which I 
wrote wrong, it should have
been this way:

"This obfuscates our monitoring because the same query with different amount of 
arguments gets translated into this:
IN ($1, $2)
and so on."

The questions are:
1. Shouldnt IN behave so that the query in pg_stat_statements would look like 
this:
IN $1
2. Shouldnt there be at least some flag to aggregate such queries into one?
3. Is there any workaround how to aggregate those queries except the "= ANY"?
4. How come no one is bothered by this if this makes pg_stat_statements 
unusable with lots of queries using IN, what others do with this problem?
5. what do you mean by changing pg_stat_statements with another view/table?

LJ

P.S.: The only serious discussion I was able to find about it was from 2015 
here, everyone basically stating that the improvement would be useful. 
https://postgrespro.com/list/thread-id/1880012



Sent with Proton Mail secure email.

--- Original Message ---
On Monday, October 2nd, 2023 at 8:50 PM, Wim Bertels  
wrote:


> byme@byme.email schreef op ma 02-10-2023 om 16:19 [+]:
> 
> > Is there a possibility the pg_stat_statements will be improved with
> > handling IN? This problem makes it so much less useful right now.
> 
> 
> not sure what the question is,
> but if you change pg_stat_statements with another view/table,
> the problem/answer would be the same
> 
> https://www.postgresql.org/docs/current/functions-comparisons.html#FUNCTIONS-COMPARISONS-IN-SCALAR

Re: How to force "re-TOAST" after changing STORAGE or COMPRESSION?

[Dominique Devienne]

On Tue, Oct 3, 2023 at 9:17 AM Laurenz Albe 
wrote:

> On Tue, 2023-10-03 at 09:08 +0200, Dominique Devienne wrote:
> >  In my case, it's OK not to be transactional, for these experiments. Is
> there a way
> > to lock the table and do the rewriting w/o generating any WAL? I don't
> have any experience
> > with unlogged tables, but should I take an exclusive lock on the table,
> switch it to unlogged,
> > rewrite, and switch it back to logged?
>
> The only way to avoid WAL is to use unlogged tables, but they lose their
> data after
> a crash.  If you change an unlogged table to a logged table, the whole
> table ends up
> in WAL, so you won't save anything that way.
>

Thanks Laurenz and Michael. I didn't know that. Interesting.
And logical I guess, considering replication.


> The best thing that you can do to reduce the amount of WAL is to TRUNCATE
> and
> populate the table in the same transaction.  Then, if you have "wal_level
> = minimal",
> PostgreSQL can forego writing WAL information.  But you cannot have
> archive recovery
> and replication with "wal_level = minimal"


No idea about the granularity of wal_level (cluster wide? db wide? more
granular?),
but that doesn't sound practical on a cluster shared with others, I suspect.

It would also force to load all the bytea values client-side, instead of
1-by-1, which
is possible (a few GBs typically, max), but not the way we've coded it.


> > What about my last question about whether storage=extended always being
> compressed?
> > Given that I don't see much compression, at least when looking
> indirectly via total-rel-sizes?
> > Is there a way to evaluate the compression ratios achieved on TOASTED
> values?
>
> If you don't see much compression in your toasted data, it is possible
> that you are
> storing data that are already compressed.  In that case, PostgreSQL will
> attempt
> compression with its algorithms that are geared at speed rather than good
> compression.
> If it finds that the data grew after compression, it will discard the
> compressed value
> and continue with the original value.  To avoid that useless compression
> attempt,
> you should use STORAGE EXTERNAL in such cases.
>

I know for sure the bytea values are not compressed. Again, they are
numerical arrays
(float, double), which don't typically compress well, especially the
mantissa part. So it
sounds like there's no way to examine the hidden TOASTed values for
compression ratio then.
Bummer. Thanks again Laurenz. --DD

Re: How to force "re-TOAST" after changing STORAGE or COMPRESSION?

[Laurenz Albe]

On Tue, 2023-10-03 at 09:08 +0200, Dominique Devienne wrote:
>  In my case, it's OK not to be transactional, for these experiments. Is there 
> a way
> to lock the table and do the rewriting w/o generating any WAL? I don't have 
> any experience
> with unlogged tables, but should I take an exclusive lock on the table, 
> switch it to unlogged,
> rewrite, and switch it back to logged?

The only way to avoid WAL is to use unlogged tables, but they lose their data 
after
a crash.  If you change an unlogged table to a logged table, the whol table 
ends up
in WAL, so you won't save anything that way.

The best thing that you can do to reduce the amount of WAL is to TRUNCATE and
populate the table in the same transaction.  Then, if you have "wal_level = 
minimal",
PostgreSQL can forego writing WAL information.  But you cannot have archive 
recovery
and replication with "wal_level = minimal".

> What about my last question about whether storage=extended always being 
> compressed?
> Given that I don't see much compression, at least when looking indirectly via 
> total-rel-sizes?
> Is there a way to evaluate the compression ratios achieved on TOASTED values?

If you don't see much compression in your toasted data, it is possible that you 
are
storing data that are already compressed.  In that case, PostgreSQL will attempt
compression with its algorithms that are geared at speed rather than good 
compression.
If it finds that the data grew after compression, it will discard the 
compressed value
and continue with the original value.  To avoid that useless compression 
attempt,
you should use STORAGE EXTERNAL in such cases.

Yours,
Laurenz Albe

Re: How to force "re-TOAST" after changing STORAGE or COMPRESSION?

[Michael Paquier]

On Tue, Oct 03, 2023 at 09:08:49AM +0200, Dominique Devienne wrote:
>  In my case, it's OK not to be transactional, for these experiments. Is
> there a way
> to lock the table and do the rewriting w/o generating any WAL? I don't have
> any experience
> with unlogged tables, but should I take an exclusive lock on the table,
> switch it to unlogged,
> rewrite, and switch it back to logged?

Switching a table back to be logged requires all its 8k blocks to be
WAL-logged, so that would be roughly the same as a plain UPDATE.
--
Michael


signature.asc
Description: PGP signature

Re: How to force "re-TOAST" after changing STORAGE or COMPRESSION?

[Dominique Devienne]

On Tue, Oct 3, 2023 at 6:45 AM Michael Paquier  wrote:

> On Tue, Oct 03, 2023 at 06:31:27AM +0200, Laurenz Albe wrote:
> > On Tue, 2023-10-03 at 12:33 +1100, rob stone wrote:
> >> Would running CLUSTER on the table use the new parameters for the re-
> >> write?
> >
> > No, as far as I know.
>
> Note that under the hoods VACUUM FULL and CLUSTER use the same code
> paths when doing their stuff.
>
> > You'd need something like
> >   -- rewrite all tuples
> >   UPDATE tab SET id = id;
> >   -- get rid of the bloat
> >   VACUUM (FULL) tab;
>

OK. I didn't expect this, but I can deal with it. Especially since this is
basically what I'm
doing anyway. Remember the thread on this ML about "chunking" large bytea
values?
Well, this is about trying out several chunk sizes and/or compression, to
find the right
config / tuning for our access patterns. We've already done the
"rechunking", and I'm adding
the changes in compression (and thus storage, when disabling compression).


> I'm afraid so, and get ready for a burst of WAL that depends on the
> size of your relation if you are too aggressive with the updates.  You
> could do that in periodic steps, as well.
>

 In my case, it's OK not to be transactional, for these experiments. Is
there a way
to lock the table and do the rewriting w/o generating any WAL? I don't have
any experience
with unlogged tables, but should I take an exclusive lock on the table,
switch it to unlogged,
rewrite, and switch it back to logged?

What about my last question about whether storage=extended always being
compressed?
Given that I don't see much compression, at least when looking indirectly
via total-rel-sizes?
Is there a way to evaluate the compression ratios achieved on TOASTED
values?

Thanks, --DD