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