Re: Notes on converting from MySQL 5.0.x to PostgreSQL

On Fri, 2006-06-30 at 16:34, Chris Browne wrote:
> smarlowe@g2switchworks.com (Scott Marlowe) writes:
> > I agree with Tom, nice notes.  I noted a few minor issues that seem to
> > derive from a familiarity with MySQL.  I'll put my corrections below...
> >
> > On Fri, 2006-06-30 at 08:17, Jason McManus wrote:
> >> On Converting from MySQL 5.0.x to PostgreSQL 8.1.x
> >> --------------------------------------------------
> >> Major differences I have noted:
> >> -------------------------------
> >>
> >> MySQL 5.0.x:
> >
> >> * Easy, built-in and extensive replication support.
> >
> > Not sure how extensive it is.  It's basically synchronous single master
> > single slave, right?  It is quite easy though.
>
> And it's statement-based, is it not?
>
> Indications are that MySQL replication is quite non-deterministic, as
> a result; if you use SYSDATE() in INSERT/UPDATE queries to set
> timestamps, replicas will get the wrong time.
>
> It looks like anything that is dynamically evaluated will be processed
> incorrectly on replicas, such as timezones.
>
> It is possible for the data on the master and slave to become
> different if a statement is designed in such a way that the data
> modification is non-deterministic; that is, left to the will of the
> query optimizer.

This is essentially correct.  Note that I can use pgpool with postgresql
and get about the same behaviour as mysql's replication, with the same
basic draw backs, that it's best to copy the database between shutdown
machines, and things that are dynamically evaluated can cause issues.
With pgpool I get synchronous replication with automatic failover, and
it's dead simple to build and install.

Which kind of shows off the difference in philosophy between the two
development camps.  The postgresql folks are very very picky about what
gets put into the main package, and let's face it, pgpool, while neat,
is not really ready for integration into the backend.  Meanwhile, a
nearly identical replication system IS integrated into the backend of
MySQL, warts and all for the sake of convenience of the users, and
possibly marketing.

It's not that one way is so much better than the other, it's just
indicative of how the two camps operate.