Re: Unable to dump database using pg_dump - Mailing list pgsql-general

I'm comfortable enough with it that I can edit it if I can find exactly
what to edit.  Whats the best way to edit the file?  I've opened it using:

vim -b 1260

Here is what is at the end of the block in the file.  If you could point
me in the right direction, I'd appreciate it.


@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^B^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^E^@^H^@^C)^X?madisoncounty^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@

^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@d^@^@^@^@^@^@^@'^@^@^@md58fd917bc348d399f31056d33330ba74f^@<98>^B^@^@<81>^E^@^@^@^@^@^@^@^@^@^@

^E^@^H^@^C%^X?madisoncounty^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@d^@^@^@^@^@^@^@'^@^@

^@md58fd917bc348d399f31056d33330ba74f^@<94>^B^@^@<98>^B^@^@^@^@^@^@^@^@^@^@^D^@^H^@^A^E^X^_madisoncounty^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@

^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@d^@^@^@^@^@^@^@^B^@^@^@^A^@^@^@^@^@^@^@^@^@^@^@^B^@^H^@^C)^X?postgres^@^@^@^@^@^@^@^@^@^@^@^@^@

^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^A^@^@^@^A^A^A^@'^@^@^@md506464ceceb2b5b44a27417bf6ac59c8a^@^A^@^@

^@T^B^@^@^A^@^@^@^@^@^@^@^B^@^H^@^A^E^X^_postgres^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@
^@^@^@^@^A^@^@^@^A^A^A^@


Tom Lane wrote:
> Adam Dear <adear@usnx.net> writes:
>> madisoncounty=# select ctid,xmin,xmax,cmin,cmax,usename from pg_shadow;
>>   ctid  | xmin | xmax | cmin | cmax |    usename
>> -------+------+------+------+------+---------------
>>   (0,1) |    1 |  596 |  596 |    1 | postgres
>>   (0,2) |    2 |    1 |    1 |    0 | postgres
>>   (0,5) |    2 |    0 |    0 |    0 | madisoncounty
>> (3 rows)
>
> Hm, I thought you deleted the (0,2) tuple ... did you restore a physical
> backup or something?
>
> Anyway, as far as I can see the way that you got into this state must
> have been
>
> 1. The (0,1) tuple must have been the one originally inserted by initdb;
> there's no other way it could have xmin=1.
>
> 2.  Shortly after initdb (at transaction 596 to be exact) this tuple was
> updated --- probably by a password-assignment operation --- creating the
> tuple at (0,2), which must originally have had xmin = 596, cmin = 1 (the
> xmax overlays cmin in 7.4, so we can assume that column value is bogus).
>
> 3.  Much time passes, and pg_shadow never gets vacuumed so the dead
> tuple at (0,1) is never cleaned up.  Eventually the XID counter passes 2
> billion + 596, and suddenly transaction 596 appears to be in the future,
> so the tuple at (0,1) starts to be seen by SELECTs again.
>
> 4.  At this point you ran VACUUM FREEZE, which replaced the xmins of the
> second and third tuples with 2 (FrozenTransactionId) ... but 7.4 does
> not think it could ever need to freeze xmax, and at this point VACUUM
> wouldn't touch the (0,1) tuple anyway because it considers the tuple as
> RECENTLY_DEAD.
>
> So VACUUM won't help you, at least not for another 2 billion
> transactions.  And the DELETE doesn't work either because it correctly
> perceives (0,1) as an updated tuple that's been superseded by (0,2),
> which doesn't meet the WHERE clause so DELETE doesn't touch it.
> You could delete (0,2) but that leaves you with no working postgres user
> (since the system's SnapshotNow rules consider (0,1) as dead), and if
> you create another one you're back to having 2 entries in pg_shadow.
> Nasty :-(
>
> I can't think of any way out of this using plain 7.4 SQL operations.
> You could maybe hack a special case into VACUUM to make it nuke the
> dead tuple, but what's probably going to be easier is to manipulate the
> data on disk.  Are you comfortable enough with editing binary data
> to find the "596" and replace it with "2"?  It'd be somewhere near
> the end of the first (and probably only) block of pg_shadow, and a
> few bytes before one of the occurrences of the string "postgres".
> BTW, pg_shadow is $PGDATA/global/1260.
>
> (If you try this, do the editing while the postmaster is stopped,
> else you might have problems with it buffering the old data.)
>
>             regards, tom lane
>