MySQL Database
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 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
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
-h server -d databasename -U usernamedatabasename=>
Using pgloader
Using SQL dumb
SET backslash_quote = ‘on’;
After you convert your tables, import them the same way you were used to in MySQL, that is –