Create Foreign Table From MySQL Server To PostgreSQL Server Using Docker On Linux Operating System

executeOnTerminal.md

~ Command List :

1. Install chumaky/postgres_mysql_fdw

$ sudo docker pull chumaky/postgres_mysql_fdw

2. Install toleg/postgres_mysql_fdw

$ sudo docker pull toleg/postgres_mysql_fdw

3. Running file : postgres_mysql.alpine.Dockerfile

$ sudo docker build -t postgres_mysql -f postgres_mysql.alpine.Dockerfile .

4. Check postgres_mysql in docker image

$ sudo docker image ls

5. Check postgres_mysql in docker container

$ sudo docker ps

6. Execute docker container names : pg_fdw_test

• pg_fdw_test is docker container names
• postgres first type is roles
• postgres second type is username

$ sudo docker exec -it pg_fdw_test psql postgres postgres

7. Select mysql_fdw in pg_available_extensions table

postgres=# SELECT * FROM pg_available_extensions WHERE name = 'mysql_fdw';

8. Create extension mysql_fdw

postgres=# CREATE EXTENSION mysql_fdw;

9. Create server : mysql_server

postgres=# CREATE SERVER mysql_server
        FOREIGN DATA WRAPPER mysql_fdw
        OPTIONS (host 'your_host_source', port 'your_port_source');

10. Create user mapping for roles : postgres

postgres=# CREATE USER MAPPING FOR postgres
        SERVER mysql_server
        OPTIONS (username 'yuor_username_source', password 'yuor_password_source');

11. Create foreign table in public schemas : your_table_name_destination

postgres=# CREATE FOREIGN TABLE public.your_table_name_destination (
            ID int NOT NULL,
            AC text NULL,
            FlightNO text NULL,
            Datereported text NULL,
            Timereported time NULL,
            CPMPartNumber text NULL,
            ActiveCMCF text NULL,
            FDEMessage text NULL,
            FDECode text NULL,
            CMCMessage text NULL,
            CMCCode text NULL,
            Ata text NULL,
            DateOccur text NULL,
            TimeOccur time NULL,
            FromTo text NULL,
            Phase text NULL,
            LevelFault text NULL,
            NONorFDE text NULL,
            ActiveFault text NULL,
            DetectedByLRU text NULL,
            LogId text NULL,
            Old_LogId int NULL
        )
        SERVER mysql_server
        OPTIONS (dbname 'your_database_name_source', table_name 'your_table_name_source');

12. Create materialized view : mv_your_table_name_destination

postgres=# CREATE MATERIALIZED view public.mv_your_table_name_destination
        TABLESPACE pg_default
        AS (
            SELECT ID AS id,
            AC AS aircraft_registration,
            FlightNO AS flight_number,
            to_date(Datereported,'YYYY-MM-DD') AS date_reported,
            to_timestamp(concat(Datereported, ' ', Timereported), 'YYYY-MM-DD HH24:MI:SS') AS time_reported,
            CPMPartNumber AS cpm_part_number,
            ActiveCMCF AS active_cmcf,
            FDEMessage AS fde_message,
            FDECode AS fde_code,
            CMCMessage AS cmc_message,
            CMCCode AS cmc_code,
            Ata AS ata,
            to_date(DateOccur,'YYYY-MM-DD') AS date_occur,
            to_timestamp(concat(DateOccur, ' ', TimeOccur), 'YYYY-MM-DD HH24:MI:SS') AS time_occur,
            FromTo AS from_to,
            Phase AS phase,
            LevelFault AS level_fault,
            NONorFDE AS non_or_fde,
            ActiveFault AS active_fault,
            DetectedByLRU AS detected_by_lru,
            LogId AS log_id,
            Old_LogId AS old_log_id
                FROM public.your_table_name_destination
        ) WITH DATA;

---

~ Sources List :

• https://github.com/chumaky/docker-images
• https://hub.docker.com/r/toleg/postgres_mysql_fdw
• https://github.com/EnterpriseDB/mysql_fdw

~ Other Source List :

• https://www.digitalocean.com/community/tutorials/how-to-install-and-use-docker-on-ubuntu-22-04

postgres_mysql.alpine.Dockerfile

ARG base_tag=16.2
FROM postgres:${base_tag}

ARG MYSQL_FDW_VERSION=2_9_1
ARG MYSQL_FDW_URL=https://github.com/EnterpriseDB/mysql_fdw/archive/REL-${MYSQL_FDW_VERSION}.tar.gz
ARG SOURCE_FILES=/tmp/mysql_fdw

RUN apt-get install ca-certificates;
RUN apt-get update;

    # compilation deps
RUN apt-get install -y --no-install-recommends wget ca-certificates \
    make gcc \
    postgresql-server-dev-16 libmariadb-dev-compat; \
    # download MYSQL_FDW source files
    mkdir -p ${SOURCE_FILES}; \
    wget -O - ${MYSQL_FDW_URL} | tar -zx -C ${SOURCE_FILES} --strip-components=1; \
    cd ${SOURCE_FILES}; \
    # compilation
    make USE_PGXS=1; \
    make USE_PGXS=1 install; \
    # cleanup
    apt-get purge -y --auto-remove wget ca-certificates \
    make gcc \
    postgresql-server-dev-16; \
    rm -rf ${SOURCE_FILES}

RUN apt-get install ca-certificates