Re: PostgreSQL Processes on a linux box

Hello,

altered a database using DBVisualizer. When I hit execute,
DBVisualizer stoped working and no I have two processes on my server:
postgres 30873 0.0 0.7 46552 7124 ? Ss 12:33 0:01
postgres: postgres dbName 10.0.1.198(53658) ALTER TABLE waiting
postgres 31007 0.0 0.6 46708 6500 ? Ss 13:07 0:00
postgres: postgres dbName 10.0.1.198(53799) SELECT waiting

What can I do? OK, I can stop and start the Database, but I think
there are other ways to handle this...
What Tool can I use to kill such processes?

Thanks and greetings,
Stefan Sturm

On Fri, Apr 11, 2008 at 03:09:43PM +0200, Stefan Sturm wrote:
> DBVisualizer stoped working and no I have two processes on my server:
> postgres 30873 0.0 0.7 46552 7124 ? Ss 12:33 0:01
> postgres: postgres dbName 10.0.1.198(53658) ALTER TABLE waiting
> postgres 31007 0.0 0.6 46708 6500 ? Ss 13:07 0:00
> postgres: postgres dbName 10.0.1.198(53799) SELECT waiting
>
> What can I do? OK, I can stop and start the Database, but I think
> there are other ways to handle this...
> What Tool can I use to kill such processes?

Why do you want to kill them? They're waiting for a lock. What is it?

A

On Fri, Apr 11, 2008 at 7:09 AM, Stefan Sturm
<stefan(dot)s(dot)sturm(at)googlemail(dot)com> wrote:
> Hello,
>
> altered a database using DBVisualizer. When I hit execute, DBVisualizer
> stoped working and no I have two processes on my server:
> postgres 30873 0.0 0.7 46552 7124 ? Ss 12:33 0:01 postgres:
> postgres dbName 10.0.1.198(53658) ALTER TABLE waiting
> postgres 31007 0.0 0.6 46708 6500 ? Ss 13:07 0:00 postgres:
> postgres dbName 10.0.1.198(53799) SELECT waiting

Are these the only two postgres processes on this box? You should
have a few others that look something like this:

/usr/lib/postgresql/8.3/bin/postgres -D .......
postgres 5178 0.0 0.0 40268 692 ? Ss Apr07 0:00
postgres: writer process
postgres 5179 0.0 0.0 40268 612 ? Ss Apr07 0:00
postgres: wal writer process
postgres 5180 0.0 0.0 40268 836 ? Ss Apr07 0:00
postgres: autovacuum launcher process
postgres 5181 0.0 0.0 11588 588 ? Ss Apr07 0:00
postgres: stats collector process

If those are the only two postgres processes then something's gone wrong.

If not, then find out what other processes are blocking the ones you
have that are waiting.

Hello,

Am 11.04.2008 um 18:41 schrieb Andrew Sullivan:

> On Fri, Apr 11, 2008 at 03:09:43PM +0200, Stefan Sturm wrote:
>> DBVisualizer stoped working and no I have two processes on my server:
>> postgres 30873 0.0 0.7 46552 7124 ? Ss 12:33 0:01
>> postgres: postgres dbName 10.0.1.198(53658) ALTER TABLE waiting
>> postgres 31007 0.0 0.6 46708 6500 ? Ss 13:07 0:00
>> postgres: postgres dbName 10.0.1.198(53799) SELECT waiting
>>
>> What can I do? OK, I can stop and start the Database, but I think
>> there are other ways to handle this...
>> What Tool can I use to kill such processes?
>
> Why do you want to kill them? They're waiting for a lock. What is
> it?

I don''t want to kill them. So how can I find out, what ist locking
them?
Is there a tool, which shows me such Information?

Thanks,
Stefan Sturm

On Apr 11, 2008, at 4:23 PM, Stefan Sturm wrote:
> Hello,
>
> Am 11.04.2008 um 18:41 schrieb Andrew Sullivan:
>
>> On Fri, Apr 11, 2008 at 03:09:43PM +0200, Stefan Sturm wrote:
>>> DBVisualizer stoped working and no I have two processes on my
>>> server:
>>> postgres 30873 0.0 0.7 46552 7124 ? Ss 12:33 0:01
>>> postgres: postgres dbName 10.0.1.198(53658) ALTER TABLE waiting
>>> postgres 31007 0.0 0.6 46708 6500 ? Ss 13:07 0:00
>>> postgres: postgres dbName 10.0.1.198(53799) SELECT waiting
>>>
>>> What can I do? OK, I can stop and start the Database, but I think
>>> there are other ways to handle this...
>>> What Tool can I use to kill such processes?
>>
>> Why do you want to kill them? They're waiting for a lock. What is
>> it?
>
> I don''t want to kill them. So how can I find out, what ist locking
> them?
> Is there a tool, which shows me such Information?

There is a system catalog view called pg_locks that has an attribute
called pid that you can use to search for entries for those procs.

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

On Fri, Apr 11, 2008 at 04:48:03PM -0500, Erik Jones wrote:
> On Apr 11, 2008, at 4:23 PM, Stefan Sturm wrote:
>> I don''t want to kill them. So how can I find out, what ist locking them?
>> Is there a tool, which shows me such Information?
>
> There is a system catalog view called pg_locks that has an attribute called
> pid that you can use to search for entries for those procs.

This is priceless:
http://www.postgresql.org/docs/8.3/static/explicit-locking.html
it explains the lock modes.

As for the data collection:
http://archives.postgresql.org/pgsql-general/2006-10/msg00841.php
this is the best starting place I've been able to find, hopefully
there is more and I've not found it.

Now what we need is some wise elaboration here: