From eduardo.naschenweng@digitro.com.br Tue Mar 16 20:32:01 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id F0258D1D2A0 for ; Tue, 16 Mar 2004 20:32:01 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 15633-10 for ; Tue, 16 Mar 2004 16:31:55 -0400 (AST) Received: from mail.digitro.com.br (unknown [200.247.100.135]) by svr1.postgresql.org (Postfix) with ESMTP id CF1A2D1CCD6 for ; Tue, 16 Mar 2004 16:31:51 -0400 (AST) Received: from serverdgt.daf.digitro.com.br (ias.digitro.com.br [200.247.100.134]) by mail.digitro.com.br (8.12.5/linuxconf) with ESMTP id i2GKVpEl009401 for ; Tue, 16 Mar 2004 17:31:51 -0300 Received: from dgtat ([192.168.160.169]) by serverdgt.daf.digitro.com.br (8.12.5/linuxconf) with SMTP id i2GKVEJP030886 for ; Tue, 16 Mar 2004 17:31:14 -0300 MIME-Version: 1.0 Message-Id: <405764A7.000003.00108@dgtat> Date: Tue, 16 Mar 2004 17:33:43 -0300 (Hora oficial do Brasil) Content-Type: Multipart/related; type="multipart/alternative"; boundary="------------Boundary-00=_7SROG6G0000000000000" X-Mailer: IncrediMail 2001 (1750690) From: "Eduardo Naschenweng" X-FID: FLAVOR00-NONE-0000-0000-000000000000 X-FVER: 2.0 X-CNT: ; X-Priority: 3 To: Subject: Space Problems X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.8 tagged_above=0.0 required=5.0 tests=BAYES_01, CLICK_BELOW, DNS_FROM_RFCI_DSN, HTML_FONTCOLOR_BLUE, HTML_FONTCOLOR_UNSAFE, HTML_LINK_CLICK_HERE, HTML_MESSAGE, RCVD_IN_NJABL, RCVD_IN_NJABL_SPAM, RCVD_IN_SORBS, RCVD_IN_SORBS_HTTP X-Spam-Level: * X-Archive-Number: 200403/229 X-Sequence-Number: 12798 --------------Boundary-00=_7SROG6G0000000000000 Content-Type: Multipart/Alternative; boundary="------------Boundary-00=_7SROBHK0000000000000" --------------Boundary-00=_7SROBHK0000000000000 Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I has a problem with a production database Postgres 7.1.2.=0D Happens when finished free space disk.=0D Some important tables were empty with no reason.=0D I tried to find respectives files for tables with psql command :=0D =0D select relfilenode from pg_class=0D where relname =3D '';=0D =0D relfilenode=0D -------------=0D 19597=0D (1 row)=0D =0D Then I found this file into /home2/postgres/data/base/18720 directory=0D $ ls -lh 19597=0D -rw------- 1 postgres postgres 0 Oct 30 2001 19597=0D =0D First, it's look like empty. Second, the file date was too old for the database.=0D But, looking into this directory, I found some files with date and size compatibles with my old database (when works !)=0D =0D $ ls -lh 1*=0D -rw------- 1 postgres postgres 182.0M Mar 11 16:23 108464=0D -rw------- 1 postgres postgres 204.0M Mar 11 16:23 108487=0D -rw------- 1 postgres postgres 94.8M Mar 11 16:23 108490=0D -rw------- 1 postgres postgres 268.6M Mar 11 16:23 118920=0D -rw------- 1 postgres postgres 206.1M Mar 11 16:23 118953=0D =0D Then, I tried to find them into pg_class table, with pgsql :=0D =0D # select * from pg_class where relfilenode in (108464,108487,108490,118920 118953);=0D =0D relname | reltype | relowner | relam | relfilenode | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhaspkey | relhasrules | relhassubclass | relacl=0D ---------+---------+----------+-------+-------------+----------+-----------= +- -------------+---------------+-------------+-------------+---------+-------= -- +-----------+-------------+----------+----------+---------+------------+---= -- -------+----------------+--------=0D (0 rows)=0D =0D =0D 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)=0D Have any way to restore them ??=0D =0D I already restarted the database and executed vacuum command but didn't work =0D =0D Could someone help me ?=0D =0D Tks=0D =0D =0D Eduardo Naschenweng=0D Analista de Sistemas - APC =0D D=CDGITRO TECNOLOGIA =0D E-mail: eduardo.naschenweng@digitro.com.br =0D Fone:(0xx48) 281-7158 =0D Fax:(0xx48) 281-7299 =0D Site:www.digitro.com.br=20 --------------Boundary-00=_7SROBHK0000000000000 Content-Type: Text/HTML; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =0D
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= =20 :
 
select relfilenode from pg_class
where relname =3D=20 '<table>';
 
 relfilenode
-------------
    &n= bsp; =20 19597
(1 row)
Then I found this file into /home2/postgres/data/base/18720=20 directory
$ ls -lh 19597
-rw-------    1 postgre= s=20 postgres        0 Oct 30  200= 1=20 19597
 
First, it's look like empty. Second, the file date was too old f= or=20 the database.
But, looking into this directory, I found some files with date a= nd=20 size compatibles with my old database (when works !)
 
$ ls -lh 1*
-rw-------    1 postgres=20 postgres   182.0M Mar 11 16:23=20 108464
-rw-------    1 postgres postgres  = ;=20 204.0M Mar 11 16:23 108487
-rw-------    1 postgres= =20 postgres    94.8M Mar 11 16:23=20 108490
-rw-------    1 postgres postgres  = ;=20 268.6M Mar 11 16:23 118920
-rw-------    1 postgres= =20 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=20 (108464,108487,108490,118920,118953);
 relname | reltype | relowner | relam | relfilenode | relpa= ges |=20 reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared= |=20 relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys |= =20 relrefs | relhaspkey | relhasrules | relhassubclass |=20 relacl
---------+---------+----------+-------+-------------+------= ----+-----------+---------------+---------------+-------------+------------= -+---------+----------+-----------+-------------+----------+----------+----= -----+------------+-------------+----------------+--------
(0=20 rows)
 
The point is : I think some system table was corrupted. The file= s are=20 still in the directory, but it seems they are not reference= d in=20 the database system ( couldn't find any relationship between pg_class= =20 table and them)
Have any way to restore them ??
 
I already restarted the database and executed vacuum command but= =20 didn't work.
 
Could someone help me ?
 
Tks

 

= Eduardo Naschenweng
Analista de Sistemas - APC=20

= D=CDGITRO TECNOLOGIA
E-mail: eduardo.naschenwen= g@digitro.com.br=20
Fone:(0xx48) 281-7158
Fax:(0xx48) 281-7299
Site:www.digitro.com.br=20

____________________________________________________
  IncrediMail - Email has finally evolved= -=20
Click=20 Here
--------------Boundary-00=_7SROBHK0000000000000-- --------------Boundary-00=_7SROG6G0000000000000 Content-Type: image/gif Content-Transfer-Encoding: base64 Content-ID: R0lGODlhFAAPALMIAP9gAM9gAM8vAM9gL/+QL5AvAGAvAP9gL////wAAAAAA AAAAAAAAAAAAAAAAAAAAACH/C05FVFNDQVBFMi4wAwEAAAAh+QQJFAAIACwA AAAAFAAPAAAEVRDJSaudJuudrxlEKI6BURlCUYyjKpgYAKSgOBSCDEuGDKgr AtC3Q/R+hkPJEDgYCjpKr5A8WK9OaPFZwHoPqm3366VKyeRtE30tVVRscMHD qV/u+AgAIfkEBWQACAAsAAAAABQADwAABBIQyUmrvTjrzbv/YCiOZGmeaAQA IfkECRQACAAsAgABABAADQAABEoQIUOrpXIOwrsPxiQUheeRAgUA49YNhbCq K1kS9grQhXGAhsDBUJgZAL2Dcqkk7ogFpvRAokSn0p4PO6UIuUsQggSmFjKX dAgRAQAh+QQFCgAIACwAAAAAFAAPAAAEEhDJSau9OOvNu/9gKI5kaZ5oBAAh +QQJFAAIACwCAAEAEAANAAAEShAhQ6ulcg7Cuw/GJBSF55ECBQDj1g2FsKor WRL2CtCFcYCGwMFQmBkAvYNyqSTuiAWm9ECiRKfSng87pQi5SxCCBKYWMpd0 CBEBACH5BAVkAAgALAAAAAAUAA8AAAQSEMlJq7046827/2AojmRpnmgEADs= --------------Boundary-00=_7SROG6G0000000000000-- From doulmas@yahoo.com Tue Mar 16 20:58:42 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id CE410D1E8CF for ; Tue, 16 Mar 2004 20:58:42 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 31407-06 for ; Tue, 16 Mar 2004 16:58:37 -0400 (AST) Received: from web21327.mail.yahoo.com (web21327.mail.yahoo.com [216.136.175.216]) by svr1.postgresql.org (Postfix) with SMTP id 3969AD1E31E for ; Tue, 16 Mar 2004 16:58:34 -0400 (AST) Message-ID: <20040316205835.59296.qmail@web21327.mail.yahoo.com> Received: from [12.109.159.53] by web21327.mail.yahoo.com via HTTP; Tue, 16 Mar 2004 12:58:35 PST Date: Tue, 16 Mar 2004 12:58:35 -0800 (PST) From: dominique oulmas Subject: Re: Space Problems To: pgsql-admin@postgresql.org In-Reply-To: <405764A7.000003.00108@dgtat> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/230 X-Sequence-Number: 12799 unsubscribe __________________________________ Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam http://mail.yahoo.com From tgl@sss.pgh.pa.us Tue Mar 16 21:00:33 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6B476D1D26C for ; Tue, 16 Mar 2004 21:00:33 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 38426-01 for ; Tue, 16 Mar 2004 17:00:27 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 9B5DFD1E28A for ; Tue, 16 Mar 2004 17:00:23 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i2GL0Jjg027482; Tue, 16 Mar 2004 16:00:19 -0500 (EST) To: "Eduardo Naschenweng" Cc: pgsql-admin@postgresql.org Subject: Re: Space Problems In-reply-to: <405764A7.000003.00108@dgtat> References: <405764A7.000003.00108@dgtat> Comments: In-reply-to "Eduardo Naschenweng" message dated "Tue, 16 Mar 2004 17:33:43 -0300" Date: Tue, 16 Mar 2004 16:00:19 -0500 Message-ID: <27481.1079470819@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/231 X-Sequence-Number: 12800 "Eduardo Naschenweng" 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