TiDB支持MySQL,Spark,但不支持Postgres, 那有没有办法实现Postgres访问呢?
现在尝试一个曲线救国的办法, MySQL FDW(mysql_fdw)。
利用mysql_fdw插件,可以读写MySQL数据库里的数据。
具体环境参考极速体验TiSpark。
安装HomeBrew
今天尝试的是在macOS下大名鼎鼎的HomeBrew(也支持Linux)
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
安装并运行Postgres
brew install postgresql
pg_ctl -D /home/linuxbrew/.linuxbrew/var/postgres start
安装MySQL FDW
git clone https://github.com/EnterpriseDB/mysql_fdw
cd mysql_fdw
#通过brew安装,pg_config配置里需要gcc-5
ln -s /bin/gcc-9 gcc-5
export PATH=.:/home/linuxbrew/.linuxbrew/bin:$PATH
make USE_PGXS=1
make USE_PGXS=1 install
cd ..
Postgres客户端
可以用psql,不过我还是愿意用pgweb(具体介绍详见推荐一个Postgres Web客户端,免安装跨平台还开源)
#下载pgweb,解压缩
wget https://github.com/sosedoff/pgweb/releases/download/v0.11.6/pgweb_linux_amd64.zip &&
unzip pgweb_linux_amd64.zip &&
rm pgweb_linux_amd64.zip
#PGHOSTADDR 变量会导致b报错,删除它
export -n PGHOSTADDR
#运行pgweb
./pgweb_linux_amd64 --url=postgres://postgres:@127.0.0.1/postgres?sslmode=disable
测试下MySQL FDW
- mysql_fdw安装好后,第一次需要加载
-- load extension first time after install安装插件
CREATE EXTENSION mysql_fdw;
- 与MySQL(TiDB)建立连接
-- create server object创建MySQL Server对象
CREATE SERVER mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '127.0.0.1', port '4000');
-- create user mapping创建用户映射
CREATE USER MAPPING FOR gitpod
SERVER mysql_server
OPTIONS (username 'root', password '');
- 创建外部表
-- create foreign table
CREATE FOREIGN TABLE "NATION" (
"N_NATIONKEY" int NOT NULL,
"N_NAME" char(25) NOT NULL,
"N_REGIONKEY" int NOT NULL,
"N_COMMENT" varchar(152) DEFAULT NULL
) SERVER mysql_server
OPTIONS (dbname 'TPCH_001', table_name 'NATION');
- 查询
select * from "NATION" limit 10;
- 插入数据
insert into "NATION"("N_NATIONKEY","N_NAME","N_REGIONKEY","N_COMMENT")
values (25,'PG',0,'TiDB 测试');
报错
ERROR: pq: first column of remote table must be unique for INSERT/UPDATE/DELETE operation
因为MySQL里的NATION表没有键值,解决办法
mysql>alter table NATION add primary key(N_NATIONKEY);
默认运行TiDB时报错如下,
ERROR 8200 (HY000): Unsupported add primary key, alter-primary-key is false
因为TiDB-Server默认alter-primary-key 为false,需要修改配置文件,设置为true。
再测试新增数据,成功。
- 更新数据
update "NATION" set "N_NAME"='MySQL FDW'
where "N_NATIONKEY"=25;
- 删除数据
delete from "NATION" where "N_NATIONKEY"=25;
TiDB系列
- TiDB安装与体验
- 安装WordPress
- Matomo
- 极速体验TiSpark
参考
- https://docs.pingcap.com/zh/tidb/dev/tidb-configuration-file
- https://github.com/EnterpriseDB/mysql_fdw