Thread: Moving from SQL Anywhere to PostGres - First Time
Hello, We have 700 user install base using Sybase SQL Anywhere 9.02 We are looking at migrating these installations over to PostGres 1. Very Very Short Answer Please - why should we? 2. Does anyone have a utility or migration application to read SQL Anywhere to go to PostGres 3. Does PostGres handle column descriptions (ie: you can give each column a 50 character description) and then access it via SQL Result Set (like I can do in Sybase) 4. Is there any Date/TimeStamp issues and conversions I need to know about. I use simply a DATE field and a TIME field - but do not use DATE/TIME stamp fields together. 5. What UI/Developer tools (GUI) are available to manage the database as well as add/change columns rather than doing it via SQL commands. Thank you. -Robert
"Robert Paresi" <firstname@lastname.net> writes: > Hello, > > We have 700 user install base using Sybase SQL Anywhere 9.02 > > We are looking at migrating these installations over to PostGres > > 1. Very Very Short Answer Please - why should we? Cheaper? Perhaps faster? It's tough to guess, absent of metrics as to why you might prefer Postgres. > 2. Does anyone have a utility or migration application to read SQL > Anywhere to go to PostGres Usually, what's done here is to use whatever tools are built-in to: a) Pull out the DDL defining the schema; b) Dump the data as a series of INSERT statements. I suggest you take a peek at the "conversion" page on the development wiki: <http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL> > 3. Does PostGres handle column descriptions (ie: you can give each > column a 50 character description) and then access it via SQL Result > Set (like I can do in Sybase) Sure. You can attach comments to virtually any sort of object: <http://www.postgresql.org/docs/9.0/static/sql-comment.html> That page describes how comments can be pulled out. > 4. Is there any Date/TimeStamp issues and conversions I need to know > about. I use simply a DATE field and a TIME field - but do not use > DATE/TIME stamp fields together. TIMESTAMP and TIMESTAMPTZ are the notable "date and time" types in Postgres. They have the fairly considerable merit over DATE + TIME that you can do rather simpler comparisons using "full" timestamps. Thus, you can replace stuff like: select * from txn_table a, other_thing b where a.date_col > b.date_col or (a.date_col = b.date_col and a.time_col > b.time_col) with select * from txn_table a, other_thing b where a.timestamp_col > b.timestamp_col Probably the latter is faster, but the *important* part is that it's enormously simpler, and your developers won't be prone to make mistakes by missing comparisons, getting them wrong, or by having the queries so much more complex that they just don't understand them. > 5. What UI/Developer tools (GUI) are available to manage the database > as well as add/change columns rather than doing it via SQL commands. There's a barrel-load of graphical tools: <http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools> Of the "free software" tools, PGAdmin III is the one that is most strongly maintained alongside the Postgres project, so is quite well commendable. -- http://linuxdatabases.info/info/multiplexor.html Rules of the Evil Overlord #87. "My vats of hazardous chemicals will be covered when not in use. Also, I will not construct walkways above them." <http://www.eviloverlord.com/>
On 1/20/2011 10:37 AM, Robert Paresi wrote: > Hello, > > We have 700 user install base using Sybase SQL Anywhere 9.02 > > We are looking at migrating these installations over to PostGres > > 1. Very Very Short Answer Please - why should we? You should not. Does not sound like you have a problem to solve. If it aint broke, dont fix it. Or maybe you are having problems? A better question might be "Can PG solve ___?" > > 2. Does anyone have a utility or migration application to read SQL > Anywhere to go to PostGres Perl? -Andy
On Thu, 2011-01-20 at 11:37 -0500, Robert Paresi wrote: > Hello, > > We have 700 user install base using Sybase SQL Anywhere 9.02 > > We are looking at migrating these installations over to PostGres > > 1. Very Very Short Answer Please - why should we? * Lower TCO * More flexibility * A growing platform, not a shrinking one (note: I am not arguing about SQL Anywhere and its capabilities. It is a fine database) > > 2. Does anyone have a utility or migration application to read SQL Anywhere > to go to PostGres I don't know of one. > > 3. Does PostGres handle column descriptions (ie: you can give each column a > 50 character description) and then access it via SQL Result Set (like I can > do in Sybase) You are able to use COMMENT ON, with pretty much every object that you can create. > > 4. Is there any Date/TimeStamp issues and conversions I need to know about. > I use simply a DATE field and a TIME field - but do not use DATE/TIME stamp > fields together. We have those types: http://www.postgresql.org/docs/9.0/static/datatype-datetime.html > > 5. What UI/Developer tools (GUI) are available to manage the database as > well as add/change columns rather than doing it via SQL commands. You can use psql (text based), pgadmin, navicat, and about 4 dozen others. Sincerely, Joshua D. Drake > > Thank you. > > -Robert > > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
Robert, We used Data Manager from JP to do this. Worked well. He recently added the ability to read OEM versions of ASA. How's come your moving away from SQL Anywhere? Best Regards Mike Gould "Robert Paresi" <firstname@lastname.net> wrote: > Hello, > > We have 700 user install base using Sybase SQL Anywhere 9.02 > > We are looking at migrating these installations over to PostGres > > 1. Very Very Short Answer Please - why should we? > > 2. Does anyone have a utility or migration application to read SQL Anywhere > to go to PostGres > > 3. Does PostGres handle column descriptions (ie: you can give each column a > 50 character description) and then access it via SQL Result Set (like I can > do in Sybase) > > 4. Is there any Date/TimeStamp issues and conversions I need to know about. > I use simply a DATE field and a TIME field - but do not use DATE/TIME stamp > fields together. > > 5. What UI/Developer tools (GUI) are available to manage the database as > well as add/change columns rather than doing it via SQL commands. > > Thank you. > > -Robert > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
Hi, Price and features. There are some changes in V12 which is causing us to do some steps with our support and resellers. We've always wanted to move away from Sybase so we can make more of a profit off our software and lower costs for us and our clients. Sybase is a wonderful product, and when things stay the same - we stay the same. But, since V12 is requiring us to make internal changes, step changes, documentation changes, training changes ... then it's a good time for us to stop and look at alternatives if we were going to look at them anyway. If we can save $100K a year moving to PostGres and give our customers better pricing, then it only makes sense to move - if PostGres does the same thing that Sybase does. They are a premium software vendor and have a premium product, but charge a premium price for it. They've made huge changes that affect us, and won't make any exceptions or changes for us, so now is the time to start looking at our options. -Robert "Michael Gould" wrote in message news:393bf55cebd45c71fec623552acbd718@intermodalsoftwaresolutions.net... Robert, We used Data Manager from JP to do this. Worked well. He recently added the ability to read OEM versions of ASA. How's come your moving away from SQL Anywhere? Best Regards Mike Gould "Robert Paresi" <firstname@lastname.net> wrote: > Hello, > > We have 700 user install base using Sybase SQL Anywhere 9.02 > > We are looking at migrating these installations over to PostGres > > 1. Very Very Short Answer Please - why should we? > > 2. Does anyone have a utility or migration application to read SQL Anywhere > to go to PostGres > > 3. Does PostGres handle column descriptions (ie: you can give each column a > 50 character description) and then access it via SQL Result Set (like I can > do in Sybase) > > 4. Is there any Date/TimeStamp issues and conversions I need to know about. > I use simply a DATE field and a TIME field - but do not use DATE/TIME stamp > fields together. > > 5. What UI/Developer tools (GUI) are available to manage the database as > well as add/change columns rather than doing it via SQL commands. > > Thank you. > > -Robert > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 01/21/2011 05:10 AM, Robert Paresi wrote: > Price and features. There are some changes in V12 which is causing us to > do some steps with our support and resellers. We've always wanted to > move away from Sybase so we can make more of a profit off our software > and lower costs for us and our clients. Sybase is a wonderful product, > and when things stay the same - we stay the same. But, since V12 is > requiring us to make internal changes, step changes, documentation > changes, training changes ... then it's a good time for us to stop and > look at alternatives if we were going to look at them anyway. OK, so you're facing vendor price push and backward-compat issues. Since you have to make changes anyway, you're trying to decide whether it's worth making somewhat bigger changes to move to a royalty-free platform. Makes sense; you're not just looking at change for change's sake. It'd help if you enumerated the features (outside core SQL) that you use heavily in Sybase, how you access Sybase, what replication if any you use, how you handle backups, all that sort of thing. All that makes a difference. For example, if you're using standard SQL and accessing Sybase via JDBC (or even ODBC), a transition might not be too bad. If you're using native client libraries for Sybase T-SQL and using lots of Sybase-specific extensions, the license cost savings may not be worth the dev costs involved in making a move. > If we can save $100K a year moving to PostGres and give our customers > better pricing, then it only makes sense to move - if PostGres does the > same thing that Sybase does. Imagine a two-circle venn diagram with a large inner overlap. The large inner overlap, consisting of the capabilities and features shared by both databases, is most of the area. There's still plenty on the outside of the shared area that only one database or the other does. What you need to do is figure out how much of that "sybase only" section you use. To do that, you need to figure out how you use Sybase, which features you use are part of the SQL standards, etc. > They are a premium software vendor and have a premium product, but > charge a premium price for it. I tend to think of them as small fry compared to IBM, Oracle and Microsoft. They're certainly more significant than folks like Cache, Progress, etc though. -- Craig Ringer
> Hi, > > Price and features. There are some changes in V12 which is causing us to > do some steps with our support and resellers. We've always wanted to move > away from Sybase so we can make more of a profit off our software and lower > costs for us and our clients. Sybase is a wonderful product, and when > things stay the same - we stay the same. But, since V12 is requiring us > to make internal changes, step changes, documentation changes, training > changes ... then it's a good time for us to stop and look at alternatives > if we were going to look at them anyway. > > If we can save $100K a year moving to PostGres and give our customers > better pricing, then it only makes sense to move - if PostGres does the > same thing that Sybase does. This is quite a jump. I've worked with Sybase on military projects - pretty much all the application logic coded in Sybase stored procedures. This would be a scenario I'm not happy to convert to any other database - maybe except MS SQL because that's historically an off-spring of Sybase and rather similar at the base of things (although a decade of MS intervention sure changed things a lot). However, if your app doesn't rely on stored procedures heavily, the switch may well be worth it. I've used postgresql long before it was even called that - even back in the day when it was postgres and there was no sql whatsoever. Still, this database has yet to fail me. I've written software for government, military and financial industry and most of the projects I worked on where I recommended postgres(sql) as the DB backend are still around, maintained and run like a charm. One of my insurance clients had a postgresql 8.2 database running for well over a year with nobody checking in on it (see -> recession - they fired all their IT staff). The old server is still running just fine - just checked in on it the other day and I was the first to log on in over a year - runs, no questions asked. So yes, if you want a database that just does the job I'd really recommend postgresql. Sure, you can do the same with mucho $$$$ commercial products, but personally I'd rather shoot a message to the mailing list here and get a very professional/knowledegable/qualified answer within hours - that's much better than any commercial big business support I've experienced (usually you call the support for your vendor and you get someone who basically has no clue what you're talking about - been there, done that - worked for Sun Microsystems 3rd level operating system support for years and was always amazed that only few people else could answer the questions of customers who paid 7+ digit support contracts - i.e. BMW, Audi etc.) > > They are a premium software vendor and have a premium product, but charge a > premium price for it. They've made huge changes that affect us, and won't > make any exceptions or changes for us, so now is the time to start looking > at our options. Have to say: here you get a premium product at cost of zip, nada, nothing and when you shout out to the mailing list - and you provide useable information - chances are you'll get premium support from people like Tom Lane. No company I know of can beat that kind of "easy access to those who know what they're talking about" Don't get me wrong: Sybase is a good product and has been around for years. But if you're on your road to change, going open source is definitely an option to look into. To sum it up: I'd put Postgresql on par with Oracle or DB2 any day of the week - except it's not subject to exorbitant license fees and the support is much better. It's not the fastest database on the market, but if you want something that just works relieably, PostgreSQL will serve you well. > > -Robert > > > "Michael Gould" wrote in message > news:393bf55cebd45c71fec623552acbd718@intermodalsoftwaresolutions.net... > > Robert, > > We used Data Manager from JP to do this. Worked well. He recently added > the ability to read OEM versions of ASA. How's come your moving away from > SQL Anywhere? > > Best Regards > > Mike Gould > > "Robert Paresi" <firstname@lastname.net> wrote: > > Hello, > > > > We have 700 user install base using Sybase SQL Anywhere 9.02 > > > > We are looking at migrating these installations over to PostGres > > > > 1. Very Very Short Answer Please - why should we? > > > > 2. Does anyone have a utility or migration application to read SQL > > Anywhere > > > to go to PostGres > > > > 3. Does PostGres handle column descriptions (ie: you can give each > > column > > a > > > 50 character description) and then access it via SQL Result Set (like I > > can > > > do in Sybase) > > > > 4. Is there any Date/TimeStamp issues and conversions I need to know > > about. > > > I use simply a DATE field and a TIME field - but do not use DATE/TIME > > stamp > > > fields together. > > > > 5. What UI/Developer tools (GUI) are available to manage the database as > > well as add/change columns rather than doing it via SQL commands. > > > > Thank you. > > > > -Robert > > -- > Michael Gould, Managing Partner > Intermodal Software Solutions, LLC > 904.226.0978 > 904.592.5250 fax
On Fri, Jan 21, 2011 at 3:16 AM, Uwe Schroeder <uwe@oss4u.com> wrote: > Have to say: here you get a premium product at cost of zip, nada, nothing and > when you shout out to the mailing list - and you provide useable information - > chances are you'll get premium support from people like Tom Lane. No company I > know of can beat that kind of "easy access to those who know what they're > talking about" And with the licensing costs you save, you can even go to the next level and hire truly exceptional consultants who also lurk about on these lists. And Robert, is your email really firstname@lastname.net? If you want to participate, be sure your email replies will work.
On 2011-01-20, Robert Paresi <firstname@lastname.net> wrote: > Hello, > > We have 700 user install base using Sybase SQL Anywhere 9.02 > > We are looking at migrating these installations over to PostGres > 1. Very Very Short Answer Please - why should we? postgres is the best database in the universe. :) > 2. Does anyone have a utility or migration application to read SQL Anywhere > to go to PostGres sed ? Any stock utility is likely to make choices that are sub optimal for your needs. with 700 changeovers pending I would want complete control over how each column is handled. > 3. Does PostGres handle column descriptions (ie: you can give each column a > 50 character description) and then access it via SQL Result Set (like I can > do in Sybase) you can put descriptions on columns, I have not hit a length limit. (probably several megabytes like other text fields). Getting descriptions in the same same result set as column data is probably impractical (see length limit for a possible reason why this has not been implemented). fetching them separately en-masse and caching them or fetching or individually afterwards should not be greatly difficult, resultsets are returned with the column and table indicated. > 4. Is there any Date/TimeStamp issues and conversions I need to know about. > I use simply a DATE field and a TIME field - but do not use DATE/TIME stamp > fields together. time goes upto 24:00:00.999999 just incase you come across a day that long. so unless that's going to cause problems there should be no issues. the range supported by date is starts in pre-history and continues for a few million years into the future. > 5. What UI/Developer tools (GUI) are available to manage the database as > well as add/change columns rather than doing it via SQL commands. I hear there are several, But I can't understand why someone would want to perform non-graphical tasks using a GUI. -- ⚂⚃ 100% natural
> I hear there are several, But I can't understand why someone would want to
> perform non-graphical tasks using a GUI.
Because it is easier.
I've managed to do everything I need - as well as write a full query
editor/report writer for PostGreSQL.
It is being beta tested now, and I plan on releasing it within the next 2 or
3 weeks.
It adds about 8 extensions to the PostGreSQL language - and does many
things.
You'll see why it is nice to use a nice/pleasant GUI interface.
ie:
/* Check to make sure all email addresses have valid domain names */
select lastname,firstname,email from profiles
domain check on email
OR
/* return a list, subtotalling adults and child by state */
select state,lastname,firstname,email,adults,child from profile
order by state
subtotal adults,child on state
OR
/* highlight all credits in red */
select company,balance from accounts
highlight balance where balance < 0 bgcolor=COLOR:RED
-Robert
"Jasen Betts" wrote in message news:ihmf86$98d$1@reversiblemaps.ath.cx...
On 2011-01-20, Robert Paresi <firstname@lastname.net> wrote:
> Hello,
>
> We have 700 user install base using Sybase SQL Anywhere 9.02
>
> We are looking at migrating these installations over to PostGres
> 1. Very Very Short Answer Please - why should we?
postgres is the best database in the universe. :)
> 2. Does anyone have a utility or migration application to read SQL
> Anywhere
> to go to PostGres
sed ? Any stock utility is likely to make choices that are sub optimal for
your needs. with 700 changeovers pending I would want complete control over
how each column is handled.
> 3. Does PostGres handle column descriptions (ie: you can give each column
> a
> 50 character description) and then access it via SQL Result Set (like I
> can
> do in Sybase)
you can put descriptions on columns, I have not hit a length limit.
(probably several megabytes like other text fields).
Getting descriptions in the same same result set as column data is probably
impractical (see length limit for a possible reason why this has not been
implemented). fetching them separately en-masse and caching them or fetching
or individually afterwards should not be greatly difficult, resultsets are
returned with the column and table indicated.
> 4. Is there any Date/TimeStamp issues and conversions I need to know
> about.
> I use simply a DATE field and a TIME field - but do not use DATE/TIME
> stamp
> fields together.
time goes upto 24:00:00.999999 just incase you come across a day that long.
so unless that's going to cause problems there should be no issues.
the range supported by date is starts in pre-history and continues
for a few million years into the future.
> 5. What UI/Developer tools (GUI) are available to manage the database as
> well as add/change columns rather than doing it via SQL commands.
I hear there are several, But I can't understand why someone would want to
perform non-graphical tasks using a GUI.
--
⚂⚃ 100% natural
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2011/1/25 Jasen Betts <jasen@xnet.co.nz>
On 2011-01-20, Robert Paresi <firstname@lastname.net> wrote:postgres is the best database in the universe. :)
> Hello,
>
> We have 700 user install base using Sybase SQL Anywhere 9.02
>
> We are looking at migrating these installations over to PostGres
> 1. Very Very Short Answer Please - why should we?sed ? Any stock utility is likely to make choices that are sub optimal for
> 2. Does anyone have a utility or migration application to read SQL Anywhere
> to go to PostGres
your needs. with 700 changeovers pending I would want complete control over
how each column is handled.you can put descriptions on columns, I have not hit a length limit.
> 3. Does PostGres handle column descriptions (ie: you can give each column a
> 50 character description) and then access it via SQL Result Set (like I can
> do in Sybase)
(probably several megabytes like other text fields).
Getting descriptions in the same same result set as column data is probably
impractical (see length limit for a possible reason why this has not been
implemented). fetching them separately en-masse and caching them or fetching
or individually afterwards should not be greatly difficult, resultsets are
returned with the column and table indicated.time goes upto 24:00:00.999999 just incase you come across a day that long.
> 4. Is there any Date/TimeStamp issues and conversions I need to know about.
> I use simply a DATE field and a TIME field - but do not use DATE/TIME stamp
> fields together.
so unless that's going to cause problems there should be no issues.
the range supported by date is starts in pre-history and continues
for a few million years into the future.I hear there are several, But I can't understand why someone would want to
> 5. What UI/Developer tools (GUI) are available to manage the database as
> well as add/change columns rather than doing it via SQL commands.
perform non-graphical tasks using a GUI.
I can't understand how to develop and maintain even small
data model without diagramming tool which can represent
entities and relationships between them.
Using pure psql and paper with a pen - is nothing more than LOL.
I recommend dbWrench as a diagramming tool for Postgres.
data model without diagramming tool which can represent
entities and relationships between them.
Using pure psql and paper with a pen - is nothing more than LOL.
I recommend dbWrench as a diagramming tool for Postgres.
--
⚂⚃ 100% natural
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
// Dmitriy.
> I recommend dbWrench as a diagramming tool for Postgres. I can also recommend SchemaSpy (http://schemaspy.sourceforge.net/), a superb command line-based schema browser and ERD generator. -- Gary Chambers