Subject: Re: Lost my tablespace
To: tel medola <tel.medola@gmail.com>
Cc: pgsql-sql@postgresql.org
References: 
 <CANRMYmgu3ddSU62-=a3QCnsbGYuMVJXZ-ydTm7vd_2TQLp-Oqg@mail.gmail.com>
 <2a418dab-d17f-9d3a-0ac4-53678c1ce16f@aklaver.com>
 <CANRMYmjbg6MFhB=3wo6tcueUL8mqmqRfysSYrgRFHUu04Mxrwg@mail.gmail.com>
 <97bd2743-46c3-32c4-7b3f-62def38aea65@aklaver.com>
 <CANRMYmhxARYp73boRCfHB5VHDFT+wM5G=kTGrCQcN0Ds=uxLWw@mail.gmail.com>
 <a9063a7d-ac70-abb8-38f4-6ef49bf401f3@aklaver.com>
 <CANRMYmjc7nc0GpxCkxApQTgAr+9YGooUvA8AEU_9SjjBJb+qqg@mail.gmail.com>
 <32bb9df2-3b4d-95cb-1f82-8da675536a9b@aklaver.com>
 <CANRMYmgw9+vreD0+43vGK+JYyshMaea=zedwc1ufYS_cBEj5VA@mail.gmail.com>
 <a18d2b5a-255f-e5b5-0c4d-686eb182e88e@aklaver.com>
 <CANRMYmjY9V-rzygyDFrDmf2GDP_-vcsEiP-ZOXd6WvNAFUEnhA@mail.gmail.com>
 <b7598d9a-eef6-1ff3-7d30-19838b3add51@aklaver.com>
 <CANRMYmjXeyEYeEaVV+8k_DZBXuNipCWZ8+wWvYCKGnSKRAAEmQ@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <100e137f-4510-031c-1fa5-51b2a145f39a@aklaver.com>
Date: Tue, 30 May 2017 07:21:53 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.1.1
MIME-Version: 1.0
In-Reply-To: 
 <CANRMYmjXeyEYeEaVV+8k_DZBXuNipCWZ8+wWvYCKGnSKRAAEmQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql