环境:
OS:Centos 7
pgdb:13.8
mysqldb:5.7
1.安装fdw
[root@localhost extension]#yum list mysql_fdw*
[root@localhost extension]#yum install mysql_fdw_13.x86_64
[postgres@localhost ~]$ psql -h localhost -U postgres -p5432
postgres=# create extension mysql_fdw;
CREATE EXTENSION
2.创建server
在PG端创建sever,外部服务定义了具体外部数据源的连接信息,如果外部源是数据库,通常会包含数据库的IP,端口号,数据库名称等信息,如下
postgres=# CREATE SERVER mysql_server_test FOREIGN DATA WRAPPER mysql_fdw OPTIONS (HOST '192.168.1.100', PORT '3306');
CREATE SERVER
postgres=# \des
List of foreign servers
Name | Owner | Foreign-data wrapper
-------------------+----------+----------------------
mysql_server_test | postgres | mysql_fdw
(1 row)
3.创建用户映射
OPTIONS 是指外部拓展的选项,指定了访问外部数据标的本地用户和远程用户信息
postgres=# CREATE USER MAPPING FOR PUBLIC SERVER mysql_server_test OPTIONS (username 'hxl', password 'mysql');
CREATE USER MAPPING
4.创建外部表
理论上外部表的字段只要包含于远程mysql表字段内就可以,但是一般的,都建议将外部表与远程表的字段一致。但是mysql和pg字段类型有时会有些许区别,需要确认好字段类型,如下:
postgres=# create foreign table frg_tb_test(id int,name varchar(32)) server mysql_server_test options(dbname 'db_test',table_name 'tb_test'); CREATE FOREIGN TABLE postgres=# \d List of relations Schema | Name | Type | Owner --------+--------+---------------+---------- public | frg_tb_test | foreign table | postgres (1 row) postgres=# select * from frg_tb_test; id | name ----+------- 1 | name1 2 | name2 3 | name3 4 | name4 5 | name5 (5 rows)
-- The End --
标签:postgres,CREATE,server,fdw,mysql,test,安装 From: https://www.cnblogs.com/hxlasky/p/16850850.html