pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Eduardo Naschenweng <eduardo.naschenweng@digitro.com.br>
To: pgsql-admin@postgresql.org
Subject: Space Problems
Date: Tue, 16 Mar 2004 17:33:43 -0300 (Hora oficial do Brasil)
Message-ID: <405764A7.000003.00108@dgtat> (raw)

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 

view thread (3+ messages)  latest in thread

Message-ID: <405764A7.000003.00108@dgtat>
Permalink:  ../405764A7.000003.00108@dgtat/
Also on:    postgresql.org/message-id/405764A7.000003.00108@dgtat

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: eduardo.naschenweng@digitro.com.br
  Subject: Re: Space Problems
  In-Reply-To: <405764A7.000003.00108@dgtat>
  References: <405764A7.000003.00108@dgtat>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox