postgresql fdw mysql_PostgreSQL uses mysql_ fdw)

postgres Use mysql appearance

Talking about

postgres Unknowingly, it has been upgraded to the version 13, I remember two years ago 10, Of course, I have been expecting to use it in the project postgresql, Now it has come true ～;

By the way ： Use postgresql The reason for this is its ecological integrity , Another important point is Fast This is in the 10 Time of edition Maybe it's too early to say that ,

But in 13 This version is not too much , It's really fast , I simply use 500w Aggregate the data of , Without indexing ( Except for the primary key ) Under the circumstances Perform an aggregation operation ,postgres

The speed of mysql Of 8 times , It's really fast ～; Okay , In this chapter, I'll talk about the problems I actually encountered , Namely ： Cross database query , This is for mysql_fdw Realized .

Environmental preparation

One mysql example (5.7 or 8 All possible )

One postgres example ( Here we use source code to compile and install 13, Suggest 13,11 or 12 Can also be )

a linux( The following uses centos, Other systems can also refer to ha )

The following is for installation and use only mysql_fdw A tutorial for , Specifically mysql And postgres I will omit how to install it

Get ready libmysqlclient

Be careful ： if mysql And postgresql At the same table linux On board , There is no need to install mysql Tools , Please skip this paragraph

wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.22-linux-glibc2.17-x86_64-minimal.tar.xz

tar -xvJf mysql-8.0.22-linux-glibc2.17-x86_64-minimal.tar.xz

chown -R mysql:mysql /usr/local/mysql/

cd mysql-8.0.22-linux-glibc2.17-x86_64-minimal

cp -r ./* /usr/local/mysql/

Configure environment variables

The configuration file

vi /etc/profile

add to mysql environment variable

export MYSQL_HOME=/usr/local/mysql

export PATH=$PATH:/usr/local/mysql/bin

export LD_LIBRARY_PATH=MYSQL_HOME/lib:$LD_LIBRARY_PATH

add to postgres environment variable

export PG_HOME=/usr/local/pgsql

export LD_LIBRARY_PATH=$PG_HOME/lib:$MYSQL_HOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib

export PATH=$PG_HOME/bin:$MYSQL_HOME/bin:$PATH:.

Refresh configuration

source /etc/profile

Download and compile mysql_fdw

decompression

tar -xzvf REL-2_5_5.tar.gz

Get into

cd mysql_fdw-REL-2_5_5

compile

make USE_PGXS=1

install

make USE_PGXS=1 install

restart postgres

install mysql_fdw and After configuration, the environment variables must be restarted postgresql, This is very important

su postgres

/usr/local/pgsql/bin/pg_ctl -D /mnt/postgres/data -l logfile stop

/usr/local/pgsql/bin/pg_ctl -D /mnt/postgres/data -l logfile start

psql [ or /usr/local/pgsql/bin/psql]

Log in to postgres And configuration mysql_server

Switch to the specified database ( Very important !!!): \c YOUR_DB_NAME

CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'HOST', port '3306');

CREATE USER MAPPING FOR YOUR_DB_NAME SERVER mysql_server OPTIONS (username 'USERNAME', password 'PASSWORD');

GRANT USAGE ON FOREIGN SERVER mysql_server TO YOUR_DB_NAME;

GRANT ALL PRIVILEGES ON ods_tianmao_transaction TO YOUR_DB_NAME;

Create a look

The appearance created must be in mysql There is a corresponding table in , Otherwise it won't work ( Not in DB The tool shows )

Examples

CREATE FOREIGN TABLE YOUR_TABLE_NAME(

id numeric(22),

date date ,

name varchar(50),

create_time timestamp

)SERVER mysql_server OPTIONS (dbname 'YOUR_DB_NAME', table_name 'MYSQL_TABLE_NAME');

Delete operation

Delete extension

DROP EXTENSION mysql_fdw CASCADE;

Delete mysql_server

DROP SERVER [mysql_server] CASCADE;

Delete the appearance

DROP FOREIGN TABLE [YOUR_FOREIGN_TABLE_NAME] CASCADE;

modify user mapping

ALTER USER MAPPING FOR YOUR_DB_USER SERVER mysql_server OPTIONS (SET password 'PASSWORD');

ALTER USER MAPPING FOR YOUR_DB_USER SERVER mysql_server OPTIONS (SET username 'USERNAME');

Last

Want to say is postgresql It's so easy to use , establish mysql After the appearance can be directly in posgresql Add, delete, modify, and check in

What's more powerful is It can also be implemented with postgresql Table join table query , It's delicious ~, It saves the trouble of configuring data source for application .