Re: Ingres to Postgres migration - Mailing list pgsql-general

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@gmail.com
> <mailto:bostock83@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