agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: tel medola <tel.medola@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Lost my tablespace
Date: Tue, 30 May 2017 07:21:53 -0700
Message-ID: <100e137f-4510-031c-1fa5-51b2a145f39a@aklaver.com> (raw)
In-Reply-To: <CANRMYmjXeyEYeEaVV+8k_DZBXuNipCWZ8+wWvYCKGnSKRAAEmQ@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
view thread (25+ messages) latest in thread
Message-ID: <100e137f-4510-031c-1fa5-51b2a145f39a@aklaver.com>
Permalink: ../100e137f-4510-031c-1fa5-51b2a145f39a@aklaver.com/
Also on: postgresql.org/message-id/100e137f-4510-031c-1fa5-51b2a145f39a@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, tel.medola@gmail.com
Subject: Re: Lost my tablespace
In-Reply-To: <100e137f-4510-031c-1fa5-51b2a145f39a@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox