Connect heroku to PGAdmin (POSTGRESQL) and using a database from MySQL
Heroku is a platform as a service (PaaS) that enables developers to build, run, and operate applications entirely in the cloud. I think this is a very useful platform to practice deploying some simple web-apps that I can try later on for my machine learning projects. Plus it is kind of easier to grasp for beginners like me, compared with its ultimate nemesis AWS (Amazon Web Server).
We can create a new server in just 10 seconds using Heroku command line, the deployment is very simple, can be done with Git Push anddd lots of add-ons!
I came up with the idea first from thinking about learning to use Heroku, yet by default, it uses database called Heroku Postgres. It is Heroku's reliable and powerful database as a service based on PostgreSQL.
PostgreSQL is one of the most popular and advanced open source RDBMS, other than MySQL. One of the key differences between the two (from what I read), Postgres is more to be object-relational database, while MySQL is purely relational database.
This highlights that Postgres has distinctive features like table inheritance and function overloading, which can be important to certain applications. It is best suited for systems that require execution of complex queries, or data warehousing and data analysis.
While MySQL is also awesome for its fast performance, it will start underperforming once it deals with complex queries or huge loads.
I was quite familiar with MySQL since I was in the undergraduate, however, by default Heroku platform is highly preferable to use alongside with Postgres. So yeah, no other choice, and I think this will be so much fun to learn as well :)
Below is the flow-chart for easier understanding of the flow of work that I'll be explaining in this entire post.
I'll divide into two segments to separate important steps to connect PostgreSQL for Heroku app.
1. Create connection to Heroku with PG-Admin PostgreSQL
2. Export data from MySQL Workbench & create database on PostgreSQL from MySQL data
Alright! Let's start with the first one.
Create connection to Heroku with PG-Admin (PostgreSQL)
After a successful login, our dashboard page will appear. The next step is to create an app that we will connect to PostgreSQL database.
Give it a good name :)
Choose the add-on plan, and submit the order form.
Yeah, we've successfully have new app added to our dashboard page now. Then click on it.
It will be redirected to the app dashboard. Go to the resource tab, and click on to Heroku Postgres link below Add-ons. It will open a new tab to our datastore page.
The next step is to set connection between Heroku and PGAdmin. We will need some datas on settings tab, just click view credentials.
And below that, the unique credential for our server will appear.
For now, we move to PGAdmin. We will create new server, connect it to Heroku, and create new database, schema, and so on.
So right click on the Servers, then choose Create -> Server
On general tab, give it a name
On connection tab, put the informations from credentials just like below.
On SSL tab, choose Require on SSL mode.
Also our DB name on the Advanced tab. Then click save. If it does not succeed, please check your internet connection, some IP disable the connection port to the server (AWS). Mine was successfully connected by using mobile hotspot ;D
Yeaayy! Success!
Phase 1 finished!
Next step, preparing data from MySQL to Postgres. From MySQL, for practice I will use a database that I've got from Seaborn library dataset, it is called 'tips'. This database is somehow quit well-known to get a good grasp of cool visualization tips and tricks using seaborn, so why not?
In this trial, I pretended myself that I already did some update, adding and tweaking into that DB by using MySQL workbench. So I could not use the original version. Then we need to export and dump it to Postgres.
From MySQL workbench, choose Server -> Data Export. Then choose the DB we want to export.
The database is now ready at Postgres.
Comments
Post a Comment