Horton
Menu

Re: pg_upgrade failing for 200+ million Large Objects

Tom Lane <tgl@sss.pgh.pa.us>

View thread
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robins Tharakan <tharakan@gmail.com>
Cc: Magnus Hagander <magnus@hagander.net>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2021-03-08T16:33:02Z
Lists: pgsql-hackers 
Robins Tharakan <tharakan@gmail.com> writes:
> On Mon, 8 Mar 2021 at 23:34, Magnus Hagander <magnus@hagander.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?

			regards, tom lane

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Count individual SQL commands in pg_restore's --transaction-size mode.

  2. Reduce number of commands dumpTableSchema emits for binary upgrade.

  3. Invent --transaction-size option for pg_restore.

  4. Rearrange pg_dump's handling of large objects for better efficiency.

  5. Add temporal PRIMARY KEY and UNIQUE constraints

  6. Fix typo and case in messages