Re: Unable to dump database using pg_dump

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.

@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^B^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^E^@^H^(at)^C)^X?madisoncounty^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^@
^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@d^(at)^@^(at)^@^(at)^@^@'^(at)^@^(at)md58fd917bc348d399f31056d33330ba74f^@<98>^B^(at)^@<81>^E^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@
^E^(at)^H^@^C%^X?madisoncounty^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)d^@^(at)^@^(at)^@^(at)^@'^(at)^@
^(at)md58fd917bc348d399f31056d33330ba74f^@<94>^B^(at)^@<98>^B^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^D^(at)^H^@^A^E^X^_madisoncounty^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^@
^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@d^(at)^@^(at)^@^(at)^@^(at)^B^@^(at)^@^A^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^B^@^H^(at)^C)^X?postgres^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^@
^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^A^@^(at)^@^A^A^A^@'^(at)^@^(at)md506464ceceb2b5b44a27417bf6ac59c8a^@^A^(at)^@
^(at)T^B^@^(at)^A^@^(at)^@^(at)^@^(at)^@^B^(at)^H^@^A^E^X^_postgres^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@^(at)^@
^(at)^@^(at)^@^A^(at)^@^(at)^A^A^A^@

Tom Lane wrote:
> Adam Dear <adear(at)usnx(dot)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
>