---------- Forwarded message --------- De : Thomas Boussekey <thomas.bousse...@gmail.com> Date: ven. 21 août 2020 à 15:37 Subject: Re: When are largobject records TOASTed into pg_toast_2613? To: Laurenz Albe <laurenz.a...@cybertec.at>
Le ven. 21 août 2020 à 15:10, Laurenz Albe <laurenz.a...@cybertec.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 > >
