Thread: Migration from other database systems to PostgreSQL
Dear friends, Because PostgreSQL is a mature database, most new PostgreSQL users are certainly migrating from other systems (mainly Oracle, DB2, MySQL, MS SQL, etc...). So, if we offer solutions for migration, we can probably boost pgAdmin3. This is no news... At first, this could be only a migration of database schemas (tables, constraints, views, etc...). Not server-side languages like PL. In this context, I asked a question recently on hackers about the possibility to create custom data types in PostgreSQL mapping Oracle data types. For example, I asked if it was possible to mapp Oracle nvarchar2(lenght) to PostgreSQL varchar(lenght). The aswer is that it is not possible, because types like "varchar(lenght)" are hard coded into PostgreSQL parser. So, to date, the only solution to read an Oracle ASCII dump into PostgreSQL is to convert the data types manually. Search "nvarchar2(lenght)", Replace by "varchar(lenght)". No news ... now we come to the point. What if I added a "Migration" section on pgAdmin3 web site. At first, this section would only list type mappings for MySQL/Oracle/DB2/MS SQL with PostgreSQL types, as well as any information related to the migration of constraints, views and default values. In a (close?) future, this would allow pgAdmin3 to include a series of Regexp that would replace foreing data types with native PostgreSQL data types. What do you think? Best regards, Jean-Michel
Hi Jean-Michel, There is an item on the todo list to create some more advanced data management tools for pgAmdin. Andreas & I have discussed this briefly and felt it would be a separate program to pgAdmin (though packaged/distributed together) along the line of SQL Server's DTS. I would like to allow some sort of source and target plugins with a mapping/scriptable transformation service in between, perhaps using embedded Python or Perl. This would allow complete flexibility with the user being able to go to or from any supported data type which should include (at least) PostgreSQL (of course), CSV, ODBC and maybe XML. In the meantime, whilst your docs will be useful, I think that a more appropriate place for them is techdocs.postgresql.org... Regards, Dave. > -----Original Message----- > From: Jean-Michel POURE [mailto:jm@poure.com] > Sent: 24 October 2003 15:56 > To: pgadmin-hackers@postgresql.org > Subject: [pgadmin-hackers] Migration from other database > systems to PostgreSQL > > Dear friends, > > Because PostgreSQL is a mature database, most new PostgreSQL > users are certainly migrating from other systems (mainly > Oracle, DB2, MySQL, MS SQL, etc...). So, if we offer > solutions for migration, we can probably boost pgAdmin3. This > is no news... > > At first, this could be only a migration of database schemas > (tables, constraints, views, etc...). Not server-side > languages like PL. > > In this context, I asked a question recently on hackers about > the possibility to create custom data types in PostgreSQL > mapping Oracle data types. For example, I asked if it was > possible to mapp Oracle nvarchar2(lenght) to PostgreSQL > varchar(lenght). > > The aswer is that it is not possible, because types like > "varchar(lenght)" are hard coded into PostgreSQL parser. > > So, to date, the only solution to read an Oracle ASCII dump > into PostgreSQL is to convert the data types manually. Search > "nvarchar2(lenght)", Replace by "varchar(lenght)". > > No news ... now we come to the point. > > What if I added a "Migration" section on pgAdmin3 web site. > At first, this section would only list type mappings for > MySQL/Oracle/DB2/MS SQL with PostgreSQL types, as well as any > information related to the migration of constraints, views > and default values. > > In a (close?) future, this would allow pgAdmin3 to include a > series of Regexp that would replace foreing data types with > native PostgreSQL data types. > > What do you think? > Best regards, Jean-Michel > > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
Le Vendredi 24 Octobre 2003 17:04, Dave Page a écrit : > I would like to allow some sort of source and target plugins with a > mapping/scriptable transformation service in between, perhaps using > embedded Python or Perl. I would call it Advanced advance :) How do you plan to connect to any database? Using which drivers? Is this why you mention ODBC? But ODBC has its own virtual types. Is it reliable? And not all Oracle database have ODBC (you must pay for it). Just for information, can you describe in more details? A good start might be to be able to read the dumps from Oracle, MySQL, DB2 and MsSQL. This would be a small revolution for PostgreSQL and would suit 90% needs of users. When this is achieved, and only then, you would go for more complex things. I feel (but I am only an average programmer as you know) that a series of regexp would suffice. Cheers, Jean-Michel
Dave Page wrote: >Hi Jean-Michel, > >There is an item on the todo list to create some more advanced data >management tools for pgAmdin. Andreas & I have discussed this briefly >and felt it would be a separate program to pgAdmin (though >packaged/distributed together) along the line of SQL Server's DTS. > >I would like to allow some sort of source and target plugins with a >mapping/scriptable transformation service in between, perhaps using >embedded Python or Perl. > > Since we could use wxPython, this is first choice. I feel that we should have the python scripting engine implemented in pgAdmin3 quite soon; I've put in on the TODO list. Regards, Andreas
> -----Original Message----- > From: Jean-Michel POURE [mailto:jm@poure.com] > Sent: 24 October 2003 16:23 > To: Dave Page; pgadmin-hackers@postgresql.org > Subject: Re: [pgadmin-hackers] Migration from other database > systems to PostgreSQL > > Le Vendredi 24 Octobre 2003 17:04, Dave Page a écrit : > > I would like to allow some sort of source and target plugins with a > > mapping/scriptable transformation service in between, perhaps using > > embedded Python or Perl. > > I would call it Advanced advance :) > > How do you plan to connect to any database? Using which > drivers? Is this why you mention ODBC? But ODBC has its own > virtual types. Is it reliable? And not all Oracle database > have ODBC (you must pay for it). Just for information, can > you describe in more details? Database wise, yes, it would be like the pga2 Migration Wizard, allowing you to map incoming ODBC types to outgoing types. > A good start might be to be able to read the dumps from > Oracle, MySQL, DB2 and MsSQL. This would be a small > revolution for PostgreSQL and would suit 90% needs of users. > When this is achieved, and only then, you would go for more > complex things. This is a slightly different approach - mine is more like the old migration wizard, expanded with DTS style transformations,and plugable data sources/targets. > I feel (but I am only an average programmer as you know) that > a series of regexp would suffice. For migrating from a dump maybe, but I want to import/export text, or script data transformations between existing tableson the same or different databases and so on. Don't expect this anytime real soon though - it's one of those ideas I would love to implement but have no real reason toactually do so.... Regards, Dave.
> -----Original Message----- > From: Andreas Pflug [mailto:pgadmin@pse-consulting.de] > Sent: 24 October 2003 16:34 > To: Dave Page > Cc: jm@poure.com; pgadmin-hackers@postgresql.org > Subject: Re: [pgadmin-hackers] Migration from other database > systems to PostgreSQL > > Dave Page wrote: > > >Hi Jean-Michel, > > > >There is an item on the todo list to create some more advanced data > >management tools for pgAmdin. Andreas & I have discussed > this briefly > >and felt it would be a separate program to pgAdmin (though > >packaged/distributed together) along the line of SQL Server's DTS. > > > >I would like to allow some sort of source and target plugins with a > >mapping/scriptable transformation service in between, perhaps using > >embedded Python or Perl. > > > > > Since we could use wxPython, this is first choice. I feel > that we should have the python scripting engine implemented > in pgAdmin3 quite soon; I've put in on the TODO list. Currently I'd love to do the same, I'm just having a hard job figuring out what to use it for! Regards, Dave.
Dave Page wrote: > > > > >>-----Original Message----- >>From: Andreas Pflug [mailto:pgadmin@pse-consulting.de] >>Sent: 24 October 2003 16:34 >>To: Dave Page >>Cc: jm@poure.com; pgadmin-hackers@postgresql.org >>Subject: Re: [pgadmin-hackers] Migration from other database >>systems to PostgreSQL >> >>Dave Page wrote: >> >> >> >>>Hi Jean-Michel, >>> >>>There is an item on the todo list to create some more advanced data >>>management tools for pgAmdin. Andreas & I have discussed >>> >>> >>this briefly >> >> >>>and felt it would be a separate program to pgAdmin (though >>>packaged/distributed together) along the line of SQL Server's DTS. >>> >>>I would like to allow some sort of source and target plugins with a >>>mapping/scriptable transformation service in between, perhaps using >>>embedded Python or Perl. >>> >>> >>> >>> >>Since we could use wxPython, this is first choice. I feel >>that we should have the python scripting engine implemented >>in pgAdmin3 quite soon; I've put in on the TODO list. >> >> > >Currently I'd love to do the same, I'm just having a hard job figuring >out what to use it for! > > It's not for us, more for folks out there that want to contribute some fancy stuff. IMHO we could provide some python objects, representing pgAdmin objects (pgObject, frmMain) so a plugin could extend the tree seamlessly. Regards, Andreas