Re: replication hooks

Hi Marko,

Replication requirements vary widely of course, but DDL support is shared by such a wide range of use cases it is very difficult to see how any real solution would fail to include it. This extends to change extraction APIs, however, defined. The question of what DDL to replicate is also quite clear-all of it with as few exceptions as possible.

For instance, it is almost impossible to set up and manage replicated systems easily if you cannot propagate schema changes in serialized order along with other updates from applications. The inconvenience of using alternative mechanisms like the SLONY 'execute script' is considerable and breaks most commonly used database management tools.

That said, SLONY at least serializes the changes. Non-serialized approaches lead to serious outages and can get you into distributed consensus problems, such as when is it 'safe' to change schema across different instances. These are very hard to solve practically and tend to run into known impossibility results like Brewer's Conjecture, which holds that it is impossible to keep distributed databases consistent while also remaining open for updates and handling network partitions.

I'll post back later on the question of the API. The key is to do something simple that avoids the problems discussed by Andrew and ties it accurately to use cases. However, this requires a more prepared response than my hastily written post from last night.

Cheers, Robert

On 5/29/08 9:05 PM, "Marko Kreen" <markokr(at)gmail(dot)com> wrote:

On 5/29/08, Andrew Sullivan <ajs(at)commandprompt(dot)com> wrote:
> On Thu, May 29, 2008 at 12:05:18PM -0700, Robert Hodges wrote:
> > people are starting to get religion on this issue I would strongly
> > advocate a parallel effort to put in a change-set extraction API
> > that would allow construction of comprehensive master/slave
> > replication.
>
> You know, I gave a talk in Ottawa just last week about how the last
> effort to develop a comprehensive API for replication failed. I had
> some ideas about why, the main one of which is something like this:
> "Big features with a roadmap have not historically worked, so unless
> we're willing to change the way we work, we won't get that."
>
> I don't think an API is what's needed. It's clear proposals for
> particlar features that can be delivered in small pieces. That's what
> the current proposal offers. I think any kind of row-based approach
> such as what you're proposing would need that kind of proposal too.
>
> That isn't to say that I think an API is impossible or undesirable.
> It is to say that the last few times we tried, it went nowhere; and
> that I don't think the circumstances have changed.

I think the issue is simpler - API for synchronous replication is
undesirable - it would be too complex and hinder future development
(as I explained above).

And the API for asynchronous replication is already there - triggers,
txid functions for queueing.

There is this tiny matter of replicating schema changes asynchronously,
but I suspect nobody actually cares. Few random points about that:

- The task cannot even be clearly defined (on technical level - how
the events should be represented).
- Any schema changes need to be carefully prepared anyway. Whether
to apply them to one or more servers does not make much difference.
- Major plus of async replica is ability to actually have different
schema on slaves.
- People _do_ care about exact schema on single place - failover servers.
- But for failover server we want also synchronous replication.

So if we have synchronous WAL based replication for failover servers,
the interest in hooks to log schema changes will decrease even more.

--
marko

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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