We have recently upgraded from PostgreSQL 7.4.5 to 8.1.4. Our DB is
about 45GB in size and has about 100 tables, lots of stored
procedures, triggers etc.
The DB lived on a SAN and is accessed via Fibre Channel cards in an
IBM BladeCenter. The system is running RedHat Linux Enterprise 3 I
think. We connect to it using the latest JDBC driver from Java 1.5.
Here is the problem. Yesterday night I saw one of my statements in
my code threw this exception:
net.za.pwnconsulting.dblayer.query.DBQueryLayerException: Could not
execute SQL query [select c.mc_ca_id as c_mc_ca_id, d.mc_ca_id as
d_mc_ca_id, b.ca_id, b.ca_ctr_id from contract a, contract_agreement
b left outer join monthend_contract c on c.mc_ca_id = ? and
c.mc_last_proc_date = ? left outer join monthend2_contract d on
d.mc_ca_id = ? and d.mc_last_proc_date = ? where a.ctr_id =
b.ca_ctr_id and b.ca_agreement_type = ? and b.ca_start_date > ? and
b.ca_start_date <= ? and a.ctr_id = ? and ca_level_company = ? and
ca_level_data = ? and ca_level_type = 'V' and ca_id <> ? limit 1] in
pool [Oasis] because: ERROR: out of memory
Detail: Failed on request of size 20.
org.postgresql.util.PSQLException: ERROR: out of memory
Detail: Failed on request of size 20.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse
(QueryExecutorImpl.java:1525)
at org.postgresql.core.v3.QueryExecutorImpl.processResults
(QueryExecutorImpl.java:1309)
at org.postgresql.core.v3.QueryExecutorImpl.execute
(QueryExecutorImpl.java:188)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute
(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags
(AbstractJdbc2Statement.java:340)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery
(AbstractJdbc2Statement.java:239)
at
net.za.pwnconsulting.dblayer.query.StandardQueryLayer.queryDatabaseInter
nal(StandardQueryLayer.java:289)
at
net.za.pwnconsulting.dblayer.query.StandardQueryLayer.queryDatabaseInter
nalExceptionWrapper(StandardQueryLayer.java:158)
at
net.za.pwnconsulting.dblayer.query.StandardQueryLayer.queryDatabase
(StandardQueryLayer.java:68)
at
net.za.pwnconsulting.dblayer.query.StandardQueryLayer.queryDatabase
(StandardQueryLayer.java:58)
at net.za.pwnconsulting.dblayer.AbstractDBLayer.queryDatabase
(AbstractDBLayer.java:57)
at
za.co.massmart.oasis.daemons.ContractExtensionTimerTask.safeExecuteInter
nal(ContractExtensionTimerTask.java:183)
at za.co.massmart.oasis.daemons.ExtendedHACronTimerTask.safeExecute
(ExtendedHACronTimerTask.java:94)
at net.za.pwnconsulting.javaconfig.utils.time.HACronTimerTask.execute
(HACronTimerTask.java:156)
at net.za.pwnconsulting.javaconfig.utils.time.CronTimerTask.run
(CronTimerTask.java:103)
at java.lang.Thread.run(Thread.java:595)
So I checked and saw another application connecting to another DB in
the same instance showed a very weird error. For an "update
tablename set field1 = X where primary_field = Y" statement I got a
duplicate key exception on the serial field "primary_field" - which
is impossible since I am not changing its value - I am updating a
different column. Vacuum full analyze did not fix this. I dropped
and recreated the index, then the error went away. This was just
after I restarted the postgres instance (I stopped it using pg_ctl
stop -m immediate" since there were connections I could not close
from other systems.
I restarted the application that encountered the first error above
and ran the task again - it worked. So I then started a vacuum full
analyze on the main production DB. It produced several warnings
about page sizes not being large enough I think, but it completed
successfully.
This morning we found some data generated this morning at 01:00 was
present, but most of the tables lost all data captured since 25
August 2006. That amounts to about 500 contracts, a while billing
cycle etc. We upgraded postgresql on the 20th.
I checked the file system and it yielded no errors (ext3). I checked
the server and there were no obvious hardware issues.
How can part of the DB just be lost like that? I have just verified
by restoring a backup made yesterday night at 20:00 - before the
errors I showed above happened - that DB also has the data missing.
The bill run happened yesterday afternoon so somewhere between that
something broke. Any ideas on how to trouble shoot this?
To summarise - the DB lost about 6 days of transactions for no known
reason.