Re: Tablespace files deleted in continuous run

Hi,

The database server is started using IP address of the machine and port
number 5432 (The default Postgresql 8.4 server is stopped).
The application deletes the contents of tables in the tablespace using
DELETE TABLE query.

No antivirus software is installed in the machine. The machine is
connected only to the local network. Not to internet.
SO no chance of virus attack.

We are not expecting a hardware failure because, we can do all the
operations on the same folder using windows explorer.

Another important thing is that the tables on the tablespace contains
bytea data which is of size 1 - 5 MB
In some situations a bulk transfer of data will happen to this tables.
(Usually in 1 hour)

Another thing is that the machine has logged off and restarted 5-6 times
during the run.
Then the application to start the database server is configured in the
startup.

Thanks & Regards,
Vishnu S

-----Original Message-----
From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
Sent: Thursday, October 13, 2011 8:10 PM
To: Vishnu S.; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] Tablespace files deleted in continuous run

"Vishnu S." <vishnu(dot)s(at)nestgroup(dot)net> wrote:

> I am using PostgreSQL 8.4 in windows. I have created a database
> and some tables on it. Also created a table space and some tables
> in it. My application inserts data into these tables in every
> second. The application is a continuous running application. My
> issue is that after a 20-30 days continuous run ( Machine logged
> off 2 times), some of the files in the \16384 folder of newly
> created table space are dropped automatically. Can you tell me
> the reason for this.?

If you have any anti-virus software installed, that would be my
first suspicion, even if that software is "disabled". You normally
need to uninstall AV software to prevent it from causing problems
with a database.

The second thing I would check is whether your storage system is
reliable. What file system are you using against what sort of
hardware? Improperly configured media or flaky drivers could be the
cause.

Other hardware could be at fault -- I've seen disks malfunction due
to a faulty UPS sending power spikes into the system. Bad RAM can
cause all sorts of weird problems, including lost files.

It's not impossible that this is a PostgreSQL bug, but it seems far
less likely to me than the above, and you haven't provided nearly
enough detail to allow anyone to find such a bug if it does exist.

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

> How can we recover from this?

If you're talking about recovering data from the deleted files, your
best bet would be to go to your backup. This is where Point In Time
Recovery (PITR) backups can be very useful. If you don't have the
data on a backup, you could try to salvage the files with an OS
utility of some sort.

If you're talking about not having a recurrence of the problem,
that's pretty hard to say without identifying the problem.

> I have a task that delete the data(only data stored in table) from
> the tables in the tablespace in certain interval. Is there any
> problem related to this?

Using what techniques? DELETE FROM <tablename>? TRUNCATE TABLE
<tablename>? DROP TABLE on partitions? Something else?

-Kevin
***** Confidentiality Statement/Disclaimer *****

This message and any attachments is intended for the sole use of the intended recipient. It may contain confidential information. Any unauthorized use, dissemination or modification is strictly prohibited. If you are not the intended recipient, please notify the sender immediately then delete it from all your systems, and do not copy, use or print. Internet communications are not secure and it is the responsibility of the recipient to make sure that it is virus/malicious code exempt.
The company/sender cannot be responsible for any unauthorized alterations or modifications made to the contents. If you require any form of confirmation of the contents, please contact the company/sender. The company/sender is not liable for any errors or omissions in the content of this message.