CDC MYSQL PGSQL

pgsql-mysql-fdw-data.sql

-- STEP 2

-- All these commands in sequence can be run from psql or dbeaver et al

-- create extension
create extension mysql_fdw;

-- create remote server
CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'localhost', port '3306');

-- create user mapping
CREATE USER MAPPING FOR tempuser SERVER mysql_server OPTIONS (username 'root', password 'yourpassword');

-- create foriegn table on pgsql
CREATE FOREIGN TABLE cuser(id INT,name VARCHAR(50),  password VARCHAR(50) ) SERVER mysql_server OPTIONS (dbname 'tempdb', table_name 'cuser');

-- now create table core_user 
CREATE TABLE core_user (id serial PRIMARY KEY,name TEXT,password TEXT,created_at TIMESTAMP DEFAULT NOW());


-- this function is triggered on each insert
CREATE OR REPLACE FUNCTION sync_inserts() RETURNS trigger AS $$ 
DECLARE 
BEGIN
    INSERT INTO cuser(id, name, password) VALUES (NEW.id, NEW.name, NEW.password);  
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- create trigger, without this sync wont work
CREATE TRIGGER sync_inserts
AFTER INSERT ON core_user
FOR EACH ROW EXECUTE PROCEDURE sync_inserts();

pgsql-mysql-fdw-setup.sh

# STEP 1

docker run --name learn_postgres -e POSTGRES_PASSWORD=password -e POSTGRES_USER=tempuser --add-host=host.docker.internal:host-gateway -p 5433:5432 -d postgres:16
docker exec -it learn_postgres /bin/bash
apt update
apt install postgresql-16-mysql-fdw
apt install default-mysql-server
mysql -u root
# run the below command
# ALTER USER 'root'@'localhost' IDENTIFIED BY 'yourpassword';
# exit
/etc/init.d/mariadb restart
# test it once & create table and run the below insert table command
mysql -u root -p tempdb -e "CREATE TABLE  cuser(id INT PRIMARY KEY,  name VARCHAR(50),password VARCHAR(50))"