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

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