Re: Redo the filenode link in tablespace

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(dot)klaver(at)aklaver(dot)com