I have a PostgreSQL for my accounting system and a MySQL database for my php website, i want to sync these two database for every update may occurred on PostgreSQL it also updates MySQL database, they are on linux operating system(ubuntu),

How can i do this work?

  • 2
    Why not use PostgreSQL for both? Far better DB! However, there is a MySQL fdw (foreign data wrapper) which may help? Does your website have to be absolutely up to date? You could batch updates every hour/day - send it to .csv from PG every hour and then 5 minutes later, schedule an event in MySQL to pick up those new/changed records? You can use triggers to record INSERTs and UPDATEs - but this is messy. My advice - consolidate your system on PG! – Vérace Oct 17 '18 at 13:03
  • Thanks @ Vérace, your advice is very acceptable, but i have two pre developed systems and i couldn't change the database type for them. i think these steps should be coded in c++ and make this code as a deamon in linux and run it every 5 minutes. – AHAD Oct 17 '18 at 13:12
  • 1
    IMHO, no need to use an external (no matter how brilliantly written by you! :-) ). You can schedule jobs either using cron or pg_cron - not sure about transaction boundaries though? If this has answered your question, I can write it up as an answer? – Vérace Oct 17 '18 at 13:17
  • Your answer is very helpful, but how can write this job by database trigger? – AHAD Oct 17 '18 at 13:34

Two basic approaches.

1) Connect the servers via ForeignDataWrapper, DatabaseLink, Linked Server, External Table, or whatever your source system calls their technology.

2) Replication

Replication changes can be collected by triggers, or puled from database logs. High throughput systems should use a log based solution, although those usually cost money (Oracle GoldenGate, Sybase RepServer, IBM IIDR, etc). You may want to check out https://www.symmetricds.org as an open source alternative.

The downside to #1 is that your transaction success may (or will?) be dependent on BOTH systems being available. Also, you will take a performance hit to update both systems at once.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.