Обсуждение: Converting from pgsql to sqlserver?
Greetings. I don´t know if this is the right place to ask this, sorry if this don't belong here. I begun this week working in a new firm. They use linux and PostgreSQL as the database for the Intranet site and for the management of CV's and Knowledge Management (they have an on-line system to manage and search information about workers of the firm and projects they have). They now want to convert from the current linux/postgresql platform to Windows and SQLServer. I've never worked before with linux or PostgreSQL so i know nothing about the capabilities of this combo. My question are: - the PostgreSQL database has a lot of information. Is it possible to migrate the data of the PostgreSQL to SQLServer in Windows? What do i need to do? - is it possible to migrate the tables relationships (the relational schema) of the DB to SQLServer or do i have to build the DB from scratch? Thanks for reading, Armindo Dias
armindo.dias@dhvmc.pt wrote: > > Greetings. > I don´t know if this is the right place to ask this, sorry if this don't > belong here. > I begun this week working in a new firm. They use linux and PostgreSQL as > the database for the Intranet site and for the management of CV's and > Knowledge Management (they have an on-line system to manage and search > information about workers of the firm and projects they have). > > They now want to convert from the current linux/postgresql platform to > Windows and SQLServer. I've never worked before with linux or PostgreSQL so > i know nothing about the capabilities of this combo. I have no idea under what circumstance one would wish to go from PostgreSQL to MicrosoftSQL, it just seems like a mistake, but hey, you'll be back when you realize how much it costs and how much you lose. > > My question are: > > - the PostgreSQL database has a lot of information. Is it possible to > migrate the data of the PostgreSQL to SQLServer in Windows? What do i need > to do? PostgreSQL has a lot of export utilities. I'm not sure what MSSQL currently supports. Find a match, and do the export/import. You may be able to use "pg_dump" to dump out the database as a series of SQL inserts. It will be slow, but it should be the more portable way to do it. Just be carfull of date formats. You will probably need a few tries. > > - is it possible to migrate the tables relationships (the relational schema) > of the DB to SQLServer or do i have to build the DB from scratch? You will need to dump out the schema for the postgres database by executing "pg_dump -s database" and that will dump out a SQL script which will create the schema. no doubt, you will have to edit it to make it work with MSSQL but, it is a good start. May I ask why you are going from PostgreSQL to MSSQL?
Hi Armindo, Ian Harding has written a guide for converting from MS SQL Server to PostgreSQL. I know this is the opposite of what you want, but it might be useful as it highlights some of the areas of difference between these products : http://techdocs.postgresql.org/techdocs/sqlserver2pgsql.php Hope that's useful. :-) Regards and best wishes, Justin Clift armindo.dias@dhvmc.pt wrote: > > Greetings. > I don´t know if this is the right place to ask this, sorry if this don't > belong here. > I begun this week working in a new firm. They use linux and PostgreSQL as > the database for the Intranet site and for the management of CV's and > Knowledge Management (they have an on-line system to manage and search > information about workers of the firm and projects they have). > > They now want to convert from the current linux/postgresql platform to > Windows and SQLServer. I've never worked before with linux or PostgreSQL so > i know nothing about the capabilities of this combo. > > My question are: > > - the PostgreSQL database has a lot of information. Is it possible to > migrate the data of the PostgreSQL to SQLServer in Windows? What do i need > to do? > > - is it possible to migrate the tables relationships (the relational schema) > of the DB to SQLServer or do i have to build the DB from scratch? > > Thanks for reading, > Armindo Dias > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
> armindo.dias@dhvmc.pt wrote: > > > > Greetings. > > I don´t know if this is the right place to ask this, sorry if this don't > > belong here. > > I begun this week working in a new firm. They use linux and PostgreSQL as > > the database for the Intranet site and for the management of CV's and > > Knowledge Management (they have an on-line system to manage and search > > information about workers of the firm and projects they have). > > > > They now want to convert from the current linux/postgresql platform to > > Windows and SQLServer. If you stay away from IIS (and Internet as a whole) you should be quite safe on Windows platforms ;) see: http://news.cnet.com/news/0-1003-200-7294516.html?tag=mainstry > > I've never worked before with linux or PostgreSQL so > > i know nothing about the capabilities of this combo. Should be capable enough ;) PG dump produces mostly ANSI-SQL compatible script that will recreate the database. (Older versions dump suome stuff, like foreign keys as system table modifications, so you have to modify at least that ). You may also have to change type names, as pg_dump uses often original postgreSQL type names and not their ANSI equivalents. (It is meant to be primarily a backup tool, not porting tool) The PL/PGSQL and Transact-SQL (or whatever MS calls it) are quite different though, so any stored procs have to be rewritten. > > My question are: > > > > - the PostgreSQL database has a lot of information. Is it possible to > > migrate the data of the PostgreSQL to SQLServer in Windows? What do i need > > to do? pg_dump -D -a dbname > > > > - is it possible to migrate the tables relationships (the relational schema) > > of the DB to SQLServer or do i have to build the DB from scratch? pg_dump -s dbname ------------------- Hannu