Thread: Space Problems

Space Problems

From
"Eduardo Naschenweng"
Date:
Hi, I has a problem with a production database Postgres 7.1.2.
Happens when finished free space disk.
Some important tables were empty with no reason.
I tried to find respectives files for tables with psql command :
 
select relfilenode from pg_class
where relname = '<table>';
 
 relfilenode
-------------
       19597
(1 row)
Then I found this file into /home2/postgres/data/base/18720 directory
$ ls -lh 19597
-rw-------    1 postgres postgres        0 Oct 30  2001 19597
 
First, it's look like empty. Second, the file date was too old for the database.
But, looking into this directory, I found some files with date and size compatibles with my old database (when works !)
 
$ ls -lh 1*
-rw-------    1 postgres postgres   182.0M Mar 11 16:23 108464
-rw-------    1 postgres postgres   204.0M Mar 11 16:23 108487
-rw-------    1 postgres postgres    94.8M Mar 11 16:23 108490
-rw-------    1 postgres postgres   268.6M Mar 11 16:23 118920
-rw-------    1 postgres postgres   206.1M Mar 11 16:23 118953
 
Then, I tried to find them into pg_class table, with pgsql :
 
# select * from pg_class where relfilenode in (108464,108487,108490,118920,118953);
 relname | reltype | relowner | relam | relfilenode | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhaspkey | relhasrules | relhassubclass | relacl
---------+---------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+-------------+----------------+--------
(0 rows)
 
The point is : I think some system table was corrupted. The files are still in the directory, but it seems they are not referenced in the database system ( couldn't find any relationship between pg_class table and them)
Have any way to restore them ??
 
I already restarted the database and executed vacuum command but didn't work.
 
Could someone help me ?
 
Tks

 

Eduardo Naschenweng
Analista de Sistemas - APC

DÍGITRO TECNOLOGIA
E-mail: eduardo.naschenweng@digitro.com.br
Fone:(0xx48) 281-7158
Fax:(0xx48) 281-7299
Site:www.digitro.com.br

____________________________________________________
  IncrediMail - Email has finally evolved - Click Here
Attachment

Re: Space Problems

From
dominique oulmas
Date:
unsubscribe

__________________________________
Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam
http://mail.yahoo.com

Re: Space Problems

From
Tom Lane
Date:
"Eduardo Naschenweng" <eduardo.naschenweng@digitro.com.br> writes:
> Hi, I has a problem with a production database Postgres 7.1.2.

Ouch, that's pretty old (and not even the latest of the 7.1 series).

> The point is : I think some system table was corrupted.

Can you find any pg_class rows with OIDs matching those file names?
I don't think that 7.1 would ever change relfilenode on ordinary
tables, but it does do so for indexes if you REINDEX.

Also, how large is pg_log ($PGDATA/global/1269)?  I'm wondering about
transaction ID wraparound, which was still an unsolved problem in 7.1.

            regards, tom lane