Re: How to rebuild index efficiently

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 req

Re: Keeping state in a foreign data wrapper

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 server

Re: Configuring only SSL in postgres docker image

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/ser

Re: Keeping state in a foreign data wrapper

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 l

Re: bad JIT decision

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

Re: 12.3 replicas falling over during WAL redo

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 past

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

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

Re: 12.3 replicas falling over during WAL redo

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,

Re: 12.3 replicas falling over during WAL redo

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

Re: 12.3 replicas falling over during WAL redo

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

Re: 12.3 replicas falling over during WAL redo

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_LE

Re: 12.3 replicas falling over during WAL redo

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 di

Re: 12.3 replicas falling over during WAL redo

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 checkpo

Configuring only SSL in postgres docker image

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

Re: 12.3 replicas falling over during WAL redo

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:

Re: 12.3 replicas falling over during WAL redo

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

Re: 12.3 replicas falling over during WAL redo

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

Re: 12.3 replicas falling over during WAL redo

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 resto

Re: How to rebuild index efficiently

> > creating another index concurrently is taking lot of time > Could you increase maintenance_work_mem significantly or is that already quite high?

Re: How to rebuild index efficiently

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

Re: How to rebuild index efficiently

> 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 mode

How to rebuild index efficiently

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

Keeping state in a foreign data wrapper

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

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

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 mo

Re: 12.3 replicas falling over during WAL redo

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 checkpoi

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

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 se

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

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 MyDatabase

Re: Out of memory with "create extension postgis"

>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_plan

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

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 SP

Re: how reliable is pg_rewind?

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 inf

Re: Out of memory with "create extension postgis"

>> 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 comp