Обсуждение: 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

> 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