postgresql与mysql使用dblink联通

前端之家收集整理的这篇文章主要介绍了postgresql与mysql使用dblink联通前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

posrgresql默认的dblink只能联通posrgresql的不同数据库,不能进行异构数据库的联通,本文将介绍使用MysqL_fdw联通posrgresqlMysqLMysqL_fdw官网为:http://pgxn.org/dist/mysql_fdw/1.0.0/

前期准备

安装posrgresql9.1或以上版本

安装MysqL

下载MysqL_fdw

(postgresqlMysqL安装过程参见其他文章


安装MysqL_fdw:

设置环境变量:

vi ~/.bash_profile 
export PATH=/usr/local/bin:/usr/local/MysqL/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/MysqL/lib
 
  
source ~/.bash_profile


编译安装:

解压下载的MysqL_fdw,并进入解压的目录

make USE_PGXS=1
make USE_PGXS=1 install

注意:安装的使用使用root用户安装


dblink测试使用:

MysqL端创建用户数据库

MysqL> create user vince;
Query OK,0 rows affected (0.00 sec)

MysqL> create database vince_db ;
Query OK,1 row affected (0.00 sec)


MysqL> grant all privileges on vince_db.*  to 'vince'@'localhost' with grant option;
Query OK,0 rows affected (0.00 sec)

MysqL> grant all privileges on vince_db.*  to 'vince'@'%' with grant option;
Query OK,0 rows affected (0.00 sec)

MysqL> use vince_db;
Database changed
MysqL> show tables;
Empty set (0.00 sec)

MysqL> create table test(a integer,b text);
Query OK,0 rows affected (0.00 sec)

MysqL> insert into test(a,b)values (1,'a');
Query OK,1 row affected (0.00 sec)

MysqL> insert into test(a,b)values (2,'b');
Query OK,1 row affected (0.00 sec)

在postgresql端创建dblink链接

-- Create the require functions for the FDW.
CREATE FUNCTION MysqL_fdw_handler() 
    RETURNS fdw_handler 
    AS '$libdir/MysqL_fdw' 
    LANGUAGE C STRICT;

CREATE FUNCTION MysqL_fdw_validator(text[],oid) 
    RETURNS void 
    AS '$libdir/MysqL_fdw' 
    LANGUAGE C STRICT;

-- Create the data wrapper or "transport".
CREATE FOREIGN DATA WRAPPER MysqL_fdw 
    HANDLER MysqL_fdw_handler 
    VALIDATOR MysqL_fdw_validator;

-- Create the foreign server,a pointer to the MysqL server.
CREATE SERVER MysqL_svr 
    FOREIGN DATA WRAPPER MysqL_fdw 
    OPTIONS (address '127.0.0.1',port '3306');
CREATE USER MAPPING FOR PUBLIC
 SERVER MysqL_svr
OPTIONS(username 'vince',password '');
create foreign table test ( a integer,b text) server MysqL_svr options(table 'vince_db.test');

测试:

查询

pgdb1=# select * from test;
 a | b 
---+---
 1 | a
 2 | b
(2 rows)

关联:

pgdb1=# create table MysqL_test(a integer,b text);
CREATE TABLE

pgdb1=# insert into MysqL_test(a,b) values(1,'a');
INSERT 0 1
pgdb1=# select * from test t1,MysqL_test t2 where t1.a=t2.a;
 a | b | a | b 
---+---+---+---
 1 | a | 1 | a
(1 row)

插入:

pgdb1=# insert into test(a,b) values (3,'c');
ERROR:  cannot change foreign table "test"

更新:

pgdb1=# update test set b='c' where a=1;
ERROR:  cannot change foreign table "test"


结论:

MysqL_fdw可以很好的使postgresql联通MysqL,进行查询,关联等操作,但是外表是不能进行添加和更新操作。

原文链接:https://www.f2er.com/postgresql/196845.html

猜你在找的Postgre SQL相关文章