> Added to TODO:
> 
> 	* Transaction log, so re-do log can be on a separate disk by
> 	  logging SQL queries, or before/after row images
> 
I would drop the "log SQL queries idea". 
No need to log before row images eighter, since this is the 
current state of the row during rollforward.
(For asserts a checksum of the before image would be sufficient, 
but IMHO not necessary.)

I suggest:
	* Transaction log that stores after row (or even only column)
images,
	  which can be put on a separate disk to allow rollforward after
	  a restore of a server.

The "restore of a server" is a main problem here, but I suggest the
following
additional backup tool, that could be used for a "restore of a server"
which could then be used for a rollforward and would also be a lot faster 
than a pg_dump:

1. place a vacuum lock on db (we don't want vacuum during backup)
2. backup pg_log using direct file access (something like dd bs=32k)
3. backup the rest in any order (same as pg_log)
4. release vacuum lock

If this was restored, this should lead to a consistent database, 
that has all transactions after the start of backup rolled back.

Is there a nono in this idea? I feel it should work.
A problem is probably, that the first to touch a row with a committed update
stores this info in that row. There would probably need to be an undo for
this 
after restore of the physical files.

Andreas


Wed, 7 Jul 1999 11:13:15 +0200 
Zeugswetter Andreas IZ5 <Andreas.Zeugswetter@telecom.at>
Re: [HACKERS] RE: [GENERAL] Transaction logging
Well, I'm thinking about WAL last two weeks. Hiroshi pointed me
problems in my approach to savepoints (when a tuple was marked
for update and updated after it) and solution would require
new tid field in header and both t_cmin/t_cmax => bigger header.
I don't like it and so I switched my mind -:).
I'm using "Transaction Processing..." book from Bruce - thanks
a lot, it's very helpful.

I'll come with thoughts and feels in next few days...

Zeugswetter Andreas IZ5 wrote:
> 
> > Added to TODO:
> >
> >       * Transaction log, so re-do log can be on a separate disk by
> >         logging SQL queries, or before/after row images
> >
> I would drop the "log SQL queries idea".

Me too.

> No need to log before row images eighter, since this is the
> current state of the row during rollforward.

This is true as long as we follow non-overwriting - may be
changed some day.

> The "restore of a server" is a main problem here, but I suggest the
> following
> additional backup tool, that could be used for a "restore of a server"
> which could then be used for a rollforward and would also be a lot faster
> than a pg_dump:
> 
> 1. place a vacuum lock on db (we don't want vacuum during backup)
> 2. backup pg_log using direct file access (something like dd bs=32k)
     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> 3. backup the rest in any order (same as pg_log)
> 4. release vacuum lock

It looks like log archiving, not backup.
I believe that _full_ backup will do near the same
things as pg_dump now, but _incremental_ backup will
fetch info about what changed after last _full_ backup
from log.

Vadim

Wed, 07 Jul 1999 19:00:03 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] RE: [GENERAL] Transaction logging

Updated TODO:

* Transaction log, so re-do log can be on a separate disk by
  with after-row images


> > Added to TODO:
> > 
> > 	* Transaction log, so re-do log can be on a separate disk by
> > 	  logging SQL queries, or before/after row images
> > 
> I would drop the "log SQL queries idea". 
> No need to log before row images eighter, since this is the 
> current state of the row during rollforward.
> (For asserts a checksum of the before image would be sufficient, 
> but IMHO not necessary.)
> 
> I suggest:
> 	* Transaction log that stores after row (or even only column)
> images,
> 	  which can be put on a separate disk to allow rollforward after
> 	  a restore of a server.
> 
> The "restore of a server" is a main problem here, but I suggest the
> following
> additional backup tool, that could be used for a "restore of a server"
> which could then be used for a rollforward and would also be a lot faster 
> than a pg_dump:
> 
> 1. place a vacuum lock on db (we don't want vacuum during backup)
> 2. backup pg_log using direct file access (something like dd bs=32k)
> 3. backup the rest in any order (same as pg_log)
> 4. release vacuum lock
> 
> If this was restored, this should lead to a consistent database, 
> that has all transactions after the start of backup rolled back.
> 
> Is there a nono in this idea? I feel it should work.
> A problem is probably, that the first to touch a row with a committed update
> stores this info in that row. There would probably need to be an undo for
> this 
> after restore of the physical files.
> 
> Andreas
> 
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Wed, 7 Jul 1999 23:25:24 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] RE: [GENERAL] Transaction logging
Vadim Mikheev wrote:
> 
> > The "restore of a server" is a main problem here, but I suggest the
> > following
> > additional backup tool, that could be used for a "restore of a server"
> > which could then be used for a rollforward and would also be a lot faster
> > than a pg_dump:
> >
> > 1. place a vacuum lock on db (we don't want vacuum during backup)
> > 2. backup pg_log using direct file access (something like dd bs=32k)
>      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> > 3. backup the rest in any order (same as pg_log)
> > 4. release vacuum lock
> 
> It looks like log archiving, not backup.
> I believe that _full_ backup will do near the same
> things as pg_dump now, but _incremental_ backup will
> fetch info about what changed after last _full_ backup
> from log.

Sorry, I was wrong. pg_dump is what's known as Export utility
in Oracle and backup is quite different thing. But I have
corrections for full backup described above:

1. no vacuum lock is needed: all vacuum ops will be logged
   in normal way to rollback changes in failures;
2. all datafiles have to be backed up _before_ log backup
   due to WAL logic: changes must be written to log before
   they'll be written to on-disk data pages.

Vadim

Fri, 16 Jul 1999 15:35:50 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] RE: [GENERAL] Transaction logging