Re: pg_upgrade failing for 200+ million Large Objects

On Mon, Apr 01, 2024 at 03:28:26PM -0400, Tom Lane wrote: > Nathan Bossart writes: >> The one design point that worries me a little is the non-configurability of >> --transaction-size in pg_upgrade. I think it's fine to default it to 1,000 >> or something, but given how often I've had to fiddle

Re: pg_upgrade failing for 200+ million Large Objects

Nathan Bossart writes: > Sorry for taking so long to get back to this one. Overall, I think the > code is in decent shape. Thanks for looking at it! > The one design point that worries me a little is the non-configurability of > --transaction-size in pg_upgrade. I think it's fine to default

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Mar 27, 2024 at 10:08:26AM -0500, Nathan Bossart wrote: > On Wed, Mar 27, 2024 at 10:54:05AM -0400, Tom Lane wrote: >> Michael Banck writes: >>> What is the status of this? In the commitfest, this patch is marked as >>> "Needs Review" with Nathan as reviewer - Nathan, were you going to

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Mar 27, 2024 at 10:54:05AM -0400, Tom Lane wrote: > Michael Banck writes: >> What is the status of this? In the commitfest, this patch is marked as >> "Needs Review" with Nathan as reviewer - Nathan, were you going to take >> another look at this or was your mail from January 12th a full

Re: pg_upgrade failing for 200+ million Large Objects

Michael Banck writes: > What is the status of this? In the commitfest, this patch is marked as > "Needs Review" with Nathan as reviewer - Nathan, were you going to take > another look at this or was your mail from January 12th a full review? In my mind the ball is in Nathan's court. I feel it's

Re: pg_upgrade failing for 200+ million Large Objects

Hi, On Wed, Mar 27, 2024 at 10:53:51AM +0100, Laurenz Albe wrote: > On Wed, 2024-03-27 at 10:20 +0100, Michael Banck wrote: > > Also, is there a chance this is going to be back-patched? I guess it > > would be enough if the ugprade target is v17 so it is less of a concern, > > but it would be

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, 2024-03-27 at 10:20 +0100, Michael Banck wrote: > Also, is there a chance this is going to be back-patched? I guess it > would be enough if the ugprade target is v17 so it is less of a concern, > but it would be nice if people with millions of large objects are not > stuck until they are

Re: pg_upgrade failing for 200+ million Large Objects

Hi, On Sat, Mar 16, 2024 at 06:46:15PM -0400, Tom Lane wrote: > Laurenz Albe writes: > > On Fri, 2024-03-15 at 19:18 -0400, Tom Lane wrote: > >> This patch seems to have stalled out again. In hopes of getting it > >> over the finish line, I've done a bit more work to address the two > >> loose

Re: pg_upgrade failing for 200+ million Large Objects

On Sat, 2024-03-16 at 18:46 -0400, Tom Lane wrote: > > Without the patch: > > Runtime: 74.5 minutes > > > With the patch: > > Runtime: 70 minutes > > Hm, I'd have hoped for a bit more runtime improvement. I did a second run with the patch, and that finished in 66 minutes, so there is some

Re: pg_upgrade failing for 200+ million Large Objects

Laurenz Albe writes: > On Fri, 2024-03-15 at 19:18 -0400, Tom Lane wrote: >> This patch seems to have stalled out again. In hopes of getting it >> over the finish line, I've done a bit more work to address the two >> loose ends I felt were probably essential to deal with: > Applies and builds

Re: pg_upgrade failing for 200+ million Large Objects

On Fri, 2024-03-15 at 19:18 -0400, Tom Lane wrote: > This patch seems to have stalled out again. In hopes of getting it > over the finish line, I've done a bit more work to address the two > loose ends I felt were probably essential to deal with: Applies and builds fine. I didn't scrutinize the

Re: pg_upgrade failing for 200+ million Large Objects

This patch seems to have stalled out again. In hopes of getting it over the finish line, I've done a bit more work to address the two loose ends I felt were probably essential to deal with: * Duplicative blob ACLs are now merged into a single TOC entry (per metadata group) with the GRANT/REVOKE

Re: pg_upgrade failing for 200+ million Large Objects

vignesh C writes: > CFBot shows that the patch does not apply anymore as in [1]: > === Applying patches on top of PostgreSQL commit ID > 46a0cd4cefb4d9b462d8cc4df5e7ecdd190bea92 === > === applying patch ./v9-005-parallel_pg_restore.patch > patching file src/bin/pg_upgrade/pg_upgrade.c > Hunk #3

Re: pg_upgrade failing for 200+ million Large Objects

On Tue, 2 Jan 2024 at 23:03, Kumar, Sachin wrote: > > > On 11/12/2023, 01:43, "Tom Lane" > > wrote: > > > I had initially supposed that in a parallel restore we could > > have child workers also commit after every N TOC items, but was > > soon disabused of that idea.

Re: pg_upgrade failing for 200+ million Large Objects

Nathan Bossart writes: > On Wed, Dec 20, 2023 at 06:47:44PM -0500, Tom Lane wrote: >> +char *cmdEnd = psprintf(" OWNER TO %s", >> fmtId(te->owner)); >> + >> +IssueCommandPerBlob(AH, te, "ALTER LARGE OBJECT ", >> cmdEnd); > This is just a nitpick,

Re: pg_upgrade failing for 200+ million Large Objects

On Fri, Jan 12, 2024 at 04:42:27PM -0600, Nathan Bossart wrote: > On Wed, Dec 20, 2023 at 06:47:44PM -0500, Tom Lane wrote: >> +char *cmdEnd = psprintf(" OWNER TO %s", >> fmtId(te->owner)); >> + >> +IssueCommandPerBlob(AH, te, "ALTER LARGE OBJECT ",

Re: pg_upgrade failing for 200+ million Large Objects

On Fri, Jan 05, 2024 at 03:02:34PM -0500, Tom Lane wrote: > On further reflection, there is a very good reason why it's done like > that. Because pg_upgrade is doing schema-only dump and restore, > there's next to no opportunity for parallelism within either pg_dump > or pg_restore. There's no

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Dec 20, 2023 at 06:47:44PM -0500, Tom Lane wrote: > * I did not invent a switch to control the batching of blobs; it's > just hard-wired at 1000 blobs per group here. Probably we need some > user knob for that, but I'm unsure if we want to expose a count or > just a boolean for one vs

Re: pg_upgrade failing for 200+ million Large Objects

I wrote: > "Kumar, Sachin" writes: >> I was not able to find email thread which details why we are not using >> parallel pg_restore for pg_upgrade. > Well, it's pretty obvious isn't it? The parallelism is being applied > at the per-database level instead. On further reflection, there is a very

Re: pg_upgrade failing for 200+ million Large Objects

"Kumar, Sachin" writes: >> On 11/12/2023, 01:43, "Tom Lane" > > wrote: >> ... Maybe that >> could be improved in future, but it seems like it'd add a >> lot more complexity, and it wouldn't make life any better for >> pg_upgrade (which doesn't use parallel pg_restore,

Re: pg_upgrade failing for 200+ million Large Objects

> On 11/12/2023, 01:43, "Tom Lane" > wrote: > I had initially supposed that in a parallel restore we could > have child workers also commit after every N TOC items, but was > soon disabused of that idea. After a worker processes a TOC > item, any dependent items (such

Re: pg_upgrade failing for 200+ million Large Objects

On Thu, 28 Dec 2023 at 01:48, Tom Lane wrote: > Robins Tharakan writes: > > Applying all 4 patches, I also see good performance improvement. > > With more Large Objects, although pg_dump improved significantly, > > pg_restore is now comfortably an order of magnitude faster. > > Yeah. The key

Re: pg_upgrade failing for 200+ million Large Objects

Robins Tharakan writes: > Applying all 4 patches, I also see good performance improvement. > With more Large Objects, although pg_dump improved significantly, > pg_restore is now comfortably an order of magnitude faster. Yeah. The key thing here is that pg_dump can only parallelize the data

Re: pg_upgrade failing for 200+ million Large Objects

Nathan Bossart writes: > Wow, thanks for putting together these patches. I intend to help review, Thanks! > but I'm not sure I'll find much time to do so before the new year. There's no urgency, surely. If we can get these in during the January CF, I'll be happy.

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Dec 20, 2023 at 06:47:44PM -0500, Tom Lane wrote: > I have spent some more effort in this area and developed a patch > series that I think addresses all of the performance issues that > we've discussed in this thread, both for pg_upgrade and more > general use of pg_dump/pg_restore.

Re: pg_upgrade failing for 200+ million Large Objects

I have spent some more effort in this area and developed a patch series that I think addresses all of the performance issues that we've discussed in this thread, both for pg_upgrade and more general use of pg_dump/pg_restore. Concretely, it absorbs the pg_restore --transaction-size switch that I

Re: pg_upgrade failing for 200+ million Large Objects

I spent some time looking at the v7 patch. I can't help feeling that this is going off in the wrong direction, primarily for these reasons: * It focuses only on cutting the number of transactions needed to restore a large number of blobs (large objects). Certainly that's a pain point, but it's

Re: pg_upgrade failing for 200+ million Large Objects

> I have updated the patch to use heuristic, During pg_upgrade we count > Large objects per database. During pg_restore execution if db large_objects > count is greater than LARGE_OBJECTS_THRESOLD (1k) we will use > --restore-blob-batch-size. I think both SECTION_DATA and SECTION_POST_DATA can

Re: pg_upgrade failing for 200+ million Large Objects

> "Tom Lane" mailto:t...@sss.pgh.pa.us>> wrote: > FWIW, I agree with Jacob's concern about it being a bad idea to let > users of pg_upgrade pass down arbitrary options to pg_dump/pg_restore. > I think we'd regret going there, because it'd hugely expand the set > of cases pg_upgrade has to deal

Re: pg_upgrade failing for 200+ million Large Objects

> On 09/11/2023, 18:41, "Tom Lane" > wrote: > Um ... you didn't attach the patch? Sorry , patch attached Regards Sachin pg_upgrade_improvements_v6.diff Description: pg_upgrade_improvements_v6.diff

Re: pg_upgrade failing for 200+ million Large Objects

Hi, On November 9, 2023 10:41:01 AM PST, Tom Lane wrote: >Also, pg_upgrade is often invoked indirectly via scripts, so I do >not especially buy the idea that we're going to get useful control >input from some human somewhere. I think we'd be better off to >assume that pg_upgrade is on its own

Re: pg_upgrade failing for 200+ million Large Objects

[ Jacob's email address updated ] "Kumar, Sachin" writes: > Hi Everyone , I want to continue this thread , I have rebased the patch to > latest > master and fixed an issue when pg_restore prints to file. Um ... you didn't attach the patch? FWIW, I agree with Jacob's concern about it being a

Re: pg_upgrade failing for 200+ million Large Objects

 Hi Everyone , I want to continue this thread , I have rebased the patch to latest master and fixed an issue when pg_restore prints to file. ` ╰─$ pg_restore dump_small.custom --restore-blob-batch-size=2 --file=a -- -- End BLOB restore batch -- COMMIT; ` > On 09/11/2023, 17:05, "Jacob

Re: pg_upgrade failing for 200+ million Large Objects

On Thu, Sep 08, 2022 at 04:34:07PM -0700, Nathan Bossart wrote: > On Thu, Sep 08, 2022 at 04:29:10PM -0700, Jacob Champion wrote: >> To clarify, I agree that pg_dump should contain the core fix. What I'm >> questioning is the addition of --dump-options to make use of that fix >> from pg_upgrade,

Re: pg_upgrade failing for 200+ million Large Objects

On Thu, Sep 08, 2022 at 04:29:10PM -0700, Jacob Champion wrote: > On Thu, Sep 8, 2022 at 4:18 PM Nathan Bossart > wrote: >> IIUC the main benefit of this approach is that it isn't dependent on >> binary-upgrade mode, which seems to be a goal based on the discussion >> upthread [0]. > > To

Re: pg_upgrade failing for 200+ million Large Objects

On Thu, Sep 8, 2022 at 4:18 PM Nathan Bossart wrote: > IIUC the main benefit of this approach is that it isn't dependent on > binary-upgrade mode, which seems to be a goal based on the discussion > upthread [0]. To clarify, I agree that pg_dump should contain the core fix. What I'm questioning

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Sep 07, 2022 at 02:42:05PM -0700, Jacob Champion wrote: > Just to clarify, was Justin's statement upthread (that the XID problem > is fixed) correct? And is this patch just trying to improve the > remaining memory and lock usage problems? I think "fixed" might not be totally accurate, but

Re: pg_upgrade failing for 200+ million Large Objects

On 8/24/22 17:32, Nathan Bossart wrote: > I'd like to revive this thread, so I've created a commitfest entry [0] and > attached a hastily rebased patch that compiles and passes the tests. I am > aiming to spend some more time on this in the near future. Just to clarify, was Justin's statement

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Mar 24, 2021 at 12:05:27PM -0400, Jan Wieck wrote: > On 3/24/21 12:04 PM, Jan Wieck wrote: >> In any case I changed the options so that they behave the same way, the >> existing -o and -O (for old/new postmaster options) work. I don't think >> it would be wise to have option forwarding

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Mar 24, 2021 at 12:05:27PM -0400, Jan Wieck wrote: > On 3/24/21 12:04 PM, Jan Wieck wrote: > > In any case I changed the options so that they behave the same way, the > > existing -o and -O (for old/new postmaster options) work. I don't think > > it would be wise to have option forwarding

Re: pg_upgrade failing for 200+ million Large Objects

On 3/24/21 12:04 PM, Jan Wieck wrote: In any case I changed the options so that they behave the same way, the existing -o and -O (for old/new postmaster options) work. I don't think it would be wise to have option forwarding work differently between options for postmaster and options for

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 4:55 PM, Tom Lane wrote: Jan Wieck writes: Have we even reached a consensus yet on that doing it the way, my patch is proposing, is the right way to go? Like that emitting BLOB TOC entries into SECTION_DATA when in binary upgrade mode is a good thing? Or that bunching all the SQL

Re: pg_upgrade failing for 200+ million Large Objects

Jan Wieck writes: > Have we even reached a consensus yet on that doing it the way, my patch > is proposing, is the right way to go? Like that emitting BLOB TOC > entries into SECTION_DATA when in binary upgrade mode is a good thing? > Or that bunching all the SQL statements for creating the

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 3:35 PM, Tom Lane wrote: Jan Wieck writes: The problem here is that pg_upgrade itself is invoking a shell again. It is not assembling an array of arguments to pass into exec*(). I'd be a happy camper if it did the latter. But as things are we'd have to add full shell escapeing for

Re: pg_upgrade failing for 200+ million Large Objects

Jan Wieck writes: > The problem here is that pg_upgrade itself is invoking a shell again. It > is not assembling an array of arguments to pass into exec*(). I'd be a > happy camper if it did the latter. But as things are we'd have to add > full shell escapeing for arbitrary strings. Surely we

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 2:59 PM, Tom Lane wrote: Jan Wieck writes: On 3/23/21 2:35 PM, Tom Lane wrote: If you're passing multiple options, that is --pg-dump-options "--foo=x --bar=y" it seems just horribly fragile. Lose the double quotes and suddenly --bar is a separate option to pg_upgrade itself, not

Re: pg_upgrade failing for 200+ million Large Objects

Jan Wieck writes: > On 3/23/21 2:35 PM, Tom Lane wrote: >> If you're passing multiple options, that is >> --pg-dump-options "--foo=x --bar=y" >> it seems just horribly fragile. Lose the double quotes and suddenly >> --bar is a separate option to pg_upgrade itself, not part of the argument >> for

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 2:35 PM, Tom Lane wrote: Jan Wieck writes: So the question remains, how do we name this? --pg-dump-options "" --pg-restore-options "" If you're passing multiple options, that is --pg-dump-options "--foo=x --bar=y" it seems just horribly fragile. Lose the

Re: pg_upgrade failing for 200+ million Large Objects

Jan Wieck writes: > So the question remains, how do we name this? > --pg-dump-options "" > --pg-restore-options "" If you're passing multiple options, that is --pg-dump-options "--foo=x --bar=y" it seems just horribly fragile. Lose the double quotes and suddenly --bar is a

Re: pg_upgrade failing for 200+ million Large Objects

On Tue, Mar 23, 2021 at 02:23:03PM -0400, Jan Wieck wrote: > On 3/23/21 2:06 PM, Bruce Momjian wrote: > > We have the postmaster which can pass arbitrary arguments to postgres > > processes using -o. > > Right, and -o is already taken in pg_upgrade for sending options to the old > postmaster. >

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 2:06 PM, Bruce Momjian wrote: We have the postmaster which can pass arbitrary arguments to postgres processes using -o. Right, and -o is already taken in pg_upgrade for sending options to the old postmaster. What we are looking for are options for sending options to pg_dump and

Re: pg_upgrade failing for 200+ million Large Objects

On Tue, Mar 23, 2021 at 01:25:15PM -0400, Jan Wieck wrote: > On 3/23/21 10:56 AM, Bruce Momjian wrote: > > Would it be better to allow pg_upgrade to pass arbitrary arguments to > > pg_restore, instead of just these specific ones? > > > > That would mean arbitrary parameters to pg_dump as well as

Re: pg_upgrade failing for 200+ million Large Objects

On 3/23/21 10:56 AM, Bruce Momjian wrote: On Tue, Mar 23, 2021 at 08:51:32AM -0400, Jan Wieck wrote: On 3/22/21 7:18 PM, Jan Wieck wrote: > On 3/22/21 5:36 PM, Zhihong Yu wrote: > > Hi, > > > > w.r.t. pg_upgrade_improvements.v2.diff. > > > > +       blobBatchCount = 0; > > +      

Re: pg_upgrade failing for 200+ million Large Objects

On Tue, Mar 23, 2021 at 08:51:32AM -0400, Jan Wieck wrote: > On 3/22/21 7:18 PM, Jan Wieck wrote: > > On 3/22/21 5:36 PM, Zhihong Yu wrote: > > > Hi, > > > > > > w.r.t. pg_upgrade_improvements.v2.diff. > > > > > > +       blobBatchCount = 0; > > > +       blobInXact = false; > > > > > > The

Re: pg_upgrade failing for 200+ million Large Objects

On 3/22/21 7:18 PM, Jan Wieck wrote: On 3/22/21 5:36 PM, Zhihong Yu wrote: Hi, w.r.t. pg_upgrade_improvements.v2.diff. +       blobBatchCount = 0; +       blobInXact = false; The count and bool flag are always reset in tandem. It seems variable blobInXact is not needed. You are right.

Re: pg_upgrade failing for 200+ million Large Objects

On 3/22/21 5:36 PM, Zhihong Yu wrote: Hi, w.r.t. pg_upgrade_improvements.v2.diff. +       blobBatchCount = 0; +       blobInXact = false; The count and bool flag are always reset in tandem. It seems variable blobInXact is not needed. You are right. I will fix that. Thanks, Jan --

Re: pg_upgrade failing for 200+ million Large Objects

> > Hi, > w.r.t. pg_upgrade_improvements.v2.diff. + blobBatchCount = 0; + blobInXact = false; The count and bool flag are always reset in tandem. It seems variable blobInXact is not needed. Cheers

Re: pg_upgrade failing for 200+ million Large Objects

On 3/21/21 12:56 PM, Jan Wieck wrote: > On 3/21/21 7:47 AM, Andrew Dunstan wrote: >> One possible (probable?) source is the JDBC driver, which currently >> treats all Blobs (and Clobs, for that matter) as LOs. I'm working on >> improving that some: >

Re: Fix pg_upgrade to preserve datdba (was: Re: pg_upgrade failing for 200+ million Large Objects)

Jan Wieck writes: > On 3/20/21 12:39 AM, Jan Wieck wrote: >> On the way pg_upgrade also mangles the pg_database.datdba >> (all databases are owned by postgres after an upgrade; will submit a >> separate patch for that as I consider that a bug by itself). > Patch attached. Hmm, doesn't this lose

Re: pg_upgrade failing for 200+ million Large Objects

On 3/21/21 7:47 AM, Andrew Dunstan wrote: One possible (probable?) source is the JDBC driver, which currently treats all Blobs (and Clobs, for that matter) as LOs. I'm working on improving that some: You mean the user is using OID columns pointing

Fix pg_upgrade to preserve datdba (was: Re: pg_upgrade failing for 200+ million Large Objects)

On 3/20/21 12:39 AM, Jan Wieck wrote: On the way pg_upgrade also mangles the pg_database.datdba (all databases are owned by postgres after an upgrade; will submit a separate patch for that as I consider that a bug by itself). Patch attached. Regards, Jan -- Jan Wieck Principle Database

Re: pg_upgrade failing for 200+ million Large Objects

On 3/20/21 12:55 PM, Jan Wieck wrote: > On 3/20/21 11:23 AM, Tom Lane wrote: >> Jan Wieck writes: >>> All that aside, the entire approach doesn't scale. >> >> Yeah, agreed.  When we gave large objects individual ownership and ACL >> info, it was argued that pg_dump could afford to treat each

Re: pg_upgrade failing for 200+ million Large Objects

On 3/20/21 11:23 AM, Tom Lane wrote: Jan Wieck writes: All that aside, the entire approach doesn't scale. Yeah, agreed. When we gave large objects individual ownership and ACL info, it was argued that pg_dump could afford to treat each one as a separate TOC entry because "you wouldn't have

Re: pg_upgrade failing for 200+ million Large Objects

Bruce Momjian writes: > On Sat, Mar 20, 2021 at 11:23:19AM -0400, Tom Lane wrote: >> Of course, that just reduces the memory consumption on the client >> side; it does nothing for the locks. Can we get away with releasing the >> lock immediately after doing an ALTER OWNER or GRANT/REVOKE on a

Re: pg_upgrade failing for 200+ million Large Objects

On Sat, Mar 20, 2021 at 11:23:19AM -0400, Tom Lane wrote: > I wonder if pg_dump could improve matters cheaply by aggregating the > large objects by owner and ACL contents. That is, do > > select distinct lomowner, lomacl from pg_largeobject_metadata; > > and make just *one* BLOB TOC entry for

Re: pg_upgrade failing for 200+ million Large Objects

Jan Wieck writes: > On 3/8/21 11:58 AM, Tom Lane wrote: >> So it seems like the path of least resistance is >> (a) make pg_upgrade use --single-transaction when calling pg_restore >> (b) document (better) how to get around too-many-locks failures. > That would first require to fix how pg_upgrade

Re: pg_upgrade failing for 200+ million Large Objects

On 3/20/21 12:39 AM, Jan Wieck wrote: > On 3/8/21 11:58 AM, Tom Lane wrote: >> The answer up to now has been "raise max_locks_per_transaction enough >> so you don't see the failure".  Having now consumed a little more >> caffeine, I remember that that works in pg_upgrade scenarios too, >> since

Re: pg_upgrade failing for 200+ million Large Objects

On 3/8/21 11:58 AM, Tom Lane wrote: The answer up to now has been "raise max_locks_per_transaction enough so you don't see the failure". Having now consumed a little more caffeine, I remember that that works in pg_upgrade scenarios too, since the user can fiddle with the target cluster's

Re: pg_upgrade failing for 200+ million Large Objects

On Wed, Mar 03, 2021 at 11:36:26AM +, Tharakan, Robins wrote: > While reviewing a failed upgrade from Postgres v9.5 (to v9.6) I saw that the > instance had ~200 million (in-use) Large Objects. I was able to reproduce > this on a test instance which too fails with a similar error. If

Re: pg_upgrade failing for 200+ million Large Objects

On Mon, Mar 8, 2021 at 5:58 PM Tom Lane wrote: > > Magnus Hagander writes: > > On Mon, Mar 8, 2021 at 5:33 PM Tom Lane wrote: > >> It does seem that --single-transaction is a better idea than fiddling with > >> the transaction wraparound parameters, since the latter is just going to > >> put

Re: pg_upgrade failing for 200+ million Large Objects

Magnus Hagander writes: > On Mon, Mar 8, 2021 at 5:33 PM Tom Lane wrote: >> It does seem that --single-transaction is a better idea than fiddling with >> the transaction wraparound parameters, since the latter is just going to >> put off the onset of trouble. However, we'd have to do something

Re: pg_upgrade failing for 200+ million Large Objects

On Mon, Mar 8, 2021 at 5:33 PM Tom Lane wrote: > > Robins Tharakan writes: > > On Mon, 8 Mar 2021 at 23:34, Magnus Hagander wrote: > >> Without looking, I would guess it's the schema reload using > >> pg_dump/pg_restore and not actually pg_upgrade itself. This is a known > >> issue in

Re: pg_upgrade failing for 200+ million Large Objects

Robins Tharakan writes: > On Mon, 8 Mar 2021 at 23:34, Magnus Hagander wrote: >> Without looking, I would guess it's the schema reload using >> pg_dump/pg_restore and not actually pg_upgrade itself. This is a known >> issue in pg_dump/pg_restore. And if that is the case -- perhaps just >>

Re: pg_upgrade failing for 200+ million Large Objects

Hi Magnus, On Mon, 8 Mar 2021 at 23:34, Magnus Hagander wrote: > AFAICT at a quick check, pg_dump in binary upgrade mode emits one lo_create() and one ALTER ... OWNER TO for each large object - so with > 500M large objects that would be a billion statements, and thus a > billion xids. And

Re: pg_upgrade failing for 200+ million Large Objects

On Mon, Mar 8, 2021 at 12:02 PM Tharakan, Robins wrote: > > Thanks Peter. > > The original email [1] had some more context that somehow didn't get > associated with this recent email. Apologies for any confusion. Please take a look at your email configuration -- all your emails are lacking both

Re: pg_upgrade failing for 200+ million Large Objects

nal Message- > From: Peter Eisentraut > Sent: Monday, 8 March 2021 9:25 PM > To: Tharakan, Robins ; pgsql-hack...@postgresql.org > Subject: [EXTERNAL] [UNVERIFIED SENDER] Re: pg_upgrade failing for 200+ > million Large Objects > > CAUTION: This email originated from outside

RE: pg_upgrade failing for 200+ million Large Objects

age- > From: Daniel Gustafsson > Sent: Monday, 8 March 2021 9:42 AM > To: Tharakan, Robins > Cc: pgsql-hack...@postgresql.org > Subject: RE: [EXTERNAL] pg_upgrade failing for 200+ million Large Objects > > CAUTION: This email originated from outside of the organization. Do n

Re: pg_upgrade failing for 200+ million Large Objects

On 07.03.21 09:43, Tharakan, Robins wrote: Attached is a proof-of-concept patch that allows Postgres to perform pg_upgrade if the instance has Millions of objects. It would be great if someone could take a look and see if this patch is in the right direction. There are some pending tasks (such

Re: pg_upgrade failing for 200+ million Large Objects

> On 7 Mar 2021, at 09:43, Tharakan, Robins wrote: > The patch (attached): > - Applies cleanly on REL9_6_STABLE - > c7a4fc3dd001646d5938687ad59ab84545d5d043 Did you target 9.6 because that's where you want to upgrade to, or is this not a problem on HEAD? If it's still a problem on HEAD you