Thread: Leftover processes on shutdown - Debian+JDBC

Leftover processes on shutdown - Debian+JDBC

From
"Nick Fankhauser"
Date:
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/


Re: Leftover processes on shutdown - Debian+JDBC

From
"Robert M. Meyer"
Date:
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


Re: Leftover processes on shutdown - Debian+JDBC

From
"Nick Fankhauser"
Date:
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


Re: Leftover processes on shutdown - Debian+JDBC

From
"Robert M. Meyer"
Date:
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


Re: Leftover processes on shutdown - Debian+JDBC

From
Tom Lane
Date:
"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

Re: Leftover processes on shutdown - Debian+JDBC

From
"Nick Fankhauser"
Date:
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
>


Re: Leftover processes on shutdown - Debian+JDBC

From
"Robert M. Meyer"
Date:
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


Re: Leftover processes on shutdown - Debian+JDBC

From
Tom Lane
Date:
"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

Re: Leftover processes on shutdown - Debian+JDBC

From
Oliver Elphick
Date:
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


Re: Leftover processes on shutdown - Debian+JDBC

From
"Chad R. Larson"
Date:
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


Re: Leftover processes on shutdown - Debian+JDBC

From
Tom Lane
Date:
"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

Hardware Tuning

From
Chris Gamache
Date:
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

Re: Hardware Tuning

From
Andrew Sullivan
Date:
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


Re: Leftover processes on shutdown - Debian+JDBC

From
"Nick Fankhauser"
Date:
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
>


Re: Leftover processes on shutdown - Debian+JDBC

From
"Chad R. Larson"
Date:
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


Re: Leftover processes on shutdown - Debian+JDBC

From
"Nick Fankhauser"
Date:
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
>


Re: Leftover processes on shutdown - Debian+JDBC

From
Tom Lane
Date:
"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

Re: Leftover processes on shutdown - Debian+JDBC

From
"Nick Fankhauser"
Date:
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
>


Re: Leftover processes on shutdown - Debian+JDBC

From
Tom Lane
Date:
"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