Re: Transaction-controlled robustness for replication

Hi Tom,

Part of this is semantics-I like Simon's logical vs. physical terminology because it distinguishes neatly between replication that copies implementation down to OIDs etc. and replication that copies data content including schema changes but not implementation. It seems a noble goal get both to work well, as they are quite complementary.

There are various ways to get information to recapitulate SQL, but piggy-backing off WAL record generation has a lot of advantages. You at least have the data structures and don't have to reverse-engineer log information on disk. Of the multiple ways to build capable logical replication solutions, this seems to involve the least effort.

My company is currently heads down building a solution for Oracle based on reading REDO log files. It requires a master of Oracle dark arts to decode them and is also purely asynchronous. PostgreSQL will eventually be far better as these discussions boil down into designs.

Thanks, Robert

On 8/12/08 8:51 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

Markus Wanner <markus(at)bluegap(dot)ch> writes:
> Robert Hodges wrote:
>> Could you expand on why logical application of WAL records is impractical in
>> these cases? This is what Oracle does. Moreover once you are into SQL a
>> lot of other use cases immediately become practical, such as large scale
>> master/slave set-ups for read scaling.

> I cannot speak for Tom, but what strikes me as a strange approach here
> is using the WAL for "logical application" of changes. That's because
> the WAL is quite far away from SQL, and thus from a "logical
> representation" of the data. It's rather pretty physical, meaning it's
> bound to a certain Postgres release and CPU architecture.

Right. To take just one example: the value of MAXALIGN affects not only
how many tuples you can put on a heap page (thus changing TIDs of tuples,
which fundamentally breaks most of the current types of WAL records)
but how many tuples you can put on an index page (and thus index page
split decisions, and thereby pretty much every single fact about the
contents of upper btree levels). We need not go into architecture
dependencies that are stronger than that one, though there are many.
As for version independence, who thinks they can WAL-replicate changes
of the system catalogs into a different version with significantly
different system catalogs?

You couldn't even begin to make this work with anything approaching the
current level of semantic detail of WAL entries.

What I think Simon was actually driving at was query-shipping, which is
not my idea of "WAL" at all. It has some usefulness, but also a bunch
of downsides of its very own, mostly centered around reproducibility.
With the current WAL design I have some faith that the slaves reproduce
the contents of the master. With any "logical replication" design that
becomes a faith of the religious kind, because it sure isn't provable.

regards, tom lane

--
Robert Hodges, CTO, Continuent, Inc.
Email: robert(dot)hodges(at)continuent(dot)com
Mobile: +1-510-501-3728 Skype: hodgesrm