Re: When are largobject records TOASTed into pg_toast_2613?

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

> On Fri, 2020-08-21 at 15:46 +0200, Thomas Boussekey wrote:
> > 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.
> > >
> > > Did you do any strange catalog modifications?
> >
> > 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.
>
> I just tried that on v12, and it didn't create a TOAST table.
>
> But obviously there is/was a bug somewhere.
>
> > > 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
>
> No, pg_dumpall will not duplicate that strange TOAST table.
> It would be the only safe way to upgrade.
>
> If you can ascertain that the TOAST table is empty and you
> like to live dangerous, you can try:
>
> UPDATE pg_class SET reltoastrelid = 0 WHERE oid = 2613;
> UPDATE pg_class SET relkind = 'r' WHERE relname = 'pg_toast_2613';
> DELETE FROM pg_depend WHERE classid = 1259 AND refclassid = 1259 AND
> refobjid = 2613 AND objid = 'pg_toast.pg_toast_2613'::regclass;
> DROP TABLE pg_toast.pg_toast_2613;
>
Thanks Laurenz & Tom for your precious information.

I wrote this BASH script to remove the TOAST table, if it may help anyone:

```sh
#!/usr/bin/env bash
#

set -euo pipefail

database_name="xxx"
postgresql_conf_file="/xxx/postgresql.conf"

# Step 1: check if table pg_toast_2613 exists
toast_count="$(psql -U postgres --dbname=${database_name} -At --no-psqlrc
-c "select count(*) from pg_class where relname = 'pg_toast_2613';")"
echo -e "TOAST exists ::${toast_count}"

if [[ "${toast_count}" == "1" ]]; then
# Step 2: Check if table pg_toast_2613 has rows and pages
toast_pages="$(psql -U postgres --dbname=${database_name} -At --no-psqlrc
-c "select relpages from pg_class where relname = 'pg_toast_2613';" )"
toast_tuples="$(psql -U postgres --dbname=${database_name} -At
--no-psqlrc -c "select reltuples from pg_class where relname =
'pg_toast_2613';" )"

echo -e "TOAST content pages ::${toast_pages}:: tuples ::${toast_tuples}"

# Step 3 OPTIONAL: vacuum full pg_largobject if needed
if [[ "${toast_tuples}" -gt "0" ]]; then

echo -e "Start of vacuum"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "VACUUM
FULL VERBOSE pg_largobject;"
echo -e "End of vacuum"

## After VACUUM post-check
toast_pages="$(psql -U postgres --dbname=${database_name} -At
--no-psqlrc -c "select relpages from pg_class where relname =
'pg_toast_2613';" )"
toast_tuples="$(psql -U postgres --dbname=${database_name} -At
--no-psqlrc -c "select reltuples from pg_class where relname =
'pg_toast_2613';" )"

echo -e "TOAST content pages ::${toast_pages}:: tuples
::${toast_tuples}"
fi

# Step 4: Remove TOAST information for pg_largobject into pg_class
echo -e "Remove TOAST on pg_largobject"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "UPDATE
pg_class SET reltoastrelid = 0 WHERE oid = 2613;"

# Step 5: Drop pg_toast_2613% objects
echo -e "Change pg_toast_2613 type to relation"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "UPDATE
pg_class SET relkind = 'r' WHERE relname = 'pg_toast_2613';"

echo -e "Delete pg_depend for pg_toast_2613"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "DELETE
FROM pg_depend WHERE classid = 1259 AND refclassid = 1259 AND refobjid =
2613 AND objid = 'pg_toast.pg_toast_2613'::regclass;"

echo "allow_system_table_mods=on" >> "${postgresql_conf_file}"
systemctl restart postgresql-9.5.service

echo -e "Drop relation pg_toast_2613"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "DROP TABLE
pg_toast.pg_toast_2613;"

sed -i '/^postgres_enable_version:/d' ${postgresql_conf_file}
systemctl restart postgresql-9.5.service
fi
```
My PostgreSQL instance is OK, and the migration to PostgreSQL12 is
sucessful.
I continue testing the instance

Have a nice week-end,
Thomas

>
> But I won't guarantee that that won't break your database.
>
> In particular, it is a no-go unless the TOAST table is empty.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>