Migration is one of the most important tasks that a DBA does. These days, most people are leaning towards Postgres(the most advanced database). I have laid out some steps for migrating your data from MYSQL to Postgres. Similar methods can be followed for migrating your data from Oracle, Sql server, Sybase etc to Postgres.
There are a lot of ways you can migrate your data. Here, I have explained only the two most important ones.
MIGRATION TOOL -
For small databases it is best to use a Migration tool. Postgres has a tool included in the Postgres Plus Advanced Server suite which can be downloaded from the URL below (for any of the operating system platforms -Linux, Windows, Solaris).
After downloading the Postgres Plus Advanced Server install it on your platform. There are a couple of conflicts I faced while installing it on Ubuntu.
- While trying to install using GUI a conflict comes saying login as root to get installed. So just switch to root user for GUI installation
- From terminal point of view its kind of easy to switch to the root and install it as ./filename.run.
Now the whole suite takes some time to be properly installed. Hmm are you eagerly waiting for migrating your data? Don’t worry we are very near to starting our process.
After the completion of installation, just go to Applications where you can find out Postgres advanced server software installed and click to open it. Other platforms can also find in the respective programs.
STEPS FOR MIGRATION –
1. Add a new server option.
2. Select the MYSQL database from the drop-down. As soon as you select you may get an error saying “MYSQL-hava driver has to be installed and should be placed it at /ext…”. Just download it from this URL : http://www.enterprisedb.com/downloads/third-party-jdbc-drivers
4. Add the host address, username, password and database name and test the connection.
5. After the successful completion of the test, login and add another new server( Postgres database) which is the target database.
6. In the same way add the host address, username, password and database name and test the connection.
7. Now create the target database name where the data has to be migrated.
8. Select the database from MYSQL which has to be migrated. Explore it for the schema–> database name. Now right click and an option appears saying migrate online, offline etc. Migrating online is very critical since we may loose some data while transactions goes on.
9. If you choose the online migration, request the users to not do any particular transactions at that time. Selecting the migrate online option will pop a window asking you to enter the target database of Postgres and target schema (default-public). You can find out the options definition only, constraints, data only etc.
10. Select your desired way of migration and run it. Once you see your migration successfully done, quit the window.
11. Now open your pgadmin and look for the target database where you will find out the migrated database as you requested.
This tool works well when the amount of data is small (say < 1 GB). For large amount of data (eg: 500 GB) use the below method.
MYSQLdump UTILITY -
The MYSQLdump utility is primarily used to backup and restore MYSQL databases, but it can also be used to move data from MYSQL to Postgres.
1. Dump tables from the source MYSQL database with the –nocreate- info option that won’t issue create table statements
2. Run the file (which contains SQL INSERT statements) through a Postgres SQL interface to insert the data.
The above methods works foolproof. Similar process has to be followed for migrating data from oracle, Sql server,Sybase to Postgres.
Have you used any other simpler method for migrating data? Do let me know how it was.