Clarification related to BDR

Hi, I came across a link published in postgresql, where it is clearly mentioned BDR as an open source. When I tried to install BDR for CentOS from 2ndQuadrant, the yum repository was not reachable and upon further enquiring with 2ndQuadrant, I got a reply from them quoting as follows "BDR is not

Check what has been done for a uncommitted prepared transaction

Hi: I want to know what happens been done for an uncommitted prepared transaction with pg_waldump, however I can't find it. demo=# begin; BEGIN demo=*# select txid_current(); txid_current -- 608 (1 row) demo=*# prepare transaction 's'; PREPARE TRANSACTION demo=# insert int

Sv: Practical usage of large objects.

På onsdag 13. mai 2020 kl. 19:53:38, skrev Dmitry Igrishin mailto:dmit...@gmail.com>>: Hello all, As you know, PostgreSQL has a large objects facility [1]. I'm curious are there real systems which are use this feature? I'm asking because and I'm in doubt should the Pgfe driver [2] provide the

PG12.2 Configure cannot enalble SSL

Greetings: This problem may be related to several threads I have found, e.g., https://www.postgresql.org/message-id/29069.1575298784%40sss.pgh.pa.us Context: Building PG12.2 and need to enable SSL Previous version builds have been uneventful but I haven’t attempted to enable SSL before

Re: Check what has been done for a uncommitted prepared transaction

On Thu, May 14, 2020 at 3:38 PM Andy Fan wrote: > I can get the log for "prepared command" only, but nothing was found for > the insert > statement. what should I do? > > My version is 9.4. > > Sorry, my production version is 9.4 and my demo above is v12. I tried in 9.4, I still have trouble

Re: Check what has been done for a uncommitted prepared transaction

On Thu, May 14, 2020 at 03:38:24PM +0800, Andy Fan wrote: > I want to know what happens been done for an uncommitted prepared > transaction with pg_waldump, however I can't find it. > > demo=*# prepare transaction 's'; > PREPARE TRANSACTION > demo=# insert into mm select generate_series(1, 1000);

Re: Clarification related to BDR

On Thu, May 14, 2020 at 9:01 AM Santhosh Kumar wrote: > Hi, > I came across a link published in postgresql, where it is clearly > mentioned BDR as an open source. When I tried to install BDR for CentOS > from 2ndQuadrant, the yum repository was not reachable and upon further > enquiring with 2nd

Re: Clarification related to BDR

Am 14.05.20 um 06:37 schrieb Santhosh Kumar: Can you please help me understand, why the following news is published in "postgresql" with an encouraging message acknowledging BDR as an open source? We invested time and effort to use BDR only to understand at a later point in time, that it is

Re: Check what has been done for a uncommitted prepared transaction

On Thu, May 14, 2020 at 4:05 PM Michael Paquier wrote: > On Thu, May 14, 2020 at 03:38:24PM +0800, Andy Fan wrote: > > I want to know what happens been done for an uncommitted prepared > > transaction with pg_waldump, however I can't find it. > > > > demo=*# prepare transaction 's'; > > PREPARE T

Re: pg_upgrade too slow on vacuum phase

> > So the analyze(and freeze) are done before the new cluster are fully > populated. Is the time being taken maybe for the loading schema/data > portion? > > No, the upgrade is stuck on these 2 stages indeed, maybe 50% on the first and 50% (or a little more) on the second. When a run them outside

Re: Clarification related to BDR

On Thu, 14 May 2020 at 08:01, Santhosh Kumar wrote: > Hi, > I came across a link published in postgresql, where it is clearly > mentioned BDR as an open source. When I tried to install BDR for CentOS > from 2ndQuadrant, the yum repository was not reachable and upon further > enquiring with 2ndQu

Re: Reuse an existing slot with a new initdb

On 5/13/2020 9:28 PM, David G. Johnston wrote: On Wednesday, May 13, 2020, Michael Paquier > wrote: On Wed, May 13, 2020 at 02:12:45PM -0700, live-school support wrote: > I didn't recal that it was not possible to create a hot standby with a fresh > n

Re: Clarification related to BDR

On 5/14/20 12:37 AM, Santhosh Kumar wrote: Can you please help me understand, why the following news is published in "postgresql" with an encouraging message acknowledging BDR as an open source? In my opinion it is not a bright idea to not have support for any product. Support is an inde

Re: Column reset all values

Thanks a lot. Drop and re-create views is not an option, because there is a lot views, (and materialized views). also nor index drop is an option, because I need re-create index as I use this table in procedure, so index is necessary for further queries. So total runtime will not decreased. Thank

Re: PG12.2 Configure cannot enalble SSL

"Gavan Schneider" writes: > -bash-3.2$ ./configure --with-openssl \ > > --with-includes=/usr/local/opt/openssl/include/openssl \ > > --with-libraries=/usr/local/opt/openssl/lib > ... > checking openssl/ssl.h usability... no > checking openssl/ssl.h presence... n

Re: Reuse an existing slot with a new initdb

Support writes: > I think David caught it, the question is Why, as long as we have an > exact copy of the master (from pg_dumpall) Stop right there. pg_dumpall does *not* produce an exact copy. It produces a logically equivalent copy, which is not close enough for physical replication to work.

Re: Check what has been done for a uncommitted prepared transaction

On Thu, 2020-05-14 at 16:26 +0800, Andy Fan wrote: > Thanks, actually I don't know how to use prepared transaction and how it > works. > I care about this because there is a long prepared transaction exists in our > customer, > and we want to know what this transaction has done（like any data it

Re: Practical usage of large objects.

On Thu, 2020-05-14 at 12:59 +0900, Michael Paquier wrote: > On Wed, May 13, 2020 at 01:55:48PM -0400, Tom Lane wrote: > > Dmitry Igrishin writes: > > > As you know, PostgreSQL has a large objects facility [1]. I'm curious > > > are there real systems which are use this feature? > > > > We get que

Re: Reuse an existing slot with a new initdb

On 5/14/2020 6:33 AM, Tom Lane wrote: Support writes: I think David caught it, the question is Why, as long as we have an exact copy of the master (from pg_dumpall) Stop right there. pg_dumpall does *not* produce an exact copy. It produces a logically equivalent copy, which is not close eno

vacuumdb --jobs deadlock: how to avoid pg_catalog ?

Hello there, I'm a long-time postgres user but vacuumdb newbie :). Using vacuumdb (v12.2) with '---jobs' to vacuum a v9.6 database on localhost with parallel processes: $ time vacuumdb --full --echo -U postgres --jobs=8 -d mydatabase_test -p 5434 --password As shown below I ran into t

Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ?

On 5/14/20 8:35 AM, Eduard Rozenberg wrote: Hello there, I'm a long-time postgres user but vacuumdb newbie :). Using vacuumdb (v12.2) with '---jobs' to vacuum a v9.6 database on localhost with parallel processes: $ time vacuumdb --full --echo -U postgres --jobs=8 -d mydatabase_test -p 5434

Re: Practical usage of large objects.

Am 14.05.20 um 15:36 schrieb Laurenz Albe: Interesting; only recently I played with that a little and found that that is not necessarily true: https://www.cybertec-postgresql.com/en/binary-data-performance-in-postgresql/ Yours, Laurenz Albe We used lo a lot in a project for large uploads (>4G

Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ?

On 5/14/20 11:27 AM, Eduard Rozenberg wrote: @Adrian thanks. I did a test vacuum full the "normal" non-parallel way (VACUUM FULL sql) previously on a test db copy and saw the DB size (postgres 9.6) shrink from 6.8 TB to 4.4 TB of actual, real disk space usage ($ df -d 1). I don't know the re

Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ?

On 5/14/20 12:03 PM, Eduard Rozenberg wrote: @Adrian thanks again. I read the postgres docs the same way - that previously used space is marked as available and therefore no need for vacuum full. Previously used = now available space, which gets re-used, in theory. And yet this same DB is gro

surprisingly slow creation of gist index used in exclude constraint

Hi, I'm upgrading a database from 9.4 to 11.5 by dumping from the old cluster and loading into the new cluster. The database is tiny: around 2.3G, but importing this table is proving problematic: Column | Type|Modifie

Re: surprisingly slow creation of gist index used in exclude constraint

Quoting Chris Withers : Hi, I'm upgrading a database from 9.4 to 11.5 by dumping from the old cluster and loading into the new cluster. The database is tiny: around 2.3G, but importing this table is proving problematic: Column | Type|Modifiers .

Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ?

@Adrian thanks again. I read the postgres docs the same way - that previously used space is marked as available and therefore no need for vacuum full. Previously used = now available space, which gets re-used, in theory. And yet this same DB is growing at 30-50 GB weekly, despite the fact that

Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ?

@Adrian thanks. I did a test vacuum full the "normal" non-parallel way (VACUUM FULL sql) previously on a test db copy and saw the DB size (postgres 9.6) shrink from 6.8 TB to 4.4 TB of actual, real disk space usage ($ df -d 1). I don't know the reason so much space was "locked up" (other than t

Re: surprisingly slow creation of gist index used in exclude constraint

Chris Withers writes: > It has 4.1 million rows in it and while importing the data only > takes a couple of minutes, when I did a test load into the new > cluster, building the mkt_profile_period_col1_col4_col2_chan_excl > index for the exclude constraint took 15 hours. Do

Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ?

You might have index fragmentation and possibly reindexing them conncurrently on PG12, should do that. As everyone agreed most of space will be marked for re-use later for table segments but indices in your case could be problem. On previous versions you can use pg_repack to do index rebuilds wh

Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ?

On Thu, May 14, 2020 at 2:20 PM Eduard Rozenberg wrote: > I did verify postgresql.conf has always been properly configured re: > autovacuum: 'autovacuum = on'and 'track_counts = on' > This may be insufficient to keep up if you have large tables. The default scale factor allows for 20% of the ro

Re: Check what has been done for a uncommitted prepared transaction

On Thu, May 14, 2020 at 9:33 PM Laurenz Albe wrote: > On Thu, 2020-05-14 at 16:26 +0800, Andy Fan wrote: > > Thanks, actually I don't know how to use prepared transaction and how > it works. > > I care about this because there is a long prepared transaction exists in > our customer, > > and we w

view selection during query rewrite

Suppose I have the following query and a view v1 defined as follows: *Q1: SELECT * FROM Table1 WHERE Table1.col1 = 5 and Table1.col2 LIKE '%abc%';* *create view v1 as select * from Table1 where Table1.col1 = 5;* An effective way to execute Q1 would be to re-write it as: *select * from v1 where col

Re: view selection during query rewrite

Vamsi Meduri writes: > Suppose I have the following query and a view v1 defined as follows: > *Q1: SELECT * FROM Table1 WHERE Table1.col1 = 5 and Table1.col2 LIKE > '%abc%';* > *create view v1 as select * from Table1 where Table1.col1 = 5;* > An effective way to execute Q1 would be to re-write it

Logical replication for async service communication?

Has anyone attempted to use logical replication with table partitioning for async service communication? Proof of concept: https://gist.github.com/shuber/8e53d42d0de40e90edaf4fb182b59dfc Services would commit messages to their own databases along with the rest of their data (with the same transac

Re: surprisingly slow creation of gist index used in exclude constraint

On 14/05/2020 21:16, k...@rice.edu wrote: Hi Chris, This sounds like a candidate for pg_logical replicating from the old to new system. Can you point me to a good guide as to how to easily set this up for one database and would work between pg 9.4 and pg 11.5? cheers, Chris

Re: Check what has been done for a uncommitted prepared transaction

On Fri, 2020-05-15 at 08:09 +0800, Andy Fan wrote: > On Thu, May 14, 2020 at 9:33 PM Laurenz Albe wrote: > > On Thu, 2020-05-14 at 16:26 +0800, Andy Fan wrote: > > > Thanks, actually I don't know how to use prepared transaction and how it > > > works. > > > I care about this because there is a l