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 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
Disadvantages of MySQL
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.
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
Disadvantages of PostgreSQL
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 - postgres
=# 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;
=# \c databasename
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
-h server -d databasename -U username
Convert and import
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';
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.
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.