Re: [SQL] Lost my tablespace

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.klaver@aklaver.com 
> <mailto: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/
>     <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 <mailto:adrian.klaver@aklaver.com>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com