Migrating from MySQL to PostgreSQL In RoR Application

Since the day Heroku has made Postgres as the default database, the popularity of Postgres has gone through the roof. A lot of developers however feel that its quite a task to move from mysql to Postgres, so we thought of putting up a small writeup on how one can go about migrating from mysql from Postgres. PostgreSQL
    • Often simply Postgres, is an object-relational database management system (ORDBMS) available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X.
    • It implements the majority of the SQL:2008 standard, is ACID-compliant, is fully transactional (including all DDL statements), has extensible data types, operators, and indexes, and has a large number of extensions written by third parties
Migrating from MySQL to PostgreSQL – Why ?
    • One of the reasons is definitely for engineers developing on Heroku platform since postgres is now the default database on Heroku.
    • MySQL is faster and easier to use than PostgreSQL but PostgreSQL is perceived as more powerful, more focused on data integrity, and stricter at complying with SQL specifications.
Installing Postgres: For the purpose of blog, we will assume that the operating system is ubuntu, it should be equally easier to install it on other operating systems. Open the terminal and type following command to install postgresql :-
sudo apt-get install postgresql postgresql-client /*For Ubuntu Operating System*/
Now your postgresql server and client is installed. You can verify the installation by logging into the postgresql console by using this command:-
sudo -u postgres psql
NOTE:- use ‘\q’ to exit from the postgresql console. Creating the user This step is something different from mysql and developers migrating from mysql found this to be quite difficult to understand. We will try to decipher it here. Postgresql creates default superuser called “postgres” but we can’t use it in our application because of postgres security reasons . So, we need to create a new user that our application can use to login to postgres. To do so, go to terminal and type the following command to create a new user :-
sudo -u postgres createuser
Pressing ENTER after writing this command will ask you some questions like name of user and basic privileges to be given. Simply type the name of the user and answer of other questions in y/n like:- Enter name of role to add: user_name /*name of the user to be created*/ Shall the new role be a superuser? (y/n) /*Type y to make the user Superuser, otherwise type n*/ You are done creating the user, we will now create the database and assign the privileges for this database to the given user. This is almost similar like we do in mysql. Create the PostgreSQL Database Use the following command to create the database:-
sudo -u postgres createdb database_name /*name of the database to be created*/
Grant access to the user for the database Grant access for the database to our user but first we set the password for the newly created user. For simplicity purpose, we assign all the privileges to the user on the database, you should seriously consider assigning only the appropriate permissions for security reasons.
sudo -u postgres psql /* For logging in to the postgresql console */
 
alter user user_name with encrypted password '#password'; /* For setting password of your user, Please change the user_name and #password with the name of user and password you want to set */
 
grant all privileges on database database_name to user_name; /* For granting previliges on your database to your user */
UI for PostgreSQL If you want to access your database through UI, you need to install phppgadmin on your machine with the following command:-
sudo apt-get install phppgadmin
After successful installation of phppgadmin, you can access phppgadmin on any web browser by typing the url ‘localhost/phppgadmin’ and login with the username/password which you just set above. Changes Required In The Rails Application:
    • use gem ‘pg’ instead of gem ‘mysql’ in Gemfile.
    • use adapter: postgresql instead of adapter: mysql in database.yml
    • change the username, password, database as set in postgresql.
    • After these changes, don’t forget to run ‘bundle install’.
Launch the app and it should now be running off with Postgresql as backend. Enjoy, working with your shiny new database.