Thread: Quick question MySQL --> PgSQL
Folks,
Just a quick question ... I need to do a regular transfer (daily + on
demand) of data from a MySQL database to a PostgreSQL database and back
again. Can anybody steer me towards a good script for this, or do I
have to write my own in PHP?
Sorry to bother everyone with something that isn't strictly a SQL
question, but I'm not sure where else to ask.
-Josh Berkus
P.S. If somebody wants consulting $$$ for the above, it may be
available.
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
On Tue, 6 Mar 2001, Josh Berkus wrote: > Just a quick question ... I need to do a regular transfer (daily + on > demand) of data from a MySQL database to a PostgreSQL database and back > again. Can anybody steer me towards a good script for this, or do I > have to write my own in PHP? Don't think there is an actual migration script (I could be wrong, though), but a program using Perl DBI or JDBC would make the data access a bit easier -- if you wrote it generically enough, you could make the data transfer go both ways with just a command-line switch. -- Brett http://www.chapelperilous.net/~bmccoy/ ------------------------------------------------------------------------ Give all orders verbally. Never write anything down that might go into a "Pearl Harbor File".
v7.1's contrib directory has the mysql->pgsql script that was used for the SourceForge migration ... its also downloadable at http://www.pgsql.com->Downloads On Tue, 6 Mar 2001, Brett W. McCoy wrote: > On Tue, 6 Mar 2001, Josh Berkus wrote: > > > Just a quick question ... I need to do a regular transfer (daily + on > > demand) of data from a MySQL database to a PostgreSQL database and back > > again. Can anybody steer me towards a good script for this, or do I > > have to write my own in PHP? > > Don't think there is an actual migration script (I could be wrong, > though), but a program using Perl DBI or JDBC would make the data access a > bit easier -- if you wrote it generically enough, you could make the data > transfer go both ways with just a command-line switch. > > -- Brett > http://www.chapelperilous.net/~bmccoy/ > ------------------------------------------------------------------------ > Give all orders verbally. Never write anything down that might go into a > "Pearl Harbor File". > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
"Brett W. McCoy" wrote: > > On Tue, 6 Mar 2001, Josh Berkus wrote: > > > Just a quick question ... I need to do a regular transfer (daily + on > > demand) of data from a MySQL database to a PostgreSQL database and back > > again. Can anybody steer me towards a good script for this, or do I > > have to write my own in PHP? > > Don't think there is an actual migration script (I could be wrong, > though), but a program using Perl DBI or JDBC would make the data access a > bit easier -- if you wrote it generically enough, you could make the data > transfer go both ways with just a command-line switch. > > -- Brett > http://www.chapelperilous.net/~bmccoy/ > ------------------------------------------------------------------------ > Give all orders verbally. Never write anything down that might go into a > "Pearl Harbor File". > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) there is one in contrib as well there is some through freshmeat.net
On 3/6/01, 5:26:18 PM, Josh Berkus <josh@agliodbs.com> wrote regarding [SQL] Quick question MySQL --> PgSQL: > Folks, > Just a quick question ... I need to do a regular transfer (daily + on > demand) of data from a MySQL database to a PostgreSQL database and back > again. Can anybody steer me towards a good script for this, or do I > have to write my own in PHP? Don't think you'll find much off the shelf - this sort of thing tends to be specific to each person and each project. It's just a matter of SELECTing from MySQL and INSERTing into PostgreSQL in a loop. Be careful with things like times and dates and if you are doing it in PHP make sure you've thought about error recovery. I'd recommend copying into an import table then when it's confirmed that's worked do an internal copy in PG. Remember if it's run from a browser the user can just break the connection. Consider running it from a cron-job with lynx or wget (or write the thing in perl). > Sorry to bother everyone with something that isn't strictly a SQL > question, but I'm not sure where else to ask. > -Josh Berkus > P.S. If somebody wants consulting $$$ for the above, it may be > available. Sadly I charge in £££ and it doesn't sound like a large enough job to make it worth mucking around with conversions. - Richard Huxton
>
>
> there is one in contrib
>
> as well there is some through freshmeat.net
There are two in /contrib. One from pgsql.com, and another from
freshmeat.net:
http://ziet.zhitomir.ua/~fonin/code/
I would like to see these merged someday.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
When I did the same task - I've writting a script on the perl, and if you have stable databese structure, I think, it's a best way - write a script which will run by cron /on-demand. Because, I don't remember exactly why (date format is different or something else) but, I couldn't move data from MySQl to text-file and from text-file to PostgreSQL directly, without transformation. Mar 6, 09:26 -0800, Josh Berkus has written: > Folks, > > Just a quick question ... I need to do a regular transfer (daily + on > demand) of data from a MySQL database to a PostgreSQL database and back > again. Can anybody steer me towards a good script for this, or do I > have to write my own in PHP? > > Sorry to bother everyone with something that isn't strictly a SQL > question, but I'm not sure where else to ask. > > -Josh Berkus > > P.S. If somebody wants consulting $$$ for the above, it may be > available. > > -- > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > ----------------------------- Grigoriy G. Vovk