Re: [SQL] Lost my tablespace - Mailing list pgsql-sql

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