Re: Need some info on Postgresql - Mailing list pgsql-admin

On Jan 4, 2008 12:41 AM, Suresh Gupta VG <suresh.g@zensar.com> 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 <<table name>>". 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 are …
>
> 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 get
> 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 installed.

> 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, can
> you brief me pls?

It's due out this year, most likely before spring.