From: | Magnus Hagander <magnus(at)hagander(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Robins Tharakan <tharakan(at)gmail(dot)com>, Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: pg_upgrade failing for 200+ million Large Objects |
Date: | 2021-03-08 16:35:56 |
Message-ID: | CABUevEzvU07CqwGdaOmxNfDtrkY-xEcLjiN3GAmurowyCnbG7w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Mar 8, 2021 at 5:33 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Robins Tharakan <tharakan(at)gmail(dot)com> writes:
> > On Mon, 8 Mar 2021 at 23:34, Magnus Hagander <magnus(at)hagander(dot)net> 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
> >> running all of those in a single transaction would be a better choice?
> >> One could argue it's still not a proper fix, because we'd still have a
> >> huge memory usage etc, but it would then only burn 1 xid instead of
> >> 500M...
>
> > (I hope I am not missing something but) When I tried to force pg_restore to
> > use a single transaction (by hacking pg_upgrade's pg_restore call to use
> > --single-transaction), it too failed owing to being unable to lock so many
> > objects in a single transaction.
>
> 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 about
> the lock consumption. Would it be sane to have the backend not bother to
> take any locks in binary-upgrade mode?
I believe the problem occurs when writing them rather than when
reading them, and I don't think we have a binary upgrade mode there.
We could invent one of course. Another option might be to exclusively
lock pg_largeobject, and just say that if you do that, we don't have
to lock the individual objects (ever)?
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Förster | 2021-03-08 16:40:22 | Re: proposal: psql –help reflecting service or URI usage |
Previous Message | Matthias van de Meent | 2021-03-08 16:33:40 | Re: Improvements and additions to COPY progress reporting |