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

On 6/30/2006 11:12 AM, Scott Marlowe wrote:
> 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.

Last thing I heard was that MySQL still had only statement based
replication and that it doesn't work together with some of the new
enterprise features like triggers and stored procedures. Row level
replication is on their TODO list and this major feature will probably
appear in some minor 5.2.x release.

Jan

>
>> PostgreSQL 8.1.x:
>> * Embedded procedures in multiple native languages (stored procedures and
>> functions can be written in native Perl, Python, Tcl, SQL, PL/PgSQL)
>
> Note that there are a dozen or more other languages as well. Just FYI.
> Off the top of my head, plPHP, plJ (java there's two different java
> implementations, I think) and plR (R is the open source equivalent of
> the S statistics language)
>
>> * 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.
>
>> Pointers, tips, quick facts and gotchas for other people converting:
>> --------------------------------------------------------------------
>>
>> * MySQL combines the concepts of 'database' and 'schema' into one. PostgreSQL
>> differentiates the two. While the hierarchy in MySQL is
>> database.table.field, PostgreSQL is roughly: database.schema.table.field.
>> A schema is a 'logically grouped set of tables but still kept within a
>> particular database.' This could allow separate applications to be built
>> that still rely upon the same database, but can be kept somewhat logically
>> separated. The default schema in each database is called 'public', and is
>> the one referred to if no others are specified. This can be modified with
>> 'SET search_path TO ...'.
>
> This is a VERY good analysis of the difference between the two
> databases.
>
>> * 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"
>
>> * Pg uses the 'serial' column type instead of AUTO_INCREMENT. This allows
>> more than one independent sequence to be specified per table (though the
>> utility of this may be of dubious value). These are closer to Oracle's
>> concept of sequence generators, and they can be manipulated with the
>> currval(), nextval(), setval(), and lastval() functions.
>
> Don't forget 64bit bigserials too.
>
>> * Pg requires its tables and databases be 'vacuumed' regularly to remove
>> completed transaction snapshots and optimize the tables on disk. It is
>> necessary because the way that PostgreSQL implements true MVCC is by
>> writing all temporary transactions to disk and setting a visibility
>> flag for the record. Vacuuming can be performed automatically, and in
>> a deferred manner by using vacuum_cost settings to limit it to low-load
>> periods or based upon numerous other criteria. See the manual for more
>> information.
>
> Interestingly enough, MySQL's innodb tables do almost the exact same
> thing, but their vacuum process is wholly automated. Generally, this
> means fewer issues pop up for the new dba, but when they do, they can be
> a little harder to deal with. It's about a wash. Of course, as you
> mentioned earlier, most mysql folks aren't using innodb.
>
>> * While MySQL supports transactions with the InnoDB databases, many MySQL
>> users generally do not use them extensively enough. With Pg, due to the
>> behaviour of the server in attempting to ensure data integrity in a
>> variety of situations (client disconnection, network trouble, server
>> crashes, etc.), it is highly advisable to become familiar and utilize
>> transactions a lot more, to ensure your DATA is left in a consistent state
>> before and after every change you wish to make.
>
> A point you might want to throw in here is that EVERYTHING in postgresql
> is a transaction. If you don't issue a begin statement, then postgresql
> runs each statement you type in inside its own transaction.
>
> This means that inserting 10,000 rows without wrapping them inside an
> explicit transaction results in 10,000 individual transactions.
>
> However, the more interesting thing here, is that every statement,
> including DDL is transactable, except for a couple of big odd ones, like
> create database. So, in postgresql, you can do:
>
> begin;
> create table xyz...
> alter table abc...
> insert into abc select * from iii
> update iii...;
> drop table iii;
> (oops, I messed up something)
> rollback;
>
> and there's no change and no lost data. Quite impressive actually.
>
>
>> Common equivalents:
>> -------------------
>>
>> MySQL PostgreSQL
>> ----- -----------
>> OPTIMIZE TABLE ... VACUUM ...
>
> vacuum and analyze for optimize I think. Also, possibly reindex,
> although nominally that's the "sledge hammer" of optimization.
>
> One last thing I'd mention that I REALLY like about PostgreSQL over any
> other database I've used is that the psql interface has a complete
> syntax lookup feature that is WAY cool. \h brings it up, and \h COMMAND
> where COMMAND is the command you want to look up will bring up the
> syntax for your command.
>
> And, I hate the fact that CTRL-C in the mysql command line tool exits
> the tool instead of interrupting the current query. In PostgreSQL it
> interrupts the current query. CTRL-\ will kill the client if you need
> to.
>
> Overall, a great review. Thanks.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #