Databases are a crucial tool for any developer or a development enterprise. If you are a software developer you already know that your application needs a database to store data. One thing to have in the count is to choose the best database for your application. There are two types of databases, SQL and NoSQL databases. The first one being the oldest. SQL databases are very famous and still being used largely around big organizations and most of SQL solutions are paid but, there are good free solutions out there with MySQL Community Edition and PostgreSQL on the top. In this article, we will let you know more about this two databases and how to migrate from MySQL to PostgreSQL.
MySQL Database
MySQL is an open-source relational database management system (RDBMS). Its name is a combination of My, the name of co-founder Michael Widenius daughter, and SQL, the abbreviation for Structured Query Language.MySQL was first created by a company known has MySQL AB and sold to Oracle Corporation and has also a paid version.
MySQL AB was founded in 1995 and acquired by Sun Microsystems in 2008 and in 2010 was owned by Oracle Corporation. MySQL is one of the building blocks of LAMP and WAMP Technology Stack where the first letter represents the Operating System (L for Linux and W for Windows).
The LAMP is the acronym for Linux, Apache, MySQL, Perl/PHP/Python that uses the MySQL database. It’s also used on high-profile, large-scale websites like Google, Facebook, Twitter, Flickr, and Youtube.
In 2004 was claimed that more than 10 million downloads were made and 5 million installations.
Advantages of MySQL
- Easy to work with: MySQL can be installed very easily;
- Feature rich: MySQL supports a lot of the SQL functionality that is expected from an RDBMS;
- Secure: A lot of security features, some rather advanced, are built in MySQL.
- Scalable and powerful: MySQL can handle a lot of data and furthermore it can be used at scale if needed;
- Speedy: Works very efficiently and cut corners, thus providing speed gains.
Disadvantages of MySQL
- Known limitations: By design, MySQL does not intend to do everything and it comes with functional limitations that some state-of-the-art applications might require;
- Stagnated development: There are not big changes in the latest releases.
PostgreSQL
PostgreSQL, often simply Postgres, is an object-relational database (with additional/optional uses object features – with an emphasis on extensibility and standards compliance.
PostgreSQL is developed by the PostgreSQL Global Development Group formed by individuals and joint companies and it’s free and open source.PostgreSQL started as a project called Ingres (Interactive Graphics Retrieval System.) with a database named Ingres database. It was the free and open source but commercially supported.
PostgreSQL is developed by the PostgreSQL Global Development Group formed by individuals and joint companies and it’s free and open source.PostgreSQL started as a project called Ingres (Interactive Graphics Retrieval System.) with a database named Ingres database. It was the free and open source but commercially supported.
Ingres life endured from 1970 to 1985 has a research project at University of California, Berkeley.
Ingress contributed for a number of commercial database applications Sybase, Microsoft SQL Server, NonStop SQL and others.
Postgres (Post Ingres), a project which started in the mid-1980s, later evolved into PostgreSQL.
Advantages of PostgreSQL
- It is Open Source: It’s open-source and free, yet a very powerful relational database management system;
- Strong community: Supported 24/7 by a large community.
- Strong third-party support: Regardless of the extremely advanced features, is adorned with many great and open-source third-party tools for designing, managing and using the management system.
- Extensible: It is possible to extend it programmatically with stored procedures like an advanced RDBMS should be.
- Objective: PostgreSQL is not just a relational database management system but an objective one – with support for nesting, and more.
Disadvantages of PostgreSQL
- Performance: Less performant in writing heavy data to database in comparison to MySQL;
- Popularity: Since is not as popular as MySQL there are lesser people who would give local or remote support if needed by an enterprise;
- Hosting: Due to above-mentioned factors, it is harder to come by hosts or service providers that offer managed PostgreSQL instances.
How to migrate from MySQL to PostgreSQL?
Check that the Server is Running
Most likely you don’t need this chapter, but very briefly: after you’ve installed your package with PostgreSQL on your Linux machine (be it from a package or following these notes), you need to do something like:
su –
su – postgres
createdb test
psql test
=# create user username password ‘ password ‘;
— To change a password:
=# alter role username password ‘ password ‘;
=# create database databasename with encoding ‘utf8’;
=# grant all privileges on database databasename to username;
=# \l
=# \c databasename
=# \q
vi /etc/postgresql/pg_hba.conf
host all all 0.0.0.0 0.0.0.0 md5
su – postgres
createdb test
psql test
=# create user username password ‘ password ‘;
— To change a password:
=# alter role username password ‘ password ‘;
=# create database databasename with encoding ‘utf8’;
=# grant all privileges on database databasename to username;
=# \l
=# \c databasename
=# \q
vi /etc/postgresql/pg_hba.conf
host all all 0.0.0.0 0.0.0.0 md5
Be SURE to cover this security issue with iptables!
/etc/init.d/postgresql reload or /usr/lib/postgresql/bin/pg_ctl reload
postmaster successfully signaled
psql
-h server -d databasename -U usernamedatabasename=>
-h server -d databasename -U usernamedatabasename=>
Convert and import
Using pgloader
Have a look at pgloader and you can migrate your MySQL database over to PostgreSQL in a single command:
pgloader mysql://user@localhost/dbname postgresql:///dbname
This will handle type casting with a default casting rules set, and also schema discovery in MySQL and creation in PostgreSQL, including tables, columns, constraints (primary keys, foreign keys, NOT NULL), default values, and secondary indexes.The dates are transformed on the fly and accepted by PostgreSQL and zero dates are removed since PostgreSQL can’t accept empty date fields considering not a date when empty, MySQL accepts empty date field
Using SQL dumb
Use the following command:
mysqldump -u username -p –compatible=postgresql databasename > outputfile.sql
but even then you will have to change escaped chars (replacing \t with ^I, \n with ^M, single quote (‘) with doubled single quote and double (escaped) backslash (\\) with a single backslash). This can’t be trivially done with sed command, you may need to write a script for it (Ruby, Perl, etc). There is a MySQL to PostgreSQL Python convert script (you need to use –default-character-set=utf8 when exporting your mysqldump to make it work). It is much better and proven solution to prepend your dump with the following lines.
SET standard_conforming_strings = ‘off’;
SET backslash_quote = ‘on’;
SET backslash_quote = ‘on’;
These options will force PostgreSQL parser to accept non-ANSI-SQL-compatible escape sequences (Postgre will still issue HINTs on it; you can safely ignore them). Do not set these options globally: this may compromise the security of the server!You also have to manually modify the data types etc. as discussed later.
After you convert your tables, import them the same way you were used to in MySQL, that is –
psql -h server -d databasename -U username -f data.sql
There are other options for converting that you can explore it here.
NOTE: The credit of the migration tutorial goes to wikibooks.
Conclusion
As was said before, the database is an important thing for any developer and the decision to choose a good one is also tough decision regarding, Speed, Scalability, and performance. There are plenty of SQL databases and you just have to choose what best suits your need.Leave your comments below.
