a strange error

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.