Re: Ingres to Postgres migration

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 <andy(at)squeakycode(dot)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 <bostock83(at)gmail(dot)com
>> <mailto:bostock83(at)gmail(dot)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
>