Re: Lost my tablespace

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.

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