Re: [GENERAL] Redo the filenode link in tablespace

2017-06-08 Thread Adrian Klaver 

On 06/07/2017 06:05 PM, tel medola wrote:

Holy shit! (sorry)

Thanks, thanks!!!

It worked!

My goodness
After I point to the filnode, I did a reindex on the toast and some 
records have already been located.


Great, glad that it worked. Just realize we have been modifying a system 
catalog(pg_class) directly, which is generally a dangerous thing to do. 
I would take a backup of the table and data via pg_dump to be on the 
safe side. This assumes you have the space to store it, though the 
backup when compressed should be a good deal smaller then the actual 
table size. You might also think about moving the tables to a new 
tablespace to get a clean tablespace directory. Again this assumes space 
available.




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-08 Thread Karsten Hilbert 
On Thu, Jun 08, 2017 at 08:11:30AM -0300, tel medola wrote:

> Sure!
> It's going to be a little long,

That's the point :-)

That way, people of the future can benefit from
Adrian's excellent effort.

Thanks,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-08 Thread tel medola 
Sure!
It's going to be a little long, but I write with great pleasure.

2017-06-08 8:04 GMT-03:00 Karsten Hilbert :

> On Thu, Jun 08, 2017 at 07:53:01AM -0300, tel medola wrote:
>
> > I would like to thank Adrian very much for his great help and patience.
> > Without your help, most likely I would be looking for another job now,
> > thank you very much !!!
> >
> > Thanks to the database being Postgres and the community being so strong
> and
> > united, everything worked out in the end.
> > Thank you very much the people who maintains Postgres (I could even help
> in
> > some, because I'm a programmer too). And
> > Thanks also to the people who dedicate their time to helping unknow
> people
> > with problems, especially to Adrian.
>
> Hi, I wonder whether you might muster the time to do a
> writeup for the benefit of the list archive -- describe what
> the problem was, how it came about, and how it was solved ?
>
> Thanks,
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-08 Thread Karsten Hilbert 
On Thu, Jun 08, 2017 at 07:53:01AM -0300, tel medola wrote:

> I would like to thank Adrian very much for his great help and patience.
> Without your help, most likely I would be looking for another job now,
> thank you very much !!!
> 
> Thanks to the database being Postgres and the community being so strong and
> united, everything worked out in the end.
> Thank you very much the people who maintains Postgres (I could even help in
> some, because I'm a programmer too). And
> Thanks also to the people who dedicate their time to helping unknow people
> with problems, especially to Adrian.

Hi, I wonder whether you might muster the time to do a
writeup for the benefit of the list archive -- describe what
the problem was, how it came about, and how it was solved ?

Thanks,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-08 Thread tel medola 
Hello guys.
I would like to thank Adrian very much for his great help and patience.
Without your help, most likely I would be looking for another job now,
thank you very much !!!

Thanks to the database being Postgres and the community being so strong and
united, everything worked out in the end.
Thank you very much the people who maintains Postgres (I could even help in
some, because I'm a programmer too). And
Thanks also to the people who dedicate their time to helping unknow people
with problems, especially to Adrian.

Thanks
Roberto.

2017-06-07 22:05 GMT-03:00 tel medola :

> Holy shit! (sorry)
>
> Thanks, thanks!!!
>
> It worked!
>
> My goodness
> After I point to the filnode, I did a reindex on the toast and some
> records have already been located.
>
>
>
> 2017-06-07 17:58 GMT-03:00 Adrian Klaver :
>
>> On 06/07/2017 07:53 AM, tel medola wrote:
>>
>>>
>>> Change the relfilenode in above from 13741353 to 5214493
>>> /I' no change yeat, but i will.../
>>>
>>>
>>>
>> What is not clear is what 5214495 is?
>>> /Not to me either/
>>>
>>> select * from pg_class where relfilenode = 5214495;
>>> /returns: none records/
>>>
>>> But I'm worried about the select error. You are returning the
>>> table:pg_toast_9277966 not the pg_toast_5214489... bellow
>>> ERROR:  missing chunk number 0 for toast value 10259186 in
>>> pg_toast_9277966
>>>
>>> Could it be because of the filenode link that is still pointing to
>>> another? -> 13741353
>>>
>>
>>
>> That is what I am betting.
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-07 Thread tel medola 
Holy shit! (sorry)

Thanks, thanks!!!

It worked!

My goodness
After I point to the filnode, I did a reindex on the toast and some records
have already been located.



2017-06-07 17:58 GMT-03:00 Adrian Klaver :

> On 06/07/2017 07:53 AM, tel medola wrote:
>
>>
>> Change the relfilenode in above from 13741353 to 5214493
>> /I' no change yeat, but i will.../
>>
>>
>>
> What is not clear is what 5214495 is?
>> /Not to me either/
>>
>> select * from pg_class where relfilenode = 5214495;
>> /returns: none records/
>>
>> But I'm worried about the select error. You are returning the
>> table:pg_toast_9277966 not the pg_toast_5214489... bellow
>> ERROR:  missing chunk number 0 for toast value 10259186 in
>> pg_toast_9277966
>>
>> Could it be because of the filenode link that is still pointing to
>> another? -> 13741353
>>
>
>
> That is what I am betting.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-07 Thread Adrian Klaver 

On 06/07/2017 07:53 AM, tel medola wrote:


Change the relfilenode in above from 13741353 to 5214493
/I' no change yeat, but i will.../





What is not clear is what 5214495 is?
/Not to me either/

select * from pg_class where relfilenode = 5214495;
/returns: none records/

But I'm worried about the select error. You are returning the 
table:pg_toast_9277966 not the pg_toast_5214489... bellow

ERROR:  missing chunk number 0 for toast value 10259186 in pg_toast_9277966

Could it be because of the filenode link that is still pointing to 
another? -> 13741353



That is what I am betting.


--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-07 Thread tel medola 
Change the relfilenode in above from 13741353 to 5214493
*I' no change yeat, but i will...*


select * from pg_classs where reltoastrelid = 9277970
returns:

*   oid   |   relname   | relnamespace | reltype | reloftype | relowner |
relam | relfilenode | reltablespace | relpages |  reltuples   |
relallvisible | reltoastrelid | reltoastidxid | relhasindex | relisshared |
relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey |
relhasrules | relhastriggers | relhassubclass | relispopulated |
relfrozenxid | relminmxid | relacl | reloptions *
*-+-+--+-+---+--+---+-+---+--+--+---+---+---+-+-++-+--+---+++-++++--+++*
* 9277966 | repositorio |  9277964 | 9277968 | 0 |   10 |
  0 | 9277966 |   9277962 |43779 | 1.50905e+006 | 43779
|   9277970 | 0 | t   | f   | p
 | r   |6 | 0 | f  | t  | f   |
f  | f  | t  |  9360288 |
 1 || *
*(1 registro)*


What is not clear is what 5214495 is?
*Not to me either*

select * from pg_class where relfilenode = 5214495;
*returns: none records*

But I'm worried about the select error. You are returning the
table:pg_toast_9277966 not the pg_toast_5214489... bellow
ERROR:  missing chunk number 0 for toast value 10259186 in pg_toast_9277966

Could it be because of the filenode link that is still pointing to another?
-> 13741353


2017-06-06 21:08 GMT-03:00 Adrian Klaver :

> On 06/06/2017 02:07 PM, tel medola wrote:
>
>> rai=# select oid, * from pg_class where oid = 5214493;
>>
>> result:
>>
>>
>> oid   | relname  | relnamespace | reltype | reloftype |
>> relowner | relam | relfilenode | reltablespace | relpages | reltuples |
>> relallvisible | reltoastrelid | reltoastidxid | relhasindex | relisshared |
>> relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey |
>> relhasrules | relhastriggers | relhassubclass | relispopulated |
>> relfrozenxid | relminmxid | relacl | reloptions
>> -+--+--+-+--
>> -+--+---+-+---+-
>> -+---+---+---+--
>> -+-+-++-
>> +--+---+++--
>> ---++++-
>> -+++
>>   5214493 | pg_toast_5214489 |   99 | 5214494 | 0 |
>>   10 | 0 |13741353 |   5205910 |0 | 0 |
>>   0 | 0 |   5214495 | t   | f   | p
>>   | t   |3 | 0 | f  | t   | f
>>  | f  | f  | t  |   9360288
>> |  1 ||
>> (1 registro)
>>
>
> Change the relfilenode in above from 13741353 to 5214493
>
>
> If I do the select below (which is toast it returns me in error):
>> Select oid, * from pg_class where relname = 'pg_toast_9277966'
>> He will return me:
>>
>>
> Not sure here. I would try:
>
> select * from pg_classs where reltoastrelid = 9277970;
>
> to see what table is claiming it.
>
>
>
>> I do not know if it helps, but the folder structure looks like this:
>>
>
> The file names = the relfilenode + additional info
>
> 5214489 is your repositorio table
> 5214489_fsm is the Free Space Map for that table.
>
> 5214493 and 5214493.x is your TOAST table broken down into 1 GB chunks.
>
> What is not clear is what 5214495 is?
>
> select * from pg_class where relfilenode = 5214495;
>
> might help, unless it was also TRUNCATEd. In that case it depends on you
> knowing what other table then repositorio you had in the tablespace.
>
>
>>   Volume in drive G is Gravacoes-III
>>   Volume Serial Number is 8CF9-EDFA
>>
>>   Directory of g:\PG_9.3_201306121\32768
>>
>> 06/06/2017  13:18  .
>> 06/06/2017  13:18  ..
>> 23/05/2017  08:25 0 13741352
>> 23/05/2017  08:25 0 13741353
>> 06/06/2017  12:04 8.192 13789591
>> 20/05/2016  09:10   649.650.176 5214489
>> 20/05/2016  09:10   180.224 5214489_fsm
>> 19/05/2016  23:4716.384 5214489_vm
>> 25/04/2016  09:00 1.073.741.824 5214493
>> 25/04/2016  09:00 1.073.741.824 5214493.1
>> 25/04/2016  09:00 1.073.741.824 5214493.10
>> 25/04/2016  09:00 1.073.741.824 5214493.100
>> 25/04/2016  09:00 1.073.741.824 5214493.101
>> 25/04/2016  09:00 1.073.741.824 5214493.102
>> 25/04/2016  09:00 1.073.741.824 5214493.103

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-06 Thread Adrian Klaver 

On 06/06/2017 02:07 PM, tel medola wrote:

rai=# select oid, * from pg_class where oid = 5214493;

result:


oid   | relname  | relnamespace | reltype | reloftype | 
relowner | relam | relfilenode | reltablespace | relpages | reltuples | 
relallvisible | reltoastrelid | reltoastidxid | relhasindex | 
relisshared | relpersistence | relkind | relnatts | relchecks | 
relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass 
| relispopulated | relfrozenxid | relminmxid | relacl | reloptions

-+--+--+-+---+--+---+-+---+--+---+---+---+---+-+-++-+--+---+++-++++--+++
  5214493 | pg_toast_5214489 |   99 | 5214494 | 0 | 
   10 | 0 |13741353 |   5205910 |0 | 0 | 
 0 | 0 |   5214495 | t   | f   | 
p  | t   |3 | 0 | f  | t 
  | f   | f  | f  | t  | 
  9360288 |  1 ||

(1 registro)


Change the relfilenode in above from 13741353 to 5214493



If I do the select below (which is toast it returns me in error):
Select oid, * from pg_class where relname = 'pg_toast_9277966'
He will return me:



Not sure here. I would try:

select * from pg_classs where reltoastrelid = 9277970;

to see what table is claiming it.




I do not know if it helps, but the folder structure looks like this:


The file names = the relfilenode + additional info

5214489 is your repositorio table
5214489_fsm is the Free Space Map for that table.

5214493 and 5214493.x is your TOAST table broken down into 1 GB chunks.

What is not clear is what 5214495 is?

select * from pg_class where relfilenode = 5214495;

might help, unless it was also TRUNCATEd. In that case it depends on you 
knowing what other table then repositorio you had in the tablespace.




  Volume in drive G is Gravacoes-III
  Volume Serial Number is 8CF9-EDFA

  Directory of g:\PG_9.3_201306121\32768

06/06/2017  13:18  .
06/06/2017  13:18  ..
23/05/2017  08:25 0 13741352
23/05/2017  08:25 0 13741353
06/06/2017  12:04 8.192 13789591
20/05/2016  09:10   649.650.176 5214489
20/05/2016  09:10   180.224 5214489_fsm
19/05/2016  23:4716.384 5214489_vm
25/04/2016  09:00 1.073.741.824 5214493
25/04/2016  09:00 1.073.741.824 5214493.1
25/04/2016  09:00 1.073.741.824 5214493.10
25/04/2016  09:00 1.073.741.824 5214493.100
25/04/2016  09:00 1.073.741.824 5214493.101
25/04/2016  09:00 1.073.741.824 5214493.102
25/04/2016  09:00 1.073.741.824 5214493.103
25/04/2016  09:00 1.073.741.824 5214493.104
25/04/2016  09:00 1.073.741.824 5214493.105
25/04/2016  09:00 1.073.741.824 5214493.106
25/04/2016  09:00 1.073.741.824 5214493.107
25/04/2016  09:00 1.073.741.824 5214493.108
25/04/2016  09:00 1.073.741.824 5214493.109
25/04/2016  09:00 1.073.741.824 5214493.11
25/04/2016  09:00 1.073.741.824 5214493.110
25/04/2016  09:00 1.073.741.824 5214493.111
25/04/2016  09:00 1.073.741.824 5214493.112
25/04/2016  09:00 1.073.741.824 5214493.113
25/04/2016  09:00 1.073.741.824 5214493.114
25/04/2016  09:00 1.073.741.824 5214493.115
25/04/2016  09:00 1.073.741.824 5214493.116
25/04/2016  09:00 1.073.741.824 5214493.117
25/04/2016  09:00 1.073.741.824 5214493.118
25/04/2016  09:00 1.073.741.824 5214493.119
25/04/2016  09:00 1.073.741.824 5214493.12
25/04/2016  09:00 1.073.741.824 5214493.120
25/04/2016  09:00 1.073.741.824 5214493.121
25/04/2016  09:00 1.073.741.824 5214493.122
25/04/2016  09:00 1.073.741.824 5214493.123
25/04/2016  09:00 1.073.741.824 5214493.124
25/04/2016  09:00 1.073.741.824 5214493.125
25/04/2016  09:00 1.073.741.824 5214493.126
25/04/2016  09:00 1.073.741.824 5214493.127
25/04/2016  09:00 1.073.741.824 5214493.128
25/04/2016  09:00 1.073.741.824 5214493.129
25/04/2016  09:00 1.073.741.824 5214493.13
25/04/2016  09:00 1.073.741.824 5214493.130
25/04/2016  09:00 1.073.741.824 5214493.131
25/04/2016  09:00 1.073.741.824 5214493.132
25/04/2016  09:00 1.073.741.824 5214493.133
25/04/2016  09:00 1.073.741.824 5214493.134
25/04/2016  09:00 1.073.741.824 5214493.135
25/04/2016  09:00 1.073.741.824 5214493.136
25/04/2016  09:00 1.073.741.824 5214493.137
25/04/2016  09:00 1.073.741.824 5214493.138
25/04/2016  09:00 1.073.741.824 5214493.139
25/04/2016  09:00 1.073.741.824 5214493.14
25/04/2016  09:00 1.073.741.824 5214493.140
25/04/2016  09:00 1.073.741.824 5214493.141

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-06 Thread tel medola 
rai=# select oid, * from pg_class where oid = 5214493;

result:


   oid   | relname  | relnamespace | reltype | reloftype | relowner
| relam | relfilenode | reltablespace | relpages | reltuples |
relallvisible | reltoastrelid | reltoastidxid | relhasindex | relisshared |
relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey |
relhasrules | relhastriggers | relhassubclass | relispopulated |
relfrozenxid | relminmxid | relacl | reloptions
-+--+--+-+---+--+---+-+---+--+---+---+---+---+-+-++-+--+---+++-++++--+++
 5214493 | pg_toast_5214489 |   99 | 5214494 | 0 |   10
| 0 |13741353 |   5205910 |0 | 0 |
0 | 0 |   5214495 | t   | f   | p
   | t   |3 | 0 | f  | t  | f
| f  | f  | t  |  9360288 |
 1 ||
(1 registro)


rai=# select count(*) from pg_toast.pg_toast_5214489;

result:

count
--
  0


If I do the select below (which is toast it returns me in error):
Select oid, * from pg_class where relname = 'pg_toast_9277966'
He will return me:

   oid   | relname  | relnamespace | reltype | reloftype | relowner
| relam | relfilenode | reltablespace | relpages | reltuples |
relallvisible | reltoastrelid | reltoastidxid | relhasindex | relisshared |
relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey |
relhasrules | relhastriggers | relhassubclass | relispopulated |
relfrozenxid | relminmxid | relacl | reloptions
-+--+--+-+---+--+---+-+---+--+---+---+---+---+-+-++-+--+---+++-++++--+++
 9277970 | pg_toast_9277966 |   99 | 9277971 | 0 |   10
| 0 |13741363 |   9277962 |0 | 0 |
0 | 0 |   9277972 | t   | f   | p
   | t   |3 | 0 | f  | t  | f
| f  | f  | t  |  9360288 |
 1 ||
(1 registro)


filenode-> 13741363

That is, it is looking at a toast that is empty because it was done so
after I run the truncate.
The old filenode of the table "05122016".repositorio it's the: 13741352
(before my change)

rai=# select count(*) from pg_toast.pg_toast_9277966;

result:

count
--
  0

I do not know if it helps, but the folder structure looks like this:

 Volume in drive G is Gravacoes-III
 Volume Serial Number is 8CF9-EDFA

 Directory of g:\PG_9.3_201306121\32768

06/06/2017  13:18  .
06/06/2017  13:18  ..
23/05/2017  08:25 0 13741352
23/05/2017  08:25 0 13741353
06/06/2017  12:04 8.192 13789591
20/05/2016  09:10   649.650.176 5214489
20/05/2016  09:10   180.224 5214489_fsm
19/05/2016  23:4716.384 5214489_vm
25/04/2016  09:00 1.073.741.824 5214493
25/04/2016  09:00 1.073.741.824 5214493.1
25/04/2016  09:00 1.073.741.824 5214493.10
25/04/2016  09:00 1.073.741.824 5214493.100
25/04/2016  09:00 1.073.741.824 5214493.101
25/04/2016  09:00 1.073.741.824 5214493.102
25/04/2016  09:00 1.073.741.824 5214493.103
25/04/2016  09:00 1.073.741.824 5214493.104
25/04/2016  09:00 1.073.741.824 5214493.105
25/04/2016  09:00 1.073.741.824 5214493.106
25/04/2016  09:00 1.073.741.824 5214493.107
25/04/2016  09:00 1.073.741.824 5214493.108
25/04/2016  09:00 1.073.741.824 5214493.109
25/04/2016  09:00 1.073.741.824 5214493.11
25/04/2016  09:00 1.073.741.824 5214493.110
25/04/2016  09:00 1.073.741.824 5214493.111
25/04/2016  09:00 1.073.741.824 5214493.112
25/04/2016  09:00 1.073.741.824 5214493.113
25/04/2016  09:00 1.073.741.824 5214493.114
25/04/2016  09:00 1.073.741.824 5214493.115
25/04/2016  09:00 1.073.741.824 5214493.116
25/04/2016  09:00 1.073.741.824 5214493.117
25/04/2016  09:00 1.073.741.824 5214493.118
25/04/2016  09:00 1.073.741.824 5214493.119
25/04/2016  09:00 1.073.741.824 5214493.12
25/04/2016  09:00 1.073.741.824 5214493.120
25/04/2016  09:00 1.073.741.824 5214493.121
25/04/2016  09:00 1.073.741.824 5214493.122
25/04/2016  09:00 1.073.741.824 5214493.123
25/04/2016  09:00 1.073.741.824 5214493.124
25/04/2016  09:00 1.073.741.824 5214493.125
25/04/2016  09:00 1.073.741.824 5214493.126

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-06 Thread Adrian Klaver 

On 06/06/2017 11:09 AM, tel medola wrote:

I will not be able to recover my information any more, right?


That is what I am trying to figure out.

The last error you got was:

"
Returns the error below:
Missing chunk number 0 for toast value 10259186 in pg_toast_9277966
"

This is related to the TOAST table that should be associated with your 
primary table:


https://www.postgresql.org/docs/9.6/static/storage-toast.html

When you did:

select * from pg_class where relfilenode = 5214489

where 5214489 is the relfilenode for the table repositorio you got among 
other things:


reltoastrelid
5214493

https://www.postgresql.org/docs/9.6/static/catalog-pg-class.html

where 5214493 is the OID for the TOAST table that is supposed to be 
associated with the repositorio table. That is why I wanted to see:


select oid, * from pg_class where oid = 5214493

to find out what relfilenode is for the TOAST table and then have you 
look for it or maybe change it.







2017-06-06 10:37 GMT-03:00 Adrian Klaver >:


On 06/06/2017 04:40 AM, tel medola wrote:

Lets go:
In my plsql:
rai=# select oid, * from pg_class where relfilenode = 5214489;


I was looking for:

select oid, * from pg_class where oid = 5214493;


Result:
 oid   |   relname   | relnamespace | reltype | reloftype |
relowner | relam | relfilenode | reltablespace | relpages | 
reltuples   | relallvisible | reltoastrelid | reltoastidxid |

relhasindex | relisshared | relpersistence | relkind | relnatts
| relchecks | relhasoids | relhaspkey | relhasrules |
relhastriggers | relhassubclass | relispopulated | relfrozenxid
| relminmxid | relacl | reloptions

-+-+--+-+---+--+---+-+---+--+--+---+---+---+-+-++-+--+---+++-++++--+++
   5214489 | repositorio |  5205962 | 5214491 | 0 | 
  10 | 0 | 5214489 |   5205910 |79303 |

1.31566e+006 |  79303 |   5214493 | 0 |
t   | f   | p   | r   |7
| 0 | f  | t   | f   | f   
   | f  | t  |   9360288 | 
 1 ||

(1 registro)

Ok?




-- 
Adrian Klaver

adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-06 Thread tel medola 
I will not be able to recover my information any more, right?

2017-06-06 10:37 GMT-03:00 Adrian Klaver :

> On 06/06/2017 04:40 AM, tel medola wrote:
>
>> Lets go:
>> In my plsql:
>> rai=# select oid, * from pg_class where relfilenode = 5214489;
>>
>
> I was looking for:
>
> select oid, * from pg_class where oid = 5214493;
>
>
>> Result:
>> oid   |   relname   | relnamespace | reltype | reloftype | relowner |
>> relam | relfilenode | reltablespace | relpages |  reltuples   |
>> relallvisible | reltoastrelid | reltoastidxid | relhasindex | relisshared |
>> relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey |
>> relhasrules | relhastriggers | relhassubclass | relispopulated |
>> relfrozenxid | relminmxid | relacl | reloptions
>> -+-+--+-+---
>> +--+---+-+---+--
>> +--+---+---+
>> ---+-+-++-+-
>> -+---+++
>> -++++---
>> ---+++
>>   5214489 | repositorio |  5205962 | 5214491 | 0 |   10
>> | 0 | 5214489 |   5205910 |79303 | 1.31566e+006 |
>> 79303 |   5214493 | 0 | t   | f   | p
>>  | r   |7 | 0 | f  | t   | f
>>| f  | f  | t  |   9360288
>> |  1 ||
>> (1 registro)
>>
>> Ok?
>>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-06 Thread Adrian Klaver 

On 06/06/2017 04:40 AM, tel medola wrote:

Lets go:
In my plsql:
rai=# select oid, * from pg_class where relfilenode = 5214489;


I was looking for:

select oid, * from pg_class where oid = 5214493;



Result:
oid   |   relname   | relnamespace | reltype | reloftype | relowner 
| relam | relfilenode | reltablespace | relpages |  reltuples   | 
relallvisible | reltoastrelid | reltoastidxid | relhasindex | 
relisshared | relpersistence | relkind | relnatts | relchecks | 
relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass 
| relispopulated | relfrozenxid | relminmxid | relacl | reloptions

-+-+--+-+---+--+---+-+---+--+--+---+---+---+-+-++-+--+---+++-++++--+++
  5214489 | repositorio |  5205962 | 5214491 | 0 |   10 
| 0 | 5214489 |   5205910 |79303 | 1.31566e+006 |   
   79303 |   5214493 | 0 | t   | f   | p 
  | r   |7 | 0 | f  | t 
  | f   | f  | f  | t  | 
  9360288 |  1 ||

(1 registro)

Ok?




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-06 Thread tel medola 
Lets go:
In my plsql:
rai=# select oid, * from pg_class where relfilenode = 5214489;

Result:
   oid   |   relname   | relnamespace | reltype | reloftype | relowner |
relam | relfilenode | reltablespace | relpages |  reltuples   |
relallvisible | reltoastrelid | reltoastidxid | relhasindex | relisshared |
relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey |
relhasrules | relhastriggers | relhassubclass | relispopulated |
relfrozenxid | relminmxid | relacl | reloptions
-+-+--+-+---+--+---+-+---+--+--+---+---+---+-+-++-+--+---+++-++++--+++
 5214489 | repositorio |  5205962 | 5214491 | 0 |   10 |
  0 | 5214489 |   5205910 |79303 | 1.31566e+006 | 79303
|   5214493 | 0 | t   | f   | p
 | r   |7 | 0 | f  | t  | f   |
f  | f  | t  |  9360288 |
 1 ||
(1 registro)

Ok?

2017-06-05 20:13 GMT-03:00 Adrian Klaver :

> On 06/05/2017 04:07 PM, tel medola wrote:
>
>> Hi,
>> attachment.
>>
>> select:
>> select * from pg_class where relfilenode = 5214489
>>
>
> Next, what do you get from:
>
> In psql
> => \x
> Expanded display is on.
>
> select oid, * from pg_class where oid = 5214493;
>
> No need to create an attachment, just cut and paste into message.
>
>
>
>> Thanks
>>
>> 2017-06-05 16:02 GMT-03:00 Adrian Klaver > >:
>>
>> On 06/05/2017 11:55 AM, tel medola wrote:
>>
>> show?
>> /Yes/
>>
>>
>> Yes, what?
>>
>> Please run the command as shown:
>>
>> select * from pg_class where relfilenode = 5214489;
>>
>> and show the result.
>>
>>
>> Do not delete the pg_statistic table. I would not even delete
>> from it.
>> /Sorry, I already deleted it. I looked for something official
>> and found in the site in postgres the reference that the delete
>> could be done
>> (https://www.postgresql.org/docs/9.1/static/release-7-4-2.html
>> )
>> and I ended up doing it. Now I'm running a vacuum analyze across
>> the base./
>>
>>
>> 2017-06-05 15:41 GMT-03:00 Adrian Klaver
>> 
>> >
>> >>:
>>
>>
>>  On 06/05/2017 07:17 AM, tel medola wrote:
>>
>>  Done.
>>  I followed the steps below after reconnecting the
>> filenode:
>>
>>  Select * from "05122016".repositorio
>>  Count -> 0
>>
>>  Then, I execute the commands:
>>  Analyze "05122016".repositorio;
>>  Count -> 0
>>  Reindex "05122016".repositorio;
>>  Count -> 1509046
>>  Yes!
>>
>>  But...
>>
>>
>>  select * from "05122016".repository where id_documento =
>>  '{A647885D-9738-4617-ACE6-6351F6ADD341}'
>>
>>  Returns the error below:
>>  Missing chunk number 0 for toast value 10259186 in
>> pg_toast_9277966
>>
>>
>>  What does:
>>
>>  select * from pg_class where relfilenode = 5214489;
>>
>>  show?
>>
>>
>>
>>  I read in the forum to run the command:
>>  Delete from pg_statistic;
>>  Reindex table pg_statistic;
>>  Vacuum analyze;
>>
>>  Is it okay to delete the pg_statistic table?
>>
>>
>>  Do not delete the pg_statistic table. I would not even
>> delete from it.
>>
>>  -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>> > >
>>
>>
>>
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-05 Thread Adrian Klaver 

On 06/05/2017 04:07 PM, tel medola wrote:

Hi,
attachment.

select:
select * from pg_class where relfilenode = 5214489


Next, what do you get from:

In psql
=> \x
Expanded display is on.

select oid, * from pg_class where oid = 5214493;

No need to create an attachment, just cut and paste into message.




Thanks

2017-06-05 16:02 GMT-03:00 Adrian Klaver >:


On 06/05/2017 11:55 AM, tel medola wrote:

show?
/Yes/


Yes, what?

Please run the command as shown:

select * from pg_class where relfilenode = 5214489;

and show the result.


Do not delete the pg_statistic table. I would not even delete
from it.
/Sorry, I already deleted it. I looked for something official
and found in the site in postgres the reference that the delete
could be done
(https://www.postgresql.org/docs/9.1/static/release-7-4-2.html
)
and I ended up doing it. Now I'm running a vacuum analyze across
the base./


2017-06-05 15:41 GMT-03:00 Adrian Klaver

>>:


 On 06/05/2017 07:17 AM, tel medola wrote:

 Done.
 I followed the steps below after reconnecting the filenode:

 Select * from "05122016".repositorio
 Count -> 0

 Then, I execute the commands:
 Analyze "05122016".repositorio;
 Count -> 0
 Reindex "05122016".repositorio;
 Count -> 1509046
 Yes!

 But...


 select * from "05122016".repository where id_documento =
 '{A647885D-9738-4617-ACE6-6351F6ADD341}'

 Returns the error below:
 Missing chunk number 0 for toast value 10259186 in
pg_toast_9277966


 What does:

 select * from pg_class where relfilenode = 5214489;

 show?



 I read in the forum to run the command:
 Delete from pg_statistic;
 Reindex table pg_statistic;
 Vacuum analyze;

 Is it okay to delete the pg_statistic table?


 Do not delete the pg_statistic table. I would not even
delete from it.

 -- Adrian Klaver
adrian.kla...@aklaver.com 
>




-- 
Adrian Klaver

adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-05 Thread tel medola 
Hi,
attachment.

select:
select * from pg_class where relfilenode = 5214489

Thanks

2017-06-05 16:02 GMT-03:00 Adrian Klaver :

> On 06/05/2017 11:55 AM, tel medola wrote:
>
>> show?
>> /Yes/
>>
>
> Yes, what?
>
> Please run the command as shown:
>
> select * from pg_class where relfilenode = 5214489;
>
> and show the result.
>
>
>> Do not delete the pg_statistic table. I would not even delete from it.
>> /Sorry, I already deleted it. I looked for something official and found
>> in the site in postgres the reference that the delete could be done (
>> https://www.postgresql.org/docs/9.1/static/release-7-4-2.html) and I
>> ended up doing it. Now I'm running a vacuum analyze across the base./
>>
>>
>> 2017-06-05 15:41 GMT-03:00 Adrian Klaver > >:
>>
>>
>> On 06/05/2017 07:17 AM, tel medola wrote:
>>
>> Done.
>> I followed the steps below after reconnecting the filenode:
>>
>> Select * from "05122016".repositorio
>> Count -> 0
>>
>> Then, I execute the commands:
>> Analyze "05122016".repositorio;
>> Count -> 0
>> Reindex "05122016".repositorio;
>> Count -> 1509046
>> Yes!
>>
>> But...
>>
>>
>> select * from "05122016".repository where id_documento =
>> '{A647885D-9738-4617-ACE6-6351F6ADD341}'
>>
>> Returns the error below:
>> Missing chunk number 0 for toast value 10259186 in
>> pg_toast_9277966
>>
>>
>> What does:
>>
>> select * from pg_class where relfilenode = 5214489;
>>
>> show?
>>
>>
>>
>> I read in the forum to run the command:
>> Delete from pg_statistic;
>> Reindex table pg_statistic;
>> Vacuum analyze;
>>
>> Is it okay to delete the pg_statistic table?
>>
>>
>> Do not delete the pg_statistic table. I would not even delete from it.
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
   relname   | relnamespace | reltype | reloftype | relowner | relam | 
relfilenode | reltablespace | relpages |  reltuples   | relallvisible | 
reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | 
relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | 
relhastriggers | relhassubclass | relispopulated | relfrozenxid | relminmxid | 
relacl | reloptions 
-+--+-+---+--+---+-+---+--+--+---+---+---+-+-++-+--+---+++-++++--+++
 repositorio |  5205962 | 5214491 | 0 |   10 | 0 | 
5214489 |   5205910 |79303 | 1.31566e+006 | 79303 |   
5214493 | 0 | t   | f   | p  | r   
|7 | 0 | f  | t  | f   | f  
| f  | t  |  9360288 |  1 || 
(1 registro)


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-05 Thread tel medola 
I will send as soon as possible. He's running the vacuum yet.

2017-06-05 16:02 GMT-03:00 Adrian Klaver :

> On 06/05/2017 11:55 AM, tel medola wrote:
>
>> show?
>> /Yes/
>>
>
> Yes, what?
>
> Please run the command as shown:
>
> select * from pg_class where relfilenode = 5214489;
>
> and show the result.
>
>
>> Do not delete the pg_statistic table. I would not even delete from it.
>> /Sorry, I already deleted it. I looked for something official and found
>> in the site in postgres the reference that the delete could be done (
>> https://www.postgresql.org/docs/9.1/static/release-7-4-2.html) and I
>> ended up doing it. Now I'm running a vacuum analyze across the base./
>>
>>
>> 2017-06-05 15:41 GMT-03:00 Adrian Klaver > >:
>>
>>
>> On 06/05/2017 07:17 AM, tel medola wrote:
>>
>> Done.
>> I followed the steps below after reconnecting the filenode:
>>
>> Select * from "05122016".repositorio
>> Count -> 0
>>
>> Then, I execute the commands:
>> Analyze "05122016".repositorio;
>> Count -> 0
>> Reindex "05122016".repositorio;
>> Count -> 1509046
>> Yes!
>>
>> But...
>>
>>
>> select * from "05122016".repository where id_documento =
>> '{A647885D-9738-4617-ACE6-6351F6ADD341}'
>>
>> Returns the error below:
>> Missing chunk number 0 for toast value 10259186 in
>> pg_toast_9277966
>>
>>
>> What does:
>>
>> select * from pg_class where relfilenode = 5214489;
>>
>> show?
>>
>>
>>
>> I read in the forum to run the command:
>> Delete from pg_statistic;
>> Reindex table pg_statistic;
>> Vacuum analyze;
>>
>> Is it okay to delete the pg_statistic table?
>>
>>
>> Do not delete the pg_statistic table. I would not even delete from it.
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-05 Thread Adrian Klaver 

On 06/05/2017 11:55 AM, tel medola wrote:

show?
/Yes/


Yes, what?

Please run the command as shown:

select * from pg_class where relfilenode = 5214489;

and show the result.



Do not delete the pg_statistic table. I would not even delete from it.
/Sorry, I already deleted it. I looked for something official and found 
in the site in postgres the reference that the delete could be done 
(https://www.postgresql.org/docs/9.1/static/release-7-4-2.html) and I 
ended up doing it. Now I'm running a vacuum analyze across the base./



2017-06-05 15:41 GMT-03:00 Adrian Klaver >:


On 06/05/2017 07:17 AM, tel medola wrote:

Done.
I followed the steps below after reconnecting the filenode:

Select * from "05122016".repositorio
Count -> 0

Then, I execute the commands:
Analyze "05122016".repositorio;
Count -> 0
Reindex "05122016".repositorio;
Count -> 1509046
Yes!

But...


select * from "05122016".repository where id_documento =
'{A647885D-9738-4617-ACE6-6351F6ADD341}'

Returns the error below:
Missing chunk number 0 for toast value 10259186 in pg_toast_9277966


What does:

select * from pg_class where relfilenode = 5214489;

show?



I read in the forum to run the command:
Delete from pg_statistic;
Reindex table pg_statistic;
Vacuum analyze;

Is it okay to delete the pg_statistic table?


Do not delete the pg_statistic table. I would not even delete from it.

-- 
Adrian Klaver

adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-05 Thread tel medola 
show?
*Yes*

Do not delete the pg_statistic table. I would not even delete from it.
*Sorry, I already deleted it. I looked for something official and found in
the site in postgres the reference that the delete could be done
(https://www.postgresql.org/docs/9.1/static/release-7-4-2.html
) and I
ended up doing it. Now I'm running a vacuum analyze across the base.*


2017-06-05 15:41 GMT-03:00 Adrian Klaver :

> On 06/05/2017 07:17 AM, tel medola wrote:
>
>> Done.
>> I followed the steps below after reconnecting the filenode:
>>
>> Select * from "05122016".repositorio
>> Count -> 0
>>
>> Then, I execute the commands:
>> Analyze "05122016".repositorio;
>> Count -> 0
>> Reindex "05122016".repositorio;
>> Count -> 1509046
>> Yes!
>>
>> But...
>>
>>
>> select * from "05122016".repository where id_documento =
>> '{A647885D-9738-4617-ACE6-6351F6ADD341}'
>>
>> Returns the error below:
>> Missing chunk number 0 for toast value 10259186 in pg_toast_9277966
>>
>
> What does:
>
> select * from pg_class where relfilenode = 5214489;
>
> show?
>
>
>
>> I read in the forum to run the command:
>> Delete from pg_statistic;
>> Reindex table pg_statistic;
>> Vacuum analyze;
>>
>> Is it okay to delete the pg_statistic table?
>>
>>
> Do not delete the pg_statistic table. I would not even delete from it.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-05 Thread Adrian Klaver 

On 06/05/2017 07:17 AM, tel medola wrote:

Done.
I followed the steps below after reconnecting the filenode:

Select * from "05122016".repositorio
Count -> 0

Then, I execute the commands:
Analyze "05122016".repositorio;
Count -> 0
Reindex "05122016".repositorio;
Count -> 1509046
Yes!

But...


select * from "05122016".repository where id_documento = 
'{A647885D-9738-4617-ACE6-6351F6ADD341}'


Returns the error below:
Missing chunk number 0 for toast value 10259186 in pg_toast_9277966


What does:

select * from pg_class where relfilenode = 5214489;

show?




I read in the forum to run the command:
Delete from pg_statistic;
Reindex table pg_statistic;
Vacuum analyze;

Is it okay to delete the pg_statistic table?



Do not delete the pg_statistic table. I would not even delete from it.

--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-05 Thread tel medola 
Done.
I followed the steps below after reconnecting the filenode:

Select * from "05122016".repositorio
Count -> 0

Then, I execute the commands:
Analyze "05122016".repositorio;
Count -> 0
Reindex "05122016".repositorio;
Count -> 1509046
Yes!

But...


select * from "05122016".repository where id_documento =
'{A647885D-9738-4617-ACE6-6351F6ADD341}'

Returns the error below:
Missing chunk number 0 for toast value 10259186 in pg_toast_9277966

I tried to do the commands below, but the error persists:
REINDEX table pg_toast.pg_toast_9277966;
VACUUM ANALYZE pg_toast_9277966
VACUUM ANALYZE "05122016".repositorio;

REINDEX TABLE pg_statistic;

I read in the forum to run the command:
Delete from pg_statistic;
Reindex table pg_statistic;
Vacuum analyze;

Is it okay to delete the pg_statistic table?

2017-06-05 9:24 GMT-03:00 tel medola :

> Sorry. I got sick these days and could not read my emails.
>
> Thanks for your help.
> I'll try to point to the direct node and see what happens.
>
> 2017-06-01 10:29 GMT-03:00 Adrian Klaver :
>
>> On 06/01/2017 03:47 AM, tel medola wrote:
>>
>>> Did you get any help with this?
>>> /I formatted correctly before sending the email. Maybe you should ask
>>> yourself if the mail server did not remove the formatting./
>>>
>>
>> I was talking about help with your relfilenode issue, I learned to deal
>> with the formatting awhile ago.
>>
>> /
>>> /
>>> Well the relpages, reltuples are estimated values that can be updated
>>> with an ANALYZE./
>>> /
>>>
>>> /I can not make analyze on a table whose filenode is pointing to another
>>> reference. The table is empty, just because the filenode does not point to
>>> the correct ID./
>>>
>>
>> Understood, I was just saying that if you could get the table pointing at
>> the right relfilenode the other values would be synced up with an ANALYZE.
>>
>> At this point it is time to just try something. You have file level
>> backups of the various backups, so you can restore that if something goes
>> wrong, correct?
>>
>> For 01052016.repositorio with current pg_class entry of relfilenode of
>> 13741352, change that back to the old entry of 5214489.
>>
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-05 Thread tel medola 
Sorry. I got sick these days and could not read my emails.

Thanks for your help.
I'll try to point to the direct node and see what happens.

2017-06-01 10:29 GMT-03:00 Adrian Klaver :

> On 06/01/2017 03:47 AM, tel medola wrote:
>
>> Did you get any help with this?
>> /I formatted correctly before sending the email. Maybe you should ask
>> yourself if the mail server did not remove the formatting./
>>
>
> I was talking about help with your relfilenode issue, I learned to deal
> with the formatting awhile ago.
>
> /
>> /
>> Well the relpages, reltuples are estimated values that can be updated
>> with an ANALYZE./
>> /
>>
>> /I can not make analyze on a table whose filenode is pointing to another
>> reference. The table is empty, just because the filenode does not point to
>> the correct ID./
>>
>
> Understood, I was just saying that if you could get the table pointing at
> the right relfilenode the other values would be synced up with an ANALYZE.
>
> At this point it is time to just try something. You have file level
> backups of the various backups, so you can restore that if something goes
> wrong, correct?
>
> For 01052016.repositorio with current pg_class entry of relfilenode of
> 13741352, change that back to the old entry of 5214489.
>
>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-01 Thread Adrian Klaver 

On 06/01/2017 03:47 AM, tel medola wrote:

Did you get any help with this?
/I formatted correctly before sending the email. Maybe you should ask 
yourself if the mail server did not remove the formatting./


I was talking about help with your relfilenode issue, I learned to deal 
with the formatting awhile ago.



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

/

/I can not make analyze on a table whose filenode is pointing to another 
reference. The table is empty, just because the filenode does not point 
to the correct ID./


Understood, I was just saying that if you could get the table pointing 
at the right relfilenode the other values would be synced up with an 
ANALYZE.


At this point it is time to just try something. You have file level 
backups of the various backups, so you can restore that if something 
goes wrong, correct?


For 01052016.repositorio with current pg_class entry of relfilenode of 
13741352, change that back to the old entry of 5214489.






--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Redo the filenode link in tablespace

2017-06-01 Thread tel medola 
Did you get any help with this?
*I formatted correctly before sending the email. Maybe you should ask
yourself if the mail server did not remove the formatting.*

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

*I can not make analyze on a table whose filenode is pointing to another
reference. The table is empty, just because the filenode does not point to
the correct ID.*

2017-05-31 20:22 GMT-03:00 Adrian Klaver :

> 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
>> *reltablespacerelpagesreltuplesrelallvisiblereltoastrelidrel
>> toastidxidrelhasindexrelissharedrelpersistencerelkindrelnatt
>> srelchecksrelhasoidsrelhaspkeyrelhasrulesrelhastriggersrelha
>> ssubclassrelispopulatedrelfrozenxidrelminmxidrelaclreloptions
>> repositorio520596252144910100*13741352*52059152144930Tru
>> eFalsepr70FalseTrueFalseFalseFalseTrue93602881
>>
>> 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.kla...@aklaver.com
>

Re: [GENERAL] Redo the filenode link in tablespace

2017-05-31 Thread Adrian Klaver 

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*52059152144930TrueFalsepr70FalseTrueFalseFalseFalseTrue93602881

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general