Обсуждение: Converting from pgsql to sqlserver?

Поиск
Список
Период
Сортировка

Converting from pgsql to sqlserver?

От
armindo.dias@dhvmc.pt
Дата:
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



Re: Converting from pgsql to sqlserver?

От
mlw
Дата:
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?


Re: Converting from pgsql to sqlserver?

От
Justin Clift
Дата:
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


Re: Converting from pgsql to sqlserver?

От
Hannu Krosing
Дата:
> 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