Re: Question about index on different tablespace and rebuild it

On 07-11-2018 08:52, Tony Shelver wrote:
> Did you check the documentation for alter index?
> https://www.postgresql.org/docs/10/sql-alterindex.html
>
> You could create a script file (plenty of examples on the internet on
> generating these) and then run through psql or whatever.
>
> Also, if you just have a few indexes to move, you could use (for
> example) pgadmin4 to generate and run stop/start scripts.
>
> Data in the index is stored separately from the table, dropping
> indexes in any of SQL standard databases doesn't affect the data in
> the tables.
>
> On Tue, 6 Nov 2018 at 21:49, Condor <condor(at)stz-bg(dot)com> wrote:
>
>> On 05-11-2018 10:56, Condor wrote:
>>> Hello,
>>>
>>> I have a database that use index on different table space (nvme).
>> I
>>> read documentation about table space and understand table space
>> cannot
>>> be treated as an autonomous collection of data files.
>>> My question is: Is this always true ? I mean if I have table ..
>> okay
>>> here is example:
>>>
>>> create table t1 ( i integer);
>>> create index t1_i_idx on t1 using btree (i) tablespace nvme;
>>>
>>> In this case only the index is on different table space nvme that
>> is
>>> not part of data stored into table, I mean if I drop the index,
>> data
>>> stored in table will still be untouched and not damaged.
>>> So in this case if I lost table space nvme that is stored into
>>> different nvme drive, can I insert new one and rebuild all index
>> files
>>> with reindexdb for example or some other tool like
>>> mysql/mariadb for example myismcheck or something like that, that
>> can
>>> rebuild index files when DB sever is offline ?
>>>
>>>
>>> Regards,
>>> HS
>>
>> Some one can answer ?

Yes, I know. My question is: Did the pg server will start at all if the
NVME / table space somehow is broken and indexes is unable to be loaded,
not how to drop an index.
Since the Postgre server is not starting at all maybe I can try dropping
my indexes on my pocket calculator all day long.

When the data is separated, is there any other tool that can rebuild my
indexes located on broken tablespace, having of course in mind, that
Data in
the index is stored separately from the table. I guess the pg server
wont start and for that reason I try to explain and wonder if the data
and index is separated
probably pg devs have a way to rebuild these indexes somehow when pg
server is offline.

HS