Обсуждение: Leftover processes on shutdown - Debian+JDBC
Hi- I've run across a problem that has a simple workaround, but I wonder if it indicates a problem that needs fixing on the postgresql server end. Alternately, it may be considered a feature by some... My environment is PostgreSQL 7.2.1 on Debian Linux 2.4. On the client side, I have Tomcat accessing the database using the 7.2 PostgreSQL JDBC Driver. The problem is that if I attempt to do a PostgreSQL shutdown while tomcat still has connections open, the backend processes that have been spawned to serve tomcat don't go away. This surprises me because they are owned by postgres & hence it seems as if they should be stopped as well. The simple workaround is to make sure that I shutdown tomcat & make the client connections go away first. This is a sensible procedure anyway, so it isn't a serious problem. Since this involves the start/stop script, I suppose it may be Debian-specific. It also involves the JDBC driver, but I'm guessing that this behavior could occur with other client connections. I have tried to make the same thing happen by leaving a psql connection open, but the shutdown is complete in that case, and attempting further access through the psql client results in an informative error message & then a connection reset, so in the case of psql, the situation is handled gracefully. Here is the error message: morgai:/data1/postgresql# /etc/init.d/postgresql restart Restarting PostgreSQL database: postmaster Stopped /usr/lib/postgresql/bin/postmaster (pid 321 323 324 5465 5466 5470 5471 5472 5473 5474 5475 5476 5477). Starting PostgreSQL postmaster. pg_ctl: Another postmaster may be running. Trying to start postmaster anyway. pg_ctl: cannot start postmaster Examine the log output. -Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
I had the same problem. I rewrote my shutdown script to call 'pg_ctl -m immediate'. So far, I haven't seen any damage because of it and we always restart tomcat at some point in the middle of the night, anyway. Hope this helps... Cheers! Bob On Tue, 2002-08-13 at 11:09, Nick Fankhauser wrote: > Hi- > > I've run across a problem that has a simple workaround, but I wonder if it > indicates a problem that needs fixing on the postgresql server end. > Alternately, it may be considered a feature by some... > > My environment is PostgreSQL 7.2.1 on Debian Linux 2.4. On the client side, > I have Tomcat accessing the database using the 7.2 PostgreSQL JDBC Driver. > > The problem is that if I attempt to do a PostgreSQL shutdown while tomcat > still has connections open, the backend processes that have been spawned to > serve tomcat don't go away. This surprises me because they are owned by > postgres & hence it seems as if they should be stopped as well. > > The simple workaround is to make sure that I shutdown tomcat & make the > client connections go away first. This is a sensible procedure anyway, so it > isn't a serious problem. > > Since this involves the start/stop script, I suppose it may be > Debian-specific. It also involves the JDBC driver, but I'm guessing that > this behavior could occur with other client connections. > > I have tried to make the same thing happen by leaving a psql connection > open, but the shutdown is complete in that case, and attempting further > access through the psql client results in an informative error message & > then a connection reset, so in the case of psql, the situation is handled > gracefully. > > > Here is the error message: > > morgai:/data1/postgresql# /etc/init.d/postgresql restart > Restarting PostgreSQL database: postmaster > Stopped /usr/lib/postgresql/bin/postmaster (pid 321 323 324 5465 5466 5470 > 5471 5472 5473 5474 5475 5476 5477). > Starting PostgreSQL postmaster. > pg_ctl: Another postmaster may be running. Trying to start postmaster > anyway. > pg_ctl: cannot start postmaster > Examine the log output. > > -Nick > > -------------------------------------------------------------------------- > Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 > Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Robert M. Meyer Sr. Network Administrator DigiVision Satellite Services 14 Lafayette Sq, Ste 410 Buffalo, NY 14203-1904 (716)332-1451
Bob- Thanks for the suggestion! Did you implement this in a Debian Linux environment? One of my dillemas is that Debian has a nice (but sometimes limiting) utility called "start-stop-daemon" that is used for shutdown in the Debian postgresql package, so I think modifying the shutdown script will involve more than just adding the "-m immediate" switch in the script. On the other hand, it looks like pg_ctl functions in a similar role, so it may be possible to just substitute one utility for the other. If you did this in Debian, I'd like to know if you were able to make a simple substitution. If it works well, we should suggest the change to Oliver Elphick for the next Debian package release. Regards, -Nick > -----Original Message----- > From: Robert M. Meyer [mailto:rmeyer@installs.com] > Sent: Tuesday, August 13, 2002 10:26 AM > To: nickf@ontko.com > Cc: pgsql-jdbc; pgsql-admin > Subject: Re: [ADMIN] Leftover processes on shutdown - Debian+JDBC > > > I had the same problem. I rewrote my shutdown script to call 'pg_ctl -m > immediate'. So far, I haven't seen any damage because of it and we > always restart tomcat at some point in the middle of the night, anyway. > > Hope this helps... > > Cheers! > > Bob > > On Tue, 2002-08-13 at 11:09, Nick Fankhauser wrote: > > Hi- > > > > I've run across a problem that has a simple workaround, but I > wonder if it > > indicates a problem that needs fixing on the postgresql server end. > > Alternately, it may be considered a feature by some... > > > > My environment is PostgreSQL 7.2.1 on Debian Linux 2.4. On the > client side, > > I have Tomcat accessing the database using the 7.2 PostgreSQL > JDBC Driver. > > > > The problem is that if I attempt to do a PostgreSQL shutdown > while tomcat > > still has connections open, the backend processes that have > been spawned to > > serve tomcat don't go away. This surprises me because they are owned by > > postgres & hence it seems as if they should be stopped as well. > > > > The simple workaround is to make sure that I shutdown tomcat & make the > > client connections go away first. This is a sensible procedure > anyway, so it > > isn't a serious problem. > > > > Since this involves the start/stop script, I suppose it may be > > Debian-specific. It also involves the JDBC driver, but I'm guessing that > > this behavior could occur with other client connections. > > > > I have tried to make the same thing happen by leaving a psql connection > > open, but the shutdown is complete in that case, and attempting further > > access through the psql client results in an informative error message & > > then a connection reset, so in the case of psql, the situation > is handled > > gracefully. > > > > > > Here is the error message: > > > > morgai:/data1/postgresql# /etc/init.d/postgresql restart > > Restarting PostgreSQL database: postmaster > > Stopped /usr/lib/postgresql/bin/postmaster (pid 321 323 324 > 5465 5466 5470 > > 5471 5472 5473 5474 5475 5476 5477). > > Starting PostgreSQL postmaster. > > pg_ctl: Another postmaster may be running. Trying to start postmaster > > anyway. > > pg_ctl: cannot start postmaster > > Examine the log output. > > > > -Nick > > > > > -------------------------------------------------------------------------- > > Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax > 1.765.962.9788 > > Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Robert M. Meyer Sr. Network Administrator DigiVision Satellite Services 14 Lafayette Sq, Ste 410 Buffalo, NY 14203-1904 (716)332-1451
Actually, I'm doing this on RedHat 7.2 but the concept is the same. If Debian is using /etc/rc.d/rc?.d/postgresql as it's start and stop script, then you should be able to modify that directly. Actually, Postgres 7.2.1 comes with an example script that I used but I just made the change to add the '-m immediate' to the thing. I did this for two reasons. One, during shutdown, the database would not be down before the machine rebooted, causing all kinds of consternation and tomcat would hold up the shutdown of the DB for our nightly maintenance. Cheers! Bob On Tue, 2002-08-13 at 11:55, Nick Fankhauser wrote: > Bob- > > Thanks for the suggestion! > > Did you implement this in a Debian Linux environment? > > One of my dillemas is that Debian has a nice (but sometimes limiting) > utility called "start-stop-daemon" that is used for shutdown in the Debian > postgresql package, so I think modifying the shutdown script will involve > more than just adding the "-m immediate" switch in the script. > > On the other hand, it looks like pg_ctl functions in a similar role, so it > may be possible to just substitute one utility for the other. If you did > this in Debian, I'd like to know if you were able to make a simple > substitution. > > If it works well, we should suggest the change to Oliver Elphick for the > next Debian package release. > > Regards, > > -Nick > > > -----Original Message----- > > From: Robert M. Meyer [mailto:rmeyer@installs.com] > > Sent: Tuesday, August 13, 2002 10:26 AM > > To: nickf@ontko.com > > Cc: pgsql-jdbc; pgsql-admin > > Subject: Re: [ADMIN] Leftover processes on shutdown - Debian+JDBC > > > > > > I had the same problem. I rewrote my shutdown script to call 'pg_ctl -m > > immediate'. So far, I haven't seen any damage because of it and we > > always restart tomcat at some point in the middle of the night, anyway. > > > > Hope this helps... > > > > Cheers! > > > > Bob > > > > On Tue, 2002-08-13 at 11:09, Nick Fankhauser wrote: > > > Hi- > > > > > > I've run across a problem that has a simple workaround, but I > > wonder if it > > > indicates a problem that needs fixing on the postgresql server end. > > > Alternately, it may be considered a feature by some... > > > > > > My environment is PostgreSQL 7.2.1 on Debian Linux 2.4. On the > > client side, > > > I have Tomcat accessing the database using the 7.2 PostgreSQL > > JDBC Driver. > > > > > > The problem is that if I attempt to do a PostgreSQL shutdown > > while tomcat > > > still has connections open, the backend processes that have > > been spawned to > > > serve tomcat don't go away. This surprises me because they are owned by > > > postgres & hence it seems as if they should be stopped as well. > > > > > > The simple workaround is to make sure that I shutdown tomcat & make the > > > client connections go away first. This is a sensible procedure > > anyway, so it > > > isn't a serious problem. > > > > > > Since this involves the start/stop script, I suppose it may be > > > Debian-specific. It also involves the JDBC driver, but I'm guessing that > > > this behavior could occur with other client connections. > > > > > > I have tried to make the same thing happen by leaving a psql connection > > > open, but the shutdown is complete in that case, and attempting further > > > access through the psql client results in an informative error message & > > > then a connection reset, so in the case of psql, the situation > > is handled > > > gracefully. > > > > > > > > > Here is the error message: > > > > > > morgai:/data1/postgresql# /etc/init.d/postgresql restart > > > Restarting PostgreSQL database: postmaster > > > Stopped /usr/lib/postgresql/bin/postmaster (pid 321 323 324 > > 5465 5466 5470 > > > 5471 5472 5473 5474 5475 5476 5477). > > > Starting PostgreSQL postmaster. > > > pg_ctl: Another postmaster may be running. Trying to start postmaster > > > anyway. > > > pg_ctl: cannot start postmaster > > > Examine the log output. > > > > > > -Nick > > > > > > > > -------------------------------------------------------------------------- > > > Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax > > 1.765.962.9788 > > > Ray Ontko & Co. Software Consulting Services > http://www.ontko.com/ > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- > Robert M. Meyer > Sr. Network Administrator > DigiVision Satellite Services > 14 Lafayette Sq, Ste 410 > Buffalo, NY 14203-1904 > (716)332-1451 > -- Robert M. Meyer Sr. Network Administrator DigiVision Satellite Services 14 Lafayette Sq, Ste 410 Buffalo, NY 14203-1904 (716)332-1451
"Robert M. Meyer" <rmeyer@installs.com> writes:
> I had the same problem. I rewrote my shutdown script to call 'pg_ctl -m
> immediate'. So far, I haven't seen any damage because of it
A much safer choice would be "-m fast".
The default ("-m smart") doesn't shut down the server until all clients
voluntarily disconnect. If you have tomcat holding persistent
connections open then that's not what you want.
"-m fast" forcibly disconnects clients and then does a clean database
shutdown. This is generally what you want if you have
persistent-connection clients.
"-m immediate" is the equivalent of the big red EMERGENCY STOP button
found in places like power plant control rooms. If you are in the habit
of hitting that as a routine measure, you need to have your operator's
permit revoked.
regards, tom lane
The difference is that in the Debian distribution, the equivalent of /etc/rc.d/rc?.d/postgresql doesn't use pg_ctl for shutdown, but I'm going to try that modification on one of our development boxes, & I'm hopeful that it will work. I'll report back to the list if it works. -Nick > -----Original Message----- > From: Robert M. Meyer [mailto:rmeyer@installs.com] > Sent: Tuesday, August 13, 2002 11:05 AM > To: nickf@ontko.com > Cc: pgsql-admin@postgresql.org > Subject: RE: [ADMIN] Leftover processes on shutdown - Debian+JDBC > > > Actually, I'm doing this on RedHat 7.2 but the concept is the same. If > Debian is using /etc/rc.d/rc?.d/postgresql as it's start and stop > script, then you should be able to modify that directly. Actually, > Postgres 7.2.1 comes with an example script that I used but I just made > the change to add the '-m immediate' to the thing. > > I did this for two reasons. One, during shutdown, the database would > not be down before the machine rebooted, causing all kinds of > consternation and tomcat would hold up the shutdown of the DB for our > nightly maintenance. > > Cheers! > > Bob > > On Tue, 2002-08-13 at 11:55, Nick Fankhauser wrote: > > Bob- > > > > Thanks for the suggestion! > > > > Did you implement this in a Debian Linux environment? > > > > One of my dillemas is that Debian has a nice (but sometimes limiting) > > utility called "start-stop-daemon" that is used for shutdown in > the Debian > > postgresql package, so I think modifying the shutdown script > will involve > > more than just adding the "-m immediate" switch in the script. > > > > On the other hand, it looks like pg_ctl functions in a similar > role, so it > > may be possible to just substitute one utility for the other. If you did > > this in Debian, I'd like to know if you were able to make a simple > > substitution. > > > > If it works well, we should suggest the change to Oliver Elphick for the > > next Debian package release. > > > > Regards, > > > > -Nick > > > > > -----Original Message----- > > > From: Robert M. Meyer [mailto:rmeyer@installs.com] > > > Sent: Tuesday, August 13, 2002 10:26 AM > > > To: nickf@ontko.com > > > Cc: pgsql-jdbc; pgsql-admin > > > Subject: Re: [ADMIN] Leftover processes on shutdown - Debian+JDBC > > > > > > > > > I had the same problem. I rewrote my shutdown script to call > 'pg_ctl -m > > > immediate'. So far, I haven't seen any damage because of it and we > > > always restart tomcat at some point in the middle of the > night, anyway. > > > > > > Hope this helps... > > > > > > Cheers! > > > > > > Bob > > > > > > On Tue, 2002-08-13 at 11:09, Nick Fankhauser wrote: > > > > Hi- > > > > > > > > I've run across a problem that has a simple workaround, but I > > > wonder if it > > > > indicates a problem that needs fixing on the postgresql server end. > > > > Alternately, it may be considered a feature by some... > > > > > > > > My environment is PostgreSQL 7.2.1 on Debian Linux 2.4. On the > > > client side, > > > > I have Tomcat accessing the database using the 7.2 PostgreSQL > > > JDBC Driver. > > > > > > > > The problem is that if I attempt to do a PostgreSQL shutdown > > > while tomcat > > > > still has connections open, the backend processes that have > > > been spawned to > > > > serve tomcat don't go away. This surprises me because they > are owned by > > > > postgres & hence it seems as if they should be stopped as well. > > > > > > > > The simple workaround is to make sure that I shutdown > tomcat & make the > > > > client connections go away first. This is a sensible procedure > > > anyway, so it > > > > isn't a serious problem. > > > > > > > > Since this involves the start/stop script, I suppose it may be > > > > Debian-specific. It also involves the JDBC driver, but I'm > guessing that > > > > this behavior could occur with other client connections. > > > > > > > > I have tried to make the same thing happen by leaving a > psql connection > > > > open, but the shutdown is complete in that case, and > attempting further > > > > access through the psql client results in an informative > error message & > > > > then a connection reset, so in the case of psql, the situation > > > is handled > > > > gracefully. > > > > > > > > > > > > Here is the error message: > > > > > > > > morgai:/data1/postgresql# /etc/init.d/postgresql restart > > > > Restarting PostgreSQL database: postmaster > > > > Stopped /usr/lib/postgresql/bin/postmaster (pid 321 323 324 > > > 5465 5466 5470 > > > > 5471 5472 5473 5474 5475 5476 5477). > > > > Starting PostgreSQL postmaster. > > > > pg_ctl: Another postmaster may be running. Trying to start > postmaster > > > > anyway. > > > > pg_ctl: cannot start postmaster > > > > Examine the log output. > > > > > > > > -Nick > > > > > > > > > > > > -------------------------------------------------------------------------- > > > > Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax > > > 1.765.962.9788 > > > > Ray Ontko & Co. Software Consulting Services > > http://www.ontko.com/ > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > TIP 2: you can get off all lists at once with the unregister command > > > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) > > -- > > Robert M. Meyer > > Sr. Network Administrator > > DigiVision Satellite Services > > 14 Lafayette Sq, Ste 410 > > Buffalo, NY 14203-1904 > > (716)332-1451 > > > -- > Robert M. Meyer > Sr. Network Administrator > DigiVision Satellite Services > 14 Lafayette Sq, Ste 410 > Buffalo, NY 14203-1904 > (716)332-1451 >
Hmmm... You're right. How much longer does a '-m fast' take. The
reason I ask is because during a server shutdown, init doesn't wait
around long after the script exits. The problem with not using the '-m'
is that pg_ctl returns quickly but the DB may not be down, causing the
system to pull the rug out from under it.
Cheers!
Bob
On Tue, 2002-08-13 at 12:20, Tom Lane wrote:
> "Robert M. Meyer" <rmeyer@installs.com> writes:
> > I had the same problem. I rewrote my shutdown script to call 'pg_ctl -m
> > immediate'. So far, I haven't seen any damage because of it
>
> A much safer choice would be "-m fast".
>
> The default ("-m smart") doesn't shut down the server until all clients
> voluntarily disconnect. If you have tomcat holding persistent
> connections open then that's not what you want.
>
> "-m fast" forcibly disconnects clients and then does a clean database
> shutdown. This is generally what you want if you have
> persistent-connection clients.
>
> "-m immediate" is the equivalent of the big red EMERGENCY STOP button
> found in places like power plant control rooms. If you are in the habit
> of hitting that as a routine measure, you need to have your operator's
> permit revoked.
>
> regards, tom lane
--
Robert M. Meyer
Sr. Network Administrator
DigiVision Satellite Services
14 Lafayette Sq, Ste 410
Buffalo, NY 14203-1904
(716)332-1451
"Robert M. Meyer" <rmeyer@installs.com> writes:
> Hmmm... You're right. How much longer does a '-m fast' take. The
> reason I ask is because during a server shutdown, init doesn't wait
> around long after the script exits. The problem with not using the '-m'
> is that pg_ctl returns quickly but the DB may not be down, causing the
> system to pull the rug out from under it.
(a) The solution to that is to use the -w option to pg_ctl, no?
(b) -m immediate is just as dangerous as letting the system pull the
rug out. Probably more so, as the system is unlikely to get around to
killing Postgres before we've had a chance to flush shared buffers.
regards, tom lane
On Tue, 2002-08-13 at 16:55, Nick Fankhauser wrote: > On the other hand, it looks like pg_ctl functions in a similar role, so it > may be possible to just substitute one utility for the other. If you did > this in Debian, I'd like to know if you were able to make a simple > substitution. > > If it works well, we should suggest the change to Oliver Elphick for the > next Debian package release. I'll be happy to accept a patch. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Watch ye therefore, and pray always, that ye may be accounted worthy to escape all these things that shall come to pass, and to stand before the Son of man." Luke 21:36
At 08:25 AM 8/13/02 , Robert M. Meyer wrote:
>I had the same problem. I rewrote my shutdown script to call 'pg_ctl -m
>immediate'. So far, I haven't seen any damage because of it and we always
>restart tomcat at some point in the middle of the night, anyway.
We're running PostgreSQL as a backend to a J2SE server (with Macromedia
JRun instead of Tomcat). When we converted our Sun Enterprise servers into
a cluster (Veritas VCS) we had similar problems. We were using the "fast"
option on a shutdown, but switchover in the cluster would hang until we
changed the "fast" to "immediate". We'd prefer "fast", but it does not
seem to work exactly as documented.
Solaris 2.6, PostgreSQL 7.1.2, VCS 1.1.
-crl
--
Chad R. Larson (CRL22) chad@eldocomp.com
Eldorado Computing, Inc. 602-604-3100
5353 North 16th Street, Suite 400
Phoenix, Arizona 85016-3228
"Chad R. Larson" <clarson@eldocomp.com> writes:
> We're running PostgreSQL as a backend to a J2SE server (with Macromedia
> JRun instead of Tomcat). When we converted our Sun Enterprise servers into
> a cluster (Veritas VCS) we had similar problems. We were using the "fast"
> option on a shutdown, but switchover in the cluster would hang until we
> changed the "fast" to "immediate". We'd prefer "fast", but it does not
> seem to work exactly as documented.
> Solaris 2.6, PostgreSQL 7.1.2, VCS 1.1.
Hmm. Could you look into it and figure out *why* it didn't work?
It's possible that this Solaris configuration has the same kind of
kernel bug that was recently reported against Mac OSX 10.1: normal
postmaster shutdown fails during system shutdown because fork()
stops working, thus the postmaster cannot launch a checkpoint subprocess
to clean up. (I'm not certain that fork itself is the issue in OSX,
but the behavior was definitely that the checkpoint subprocess never
got dispatched, even though the postmaster got a success return from
fork...)
regards, tom lane
I've read Bruce's white paper on Hardware Tuning. I'd like advise on squeezing the most performance out of our database... It is performing nicely with the settings below. There is no swapping to disk going on, which makes me think I might be able to push the envelope even further... Perhaps y'all could make some suggestions. :) Dual P4 2.0GHz 1GB RDRAM Linux Kernel 2.4.18 2 seperate 10000 RPM SCSI Filesystems data files and WAL on seperate filesystems ---[postgresql.conf]----------- max_connections = 32 shared_buffers = 256 sort_mem = 2048 fsync=false effective_cache_size = 3000 wal_buffers = 8 wal_files = 32 wal_sync_method = fsync wal_debug = 0 commit_delay = 0 commit_siblings = 5 checkpoint_segments = 3 checkpoint_timeout = 300 ---[/postgresql.conf]----------- I could post some system stats... Its biggest performance hit is when I do a SELECT on non-indexed columns, which is not very often. I doubt there is much that can be done about that (except for creating an index...) CG __________________________________________________ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com
On Tue, Aug 13, 2002 at 11:37:15AM -0700, Chris Gamache wrote:
>
> ---[postgresql.conf]-----------
>
> max_connections = 32
> shared_buffers = 256
You can probably increase the shared buffers by a lot. 256 is _not_
256 meg: it's 256 buffers, which are typically 8192 bytes.
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
Hi Folks- After gleaning some good info from everyone's responses yesterday, I tried a couple of changes on the Debian start/stop script today, and here is what I learned: First, I removed the old commands to stop postgresql & substituted: pg_ctl stop -m smart Then I tried shutting down postgresql without first shutting down Tomcat, so the persistent connections remained. This is the response I got: morgai:/home/nickf# /etc/init.d/postgresql stop Stopping PostgreSQL database: postmaster waiting for postmaster to shut down................................................................ failed pg_ctl: postmaster does not shut down . Then I tried again, & shut down tomcat from another session after a few seconds so the connections would go away, and got this response: morgai:/home/nickf# /etc/init.d/postgresql stop Stopping PostgreSQL database: postmaster waiting for postmaster to shut down..........done postmaster successfully shut down . So far, so good. Then I tried changing "smart" to "fast", and did the same two tests. The results were exactly the same, so apparently "fast" mode does not forcibly disconnect clients as the Docs state. (I'm looking at: http://www.postgresql.org/idocs/index.php?app-pg-ctl.html) Just to cover all of the bases, I also tried "immediate" mode (on a development database <grin>) and confirmed that it does indeed force an immediate shutdown. As I mentioned in my original post, the simple workaround on the "fast" problem is to just not do something silly like neglect to shut down my client before shutting down the backend, so this is not an urgent issue for me, but it does appear to either be a bug or a misunderstanding of the Docs on my part. -Nick > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Oliver Elphick > Sent: Tuesday, August 13, 2002 11:56 AM > To: nickf@ontko.com > Cc: Robert M. Meyer; pgsql-admin > Subject: Re: [ADMIN] Leftover processes on shutdown - Debian+JDBC > > > On Tue, 2002-08-13 at 16:55, Nick Fankhauser wrote: > > > On the other hand, it looks like pg_ctl functions in a similar > role, so it > > may be possible to just substitute one utility for the other. If you did > > this in Debian, I'd like to know if you were able to make a simple > > substitution. > > > > If it works well, we should suggest the change to Oliver Elphick for the > > next Debian package release. > > I'll be happy to accept a patch. > > -- > Oliver Elphick Oliver.Elphick@lfix.co.uk > Isle of Wight, UK > http://www.lfix.co.uk/oliver > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "Watch ye therefore, and pray always, that ye may be > accounted worthy to escape all these things that shall > come to pass, and to stand before the Son of man." > Luke 21:36 > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
At 12:48 PM 8/14/02 , Nick Fankhauser wrote:
>Then I tried changing "smart" to "fast", and did the same two tests. The
>results were exactly the same, so apparently "fast" mode does not forcibly
>disconnect clients as the Docs state.
As best I can tell, with "smart" the backend waits for the connection to go
away, "fast" waits for the current transaction to complete and then exits,
and with "immediate" the backend croaks right away (forcing a rollback and
recovery on the next start up).
If your Java servlet has requested some transaction that takes a =very=
long time to run, you'd see what you see.
-crl
--
Chad R. Larson (CRL22) chad@eldocomp.com
Eldorado Computing, Inc. 602-604-3100
5353 North 16th Street, Suite 400
Phoenix, Arizona 85016-3228
The tests I did were on a "quiet" system- I shut down Tomcat to ensure that any old transactions were closed, then brought it back up and immediately did the tests before any new transactions had started. (On a development system, so no users could have snuck in.) Our application opens a group of pooled persistent connections on startup, so there were about 4 connections open, but they weren't doing anything. Does -m fast actually close connections on your server? -Nick > -----Original Message----- > From: Chad R. Larson [mailto:clarson@eldocomp.com] > Sent: Wednesday, August 14, 2002 4:24 PM > To: nickf@ontko.com; pgsql-admin > Subject: Re: [ADMIN] Leftover processes on shutdown - Debian+JDBC > > > At 12:48 PM 8/14/02 , Nick Fankhauser wrote: > >Then I tried changing "smart" to "fast", and did the same two tests. The > >results were exactly the same, so apparently "fast" mode does > not forcibly > >disconnect clients as the Docs state. > > As best I can tell, with "smart" the backend waits for the > connection to go > away, "fast" waits for the current transaction to complete and > then exits, > and with "immediate" the backend croaks right away (forcing a > rollback and > recovery on the next start up). > > If your Java servlet has requested some transaction that takes a =very= > long time to run, you'd see what you see. > > > -crl > -- > Chad R. Larson (CRL22) chad@eldocomp.com > Eldorado Computing, Inc. 602-604-3100 > 5353 North 16th Street, Suite 400 > Phoenix, Arizona 85016-3228 >
"Nick Fankhauser" <nickf@ontko.com> writes:
> Does -m fast actually close connections on your server?
It works as advertised for me: not only will it cut connections, but it
will abort queries-in-progress. For instance, I did this:
regression=# begin;
BEGIN
regression=# select * from tenk1 a, tenk1 b, tenk1 c;
-- the above would return 100billion rows if given the chance, so
-- after a second or two I issued "pg_ctl stop -m fast" in another
-- window, and promptly got:
FATAL: This connection has been terminated by the administrator.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#
Successful shutdown was reported by pg_ctl a couple seconds later,
as expected.
Not sure what's going wrong for you --- are you certain you did the test
correctly? Any possibility you ran the wrong script, shut down the
wrong server, etc?
regards, tom lane
Tom- I just spent some time trying to reproduce the behavior I saw yesterday, and I can't do it... In fact -m fast is doing exactly what it is supposed to. Apologies for the false alarm! So going back to my original question about the leftover processes- Doing a pg_ctl stop -m fast solves the problem nicely, and Oliver said he is going to change the script in the Debian package accordingly. -Nick > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Tom Lane > Sent: Wednesday, August 14, 2002 5:16 PM > To: nickf@ontko.com > Cc: Chad R. Larson; pgsql-admin > Subject: Re: [ADMIN] Leftover processes on shutdown - Debian+JDBC > > > "Nick Fankhauser" <nickf@ontko.com> writes: > > Does -m fast actually close connections on your server? > > It works as advertised for me: not only will it cut connections, but it > will abort queries-in-progress. For instance, I did this: > > regression=# begin; > BEGIN > regression=# select * from tenk1 a, tenk1 b, tenk1 c; > > -- the above would return 100billion rows if given the chance, so > -- after a second or two I issued "pg_ctl stop -m fast" in another > -- window, and promptly got: > > FATAL: This connection has been terminated by the administrator. > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > !# > > Successful shutdown was reported by pg_ctl a couple seconds later, > as expected. > > Not sure what's going wrong for you --- are you certain you did the test > correctly? Any possibility you ran the wrong script, shut down the > wrong server, etc? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
"Chad R. Larson" <clarson@eldocomp.com> writes:
> At 08:25 AM 8/13/02 , Robert M. Meyer wrote:
>> I had the same problem. I rewrote my shutdown script to call 'pg_ctl -m
>> immediate'. So far, I haven't seen any damage because of it and we always
>> restart tomcat at some point in the middle of the night, anyway.
> We're running PostgreSQL as a backend to a J2SE server (with Macromedia
> JRun instead of Tomcat). When we converted our Sun Enterprise servers into
> a cluster (Veritas VCS) we had similar problems. We were using the "fast"
> option on a shutdown, but switchover in the cluster would hang until we
> changed the "fast" to "immediate". We'd prefer "fast", but it does not
> seem to work exactly as documented.
> Solaris 2.6, PostgreSQL 7.1.2, VCS 1.1.
FYI, I think the cause of this has finally been identified -- a client
that sits idle for long enough to let the SI buffer overrun means that
its connected backend will ignore "fast" shutdown requests for as long
as the client continues to do nothing. The bug exists in 7.1 through
current. I just posted a patch to pgsql-admin.
regards, tom lane