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
