Migrating from SQL Server to Postgres advice

Hi Experts,

I'm looking for advice on migrating from SQL server to Postgres.  Basically there will be a few areas to work on:
   - Changing the database connector
   - I will create the exact same database in Postgres with the equivalent tables/columns/index values
   - update/insert queries will be stubbed to check that tables in both tables are exactly the same (we'll continue to work in parallel until we can pull all queries in a model and switch (as they should have been) depending on which database we're currently using, but we'll avoid that until it's absolutely necessary).
   - result sets from queries will be compared from both databases to detect differences there

So that's our plan for now.  Other ideas please?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
afaik, there is an migration wizard. I would start with that.

Otherwise: Create structural identic check queries in both databases and run "unit" test on those.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
threadyAuthor Commented:
thanks for your answer.

question about my method:
Will I be able to read result sets twice?  If I get a query, I will execute it on SQL server.  I will also execute it on postgres.  I'll loop through both simultaneously, verifying that both rows match.  If not, I found a problem with a query.  If all match, I need to be able to rewind the result set from SQL server so that the client continues on like nothing happened....
threadyAuthor Commented:
I'm hoping for a quick sanity test that will help me find possible errors in hundreds of queries.
ste5anSenior DeveloperCommented:

As you said, you're using the "exact same database", start with row counts for each table. When additive columns exist in tables, create sums over the table.

Review the implementation of your model: does it use triggers? They may introduce differences, when working with the data.

Then do you have database first design? Are those hundreds of queries part of the database? Then you can also run some row count/sum check on those.

Review the usage of the database in your code: Do you use T-SQL features not available in PostreSQL?
threadyAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.