Wow, thanks for the swift response and a lot of detailed advice! I will look further into what you have suggested and see what bits I can take forward with our migration.
Thanks a lot, Mike On 30 April 2014 15:14, Andy Colson <a...@squeakycode.net> wrote: > On 4/30/2014 8:34 AM, Jeff Johnston wrote: > >> We moved from Oracle to PostgreSQL recently and just used the \copy >> command with csv files. To get the data out of oracle we wrote a small >> php script to connect to Oracle and then generate the csv files. The way >> it works is each csv file represented a database table. The copy command >> is amazingly fast...like faster than you would think is possible. I did >> have to do the database schema by hand, but we only had 110 tables so it >> was pretty easy. And by hand I mean I used an Oracle client tool to grab >> the table/index/sequence creation sql and then copied that into a >> separate file. It worked really well and wasn't that much work. It also >> gave me a chance to clean up the schema. >> >> I did spend some time looking at tools (scripts, frameworks) to do the >> migration automatically but none worked very well. With the \copy >> command you really have all you need. >> >> >> >> >> On Wed, Apr 30, 2014 at 8:06 AM, Michael Bostock <bostoc...@gmail.com >> <mailto:bostoc...@gmail.com>> wrote: >> >> Hi, >> We are using an old version of Ingres (3.0.1) and I have been asked >> to investigate the possibility of migrating to PostgreSQL (9.3.4). I >> was just wondering if there is any general advice you can give me or >> if there are any details/tools that will help with the migration. >> Thanks, >> Mike >> >> >> > tldr: > Tools: a script language > Gotchas: invalid sql (as per the standard), Ingres specific sql > > > > Yep, pretty much same story with me. We moved from MSSql to MySql to > Firebird to PG. (I'm finally happy with PG, I think I'll stay :-) ) > > I have a big sql script to create all the tables and indexes. I wrote > perl to search/replace all the field types I needed changed. We host our > main app on Firebird (FB), so the script is for FB, but the website is on > PG, so perl converts FB to PG, creates the tables n'stuff, then copies over > data (via copy). (DBD::Pg is the best thing ever invented). > > The website is in php (unfortunately) and it used to do a lot of extra > work that I think the database should be able to do. Now that we've been > on PG for a while I've been going through and replacing large stupid parts > of php with a few lines of sql. (If you have to join tables in php, that > is a complete failure of your database) > > Watch out for your old sql that isn't valid. I had some sql that ran in > mysql that turned out to be completely invalid. At first your like, > "Stupid PG, why don't you understand this sql statement". Then you start > researching sql and PG and realize that statement is nonsense, and who know > what mysql was even returning because the question is insane. > > I never had database functions/procedures to migrate, just structure and > data. And with perl that was simple. > > The application moved over pretty easy once I wrote valid sql. There was > maybe one or two spots where I'd used specific mysql syntax to get table > names ("show tables", etc) that needed replaced. Otherwise the sql was > fine. > > -Andy >
