Re: Out of memory with "create extension postgis"

[Daniel Westermann (DWE)]

>> Here is a new one with bt at the end:

>That's just showing the stack when the backend is idle waiting for input.
>We need to capture the stack at the moment when the "out of memory" error
>is reported (errfinish() should be the top of stack).

Then I don't know what/how to do it. Here is a complete trace of what we did 
today:

-- First session 

$ psql
# select pg_backend_pid();
 pg_backend_pid

  22480
(1 row)


-- Second session

# gdb -p 22480
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-115.el7
Copyright (C) 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later 
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
.
Attaching to process 22480
Reading symbols from /usr/pgsql-12/bin/postgres...Reading symbols from 
/usr/lib/debug/usr/pgsql-12/bin/postgres.debug...done.
done.
Reading symbols from /lib64/libpthread.so.0...(no debugging symbols 
found)...done.
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /lib64/libxml2.so.2...Reading symbols from 
/lib64/libxml2.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libxml2.so.2
Reading symbols from /lib64/libpam.so.0...Reading symbols from 
/lib64/libpam.so.0...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libpam.so.0
Reading symbols from /lib64/libssl.so.10...Reading symbols from 
/lib64/libssl.so.10...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libssl.so.10
Reading symbols from /lib64/libcrypto.so.10...Reading symbols from 
/lib64/libcrypto.so.10...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libcrypto.so.10
Reading symbols from /lib64/libgssapi_krb5.so.2...Reading symbols from 
/lib64/libgssapi_krb5.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libgssapi_krb5.so.2
Reading symbols from /lib64/librt.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/librt.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libldap-2.4.so.2...Reading symbols from 
/lib64/libldap-2.4.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libldap-2.4.so.2
Reading symbols from /lib64/libicui18n.so.50...Reading symbols from 
/lib64/libicui18n.so.50...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libicui18n.so.50
Reading symbols from /lib64/libicuuc.so.50...Reading symbols from 
/lib64/libicuuc.so.50...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libicuuc.so.50
Reading symbols from /lib64/libsystemd.so.0...Reading symbols from 
/lib64/libsystemd.so.0...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libsystemd.so.0
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols 
found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /lib64/libz.so.1...Reading symbols from 
/lib64/libz.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libz.so.1
Reading symbols from /lib64/liblzma.so.5...Reading symbols from 
/lib64/liblzma.so.5...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/liblzma.so.5
Reading symbols from /lib64/libaudit.so.1...Reading symbols from 
/lib64/libaudit.so.1...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libaudit.so.1
Reading symbols from /lib64/libkrb5.so.3...Reading symbols from 
/lib64/libkrb5.so.3...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libkrb5.so.3
Reading symbols from /lib64/libcom_err.so.2...Reading symbols from 
/lib64/libcom_err.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Loaded symbols for /lib64/libcom_err.so.2
Reading symbols from /lib64/libk5crypto.so.3...Reading symbols from 
/lib64/libk5crypto.so.3...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Lo

How to get the name of the current database in C function/extention inside a trigger?

[alex m]

I'm writting a function/extention in C for a trigger. Inside a trigger, 
in C, I want to get the name of the current database. However, not via 
SPI_exec(), SPI_prepare() and the like, but more directly, in a more 
faster way.


I'm aware of "current_database()" but it'll require calling it via 
SPI_exec()


I've found this code which is  implementation of 'conninfo' command:

https://github.com/LuberZheng/FAS_Sniffer/blob/84872cb2360ce12219970b828e4377e5d3a91bb8/postgres/postgresql-9.5.4/src/bin/psql/command.c#L343

but it's still unclear how to do the same thing from a C function inside 
for a trigger.



How to do it?


P.S.

While SPI_exec(), SPI_prepare() won't work for me, other SPI functions 
such SPI_get* will be ok.


Versions: 11 or 12



For the versions 11 or 12

Re: how reliable is pg_rewind?

[Curt Kolovson]

Thanks, Paul and Michael. I forgot to mention that we're using postgres
v10.12.

On Sun, Aug 2, 2020 at 10:29 PM Paul Förster 
wrote:

> Hi Curt, hi Michael,
>
> > On 03. Aug, 2020, at 03:58, Michael Paquier  wrote:
> >
> > On Sat, Aug 01, 2020 at 10:35:37AM -0700, Curt Kolovson wrote:
> >> Any info on the reliability of pg_rewind and its limitations would be
> appreciated.
> >
> > FWIW, we use it in production to accelerate the redeployment of
> > standbys in HA configuration for 4 years now in at least one product,
> > and it is present in upstream for since 9.5, for 5 years now.  So the
> > tool is rather baked at this stage of the game.
>
> same here. We use it with Patroni in failover cluster setups for about 2-3
> years now. It has not failed us yet.
>
> Cheers,
> Paul
>
>

Re: Out of memory with "create extension postgis"

[Daniel Westermann (DWE)]

>I am aware that the behavior is different from what we've seen last week but 
>this is how it looks today.
>Anything we missed or did not do correct?

Finally this can be re-produced quite easily by installing this extension: 
https://de.osdn.net/projects/pgstoreplans/downloads/72297/pg_store_plans12-1.4-1.el7.x86_64.rpm/
As soon as pg_store_plans is in shared_preload_libraries the postgis extension 
can not be installed anymore. This is the exact package list:

[root@rhel77 ~]# rpm -qa | egrep "postgres|postgis|store_plan"
postgresql12-server-12.3-5PGDG.rhel7.x86_64
postgresql12-contrib-12.3-5PGDG.rhel7.x86_64
postgresql12-12.3-5PGDG.rhel7.x86_64
postgis30_12-3.0.1-5.rhel7.x86_64
pg_store_plans12-1.4-1.el7.x86_64
postgresql12-libs-12.3-5PGDG.rhel7.x86_64
postgresql12-llvmjit-12.3-5PGDG.rhel7.x86_64

I will open an issue on the extensions' GitHub repo.

Regards
Daniel

Re: How to get the name of the current database in C function/extention inside a trigger?

[David Rowley]

On Mon, 3 Aug 2020 at 21:26, alex m  wrote:
> I'm writting a function/extention in C for a trigger. Inside a trigger, in C, 
> I want to get the name of the current database. However, not via SPI_exec(), 
> SPI_prepare() and the like, but more directly, in a more faster way.

You can use MyDatabaseId in miscadmin.h

If you git grep MyDatabaseId in src/contrib you can see some examples.

A good tip for the future would be to just look at the source code of
the built-in function and see how it does it.

David

How can you find out what point logical replication is at?

[John Ashmead]

I have logical replication setup from a factory in Zhuhai China to a data 
warehouse in New Jersey. We are using postgresql 10.13 on both sides, on Redhat 
Linux 7.6.

The logical replication has been in “catchup” mode for several days now, stuck 
at a specific LSN (9EF/89ADF7E0). The slave side seems to be chugging along, 
generating lots of WAL files — but not actually getting any new records in. 

The logical replication is being used to move some fairly large files:  
averaging about 1 MB but with a max up to about 250 MB. (I think I want to fix 
that longer term, but that’s not a quick fix.)

My working hypothesis is that we are stuck on a long transaction: that we can’t 
get some abnormally large blob over before we drop the line.  In this case 
fixing the connection should fix the problem.

Is there a way I can see what is going on?  The slave is working hard, but what 
is it working on?

And are there any suggestions on how to handle this?

I could restart the logical replication with “copy_data = false”, then fill in 
the holes by hand.  But I would rather not!

Thanks in advance!

John

PS. Output of pg_stat_replication & pg_stat_subscription on master & slave 
respectively.  (Some proprietary information X’d out)

select * from pg_stat_replication;
-[ RECORD 1 ]+--
pid  | 42451
usesysid | 10
usename  | postgres
application_name | china_to_nj_sub
client_addr  | XXX.XXX.XXX.XXX
client_hostname  | 
client_port  | 54300
backend_start| 2020-08-03 09:07:07.257454-04
backend_xmin | 16574498
state| catchup
sent_lsn | 9EF/89ADF7E0
write_lsn| 9EF/89ADF7E0
flush_lsn| 9EF/89ADF7E0
replay_lsn   | 9EF/89ADF7E0
write_lag| 
flush_lag| 
replay_lag   | 
sync_priority| 0
sync_state   | async

 select * from pg_stat_subscription;
-[ RECORD 1 ]-+--
subid | 7222699
subname   | china_to_nj_sub
pid   | 14764
relid | 
received_lsn  | 9EF/89ADF7E0
last_msg_send_time| 2020-08-03 10:15:48.644575-04
last_msg_receipt_time | 2020-08-03 10:14:57.247993-04
latest_end_lsn| 9EF/89ADF7E0
latest_end_time   | 2020-08-03 09:30:57.974223-04


John Ashmead
139 Montrose Avenue
Rosemont, PA, 19010-1508
(610) 527 9560 
mobile (610) 247 2323
john.ashm...@ashmeadsoftware.com 








smime.p7s
Description: S/MIME cryptographic signature

Re: 12.3 replicas falling over during WAL redo

[Ben Chobot]

Kyotaro Horiguchi wrote on 8/2/20 9:39 PM:

At Sat, 1 Aug 2020 09:58:05 -0700, Ben Chobot  wrote in
All of the cited log lines seem suggesting relation with deleted btree
page items. As a possibility I can guess, that can happen if the pages
were flushed out during a vacuum after the last checkpoint and
full-page-writes didn't restored the page to the state before the
index-item deletion happened(that is, if full_page_writes were set to
off.). (If it found to be the cause, I'm not sure why that didn't
happen on 9.5.)

regards.


We have always had full_page_writes enabled.

Re: How to get the name of the current database in C function/extention inside a trigger?

[alex m]

Thanks.


El 03/08/2020 a las 16:04, David Rowley escribió:


On Mon, 3 Aug 2020 at 21:26, alex m  wrote:

I'm writting a function/extention in C for a trigger. Inside a trigger, in C, I 
want to get the name of the current database. However, not via SPI_exec(), 
SPI_prepare() and the like, but more directly, in a more faster way.

You can use MyDatabaseId in miscadmin.h

If you git grep MyDatabaseId in src/contrib you can see some examples.

A good tip for the future would be to just look at the source code of
the built-in function and see how it does it.

David

Keeping state in a foreign data wrapper

[Stelios Sfakianakis]

Hi,

I am trying to  implement a FDW in Postgres for accessing a web api and I would 
like to keep information like for example the total number of requests 
submiited. Ideally these data should be kept “per-user” and of course with the 
proper locking to eliminate race conditions. So the question I have is how to 
design such a global (but per user and database) state, using the C FDW API of 
Postgres. I have gone through the API and for example I see various fdw_private 
fields in the query planning structures and callbacks but these do not seem to 
be relevant to my requirements. Another possiblity seems to be to use shared 
memory (https://www.postgresql.org/docs/12/xfunc-c.html#id-1.8.3.13.14) but it 
is even less clear how to do it.

So is there any simple way to do implement such shared thread/process safe 
state?
Thank you
Stelios

How to rebuild index efficiently

[Konireddy Rajashekar]

Hi Team,

i have a table of size 2.6TB which is very prone to updates and inserts so
we have tuned autovacuum to run on it very aggressively , so the table
level bloat is fine .
Now we are facing issue with indexes on this table. the total size  of all
indexes  on this table is around 2.4TB.

There is an unique index of 1.2TB size out of which 850 GB is bloat ,
creating another index concurrently is taking lot of time and we cannot
offer down time to reindex this index.

Could you please suggest any ideal approach to tackle this ?

Postgres version:9.6.15

postgres=> \dt+ cust_table
 List of relations
 Schema |  Name   | Type  | Owner  |  Size   | Description
+-+---++-+-
 public | cust_table | table | raj | 2685 GB |
(1 row)


Index bloat:

database_name | schema_name |  table_name   |
index_name| bloat_pct | bloat_mb |  index_mb   |  table_mb   |
index_scans
---+-+---+-+---+--+-+-+-
 postgres | public  | cust_table  |
unique_event_type_action_id |69 |   884477 | 1285743.648 |
2749094.070 |   342466359




postgres=> \di+ unique_event_type_action_id
List of relations
 Schema |Name | Type  | Owner  |  Table  |
 Size   | Description
+-+---++-+-+-
 public | unique_event_type_action_id | index | raj |cust_table | 1256 GB |


Regards,
Raj

Re: How to rebuild index efficiently

[Christophe Pettus]

> On Aug 3, 2020, at 10:20, Konireddy Rajashekar  wrote:
> Could you please suggest any ideal approach to tackle this ?

You can do CREATE INDEX CONCURRENTLY to build a new index with the same 
definition, and when that is complete, drop the old index.  The locking that is 
required here is modest: CREATE INDEX CONCURRENTLY needs to lock the table 
briefly at a couple of points in the operation, and dropping the old index 
requires a brief lock on the table.  It is, however, much less overall lock 
time than REINDEX would be.

--
-- Christophe Pettus
   x...@thebuild.com

Re: How to rebuild index efficiently

[Ron]

On 8/3/20 12:58 PM, Christophe Pettus wrote

On Aug 3, 2020, at 10:20, Konireddy Rajashekar  wrote:
Could you please suggest any ideal approach to tackle this ?

You can do CREATE INDEX CONCURRENTLY to build a new index with the same 
definition, and when that is complete, drop the old index.  The locking that is 
required here is modest: CREATE INDEX CONCURRENTLY needs to lock the table 
briefly at a couple of points in the operation, and dropping the old index 
requires a brief lock on the table.  It is, however, much less overall lock 
time than REINDEX would be.


Of course, you need enough disk space... :)

--
Angular momentum makes the world go 'round.

Re: How to rebuild index efficiently

[Michael Lewis]

>
> creating another index concurrently is taking lot of time
>

Could you increase maintenance_work_mem significantly or is that already
quite high?

Re: 12.3 replicas falling over during WAL redo

[Peter Geoghegan]

On Sun, Aug 2, 2020 at 9:39 PM Kyotaro Horiguchi
 wrote:
> All of the cited log lines seem suggesting relation with deleted btree
> page items. As a possibility I can guess, that can happen if the pages
> were flushed out during a vacuum after the last checkpoint and
> full-page-writes didn't restored the page to the state before the
> index-item deletion happened(that is, if full_page_writes were set to
> off.). (If it found to be the cause, I'm not sure why that didn't
> happen on 9.5.)

There is also a Heap/HOT_UPDATE log line with similar errors.

-- 
Peter Geoghegan

Re: 12.3 replicas falling over during WAL redo

[Ben Chobot]

Ben Chobot wrote on 8/1/20 9:58 AM:

Alvaro Herrera wrote on 8/1/20 9:35 AM:

On 2020-Aug-01, Ben Chobot wrote:

Can you find out what the index is being modified by those LSNs -- is it
always the same index?  Can you have a look at nearby WAL records that
touch the same page of the same index in each case?

One possibility is that the storage forgot a previous write.


So here is me fumbling how to do this. Hopefully this is useful?

The error for this host was:

2020-07-30T14:59:36.839243+00:00 host postgres[24338]: [45253-1] 
db=,user= WARNING:  specified item offset is too large
2020-07-30T14:59:36.839307+00:00 host postgres[24338]: [45253-2] 
db=,user= CONTEXT:  WAL redo at A0A/AC4204A0 for Btree/INSERT_LEAF: off 48
2020-07-30T14:59:36.839337+00:00 host postgres[24338]: [45254-1] 
db=,user= PANIC:  btree_xlog_insert: failed to add item
2020-07-30T14:59:36.839366+00:00 host postgres[24338]: [45254-2] 
db=,user= CONTEXT:  WAL redo at A0A/AC4204A0 for Btree/INSERT_LEAF: off 48
2020-07-30T14:59:37.587173+00:00 host postgres[24337]: [11-1] db=,user= 
LOG:  startup process (PID 24338) was terminated by signal 6: Aborted
2020-07-30T14:59:37.587266+00:00 host postgres[24337]: [12-1] db=,user= 
LOG:  terminating any other active server processes


So, starting from a filesystem snapshot where the db had crashed and not 
yet been started, I did:


/usr/lib/postgresql/12/bin/pg_waldump -p 
/var/lib/postgresql/12/main/pg_wal/ 00010A0A00AB 
00010A0A00AD | grep A0A/AC4204A0


This shows me:

rmgr: Btree   len (rec/tot): 72/    72, tx:   76396065, lsn: 
A0A/AC4204A0, prev A0A/AC420450, desc: INSERT_LEAF off 48, blkref #0: 
rel 16605/16613/60529051 blk 6501


So then I did:

/usr/lib/postgresql/12/bin/pg_waldump -p 
/var/lib/postgresql/12/main/pg_wal/ 00010A0A00AB 
00010A0A00AD | grep 16605/16613/60529051
rmgr: Btree   len (rec/tot): 53/   813, tx:   76393363, lsn: 
A0A/AB289778, prev A0A/AB287F30, desc: INSERT_LEAF off 26, blkref #0: 
rel 16605/16613/60529051 blk 6089 FPW
rmgr: Btree   len (rec/tot): 72/    72, tx:   76393390, lsn: 
A0A/AB2BE870, prev A0A/AB2BD6E0, desc: INSERT_LEAF off 128, blkref #0: 
rel 16605/16613/60529051 blk 6519
rmgr: Btree   len (rec/tot): 72/    72, tx:   76393394, lsn: 
A0A/AB2C43D0, prev A0A/AB2C4378, desc: INSERT_LEAF off 41, blkref #0: 
rel 16605/16613/60529051 blk 6501
rmgr: Btree   len (rec/tot): 72/    72, tx:   76393992, lsn: 
A0A/AB5FA848, prev A0A/AB5FA7F0, desc: INSERT_LEAF off 133, blkref #0: 
rel 16605/16613/60529051 blk 5999
rmgr: Btree   len (rec/tot): 72/    72, tx:   76394938, lsn: 
A0A/ABE49738, prev A0A/ABE47AF8, desc: INSERT_LEAF off 189, blkref #0: 
rel 16605/16613/60529051 blk 6519
rmgr: Btree   len (rec/tot): 72/    72, tx:   76394977, lsn: 
A0A/ABEB0330, prev A0A/ABEB02D8, desc: INSERT_LEAF off 204, blkref #0: 
rel 16605/16613/60529051 blk 6519
rmgr: Btree   len (rec/tot): 72/    72, tx:   76396065, lsn: 
A0A/AC4204A0, prev A0A/AC420450, desc: INSERT_LEAF off 48, blkref #0: 
rel 16605/16613/60529051 blk 6501
rmgr: Btree   len (rec/tot): 72/    72, tx:   76396075, lsn: 
A0A/AC427518, prev A0A/AC4274C0, desc: INSERT_LEAF off 211, blkref #0: 
rel 16605/16613/60529051 blk 6519
rmgr: Btree   len (rec/tot): 53/  2529, tx:   76396250, lsn: 
A0A/AC5352F8, prev A0A/AC533A00, desc: INSERT_LEAF off 87, blkref #0: 
rel 16605/16613/60529051 blk 5639 FPW
pg_waldump: fatal: error in WAL record at A0A/AC5411B0: invalid resource 
manager ID 110 at A0A/AC5411E0


...and I have no idea what I'm looking at. I assume/hope the error at 
the end is due to the db shutting down, and nothing to be particularly 
worried about?


I have the specific index that postgres tripped on here and I'll spend 
some time today trying to see if the other crashes were the same index.

Re: 12.3 replicas falling over during WAL redo

[Alvaro Herrera]

On 2020-Aug-03, Ben Chobot wrote:

> rmgr: Btree   len (rec/tot): 72/    72, tx:   76396065, lsn:
> A0A/AC4204A0, prev A0A/AC420450, desc: INSERT_LEAF off 48, blkref #0: rel
> 16605/16613/60529051 blk 6501
> 
> So then I did:
> 
> /usr/lib/postgresql/12/bin/pg_waldump -p /var/lib/postgresql/12/main/pg_wal/
> 00010A0A00AB 00010A0A00AD | grep
> 16605/16613/60529051

Yep. Looking at the ones in block 6501,

> rmgr: Btree   len (rec/tot): 72/    72, tx:   76393394, lsn:
> A0A/AB2C43D0, prev A0A/AB2C4378, desc: INSERT_LEAF off 41, blkref #0: rel
> 16605/16613/60529051 blk 6501

> rmgr: Btree   len (rec/tot): 72/    72, tx:   76396065, lsn:
> A0A/AC4204A0, prev A0A/AC420450, desc: INSERT_LEAF off 48, blkref #0: rel
> 16605/16613/60529051 blk 6501

My question was whether the block has received the update that added the
item in offset 41; that is, is the LSN in the crashed copy of the page
equal to A0A/AB2C43D0?  If it's an older value, then the write above was
lost for some reason.

> pg_waldump: fatal: error in WAL record at A0A/AC5411B0: invalid resource
> manager ID 110 at A0A/AC5411E0
> 
> ...and I have no idea what I'm looking at. I assume/hope the error at the
> end is due to the db shutting down, and nothing to be particularly worried
> about?

Yeah.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: 12.3 replicas falling over during WAL redo

[Ben Chobot]

Alvaro Herrera wrote on 8/3/20 12:34 PM:

On 2020-Aug-03, Ben Chobot wrote:

Yep. Looking at the ones in block 6501,


rmgr: Btree   len (rec/tot): 72/    72, tx:   76393394, lsn:
A0A/AB2C43D0, prev A0A/AB2C4378, desc: INSERT_LEAF off 41, blkref #0: rel
16605/16613/60529051 blk 6501
rmgr: Btree   len (rec/tot): 72/    72, tx:   76396065, lsn:
A0A/AC4204A0, prev A0A/AC420450, desc: INSERT_LEAF off 48, blkref #0: rel
16605/16613/60529051 blk 6501

My question was whether the block has received the update that added the
item in offset 41; that is, is the LSN in the crashed copy of the page
equal to A0A/AB2C43D0?  If it's an older value, then the write above was
lost for some reason.


How do I tell?

Configuring only SSL in postgres docker image

[Shankar Bhaskaran]

Hi ,

I am trying to enable ssl on postgres docker image . The postgres  image
should only support SSL . I did configure the  command:" -c ssl=on -c
ssl_key_file=/opt/postgresql/server.key -c
ssl_cert_file=/opt/postgresql/server.crt"   in the docker compose file.
I also added the following entry in the pg_hba.conf after commenting all
other host entries
"hostssl   all  all   all md5".

But I am able to make ssl as well as non ssl connections to the postgres
image.

My observation is the command only updates the postmaster.opts file and not
the postgres.conf file .

Could I get some help

Regards,
Shankar

Re: 12.3 replicas falling over during WAL redo

[Ben Chobot]

Peter Geoghegan wrote on 8/3/20 11:25 AM:

On Sun, Aug 2, 2020 at 9:39 PM Kyotaro Horiguchi
 wrote:

All of the cited log lines seem suggesting relation with deleted btree
page items. As a possibility I can guess, that can happen if the pages
were flushed out during a vacuum after the last checkpoint and
full-page-writes didn't restored the page to the state before the
index-item deletion happened(that is, if full_page_writes were set to
off.). (If it found to be the cause, I'm not sure why that didn't
happen on 9.5.)

There is also a Heap/HOT_UPDATE log line with similar errors.


Yes, and I have the pg_waldump output for it. But, that table is quite 
large, and the transaction that contains the LSN in the error log is 
1,752 waldump lines long. I'm happy to share what would be useful to 
help debug it but I'm guessing it should be a subset of that.

Re: 12.3 replicas falling over during WAL redo

[Ben Chobot]

Alvaro Herrera wrote on 8/1/20 9:35 AM:

On 2020-Aug-01, Ben Chobot wrote:

Can you find out what the index is being modified by those LSNs -- 
is it

always the same index?  Can you have a look at nearby WAL records that
touch the same page of the same index in each case?


They turn out to be different indices on different tables.

Re: 12.3 replicas falling over during WAL redo

[Alvaro Herrera]

On 2020-Aug-03, Ben Chobot wrote:

> Alvaro Herrera wrote on 8/3/20 12:34 PM:
> > On 2020-Aug-03, Ben Chobot wrote:
> > 
> > Yep. Looking at the ones in block 6501,
> > 
> > > rmgr: Btree   len (rec/tot): 72/    72, tx:   76393394, lsn:
> > > A0A/AB2C43D0, prev A0A/AB2C4378, desc: INSERT_LEAF off 41, blkref #0: rel
> > > 16605/16613/60529051 blk 6501
> > > rmgr: Btree   len (rec/tot): 72/    72, tx:   76396065, lsn:
> > > A0A/AC4204A0, prev A0A/AC420450, desc: INSERT_LEAF off 48, blkref #0: rel
> > > 16605/16613/60529051 blk 6501
> > My question was whether the block has received the update that added the
> > item in offset 41; that is, is the LSN in the crashed copy of the page
> > equal to A0A/AB2C43D0?  If it's an older value, then the write above was
> > lost for some reason.
> 
> How do I tell?

You can use pageinspect's page_header() function to obtain the page's
LSN.  You can use dd to obtain the page from the file,

dd if=16605/16613/60529051 bs=8192 count=1 seek=6501 of=/tmp/page.6501 

then put that binary file in a bytea column, perhaps like

create table page (raw bytea);
insert into page select pg_read_binary_file('/tmp/page');

and with that you can run page_header:

create extension pageinspect;
select h.* from page, page_header(raw) h;

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: 12.3 replicas falling over during WAL redo

[Peter Geoghegan]

On Mon, Aug 3, 2020 at 2:35 PM Alvaro Herrera  wrote:
> You can use pageinspect's page_header() function to obtain the page's
> LSN.  You can use dd to obtain the page from the file,
>
> dd if=16605/16613/60529051 bs=8192 count=1 seek=6501 of=/tmp/page.6501

Ben might find this approach to dumping out a single page image
easier, since it doesn't involve relfilenodes or filesystem files:

https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD#contrib.2Fpageinspect_page_dump

-- 
Peter Geoghegan

Re: 12.3 replicas falling over during WAL redo

[Ben Chobot]

Peter Geoghegan wrote on 8/3/20 3:04 PM:

On Mon, Aug 3, 2020 at 2:35 PM Alvaro Herrera  wrote:

You can use pageinspect's page_header() function to obtain the page's
LSN.  You can use dd to obtain the page from the file,

dd if=16605/16613/60529051 bs=8192 count=1 seek=6501 of=/tmp/page.6501

Ben might find this approach to dumping out a single page image
easier, since it doesn't involve relfilenodes or filesystem files:

https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD#contrib.2Fpageinspect_page_dump


I don't think that works, because it sounds like this requires I connect 
to the db, but that means I have to start it up. I am (wrongly?) 
assuming we should get what we can while the db is still in a 
just-crashed state.


Still, I seem to have mangled Alvaro's suggestion somehow, because this 
doesn't look right:


# select h.* from public.page , page_header(raw) h;
 lsn | checksum | flags | lower | upper | special | pagesize | version 
| prune_xid

-+--+---+---+---+-+--+-+---
 0/0 |    0 | 0 | 0 | 0 |   0 |    0 |   0 
| 0

(1 row)

I'll keep trying though.

Re: 12.3 replicas falling over during WAL redo

[Ben Chobot]

Alvaro Herrera wrote on 8/3/20 2:34 PM:

On 2020-Aug-03, Ben Chobot wrote:


Alvaro Herrera wrote on 8/3/20 12:34 PM:

On 2020-Aug-03, Ben Chobot wrote:

Yep. Looking at the ones in block 6501,


rmgr: Btree   len (rec/tot): 72/    72, tx:   76393394, lsn:
A0A/AB2C43D0, prev A0A/AB2C4378, desc: INSERT_LEAF off 41, blkref #0: rel
16605/16613/60529051 blk 6501
rmgr: Btree   len (rec/tot): 72/    72, tx:   76396065, lsn:
A0A/AC4204A0, prev A0A/AC420450, desc: INSERT_LEAF off 48, blkref #0: rel
16605/16613/60529051 blk 6501

My question was whether the block has received the update that added the
item in offset 41; that is, is the LSN in the crashed copy of the page
equal to A0A/AB2C43D0?  If it's an older value, then the write above was
lost for some reason.

How do I tell?

You can use pageinspect's page_header() function to obtain the page's
LSN.  You can use dd to obtain the page from the file,

dd if=16605/16613/60529051 bs=8192 count=1 seek=6501 of=/tmp/page.6501


If I use skip instead of seek


then put that binary file in a bytea column, perhaps like

create table page (raw bytea);
insert into page select pg_read_binary_file('/tmp/page');

and with that you can run page_header:

create extension pageinspect;
select h.* from page, page_header(raw) h;


 lsn  | checksum | flags | lower | upper | special | pagesize | 
version | prune_xid

--+--+---+---+---+-+--+-+---
 A0A/99BA11F8 | -215 | 0 |   180 |  7240 |    8176 | 8192 
|   4 | 0


As I understand what we're looking at, this means the WAL stream was 
assuming this page was last touched by A0A/AB2C43D0, but the page itself 
thinks it was last touched by A0A/99BA11F8, which means at least one 
write to the page is missing?

Re: How can you find out what point logical replication is at? -- or weird, slow, infinite loop

[John Ashmead]

This has gotten a bit weirder.

The replication is getting up to a specific LSN.  

Then it moves the sent_lsn to a number less than that, for a WAL file about an 
hour or more earlier and slowly walks forward again till it hit the same 
highwater mark.

So the replication seems to be stuck in some kind of a slow infinite loop.

Corrupted WAL file?  Any other ideas of what to look for?

TIA,

John

> On Aug 3, 2020, at 10:38 AM, John Ashmead  
> wrote:
> 
> I have logical replication setup from a factory in Zhuhai China to a data 
> warehouse in New Jersey. We are using postgresql 10.13 on both sides, on 
> Redhat Linux 7.6.
> 
> The logical replication has been in “catchup” mode for several days now, 
> stuck at a specific LSN (9EF/89ADF7E0). The slave side seems to be chugging 
> along, generating lots of WAL files — but not actually getting any new 
> records in. 
> 
> The logical replication is being used to move some fairly large files:  
> averaging about 1 MB but with a max up to about 250 MB. (I think I want to 
> fix that longer term, but that’s not a quick fix.)
> 
> My working hypothesis is that we are stuck on a long transaction: that we 
> can’t get some abnormally large blob over before we drop the line.  In this 
> case fixing the connection should fix the problem.
> 
> Is there a way I can see what is going on?  The slave is working hard, but 
> what is it working on?
> 
> And are there any suggestions on how to handle this?
> 
> I could restart the logical replication with “copy_data = false”, then fill 
> in the holes by hand.  But I would rather not!
> 
> Thanks in advance!
> 
> John
> 
> PS. Output of pg_stat_replication & pg_stat_subscription on master & slave 
> respectively.  (Some proprietary information X’d out)
> 
> select * from pg_stat_replication;
> -[ RECORD 1 ]+--
> pid  | 42451
> usesysid | 10
> usename  | postgres
> application_name | china_to_nj_sub
> client_addr  | XXX.XXX.XXX.XXX
> client_hostname  | 
> client_port  | 54300
> backend_start| 2020-08-03 09:07:07.257454-04
> backend_xmin | 16574498
> state| catchup
> sent_lsn | 9EF/89ADF7E0
> write_lsn| 9EF/89ADF7E0
> flush_lsn| 9EF/89ADF7E0
> replay_lsn   | 9EF/89ADF7E0
> write_lag| 
> flush_lag| 
> replay_lag   | 
> sync_priority| 0
> sync_state   | async
> 
>  select * from pg_stat_subscription;
> -[ RECORD 1 ]-+--
> subid | 7222699
> subname   | china_to_nj_sub
> pid   | 14764
> relid | 
> received_lsn  | 9EF/89ADF7E0
> last_msg_send_time| 2020-08-03 10:15:48.644575-04
> last_msg_receipt_time | 2020-08-03 10:14:57.247993-04
> latest_end_lsn| 9EF/89ADF7E0
> latest_end_time   | 2020-08-03 09:30:57.974223-04
> 
> 
> John Ashmead
> 139 Montrose Avenue
> Rosemont, PA, 19010-1508
> (610) 527 9560 
> mobile (610) 247 2323
> john.ashm...@ashmeadsoftware.com 
> 
> 
> 
> 
> 
> 

John Ashmead
139 Montrose Avenue
Rosemont, PA, 19010-1508
(610) 527 9560 
mobile (610) 247 2323
john.ashm...@ashmeadsoftware.com








smime.p7s
Description: S/MIME cryptographic signature

Re: 12.3 replicas falling over during WAL redo

[Alvaro Herrera]

On 2020-Aug-03, Ben Chobot wrote:

> Alvaro Herrera wrote on 8/3/20 2:34 PM:
> > On 2020-Aug-03, Ben Chobot wrote:

> > dd if=16605/16613/60529051 bs=8192 count=1 seek=6501 of=/tmp/page.6501
> 
> If I use skip instead of seek

Argh, yes, I did correct that in my test and forgot to copy and paste.

>  lsn  | checksum | flags | lower | upper | special | pagesize |
> version | prune_xid
> --+--+---+---+---+-+--+-+---
>  A0A/99BA11F8 | -215 | 0 |   180 |  7240 |    8176 | 8192
> |   4 | 0
> 
> As I understand what we're looking at, this means the WAL stream was
> assuming this page was last touched by A0A/AB2C43D0, but the page itself
> thinks it was last touched by A0A/99BA11F8, which means at least one write
> to the page is missing?

Yeah, that's exactly what we're seeing.  Somehow an older page version
was resurrected.  Of course, this should never happen.

So my theory has been proved.  What now?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: bad JIT decision

[David Rowley]

On Wed, 29 Jul 2020 at 09:28, Andres Freund  wrote:
> FWIW, I created a demo workload for this, and repro'ed the issue with
> that. Those improvements does make a very significant difference:

> Before:
> Timing: Generation 335.345 ms, Inlining 51.025 ms, Optimization 11967.776 
> ms, Emission 9201.499 ms, Total 21555.645 ms
> IR size: unoptimized: 9022868 bytes, optimized: 6206368 bytes
>
> After:
> Timing: Generation 261.283 ms, Inlining 30.875 ms, Optimization 1671.969 
> ms, Emission 18.557 ms, Total 1982.683 ms
> IR size: unoptimized 8776100 bytes, optimized 115868 bytes

That's a really impressive speedup.  However, no matter how fast we
make the compilation, it's still most likely to be a waste of time
doing it for plan nodes that are just not that costly.

I just wrote a patch to consider JIT on a per-plan-node basis instead
of globally over the entire plan. I'll post it to -hackers.

With a 1000 partition table where all of the cost is on just 1
partition, running a query that hits all partitions, I see:

Master jit=on:
 JIT:
   Functions: 3002
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 141.587 ms, Inlining 11.760 ms, Optimization
6518.664 ms, Emission 3152.266 ms, Total 9824.277 ms
 Execution Time: 12588.292 ms

Master jit=off:
 Execution Time: 3672.391 ms

Patched jit=on:
 JIT:
   Functions: 5
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 0.675 ms, Inlining 3.322 ms, Optimization 10.766
ms, Emission 5.892 ms, Total 20.655 ms
 Execution Time: 2754.160 ms

Most likely the EXPLAIN output will need to do something more than
show true/false for the options here, but I didn't want to go to too
much trouble unless this is seen as a good direction to go in.

> That obviously needs to be improved further, but it's already a lot
> better. In particular after these changes the generated code could be
> cached.

That would be a game-changer.

David

Re: Keeping state in a foreign data wrapper

[Ian Lawrence Barwick]

2020年8月4日(火) 1:24 Stelios Sfakianakis :
>
> Hi,
>
> I am trying to  implement a FDW in Postgres for accessing a web api and I 
> would like to keep information like for example the total number of requests 
> submiited. Ideally these data should be kept “per-user” and of course with 
> the proper locking to eliminate race conditions. So the question I have is 
> how to design such a global (but per user and database) state, using the C 
> FDW API of Postgres. I have gone through the API and for example I see 
> various fdw_private fields in the query planning structures and callbacks but 
> these do not seem to be relevant to my requirements. Another possiblity seems 
> to be to use shared memory 
> (https://www.postgresql.org/docs/12/xfunc-c.html#id-1.8.3.13.14) but it is 
> even less clear how to do it.

Shared memory would probably work; you'd need to load the FDW via
"shared_preload_libraries" and have the FDW handler function(s) update
shared memory with whatever statistics you want to track. You could
then define SQL functions to retrieve the stored values, and possibly
persist them over server restarts by storing/retrieving them from a
file.

Look at "pg_stat_statements" for a good example of how to do that kind of thing.

Regards

Ian Barwick

-- 
Ian Barwick   https://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Configuring only SSL in postgres docker image

[Paul Förster]

Hi Shankar,

> On 03. Aug, 2020, at 22:01, Shankar Bhaskaran  wrote:
> I am trying to enable ssl on postgres docker image . The postgres  image 
> should only support SSL . I did configure the  command:" -c ssl=on -c 
> ssl_key_file=/opt/postgresql/server.key -c 
> ssl_cert_file=/opt/postgresql/server.crt"   in the docker compose file.

I don't know anything about Docker at all, so I can't say anything here.

> I also added the following entry in the pg_hba.conf after commenting all 
> other host entries 
> "hostssl   all  all   all md5".

are there any "host" entries in the pg_hba.conf file? If so, comment them out 
so that only the "hostssl" entries are enabled.

> But I am able to make ssl as well as non ssl connections to the postgres 
> image.

see above.

> My observation is the command only updates the postmaster.opts file and not 
> the postgres.conf file . 
> 
> Could I get some help 

the postgresql.conf file should have ssl=on and of course the key & certificate 
file settings. Maybe you should put them there.

Cheers,
Paul

Re: Keeping state in a foreign data wrapper

[Stelios Sfakianakis]

Thank you Ian for the prompt reply! I will certainly have a look at 
pg_stat_statements

I also came across mysql_fdw (https://github.com/EnterpriseDB/mysql_fdw 
) that features a connection pool 
shared across queries. It uses a hash table with the serverid and userid as 
lookup key : 
https://github.com/EnterpriseDB/mysql_fdw/blob/REL-2_5_4/connection.c#L55 
 The 
hash table is allocated in the cache memory context but it worries me that 1) 
no locks are used, 2) the "ConnectionHash" variable is declared static so in 
the multi-process architecture of Postgres could have been the case that 
multiple copies of this exist when the shared library of mysql_fdw is loaded?

Best regards
Stelios

> On 4 Aug 2020, at 06:25, Ian Lawrence Barwick  wrote:
> 
> 2020年8月4日(火) 1:24 Stelios Sfakianakis :
>> 
>> Hi,
>> 
>> I am trying to  implement a FDW in Postgres for accessing a web api and I 
>> would like to keep information like for example the total number of requests 
>> submiited. Ideally these data should be kept “per-user” and of course with 
>> the proper locking to eliminate race conditions. So the question I have is 
>> how to design such a global (but per user and database) state, using the C 
>> FDW API of Postgres. I have gone through the API and for example I see 
>> various fdw_private fields in the query planning structures and callbacks 
>> but these do not seem to be relevant to my requirements. Another possiblity 
>> seems to be to use shared memory 
>> (https://www.postgresql.org/docs/12/xfunc-c.html#id-1.8.3.13.14) but it is 
>> even less clear how to do it.
> 
> Shared memory would probably work; you'd need to load the FDW via
> "shared_preload_libraries" and have the FDW handler function(s) update
> shared memory with whatever statistics you want to track. You could
> then define SQL functions to retrieve the stored values, and possibly
> persist them over server restarts by storing/retrieving them from a
> file.
> 
> Look at "pg_stat_statements" for a good example of how to do that kind of 
> thing.
> 
> Regards
> 
> Ian Barwick
> 
> -- 
> Ian Barwick   https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services

Re: How to rebuild index efficiently

[Michael Paquier]

On Mon, Aug 03, 2020 at 01:04:45PM -0500, Ron wrote:
> same definition, and when that is complete, drop the old index.  The
> locking that is required here is modest: CREATE INDEX CONCURRENTLY
> needs to lock the table briefly at a couple of points in the
> operation, and dropping the old index requires a brief lock on the
> table.   It is, however, much less overall lock time than REINDEX would be.
> 
> Of course, you need enough disk space... :)

A SHARE UPDATE EXCLUSIVE lock is taken during a CIC, meaning that
writes and reads are allowed on the parent table while the operation
works, but no DDLs are allowed (roughly).  The operation takes a
couple of transactions to complete, and  there are two wait points
after building and validating the new index to make sure that there
are no transactions remaining around that may cause visiblity issues
once the new index is ready to use and becomes valid.  So the
operation is longer, takes more resources, but it has the advantage to
be non-disruptive.
--
Michael


signature.asc
Description: PGP signature