Re: [SQL] Lost my tablespace - Mailing list pgsql-sql
| From | tel medola |
|---|---|
| Subject | Re: [SQL] Lost my tablespace |
| Date | |
| Msg-id | CANRMYmjc7nc0GpxCkxApQTgAr+9YGooUvA8AEU_9SjjBJb+qqg@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 |
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