Обсуждение: AW: more corruption

> You have recreated what pg_upgrade does.  It is for upgrading system
> tables.  If only your system tables were hosed, you are fine now.

Only if your previous system has been vacuum'ed and no dml afterwards.
Otherwise you also need to copy your old pg_log.

> 
> 
> > The Hermit Hacker wrote:
> > > just a quick thought ... have you tried shutting down and 
> restrating the
> > > postmaster?  basically, "reset" the shared memory?  v7.x handles
> > > corruptions like that alot cleaner, but previous versions 
> caused odd
> > > results if shared memory got corrupted ...
> > 
> > Well, I've rebooted twice. In fact, it was a hard lock that 
> caused the
> > problems. When the machine was brought back up, the db was foobar.
> > 
> > I'm doing something really really evil to avoid losing the 
> last days'
> > data:
> > 
> > -I created a new db
> > -used the old db schema to create all new blank tables

vacuum new db
(I would do a tar backup of the whole old db)
vacuum old db, if that is possible 

> > -copied the physical table files from the old data 
> directory into the
> > new database directory

if above vacuum old db was not possible copy old pg_log

> > -currently vacuuming the new db - nothing is barfing yet
> > -now hopefully I can create my indexes and be back in business
> > 
> > Tim

Andreas

Zeugswetter Andreas SB wrote:
> > > -I created a new db
> > > -used the old db schema to create all new blank tables
> 
> vacuum new db
> (I would do a tar backup of the whole old db)
> vacuum old db, if that is possible

Was not possible.

> > > -copied the physical table files from the old data
> > directory into the
> > > new database directory
> 
> if above vacuum old db was not possible copy old pg_log

Oops - I didn't do that.

> > > -currently vacuuming the new db - nothing is barfing yet

Actually, the vacuum seemed to be running forever making no progress so
I killed it.

> > > -now hopefully I can create my indexes and be back in business

I vacuumed here and it worked. I did not use my "old" pg_log file - what
did I lose?

Tim


-- 
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723

Tim Perdue <tperdue@valinux.com> writes:
>>>>>> -now hopefully I can create my indexes and be back in business

> I vacuumed here and it worked. I did not use my "old" pg_log file - what
> did I lose?

Hard to tell.  Any tuples that weren't already marked on disk as "known
committed" have probably gone missing, because their originating
transaction IDs likely won't be shown as committed in the new pg_log.
So I'd look for missing tuples from recent transactions in the old DB.
        regards, tom lane

> -----Original Message-----
> From: pgsql-hackers-owner@hub.org [mailto:pgsql-hackers-owner@hub.org]On
> Behalf Of Tom Lane
>
> Tim Perdue <tperdue@valinux.com> writes:
> >>>>>> -now hopefully I can create my indexes and be back in business
>
> > I vacuumed here and it worked. I did not use my "old" pg_log file - what
> > did I lose?
>
> Hard to tell.  Any tuples that weren't already marked on disk as "known
> committed" have probably gone missing, because their originating
> transaction IDs likely won't be shown as committed in the new pg_log.
> So I'd look for missing tuples from recent transactions in the old DB.
>

Hmm,this may be more serious.
MVCC doesn't see committed(marked HEAP_XMIN_COMMITTED) but
not yet committed(t_xmin > CurrentTransactionId) tuples.
He will see them in the future.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

> > -----Original Message-----
> > From: pgsql-hackers-owner@hub.org [mailto:pgsql-hackers-owner@hub.org]On
> > Behalf Of Tom Lane
> >
> > Tim Perdue <tperdue@valinux.com> writes:
> > >>>>>> -now hopefully I can create my indexes and be back in business
> >
> > > I vacuumed here and it worked. I did not use my "old" pg_log
> file - what
> > > did I lose?
> >
> > Hard to tell.  Any tuples that weren't already marked on disk as "known
> > committed" have probably gone missing, because their originating
> > transaction IDs likely won't be shown as committed in the new pg_log.
> > So I'd look for missing tuples from recent transactions in the old DB.
> >
>
> Hmm,this may be more serious.
> MVCC doesn't see committed(marked HEAP_XMIN_COMMITTED) but
> not yet committed(t_xmin > CurrentTransactionId) tuples.
> He will see them in the future.
>

P.S.
This is the main reason that I once proposed to call
'pg_ctl stop' to stop postmaster in pg_upgrade before/after
moving pg_log and pg_varibale.

There was a dicussion to recycle OIDs.
It's impossible to recycle XIDs.

Regards.

Hiroshi Inoue

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>>>> I vacuumed here and it worked. I did not use my "old" pg_log file - what
>>>> did I lose?
>> 
>> Hard to tell.  Any tuples that weren't already marked on disk as "known
>> committed" have probably gone missing, because their originating
>> transaction IDs likely won't be shown as committed in the new pg_log.
>> So I'd look for missing tuples from recent transactions in the old DB.
>> 

> Hmm,this may be more serious.
> MVCC doesn't see committed(marked HEAP_XMIN_COMMITTED) but
> not yet committed(t_xmin > CurrentTransactionId) tuples.
> He will see them in the future.

But he did a vacuum --- won't that get rid of any tuples that aren't
currently considered committed?
        regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> >>>> I vacuumed here and it worked. I did not use my "old" pg_log
> file - what
> >>>> did I lose?
> >>
> >> Hard to tell.  Any tuples that weren't already marked on disk as "known
> >> committed" have probably gone missing, because their originating
> >> transaction IDs likely won't be shown as committed in the new pg_log.
> >> So I'd look for missing tuples from recent transactions in the old DB.
> >>
>
> > Hmm,this may be more serious.
> > MVCC doesn't see committed(marked HEAP_XMIN_COMMITTED) but
> > not yet committed(t_xmin > CurrentTransactionId) tuples.
> > He will see them in the future.
>
> But he did a vacuum --- won't that get rid of any tuples that aren't
> currently considered committed?
>

Oops,did he move old pg_varibale ?
If so my anxiety has no meaning.

Regards.

Hiroshi Inoue