Exporting Databases from MySQL to PostgreSQL and some of it's advantages.

Warning: This will be short and simple.


You might need to migrate from using MySQL to PostgreSQL, a tool for that is available and can be used without stress.

First steps is to install pgloader

You can checkout pgloader's quickstart here
(Note that it's possible to also export from SQLite)


I'll be listing a few use cases and I hope you find one suitable for you.

1. Exporting from local MySQL DB to a local PostgreSQL DB: 

pgloader mysql://root:password@localhost/mdb postgres:///pdb

- This assumes you have a local MySQL database (user=root, password=password) with records named mdb and have a PostgreSQL database with an already created named pdb 

- Note that you might need to run the above command from your terminal as the PostgreSQL owner  e.g. You are to first run: sudo su - postgres 

2. Exporting from local MySQL DB to a remote PostgreSQL DB: 

pgloader  mysql://root:password@localhost/mdb postgres://root:password@54.32.21.98:5432/pdb

- This assumes you have a local MySQL database (user=root, password=password) with records named mdb and have a remote PostgreSQL database (user=root, password=password, host=54.32.21.98, port=5432) with an already created named pdb 


- Note that you might need to run the above command from your terminal as the PostgreSQL owner  e.g. You are to first run: sudo su - postgres 


Let me know which one works for you. 


A few advantages of PostgreSQL over MySQL are :


  • A PGSQL Database has support for multiple schemas (which houses your tables), thus allowing you implement multinenacy with a single database.
  • It implements the SQL standard very well
  • It includes support for "advanced" SQL stuff like window functions or common table expressions
  • It is very innovative in terms of how plpgsql interacts with SQL
  • It supports lots of advanced data types, such as (multi-dimensional) arrays, user-defined types, etc.

  • Checkout the following links for more comparison:

    https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems

    https://www.godaddy.com/help/key-differences-between-mysql-vs-postgresql-12392

    Comments

    Post a Comment

    Popular Posts