Fwd: When are largobject records TOASTed into pg_toast_2613?

[Thomas Boussekey <thomas.boussekey@xxxxxxxxx>]

---------- Forwarded message ---------
De : Thomas Boussekey <thomas.boussekey@xxxxxxxxx>
Date: ven. 21 août 2020 à 15:37
Subject: Re: When are largobject records TOASTed into pg_toast_2613?
To: Laurenz Albe <laurenz.albe@xxxxxxxxxxx>

Le ven. 21 août 2020 à 15:10, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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