My database ran out of disk space the other day (I usually monitor better
but my wife is having chemo and that is more important). Anyway, I
shtudown the server using the 'pg_ctl stop' command, moved about 6GB of
indexes to another drive and fixed all the links. After I restarted the
server everything looked fine until I got:
FATAL 2: open of /usr/local/pgsql/data/pg_clog/0000 failed: No such file
or directory
This happens about every 30-40 minutes, then the server comes up and
behaves okay for a while until *POOF*.
I tried to dump my databases to one of my alternate servers, but it fails
because I have duplicate records in miscellaneous tables in a primary key.
I tried to get smart and changed the primary key to include the oid,
figuring that would make it unique, and it would also be easier to delete
one of the conflicting records. When I started the dump, I got the same
error. After many hours running on a table with about 200M records:
SELECT oid,cnt FROM (SELECT oid,count(oid) AS cnt FROM foo GROUP BY oid)
as bar WHERE cnt > 1 ;
I discovered that I have a bunch (20,000+) of records that have duplicated
oid numbers. Is this because of the disk running out of space or is it
some deeper more evil problem. Also, how the %!$#! do I fix it without
losing the associated data? Most of the records are identical (maybe
all), and when I delete any 1, all of them disappear. I guess I could
select distinct into a temporary table, delete from current, and then
insert from the temporary table, but this is gonna take a long time.
- brian
Wm. Brian McCane | Life is full of doors that won't open
Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"