Migrating from MySQL / MariaDB to PostgreSQL

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 get the feeling that this is a bad thing, save your data and move to PostgreSQL.

1) automatically changing data …

MariaDB [test]&gt; CREATE TABLE data (
id integer NOT NULL,
data numeric(4, 2)
);
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]&gt; INSERT INTO data
VALUES (1, 1234.5678);
Query OK, 1 row affected,
1 warning (0.01 sec)

MariaDB [test]&gt; SELECT * FROM data;
+----+-------+
| id | data |
+----+-------+
| 1 | 99.99 |
+----+-------+
1 row in set (0.00 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

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.

1

2

3

4

5

6

7

8

9

10

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]&gt; 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]&gt; SELECT * FROM data;
+----+------+
| id | data |
+----+------+
| 0 | 99.99 |
+----+------+
1 row in set (0.00 sec)

1

2

3

4

5

6

7

8

9

10

11

12

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

Professional help

Contact us today to receive your personal offer from CYBERTEC. We offer timely delivery, professional handling, and 20 years of PostgreSQL experience.

Contact us