If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Best Answer

"When migrate databases to Postgres we usually just re-create all tables in Postgres either by doing smart search and replace on the original SQL scripts or by adjusting our Liquibase changesets to get rid of things that would not work in Postgres. Then we just dump the original database to CSV and import that into Postgres. We mainly use SQL Workbench/J for that sometimes we only use it for dumping data and then using Postgres' COPY command to import it, because that is a bit faster. It also has the ability to dump a database schema in a somewhat DBMS independent XML file that can be converted to a different SQL dialect through XSLT.

what are the challenges involved? precautions that we need to take.

The easy part are the syntax differences e.g. "||" vs "+" for string concatenation.

As Postgres doesn't support cross-database queries and you use that in SQL Server you most probably want to map each database to a schema (unless you also use multiple schemas in a single database - then things will become a bit more complicated).

You need to learn that for Postgres every statement has to be properly terminated with a ;

Postgres (like many other DBMS) is also case sensitive when comparing strings - unlike SQL Server (at least with the default installation).

One thing that SQL Server users seem to stumble upon very often are UPDATE statements using JOINs. In SQL Server you have to repeat the target table in the FROM clause.

You must not do that in Postgres.

The following T-SQL update statement:

Code:

update foo
set foo.x = bar.y
from foo, bar
where foo.id = bar.foo_id

needs to be re-written to:

Code:

update foo
set foo.x = bar.y
from bar --<< only the other table is listed here!
where foo.id = bar.foo_id

Another thing that might bite you is that SQL Server will not allow multiple NULL values in a unique index, whereas Postgres will. The following will fail in SQL Server, but will work in Postgres

When migrate databases to Postgres we usually just re-create all tables in Postgres either by doing smart search and replace on the original SQL scripts or by adjusting our Liquibase changesets to get rid of things that would not work in Postgres. Then we just dump the original database to CSV and import that into Postgres. We mainly use SQL Workbench/J for that sometimes we only use it for dumping data and then using Postgres' COPY command to import it, because that is a bit faster. It also has the ability to dump a database schema in a somewhat DBMS independent XML file that can be converted to a different SQL dialect through XSLT.

what are the challenges involved? precautions that we need to take.

The easy part are the syntax differences e.g. "||" vs "+" for string concatenation.

As Postgres doesn't support cross-database queries and you use that in SQL Server you most probably want to map each database to a schema (unless you also use multiple schemas in a single database - then things will become a bit more complicated).

You need to learn that for Postgres every statement has to be properly terminated with a ;

Postgres (like many other DBMS) is also case sensitive when comparing strings - unlike SQL Server (at least with the default installation).

One thing that SQL Server users seem to stumble upon very often are UPDATE statements using JOINs. In SQL Server you have to repeat the target table in the FROM clause.

You must not do that in Postgres.

The following T-SQL update statement:

Code:

update foo
set foo.x = bar.y
from foo, bar
where foo.id = bar.foo_id

needs to be re-written to:

Code:

update foo
set foo.x = bar.y
from bar --<< only the other table is listed here!
where foo.id = bar.foo_id

Another thing that might bite you is that SQL Server will not allow multiple NULL values in a unique index, whereas Postgres will. The following will fail in SQL Server, but will work in Postgres

Indeed it's a good summary of migration process that will definitely help us. Thank you so much for your time.

One more point that's bothering us is if SQL Server has portioned tables how it'll be migrated to Postgres. Some of our customers are having portioned tables. Any pointers in this direction will definitely help us.

In my experience you can go a long way in Postgres without partitioning. With the current implementation I would only consider it for administrative purposes, e.g. when you need to delete a large amount of data from a large table, e.g. delete 10 million rows out of 100 million on a regular basis. In those cases simply dropping the partitions will be a lot faster.

Partitioning is being worked on in Postgres and a "proper" implementation will very likely make it into the version after the upcoming 9.6 (which is currently in beta). So something like end of next year.

If you do decide that you do need partitioning in Postgres you probably want to use something like pg_partman to make managing the partitions easier: https://github.com/keithf4/pg_partman