Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 5B6FA2E02C3 for ; Fri, 4 Jan 2008 06:41:00 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 42424-07 for ; Fri, 4 Jan 2008 06:40:52 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.180]) by postgresql.org (Postfix) with ESMTP id AE73A2E024C for ; Fri, 4 Jan 2008 06:40:55 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so10659024waf.5 for ; Fri, 04 Jan 2008 02:40:54 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=nJlSRGk3fv+ig9UlcStObt0fjo1uHTQCICJL7KHHooA=; b=FthsA9pDzEOO+NtBDhSrIEattsT2FKb8B+r7tSJJJDlM5cWUPppQo3YoiizHr9Ei9sXiZcX4fGgz0kYymxhdJVWwN10IRECQoZnZU27iUzKL75Ry+46TBtRQ9n2ZthT1q1o/qG/yI9kVCKs4xCXZFf8afxSGDqV1HslkACAXKww= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=qPzqYRsr9tICKz/vfd1Z7/5Z7F4W0C5ljyKUiVg3WQCZFPLaJ9mxn95YkvEStIBcSB7zyg9QmMypPDD+MsA6F1dHnbjMQQ8FyXFv7ietzfRK2sc3pnhh+rjNvru7QwjcazJld3Fyt2xtr9pMb5tnVs9/RYylBO5dpNnoFjnaahs= Received: by 10.114.195.19 with SMTP id s19mr799736waf.58.1199443254290; Fri, 04 Jan 2008 02:40:54 -0800 (PST) Received: by 10.114.131.4 with HTTP; Fri, 4 Jan 2008 02:40:54 -0800 (PST) Message-ID: Date: Fri, 4 Jan 2008 04:40:54 -0600 From: "Scott Marlowe" To: "Suresh Gupta VG" Subject: Re: Need some info on Postgresql Cc: "Peter Koczan" , pgsql-admin@postgresql.org In-Reply-To: <3D5445983859B84B92669C0D883EA6FB0BB3576A@ZENMAILHQ1.ind.zensar.com> MIME-Version: 1.0 Content-Type: text/plain; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <3D5445983859B84B92669C0D883EA6FB0BB3576A@ZENMAILHQ1.ind.zensar.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/52 X-Sequence-Number: 27826 On Jan 4, 2008 12:41 AM, Suresh Gupta VG wrote: > > Hi Scott, > > Thanks for your reply and pls see my inline comments in red color. > I am using Postgresql 7.4.2 version on Solaris. > > > > You need to update to 7.4.18 or whatever the last version was. 7.4.2 > > has known data eating bugs, and if you value your data even a little, > > you should update. this is a relatively painless update not requiring > > a dump / restore. > > > > Suresh : Can you pls tell is 7.4.2 is latest or 7.4.18 is latest? We will > plan to upgrade to 8.2.5. http://www.postgresql.org/ says the latest minor update to the 7.4 branch is 7.4.18. You are missing over two years of bug fixes by running 7.4.2. updating to 7.4.2 will take minutes. Migrating to 8.2.5 requires you to test your applications against 8.2.5 then dump and restore your database. Update 7.4 first. Then plan a migration. > > There are number of tables > > say about 30+ tables in our database. I started to "reindex" the tables > > individually. "reindex table <>". All the queries executed > > normally with less than 1 minute of duration. But one table is not > > responding any thing even after 10 minutes. The details of the table ar= e =85 > > I've had large tables take well over 10 minutes on 7.4 to reindex. > That's not necessarily a sign of a problem. How many rows does this > table have? Do you get any kind of error message or the database just > never returns. Have you tried vacuum full on this table instead? Do > the logs say anything about the reindex operation getting an error > etc??? > > > Suresh : This table contains only 5 columns and 5 rows of data. I don't g= et > any error message instead it remains un answered, I am using "ctrl C" to > cancel the command. I also had done the vacuum on this table, pls see the > result below. Pls give the general path of log files available in the > database. I don't have idea of this logs files of PGSQL. If it's hanging with only 5 rows then you've likely that reindex can't get some kind of lock it needs. I'm not sure if 7.4 has the same views as 8.2 so I'm not sure if pg_locks has the answer or not. while your reindex is trying to run, in another shell, in psql to your db, type in select * from pg_locks; No idea where your log files are, they change based on how pgsql was instal= led. > You would do well to consider a migration to 8.2.5 or 8.3 when it > comes out of beta / RC status. 7.4.x is getting rather old, and the > 8.x series (8.0, 8.1, 8.2, and now 8.3) have each had major > improvements in speed. > > Suresh : We will plan to upgrade to 8.X. What is this beta / RC status, c= an > you brief me pls? It's due out this year, most likely before spring.