Thread: [SQL] Lost my tablespace
I have a serious problem in my database. I have a table, divided into 4 tablespaces, one in each unit (E :; G :; H:; I:), linked with inheritance, of approximately 500 gb each. It happens that a truncate was done in the main table without undoing the inheritance for the other tables and consequently the "daughters" tables were cleaned together with the main one. I lost everything! But ... I have the backup of all drives and I have already done the data restore. Everything has been successfully retrieved, however the table is listed as 8192 bytes in postgres, but the units are all as they were before the truncate was executed.
Is there any way Postgres redo the link with the child tables ?? I already ran the command to rewrite inheritance, but the size of the tables continues with 8192.
If you execute the SELECT command pg_size_pretty (pg_database_size ('database_name')), the result comes complete, as if the tables were filled normally.
Can someone help me please?
Thanks Roberto.
On 05/29/2017 07:10 AM, tel medola wrote:
>
> I have a serious problem in my database. I have a table, divided into 4
Postgres version?
> tablespaces, one in each unit (E :; G :; H:; I:), linked with
So for OS some version of Windows, correct?
> inheritance, of approximately 500 gb each. It happens that a truncate
> was done in the main table without undoing the inheritance for the other
> tables and consequently the "daughters" tables were cleaned together
> with the main one. I lost everything! But ... I have the backup of all
> drives and I have already done the data restore. Everything has been
> successfully retrieved, however the table is listed as 8192 bytes in
> postgres, but the units are all as they were before the truncate was
> executed.
> Is there any way Postgres redo the link with the child tables ?? I
> already ran the command to rewrite inheritance, but the size of the
> tables continues with 8192.
What was the command?
> If you execute the SELECT command pg_size_pretty (pg_database_size
> ('database_name')), the result comes complete, as if the tables were
> filled normally.
What does
select * from pg_tablespace;
show?
What does $PGDATA/pg_tblspc show?
> Can someone help me please?
> Thanks Roberto.
--
Adrian Klaver
adrian.klaver@aklaver.com
I have a serious problem in my database. I have a table, divided into 4
Postgres version?
PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit
tablespaces, one in each unit (E :; G :; H:; I:), linked with
So for OS some version of Windows, correct?
Yes, correct!
What was the command?
What does
select * from pg_tablespace;
show?
inheritance, of approximately 500 gb each. It happens that a truncate was done in the main table without undoing the inheritance for the other tables and consequently the "daughters" tables were cleaned together with the main one. I lost everything! But ... I have the backup of all drives and I have already done the data restore. Everything has been successfully retrieved, however the table is listed as 8192 bytes in postgres, but the units are all as they were before the truncate was executed.
Is there any way Postgres redo the link with the child tables ?? I already ran the command to rewrite inheritance, but the size of the tables continues with 8192.
What was the command?
If you execute the SELECT command pg_size_pretty (pg_database_size ('database_name')), the result comes complete, as if the tables were filled normally.
What does
2848 GB
select * from pg_tablespace;
show?
Yes, all tablespaces
disco01
disco02
disco03
disco04
disco05
What does $PGDATA/pg_tblspc show?
What does $PGDATA/pg_tblspc show?
this shortcouts:
2193601 -> E:
5205910 -> G:
7245095 -> H:
9277962 -> I:
11242858-> J: (new)
Thanks.
2017-05-29 11:23 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 05/29/2017 07:10 AM, tel medola wrote:
I have a serious problem in my database. I have a table, divided into 4
Postgres version?tablespaces, one in each unit (E :; G :; H:; I:), linked with
So for OS some version of Windows, correct?inheritance, of approximately 500 gb each. It happens that a truncate was done in the main table without undoing the inheritance for the other tables and consequently the "daughters" tables were cleaned together with the main one. I lost everything! But ... I have the backup of all drives and I have already done the data restore. Everything has been successfully retrieved, however the table is listed as 8192 bytes in postgres, but the units are all as they were before the truncate was executed.
Is there any way Postgres redo the link with the child tables ?? I already ran the command to rewrite inheritance, but the size of the tables continues with 8192.
What was the command?If you execute the SELECT command pg_size_pretty (pg_database_size ('database_name')), the result comes complete, as if the tables were filled normally.
What does
select * from pg_tablespace;
show?
What does $PGDATA/pg_tblspc show?--Can someone help me please?
Thanks Roberto.
Adrian Klaver
adrian.klaver@aklaver.com
On 05/29/2017 07:39 AM, tel medola wrote:
> I have a serious problem in my database. I have a table, divided into 4
>
>
> Postgres version?
>
> /PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit/
>
>
> What was the command?
I meant the command you used to create the inheritance structure.
>
> If you execute the SELECT command pg_size_pretty (pg_database_size
> ('database_name')), the result comes complete, as if the tables were
> filled normally.
>
>
> What does
> /2848 GB/
Can you provide the parent and child table sizes?
>
> select * from pg_tablespace;
My mistake I should have asked for:
select oid, * from pg_tablespace;
Please run the command as above.
>
> show?
> /Yes, all tablespaces/
> /disco01/
> /disco02/
> /disco03/
> /disco04/
> /disco05
> /
>
> What does $PGDATA/pg_tblspc show?
> /this shortcouts:/
>
> /2193601 -> E:/
> /5205910 -> G:/
> /7245095 -> H:/
> /9277962 -> I:/
> /11242858-> J: (new)/
What do you mean by new?
>
>
> Thanks.
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
tel medola <tel.medola@gmail.com> writes:
> I have a serious problem in my database. I have a table, divided into 4
> tablespaces, one in each unit (E :; G :; H:; I:), linked with inheritance,
> of approximately 500 gb each. It happens that a truncate was done in the
> main table without undoing the inheritance for the other tables and
> consequently the "daughters" tables were cleaned together with the main
> one. I lost everything! But ... I have the backup of all drives and I have
> already done the data restore. Everything has been successfully retrieved,
> however the table is listed as 8192 bytes in postgres, but the units are
> all as they were before the truncate was executed.
> Is there any way Postgres redo the link with the child tables ?? I already
> ran the command to rewrite inheritance, but the size of the tables
> continues with 8192.
This doesn't sound like a problem. This sounds like a misunderstanding
of what table size means. If you do "select from" the main table, do
you see rows from the child tables? If so, the inheritance is fine.
regards, tom lane
What was the command?
I meant the command you used to create the inheritance structure.
If you execute the SELECT command pg_size_pretty (pg_database_size
('database_name')), the result comes complete, as if the tables were
filled normally.
What does
/2848 GB/
CREATE TABLESPACE <tablespace> OWNER postgres LOCATION 'j:\';
CREATE SCHEMA "<new schema>" AUTHORIZATION postgres;
CREATE TABLE "<new schema>".<table> (LIKE public.repositorio INCLUDING STORAGE INCLUDING INDEXES INCLUDING DEFAULTS INCLUDING CONSTRAINTS ) INHERITS ( public.<table>) TABLESPACE <tablespace>;
ALTER TABLE "<new schema>".<table> NO INHERIT public.<table>;
copy from public.<table> to <new schema>.<table>;
truncate public.<table>
ALTER TABLE "<new schema>".<table> INHERIT public.<table>;
Can you provide the parent and child table sizes?
Unfortunately not. After the backup is back they are all 8192 bytes.
My mistake I should have asked for:
select oid, * from pg_tablespace;
Please run the command as above.
What do you mean by new?
select * from pg_tablespace;
My mistake I should have asked for:
select oid, * from pg_tablespace;
Please run the command as above.
oid spcname spcowner spcacl spcoptions
1663 pg_default 10
1664 pg_global 10
2193601 disco01 10
5205910 disco02 10
7245095 disco03 10
9277962 disco04 10
11242858 disco05 10
show?
/Yes, all tablespaces/
/disco01/
/disco02/
/disco03/
/disco04/
/disco05
/
What does $PGDATA/pg_tblspc show?
/this shortcouts:/
/2193601 -> E:/
/5205910 -> G:/
/7245095 -> H:/
/9277962 -> I:/
/11242858-> J: (new)/
What do you mean by new?
It was made after the return of the bakcup. This I can access normally.
2017-05-29 11:57 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 05/29/2017 07:39 AM, tel medola wrote:I have a serious problem in my database. I have a table, divided into 4
Postgres version?
/PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit/
What was the command?
I meant the command you used to create the inheritance structure.
If you execute the SELECT command pg_size_pretty (pg_database_size
('database_name')), the result comes complete, as if the tables were
filled normally.
What does
/2848 GB/
Can you provide the parent and child table sizes?
select * from pg_tablespace;
My mistake I should have asked for:
select oid, * from pg_tablespace;
Please run the command as above.
show?
/Yes, all tablespaces/
/disco01/
/disco02/
/disco03/
/disco04/
/disco05
/
What does $PGDATA/pg_tblspc show?
/this shortcouts:/
/2193601 -> E:/
/5205910 -> G:/
/7245095 -> H:/
/9277962 -> I:/
/11242858-> J: (new)/
What do you mean by new?
Thanks.
--
Adrian Klaver
adrian.klaver@aklaver.com
This doesn't sound like a problem. This sounds like a misunderstanding
of what table size means. If you do "select from" the main table, do
you see rows from the child tables? If so, the inheritance is fine.
of what table size means. If you do "select from" the main table, do
you see rows from the child tables? If so, the inheritance is fine.
Unfortunately not. The tables are empty
pg_size_pretty Schema Tabela Tamanho Tamanho total
1991 MB public repositorio 1991 MB 1993 MB
8192 bytes 30122015 repositorio 8192 bytes 24 kB
491 GB 13042017 repositorio 491 GB 491 GB <--- new, is ok!
8192 bytes 01052016 repositorio 8192 bytes 24 kB
8192 bytes 22082016 repositorio 8192 bytes 24 kB
8192 bytes 05122016 repositorio 8192 bytes 24 kB
2017-05-29 12:46 GMT-03:00 tel medola <tel.medola@gmail.com>:
What was the command?
I meant the command you used to create the inheritance structure.
If you execute the SELECT command pg_size_pretty (pg_database_size
('database_name')), the result comes complete, as if the tables were
filled normally.
What does
/2848 GB/CREATE TABLESPACE <tablespace> OWNER postgres LOCATION 'j:\';CREATE SCHEMA "<new schema>" AUTHORIZATION postgres;CREATE TABLE "<new schema>".<table> (LIKE public.repositorio INCLUDING STORAGE INCLUDING INDEXES INCLUDING DEFAULTS INCLUDING CONSTRAINTS ) INHERITS ( public.<table>) TABLESPACE <tablespace>;ALTER TABLE "<new schema>".<table> NO INHERIT public.<table>;copy from public.<table> to <new schema>.<table>;truncate public.<table>ALTER TABLE "<new schema>".<table> INHERIT public.<table>;Can you provide the parent and child table sizes?Unfortunately not. After the backup is back they are all 8192 bytes.
select * from pg_tablespace;
My mistake I should have asked for:
select oid, * from pg_tablespace;
Please run the command as above.oid spcname spcowner spcacl spcoptions1663 pg_default 101664 pg_global 102193601 disco01 105205910 disco02 107245095 disco03 109277962 disco04 1011242858 disco05 10
show?
/Yes, all tablespaces/
/disco01/
/disco02/
/disco03/
/disco04/
/disco05
/
What does $PGDATA/pg_tblspc show?
/this shortcouts:/
/2193601 -> E:/
/5205910 -> G:/
/7245095 -> H:/
/9277962 -> I:/
/11242858-> J: (new)/
What do you mean by new?It was made after the return of the bakcup. This I can access normally.2017-05-29 11:57 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.com>:On 05/29/2017 07:39 AM, tel medola wrote:I have a serious problem in my database. I have a table, divided into 4
Postgres version?
/PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit/
What was the command?
I meant the command you used to create the inheritance structure.
If you execute the SELECT command pg_size_pretty (pg_database_size
('database_name')), the result comes complete, as if the tables were
filled normally.
What does
/2848 GB/
Can you provide the parent and child table sizes?
select * from pg_tablespace;
My mistake I should have asked for:
select oid, * from pg_tablespace;
Please run the command as above.
show?
/Yes, all tablespaces/
/disco01/
/disco02/
/disco03/
/disco04/
/disco05
/
What does $PGDATA/pg_tblspc show?
/this shortcouts:/
/2193601 -> E:/
/5205910 -> G:/
/7245095 -> H:/
/9277962 -> I:/
/11242858-> J: (new)/
What do you mean by new?
Thanks.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 05/29/2017 08:46 AM, tel medola wrote: > What was the command? > > In psql: \d <The various tables involved> As to below: 1) In the below you are saying that you used that template to try to recreate the original tables, correct? 2) Why the INHERIT/NO INHERIT/INHERIT sequence? 3) If 1) is correct did the COPY actually do anything? > / > / > /CREATE TABLESPACE <tablespace> OWNER postgres LOCATION 'j:\';/ > / > / > /CREATE SCHEMA "<new schema>" AUTHORIZATION postgres;/ > / > / > /CREATE TABLE "<new schema>".<table> (LIKE public.repositorio INCLUDING > STORAGE INCLUDING INDEXES INCLUDING DEFAULTS INCLUDING CONSTRAINTS ) > INHERITS ( public.<table>) TABLESPACE <tablespace>;/ > / > / > /ALTER TABLE "<new schema>".<table> NO INHERIT public.<table>;/ > / > / > /copy from public.<table> to <new schema>.<table>;/ > / > / > /truncate public.<table>/ > / > / > /ALTER TABLE "<new schema>".<table> INHERIT public.<table>;/ > > > > My mistake I should have asked for: > > select oid, * from pg_tablespace; > > Please run the command as above. > / > / > /oidspcnamespcownerspcaclspcoptions/ > /1663pg_default10/ > /1664pg_global10/ > /2193601disco0110/ > /5205910disco0210/ > /7245095disco0310/ > /9277962disco0410/ > /11242858disco0510/ > So the OIDS in pg_tblspc match the links. -- Adrian Klaver adrian.klaver@aklaver.com
As to below:
1) In the below you are saying that you used that template to try to recreate the original tables, correct?
No.I have not done anything yet to try to retrieve the information, even though they are there. I just can not redo the links, even with the inherit command already executed.
2) Why the INHERIT/NO INHERIT/INHERIT sequence?
Because if I do not undo the inheritance, it will copy from it, to itself. After the copy is finished and I make sure everything is in place, I delete the original table and redo the links. With this, all my data is a new drive and with the link redone, my SQL's will function normally as if it were just a table.
3) If 1) is correct did the COPY actually do anything?
I only do this when I move my data to a new drive.
/
/
/CREATE TABLESPACE <tablespace> OWNER postgres LOCATION 'j:\';/
/
/
/CREATE SCHEMA "<new schema>" AUTHORIZATION postgres;/
/
/
/CREATE TABLE "<new schema>".<table> (LIKE public.repositorio INCLUDING STORAGE INCLUDING INDEXES INCLUDING DEFAULTS INCLUDING CONSTRAINTS ) INHERITS ( public.<table>) TABLESPACE <tablespace>;/
/
/
/ALTER TABLE "<new schema>".<table> NO INHERIT public.<table>;/
/
/
/copy from public.<table> to <new schema>.<table>;/
/
/
/truncate public.<table>/
/
/
/ALTER TABLE "<new schema>".<table> INHERIT public.<table>;/
My mistake I should have asked for:
select oid, * from pg_tablespace;
Please run the command as above.
/
/
/oidspcnamespcownerspcaclspcoptions/
/1663pg_default10/
/1664pg_global10/
/2193601disco0110/
/5205910disco0210/
/7245095disco0310/
/9277962disco0410/
/11242858disco0510/
So the OIDS in pg_tblspc match the links
Yes, but I still can not see the data. I thought perhaps of re-creating the indexes, would that help?
2017-05-29 15:01 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 05/29/2017 08:46 AM, tel medola wrote:What was the command?
In psql:
\d <The various tables involved>
As to below:
1) In the below you are saying that you used that template to try to recreate the original tables, correct?
2) Why the INHERIT/NO INHERIT/INHERIT sequence?
3) If 1) is correct did the COPY actually do anything?/
/
/CREATE TABLESPACE <tablespace> OWNER postgres LOCATION 'j:\';/
/
/
/CREATE SCHEMA "<new schema>" AUTHORIZATION postgres;/
/
/
/CREATE TABLE "<new schema>".<table> (LIKE public.repositorio INCLUDING STORAGE INCLUDING INDEXES INCLUDING DEFAULTS INCLUDING CONSTRAINTS ) INHERITS ( public.<table>) TABLESPACE <tablespace>;/
/
/
/ALTER TABLE "<new schema>".<table> NO INHERIT public.<table>;/
/
/
/copy from public.<table> to <new schema>.<table>;/
/
/
/truncate public.<table>/
/
/
/ALTER TABLE "<new schema>".<table> INHERIT public.<table>;/
My mistake I should have asked for:
select oid, * from pg_tablespace;
Please run the command as above.
/
/
/oidspcnamespcownerspcaclspcoptions/
/1663pg_default10/
/1664pg_global10/
/2193601disco0110/
/5205910disco0210/
/7245095disco0310/
/9277962disco0410/
/11242858disco0510/
So the OIDS in pg_tblspc match the links.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 05/29/2017 11:21 AM, tel medola wrote: > As to below: > > 1) In the below you are saying that you used that template to try to > recreate the original tables, correct? > > /No.I have not done anything yet to try to retrieve the information, > even though they are there. I just can not redo the links, even with the > inherit command already executed./ It seems you are doing something, that is what I am trying figure out. What INHERIT command? Also can you show in psql: \d+ (The tables involved) > > 2) Why the INHERIT/NO INHERIT/INHERIT sequence? > > /Because if I do not undo the inheritance, it will copy from it, to > itself. After the copy is finished and I make sure everything is in > place, I delete the original table and redo the links. With this, all my > data is a new drive and with the link redone, my SQL's will function > normally as if it were just a table./ But none of this was actually done when you tried to recover the tables. > > 3) If 1) is correct did the COPY actually do anything? > > /I only do this when I move my data to a new drive./ -- Adrian Klaver adrian.klaver@aklaver.com
1) In the below you are saying that you used that template to try to recreate the original tables, correct?
/No.I have not done anything yet to try to retrieve the information, even though they are there. I just can not redo the links, even with the inherit command already executed./
It seems you are doing something, that is what I am trying figure out.
What INHERIT command?
I did it, I'm not doing it.
After I returned the copy, I did nothing else. That "new" I said, I had done before. But it's only "new" that I can get with the SELECT, the others, not.
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)
2) Why the INHERIT/NO INHERIT/INHERIT sequence?
/Because if I do not undo the inheritance, it will copy from it, to itself. After the copy is finished and I make sure everything is in place, I delete the original table and redo the links. With this, all my data is a new drive and with the link redone, my SQL's will function normally as if it were just a table./
But none of this was actually done when you tried to recover the tables.
When I returned the backup (drive copy, not pg_dump), I realized that the information was not being found. I did not change anything else at the database and sent the email to the community.
2017-05-29 15:45 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 05/29/2017 11:21 AM, tel medola wrote:As to below:
1) In the below you are saying that you used that template to try to recreate the original tables, correct?
/No.I have not done anything yet to try to retrieve the information, even though they are there. I just can not redo the links, even with the inherit command already executed./
It seems you are doing something, that is what I am trying figure out.
What INHERIT command?
Also can you show in psql:
\d+ (The tables involved)
2) Why the INHERIT/NO INHERIT/INHERIT sequence?
/Because if I do not undo the inheritance, it will copy from it, to itself. After the copy is finished and I make sure everything is in place, I delete the original table and redo the links. With this, all my data is a new drive and with the link redone, my SQL's will function normally as if it were just a table./
But none of this was actually done when you tried to recover the tables.
3) If 1) is correct did the COPY actually do anything?
/I only do this when I move my data to a new drive./
--
Adrian Klaver
adrian.klaver@aklaver.com
On 05/29/2017 12:05 PM, tel medola wrote: > 1) In the below you are saying that you used that template to try to > recreate the original tables, correct? > > /No.I have not done anything yet to try to retrieve the information, > even though they are there. I just can not redo the links, even with the > inherit command already executed./ > > It seems you are doing something, that is what I am trying figure out. > What INHERIT command? > > /I did it, I'm not doing it./ > /After I returned the copy, I did nothing else. That "new" I said, I had The copy being the file system drive backups of each tablespace? > done before. But it's only "new" that I can get with the SELECT, the > others, not./ Then the below from your original post means?: "Is there any way Postgres redo the link with the child tables ?? I already ran the command to rewrite inheritance, but the size of the tables continues with 8192." > / > / > /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) > / 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 > > But none of this was actually done when you tried to recover the tables. > > /When I returned the backup (drive copy, not pg_dump), I realized that > the information was not being found. I did not change anything else at > the database and sent the email to the community./ > -- Adrian Klaver adrian.klaver@aklaver.com
Sorry by delay....
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)
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 |
2017-05-29 16:32 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 05/29/2017 12:05 PM, tel medola wrote:1) In the below you are saying that you used that template to try to recreate the original tables, correct?
/No.I have not done anything yet to try to retrieve the information, even though they are there. I just can not redo the links, even with the inherit command already executed./
It seems you are doing something, that is what I am trying figure out.
What INHERIT command?
/I did it, I'm not doing it./
/After I returned the copy, I did nothing else. That "new" I said, I had
The copy being the file system drive backups of each tablespace?done before. But it's only "new" that I can get with the SELECT, the others, not./
Then the below from your original post means?:
"Is there any way Postgres redo the link with the child tables ?? I already ran the command to rewrite inheritance, but the size of the tables continues with 8192."/
/
/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)
/
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
But none of this was actually done when you tried to recover the tables.
/When I returned the backup (drive copy, not pg_dump), I realized that the information was not being found. I did not change anything else at the database and sent the email to the community./
--
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
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
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
On 05/30/2017 06:50 AM, tel medola wrote: Another thought. This list is in use but does not see as much traffic nor have as many subscribers as --general: https://www.postgresql.org/list/pgsql-general/ You might want to bring this up on --general. More eyes to look at the problem. I would point at this thread to get people up to speed(though I would warn them it is a long thread): https://www.postgresql.org/message-id/CANRMYmgu3ddSU62-%3Da3QCnsbGYuMVJXZ-ydTm7vd_2TQLp-Oqg%40mail.gmail.com -- Adrian Klaver adrian.klaver@aklaver.com
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"
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)
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).
2017-05-30 11:21 GMT-03:00 Adrian Klaver <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/
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
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
"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.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>: adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.
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 Klavercom>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 05/30/2017 11:56 AM, tel medola wrote:
> 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/
>
>
> 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 )/
>
I think I see now. The schema names are the dates you transferred the
data out of public.repositorio into the appropriate schema. I also think
I see what the issue might be with the tablespaces. When you did the
TRUNCATE the table relfilenode changed:
https://www.postgresql.org/docs/9.3/static/storage-file-layout.html
"
Note that while a table's filenode often matches its OID, this is not
necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER
and some forms of ALTER TABLE, can change the filenode while preserving
the OID. Avoid assuming that filenode and table OID are the same. Also,
for certain system catalogs including pg_class itself,
pg_class.relfilenode contains zero. The actual filenode number of these
catalogs is stored in a lower-level data structure, and can be obtained
using the pg_relation_filenode() function.
In the tablespace the tables are stored by that relfilenode also:
From same link as above:
"Tablespaces make the scenario more complicated. Each user-defined
tablespace has a symbolic link inside the PGDATA/pg_tblspc directory,
which points to the physical tablespace directory (i.e., the location
specified in the tablespace's CREATE TABLESPACE command). This symbolic
link is named after the tablespace's OID. Inside the physical tablespace
directory there is a subdirectory with a name that depends on the
PostgreSQL server version, such as PG_9.0_201008051. (The reason for
using this subdirectory is so that successive versions of the database
can use the same CREATE TABLESPACE location value without conflicts.)
Within the version-specific subdirectory, there is a subdirectory for
each database that has elements in the tablespace, named after the
database's OID. Tables and indexes are stored within that directory,
using the filenode naming scheme. The pg_default tablespace is not
accessed through pg_tblspc, but corresponds to PGDATA/base. Similarly,
the pg_global tablespace is not accessed through pg_tblspc, but
corresponds to PGDATA/global.
"
You used the file system backup to restore the old tablespace that that
had the old relfilenode names for the table. The thing is that Postgres
is looking for the new relfilnode names in the tablespace and not
finding them. I would start by doing this:
select pg_relation_filenode('01052016.repositorio'::regclass);
and seeing if that returned number exists in the tablespoace directory
for disco02. My guess is that it does not. I'm also going to say that is
that is going to be the same for all the tables except 13042017.repositorio.
If that is the case then it is a matter of getting the number that is in
the Postgres system catalog in sync with the one that is on disk. This
is not something I have done before and I would advise you to get other
opinions on how to do this. I would say it is now time to subscribe to
pgsql-general and ask how to do this. It would help to give a brief
description of what you did and then cut and paste my thoughts from above.
--
Adrian Klaver
adrian.klaver@aklaver.com
EXACT !!!!!
When I did the truncate, it erased all the files that referenced the table and created a new one (empty). That's why when I returned the physical files to the drives, it does not find the old reference and it is empty.
I'll search how to redo the link for the correct filenode.
Thanks very much for your help!!!
2017-05-30 16:35 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 05/30/2017 11:56 AM, tel medola wrote: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/
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,"220820 16".repositorio,"30122015". repositorio )/
I think I see now. The schema names are the dates you transferred the data out of public.repositorio into the appropriate schema. I also think I see what the issue might be with the tablespaces. When you did the
TRUNCATE the table relfilenode changed:
https://www.postgresql.org/docs/9.3/static/storage-file-layo ut.html
"
Note that while a table's filenode often matches its OID, this is not necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER and some forms of ALTER TABLE, can change the filenode while preserving the OID. Avoid assuming that filenode and table OID are the same. Also, for certain system catalogs including pg_class itself, pg_class.relfilenode contains zero. The actual filenode number of these catalogs is stored in a lower-level data structure, and can be obtained using the pg_relation_filenode() function.
In the tablespace the tables are stored by that relfilenode also:
From same link as above:
"Tablespaces make the scenario more complicated. Each user-defined tablespace has a symbolic link inside the PGDATA/pg_tblspc directory, which points to the physical tablespace directory (i.e., the location specified in the tablespace's CREATE TABLESPACE command). This symbolic link is named after the tablespace's OID. Inside the physical tablespace directory there is a subdirectory with a name that depends on the PostgreSQL server version, such as PG_9.0_201008051. (The reason for using this subdirectory is so that successive versions of the database can use the same CREATE TABLESPACE location value without conflicts.) Within the version-specific subdirectory, there is a subdirectory for each database that has elements in the tablespace, named after the database's OID. Tables and indexes are stored within that directory, using the filenode naming scheme. The pg_default tablespace is not accessed through pg_tblspc, but corresponds to PGDATA/base. Similarly, the pg_global tablespace is not accessed through pg_tblspc, but corresponds to PGDATA/global.
"
You used the file system backup to restore the old tablespace that that had the old relfilenode names for the table. The thing is that Postgres is looking for the new relfilnode names in the tablespace and not finding them. I would start by doing this:
select pg_relation_filenode('01052016.repositorio'::regclass);
and seeing if that returned number exists in the tablespoace directory for disco02. My guess is that it does not. I'm also going to say that is that is going to be the same for all the tables except 13042017.repositorio.
If that is the case then it is a matter of getting the number that is in the Postgres system catalog in sync with the one that is on disk. This is not something I have done before and I would advise you to get other opinions on how to do this. I would say it is now time to subscribe to pgsql-general and ask how to do this. It would help to give a brief description of what you did and then cut and paste my thoughts from above.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 05/30/2017 01:36 PM, tel medola wrote: > EXACT !!!!! > > When I did the truncate, it erased all the files that referenced the > table and created a new one (empty). That's why when I returned the > physical files to the drives, it does not find the old reference and it > is empty. > > I'll search how to redo the link for the correct filenode. > Thanks very much for your help!!! > The thing to remember is: https://www.postgresql.org/docs/9.3/static/storage-file-layout.html "When a table or index exceeds 1 GB, it is divided into gigabyte-sized segments. The first segment's file name is the same as the filenode; subsequent segments are named filenode.1, filenode.2, etc. This arrangement avoids problems on platforms that have file size limitations. (Actually, 1 GB is just the default segment size. The segment size can be adjusted using the configuration option --with-segsize when building PostgreSQL.) In principle, free space map and visibility map forks could require multiple segments as well, though this is unlikely to happen in practice." and: "A table that has columns with potentially large entries will have an associated TOAST table, which is used for out-of-line storage of field values that are too large to keep in the table rows proper. pg_class.reltoastrelid links from a table to its TOAST table, if any. See Section 58.2 for more information." -- Adrian Klaver adrian.klaver@aklaver.com
On 05/30/2017 01:36 PM, tel medola wrote: > EXACT !!!!! > > When I did the truncate, it erased all the files that referenced the > table and created a new one (empty). That's why when I returned the > physical files to the drives, it does not find the old reference and it > is empty. > > I'll search how to redo the link for the correct filenode. > Thanks very much for your help!!! > Just so you know I am willing to ask how to do this on the pgsql-general list if you want. -- Adrian Klaver adrian.klaver@aklaver.com
Ok Adrian.
I' will asking pgsql-general list.
Very thanks for all help!
Em ter, 30 de mai de 2017 às 20:25, Adrian Klaver <adrian.klaver@aklaver.com> escreveu:
On 05/30/2017 01:36 PM, tel medola wrote:
> EXACT !!!!!
>
> When I did the truncate, it erased all the files that referenced the
> table and created a new one (empty). That's why when I returned the
> physical files to the drives, it does not find the old reference and it
> is empty.
>
> I'll search how to redo the link for the correct filenode.
> Thanks very much for your help!!!
>
Just so you know I am willing to ask how to do this on the pgsql-general
list if you want.
--
Adrian Klaver
adrian.klaver@aklaver.com