Re: Lost my tablespace

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>