X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 8DD9A9FA5E9 for ; Fri, 2 Jun 2006 04:41:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 36578-06 for ; Fri, 2 Jun 2006 04:41:29 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id 9B6FD9FA111 for ; Fri, 2 Jun 2006 04:41:29 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so384648ugf for ; Fri, 02 Jun 2006 00:41:28 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=FBF8JjscSYoMQrOapFmSfDItMv5H9Rs4bvVcrav0S3ugYXOGTZ1EG4bvF/wAgtO+acb4YsdXE+VsuyBTNtTNSFS47jWkBC71lmYij7agzuuvH+IeN9Ssu6xhfpQ/C9qx4RL7JYld1ZbyqvQyyrGzmfX3WzqojZY+vjqc58pOrQs= Received: by 10.66.250.17 with SMTP id x17mr506806ugh; Fri, 02 Jun 2006 00:41:28 -0700 (PDT) Received: by 10.66.222.12 with HTTP; Fri, 2 Jun 2006 00:41:27 -0700 (PDT) Message-ID: <674d1f8a0606020041t42878fadxd154cb076a27cdaa@mail.gmail.com> Date: Fri, 2 Jun 2006 13:11:27 +0530 From: "Gourish Singbal" To: Joao Subject: Re: [PERFORM] help me problems with pg_clog file Cc: pgsql-performance@postgresql.org, "pgsql-admin@postgresql.org" In-Reply-To: <002b01c6859f$4f6a7930$d815010a@gesin293> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4472_12282173.1149234087932" References: <002b01c6859f$4f6a7930$d815010a@gesin293> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/7 X-Sequence-Number: 21969 ------=_Part_4472_12282173.1149234087932 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Joao, If you had send the Email to pgsql-admin mailing list you would have got a faster answer to ur query.. here is what i managed to do:- 1. I deleted the $ls -lart the pg_clog folder total 756 -rw------- 1 postgres users 262144 2006-04-10 17:16 0001 -rw------- 1 postgres users 262144 2006-04-10 17:16 0000 drwx------ 2 postgres users 4096 2006-04-10 17:16 . -rw------- 1 postgres users 229376 2006-05-31 18:17 0002 drwx------ 10 postgres users 4096 2006-06-02 12:22 .. $ mv 0002 ../ $ ls 0000 0001 $psql regression regression=# select count(1) from accounts; ERROR: could not access status of transaction 2225656 DETAIL: could not open file "pg_clog/0002": No such file or directory regression=# \q This Error came since the 0002 file from the pg_clog folder was missing. Since the logs are missing from pg_clog folder can perfom pg_resetxlogs to reset the logs and bring up the database. $ /usr/local/pgsql/bin/pg_ctl -D /newdisk/postgres/data -l /newdisk/postgres/data_log stop waiting for postmaster to shut down... done postmaster stopped $ /usr/local/pgsql/bin/pg_resetxlog -x 2999800 /newdisk/postgres/data Transaction log reset The Value 2999800 u can get if u see the postgresql output file during startup or using grep "next transaction ID" /newdisk/postgres/data_log than did the following:- /usr/local/pgsql/bin/psql regression regression=# select count(1) from accounts; count --------- 1000001 (1 row) regression=# \q You can get more info from http://unix.business.utah.edu/doc/applications/postgres/postgres-html/app-pgresetxlog.html Hope this gives u some usefull information in solving ur recovery condition. ~gourish On 6/1/06, Joao wrote: > > when i try do \d in psql on a table i get this message! > this happens too when i try to run pg_dump... > > ERROR: could not access status of transaction 4294967295 > DETAIL: could not open file "pg_clog/0FFF": File or directory not found > > > someone could help me?? PleasE! > > > > > -- Best, Gourish Singbal ------=_Part_4472_12282173.1149234087932 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
 
Joao,
 
If you had send the Email to pgsql-admin mailing list you would have got a faster answer to ur query..
 
here is what i managed to do:-
1. I deleted the
$ls -lart the pg_clog folder
total 756
-rw-------   1 postgres users 262144 2006-04-10 17:16 0001
-rw-------   1 postgres users 262144 2006-04-10 17:16 0000
drwx------   2 postgres users   4096 2006-04-10 17:16 .
-rw-------   1 postgres users 229376 2006-05-31 18:17 0002
drwx------  10 postgres users   4096 2006-06-02 12:22 ..
$ mv 0002 ../
$ ls
0000  0001
$psql regression
regression=# select count(1) from accounts;
ERROR:  could not access status of transaction 2225656
DETAIL:  could not open file "pg_clog/0002": No such file or directory
regression=# \q

This Error came since the 0002 file from the pg_clog folder was missing.
Since the logs are missing from pg_clog folder can perfom pg_resetxlogs to reset the logs and bring up the database.
 
$ /usr/local/pgsql/bin/pg_ctl -D /newdisk/postgres/data -l /newdisk/postgres/data_log stop
waiting for postmaster to shut down... done
postmaster stopped
 
$ /usr/local/pgsql/bin/pg_resetxlog -x 2999800 /newdisk/postgres/data
Transaction log reset
 
The Value 2999800 u can get if u see the postgresql output file during startup or using  
grep "next transaction ID" /newdisk/postgres/data_log
 
than did the following:-
/usr/local/pgsql/bin/psql regression

regression=# select count(1) from accounts;
  count
---------
 1000001
(1 row)

regression=# \q

You can get more info from
 
Hope this gives u some usefull information in solving ur recovery condition.
 
~gourish
 
On 6/1/06, Joao <joao.junior@conab.gov.br> wrote:
when i try do \d in psql on a table i get this message!
this happens too when i try to run pg_dump...
 
ERROR:  could not access status of transaction 4294967295
DETAIL:  could not open file "pg_clog/0FFF": File or directory not found
 
 
someone could help me?? PleasE!
 
 

 



--
Best,
Gourish Singbal ------=_Part_4472_12282173.1149234087932--