postgresql fdw mysql_postgresql添加mysql_fdw测试过程

1.切换至postgres用户，输入密码登录 。注：如果安装了postgresql系统会自动创建该用户

su postgres

2.登录postgres数据库。注：安装postgresql时默认生成的数据库， 也可以登录自己创建的数据库，这里为了方便直接登录默认提供的数据库。

$ psql

psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))

Type "help" for help.

postgres=#

2.接下来按照官方文档流程使用mysql_fdw,官方文档点这：https://pgxn.org/dist/mysql_fdw/

第一步:加载mysql_fdw扩展

CREATE EXTENSION mysql_fdw;

第二步:创建连接外部数据库的连接对象

CREATE SERVER mysql_server

FOREIGN DATA WRAPPER mysql_fdw

OPTIONS (host '127.0.0.1', port '3306');

第三步:创建用户映射，postgresql用户映射mysql服务器的用户(postgresql数据库用户:postgres; mysql数据库用户:foo)

CREATE USER MAPPING FOR postgres

SERVER mysql_server

OPTIONS (username 'foo', password 'bar');

第四步:创建与mysql中对应的外部表。注:表名称，列名一致，对应列数据类型可以不同但能够转换(依赖与数据库本身)

CREATE FOREIGN TABLE warehouse(

warehouse_id int,

warehouse_name text,

warehouse_created TIMESTAMPZ)

SERVER mysql_server

OPTIONS (dbname 'db', table_name 'warehouse');

在postgresql中向mysql外部数据库插入数据，发现出出错。

postgres=# insert into warehouse values (1,'ups',now());

ERROR: failed to connect to MySQL: Access denied for user 'foo'@'localhost' (using password: YES)

3.上面出错的原因是我没有安装mysql_server，

以及没有在mysql中添加上面映射的 foo 用户，

以及没有创建名为db的数据库并在其中创建warehouse表，接下来进行配置。

第一步:安装mysql_server，启动mysql服务，切换至root用户登录mysql

安装数据库

$ sudo apt install mysql-server

启动数据库

$ service mysql start

登录mysql并创建远程用户

$ su root

# mysql -uroot

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 14

Server version: 5.7.30-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

create user foo identified by 'bar';

Query OK, 0 rows affected (0.00 sec)

给用户授权

mysql> grant all privileges on *.* to 'foo'@'%'identified by 'bar' with grant option;

Query OK, 0 rows affected, 1 warning (0.00 sec)

创建数据库

mysql> create database db;

Query OK, 1 row affected (0.00 sec)

计入数据库

mysql> user db;

创建表

mysql> create table warehouse( warehouse_id int, warehouse_name text, warehouse_created timestamp);

Query OK, 0 rows affected (0.03 sec)

4.在postgresql上向外表插入数据,发现没有创建表的唯一索引

postgres=# insert into warehouse values (1, 'ups', now());

ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation

mysql表中创建唯一索引

mysql> create unique index warehouse_index on warehouse(warehouse_id);

5.最后测试

postgresql插入数据 mysql中查询成功(INSERT INTO)

postgres=# insert into warehouse values (1,'ups',now());

INSERT 0 1

postgres=#

mysql> select * from warehouse;

+--------------+----------------+---------------------+

| warehouse_id | warehouse_name | warehouse_created |

+--------------+----------------+---------------------+

| 1 | ups | 2020-06-04 15:41:51 |

+--------------+----------------+---------------------+

1 row in set (0.00 sec)

mysql>

postgresql更新数据 mysql中查询成功(UPDATE)

postgres=# update warehouse set warehouse_name = 'ups_new' ,warehouse_created = now() where warehouse_id=1;

UPDATE 1

postgres=#

mysql> select * from warehouse;

+--------------+----------------+---------------------+

| warehouse_id | warehouse_name | warehouse_created |

+--------------+----------------+---------------------+

| 1 | ups_new | 2020-06-04 16:53:01 |

+--------------+----------------+---------------------+

1 row in set (0.00 sec)

mysql>

postgresql删除数据 mysql中查询成功(DELETE)

postgres=# delete from warehouse where warehouse_id=1;

DELETE 1

postgres=#

mysql> select * from warehouse;

Empty set (0.00 sec)

mysql>

done

第一次使用外部数据功能，看了一些文档，总的来说不难。吧整个流程整理了一下，以后用到有迹可循，也希望能够帮助到大家。