Error while creating new indexes on Azure database

JOLY, Josselin (ITOP CD) - AFKL 0

Hello

The migration from on premise to Azure has been done with success with all the metadata and data the 18/06/2024.

It included indexes.

At this time we had no errors.

Now we want to try to create new BTREE indexe on our database but it fails with the following messsage :

ERROR: cannot create temporary directory "pg_tblspc/16386/PG_14_202107181/pgsql_tmp": No such file or directory

CONTEXT: parallel worker

can you please help us ?

extra info : db size : 4422 MB

size of the table we want to create indexe on is : 381 MB with 3797753 rows.

pg_size_pretty | nspname | relkind

----------------+--------------------+----------

3636 MB | <dbname> | table

731 MB | <dbname>| index

43 MB | pg_toast | toast

4400 kB | pg_catalog | table

4096 kB | pg_catalog | index

872 kB | pg_toast | index

88 kB | <dbname>| sequence

JOLY, Josselin (ITOP CD) - AFKL 0 Reputation points

2024-06-28T08:50:18.36+00:00

set azure.enable_temp_tablespaces_on_local_ssd = off
GeethaThatipatri-MSFT 28,852 Reputation points Microsoft Employee

2024-06-28T15:04:24.1766667+00:00

@JOLY, Josselin (ITOP CD) - AFKL Welcome to Microsoft Q&A thanks for posting your question.

pg_tblspc/16386/PG_14_202107181/pgsql_tmp does not exist or cannot be accessed by the database system. This could be due to various reasons such as permissions issues, incorrect path specifications, or the directory not being created during the migration process. can you please provide more information.

Regards

Geetha
GeethaThatipatri-MSFT 28,852 Reputation points Microsoft Employee

2024-06-28T17:29:00.93+00:00

@JOLY, Josselin (ITOP CD) - AFKL Welcome to Microsoft Q&A thanks for posting your question.

pg_tblspc/16386/PG_14_202107181/pgsql_tmp does not exist or cannot be accessed by the database system. This could be due to various reasons such as permissions issues, incorrect path specifications, or the directory not being created during the migration process.

Regards

Geetha