Unable to Create or Drop Index Concurrently

[Abdul Qoyyuum <aqoyyuum@xxxxxxxxxxxxxxxxx>]

Hi list,

We have a running Master-Slave High Availability set up. Naturally, we can't run any changes on read-only databases on slave, so we have to do it on the master node.

When trying to run the following command:

create index concurrently idx_cash_deposit_channel_id_batch_id on cash_deposit (channel_id, batch_id);

Waiting for a long time, and my connection dropped. When checking the table, we get the index as INVALID

Indexes:
    "pk_cash_deposit" PRIMARY KEY, btree (id)
    "idx_cash_deposit_channel_id_batch_id" btree (channel_id, batch_id) INVALID

And when dropping the invalid index, also takes a long time, my connection timed out, then when logging back in and check the table, it hasn't dropped.

Question is, do we have to shutdown traffic and close all existing open connections in order to drop and properly recreate the index? Any advice appreciated.

--

Abdul Qoyyuum Bin Haji Abdul Kadir

HP No: +673 720 8043