PostgreSQL to MySQL Foreign Data Wrapper Setup
Postgresql has a foreign data wrapper (fdw) that can read/write to mysql tables.
Goal Install in my dev environment and test how well it works.
My setup:
-
Macos 10.11
-
homebrew installed with its root folder set to ~/hbrew
-
the hbrew folder at the root of my user account
-
-
postgresql 10
Install Process
1. install pex, the postgresql extension package manager, https://github.com/petere/pex:
-
brew install pex
2. initialize pex:
-
pex init
3. find and install mysql_fdw
-
pex search mysql_fdw
-
pex install mysql_fdw
-
it will fail because the pex meta download info is out of date
4. change the pex meta info yaml file
-
~/hbrew/share/postgresql/pex/installed/mysql_fdw.yaml
-
change the url point to the latest version from https://github.com/EnterpriseDB/mysql_fdw/archive/
-
i.e. go to https://github.com/EnterpriseDB/mysql_fdw/ and look at what link the download file button points to
-
-
download that file
-
generate a sha1 hash for it using:
-
shasum /path/to/file, i.e. shasum Downloads/master-filename.zip
-
-
add the new sha1 hash to the meta file’s sha1 field
5. rerun the install
-
it should work this time
6. load mysql_fdw:
-
log into your postgresql
-
CREATE EXTENSION mysql_fdw;
7. proceed with the tutorial in 'PostgreSQL foreign data wrapper for MySQL,' https://github.com/EnterpriseDB/mysql_fdw
-
note that the mysql table must exists and have a primary key
-
or in other words, the create foreign table postgresql command does not create a mysql table
— MarlonAGriffith - 22 Nov 2018