An Awesome Way To Migrate To PostgreSQL From MySQL
PostgreSQL... We read about it, but so many of us have MASSIVE legacy projects built in MySQL that we cannot easily jump the fence... Well, that has gotten a lot easier of late with the improvement of PostgreSQL's Foreign Data Wrappers (FDW)... FDW allows you to use PostgreSQL as your database connection, but have it actually pass queries through to an FDW implementation (like mysql_fdw). Then you can still have all of your legacy data in the MySQL server and implement all of your new functionality with PostgreSQL without having to perform a possibly painful migration.
The first step on this journey is to install PostgreSQL. On Linux and other UNIX-like operating systems, this is a simple as installing a package using YUM or APT.
sudo apt-get install postgresql-9.4 postgresql-server-dev-9.4
That will install PostgreSQL 9.4 server with it's development files on Debian or Ubuntu, for Red Hat and it's derivatives there will be a similar set of commands. In order to install the FDW for MySQL, you will also need the MySQL development files:
sudo apt-get install libmysqlclient-dev
Once those packages are installed, it is time to download and install the MySQL FDW for PostgreSQL. The repository linked above can be found at: https://github.com/EnterpriseDB/mysql_fdw. Using git you can clone the source code to your local machine.
git clone https://github.com/EnterpriseDB/mysql_fdw.git cd mysql_fdw make USE_PGXS=1 sudo make USE_PGXS=1 install
The library will now be installed with your existing PostgreSQL shared libraries and can be activated as follows:
psql template1 CREATE EXTENSION mysql_fdw;
That's the hard part all done. The remaining bits are tedious, but simple. You will need to create a server object, a user mapping, and finally table mappings for each table you wish to expose to PostgreSQL.
CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '127.0.0.1', port '3306');
;; The PostgreSQL docs show using 'user', but the MySQL FDW requires the option to be called 'username'
CREATE USER MAPPING FOR postgres SERVER mysql_server OPTIONS (username 'foo', password 'bar');
CREATE FOREIGN TABLE warehouse(
warehouse_id int,
warehouse_name text,
warehouse_created timestamp)
SERVER mysql_server
OPTIONS (dbname 'db', table_name 'warehouse');
Things to keep in mind as you create your table mappings. The data types from MySQL are not a 1-to-1 map to PostgreSQL. For example, "int(11)" in MySQL will have to be represented as "bigint" in PostgreSQL. Additionally, "AUTO_INCREMENT" should be left off of any primary keys as the generation of the value will be handled by the MySQL server and PostgreSQL need not know about it. Another thing to consider is that MySQL often uses backticks (`) to indicate table and/or field names, and you cannot use those backticks in PostgreSQL. There is a convenient mapping of the data types show HERE.
Once you have created a table mapping, you can query it like any other PostgreSQL table. You can even perform JOIN queries against a mix of mapped tables and native tables. This makes using your legacy MySQL tables along with your PostgreSQL tables a cinch!
This was just a quick overview of configuring Foreign Data Wrappers for PostgreSQL, but you can find more details and the MySQL FDW HERE on GitHub. You can also find out more about FDW in general and more FDW implementations at the PostgreSQL Wiki. Feel free to leave comments below if you have questions or would like to add something to the conversation!
Comments