Thread: Tablespace files deleted in continuous run
=20 =20 HI, =20 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.? How can we recover from this? =20 =20 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? Can you tell me the reason for this? =20 =20 Thanks & Regards, Vishnu S =20 =20 ***** Confidentiality Statement/Disclaimer ***** This message and any attachments is intended for the sole use of the intend= ed 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 fro= m 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 t= hat it is virus/malicious code exempt. The company/sender cannot be responsible for any unauthorized alterations o= r modifications made to the contents. If you require any form of confirmati= on of the contents, please contact the company/sender. The company/sender i= s not liable for any errors or omissions in the content of this message.
"Vishnu S." <vishnu.s@nestgroup.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
I generally use Ontrack EasyRecovery Professional for recovering deleted or data sitting in bad sectors from hard drives in windows. It's not free, but it works really well.=20 =20 I have had issues with Symantec BackupExec causing PostgreSQL to panic and shutdown when data is being inserted into tables during a backup. Might check to see if any backups were in progress at the time if you are doing file system backups of your PostgreSQL directory. =20 =20 From: pgsql-bugs-owner@postgresql.org [mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Vishnu S. Sent: Thursday, October 13, 2011 7:53 AM To: pgsql-bugs@postgresql.org Subject: [BUGS] Tablespace files deleted in continuous run =20 =20 =20 HI, =20 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.? How can we recover from this? =20 =20 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? Can you tell me the reason for this? =20 =20 Thanks & Regards, Vishnu S =20 =20 =20 ***** 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.
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.=20 SO no chance of virus attack.=20 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.=20 Thanks & Regards, Vishnu S -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]=20 Sent: Thursday, October 13, 2011 8:10 PM To: Vishnu S.; pgsql-bugs@postgresql.org Subject: Re: [BUGS] Tablespace files deleted in continuous run "Vishnu S." <vishnu.s@nestgroup.net> wrote: =20 > 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.? =20 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. =20 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. =20 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. =20 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. =20 http://wiki.postgresql.org/wiki/Guide_to_reporting_problems =20 > How can we recover from this? =20 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. =20 If you're talking about not having a recurrence of the problem, that's pretty hard to say without identifying the problem. =20 > 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? =20 Using what techniques? DELETE FROM <tablename>? TRUNCATE TABLE <tablename>? DROP TABLE on partitions? Something else? =20 -Kevin ***** Confidentiality Statement/Disclaimer ***** This message and any attachments is intended for the sole use of the intend= ed 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 fro= m 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 t= hat it is virus/malicious code exempt. The company/sender cannot be responsible for any unauthorized alterations o= r modifications made to the contents. If you require any form of confirmati= on of the contents, please contact the company/sender. The company/sender i= s not liable for any errors or omissions in the content of this message.
On Fri, Oct 14, 2011 at 12:29 AM, Vishnu S. <vishnu.s@nestgroup.net> wrote: > The application deletes the contents of tables in the tablespace using > DELETE TABLE query. There's no DELETE TABLE command. Do you mean DELETE, or DROP TABLE? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
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.Grittner@wicourts.gov]=20 Sent: Thursday, October 13, 2011 8:10 PM To: Vishnu S.; pgsql-bugs@postgresql.org Subject: Re: [BUGS] Tablespace files deleted in continuous run "Vishnu S." <vishnu.s@nestgroup.net> wrote: =20 > 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.? =20 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. =20 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. =20 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. =20 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. =20 http://wiki.postgresql.org/wiki/Guide_to_reporting_problems =20 > How can we recover from this? =20 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. =20 If you're talking about not having a recurrence of the problem, that's pretty hard to say without identifying the problem. =20 > 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? =20 Using what techniques? DELETE FROM <tablename>? TRUNCATE TABLE <tablename>? DROP TABLE on partitions? Something else? =20 -Kevin ***** Confidentiality Statement/Disclaimer ***** This message and any attachments is intended for the sole use of the intend= ed 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 fro= m 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 t= hat it is virus/malicious code exempt. The company/sender cannot be responsible for any unauthorized alterations o= r modifications made to the contents. If you require any form of confirmati= on of the contents, please contact the company/sender. The company/sender i= s not liable for any errors or omissions in the content of this message.
"Vishnu S." <vishnu.s@nestgroup.net> wrote: > 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? No. > Is there any chance for deleting the tablespace, if the crash > recovery using WAL and fsync is failed? I don't know what "fsync is failed" means, but if you turn off fsync or have volatile caches between the database and the permanent media, an OS or hardware crash can leave you with an unusable database. http://www.postgresql.org/docs/current/interactive/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS You haven't given the lists enough information to do more than make wild guesses about the nature of your runtime environment or your actual problems, but if part of the picture is that fsync is turned off, there are absolutely no guarantees about having a usable database if the OS goes down abruptly. -Kevin