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

smarlowe(at)g2switchworks(dot)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.

It's multi-slave, mind you...

>> * Replication support still rudimentary.
>
> Hmmmm. I think that's an overly simplistic evaluation. The slony
> replication engine is actually VERY advanced, but the administrative
> tools consist mostly of "your brain". hehe. That said, once you've
> learned how to drive it, it's quite amazing. Keep in mind, slony
> can be applied to a living database while it's running, and can run
> between different major versions of postgresql. That's a pretty
> advanced feature. Plus, if the replication daemons die (kill -9ed
> or whatever) you can restart replication and slony will come right
> back where it was and catch up.

And you can trust that the data that is replicated will actually be
faithfully replicated, even in the presence of timestamps, triggers,
and other things that challenge determinism...

>> * Pg uses a 'template1' pseudo-database that can be tailored to provide
>> default objects for new database creation, if you should desire. It
>> obviously also offers a 'template0' database that is read-only and
>> offers a barebones database, more equivalent to the empty db created with
>> mysql's CREATE DATABASE statement.
>
> This isn't quite right.
>
> template0 is a locked and "pure" copy of the template database. It's
> there for "break glass in case of emergency" use. :)
>
> template1, when you first initdb, is exactly the same as template0, but
> you can connect to it, and alter it. Both of these are "real"
> postgresql databases. template1 is the database that gets copied by
> default when you do "create database". Note that you can also define a
> different template database when running create database, which lets you
> easily clone any database on your machine. "create database newdb with
> template olddb"

In the last few weeks, we've had fun using "createdb --template=" to
create test copies of production databases (well, replicas thereof...).

Creating a replica via Slony-I takes several hours, for large
databases, as it has to load data into tables, then generate indexes.

We've used "createdb" on such databases; the longest it took to set up
an "extra duplicate" was something like 8 minutes, and that gave our
sysadmins full copies of the production databases that could be used
for testing... The speed was *stunning*...
--
(format nil "~S(at)~S" "cbbrowne" "cbbrowne.com")
http://cbbrowne.com/info/multiplexor.html
"How much more helpful could I be than to provide you with the
appropriate e-mail address? I could engrave it on a clue-by-four and
deliver it to you in Chicago, I suppose." -- Seen on Slashdot...