Re: pg_dumpall --exclude-database case folding, was Re: AWS forcing PG upgrade from v9.6 a disaster

Andrew Dunstan writes: > On 6/10/21 2:23 PM, Andrew Dunstan wrote: >> Ouch. That looks like a plain old bug. Let's fix it. IIRC I just used >> the same logic that we use for pg_dump's --exclude-* options, so we need >> to check if they have similar issues. > Peter Eisentraut has pointed out to me

pg_dumpall --exclude-database case folding, was Re: AWS forcing PG upgrade from v9.6 a disaster

On 6/10/21 2:23 PM, Andrew Dunstan wrote: > On 6/10/21 2:00 PM, Tom Lane wrote: >> "Dean Gibson (DB Administrator)" writes: >>> On 2021-06-10 09:54, Ranier Vilela wrote: Your cmd lacks = =>pg_dumpall -U Admin --exclude-database=MailPen >zzz.sql >>> I read that before posting, but misse

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-06-10 10:43, Dean Gibson (DB Administrator) wrote: On 2021-06-10 09:54, Ranier Vilela wrote: Em qui., 10 de jun. de 2021 às 13:08, Dean Gibson (DB Administrator) mailto:postgre...@mailpen.com>> escreveu: ...  Do I need quotes on the command line? See at: https://www.postgresql.or

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-06-10 11:23, Andrew Dunstan wrote: On 6/10/21 2:00 PM, Tom Lane wrote: "Dean Gibson (DB Administrator)" writes: ... Do I need quotes on the command line? On 2021-06-10 09:54, Ranier Vilela wrote: Your cmd lacks = =>pg_dumpall -U Admin --exclude-database=MailPen >zzz.sql I read [the m

Re: AWS forcing PG upgrade from v9.6 a disaster

On 6/10/21 2:00 PM, Tom Lane wrote: > "Dean Gibson (DB Administrator)" writes: >> On 2021-06-10 09:54, Ranier Vilela wrote: >>> Your cmd lacks = >>> =>pg_dumpall -U Admin --exclude-database=MailPen >zzz.sql >> I read that before posting, but missed that. Old command line patterns >> die hard!

Re: AWS forcing PG upgrade from v9.6 a disaster

"Dean Gibson (DB Administrator)" writes: > On 2021-06-10 09:54, Ranier Vilela wrote: >> Your cmd lacks = >> =>pg_dumpall -U Admin --exclude-database=MailPen >zzz.sql > I read that before posting, but missed that. Old command line patterns > die hard! > However, the result was the same: 3.5GB b

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-06-10 09:54, Ranier Vilela wrote: Em qui., 10 de jun. de 2021 às 13:08, Dean Gibson (DB Administrator) mailto:postgre...@mailpen.com>> escreveu: I guess I don't understand what that option does: =>pg_dumpall -U Admin --exclude-database MailPen >zzz.sql pg_dump: error: coul

Re: AWS forcing PG upgrade from v9.6 a disaster

Em qui., 10 de jun. de 2021 às 13:08, Dean Gibson (DB Administrator) < postgre...@mailpen.com> escreveu: > On 2021-06-10 03:29, Andrew Dunstan wrote: > > On 6/9/21 9:50 PM, Dean Gibson (DB Administrator) wrote: > > First, pg_dumpall (v13.3) errors out, because on RDS, you cannot be a > superuser,

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-06-10 03:29, Andrew Dunstan wrote: On 6/9/21 9:50 PM, Dean Gibson (DB Administrator) wrote: First, pg_dumpall (v13.3) errors out, because on RDS, you cannot be a superuser, & it tries to dump protected stuff.  If there is a way around that, I'd like to know it, even though it's not an

Re: AWS forcing PG upgrade from v9.6 a disaster

On Wed, Jun 9, 2021 at 6:50 PM Dean Gibson (DB Administrator) < postgre...@mailpen.com> wrote: > Having now successfully migrated from PostgreSQL v9.6 to v13.2 in Amazon > RDS, I wondered, why I am paying AWS for an RDS-based version, when I was > forced by their POLICY to go through the effort I

Re: AWS forcing PG upgrade from v9.6 a disaster

On 6/9/21 9:50 PM, Dean Gibson (DB Administrator) wrote: > Having now successfully migrated from PostgreSQL v9.6 to v13.2 in > Amazon RDS, I wondered, why I am paying AWS for an RDS-based version, > when I was forced by their POLICY to go through the effort I did?  I'm > not one of the crowd who

Re: AWS forcing PG upgrade from v9.6 a disaster

Having now successfully migrated from PostgreSQL v9.6 to v13.2 in Amazon RDS, I wondered, why I am paying AWS for an RDS-based version, when I was forced by their POLICY to go through the effort I did?  I'm not one of the crowd who thinks, "It works OK, so I don't update anything".  I'm usually

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-06-07 04:52, Andrew Dunstan wrote: On 6/6/21 7:49 PM, Dean Gibson (DB Administrator) wrote: On 2021-05-29 13:35, Andrew Dunstan wrote: On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote: ...  If I remove "CAST( license_status AS CHAR ) = 'A'", ... Why are you using this expressi

Re: AWS forcing PG upgrade from v9.6 a disaster

On 6/6/21 7:49 PM, Dean Gibson (DB Administrator) wrote: > On 2021-05-29 13:35, Andrew Dunstan wrote: >> On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote: >>> Meanwhile, I've been doing some checking.  If I remove "CAST( >>> license_status AS CHAR ) = 'A'", the problem disappears.  Changi

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-29 13:35, Andrew Dunstan wrote: On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote: Meanwhile, I've been doing some checking.  If I remove "CAST( license_status AS CHAR ) = 'A'", the problem disappears.  Changing the JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves"

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-31 21:16, Justin Pryzby wrote: Here's the FROM clause that bit me: FROM lic_en JOIN govt_region USING (territory_id, country_id) LEFT JOIN zip_code USING (territory_id, country_id, zip5) LEFT JOIN "County" USING (territory_id, country_id, fips_county); I'm gue

Re: AWS forcing PG upgrade from v9.6 a disaster

> Here's the FROM clause that bit me: > >FROM lic_en > JOIN govt_region USING (territory_id, country_id) > LEFT JOIN zip_code USING (territory_id, country_id, zip5) > LEFT JOIN "County" USING (territory_id, country_id, fips_county); I'm guessing that there's a dependency/corre

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-30 21:44, Tom Lane wrote: "Dean Gibson (DB Administrator)" writes: I thought that having a "USING" clause, was semantically equivalent to an "ON" clause with the equalities explicitly stated.  So no, I didn't try that. USING is not that, or at least not only that ... read the manual

Re: AWS forcing PG upgrade from v9.6 a disaster

"Dean Gibson (DB Administrator)" writes: > I thought that having a "USING" clause, was semantically equivalent to > an "ON" clause with the equalities explicitly stated.  So no, I didn't > try that. USING is not that, or at least not only that ... read the manual. I'm wondering if what you saw

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-30 20:41, Christophe Pettus wrote: On May 30, 2021, at 20:07, Dean Gibson (DB Administrator) wrote: The first two JOINs are not the problem, & are in fact retained in my solution. The problem is the third JOIN, where "fips_county" from "County" is actually matched with the correspo

Re: AWS forcing PG upgrade from v9.6 a disaster

> On May 30, 2021, at 20:07, Dean Gibson (DB Administrator) > wrote: > The first two JOINs are not the problem, & are in fact retained in my > solution. The problem is the third JOIN, where "fips_county" from "County" > is actually matched with the corresponding field from the "zip_code" VI

Re: AWS forcing PG upgrade from v9.6 a disaster

*SOLVED !!!*  Below is the *new* EXPLAIN ANALYZE for *13.2* on AWS RDS (with *no changes* to server parameters) along with the prior EXPLAIN ANALYZE outputs for easy comparison. While I didn't discount the significance & effect of optimizing the server parameters, this problem always seemed to

Re: AWS forcing PG upgrade from v9.6 a disaster

I tried 500, to no avail.  Since each change involves a delay as RDS readjusts, I'm going down a different path at the moment. On 2021-05-29 03:40, Lionel Bouton wrote: Le 29/05/2021 à 02:38, Dean Gibson (DB Administrator) a écrit : The original VACUUM FULL ANALYZE ran in 10 hours.  The plain A

Re: AWS forcing PG upgrade from v9.6 a disaster

On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote: > > > Meanwhile, I've been doing some checking.  If I remove "CAST( > license_status AS CHAR ) = 'A'", the problem disappears.  Changing the > JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves" the > problem, but there is an ex

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-28 22:24, Alexey M Boltenkov wrote: On 05/29/21 07:08, Dean Gibson (DB Administrator) wrote: [deleted] BTW what is the planner reason to not use index in v13.2? Is index in corrupted state? Have you try to reindex index "FccLookup"."_LicStatus_pkey" ? 1.5M of seqscan's are looking

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-29 09:25, Adrian Klaver wrote: On 5/28/21 5:38 PM, Dean Gibson (DB Administrator) wrote: Can you repeat your EXPLAIN (ANALYZE, BUFFERS) of the query from your first post and post them here: https://explain.depesz.com/ Other information: 1) A diff of your configuration settings bet

Re: AWS forcing PG upgrade from v9.6 a disaster

On Fri, May 28, 2021, 22:41 Andrew Dunstan wrote: > > On 5/28/21 10:27 PM, Jan Wieck wrote: > > > > > > On Fri, May 28, 2021, 17:15 Andrew Dunstan > > wrote: > > > > > > > > > > AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I > > assume y

Re: AWS forcing PG upgrade from v9.6 a disaster

On 05/29/21 07:08, Dean Gibson (DB Administrator) wrote: On 2021-05-28 19:43, Christophe Pettus wrote: ... The most common reason for this kind of inexplicable stuff after an RDS upgrade is, as others have said, parameter changes, since you get a new default parameter group after the upgrade.

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-28 19:43, Christophe Pettus wrote: ... The most common reason for this kind of inexplicable stuff after an RDS upgrade is, as others have said, parameter changes, since you get a new default parameter group after the upgrade. That being said, this does look like something happened t

Re: AWS forcing PG upgrade from v9.6 a disaster

> On May 28, 2021, at 14:30, Bruce Momjian wrote: > I think it uses pg_upgrade. It does. It does not, however, do the vacuum analyze step afterwards. A VACUUM (FULL, ANALYZE) should take care of that, and I believe the OP said he had done that after the pg_upgrade. The most common reason

Re: AWS forcing PG upgrade from v9.6 a disaster

On 5/28/21 10:27 PM, Jan Wieck wrote: > > > On Fri, May 28, 2021, 17:15 Andrew Dunstan > wrote: > > > > > AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I > assume you would know better than him or me what it actually does > do :-) > >

Re: AWS forcing PG upgrade from v9.6 a disaster

On Fri, May 28, 2021, 17:15 Andrew Dunstan wrote: > > On 5/28/21 4:23 PM, Jan Wieck wrote: > > On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: > > > > What sticks out for me are these two scans, which balloon from 50-60 > > heap fetches to 1.5M each. > > > >> -> N

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-28 13:23, Jan Wieck wrote: On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: What sticks out for me are these two scans, which balloon from 50-60 heap fetches to 1.5M each.   ->  Nested Loop (cost=0.29..0.68 rows=1 width=7) (actual time=0.003..0.004 row

Re: AWS forcing PG upgrade from v9.6 a disaster

I recently did 20 upgrades from 9.6 to 12.4 and 12.5. No issues and the upgrade process uses pg_upgrade. I don’t know if AWS modified it though. Bob Sent from my PDP11 > On May 28, 2021, at 5:15 PM, Andrew Dunstan wrote: > >  >> On 5/28/21 4:23 PM, Jan Wieck wrote: >> On 5/28/21 2:48 PM,

Re: AWS forcing PG upgrade from v9.6 a disaster

On Fri, May 28, 2021 at 05:15:33PM -0400, Andrew Dunstan wrote: > > How did you load the database? pg_dump -> psql/pg_restore? > > > > If so, did you perform a VACUUM FREEZE after the load? > > Jan > > > AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I > assume you would kno

Re: AWS forcing PG upgrade from v9.6 a disaster

On 5/28/21 4:23 PM, Jan Wieck wrote: > On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: > > What sticks out for me are these two scans, which balloon from 50-60 > heap fetches to 1.5M each. > >>   ->  Nested Loop  (cost=0.29..0.68 rows=1 >> width=7) (actual time=0.003

Re: AWS forcing PG upgrade from v9.6 a disaster

t; *From: *Andrew Dunstan >> *Date: *Friday, May 28, 2021 at 2:08 PM >> *To: *Dean Gibson (DB Administrator) , >> pgsql-performance@lists.postgresql.org < >> pgsql-performance@lists.postgresql.org> >> *Subject: *Re: AWS forcing PG upgrade from v9.6 a disaster &g

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-28 12:18, Campbell, Lance wrote: Also, did you check your RDS setting in AWS after upgrading?  I run four databases in AWS.  I found that the work_mem was set way low after an upgrade.  I had to tweak many of my settings. Lance I've wondered a lot about work_mem.  The default

Re: AWS forcing PG upgrade from v9.6 a disaster

On 2021-05-28 12:08, Andrew Dunstan wrote: On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: [Reposted to the proper list] ... Have you tried reproducing these results outside RDS, say on an EC2 instance running vanilla PostgreSQL? cheers, andrew -- Andrew Dunstan EDB: https://www.

Re: AWS forcing PG upgrade from v9.6 a disaster

On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: What sticks out for me are these two scans, which balloon from 50-60 heap fetches to 1.5M each. ->  Nested Loop  (cost=0.29..0.68 rows=1 width=7) (actual time=0.003..0.004 rows=1 loops=1487153)   

Re: AWS forcing PG upgrade from v9.6 a disaster

The plan is also influenced by cost related and memory related config settings such as random_page_cost and work_mem, right? Hence the questions if configs are matching or newer versions are using very conservative (default) settings.

Re: AWS forcing PG upgrade from v9.6 a disaster

n > *Date: *Friday, May 28, 2021 at 2:08 PM > *To: *Dean Gibson (DB Administrator) , > pgsql-performance@lists.postgresql.org < > pgsql-performance@lists.postgresql.org> > *Subject: *Re: AWS forcing PG upgrade from v9.6 a disaster > > > On 5/28/21 2:48 PM, Dean Gi

Re: AWS forcing PG upgrade from v9.6 a disaster

28, 2021 at 2:08 PM *To: *Dean Gibson (DB Administrator) , pgsql-performance@lists.postgresql.org *Subject: *Re: AWS forcing PG upgrade from v9.6 a disaster On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: > [Reposted to the proper list] > > I started to use PostgreSQL v7.3 i

Re: AWS forcing PG upgrade from v9.6 a disaster

) , pgsql-performance@lists.postgresql.org Subject: Re: AWS forcing PG upgrade from v9.6 a disaster On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: > [Reposted to the proper list] > > I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4 > at one point), gradual

Re: AWS forcing PG upgrade from v9.6 a disaster

On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: > [Reposted to the proper list] > > I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4 > at one point), gradually moving to v9.0 w/ replication in 2010.  In > 2017 I moved my 20GB database to AWS/RDS, gradually upgrading