Re: Ingres to Postgres migration
От | Andy Colson |
---|---|
Тема | Re: Ingres to Postgres migration |
Дата | |
Msg-id | 53610535.7050401@squeakycode.net обсуждение исходный текст |
Ответ на | Re: Ingres to Postgres migration (Jeff Johnston <jeff.johnston.mn@gmail.com>) |
Ответы |
Re: Ingres to Postgres migration
|
Список | 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
В списке pgsql-general по дате отправления:
Предыдущее
От: Merlin MoncureДата:
Сообщение: Re: Altering array(composite-types) without breaking code when inserting them and similar questions