Hello Florian,
Thanks for your reply.
lunedì, 24 agosto 98, you wrote:
>>In my experience, the problem seems to be caused by a lot of data being put
>>into the database. We are using the database to ingest real-time data 24
>>hours a day/7 days a week. The data comes in about every three minutes.
>>While I was not able to identify what the exact cause has been, I have
>>noticed that before the problem becomes critical (before the backend
>>terminates abnormally), the (number of) indexes do not correspond to the
>>actual table. That leads me to believe that the indexes do not get created
>>on all occasions. After some time, the table's internal indexes may be come
>>corrupted, and vacuuming does no good.
>>
>>When trying to fix this, I first delete the index, then recreate it., then
>>vacuum. If that doesn't work, then I drop the table, create the index,
>>reload the data, and then vacuum the table.
>>
>>I would be curious to see if anyone else has had this type of problem and
>>what their solutions were.
FW> Same with us here, we use a really big database, but in my experience, it
FW> happend only when I killed (with SIGTERM, but anyway) a single postgres
FW> process that made an "endless" query. I think that some internal tables are
FW> left over in the data/base directory tree, and postgres tends to get
FW> confused about them.
FW> Not sure about this anyway.
I want to say something about this stuff to hackers;
I see that temporary files are created in the same directory of data
and this generate a few of confusion, often I find files like
pg_psort# or pg_vlock and I'm not sure if I can safety remove them or not.
May I suggest to have a tmp directory on data/base/dbname/tmp
directory tree to these files ?
FW> DROP/CREATE INDEX didn't solve this, I always did a DROP DATABASE and a
FW> complete reload of all data, and then it worked fine again.
Your is a good tip Florian but I think it's not very professional, I
can't imagine me removing and re-creating the database every time I
have a problem with it. I can't believe that PostgreSQL is like
M$-Windows that I have to re-install every time I have a problem.
Anyway DROP DATABASE statement doesn't work for me, the postmaster gives me the same
error message before die.
Thanks anyway for your tips Florian.
Best regards,
Jose' mailto:sferac@bo.nettuno.it
