Re: [SQL] Lost my tablespace

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.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