Re: [SQL] Lost my tablespace - Mailing list pgsql-sql
| From | tel medola |
|---|---|
| Subject | Re: [SQL] Lost my tablespace |
| Date | |
| Msg-id | CANRMYmjbg6MFhB=3wo6tcueUL8mqmqRfysSYrgRFHUu04Mxrwg@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
|
| List | pgsql-sql |
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