Migrating from MySQL to PostgreSQL

I have a major bee in my bonnet about wanting to migrate the database backend from MySQL to PostgreSQL. Although I do like MySQL, I prefer PostgreSQL, mainly for its superior granularity in record locking and performance issues. Although SlashBoot dot org is unlikely to push either database server to the levels where the performance comparisons become relevant, I do have future plans that are likely to make far better use of a high performance backend. I have some initial plans for features that make extensive use of databases and so I wanted to move over to PostgreSQL sooner rather than later.

Unfortunately, I have a couple of projects in place, mainly my LAMPpost site and the BeaFAQ system which are both MySQL based and the latter has to stay that way. My LAMPpost site will be migrated in its entirety to this CMS anyway, so that isn't so much of a problem. What it does mean for now, is that I will need to run both a MySQL and a PostgreSQL server on one machine, at least for a while. Thankfully, the host machine should be up to the task, being the Dell Poweredge 1400SC with an Intel P3@933MHz, 640MB of RAM and SCSI hard disks. It just means that for now, I'll have to allocate resources to the two database serving daemons sparingly, but when the time is right, I can remove MySQL altogether and allow PostgreSQL full reign. It'll be needing it when the new features come into play, as they'll make extensive use of temporary tables and fairly large result sets.

Initial findings

I've been messing about with SQL dumps and the mysql2pgsql script, so as to be able to migrate the existing SlashBoot database over and it appears to be a smooth and relatively painless move. The LAMPpost site will eventually be migrated to this same CMS, but I'm not in any rush on that point, as that one can't be solved by a simple dump conversion.

Other than the more advanced locking and performance features of PostgeSQL, I'm also impressed with the range of APIs, allowing many ways of working with the system. My only regret is not getting back into PostgreSQL sooner and not going into this level of depth in the first place. That'll be the insidious MySQL deployments, so enjoyed by hosting companies around the world and it will teach me to allow myself to be lead as I was.

If you aren't already in the grips of a database backend and are trying to decide which to choose, then look into both MySQL and PostgreSQL. I think you'll probably find the latter to be the superior product and if you choose the server in your deployment, then it should be the right choice. If you are at the mercy of the hosting provider, then you might have no option but to use MySQL. This isn't necessarily a bad thing and for 99% of situations, MySQL will probably meet your needs. I started off many moons ago with MS-SQL for a couple of years, had a couple of months with PostgreSQL, but didn't get to know it as well as I should have. I've spent a good few years developing site systems around MySQL and it was only when I started to get nostalgic about my early PostgreSQL days, that I became aware of the real world differences and saw ways that I could leverage those differences to my own advantage.

As I say, if you are starting out and have a choice in the matter, I advise you to seriously look at PostgreSQL, although if your path is steered towards MySQL, don't be disheartened as it is still a very capable database system. Switching from one to the other isn't a huge wrench from a developer's point of view, although moving data and system can be, if you don't use an abstraction layer in your code. But there is not much that will need to be re-learnt if and when you need to migrate from one to the other. They are, after all, SQL systems and have plenty in common.

Post a comment:

Like this page? | |