In response to Andrew Edson <cheighlund@yahoo.com>:
> The company I work for provides services for various offices around the country. In order to help keep our database
straight,and allow several of our client-side programs to verify their location, we include a table called 'region' in
ourdatabase. Although the database is replicated by Slony, this particular table I do not think is. Servers running
thedb are SuSE 9.3, running Postgresql 8.1.3, and I believe Slony 1.1.
>
> Recently, there have been incidents across a few of the offices where the region table in their local copy of the
databasehas mysteriously lost all of its data. When this happens, the programs that require it (most of our
client-sidestuff) will not retrieve data related to those areas. I've already built an sql script to restore the table
quickly,I'm just curious as to why this is happening. It's not really a common event, as it's only happened maybe five
timesor so across a few dozen servers and about three months. The most recent incident was brought to our attention
today,which is why this is on my mind now.
>
> Does anyone have any suggestions as to what could be causing this single table to lose its data? I don't think
it'shappened more than once to any given server, and I have no clue where to begin looking for the cause. Any
suggestionswill be greatly appreciated.
Sounds like the problem is too erratic to be easily diagnosed. It also sounds
too dispersed to be related to a hardware problem or anything like that.
My first thought is that 8.1.3 is pretty old. Any reason not to upgrade to
8.1.9?
My second thought would be to enable statement logging on all the servers.
This will generate a lot of log activity, so make sure you have the disk
space available, and proper log rotation in place. I'm suggesting this
because my second guess on this problem would be that an application is
erroneously deleting the data. If that's the case, you should be able to
track it down next time it happens if you have statement logging enabled.
--
Bill Moran
http://www.potentialtech.com