Preface

PostgreSQL can access MySQL (or MariaDB) through mysql_fdw,

How does MySQL access PostgreSQL?

The answer is CONNECT Store Engine.

MariaDB supports CONNECT Store Engine from version 10.0.2.

The CONNECT storage engine enables MariaDB to access external (local or remote) data (MED). This is based on different data types (especially files in various formats), through ODBC or JDBC from other DBMS or products (such as Excel or MongoDB) extracted data or data retrieved from the environment (such as DIR, WMI) Define the table to complete and MAC table).

The storage engine supports table partitions, MariaDB virtual columns, and allows the definition of special columns, such as ROWID, FILEID, and SERVID.

This article introduces how Ubuntu MariaDB can access Postgres through the CONNECT plug-in.

environment

MariaDB 10.4

• host: 127.0.0.1

• port: 3306

• username: root

• password: pass

PostgreSQL 12.2

• host: 192.168.1.6

• port: 5433

• username: postgres

• database: postgres

• password:

Ubuntu 18.04 LTS

Install the CONNECT plugin

$ apt-get install mariadb-plugin-connect1

mysql> INSTALL SONAME 'ha_connect';1

Install ODBC-PostgreSQL

Install ODBC-PostgreSQL on MariaDB server

$ apt-get install unixodbc odbc-postgresql1

Configure ODBC.ini

cat >>/etc/odbc.ini <<EOF
[pg12]
Description = PostgreSQL
Driver      = PostgreSQL Unicode
Trace       = No
TraceFile   = /tmp/psqlodbc.log
Database    = postgres
Servername  = 192.168.1.6
UserName    = postgres
Password    =
Port        = 5433
ReadOnly         = Yes
RowVersioning    = No
ShowSystemTables = No
ShowOidColumn    = No
FakeOidIndex     = No
ConnSettings     =
EOF

Test odbc

isql -v pg121

Test CONNECT

create database postgres
use postgres;
CREATE TABLE iris (
  sepal_length double(12,2) NULL,
  sepal_width double(12,2) NULL,
  petal_length double(12,2) NULL,
  petal_width double(12,2) NULL,
  species varchar(20) NULL
)ENGINE=CONNECT TABLE_TYPE=ODBC tabname='mysql.iris'
CONNECTION='DSN=pg12' 
;
select * from iris limit 10;

mysql> show create table postgres.iris;
>>Back
CREATE TABLE `iris` (
`sepal_length` float DEFAULT NULL,
`sepal_width` float DEFAULT NULL,
`petal_length` float DEFAULT NULL,
`petal_width` float DEFAULT NULL,
`species` varchar(20) DEFAULT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1
CONNECTION='DSN=pg12'
`TABLE_TYPE`=ODBC `tabname`='mysql.iris'

success.

refer to

https://mariadb.com/kb/en/connect/

https://mariadb.com/kb/en/connect-odbc-table-type-accessing-tables-from-another-dbms

https://odbc.postgresql.org/

Reference connection:

How does MySQL access Postgres: https://mp.weixin.qq.com/s/XSRh1IKgBXz5QxShRtr0Nw