473,222 Members | 1,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,222 software developers and data experts.

open of /usr/lib/pgsql/data/pg_clog/0F3E failed

Hi:

I'm running a large database on PostgreSQL 7.1.3. 20 days ago the
database failed with a threatening and not too descriptive error like:

pg_exec() query failed: server closed the connection unexpectedlyThis
probably means the server terminated abnormally before or while
processing the request.

I lost some data and had to recreate a table, without knowing the reason
for the error, now I got this error:

FATAL 2: open of /usr/lib/pgsql/data/pg_clog/0F3E failed: No existe el
fichero o el directorio
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

When I VACUUM the affected table I get:

FATAL 2: open of /usr/lib/pgsql/data/pg_clog/0F5C failed: No existe el
fichero o el directorio
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: NOTICE:
Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
Failed.

I guess I have lost again some data in one table.

Someone can help with the origin of these errors? How can I avoid them?

Thanks in advance.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #1
6 2658
ruben <ru*****@superguai.com> writes:
I lost some data and had to recreate a table, without knowing the reason
for the error, now I got this error: FATAL 2: open of /usr/lib/pgsql/data/pg_clog/0F3E failed: No existe el
fichero o el directorio


This looks like one of the common symptoms of corrupted data
(specifically, you have a trashed transaction number in some row header,
which causes the code to try to look up the transaction status in a
portion of clog that doesn't exist yet).

The most likely bet is that you are having intermittent hardware
problems causing data corruption. I'd suggest testing the hardware as
best you can. memtest86 and badblocks are a couple of widely-used test
programs for RAM and disk respectively.

As for trying to recover your data, you can look in the mailing list
archives for prior discussions of coping with data corruption. The
particular rows or pages that are corrupt are probably unrecoverable,
but you can zero them out and at least make the rest of the table
readable. First you need to stabilize the hardware, though.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2
Hi:

Lately I run into some problems with postmaster fatal errors, corrupted
data and indexes, lost tuples, etc. I was advised to check hardware,
I'll do so.

Today, one of the processes running daily took 4 hours when it takes
about 5 minutes. After a VACCUM ANALYZE of the affected tables it took
the same to finish, then I recreated (drop and create) the index of the
affected table and the process when again fast. My question is, isn't
enough to run a VACCUM to optimize a table and its indexes? Is it
advisable to recreate indexes from time to time?

Thanks in advance.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #3
In article <41**************@superguai.com>,
ruben <ru*****@superguai.com> writes:
Today, one of the processes running daily took 4 hours when it takes
about 5 minutes. After a VACCUM ANALYZE of the affected tables it took
the same to finish, then I recreated (drop and create) the index of
the affected table and the process when again fast. My question is,
isn't enough to run a VACCUM to optimize a table and its indexes? Is
it advisable to recreate indexes from time to time?


This was necessary in PostgreSQL up to 7.3.x, but 7.4.x is supposed to
fix that. What version are you running?
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4
Thanks Harald, i'm running PostgreSQL 7.1.3.

Harald Fuchs wrote:
In article <41**************@superguai.com>,
ruben <ru*****@superguai.com> writes:

Today, one of the processes running daily took 4 hours when it takes
about 5 minutes. After a VACCUM ANALYZE of the affected tables it took
the same to finish, then I recreated (drop and create) the index of
the affected table and the process when again fast. My question is,
isn't enough to run a VACCUM to optimize a table and its indexes? Is
it advisable to recreate indexes from time to time?

This was necessary in PostgreSQL up to 7.3.x, but 7.4.x is supposed to
fix that. What version are you running?
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #5
Unfortunately, the administrative overhead in 7.1.3 is noticeably higher
than it is in 7.4. The overhead should be lowered even more in 8.0 with
the integration of the autovacuum daemon into the backend process.

On Fri, 2004-08-06 at 10:24, ruben wrote:
Thanks Harald, i'm running PostgreSQL 7.1.3.

Harald Fuchs wrote:
In article <41**************@superguai.com>,
ruben <ru*****@superguai.com> writes:

Today, one of the processes running daily took 4 hours when it takes
about 5 minutes. After a VACCUM ANALYZE of the affected tables it took
the same to finish, then I recreated (drop and create) the index of
the affected table and the process when again fast. My question is,
isn't enough to run a VACCUM to optimize a table and its indexes? Is
it advisable to recreate indexes from time to time?

This was necessary in PostgreSQL up to 7.3.x, but 7.4.x is supposed to
fix that. What version are you running?
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #6
Scott Marlowe wrote:
Unfortunately, the administrative overhead in 7.1.3 is noticeably higher
than it is in 7.4. The overhead should be lowered even more in 8.0 with
the integration of the autovacuum daemon into the backend process.


May be the autovacuum will be integrated in the version 8.1 and not in the
version 8.0. However the pg_autovacuum is not enough, I suggest at least
once a day perform a vacuum full and a reindex for tables eavily updated.
Regards
Gaetano Mendola
Nov 23 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: news.paradise.net.nz | last post by:
My database appears to be a little corrupted, so far it's running along 99% ok, but i am getting some strange results. Primarily i can't back it up because of the following error. Similar errors...
1
by: Hema Sekhar | last post by:
Hi I am Hemasekhar K.P I am trying to replicae pgsql on REDHATLinux 8, but the site http://gborg.postgresql.org/genpage?replication_72install. is giving instructions on REDHATLinux 7, is there...
1
by: Phil Campaigne | last post by:
Hi All, Took a break from developing on pgsql 7.3 to set up network printing where I added another host(localhost 193.168.1.2) in red hat network settings. Later, I could start postmaster but...
2
by: Adam Haberlach | last post by:
We had one of our databases suddenly try to reference a pg_clog file that didn't exist whenever we attempted to select from a certain table. Anyone know of a way to recover from this? Is there a...
1
by: NTPT | last post by:
Hi I try to upgrade postgresql 7.3.3 to 7.4.3 on my machine running Apache 2.0.49 and PHP 4.3.7 Because i use another path for postgres 7.4 there (luckily) stay a old libpg So while i...
3
by: Glen Parker | last post by:
First things first: Postgresql 8.4.2 on Fedora Core 2 X86. Something seems to have happened to my pg_xlog and pg_clog directories after (I believe) a power outage. In the course of trying to...
9
by: Matthew T. O'Connor | last post by:
Hello, pg_dump started failing for one of my databases, so I looked in to it and it appears that I have some corrupted data or something. I assume this is related to a failed hard disk that was...
2
by: dennist685 | last post by:
Can't open Northwind I remembered doing walkthrough using Northwind. However it wasn't an http project but a file project using the development server. Northwind allowed me to add, edit and...
6
by: Frieder BĂĽrzele | last post by:
Hi, Im trying to integrate a rss feed into my page but struggle to fetch the dynamic generation of the rss feed If I type this into the browser as a result the xml file is shown as expected...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.