Thread: 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.s.sturm@googlemail.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@myemma.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: