.::: Access MariaDB from PostgreSQL using dblink /FOREIGN DATA WRAPPER mysql_fdw :::.

1. install mysql_fdw
wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-7.9-x86_64/mysql_fdw_15-2.8.0-2.rhel7.x86_64.rpm
yum install mysql_fdw_15-2.8.0-2.rhel7.x86_64.rpm -y

2. create extension;

teguhth=# \c edbmaria;
You are now connected to database "edbmaria" as user "postgres".
edbmaria=#

edbmaria=# CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
edbmaria=#
SELECT * FROM pg_extension;
 

3. create remote server for mariadb
CREATE SERVER remote_maria
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (
   host '10.10.10.8',
   port '3306',
   init_command 'SET NAMES utf8',
   character_set 'utf8'
);

select * from pg_foreign_server;
 

4. maping user

CREATE USER MAPPING FOR postgres SERVER remote_maria
OPTIONS (username 'admin', password 'admin');

select * from  pg_user_mappings;
 
5. create schema * import

CREATE SCHEMA local_maria;
IMPORT FOREIGN SCHEMA teguhth from SERVER remote_maria into local_maria;
select * from information_schema.foreign_tables;
 

6. test testing table
6.1 check foreign table before insert in mariadb

select * from local_maria.barang;
select * from local_maria.pembelian;

edbmaria=# select * from local_maria.barang;
 KODE_BARANG
| NAMA_BARANG | SATUAN_BARANG | STOK_BARANG
-------------+-------------+---------------+-------------
 ELK-01      | RICE COOKER | BUAH          |          20
 ELK-02      | LEMARI ES   | UNIT          |           8
 ELK-03      | TELEVISI    | UNIT          |          30
 ELK-04      | RADIO/TAPE  | BUAH          |          35
 ELK-05      | KOMPUTER    | UNIT          |          28
 ELK-06      | KIPAS ANGIN | BUAH          |          38
 ELK-07      | KIPAS ANGIN | BUAH          |          38
 ELK-09      | KIPAS ANGIN | BUAH          |          38
(8 rows)

edbmaria=# select * from local_maria.pembelian;
 KODE_PEMBELIAN | KODE_BARANG | KODE_CUSTOMER | TANGGAL_PEMBELIAN | JUMLAH_PEMBELIAN
----------------+-------------+---------------+-------------------+------------------
 BEL-E001       | ELK-01      | J-0001        | 2002-05-20        |                3
 BEL-E002       | ELK-01      | J-0001        | 2002-05-21        |                4
 BEL-E003       | ELK-01      | J-0002        | 2002-05-20        |                2
 BEL-E004       | ELK-01      | B-0001        | 2002-05-20        |                2
 BEL-E005       | ELK-01      | B-0002        | 2002-05-22        |                3
(5 rows)

edbmaria=#
 

6.2 insert table in mariadb

select * from teguhth.pembelian;
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E006','ELK-02','J-0001','2002-06-24',1);  
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E007','ELK-02','J-0002','2002-06-24',1);  
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E008','ELK-02','B-0001','2002-06-25',2);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E009','ELK-02','B-0002','2002-06-25',2);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E010','ELK-03','J-0001','2002-06-20',5);
select * from teguhth.pembelian;
 

6.3 check foreign table after insert in mariadb

select * from local_maria.barang;
select * from local_maria.pembelian;