Re: [SQL] Lost my tablespace - Mailing list pgsql-sql
| From | tel medola |
|---|---|
| Subject | Re: [SQL] Lost my tablespace |
| Date | |
| Msg-id | CANRMYmjXeyEYeEaVV+8k_DZBXuNipCWZ8+wWvYCKGnSKRAAEmQ@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
(Adrian Klaver <adrian.klaver@aklaver.com>)
Re: [SQL] Lost my tablespace (Adrian Klaver <adrian.klaver@aklaver.com>) |
| List | pgsql-sql |
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
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
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"
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.
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.
Is there a way to get access to this data again?
2017-05-30 10:22 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 05/30/2017 05:11 AM, tel medola wrote:Sorry by delay....
The copy being the file system drive backups of each tablespace?
/
/
/Yes/
/
/
Then the below from your original post means?/
/
/That despite recovering the backup, I can not access my data. So I posted that I lost my tablespaces./
When I run the \ d + command the involved tables are not shown. Not even the one I can access via Select (only those that are in the public schema are shown)
So how did you get the below?
/ 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 |/
What does:
show search_path;
return?
Did you try my previous suggestions:
You will either need to set the search_path to all the schemas involved, see example at bottom of page below:
https://www.postgresql.org/docs/9.6/static/sql-set.html
or schema.qualify the table name passed to \d+:
\d+ some_schema.table_name
As examples:
SET search_path TO 01052016, 05122016 , 13042017, 22082016, 30122015 public;
or
For a single object(table, view, sequence) in a schema:
\d+ 01052016.repositorio
For all in a schema:
\d+ 01052016.*
--
Adrian Klaver
adrian.klaver@aklaver.com