Re: [SQL] Lost my tablespace

On 05/30/2017 06:50 AM, tel medola wrote:

That despite recovering the backup, I can not access my data. So I posted that I lost my tablespaces.
/Aware, thanks. In the next email I'll be careful about that/
/
/

See comments inline.

Did you try my previous suggestions:/
/
/Yes, but dont list all tables, in all schemas/.
/Bellow the main table/:

/rai=# \d+ public.repositorio;/
/                                          Tabela "public.repositorio"/
/    Coluna     |            Tipo             |     Modificadores     | Armazenamento | Estatísticas | Descrição/
/---------------+-----------------------------+-----------------------+---------------+--------------+-----------/
/ id_documento  | character(39)               |                       | extended      |              |/
/ documento     | bytea                       |                       | extended      |              |/
/ nomedocumento | character varying           |                       | extended      |              |/
/ id            | character(39)               | nÒo nulo              | extended      |              |/
/ datahora      | timestamp without time zone | valor padrÒo de now() | plain         |              |/
/ id_itemtype   | bigint                      | nÒo nulo              | plain         |              |/
/═ndices:/
/    "repositorio_pkey" PRIMARY KEY, btree (id)/
/    "repositorio_iddocumento" btree (id_documento) WITH (fillfactor=100)/
*/Tabelas descendentes: "01052016".repositorio,/*
*/                      "05122016".repositorio,/*
*/                      "22082016".repositorio,/*
*/                      "30122015".repositorio,/*
*/                      repositorio/*

Looks to me like the above is inheriting itself, note the non-schema qualified repositorio. Pretty sure that is not good.

/Têm OIDs: não/


rai=# \d+ 01052016.*
                                          Tabela "01052016.repositorio"
     Coluna     |            Tipo             |     Modificadores     | Armazenamento | EstatÝsticas | DescriþÒo
---------------+-----------------------------+-----------------------+---------------+--------------+-----------
  id_documento  | character(39)               |                       | extended     |              |
  documento     | bytea                       |                       | extended     |              |
  nomedocumento | character varying           |                       | extended     |              |
  id            | character(39)               | nÒo nulo              | extended     |              |
  datahora      | timestamp without time zone | valor padrÒo de now() | plain        |              |
  id_itemtype   | bigint                      | nÒo nulo              | plain        |              |
═ndices:
     "repositorio_pkey" PRIMARY KEY, btree (id)
     "repositorio_id_documento_idx" btree (id_documento) WITH (fillfactor=100)
*Heranças: public.repositorio*
*Têm OIDs: não*
*Tablespace: "disco02"*


        ═ndice "01052016.repositorio_id_documento_idx"
     Coluna    |     Tipo      |  DefiniþÒo   | Armazenamento
--------------+---------------+--------------+---------------
  id_documento | character(39) | id_documento | extended
btree, para tabela "01052016.repositorio"
Opþ§es: fillfactor=100


          ═ndice "01052016.repositorio_pkey"
  Coluna |     Tipo      | DefiniþÒo | Armazenamento
--------+---------------+-----------+---------------
  id     | character(39) | id        | extended
chave primßria, btree, para tabela "01052016.repositorio"

So I assume the other repositorio tables in the other schemas are as above but pointing at different tablespaces, correct?

/Adrian, I see you really want to help me, thank you very much for that. I apologize if at any point I did not quite understand what you meant, it is that writing in English is not the best.

Understood. Still one of the issues is not providing information from explicit commands provided. As an example in previous post I had:

What does:

   show search_path;

return?

It is important remember is that what is obvious to you looking at the terminal is not so obvious on this end. To understand what is going on we need specific information.

/
/But I need to know where you want to get the questions, because the logical links in the table are all correct, but for some reason Postgres can not access my data and I'm practically losing my job because I can not deliver the information I should./

I understand the pressure you are under. I am going to be heading out to work here shortly and will not be able to help for awhile. I am not sure where you are, but you might want to look here:

https://www.postgresql.org/support/professional_support/

for folks close by that could help.

/Is there a way to get access to this data again?/

One thing that I have not understood is:

Esquema  |    Nome     |  Tipo  |   Dono   |  Tamanho   | Descrição
----------+-------------+--------+----------+------------+-----------
 01052016 | repositorio | tabela | postgres | 8192 bytes |
 05122016 | repositorio | tabela | postgres | 8192 bytes |
 13042017 | repositorio | tabela | postgres | 491 GB  |
 22082016 | repositorio | tabela | postgres | 8192 bytes |
 30122015 | repositorio | tabela | postgres | 8192 bytes |


As I remember 13042017.repositorio  is something you created after the TRUNCATE.

So where did the 491 GB in data come from?

Can it be used to seed the other tables?



--
Adrian Klaver
adrian.klaver@aklaver.com