How to daily copy selected data from MySQL and PostgreSQL servers to another PostgreSQL server

Question

I have two source servers, a MySQL 5.7 and a PostgreSQL 9.6, respectively. I would like to daily update some tables from each source to another target PostgreSQL 11 server (via a cron-job).

On the target server I also have some views involving the migrating tables that should update regularly accordingly.

I used pgloader for the initial MySQL -> PostgreSQL data migration and pg_dump for the initial PostgreSQL-> PostgreSQL data migration. However, because of the aforementioned views, the pg_dump command (see below) refuses to alter the tables because of the dependencies. The specific pg_dump command was

sudo pg_dump -x --no-privileges --no-owner --dbname=postgresql://<source_user>:<source_pass>@<source_server>/<source_db> -t 'table_1' -t 'table_2' -c | psql --dbname=postgresql://<target_user>:<target_pass>@<target_server>/<target_db>

How could I run the data update without running into the dependencies issues? Moreover, is there a unified way to achieve the above updates for both MySQL -> PostgreSQL and PostgreSQL -> PostgreSQL? Ideally, I would like to avoid having the views defined as part of the update process. I am running on Ubuntu-18.04-bionic.

Answer 1

What you are looking for is called ETL (extract transform load) tool https://en.wikipedia.org/wiki/Extract%2C_transform%2C_load

There is no FREE/open source pre-built tool that exist to do what you want when moving data from different databases that I am aware of. I have used the paid for ETL tools they just speed up the process coding/typing time. The computer generated scripts always require tweaking to get to work and speed them up.

here is list i found... (no idea how valid this review is, i do not use such tools) https://suyati.com/blog/top-10-etl-tools-salesforce-data-migration/

What you have to do is write scripts that update the tables in sequence you want.

This script can be python, perl, or some other script that runs and moves the data from one server to another.

Another option is use FDW on the PG 11 server to copy the data from the MySQL and PG 9.6 server. Using FDW means it can be done in pure SQL using simple Updates and Inserts. https://www.postgresql.org/docs/current/postgres-fdw.html https://wiki.postgresql.org/wiki/Foreign_data_wrappers

To execute the SQL script use PG Agent for Perl/Pytho/other script language use cron Job.