Re: [SQL] Lost my tablespace - Mailing list pgsql-sql
From | tel medola |
---|---|
Subject | Re: [SQL] Lost my tablespace |
Date | |
Msg-id | CANRMYmgKvrGEo5NOQ7ScRBJj-jgae5bS5y6oXVrwQh0csonPbw@mail.gmail.com Whole thread Raw |
In response to | Re: [SQL] Lost my tablespace (Adrian Klaver <adrian.klaver@aklaver.com>) |
Responses |
Re: [SQL] Lost my tablespace
|
List | pgsql-sql |
Looks to me like the above is inheriting itself, note the non-schema qualified repositorio. Pretty sure that is not good
I realized this too, I looked in other banks (of other applications) that I have, and this information is the same: "Têm OIDs: não"
So I assume the other repositorio tables in the other schemas are as above but pointing at different tablespaces, correct?
Yes
Understood. Still one of the issues is not providing information from explicit commands provided. As an example in previous post I had:
Ok. Thanks!
rai=# show search_path;
search_path
--------------------------------------------------------
1052016, 5122016, 13042017, 22082016, 30122015, public
(1 registro)
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.
Understood
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:
Ok, very thanks.
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?
I always use the public.repository table for the preliminary information. After drive C: gets full, I move the data to a new tablespac on another drive and then do the truncate of the public. After that, I relink the inheritance with the new table created (in the new tablespace) so that postgres can extract the data automatically.
The schema(13042017) was done this way (after the wrong truncate).
2017-05-30 11:21 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.com>:
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