PostgresSQL/Ingres Migration & Usage

Gaz 45 · #1 16-May-2008, 05:23 PM

Alright guys,

I've been asked by work to investigate migrating from SQL Server 2000 to PostgreSQL or Ingres, anyone ever done this before and any tips on how easy/difficult painless/painful it was? And also, anyone use Postgre or Ingres? How are they to use? Mainly from a DBA point of view, the syntaxes seem pretty similar.

The db's that need migrating are production db's with over 1,000 users, have multiple applications accessing them, are involved in transactional replication (for reporting), and all the reports (> 500) would need to be converted.

I've been doing some investigation, but personally, I can't see it being doable, especially as no-one as the company has much if any Postgre/Ingres experience, but any tips/advice/anecdotes are welcome.

The main reason for looking at them is to avoid the cost of SQL 2k5 or 2k8 - as another question, anyone got any views on whether or not it's worth waiting for 2k8 over 2k5?

Cheers
Gaz

ffreeloader · #2 16-May-2008, 06:25 PM

Quote:

“

Originally Posted by Gaz 45

Alright guys,

I've been asked by work to investigate migrating from SQL Server 2000 to PostgreSQL or Ingres, anyone ever done this before and any tips on how easy/difficult painless/painful it was? And also, anyone use Postgre or Ingres? How are they to use? Mainly from a DBA point of view, the syntaxes seem pretty similar.

The db's that need migrating are production db's with over 1,000 users, have multiple applications accessing them, are involved in transactional replication (for reporting), and all the reports (> 500) would need to be converted.

I've been doing some investigation, but personally, I can't see it being doable, especially as no-one as the company has much if any Postgre/Ingres experience, but any tips/advice/anecdotes are welcome.

The main reason for looking at them is to avoid the cost of SQL 2k5 or 2k8 - as another question, anyone got any views on whether or not it's worth waiting for 2k8 over 2k5?

Cheers
Gaz

”

Gaz,

I'm not a highly experienced dba, but I have used both SQL Server and Postgres. I'd say your first step would be to actually install Postgres and use it first. It's very powerful and reliable, but it does have a rather steep learning curve. It's also highly flexible in that you can write stored procedures, triggers, etc... in perl, C, pl/pgsql, and if I remember correctly, python. I'm not positive about the python bit, but for some reason that sticks in my head.

You're going to have a fair amount of development work to port everything over, but you will save a bundle in licensing fees since you can put Postgres on a Linux box and save licensing fees all the way around.

Depending on your budget you might get an experienced Postgres consultant to do your migration. It might be a whole lot cheaper and faster that way. I'd at least talk to a few of them and see what they would charge you.

**hbroomhall** · #3 16-May-2008, 07:29 PM

I have been using Postgres for several years now - so I'll give you my views on it...

Porting is doable, but you will need either to get in Postgres consultants, or accept a long learning curve.

Postgres is very standard compliant, whereas MSSQL tends to bend the standard somewhat. This means that there will be quite a lot of SQL statements that will need to be modified, and the schemas may also need to be modified.

Stored procedures are less well defined in the standard - Postgres is similar to Oracle. So you may need to rewrite some of them.

One classic that often bites people is the casual use of COUNT(*) in their SQL statements. This is quick in MSSQL because of the way it was designed, but is slow in Postgres. Postgres experts usual counter that use of such a construct is a design error anyway!

You mention replication. This is one area that you need to be cautious in, as Postgres has certain types of replication as an add on, but not others. This is why I suggest getting in consultants.

To get some more info on Postgres I would strongly suggest that you get yourself on some of the Postgres mailing lists, and also search the archives - porting is a frequent topic of conversation.

Harry.

**hbroomhall** · #4 16-May-2008, 10:08 PM

This site may also be of help.

Harry.

Gaz 45 · #5 20-May-2008, 05:58 PM

Cheers for the tips guys, and good link Harry!

If we go PostgreSQL it will be installed on a Linux box, and almost certainly we'll get a consultant in.
Going to be playing on an install once I've got my other workload down a bit!
(I've already got a 2k8 CTP release to work on as well)

From what I've seen, most of the SQL we have should be pretty easy to port, except the stored procs.
Porting all the reports will be a right cow too.
Plus my tendency to use dbo.TableName, and ISNULL() (which don't work in Postgre), among others.

Personally I'm leaning towards staying with MS - better the devil you know(!), but I'm keeping a open mind too.

Harry, If i find a COUNT(*), I hunt the author down and give them a slap!