Re: [SQL] Lost my tablespace - Mailing list pgsql-sql
| From | tel medola |
|---|---|
| Subject | Re: [SQL] Lost my tablespace |
| Date | |
| Msg-id | CANRMYmhxARYp73boRCfHB5VHDFT+wM5G=kTGrCQcN0Ds=uxLWw@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
Re: [SQL] Lost my tablespace |
| List | pgsql-sql |
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