Обсуждение: how critical is WAL
Hi, I happenned to come across the following in the documentation on WAL implementation in v7.1 - ***************************************************** Before WAL, any crash during writing could result in: 1.index tuples pointing to non-existent table rows 2.index tuples lost in split operations 3.totally corrupted table or index page content, because of partially written data pages ***************************************************** Does anybody know what kind of a problem this refers to ? Does this mean that incomplete transactions would be stored or does this mean that the entire table might get corrupted and unusable, implying loss of all data ? ( I am using postgresql v7.0.x , and would ideally like to migrate to v7.1 after a few months ... unless it is critical enough to do so earlier. ) Thanks, Rini __________________________________________________ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices! http://auctions.yahoo.com/
> Hi, > > I happenned to come across the following in the > documentation on WAL implementation in v7.1 - > > ***************************************************** > Before WAL, any crash during writing could result in: > > 1.index tuples pointing to non-existent table rows > > 2.index tuples lost in split operations > > 3.totally corrupted table or index page content, > because of partially written data pages > ***************************************************** Indexes could get corrupt, requiring rebuilding. That problem is gone with WAL. Transaction commits where always safe. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
--- Bruce Momjian <pgman@candle.pha.pa.us> wrote: > > Hi, > > > > I happenned to come across the following in the > > documentation on WAL implementation in v7.1 - > > > > > ***************************************************** > > > Before WAL, any crash during writing could result > in: > > > > 1.index tuples pointing to non-existent table > rows > > > > 2.index tuples lost in split operations > > > > 3.totally corrupted table or index page > content, > > because of partially written data pages > > > ***************************************************** > > Indexes could get corrupt, requiring rebuilding. > That problem is gone > with WAL. Transaction commits where always safe. > Thanks. I was specifically asking about Result '3' above mentions 'totally corrupted table page content because of partially written data pages' . Is this also repairable ? __________________________________________________ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices! http://auctions.yahoo.com/
> > Indexes could get corrupt, requiring rebuilding. > > That problem is gone > > with WAL. Transaction commits where always safe. > > > > Thanks. I was specifically asking about Result '3' > above mentions 'totally corrupted table page content > because of partially written data pages' . Is this > also repairable ? I never heard of that happening to anyone. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
I'm glad about that ! The documentation led me to believe that it was imperative to move to the version using WAL, perhaps the documentation could be modified to remove Result '3' if it has not hapenned to anybody. Thanks Rini --- Bruce Momjian <pgman@candle.pha.pa.us> wrote: > > > Indexes could get corrupt, requiring rebuilding. > > > > That problem is gone > > > with WAL. Transaction commits where always > safe. > > > > > > > Thanks. I was specifically asking about Result '3' > > above mentions 'totally corrupted table page > content > > because of partially written data pages' . Is this > > also repairable ? > > I never heard of that happening to anyone. > > -- > Bruce Momjian | > http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) > 853-3000 > + If your life is a hard drive, | 830 Blythe > Avenue > + Christ can be your backup. | Drexel > Hill, Pennsylvania 19026 __________________________________________________ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices! http://auctions.yahoo.com/
> > > Indexes could get corrupt, requiring rebuilding. > > > That problem is gone > > > with WAL. Transaction commits where always safe. > > > > > > > Thanks. I was specifically asking about Result '3' > > above mentions 'totally corrupted table page content > > because of partially written data pages' . Is this > > also repairable ? > > I never heard of that happening to anyone. Sorry to disappoint you but it seems to be the case with my crash. One of our customers accindentally switched off his machine. This is a shop with 5500+ items. The table contained these items were lost. All the other tables (bills, partners, etc.) were in good shape, select retrieved all data from them. I looked into the postgres manuals and as it suggests, I stopped postmaster and I tried to reindex this table in a standalone postgres. "reindex table" (without force) did not reindexed. "reindex index" dumped core. I dropped the primary index and tried to recreate it, but it also dumped core. I guess it falls under the 3rd category of your listed types of data loss. :-( The question still is : is this kind of failure repairable? The system is: RH 6.2, kernel-2.2.16-3, postgresql-7.0.3 rpms from ftp.postgresql.org. Regards, Zoltan Boszormenyi
> I looked into the postgres manuals and as it suggests, I stopped > postmaster and I tried to reindex this table in a standalone > postgres. "reindex table" (without force) did not reindexed. > "reindex index" dumped core. I dropped the primary index and > tried to recreate it, but it also dumped core. > > I guess it falls under the 3rd category of your listed types of data loss. > :-( > > The question still is : is this kind of failure repairable? > > The system is: RH 6.2, kernel-2.2.16-3, postgresql-7.0.3 rpms from > ftp.postgresql.org. Oh, I see. I don't know of any way to repair it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026