Fwd: When are largobject records TOASTed into pg_toast_2613?

---------- Forwarded message ---------
De : Thomas Boussekey <thomas(dot)boussekey(at)gmail(dot)com>
Date: ven. 21 août 2020 à 15:37
Subject: Re: When are largobject records TOASTed into pg_toast_2613?
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>

Le ven. 21 août 2020 à 15:10, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> a
écrit :

> On Fri, 2020-08-21 at 14:00 +0200, Thomas Boussekey wrote:
> > Working on a PostgreSQL 9.5 to 12 upgrade, I encounter problems on a
> PostgreSQL instance when I have
> > an existing table `pg_toast_2613` into my application database.
> >
> > The upgrade process fails with the following error:
> >
> > ```
> > No match found in new cluster for old relation with OID 16619 in
> database "mirakl_db": "pg_toast.pg_toast_2613" which is the TOAST table for
> "pg_catalog.pg_largeobject"
> > No match found in new cluster for old relation with OID 16621 in
> database "mirakl_db": "pg_toast.pg_toast_2613_index" which is an index on
> "pg_toast.pg_toast_2613" which is the TOAST table for
> > "pg_catalog.pg_largeobject"
> > ```
> >
> > The `pg_upgrade` command fails when I have the table `pg_toast_2613`
> that exists, even if it is empty.
> > I read the PostgreSQL documentation, and I didn't find when the
> pg_largeobject table needs to be toasted.
> > I thought it might be linked with records' size, but my queries below
> don't correlate that!
>
> Indeed, system tables have no TOAST tables in PostgreSQL, so I wonder
> how your "pg_largeobject" table could have grown one.
>
Several years before I arrived in this company, the `pg_largeobject` table
had been moved to a dedicated tablespace located on a low-IOPS mechanical
disk.
One of my first projects when I started working in the company was to move
the `pg_largeobject` table back to the default system tablespace.
This might be a side-effect of the migration.

>
> Did you do any strange catalog modifications?
>
> The safest way would be to upgrade with pg_dumpall/psql.
>
The `pg_dumpall` command will also copy the content and the existence of
the `pg_toast_2613` table, isn't it?
It might generate errors at the execution on the new instance?
Moreover, it will generate a large downtime

> That should get rid of that data corruption.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>