MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Date: Tue, 19 May 2015 12:45:49 +0200
From: Jan Lentfer <Jan.Lentfer@web.de>
To: <pgsql-admin@postgresql.org>
Subject: Re: Create Index CONCURRENTLY Hangs Indefinitely.
In-Reply-To: 
 <CAFe9ZTquML6VTu=QA+0fEheNr-LnJC-yTU_yrL12wRL5BXVdbA@mail.gmail.com>
References: 
 <CAFe9ZTquML6VTu=QA+0fEheNr-LnJC-yTU_yrL12wRL5BXVdbA@mail.gmail.com>
Message-ID: <62cfc7c5688f59b16d257b9dbfb382d3@imap.lan.net>
User-Agent: Roundcube Webmail/0.7.2
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Am 2015-05-19 12:30, schrieb Wei Shan:
> Hi all,
>
> Today I tried to create an index online, however it took far too long
> that I cancelled the query.
>
> 1. psql version =3D> 9.2.4
> 2. Table structure as follows. (7.5GB in size)
>
> evocsp=3D# d certificatedata
> =C2=A0 =C2=A0 =C2=A0 =C2=A0Table "public.certificatedata"
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Column =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0Typ=
e =C2=A0 | Modifiers
> ----------------------+---------+-----------
> =C2=A0fingerprint =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| text =C2=A0 =C2=A0|=
 not null
> =C2=A0base64cert =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =C2=A0|
> =C2=A0cafingerprint =C2=A0 =C2=A0 =C2=A0 =C2=A0| text =C2=A0 =C2=A0|
> =C2=A0certificateprofileid | integer | not null
> =C2=A0expiredate =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | bigint =C2=A0| not =
null
> =C2=A0issuerdn =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =
=C2=A0| not null
> =C2=A0revocationdate =C2=A0 =C2=A0 =C2=A0 | bigint =C2=A0| not null
> =C2=A0revocationreason =C2=A0 =C2=A0 | integer | not null
> =C2=A0rowprotection =C2=A0 =C2=A0 =C2=A0 =C2=A0| text =C2=A0 =C2=A0|
> =C2=A0rowversion =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | integer | not null
> =C2=A0serialnumber =C2=A0 =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =C2=A0| not =
null
> =C2=A0status =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | integer |=
 not null
> =C2=A0subjectdn =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| text =C2=A0 =
=C2=A0| not null
> =C2=A0subjectkeyid =C2=A0 =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =C2=A0|
> =C2=A0tag =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=
 text =C2=A0 =C2=A0|
> =C2=A0type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | inte=
ger | not null
> =C2=A0updatetime =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | bigint =C2=A0| not =
null
> =C2=A0username =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =
=C2=A0|
> Indexes:
> =C2=A0 =C2=A0 "certificatedata_pkey" PRIMARY KEY, btree (fingerprint)
> =C2=A0 =C2=A0 "certificatedata_idx7" btree (certificateprofileid)
>
> 3. The query ran was =3D> CREATE INDEX CONCURRENTLY=20
> 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: =C2=A0canceling wait for synchronous replication due to user
> request
> DETAIL: =C2=A0The transaction has already committed locally, but might not
> have been replicated to the standby.
> ^CCancel request sent
> ERROR: =C2=A0canceling statement due to user request
>
> Any input or ideas are welcome.


Was your SR slave actually online consuming changes? What does "select=20
* from pg_stat_replication" tell you?

Jan


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin