Working with MySQL and Postgres (need to share/mirror)

[robbLA]

Hi,

I'm in a situation that requires me to share data between a MySQL database on one machine and Postgres on a second. The data in the first (MySQL) is the authoritative and currently I'm just dumping the data out of MySQL and importing it into a fresh Postgres db nightly to use Postgres.

Ideally, I would love to just pick a db and stick with it, but the powers that control that decision won't allow the switch at this time.

My question is:

1. Can I use something like ODBC to allow the two to talk to each other. Speed on the second machine (Postgres) is not critical. It would be a read-only one-way for data flowing from MySQL to Postgres.

2. If no to number 1 above, can I do something to automate the nightly dumps. I've already built the scripts to do the MySQL dumps, but I need to preserve the view/indices/etc in Postgres, basically just replace the data with the current data.

3. Other ideas.

-r --
robb

 

[Leland123]

Hi robbLA,

You might consider using VFP7 application as a front-end. VFP7 works well with both MySQL and Postgres. VFP7's major strength is its rich programming language and speedy cursors. I can pull a cursor or table of 16,500 records from a Postgres backend in between 5 to 8 seconds.

If you used this method you could have the app connect to MySQL and select * form MySQL_table into VFP_cursor. Once the cursor was capture, you could connect to the postgres back-end server and have the VFP7 app insert all the records from the cursor into the postgres table.

Another though is to use comma deliminted files. Have MySQL output a CSV file and then use a "copy to" on the postgres end to pull the text file into the postgres database. Both MySQL and Postgres would need identical schemas or table structures.

It's a pitty you don't have two Postgres tables as you could use a pipe between the two tables to easily perform the backup piping a pg_dump from one table to the other like:

pg_dump -h host1 dbname | psql -h host2 dbname

for me this might translate to

pg_dump -h 192.168.1.78 smvfp | psql -h 192.168.1.117 smvfp

To do this, all that is necessary is to set up a database named smvfp on host2.

Also, postgres has replication abilities. I don't know if MySQL does, but it might be possible to use postgres to replicate your MySQL table. Sorry I can't be of more help here.

LelandJ
Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox