Thread: Ingres to Postgres migration
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
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.
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> 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
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
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@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 <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