Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL

If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished?

I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until the new one has finished, so I need to know when it's finished.

This is PostgreSQL 9.2/3ish

Combining the comment from @Rory and info in @Zaytsev Dmitry's answer:

The CREATE INDEX CONCURRENTLY will not return until the index has finished building. So you know the index is done when your query returns.

However if you're building a large index that runs for hours you may wonder if it is 'really' still running.

You can use the query for 'invalid' indexes:

SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;

...if your index appears in the results then it either failed or is still in progress.

Another way to confirm the latter is to check the locks table:

SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE mode = 'ShareUpdateExclusiveLock'
ORDER BY a.query_start;

Although CONCURRENTLY does not take exclusive lock on the table it will hold a weaker ShareUpdateExclusiveLock which you should see in the results from this query.

If there is no lock it implies that either the index has finished building, or the create failed and left an invalid index.

Between these two queries you can thus determine which of the three possible states (completed/in progress/failed) your index is in.

StackExchange Database Administrators Q#131945, answer score: 23