Apparently just leaving it alone until tomorrow managed to finish creating/dropping the index. Thank you all very much.
On Thu, Aug 18, 2022 at 5:00 PM hubert depesz lubaczewski <depesz@depesz.com> wrote:
On Thu, Aug 18, 2022 at 01:57:48PM +0800, Abdul Qoyyuum wrote: > 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.
This means that you have some very long transactions.
To make/drop index concurrently, all transactions that have started before you started create/drop, have to finish.
You can see your oldest transactions by doing:
select * from pg_stat_activity where xact_start is not null order by xact_start