Thread: [GENERAL] migrate Sql Server database to PostgreSql

Hi,

At the company we work for, we migrated from Oracle to PostgreSQL "community edition". We have no regrets. PostgreSQL allowed us to expand, we configured it so now we have a better performance compared to before, and we even have streaming replication to mirror our main database.

By the way, to perform the migration from Oracle to PostgreSQL we used OmniDB [1], an open source web tool for database management and migration, written by ourselves. OmniDB supports many well-known RDBMS system, among them Oracle, PostgreSQL and MS SQL Server.

However, OmniDB can only migrate tables, data, indexes, unique constraints, primary and foreign keys. In our case, we had hundreds of procedures and functions, and they were manually translated.

[1] http://www.omnidb.com.br/en_index.aspx

On Thu, Jan 19, 2017 at 03:29:34PM +1100, Venkata B Nagothi wrote:
> On Thu, Jan 19, 2017 at 6:17 AM, PAWAN SHARMA <er.pawanshr0963@gmail.com>
> wrote:
>
> > Hello All,
> >
> > I am using postgres 9.5  enterprise edition.
> >
> > i want to to how to migrate Sql Server database to PostgreSql. what are
> > the things required for migration and what are the cron and prons of
> > migration.
> >
>
> Well, how challenging the migration is going to be purely depends on
> complexity of the the database and the how the Applications. As far as i
> know, there is no open-source tool available to migrate SQL Server database
> to PostgreSQL. You can try "sqlserver2pgsql" which migrates does not
> migrate procedures.
>
> Overall with my experience, i can say, migrating application would be most
> challenging part.
>
> - Preliminary analysis on the database, its object types, data types and
> code complexity of the procedures. T-SQL procedures will need to re-written
> and they can in-turn trigger application code changes which can be
> challenging.
>   Data types and procedures can pose real challenges while migrating
> applications.
> - If applications being used against SQL Server are proprietary, then, you
> need to ensure they work with PostgreSQL.
> - Evaluate the amount of Application code changes to be done, that will
> give you an idea on how difficult migration will be.
>
> Hope that helps !
>
>
> Regards,
>
> Venkata B N
> Database Consultant

Hi,

For the procedure part, you might want to take a look at pgTSQL:

https://bitbucket.org/openscg/pgtsql

Here is another link with some examples. I have not tried them myself.

https://www.bigsql.org/docs/pgtsql/

Regards,
Ken