missing pg_clog files ?
Patrick Welche
glibc *last year*. I am seeing something similar *now* with a server
PostgreSQL 7.4beta1 on i386-unknown-netbsdelf1.6X, compiled by GCC 2.95.3
accessed by a similar client and a client
PostgreSQL 7.4devel on i686-pc-linux-gnu, compiled by GCC 2.95.4
The following works:
select * from olddata02_03vac offset 2573718 limit 1;
however
select * from olddata02_03vac offset 2573719 limit 1;
ERROR: could not access status of transaction 1664158221
DETAIL: open of file "/usr/local/pgsql/data/pg_clog/0633" failed: No such file or directory
and
# ls -l pg_clog
total 32
-rw------- 1 postgres postgres 16384 Sep 22 13:12 0000
#
Is it true that the problem was with the server, so the fact that a glibc
client was connecting and possibly doing a vacuum is irrelevant?
What can I do now to fix it? Known problem with beta1 which is now old?
Cheers,
Patrick
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Tom Lane
> select * from olddata02_03vac offset 2573719 limit 1;
> ERROR: could not access status of transaction 1664158221
> DETAIL: open of file "/usr/local/pgsql/data/pg_clog/0633" failed: No such file or directory
> # ls -l pg_clog
> total 32
> -rw------- 1 postgres postgres 16384 Sep 22 13:12 0000
What you have here is a corrupted tuple (viz, a silly transaction number).
It would be useful to look at the page containing the tuple to see if
any pattern can be detected in the corruption. To do this, get the
ctid of the prior tuple:
select ctid from olddata02_03vac offset 2573718 limit 1;
This will give you a result "(blocknumber,tuplenumber)". The bogus
tuple is probably on the same page, though possibly further along.
Next find a dump tool --- I usually use Red Hat's pg_filedump:
http://sources.redhat.com/rhdb/tools.html
Dump out the page(s) in question and send them along.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Patrick Welche
> Patrick Welche <pr...@newn.cam.ac.uk> writes:
> > select * from olddata02_03vac offset 2573719 limit 1;
> > ERROR: could not access status of transaction 1664158221
> > DETAIL: open of file "/usr/local/pgsql/data/pg_clog/0633" failed: No such file or directory
>
> > # ls -l pg_clog
> > total 32
> > -rw------- 1 postgres postgres 16384 Sep 22 13:12 0000
>
> What you have here is a corrupted tuple (viz, a silly transaction number).
>
> It would be useful to look at the page containing the tuple to see if
> any pattern can be detected in the corruption. To do this, get the
> ctid of the prior tuple:
> select ctid from olddata02_03vac offset 2573718 limit 1;
(71716,15)
> This will give you a result "(blocknumber,tuplenumber)". The bogus
> tuple is probably on the same page, though possibly further along.
> Next find a dump tool --- I usually use Red Hat's pg_filedump:
> http://sources.redhat.com/rhdb/tools.html
> Dump out the page(s) in question and send them along.
I hope I guessed the right syntax...
% pg_filedump -R 71716 data/base/17148/283342
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 2.0-Alpha
*
* File: data/base/17148/283342
* Options used: -R 71716
*
* Dump created on: Mon Sep 22 16:21:29 2003
*******************************************************************
Block 71716 ********************************************************
<Header> -----
Block Offset: 0x23048000 Offsets: Lower 176 (0x00b0)
Block: Size 8192 Version 1 Upper 236 (0x00ec)
LSN: logid 1 recoff 0xd308022c Special 8192 (0x2000)
Items: 39 Free Space: 60
Length (including item array): 180
<Data> ------
Item 1 -- Length: 204 Offset: 7988 (0x1f34) Flags: USED
Item 2 -- Length: 204 Offset: 7784 (0x1e68) Flags: USED
Item 3 -- Length: 204 Offset: 7580 (0x1d9c) Flags: USED
Item 4 -- Length: 204 Offset: 7376 (0x1cd0) Flags: USED
Item 5 -- Length: 204 Offset: 7172 (0x1c04) Flags: USED
Item 6 -- Length: 204 Offset: 6968 (0x1b38) Flags: USED
Item 7 -- Length: 204 Offset: 6764 (0x1a6c) Flags: USED
Item 8 -- Length: 204 Offset: 6560 (0x19a0) Flags: USED
Item 9 -- Length: 204 Offset: 6356 (0x18d4) Flags: USED
Item 10 -- Length: 204 Offset: 6152 (0x1808) Flags: USED
Item 11 -- Length: 204 Offset: 5948 (0x173c) Flags: USED
Item 12 -- Length: 204 Offset: 5744 (0x1670) Flags: USED
Item 13 -- Length: 204 Offset: 5540 (0x15a4) Flags: USED
Item 14 -- Length: 204 Offset: 5336 (0x14d8) Flags: USED
Item 15 -- Length: 204 Offset: 5132 (0x140c) Flags: USED
Item 16 -- Length: 204 Offset: 4928 (0x1340) Flags: USED
Item 17 -- Length: 204 Offset: 4724 (0x1274) Flags: USED
Item 18 -- Length: 204 Offset: 4520 (0x11a8) Flags: USED
Item 19 -- Length: 204 Offset: 4316 (0x10dc) Flags: USED
Item 20 -- Length: 204 Offset: 4112 (0x1010) Flags: USED
Item 21 -- Length: 204 Offset: 3908 (0x0f44) Flags: USED
Item 22 -- Length: 204 Offset: 3704 (0x0e78) Flags: USED
Item 23 -- Length: 204 Offset: 3500 (0x0dac) Flags: USED
Item 24 -- Length: 204 Offset: 3296 (0x0ce0) Flags: USED
Item 25 -- Length: 204 Offset: 3092 (0x0c14) Flags: USED
Item 26 -- Length: 204 Offset: 2888 (0x0b48) Flags: USED
Item 27 -- Length: 204 Offset: 2684 (0x0a7c) Flags: USED
Item 28 -- Length: 204 Offset: 2480 (0x09b0) Flags: USED
Item 29 -- Length: 204 Offset: 2276 (0x08e4) Flags: USED
Item 30 -- Length: 204 Offset: 2072 (0x0818) Flags: USED
Item 31 -- Length: 204 Offset: 1868 (0x074c) Flags: USED
Item 32 -- Length: 204 Offset: 1664 (0x0680) Flags: USED
Item 33 -- Length: 204 Offset: 1460 (0x05b4) Flags: USED
Item 34 -- Length: 204 Offset: 1256 (0x04e8) Flags: USED
Item 35 -- Length: 204 Offset: 1052 (0x041c) Flags: USED
Item 36 -- Length: 204 Offset: 848 (0x0350) Flags: USED
Item 37 -- Length: 204 Offset: 644 (0x0284) Flags: USED
Item 38 -- Length: 204 Offset: 440 (0x01b8) Flags: USED
Item 39 -- Length: 204 Offset: 236 (0x00ec) Flags: USED
*** End of Requested Range Encountered. Last Block Read: 71716 ***
Cheers,
Patrick
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)
Tom Lane
> I hope I guessed the right syntax...
> % pg_filedump -R 71716 data/base/17148/283342
Yes, but this doesn't give all the available info. Add -i and -f
options. A plain -d dump might be interesting too.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
Patrick Welche
> Patrick Welche <pr...@newn.cam.ac.uk> writes:
> > I hope I guessed the right syntax...
> > % pg_filedump -R 71716 data/base/17148/283342
>
> Yes, but this doesn't give all the available info. Add -i and -f
> options. A plain -d dump might be interesting too.
Indeed, the plain -d dump says that I have a chunk of /var/mail/prlw1
in 1000-13ff. No wonder postgres complained!
Highlight:
0fe0: 06000000 00000000 00000000 00000000 ................
0ff0: 01000000 3e000000 00000000 00000000 ....>...........
1000: 52657475 726e2d70 6174683a 203c7072 Return-path: <pr
1010: 6c773140 6e65776e 2e63616d 2e61632e l...@newn.cam.ac.
...
13e0: 38323020 20202020 37313139 38202020 820 71198
13f0: 20323425 20202020 32303637 20202032 24% 2067 2
1400: 3e000000 00000000 03000000 b6090000 >...........ś...
1410: 00000000 00000000 01002418 0f001a00 ..........$.....
Would you be interested in the full dump anyway? It seems this is trashed
and I need to bring out the backups, right? Next is speculation as to how?
I read a very large mail file with mutt which I think uses mmap. It still
begs the question how did that end up in the database.. Worth reloading into
same database server, or upgrade to current cvs?
NetBSD-1.6ZC/i386 with 2Gb memory.
Thanks for the help!
Cheers,
Patrick
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Tom Lane
> Indeed, the plain -d dump says that I have a chunk of /var/mail/prlw1
> in 1000-13ff. No wonder postgres complained!
Yipes. We have seen this sort of thing once or twice in the past.
I don't know whether you are looking at a disk drive fault (dropping
the right data onto the wrong sector) or a kernel fault (getting
confused about which buffer holds which file block), but I believe
it's one or the other. It's hard to see how anything at the application
level could have gotten those two files switched. You might look to see
if there are any updates available for your kernel.
As for recovery, you probably want to drop and reload at least that one
table. Whether it's worth a complete reload is your call.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Alvaro Herrera
> On Mon, Sep 22, 2003 at 11:33:30AM -0400, Tom Lane wrote:
> > Patrick Welche <pr...@newn.cam.ac.uk> writes:
> > > I hope I guessed the right syntax...
> > > % pg_filedump -R 71716 data/base/17148/283342
> >
> > Yes, but this doesn't give all the available info. Add -i and -f
> > options. A plain -d dump might be interesting too.
>
> Indeed, the plain -d dump says that I have a chunk of /var/mail/prlw1
> in 1000-13ff. No wonder postgres complained!
For the record, what filesystem is this on? Is it ReiserFS by any
chance?
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Aprende a avergonzarte mas ante ti que ante los demas" (Democrito)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Patrick Welche
> On Mon, Sep 22, 2003 at 05:03:28PM +0100, Patrick Welche wrote:
> > On Mon, Sep 22, 2003 at 11:33:30AM -0400, Tom Lane wrote:
> > > Patrick Welche <pr...@newn.cam.ac.uk> writes:
> > > > I hope I guessed the right syntax...
> > > > % pg_filedump -R 71716 data/base/17148/283342
> > >
> > > Yes, but this doesn't give all the available info. Add -i and -f
> > > options. A plain -d dump might be interesting too.
> >
> > Indeed, the plain -d dump says that I have a chunk of /var/mail/prlw1
> > in 1000-13ff. No wonder postgres complained!
>
> For the record, what filesystem is this on? Is it ReiserFS by any
> chance?
Nope, ffs with soft dependencies, on a 1 month old IDE drive (read not
yet known good...) Hmm maybe I'd better ask over on the NetBSD list,
though I think my kernel is from just before ide rototill which was actually
on atapi rather than straight ide AFAICT, and certainly before gcc 3.3.1,
so the duff hardware scenario seems best..
Cheers,
Patrick
---------------------------(end of broadcast)---------------------------
Bruce Momjian
> Patrick Welche <pr...@newn.cam.ac.uk> writes:
> > Indeed, the plain -d dump says that I have a chunk of /var/mail/prlw1
> > in 1000-13ff. No wonder postgres complained!
>
> Yipes. We have seen this sort of thing once or twice in the past.
> I don't know whether you are looking at a disk drive fault (dropping
> the right data onto the wrong sector) or a kernel fault (getting
> confused about which buffer holds which file block), but I believe
> it's one or the other. It's hard to see how anything at the application
> level could have gotten those two files switched. You might look to see
> if there are any updates available for your kernel.
>
> As for recovery, you probably want to drop and reload at least that one
> table. Whether it's worth a complete reload is your call.
Or just delete the row with that tid and see if you can access the other
data. You might be able to get away with just restoring that one row
from backup.
--
Bruce Momjian | http://candle.pha.pa.us
pg...@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073