I'm going to attempt to do my best in describing the situation... so please forgive any ignorance on my part since I am very new to the Postgres community.

We have 2 identical Postgres databases, one on a Windows 2003 platform, and the other on a SUSe 9.3 Linux platform (both platforms have identical hardware). They both have the same schema, the same configuration settings (as far as I can tell), and relatively the same amount of data (approx 32GB). OK... here is the scoop, I started a "vacuum full analyze verbose" on both last night... the windows box completed in about 1 1/2 hours... the Linux box however is still chugging away this morning... and appears to be stuck on vacuuming "pg_constraint_contypid_index". How do I know... well I don't really... I'm inferring based on the order of the log output on the Windows box.

So the question is... has anyone experienced this problem in the past? If so, what do you recommend to rectify the problem? Is it normal to be running for more than 12 hours on a table that only has 35 rows? Do you need me to attach the log output thus far from both the Windows and Linux box? Any help here would be greatly appreciated. Any commands you can suggest to run on the Linux machine... no problem.

Jeffrey Kirby
CCAP Web Team
jeff.kirby@wicourts.gov
608-264-6253


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Jeff Kirby" <Jeff.Kirby@wicourts.gov> writes:
> the Linux box however is still chugging away this morning... and
> appears to be stuck on vacuuming "pg_constraint_contypid_index". How
> do I know... well I don't really... I'm inferring based on the order
> of the log output on the Windows box.

Looking in pg_locks would give you a more reliable indicator of what the
VACUUM is currently working on.

Is the Linux box otherwise idle? There was another report recently of a
vacuum hung up for a long time on pg_constraint_contypid_index, but it
seemed to be due to extremely heavy usage of domain types ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On the linux box check if any application left a "idle in transaction" backend
around.
just
ps -ef | grep transaction

if that shows any postgres process then you most likely found the culprit.
An application that's still running can have a bug that starts a transaction
which never finishes for some reason (commit or rollback it never called).
I had a couple of those errors in my app - always leading to a stuck vacuum
full. If that's the case just kill the backend in question and the vauum will
continue. Don't kill -9 it!!
After that try to figure out what caused the application not to commit or
rollback a transaction. It's usually an error handler of some sort.

just an idea.

UC

On Sunday 02 October 2005 09:42, Jeff Kirby wrote:
> I'm going to attempt to do my best in describing the situation... so please
> forgive any ignorance on my part since I am very new to the Postgres
> community.
>
> We have 2 identical Postgres databases, one on a Windows 2003 platform, and
> the other on a SUSe 9.3 Linux platform (both platforms have identical
> hardware). They both have the same schema, the same configuration settings
> (as far as I can tell), and relatively the same amount of data (approx
> 32GB). OK... here is the scoop, I started a "vacuum full analyze verbose"
> on both last night... the windows box completed in about 1 1/2 hours... the
> Linux box however is still chugging away this morning... and appears to be
> stuck on vacuuming "pg_constraint_contypid_index". How do I know... well I
> don't really... I'm inferring based on the order of the log output on the
> Windows box.
>
> So the question is... has anyone experienced this problem in the past? If
> so, what do you recommend to rectify the problem? Is it normal to be
> running for more than 12 hours on a table that only has 35 rows? Do you
> need me to attach the log output thus far from both the Windows and Linux
> box? Any help here would be greatly appreciated. Any commands you can
> suggest to run on the Linux machine... no problem.
>
> Jeffrey Kirby
> CCAP Web Team
> jeff.kirby@wicourts.gov
> 608-264-6253
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq


--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq