Re: Tablespace files deleted in continuous run

Hi,

I have created index on some of the tables in the custom tablespace. But
the indexes resides in the default tablspace. Is there any issue in it?
Is there any chances for failing the crash recovery? Is there any chance
for deleting the tablespace, if the crash recovery using WAL and fsync
is failed?

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.