Question about index on different tablespace and rebuild it

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 ?

Le mar. 6 nov. 2018 20:49, Condor <condor@stz-bg.com> a écrit :

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 ?

Not sure I really understand your question but to do a reindex, you need
the database server to be online.

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@stz-bg.com> wrote:

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@stz-bg.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

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

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

Condor wrote:

Thank you for your answer.

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

It isn't.
If your secondary storage goes bad, a database is supposed to fail.
That's why you take backups.

Yours,
Laurenz Albe
--
+43-670-6056265
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com