Re: [GENERAL] Redo the filenode link in tablespace - Mailing list pgsql-general

On 05/31/2017 06:05 AM, tel medola wrote:

Hi.
I have a rather serious problem in my database. I'll try to summarize what happened and how far I've gotten with the help of friends from the pgsql-sql list.

When I returned the copy of the drives, the records were no longer found. For example, if I make a "select count (*) from" 01052016 ".repository", the record amount will result to 0. But all the binaries are there, intact.

As I said above, with the help of friends from the pgsql-sql list, I managed to find the problem. When I did the truncate, the data was erased and the filenode was recreated and pointed to a zero file. Doing this query:
select pg_relation_filenode ('01052016.repository' :: regclass), it returns me: 13741352, when the correct link (before truncate) was 5214489.

Now, doing this other query:
select c. *
 From pg_class c
Where c.relfilenode = 13741352
He returns me:

relnamerelnamespacereltypereloftyperelownerrelam*relfilenode*reltablespacerelpagesreltuplesrelallvisiblereltoastrelidreltoastidxidrelhasindexrelissharedrelpersistencerelkindrelnattsrelchecksrelhasoidsrelhaspkeyrelhasrulesrelhastriggersrelhassubclassrelispopulatedrelfrozenxidrelminmxidrelaclreloptions
repositorio520596252144910100*13741352*520591000052144930TrueFalsepr70FalseTrueFalseFalseFalseTrue93602881

Now the question I need the help of friends ...

Is it possible to re-link to the item before truncate?

Did you get any help with this?

I noticed that it is not only a change of the field relfilenode, because there are more fields to be informed, such as (relpages, reltuples).

Well the relpages, reltuples are estimated values that can be updated with an ANALYZE.

Has anyone ever had to do this and succeeded?


Every help is welcome. I have a part of the production stopped and some jobs are at risk.

Thanks
Roberto.

--
Adrian Klaver
adrian.klaver@aklaver.com