Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bae8c-00044k-Ib for pgsql-admin@arkaria.postgresql.org; Fri, 19 Aug 2016 07:22:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bae8c-0001hv-4B for pgsql-admin@arkaria.postgresql.org; Fri, 19 Aug 2016 07:22:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bae8a-0001gf-EA for pgsql-admin@postgresql.org; Fri, 19 Aug 2016 07:22:44 +0000 Received: from nm15.bullet.mail.bf1.yahoo.com ([98.139.212.174]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bae8W-0006xb-IF for pgsql-admin@postgresql.org; Fri, 19 Aug 2016 07:22:43 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com.ar; s=s2048; t=1471591358; bh=rno3kWFV2imY1d3PMLzb4IyY9nVCi4Sjp8XV+LJKhb8=; h=Subject:To:References:From:Date:In-Reply-To:From:Subject; b=AmSYCVUfucftHd9t3S2QG2BPM6fUT4bOXuHNWfkbx1UuSTVXrYW4UuXJpnarcIGVdwEDOsib0qgX2hRX6tefD7nUmffp6Oe//UdBttckmpcQoNp8vsZXm3O+2HrMFeKx9Kf4s//ofGMI3qcIm/nHjaz7hqj0UqkVFSFiumToorKAseu0RPaib2h4g2SQ1RTcVM3hgqutQAWCptw8MEln3H+mtGEYWpYnvtNqhyG9AF9TdA+G2Dr4DgofQLJTTwKY/JXAgHrfmnD3kvUMINO0ITyNhSm7y9ETN7nE2Ao+TyX0LbiGbfcaUydUv3c2syjREhCLnSwcjzF4lQgDLMNlxQ== Received: from [66.196.81.171] by nm15.bullet.mail.bf1.yahoo.com with NNFMP; 19 Aug 2016 07:22:38 -0000 Received: from [98.139.213.8] by tm17.bullet.mail.bf1.yahoo.com with NNFMP; 19 Aug 2016 07:22:37 -0000 Received: from [127.0.0.1] by smtp108.mail.bf1.yahoo.com with NNFMP; 19 Aug 2016 07:22:37 -0000 X-Yahoo-Newman-Id: 981755.67976.bm@smtp108.mail.bf1.yahoo.com X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: RaQ9lmoVM1mBY3QXyEmue2nNIh3n42T3CDnfePCUQuEV94f R3SXw8sy9uZ9z1_a2Hn2dL0wDxw7.NtkY..6jRQUPw73Wk20ijB769ThttUB t5ibsYTIe0gUVnYVn7hzyTzdNHemGgbejt6nUgf2hwAZ2m4ER_Rf92HIshOT TASQAEoBLsuCavj.WBYCyy.FbEEJJo4P70M4K.0IqY0Rpb38k3Mn45pjxv8x 4rYZCvMFYxz..LumL_33d1XgtxPFzxMWNY5yVMkiJE2D0YI9.QYFVHF4HoS9 lITWWrJhw6nYO5.vhGJy6jsH3TuC6kqsFD88XzIe2aNsVdnjjzJA83SsqATN c65.CQokp8euYpLuVa8KZMLtmhJsK75ppde2pyEzhz32WTLLzts1NDRpckXx J1.K0TzjtCMyYbGc1U62T7ujNdKvGm60vWGej9sJkZLuRMpsqjLwVz45Q7uZ AYuQsbS3uE9N2Ex.vXxBeTLjKOaB9RBlJMFi_7W_g8amtdDIuHDhi4.N__pz ao9YIVAUxJ7pSevNHXifjWWw_vm5ycfkiZF.2d05FJJ0qfuRzURVSbzHt9G4 . X-Yahoo-SMTP: Gn.Iy4OswBBvHOOi1k0uxfaapFJdi7Q- Subject: Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? To: pgsql-admin@postgresql.org References: <569661471554327@web30h.yandex.ru> From: Esteban Casuscelli Message-ID: <57B6B3B6.3040203@yahoo.com.ar> Date: Fri, 19 Aug 2016 09:22:30 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.7.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------000505090307080009030805" X-Pg-Spam-Score: -2.5 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------000505090307080009030805 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Have you considered the following: 1) create a new table with all you need. 2) load the new table from the original table 3) keep updating the new table from the original table until a time that you do not have users 4) rename original table 5) rename new table You need to consider all additional variables from your production environment that I do not know and test it but it is another option. Thanks esteban.- On 08/18/2016 11:19 PM, Ravi Tammineni wrote: > > Hi Samed, > > Thanks for the response. > > But my question is, I have to add a new id column to the existing > table, populate the data and then convert that column into primary key > column. I am running the following command but its taking forever. > > ALTER TABLE tblclinchecklog ADD COLUMN id serial primary key; > > Is there a better way? Like > > 1>Add column > > 2>Create sequence > > 3>Update the table > > 4>Create index > > 5>Convert the index into pk index > > Thanks > > ravi > > *From:*Samed YILDIRIM [mailto:samed@reddoc.net] > *Sent:* Thursday, August 18, 2016 2:05 PM > *To:* Ravi Tammineni ; > pgsql-admin@postgresql.org > *Subject:* Re: [ADMIN] What is the best way to create Primary Key on a > large table in Postgresql 9.5? > > Hi Ravi, > > You can use CREATE INDEX with CONCURRENTLY keyword. It will not lock > your table during the process. > > Best regards. > > > > İyi çalışmalar. > Samed YILDIRIM > > > 19.08.2016, 00:01, "Ravi Tammineni" >: > > I have to create a primary key on a large table (~100Million > records). What is the best and fastest way to create pkey? This > column is a sequence column and i don't want to lock the table > because this is highly transactional database. > > Thanks > > ravi > --------------000505090307080009030805 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Have you considered the following:

1) create a new table with all you need.
2) load the new table from the original table
3) keep updating the new table from the original table until a time that you do not have users
4) rename original table
5) rename new table

You need to consider all additional variables from your production environment that I do not know and test it but it is another option.

Thanks
esteban.-

On 08/18/2016 11:19 PM, Ravi Tammineni wrote:

Hi Samed,

 

Thanks for the response.

 

But my question is, I have to add a new id column to the existing table, populate the data and then convert that column into primary key column. I am running the following command but its taking forever.

 

ALTER TABLE tblclinchecklog ADD COLUMN id serial primary key;

 

Is there a better way? Like

 

1>     Add column

2>     Create sequence

3>     Update the table

4>     Create index

5>     Convert the index into pk index

 

Thanks

ravi

 

From: Samed YILDIRIM [mailto:samed@reddoc.net]
Sent: Thursday, August 18, 2016 2:05 PM
To: Ravi Tammineni <rtammineni@partner.aligntech.com>; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] What is the best way to create Primary Key on a large table in Postgresql 9.5?

 

Hi Ravi,

You can use CREATE INDEX with CONCURRENTLY keyword. It will not lock your table during the process.

Best regards.



İyi çalışmalar.
Samed YILDIRIM


19.08.2016, 00:01, "Ravi Tammineni" <rtammineni@partner.aligntech.com>:

 I have to create a primary key on a large table (~100Million records). What is the best and fastest way to create pkey? This column is a sequence column and i don't want to lock the table because this is highly transactional database.

 Thanks

 ravi


--------------000505090307080009030805--