Re: Create Index CONCURRENTLY Hangs Indefinitely.

Hi Wei,

There are many aspects that comes into picture :

1. Did you check that your create index concurrently command is in waiting
or running state in pg_stat_activity.
2. Terminate the session by Ctrl+C results into INVALID index. You might
want to drop that as well.
3. Increase the work_mem to ~ 3-4 GB before creating the index.
4. Create Index Concurrently usually takes longer time as compared with
Create Index Command.

thanks

On Tue, 19 May 2015 at 16:01 Wei Shan <weishan(dot)ang(at)gmail(dot)com> wrote:

> Hi all,
>
> Today I tried to create an index online, however it took far too long that
> I cancelled the query.
>
> 1. psql version => 9.2.4
> 2. Table structure as follows. (7.5GB in size)
> evocsp=# \d certificatedata
> Table "public.certificatedata"
> Column | Type | Modifiers
> ----------------------+---------+-----------
> fingerprint | text | not null
> base64cert | text |
> cafingerprint | text |
> certificateprofileid | integer | not null
> expiredate | bigint | not null
> issuerdn | text | not null
> revocationdate | bigint | not null
> revocationreason | integer | not null
> rowprotection | text |
> rowversion | integer | not null
> serialnumber | text | not null
> status | integer | not null
> subjectdn | text | not null
> subjectkeyid | text |
> tag | text |
> type | integer | not null
> updatetime | bigint | not null
> username | text |
> Indexes:
> "certificatedata_pkey" PRIMARY KEY, btree (fingerprint)
> "certificatedata_idx7" btree (certificateprofileid)
>
> 3. The query ran was => CREATE INDEX CONCURRENTLY certificatedata_idx2 ON
> CertificateData (username) tablespace tablespace_index;
> 4. After it took more than 2hrs+, I went to terminate the session via
> Ctrl-C, it returned the following error.
>
> WARNING: canceling wait for synchronous replication due to user request
> DETAIL: The transaction has already committed locally, but might not have
> been replicated to the standby.
> ^CCancel request sent
> ERROR: canceling statement due to user request
>
> Any input or ideas are welcome.
>
> Thanks!
>
> --
> Regards,
> Ang Wei Shan
>