Rebuild pg_toast from scratch?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Rebuild pg_toast from scratch?

Moreno Andreo
Hi,
     I have an issue with a Windows 10 PC with Postgres 9.1 x86.
Running some SELECTs we got "ERROR: could not open file
"base/48121/784576": No such file or directory"

I then ran

select
n.nspname AS tableschema,
     c.relname AS tablename
from pg_class c
inner join pg_namespace n on (c.relnamespace = n.oid)
where c.relfilenode = 784576;

and discovered that the involved table is pg_toast_49713.

Now I need to recreate an empty copy of this table (data can be
recovered later), how can I achieve this?

Thanks in advance
Moreno.-





Reply | Threaded
Open this post in threaded view
|

Re: Rebuild pg_toast from scratch?

Tom Lane-2
Moreno Andreo <[hidden email]> writes:
>      I have an issue with a Windows 10 PC with Postgres 9.1 x86.

You realize 9.1 is long out of support ...

> Running some SELECTs we got "ERROR: could not open file
> "base/48121/784576": No such file or directory"

Ugh :-(

> Now I need to recreate an empty copy of this table (data can be
> recovered later), how can I achieve this?

TRUNCATE ought to be enough.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Rebuild pg_toast from scratch?

Moreno Andreo
Hi Tom and thanks for your time,

Il 05/09/19 15:53, Tom Lane ha scritto:
> Moreno Andreo <[hidden email]> writes:
>>       I have an issue with a Windows 10 PC with Postgres 9.1 x86.
> You realize 9.1 is long out of support ...
Absolutely :-)
I'm about to migrate it to 11
> Now I need to recreate an empty copy of this table (data can be
> recovered later), how can I achieve this?
> TRUNCATE ought to be enough.
truncate pg_toast_49713;

ERROR: relation "pg_toast_49713" does not exist

:-\
... or do I need to truncate the "master" table (the table blobs are in)?

Thanks



Reply | Threaded
Open this post in threaded view
|

Re: Rebuild pg_toast from scratch?

Tom Lane-2
Moreno Andreo <[hidden email]> writes:
> Il 05/09/19 15:53, Tom Lane ha scritto:
>> TRUNCATE ought to be enough.

> ... or do I need to truncate the "master" table (the table blobs are in)?

Yeah, that one.

                        regards, tom lane