Thread: a strange error
We are using PostgreSQL for more than 5 years by now and at least the
last 2-3 years we experience the following situation.
We refresh the live database on the development server using the
following shell script -
/usr/local/etc/rc.d/postgresql stop
rm -r /home/data/postgresql/data
/usr/local/etc/rc.d/postgresql initdb -D /home/data/postgresql/data
/usr/local/etc/rc.d/postgresql start
psql -f /home/database.sql -d template1 -U pgsql
(FreeBSD 6.x, PostgreSQL 8.3)
Everything works perfect, except the following -
When the db is refreshed, the first click inside the web application
that uses it (php 5.x, Apache 2.x), and some of the next clicks (i. e.
the 3rd, 5th, 8th, 12th) result in the following error -
PGSQL ERROR: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
After two dozen of clicks and several such messages, the application
operates normally until the next refresh.
Could somebody give us a tip what might be the reason?
Iv
Hi, I guess this has nothing to do with postgres. Your web applicationdoes some kind of connection pooling and doesn't notice when you shut down the database. When you access the application after you reinitialized the database it will use these old and now invald connections and run into the given error. After some requests, your webserver will spawn new child processes while others die and those new processes connect to the new database. To solve this, you should restart your application as well. Marc Iv Ray wrote: > We are using PostgreSQL for more than 5 years by now and at least the > last 2-3 years we experience the following situation. > > We refresh the live database on the development server using the > following shell script - > > /usr/local/etc/rc.d/postgresql stop > rm -r /home/data/postgresql/data > /usr/local/etc/rc.d/postgresql initdb -D /home/data/postgresql/data > /usr/local/etc/rc.d/postgresql start > psql -f /home/database.sql -d template1 -U pgsql > > (FreeBSD 6.x, PostgreSQL 8.3) > > Everything works perfect, except the following - > > When the db is refreshed, the first click inside the web application > that uses it (php 5.x, Apache 2.x), and some of the next clicks (i. e. > the 3rd, 5th, 8th, 12th) result in the following error - > PGSQL ERROR: server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > After two dozen of clicks and several such messages, the application > operates normally until the next refresh. > > Could somebody give us a tip what might be the reason? > > Iv > -- click:ware Informationstechnik GmbH i. A. Marc Schablewski Hausanschrift: Kamekestraße 19 50672 Köln Postanschrift: Postfach 10 04 22 50444 Köln Tel: (+49) 0221 139988-0 Fax: (+49) 0221 139988-79 Geschäftsführer: Dipl.-Ing. Thomas Goerner Handelsregisternr: HRB 31438 ( Amtsgericht Köln )
Hi Ray, On Wed, 2009-05-27 at 14:24 +0200, Iv Ray wrote: > When the db is refreshed, the first click inside the web application > that uses it (php 5.x, Apache 2.x), and some of the next clicks (i. e. > the 3rd, 5th, 8th, 12th) result in the following error - > PGSQL ERROR: server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > After two dozen of clicks and several such messages, the application > operates normally until the next refresh. Although I don't know the exact mechanics of how this happens, I guess it has to do with "permanent connections" used by PHP (a kind of connection caching), and the reused connections discovering that the data base backend they were connected to is gone. Given that an idle connection will not notice that the backend is gone until the next query, it can happen well after the "data base refresh" if the cached connection stays idle for that time. So probably you also want to clear the connection cache of php, but for that I have no idea how can be done. BTW, I think it would be less disruptive if you truncate your tables (supposing you don't need the content)... that can also be done using a plsql script which iterates through all tables in the public schema and truncates them, if you're concerned with changing schema... of course you would need to lock all tables exclusively for a short time, but that would be for sure less disruptive than deleting the DB. Cheers, Csaba.
Both explanations sound very similar and very logical indeed. Never occurred to me to think that way. Actually, now I remembered, that in a few rare occasions we had to restart the whole server after the "refresh" and then there was no problem, at all. Thank you both! Iv.