MySQL and MariaDB are by far the most widespread Open Source database systems around. Still, many users out there are not satisfied with what MySQL and MariaDB have to offer. Migrating from MySQL or MariaDB to PostgreSQL is therefore a logical step and makes a lot of sense from a technical point of view.
Why favor PostgreSQL over MySQL / MariaDB?
MySQL and MariaDB have a totally different historical background. Traditionally MySQL has been used as a small web database and it still powers millions of websites. However, proper handling has often been sacrificed in favor of speed and ease of use.
To use MySQL vs. PostgreSQL is not an ideological issue – it is a purely technical one. Check out the following selection as an example. If you agree that a database is allowed to silently change your data or make “wise decisions” for you, MySQL and MariaDB are just fine. If you got the feeling that this is a bad thing, save your data and move to PostgreSQL
1) automatically changing data …
MariaDB [test]> CREATE TABLE data ( id integer NOT NULL, data numeric(4, 2) ); Query OK, 0 rows affected (0.02 sec) MariaDB [test]> INSERT INTO data VALUES (1, 1234.5678); Query OK, 1 row affected, 1 warning (0.01 sec) MariaDB [test]> SELECT * FROM data; +----+-------+ | id | data | +----+-------+ | 1 | 99.99 | +----+-------+ 1 row in set (0.00 sec)
test=# CREATE TABLE data ( id integer NOT NULL, data numeric(4, 2) ); CREATE TABLE test=# INSERT INTO data VALUES (1, 1234.5678); ERROR: numeric field overflow DETAIL: A field with precision 4, scale 2 must round to an absolute value less than 10^2.
PostgreSQL will rather error out than silently corrupt your data.
2) … silently ignoring your constraints
MariaDB [test]> UPDATE data SET id = NULL WHERE id = 1; Query OK, 1 row affected, 1 warning (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 1 MariaDB [test]> SELECT * FROM data; +----+------+ | id | data | +----+------+ | 0 | 99.99 | +----+------+ 1 row in set (0.00 sec)
We marked the column as “NOT NULL”.
PostgreSQL has to error out and reject bad data.
Note that these are just a few examples. If you want to see a more extensive (yet far from complete) compilation of problems, consider watching the following video.
Moving to PostgreSQL
If you want to move to PostgreSQL, we are more than willing to help. We use a modern toolchain including mysql_fdw, pg_chameleon as well as many custom made scripts, which make the migration process easy and smooth.
The key to MySQL / MariaDB migration is the fact that
data has to be cleaned before imported into PostgreSQL.
MySQL / MariaDB does not handle constraints well”
We provide professional services to customers around the world who want to get rid of MySQL and move to a more professional database engine such as PostgreSQL.
The following services are available:
- Migrating database schema
- Migrating of data
- Cleaning data in MySQL / MariaDB to satisfy constraints
- Moving server side code
- Migrating applications
- Performance tuning
- Fixing replication
- Improving database security
Contact us today to receive your personal offer from CYBERTEC. We offer timely delivery, professional handling, and 20 years of PostgreSQL experience.Contact us >>