Re: pg_restore V12 fails consistently against piped pg_dumps

On Thu, 2020-05-07 at 11:04 -0400, Mohamed Wael Khobalatte wrote: > Thanks Tom, I will review those changes tonight. In the meantime, to > reproduce, I run the following: > > - createdb test > - create some tables, I used a simple loop to create 10 empty ones. > - pg_dump -v -Fc test | tee test.du

Re: Odd postgres12 upgrade is changing or munging a password?

On Thu, 2020-05-07 at 11:55 -0700, Tory M Blue wrote: > Going from 9.5 to 12 and 2 times now, I've had a password either go missing > or munged. > I've had to add an alter statement at the end of the upgrade. > > The DB is functioning fine, shut it down, do the upgrade and the password is > mung

Re: Memory footprint diff between 9.5 and 12

On Thu, 2020-05-07 at 13:33 -0700, Tory M Blue wrote: > I hadn't noticed this until today, but a running 9.5 system with buffers at > 10GB starts > and has been running years without issues. (15GB available) > > Postgres 12 will not start with that configuration, complaining about memory > avail

Re: pg_temp schema created while using DB Link

On Fri, 2020-05-08 at 10:11 +0400, Jill Jade wrote: > We have an issue with DB_Link from Oracle to PostgreSQL. When we try to > access tables from > Oracle database via DB_link, temp schemas(pg_temp,pg_toast) are automatically > created > in the database while using the query below. > > create t

Re: AutoVacuum and growing transaction XID's

On Thu, May 7, 2020 at 11:04 PM David Rowley wrote: > On Fri, 8 May 2020 at 13:51, github kran wrote: > > I can't either DROP or ALTER any other tables ( REMOVE Inheritance > for any of old tables where the WRITES are not getting written to). Any of > the ALTER TABLE OR DROP TABLE DDL's a

Re: AutoVacuum and growing transaction XID's

Thanks David for your replies. On Thu, May 7, 2020 at 11:01 PM David Rowley wrote: > On Fri, 8 May 2020 at 09:18, github kran wrote: > > 1) We haven't changed anything related to autovacuum except a work_mem > parameter which was increased to 4 GB which I believe is not related to > autovacuum

Question correct Way to switch Slave to master

Hello, I actually work on a Master Slave Server with postgresql 11 on redhat 8. Ich am Building the Server with Ansible. When i need to restore the Master Server i want to build it as Slave, connect it to the actually master to sync the Data, and then build the postgres Server itself with An

Re: AutoVacuum and growing transaction XID's

On Fri, 8 May 2020 at 13:51, github kran wrote: > I can't either DROP or ALTER any other tables ( REMOVE Inheritance for > any of old tables where the WRITES are not getting written to). Any of the > ALTER TABLE OR DROP TABLE DDL's arer not getting exeucted even I WAITED FOR > SEVERAL MI

Re: AutoVacuum and growing transaction XID's

On Fri, 8 May 2020 at 09:18, github kran wrote: > 1) We haven't changed anything related to autovacuum except a work_mem > parameter which was increased to 4 GB which I believe is not related to > autovacuum It might want to look into increasing vacuum_cost_limit to something well above 200 or

Re: AutoVacuum and growing transaction XID's

On Thu, May 7, 2020 at 4:18 PM github kran wrote: > > > On Thu, May 7, 2020 at 1:33 PM Michael Lewis wrote: > >> It is trying to do a vacuum freeze. Do you have autovacuum turned off? >> Any settings changed from default related to autovacuum? >> >> https://www.postgresql.org/docs/9.6/routine-va

Re: walreceiver termination

Hello. At Mon, 4 May 2020 09:09:15 -0500, Justin King wrote in > Would there be anyone that might be able to help troubleshoot this > issue -- or at least give me something that would be helpful to look > for? > > https://www.postgresql.org/message-id/flat/CAGH8ccdWLLGC7qag5pDUFbh96LbyzN_toORh2

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

On Fri, 8 May 2020 at 10:00, David G. Johnston wrote: > > On Thu, May 7, 2020 at 11:07 AM Amarendra Konda wrote: >> >> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS >> pa_process_activity_id FROM process_activity pa WHERE pa.app_id = >> '126502930200650' AND pa.c

Re: Memory footprint diff between 9.5 and 12

On Thu, 2020-05-07 at 13:33 -0700, Tory M Blue wrote: > I hadn't noticed this until today, but a running 9.5 system with > buffers at 10GB starts and has been running years without issues. > (15GB available) > Postgres 12 will not start with that configuration, complaining about > memory availabili

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

On Thu, May 7, 2020 at 11:07 AM Amarendra Konda wrote: > EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id > AS pa_process_activity_id FROM process_activity pa WHERE pa.app_id = > '126502930200650' AND pa.created > '1970-01-01 00:00:00' AND EXISTS ( > SELECT 1 FROM proce

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

"David G. Johnston" writes: > On Thu, May 7, 2020 at 10:49 AM Amarendra Konda > wrote: >> Can you please explain, why it is getting more columns in output, even >> though we have asked for only one column ? >> * Output: pa.process_activity_id, pa.process_activity_type, pa.voice_url, >> pa.process

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

On Thu, May 7, 2020 at 10:49 AM Amarendra Konda wrote: > Can you please explain, why it is getting more columns in output, even > though we have asked for only one column ? > > > > * Output: pa.process_activity_id, pa.process_activity_type, pa.voice_url, > pa.process_activity_user_id, pa.app_id,

Re: AutoVacuum and growing transaction XID's

On Thu, May 7, 2020 at 1:33 PM Michael Lewis wrote: > It is trying to do a vacuum freeze. Do you have autovacuum turned off? Any > settings changed from default related to autovacuum? > > https://www.postgresql.org/docs/9.6/routine-vacuuming.html > Read 24.1.5. Preventing Transaction ID Wraparoun

Memory footprint diff between 9.5 and 12

I hadn't noticed this until today, but a running 9.5 system with buffers at 10GB starts and has been running years without issues. (15GB available) Postgres 12 will not start with that configuration, complaining about memory availability. So Postgres12 won't start until shared buffers is 6GB, but

Re: pg_dump negation regex

You're right Rob. Thanks, it worked! Eudald El jue., 7 may. 2020 a las 21:16, Rob Sargent () escribió: > > > > On May 7, 2020, at 1:03 PM, Eudald Valcàrcel Lacasa < > eudald.valcar...@gmail.com> wrote: > > > > I know the -T command, but I'm trying to regex the "pattern" in -T in > order to exclu

Re: Odd postgres12 upgrade is changing or munging a password?

On 5/7/20 12:24 PM, Tory M Blue wrote: Yes same password, I'm using a basic alter command to put the right password back. I'm doing another upgrade in an hour, and will do some more checks to see if it's trying to use another password or what. I obviously can't read the password from the file

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

Here is my thought on why row is not limiting when joined vs why it is limiting when not joined. When not joined and where clause is having IN, it is using index process_activity_process_instance_id_app_id_created_idx which has columns process_instance_id, created which is in order by and hence

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

On 5/7/20 10:49 AM, Amarendra Konda wrote: Hi David, Thanks for the reply.This has optimized number of rows. Yeah, but your execution time has increased an order of magnitude. Not sure if that is what you want. Can you please explain, why it is getting more columns in output, even though

Re: Odd postgres12 upgrade is changing or munging a password?

Yes same password, I'm using a basic alter command to put the right password back. I'm doing another upgrade in an hour, and will do some more checks to see if it's trying to use another password or what. I obviously can't read the password from the file , so knowing if it's munged or other, I'm n

Re: pg_dump negation regex

> On May 7, 2020, at 1:03 PM, Eudald Valcàrcel Lacasa > wrote: > > I know the -T command, but I'm trying to regex the "pattern" in -T in order > to exclude all tables named program_1 onwards. > I believe commonly you'd use -T "program_!0", but the ! negation keyword is > not defined. > > T

Re: Odd postgres12 upgrade is changing or munging a password?

On 5/7/20 11:55 AM, Tory M Blue wrote: Going from 9.5 to 12 and 2 times now, I've had a password either go missing or munged. I've had to add an alter statement at the end of the upgrade. What are the commands you are using? Is it the same password? The DB is functioning fine, shut it down

Re: pg_dump negation regex

I know the -T command, but I'm trying to regex the "pattern" in -T in order to exclude all tables named program_1 onwards. I believe commonly you'd use -T "program_!0", but the ! negation keyword is not defined. Thanks, Eudald El jue., 7 may. 2020 a las 19:36, Adrian Klaver () escribió: > On 5/7

Odd postgres12 upgrade is changing or munging a password?

Going from 9.5 to 12 and 2 times now, I've had a password either go missing or munged. I've had to add an alter statement at the end of the upgrade. The DB is functioning fine, shut it down, do the upgrade and the password is munged. Seems like an odd occurrence, we have not noted any other weird

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

Hi Virendra, Thanks for your time. Here is the table and index structure * process_activity* Table "public.process_activity" Column |Type | Modifiers +-+-

Re: AutoVacuum and growing transaction XID's

It is trying to do a vacuum freeze. Do you have autovacuum turned off? Any settings changed from default related to autovacuum? https://www.postgresql.org/docs/9.6/routine-vacuuming.html Read 24.1.5. Preventing Transaction ID Wraparound Failures These may also be of help- https://info.crunchydata

AutoVacuum and growing transaction XID's

Hello Team, We are using a PostgreSQL version -9.6.12 version and from last 4 weeks our Transaction ID's (XID's) have increased by 195 million to 341 million transactions. I see the below from pg_stat_activity from the postGreSQL DB. 1) Viewing the pg_stat-activity I noticed that the vacuum qu

Re: increase of xact_commit vs txid_current

On Thu, May 7, 2020 at 7:01 PM wrote: > > Hi, > > I am confused, the documentation says for pg_stat_database > xact_commit Number of transactions in this database that have > been committed > and somewhere else > txid_current()get current transaction ID, assigning a new one > if

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

Hi David, In earlier reply, Over looked another condition, hence please ignore that one Here is the correct one with all the needed conditions. According to the latest one, exists also not limiting rows from the process_activity table. EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.proce

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

Hi David, Thanks for the reply.This has optimized number of rows. Can you please explain, why it is getting more columns in output, even though we have asked for only one column ? EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS pa_process_activity_id FROM process_

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

Hi Adrian, Thanks for the reply. And i have kept latest execution plans, for various SQL statements ( inner join, sub queries and placing values instead of sub query) . As suggested, tried with INNER JOIN, however result was similar to subquery. Is there any way we can tell the optimiser to proc

Re: pg_dump negation regex

On 5/7/20 9:53 AM, Eudald Valcàrcel Lacasa wrote: Hello! I've been looking at the documentation and there seems to be no keyword for negation purposes. Am I missing something? Or it's like this? https://www.postgresql.org/docs/12/app-pgdump.html -T pattern --exclude-table=pattern Do no

Re: Lock Postgres account after X number of failed logins?

On 06.05.20 13:48, Guillaume Lelarge wrote: > Le mer. 6 mai 2020 à 04:18, Christian Ramseyer > a écrit : > > Here is a quick, rough example with still some blanks to fill in - I put > it on github for readability: >

increase of xact_commit vs txid_current

Hi, I am confused, the documentation says for pg_stat_database    xact_commit      Number of transactions in this database that have been committed and somewhere else     txid_current()    get current transaction ID, assigning a new one if the current transaction does not have one I would ha

pg_dump negation regex

Hello! I've been looking at the documentation and there seems to be no keyword for negation purposes. Am I missing something? Or it's like this? I'm trying to use pg_dump to create a database skeleton and I have tables like program_0, program_1, etc. And I'd like to dump all the tables but client

Re: Abnormal Growth of Index Size - Index Size 3x large than table size.

On 5/7/20 6:34 AM, Ashish Chugh wrote: Hi Ravi, Thanks for your reply. One more query from our side. To improve performance and release index space from database, We are running FULL Vacuum on monthly basis. As I recently learned: https://www.postgresql.org/message-id/1392022649.706483.1587

Re: wal_sender_timeout default

On 5/7/20 9:07 AM, Adrian Klaver wrote: On 5/7/20 8:56 AM, Jasen Lentz wrote: In Postgres 11.6-2, what is the default timeout for wal_sender_timeout if it is commented out? Form postgresql.conf Oops, from. # The commented-out settings shown in this file represent the default values. # Re

Re: Abnormal Growth of Index Size - Index Size 3x large than table size.

On Thu, May 7, 2020 at 8:50 AM Ashish Chugh < ashish.ch...@lavainternational.in> wrote > To improve performance and release index space from database, We are > running FULL Vacuum on monthly basis. > > On PostgreSQL website it is not recommended to run FULL Vacuum on > Production Database and this

Re: wal_sender_timeout default

On Thu, May 7, 2020 at 8:56 AM Jasen Lentz wrote: > In Postgres 11.6-2, what is the default timeout for wal_sender_timeout if > it is commented out? > > > The same value you would get if you uncommented the commented out setting... Also the value you would get by querying the database while the

Re: wal_sender_timeout default

On 5/7/20 8:56 AM, Jasen Lentz wrote: In Postgres 11.6-2, what is the default timeout for wal_sender_timeout if it is commented out? Form postgresql.conf # The commented-out settings shown in this file represent the default values. # Re-commenting a setting is NOT sufficient to revert it to t

Re: wal_sender_timeout default

https://www.postgresql.org/docs/11/runtime-config-replication.html >

Re: Postgres 11.6-2 to 11.7 worth the upgrade?

On Thu, May 7, 2020 at 8:58 AM Jasen Lentz wrote: > > > Would upgrading from 11.6-2 to 11.7-current give us any performance > advantages? We are seeing intermittent performance problems that come and > go. Also are there any fixes for the wal_sender and pg_basebackup between > the versions? >

Re: Postgres 11.6-2 to 11.7 worth the upgrade?

https://www.postgresql.org/docs/release/11.7/ It doesn't seem like it. Always best to run the most current minor version though.

Postgres 11.6-2 to 11.7 worth the upgrade?

Would upgrading from 11.6-2 to 11.7-current give us any performance advantages? We are seeing intermittent performance problems that come and go. Also are there any fixes for the wal_sender and pg_basebackup between the versions?

wal_sender_timeout default

In Postgres 11.6-2, what is the default timeout for wal_sender_timeout if it is commented out? [cid:image003.png@01D62466.8C9AAEE0] Jasen M. Lentz, M.Ed Lead Systems Administrator Sesco Enterprises, LLC 4977 State Route 30 East (Mailing Address Only) Greensburg, PA 15601 W: (724) 837-1991 x207

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

On Thu, May 7, 2020 at 7:40 AM Adrian Klaver wrote: > On 5/7/20 4:19 AM, Amarendra Konda wrote: > > Hi, > > > > PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled > > by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit > > > > We have noticed huge difference interms of execu

Re: New Role drop with Grant/Revokes stop working after subsequent runs

Greetings, * David G. Johnston (david.g.johns...@gmail.com) wrote: > On Wed, May 6, 2020 at 5:05 PM AC Gomez wrote: > > I suppose the main question is, why would a bunch of grant and revoke > > commands run and not do anything, not even throw an error? > > Maybe its a bug? - I doubt this kind of

Re: Lock Postgres account after X number of failed logins?

Greetings, * Geoff Winkless (pgsqlad...@geoff.dj) wrote: > On Wed, 6 May 2020, 14:28 Stephen Frost, wrote: > > * Geoff Winkless (pgsqlad...@geoff.dj) wrote: > > > On Wed, 6 May 2020 at 00:05, Tim Cross wrote: > > > > Where Tom's solution fails is with smaller companies that cannot afford > > > >

Re: pg_restore V12 fails consistently against piped pg_dumps

On Thu, May 7, 2020 at 2:35 AM Tom Lane wrote: > > Without a concrete example it's hard to say, but maybe the issue is that > v12 is more aggressive about parallelizing restores --- see 548e50976. Thanks Tom, I will review those changes tonight. In the meantime, to reproduce, I run the following:

RE: Abnormal Growth of Index Size - Index Size 3x large than table size.

Hi Ravi, Thanks for your reply. One more query from our side. To improve performance and release index space from database, We are running FULL Vacuum on monthly basis. On PostgreSQL website it is not recommended to run FULL Vacuum on Production Database and this also requires long downtime alo

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

On 5/7/20 4:19 AM, Amarendra Konda wrote: Hi, PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit We have noticed huge difference interms of execution plan ( response time) , When we pass the direct values  Vs  inner qu

RE: pg_basebackup inconsistent performance

Is the backup server shared among other systems..? No, physical system And there's no increased activity on the backup server either? No Have you looked at network traffic for the duration? And/or disk i/o on each system? If you ran a backup once and then immediately after and that's the '

Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

Hi, PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit We have noticed huge difference interms of execution plan ( response time) , When we pass the direct values Vs inner query to IN clause. High level details of the us

Could not launch new process for connection: Could not allocate memory

I need some help to understand the cause of this error and how to fix it. I have a server running PostgreSQL 9.3 The cluster has around 1.000 databases I'm running pgBouncer for pooling connections, and I set up a pool_size of only 1 connection per database. The total amount of active connections