[andrew@dino mysql_fdw]$ psql wp psql (9.5.0) Type "help" for help. wp=# create extension mysql_fdw; CREATE EXTENSION wp=# CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '127.0.0.1', port '3306'); CREATE SERVER wp=# create user mapping for andrew server mysql_server; CREATE USER MAPPING wp=# import FOREIGN SCHEMA test from server mysql_server into public; ERROR: type "tinytext" does not exist LINE 4: comment_author tinytext NOT NULL, ^ QUERY: CREATE FOREIGN TABLE wp_comments ( "comment_ID" bigint NOT NULL, "comment_post_ID" bigint NOT NULL, comment_author tinytext NOT NULL, comment_author_email varchar NOT NULL, comment_author_url varchar NOT NULL, "comment_author_IP" varchar NOT NULL, comment_date timestamp NOT NULL, comment_date_gmt timestamp NOT NULL, comment_content text NOT NULL, comment_karma int NOT NULL, comment_approved varchar NOT NULL, comment_agent varchar NOT NULL, comment_type varchar NOT NULL, comment_parent bigint NOT NULL, user_id bigint NOT NULL ) SERVER mysql_server OPTIONS (dbname 'test', table_name 'wp_comments'); CONTEXT: importing foreign table "wp_comments" wp=# create domain tinytext as text; CREATE DOMAIN wp=# import FOREIGN SCHEMA test from server mysql_server into public; IMPORT FOREIGN SCHEMA wp=#
Friday, January 15, 2016
Using PostgreSQL 9.5's IMPORT FOREIGN SCHEMA
This is a pretty nice feature in 9.5. The other day I needed to look at some data from a WordPress site that's stored in MySQL. I had a dump of the data, but I'm not terribly proficient at using MySQL tools or their SQL dialect, so I thought I would try out just importing the schema to PostgreSQL using the new IMPORT FOREIGN SCHEMA command. It worked pretty well. First I installed the Postgres rpms from the community repository. Unfortunately, the repository doesn't have the latest mysql foreign data wrapper for 9.5, so I cloned it from github and built and installed it without difficulties. Then I restored the backup into the test database on local mysql instance. Then there was a small glitch. When I tried to import the schema it complained that it didn't know about the type "tinytext". So I created a domain for this that just mapped it to text. and then re-ran the import, and it worked just fine. Here is the whole import session:
PostgreSQL is more and more becoming the Swiss Army Knife of databases. Love this use case. (I will be using it, for sure!)
ReplyDelete