Re: Question about index on different tablespace and rebuild it

On 16-11-2018 19:40, Alexey Bashtanov wrote:
>> 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.
>>
> Unfortunately it doesn't sound like something feasible to me, at least
> without deep hacking of postgres itself.
>
> To start after a crash, the server needs to apply the WAL records
> since last checkpoint, and they need data files to be binary the same
> as before the crash.
> So the index file not only needs to be there and not too corrupted,
> but should have its pages and their contents to be located physically
> in the same order,
> which depends on the prior history of the table updates.
>
> Postgres manual explicitly warns that all the tablespaces need to be
> preserved in order to started the server.
> The only possible exclusion may be temp_tablespaces and unlogged and
> temporary tables -- kind of grey zone, people do it but the manual
> don't allow this trick.
>
> See also
> https://blog.2ndquadrant.com/postgresql-no-tablespaces-on-ramdisks/
>
> Best regards,
>   Alexey


Thank you for your answer.

It's seems this is a weak spot of Postgres.

Best regards,
HS