Thread: Help using pgfsck
Greetings,
I've been trying to use pgfsck since my PostgreSQL database was raising a
"Memory exhausted in
AllocSetAlloc" but it seems pgfsck is reading my datetimes in a wrong way,
so I can't export data to recover my database.
I'd executed initdb with pt_BR (Brazilian's Portuguese language). Is it
possible to pgfsck problem be related with localization settings?
Here is my "log" :
bxs=#CREATE TABLE teste(a datetime);
bxs=#
bxs=# insert into teste values ( now());
INSERT 22184 1
...
bxs=# insert into teste values ( now());
INSERT 22188 1
bxs=# select * from teste;
a
------------------------
2003-04-14 12:56:35-03
2003-04-14 12:56:36-03
2003-04-14 12:56:38-03
2003-04-14 12:56:39-03
2003-04-14 12:56:40-03
2003-04-14 12:56:50-03
2003-04-14 12:56:51-03
2003-04-14 12:56:53-03
2003-04-14 12:56:56-03
2003-04-14 12:56:58-03
(10 rows)
bxs=# \q
[postgres@dgtao ddd]$ ./pgfsck -a -d -D /home/postgres/data bxs teste
-- Detected database format 7.1
-- Scanning table teste
-- Table pg_class(1259):Page 0:Tuple 64: Unknown type _aclitem (1034)
-- Table pg_class(1259):Page 1:Tuple 0: Unknown type _aclitem (1034)
-- Table pg_class(1259):Page 1:Tuple 1: Unknown type _aclitem (1034)
...
-- Table pg_class(1259):Page 3:Tuple 20: Unknown type _aclitem (1034)
-- Table pg_class(1259):Page 3:Tuple 21: Unknown type _aclitem (1034)
-- Table pg_class(1259):Page 3:Tuple 22: Unknown type _aclitem (1034)
-- Table teste(22169):Page 0:Tuple 0: Unknown type timestamp (1184)
-- Table teste(22169):Page 0:Tuple 1: Unknown type timestamp (1184)
-- Table teste(22169):Page 0:Tuple 2: Unknown type timestamp (1184)
-- Table teste(22169):Page 0:Tuple 3: Unknown type timestamp (1184)
-- Table teste(22169):Page 0:Tuple 4: Unknown type timestamp (1184)
-- Table teste(22169):Page 0:Tuple 5: Unknown type timestamp (1184)
-- Table teste(22169):Page 0:Tuple 6: Unknown type timestamp (1184)
-- Table teste(22169):Page 0:Tuple 7: Unknown type timestamp (1184)
-- Table teste(22169):Page 0:Tuple 8: Unknown type timestamp (1184)
-- Table teste(22169):Page 0:Tuple 9: Unknown type timestamp (1184)
insert into teste (a) values ('ÌZ¶~A'); --
page=0,tuple=0,oid=22179,xmin=1658,xmax=0,cmin=0,cmax=0
insert into teste (a) values ('ÐZ¶~A'); --
page=0,tuple=1,oid=22180,xmin=1659,xmax=0,cmin=0,cmax=0
insert into teste (a) values ('ØZ¶~A'); --
page=0,tuple=2,oid=22181,xmin=1660,xmax=0,cmin=0,cmax=0
insert into teste (a) values ('ÜZ¶~A'); --
page=0,tuple=3,oid=22182,xmin=1661,xmax=0,cmin=0,cmax=0
insert into teste (a) values ('àZ¶~A'); --
page=0,tuple=4,oid=22183,xmin=1662,xmax=0,cmin=0,cmax=0
insert into teste (a) values ([¶~A'); --
page=0,tuple=5,oid=22184,xmin=1664,xmax=0,cmin=0,cmax=0
insert into teste (a) values ('[¶~A'); --
page=0,tuple=6,oid=22185,xmin=1665,xmax=0,cmin=0,cmax=0
insert into teste (a) values ('[¶~A'); --
page=0,tuple=7,oid=22186,xmin=1666,xmax=0,cmin=0,cmax=0
insert into teste (a) values (' [¶~A'); --
page=0,tuple=8,oid=22187,xmin=1667,xmax=0,cmin=0,cmax=0
insert into teste (a) values ('([¶~A'); --
page=0,tuple=9,oid=22188,xmin=1668,xmax=0,cmin=0,cmax=0
After I got this output, I'd reconfigured env with new values and ran pgfsck
again :
[postgres@dgtao ddd]$ LANG=pt_BR
[postgres@dgtao ddd]$ LC_ALL=pt_BR
[postgres@dgtao ddd]$ LC_CTYPE=pt_BR
[postgres@dgtao ddd]$ LESSCHARSET=latin1
[postgres@dgtao ddd]$ export LANG LC_ALL LC_CTYPE
The new test brought the same kind of values. Am I doing somethig wrong?
Best regards,
----------------------------------------------------------------------------
----
José Vilson de Mello de Farias
Software Engineer
Dígitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: vilson.farias@digitro.com.br
Tel.: +55 48 281 7158
ICQ 11866179
On Mon, Apr 14, 2003 at 06:34:33PM -0300, Vilson farias wrote: > Greetings, > > I've been trying to use pgfsck since my PostgreSQL database was raising a > "Memory exhausted in > AllocSetAlloc" but it seems pgfsck is reading my datetimes in a wrong way, > so I can't export data to recover my database. Yeah, sorry about that. I couldn't work out how datetimes and timestamps were stored in the file so I never got round to adding them. As you can see they look about 5 bytes in width. If you can work it out there is a section near the front of the program that does the data type conversions. Add it there and it should dump properly. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organized violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
Attachment
Mr. Oosterhout, it's been very hard to deal with this database error. Since my knowledge in perl is null I think it's easier to edit database files manually. Could you or someone here in pgsql-general tell me where can I find more information about database files and how to edit? Pgfsck gives me valuable information, but I don't know how to access and change pages it reports me. bash$ ./pgfsck -D $(pwd) bxs cham_chamada > r bash$ cat r -- Detected database format 7.1 -- Scanning table cham_chamada -- Table pg_class(1259):Page 0:Tuple 64: Unknown type _aclitem (1034) -- Table pg_class(1259):Page 1:Tuple 0: Unknown type _aclitem (1034) ... -- Table cham_chamada(19597):Page 876:Tuple 25: Decoding tuple runs off end: 4168 > 104 >>01 00 00 00 07 00 00 00 09 00 00 00 00 00 00 90 8E 06 95 41 00 00 00 00 0A 00 00 00 0C 00 00 00 33 32 31 34 38 33 35 35 0 0 00 00 C4 8E 06 95 41 03 00 00 00 00 00 00 00 10 00 00 00 30 32 33 31 38 33 34 31 31 35 34 30 05 00 00 00 31 00 00 00 05 0 0 00 00 30 00 00 00 00 00 00 00 3D 00 00 00 00 00 00 00 02 01 00 00 00 00 00 00 07 00 00 00 -- Table cham_chamada(19597):Page 968:Tuple 30: Tuple incorrect length (parsed data=116,length=112) -- Table cham_chamada(19597):Page 1862:Tuple 31: Decoding tuple runs off end: 4176 > 104 Thanks in advance. >>Yeah, sorry about that. I couldn't work out how datetimes and timestamps >>were stored in the file so I never got round to adding them. As you can see >>they look about 5 bytes in width. If you can work it out there is a section >>near the front of the program that does the data type conversions. Add it >>there and it should dump properly. --
learning=> CREATE TABLE dates (a timestamp);
CREATE TABLE
learning=> INSERT INTO dates VALUES (now());
INSERT 17647 1
learning=> \d dates
Table "public.dates"
Column | Type | Modifiers
--------+-----------------------------+-----------
a | timestamp without time zone |
learning=> SELECT * FROM dates ;
a
----------------------------
2003-04-16 00:28:38.787615
(1 row)
maybe this is, what you are looking for...
well, this doesn't solve your pgfsck problem, but maybe avoids it.
by the way, I couldn't find the type "datetime" as you used it on your
CREATE TABLE..
http://www.postgresql.com/docs/view.php?version=7.3&idoc=0&file=datatype.html
Vilson farias wrote:
> Greetings,
>
> I've been trying to use pgfsck since my PostgreSQL database was raising a
> "Memory exhausted in
> AllocSetAlloc" but it seems pgfsck is reading my datetimes in a wrong way,
> so I can't export data to recover my database.
>
> I'd executed initdb with pt_BR (Brazilian's Portuguese language). Is it
> possible to pgfsck problem be related with localization settings?
>
> Here is my "log" :
>
> bxs=#CREATE TABLE teste(a datetime);
> bxs=#
> bxs=# insert into teste values ( now());
> INSERT 22184 1
> ...
> bxs=# insert into teste values ( now());
> INSERT 22188 1
> bxs=# select * from teste;
> a
> ------------------------
> 2003-04-14 12:56:35-03
> 2003-04-14 12:56:36-03
> 2003-04-14 12:56:38-03
> 2003-04-14 12:56:39-03
> 2003-04-14 12:56:40-03
> 2003-04-14 12:56:50-03
> 2003-04-14 12:56:51-03
> 2003-04-14 12:56:53-03
> 2003-04-14 12:56:56-03
> 2003-04-14 12:56:58-03
> (10 rows)
>
> bxs=# \q
> [postgres@dgtao ddd]$ ./pgfsck -a -d -D /home/postgres/data bxs teste
> -- Detected database format 7.1
> -- Scanning table teste
> -- Table pg_class(1259):Page 0:Tuple 64: Unknown type _aclitem (1034)
> -- Table pg_class(1259):Page 1:Tuple 0: Unknown type _aclitem (1034)
> -- Table pg_class(1259):Page 1:Tuple 1: Unknown type _aclitem (1034)
> ...
> -- Table pg_class(1259):Page 3:Tuple 20: Unknown type _aclitem (1034)
> -- Table pg_class(1259):Page 3:Tuple 21: Unknown type _aclitem (1034)
> -- Table pg_class(1259):Page 3:Tuple 22: Unknown type _aclitem (1034)
> -- Table teste(22169):Page 0:Tuple 0: Unknown type timestamp (1184)
> -- Table teste(22169):Page 0:Tuple 1: Unknown type timestamp (1184)
> -- Table teste(22169):Page 0:Tuple 2: Unknown type timestamp (1184)
> -- Table teste(22169):Page 0:Tuple 3: Unknown type timestamp (1184)
> -- Table teste(22169):Page 0:Tuple 4: Unknown type timestamp (1184)
> -- Table teste(22169):Page 0:Tuple 5: Unknown type timestamp (1184)
> -- Table teste(22169):Page 0:Tuple 6: Unknown type timestamp (1184)
> -- Table teste(22169):Page 0:Tuple 7: Unknown type timestamp (1184)
> -- Table teste(22169):Page 0:Tuple 8: Unknown type timestamp (1184)
> -- Table teste(22169):Page 0:Tuple 9: Unknown type timestamp (1184)
> insert into teste (a) values ('ÌZ¶~A'); --
> page=0,tuple=0,oid=22179,xmin=1658,xmax=0,cmin=0,cmax=0
> insert into teste (a) values ('ÐZ¶~A'); --
> page=0,tuple=1,oid=22180,xmin=1659,xmax=0,cmin=0,cmax=0
> insert into teste (a) values ('ØZ¶~A'); --
> page=0,tuple=2,oid=22181,xmin=1660,xmax=0,cmin=0,cmax=0
> insert into teste (a) values ('ÜZ¶~A'); --
> page=0,tuple=3,oid=22182,xmin=1661,xmax=0,cmin=0,cmax=0
> insert into teste (a) values ('àZ¶~A'); --
> page=0,tuple=4,oid=22183,xmin=1662,xmax=0,cmin=0,cmax=0
> insert into teste (a) values ([¶~A'); --
> page=0,tuple=5,oid=22184,xmin=1664,xmax=0,cmin=0,cmax=0
> insert into teste (a) values ('[¶~A'); --
> page=0,tuple=6,oid=22185,xmin=1665,xmax=0,cmin=0,cmax=0
> insert into teste (a) values ('[¶~A'); --
> page=0,tuple=7,oid=22186,xmin=1666,xmax=0,cmin=0,cmax=0
> insert into teste (a) values (' [¶~A'); --
> page=0,tuple=8,oid=22187,xmin=1667,xmax=0,cmin=0,cmax=0
> insert into teste (a) values ('([¶~A'); --
> page=0,tuple=9,oid=22188,xmin=1668,xmax=0,cmin=0,cmax=0
>
> After I got this output, I'd reconfigured env with new values and ran pgfsck
> again :
> [postgres@dgtao ddd]$ LANG=pt_BR
> [postgres@dgtao ddd]$ LC_ALL=pt_BR
> [postgres@dgtao ddd]$ LC_CTYPE=pt_BR
> [postgres@dgtao ddd]$ LESSCHARSET=latin1
> [postgres@dgtao ddd]$ export LANG LC_ALL LC_CTYPE
>
> The new test brought the same kind of values. Am I doing somethig wrong?
>
>
> Best regards,
>
> ----------------------------------------------------------------------------
> ----
> José Vilson de Mello de Farias
> Software Engineer
>
> Dígitro Tecnologia Ltda - www.digitro.com.br
> APC - Customer Oriented Applications
> E-mail: vilson.farias@digitro.com.br
> Tel.: +55 48 281 7158
> ICQ 11866179
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
On Tue, Apr 15, 2003 at 03:34:03PM -0300, Vilson farias wrote:
> Mr. Oosterhout,
>
> it's been very hard to deal with this database error. Since my knowledge
> in perl is null I think it's easier to edit database files manually.
>
> Could you or someone here in pgsql-general tell me where can I find more
> information about database files and how to edit? Pgfsck gives me valuable
> information, but I don't know how to access and change pages it reports me.
Editing the database files directly doesn't seem to me to be a real good
idea. Unfortunatly I don't know as much about fixing the problems as I do
about finding them.
My guess is (and I would like an actual postgres developer to confirm this)
is that the numbers below give you the ctid of the tuples to delete. So,
executing (please have a backup of the entire data directory before
attempting this):
delete from cham_chamada where ctid = '(876,25)';
delete from cham_chamada where ctid = '(968,30)';
delete from cham_chamada where ctid = '(1862,31)';
^page ^tuple
*might* fix it. At least to the stage where pg_dump will work again.
Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington
Attachment
Greetings, I found a way to fix this kind of database corruption. It seems only invalid tuples cause database shutdown. I just did a lot of selects until I found all of these tuples(just 1 in this case). After deletion of invalid records my database is running again :). Just in case, I'm dumping/restoring the entire database to make sure new one doesn't has any problems. I would like to thanks people that helped me, especially Mr. Oosterhout who gave me a lot of valuable information. Mr. Alex B, datetime type really existis (at least here in my PostgreSQL 7.1.2). Maybe it's an alias for timestamp type, but I'm not sure. Best regards. ----- Original Message ----- From: "Martijn van Oosterhout" <kleptog@svana.org> To: "Vilson farias" <vilson.farias@digitro.com.br> Cc: <pgsql-general@postgresql.org>; <ricardo.klava@digitro.com.br> Sent: Tuesday, April 15, 2003 8:20 PM Subject: Re: [GENERAL] Help using pgfsck