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.