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

On 7/10/2006 10:00 PM, Alex Turner wrote:

> http://dev.mysql.com/doc/refman/5.1/en/replication-row-based.html
>
> 5.1

Ah, thanks. So I guess 5.1.5 and 5.1.8 must be considered major feature
minor/bugfix releases. I still don't understand how people can use
software in production that has literally zero bugfix upgrade path
without the risk of incompatibility due to new features. I consider
every IT manager, who makes that choice, simply overpaid.


Jan

>
> Alex
>
> On 7/10/06, Jan Wieck <JanWieck@yahoo.com> wrote:
>>
>> 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@Yahoo.com #
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>        choose an index scan if your joining column's datatypes do not
>>        match
>>
>


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