Re: Lost my tablespace

"Têm OIDs: não" is not part of the problem. Many years ago user tables
where created with a system column oid. This turned into a problem and now
user tables are not created with an oid column. It is still possible to
create or alter a table to have an oid column(I would not suggest that).
That is what "Têm OIDs: não" is showing.
*Ok*

So if you do:
\d+ *.repositorio
you should see all the repositorio tables.

*Like this:*

* 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) | no nulo |
extended | | *
* datahora | timestamp without time zone | valor padro de now() |
plain | | *
* id_itemtype | bigint | no nulo |
plain | | *
*Índices:*
* "repositorio_pkey" PRIMARY KEY, btree (id)*
* "repositorio_id_documento_idx" btree (id_documento) WITH
(fillfactor=100)*
*Heranças: repositorio*
*Têm OIDs: não*
*Tablespace: "disco02"*

* Tabela "05122016.repositorio"*
* Coluna | Tipo | Modificadores |
Armazenamento | Estatísticas | Descrição *
*---------------+-----------------------------+----------------------+---------------+------------+----------*
* id_documento | character(39) | |
extended | | *
* documento | bytea | |
extended | | *
* nomedocumento | character varying | |
extended | | *
* id | character(39) | no nulo |
extended | | *
* datahora | timestamp without time zone | valor padro de now() |
plain | | *
* id_itemtype | bigint | no nulo |
plain | | *
*Índices:*
* "repositorio_pkey" PRIMARY KEY, btree (id)*
* "repositorio_id_documento_idx" btree (id_documento) WITH
(fillfactor=100)*
*Heranças: repositorio*
*Têm OIDs: não*
*Tablespace: "disco04"*

* Tabela "13042017.repositorio"*
* Coluna | Tipo | Modificadores |
Armazenamento | Estatísticas | Descrição *
*---------------+-----------------------------+----------------------+---------------+------------+----------*
* id_documento | character(39) | |
extended | | *
* documento | bytea | |
extended | | *
* nomedocumento | character varying | |
extended | | *
* id | character(39) | no nulo |
extended | | *
* datahora | timestamp without time zone | valor padro de now() |
plain | | *
* id_itemtype | bigint | no nulo |
plain | | *
*Índices:*
* "repositorio_pkey" PRIMARY KEY, btree (id)*
* "repositorio_id_documento_idx" btree (id_documento) WITH
(fillfactor=100)*
*Heranças: repositorio*
*Têm OIDs: não*
*Tablespace: "disco05"*

* Tabela "22082016.repositorio"*
* Coluna | Tipo | Modificadores |
Armazenamento | Estatísticas | Descrição *
*---------------+-----------------------------+----------------------+---------------+------------+----------*
* id_documento | character(39) | |
extended | | *
* documento | bytea | |
extended | | *
* nomedocumento | character varying | |
extended | | *
* id | character(39) | no nulo |
extended | | *
* datahora | timestamp without time zone | valor padro de now() |
plain | | *
* id_itemtype | bigint | no nulo |
plain | | *
*Índices:*
* "repositorio_pkey" PRIMARY KEY, btree (id)*
* "repositorio_id_documento_idx" btree (id_documento) WITH
(fillfactor=100)*
*Heranças: repositorio*
*Têm OIDs: não*
*Tablespace: "disco03"*

* Tabela "30122015.repositorio"*
* Coluna | Tipo | Modificadores |
Armazenamento | Estatísticas | Descrição *
*---------------+-----------------------------+----------------------+---------------+------------+----------*
* id_documento | character(39) | |
extended | | *
* documento | bytea | |
extended | | *
* nomedocumento | character varying | |
extended | | *
* id | character(39) | no nulo |
extended | | *
* datahora | timestamp without time zone | valor padro de now() |
plain | | *
* id_itemtype | bigint | no nulo |
plain | | *
*Índices:*
* "repositorio_pkey" PRIMARY KEY, btree (id)*
* "repositorio_id_documento_idx" btree (id_documento) WITH
(fillfactor=100)*
*Heranças: repositorio*
*Têm OIDs: não*
*Tablespace: "disco01"*

* 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) | no nulo |
extended | | *
* datahora | timestamp without time zone | valor padro de now() |
plain | | *
* id_itemtype | bigint | no nulo |
plain | | *
*Índices:*
* "repositorio_pkey" PRIMARY KEY, btree (id)*
* "repositorio_iddocumento" btree (id_documento) WITH (fillfactor=100)*
*Tabelas descendentes: "01052016".repositorio,*
* "05122016".repositorio,*
* "13042017".repositorio,*
* "22082016".repositorio,*
* "30122015".repositorio*
*Têm OIDs: não*

To be clear the tablespace for public.repositorio is the default one in
$PGDATA on the C:\ drive, correct?
*Yes.*

So is there anything in public.repositorio now?
*Yes, users are inserting information into the public.repositorio table*

Before I forget and for future use:
*Ok*.

Is the data in 13042017.repositorio the data you want?
*No. The information on this drive I have, because the link was not lost.
Those are the other units I need to recover("01052016".repositorio,
"05122016".repositorio,"22082016".repositorio,"30122015".repositorio )*

2017-05-30 15:35 GMT-03:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 05/30/2017 07:56 AM, tel medola wrote:
>
>> 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"/
>>
>
> "Têm OIDs: não" is not part of the problem. Many years ago user tables
> where created with a system column oid. This turned into a problem and now
> user tables are not created with an oid column. It is still possible to
> create or alter a table to have an oid column(I would not suggest that).
> That is what "Têm OIDs: não" is showing.
>
>
>> 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)/
>>
>
> So if you do:
>
> \d+ *.repositorio
>
> you should see all the repositorio tables.
>
>
>>
>> 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)./
>>
>
> To be clear the tablespace for public.repositorio is the default one in
> $PGDATA on the C:\ drive, correct?
>
> So is there anything in public.repositorio now?
>
> <NOTE>
> Before I forget and for future use:
>
> 1) Truncate is transaction safe so can be done in a BEGIN/ROLLBACK or
> COMMIT sequence. Though if you are space constrained on C:\ I am not quite
> sure if truncating 400GB+ of data in an open transaction might not cause
> space issues also.
>
> 2) TRUNCATE has an ONLY option that restricts the action to the named
> table only and not its descendant(child) tables:
>
> https://www.postgresql.org/docs/9.6/static/sql-truncate.html
> <NOTE>
>
>
> Is the data in 13042017.repositorio the data you want?
>
> If so why not use that data to reseed the other child tables?
>
>
>
>
>
>
>
>
>> 2017-05-30 11:21 GMT-03:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)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/
>> <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 <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>