×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Compare Postgresql

Compare Postgresql

Compare Postgresql

(OP)
how does PostgreSQL compare to DB2, What makes it better or worse

RE: Compare Postgresql

Conceptually speaking, there is essentially nothing that DB2 has that PostgreSQL doesn't. By "conceptually" I mean the logical capabilities of a relational database. They will both have transactions, foreign keys, views, triggers, stored procedures, etc...

Now, the differences:

1. Pro-DB2

I'm fairly sure that the main areas where DB2 will differ from PostgreSQL are what the business usually refers to as "value added" features, such as an integrated GUI administration system, (possibly) a smoother install routine, and integrated add-on features such as replication, failover, clustering, etc... Also, of course, there is "official" corporate support for the software.

2. You Be the Judge

DB2 also of course (as most commercial DBMS's) now boasts a whole set of bundled tools for such stuff as XML, SOAP, development libraries, OLAP, etc... These things shouldn't rightfully be called part of the database, because they are application tools, rather than core database features. And of course special support for "complex" datatypes such as multimedia data, etc... I personally think most of these attempts are somewhat counter-productive, as they take the focus off of what should be the main job of the database: providing the proper internal constraints for managing data and providing a logically universal method to query and manipulate the data (the query language). Everything else belongs in application space. It has become a fad lately to bundle everything but the kitchen sink in with a database, when those tools can quite easily be applied separately, as the developer wishes. An example of the most ridiculous side of this is how all the vendors are now saying their databases are "Web-ready", as if it takes a special kind of database to handle data for the internet. (lol) Data is data! These are just marketing techniques designed to get business leaders to try and make the technical decisions they probably should leave to their engineers and developers. See my favorite database website for more opinions in this area: www.dbdebunk.com.

3. Pro-PostgreSQL

ON the contrary from section 2, PostgreSQL supports all of the standard SQL-relational features of the "big business" databases, in a relatively clean and straightforward fashion. The primary download for PostgreSQL is around 9-10 MB, while any other big SQL database usually takes more than 100 MB to download just the base files. Yet it provides all the same relational database constraints. Go figure.

PostgreSQL also does a few extra things that many other databases don't, such as:

1. object-relational inheritance in table attributes. I don't think any other major database supports this feature. It provides for some very interesting programming possibilities that would take far too long to explain here.

2. Spatial (geometric) datatypes, as well as special operators and algorithms for doing queries based on spatial relationships. I believe DB2 has some of these features also, but I don't know if it is as complete as PostgreSQL's

3. User-defined datatypes and operators. With PostgreSQL you can define your own datatype that plays by your own rules. Thus, you can handle any type of "complex" data however you want. In fact, there is a company that has extended postgreSQL's spatial datatypes to include a full GIS package (http://postgis.refractions.net)

4. PostgreSQL is making serious advances on enterprise features such as replication, high availability, clustering, failover, etc... Spend some time at http://techdocs.postgresql.org/ for more info.

5. The source code. Commercial vendors don't give you the source code to your database. With PostgreSQL, if there is really something you aren't sure about, you can (hire someone to) look at the source code to make sure. You can even re-write parts of it for your needs.

6. The source code is free, and not just in the GPL sense. Software that is distributed under the GPL prevents you from re-releasing any project that includes that code, without releasing the code itself, including any of your proprietary code that is included. PostgreSQL is released under the BSD license, which allows you to embed the code in your project without forcing you to release your source along with it. All you need to do is give the PostgreSQL team credit for the database. This is great for any business who might need to include an embedded database engine in a complex project they are releasing.

7. Commercial support? Go to www.pgsql.com, click on "Support". Enough said.

As you can tell, I really like PostgreSQL. I have spent my time with MySQL, and while it makes the easy things easier, it makes the hard things harder. PostgreSQL makes the hard things easier, and in some cases makes the impossible... possible. I'm sure you can't go wrong with DB2, but you also can't go wrong with PostgreSQL, and while it may require a little more effort up front, in the end you won't regret it.

-------------------------------------------

"Now, this might cause some discomfort..."
(http://www.wired.com/news/politics/0,1283,51274,00.html)

RE: Compare Postgresql

Oh, and one more plus for PostgreSQL: most of the commercial DB vendors have chosen to ignore FreeBSD as a platform, which I think is a mistake, since it is the most stable open source OS out there. PostgreSQL supports FreeBSD nicely, as it should, since they both get their research heritage from the same place-- U of C at Berkeley.

-------------------------------------------

"Now, this might cause some discomfort..."
(http://www.wired.com/news/politics/0,1283,51274,00.html)

RE: Compare Postgresql

(OP)
Thank Rycamor, as always you have the best answers.

You should consider writing a book, your writing style and knowledge is consistent with a top technical author.. and you could always have alex help you fill in the Unix blanks..
Talk to you later

RE: Compare Postgresql

Thanks, Rob.

It is something we have been considering, but other projects are still occupying our time. Sometime soon, though, I think we will at least write a few online articles, and then see where that takes us.

-------------------------------------------

"Now, this might cause some discomfort..."
(http://www.wired.com/news/politics/0,1283,51274,00.html)

RE: Compare Postgresql

(OP)
Good luck with that, I really think you are well suited, I think you have a great writing style.. Anyway, I have another question, If I'm running a PostgreSQL DB on a freeBSD machine, can I export (replicate) the DB including the tables, Data and User-defined datatypes and operators to an AS400 running AIX.

Thanks

RE: Compare Postgresql

Exporting and replicating are technically two different things. Replication assumes a constant streaming of data over to another database, so that the two are always in sync.

Exporting any postgreSQL database is pretty easy to do with "pg_dump". Be sure to read the pg_dump manual carefully, though, because there are a few options that can make a difference in certain areas. My standard usage to dump a complete database is pg_dump -C -f /path/to/filename.sql databasename, but there are a few other options to consider, especially with a very large database containing BLOBs. Still, PostgreSQL's dump facility is very simple compared to many large databases. To create a database on the other server with the dumpfile, you can either pipe the dumped file directly to psql, or if you need more flexibility, you can use "pg_restore".

If you want to actually replicate your database onto the AIX machine, then you should definitely spend some time browsing around http://techdocs.postgresql.org/. There is some amazing information buried in that site . Unfortunately (or fortunately, depending on how you look at it), there is no one single integrated solution for PostgreSQL replication, but there are several third-party libraries. So, you are left with the task of evaluating which one will best meet your needs. Here are a few good links for replication:

http://techdocs.postgresql.org/oresources.php (search this page for "replication")
http://pgreplicator.sourceforge.net/
http://techdocs.postgresql.org/techdocs/settinguprserv.php
http://www.taygeta.com/ha-postgresql.html
http://usogres.good-day.net/ (and http://www.rot13.org/~dpavlin/usogres/index.html)
http://dbbalancer.sourceforge.net/

To be honest, I have not tested any of these solutions, so I can't offer you any help in deciding which to use.

-------------------------------------------

"Now, this might cause some discomfort..."
(http://www.wired.com/news/politics/0,1283,51274,00.html)

RE: Compare Postgresql

(OP)
Thanks your the best,
Talk to you later

RE: Compare Postgresql

(OP)
Ok another Question, kind of off the subject but since I got you here..

If I run FreeBSD in Linux compatability mode, can I a specific software or task in Linux compatability mode or does Linux mode apply to every process running.

i.e. Can I run DB2 in Linux compatibility mode and apache web server in normal (freeBSD) mode..

RE: Compare Postgresql

That did not work for me - but I did not trouble shoot beyond a preliminary level.

RE: Compare Postgresql

Running FreeBSD in Linux compatibility mode does not in any way affect FreeBSD software itself. It is only intended as a wrapper for Linux binaries. For example, on one of my previous FreeBSD machines, I ran Java in Linux mode, since the Linux JDK was easier to get working at the time, but the rest of the processes were standard. Fortunately, there is almost no performance loss when running in Linux compatibility mode.

Sometimes it is a bit of a pain to get Linux stuff working in FreeBSD, though. Here is a good article on running DB2 on FreeBSD: http://www.bsdtoday.com/2000/October/Features316.html, and here is one on general Linux compatibility: http://www.bsdtoday.com/2000/August/Features252.html

-------------------------------------------

"Now, this might cause some discomfort..."
(http://www.wired.com/news/politics/0,1283,51274,00.html)

RE: Compare Postgresql

(OP)
Hey Rick,

Do you know if anyone has extended MySQL's spatial datatypes to include a full GIS package

RE: Compare Postgresql

Oh, and speaking of spatial/geographic database applications, I just finished a small but fun project at www.anisite.com (click on the maps). It maintains information about communication tower distances.

-------------------------------------------

"Now, this might cause some discomfort..."
(http://www.wired.com/news/politics/0,1283,51274,00.html)

RE: Compare Postgresql

Rycamor - Great work at www.anisite.com

And thanks for your always very educating posts. You know way more than my professor here at UBC.

RE: Compare Postgresql

(OP)
Rick,
How comfortable is it for a MySQL Designer, to switch to PostgreSQL, are their enough similarities between the 2 to make an easy transistion. Mentally? not mechanically.

RE: Compare Postgresql

The transition depends on your conceptual knowledge of relational databases. For example, there are things you have to do in MySQL that you wouldn't have to do in PostgreSQL. For the most part, though, the difference is that your horizons will be faaaar wider. In other words, your basic methods from MySQL would work with PostgreSQL, but you really would want to go beyond those methods, and learn to work with real data constraints, and abstraction methods.

Mechanically, there are differences, especially in the finer points of syntax for such things as date handling, type casting, string manipulation, etc... Basically, PostgreSQL runs circles around MySQL in all these areas, so the syntax is much richer, and a little more complicated.

See http://forums.devshed.com/showthread.php?s=&threadid=18987&forumid=21 for a discussion on migrating from MySQL to PostgreSQL.

My recommendation: install PostgreSQL, PHP, and phpPgAdmin from phppgadmin.sourceforge.net, and start playing with it. Get a good book, such as "PostgreSQL Developer's Handbook", and start reading. Reading is a must with PostgreSQL.

-------------------------------------------

"Now, this might cause some discomfort..."
(http://www.wired.com/news/politics/0,1283,51274,00.html)

RE: Compare Postgresql

(OP)
Thank you,

Is Ansi SQL an Aplication or a srandard. I talk to some people and they speak of ansi as an app, and some as if it is just a standard, so shoot me a little insight

Thanks

RE: Compare Postgresql

ANSI stands for American National Standards Institute. It is concerned with standards in all kinds of industries, not just computers.

So it is definitely not an spp. It is just the standardization of SQL decided on by the IT branch of ANSI. Every few years, various authorities on SQL get together and decide on the latest version of this standard. I believe ANSI SQL-93 was the last version, and there is another one on the way.

www.ansi.org has more info, but the site seems to be down right now...

No SQL database complies 100% with ANSI standards. They all leave some things out, and add some proprietary methods of their own.

-------------------------------------------

"Now, this might cause some discomfort..."
(http://www.wired.com/news/politics/0,1283,51274,00.html)

RE: Compare Postgresql

(OP)
thank you again,

Next question. do you know of any software that I could integrate with Php or Java, AND MySQL/PostgreSQL, that would enable me to get Latitude/Longitude coordinates from street addresses in the USA.

WOW I Ask a lot of questions..

RE: Compare Postgresql

Do you just want coordinates, or do you want graphical maps with coordinates? If it is the first, then I believe there are publicly available databases, or even purchaseable data snapshots that you can get. Maybe from the USGS, or even the US Post Office.

The two remote services I have worked with are www.mapsonus.com and www.maptech.com. Each one just provides a simple HTTP GET/POST services, which provides maps to the browser, with a fairly simple list of parameters. This made my job harder, since I had to do a log of logic on the PHP side, assembling arrays of values into strings to post to the remote server.

Maptech apparently also sells a few other interesting things, such as a standalone dedicated mapping server (http://www.maptech.com/land/MapServerPro/index.cfm)/. I have never worked with this, but it looks interesting. MapsOnUs has a fairly limited service, but at least they are cheap. All the others wanted at least 2-3 times as much per year to rent the service.

Really, there are a lot of different services out there that can do what you want, and I'm sure that for the right price, there are some that are configured as a full web servics, with XML/SOAP methods, etc... to make it far easier to connect to a remote API. It just comes down to defining exactly what you want to do, and then examining a lot of choices, to see what they can offer. Most of these systems allow you to do a "test drive" for free, or even use a limited part of the API in production for free.

Beyond that, there is no software I know of that is specifically set up to integrate the two, although I have done some work in that area with PHP. It really is not that complicated if you just want to send address requests to a mapping server and get the coordinates back.

-------------------------------------------

"Now, this might cause some discomfort..."
(http://www.wired.com/news/politics/0,1283,51274,00.html)

RE: Compare Postgresql

(OP)
Well it is 2 parts,

1. I need to send address requests to a mapping server and get the coordinates back. "Simple, you've helped me do that almost a year ago." - Now I just need to find a service that I'm not borrowing from.

2. I need to send two addresses or coordinates (doesn't matter) to a mapping server to retrieve road distance between the two addresses.

Do you know of any software you could buy to have on a local server, so you don't need to Post/Get, in order to retrieve info?

RE: Compare Postgresql

If you want it to run locally, you will probably have to shell out at least a five-digit amount for the software, and regular payments to update the data. I believe that ESRI (www.esri.com) is the biggest provider of such things. Basically, if it is possible with GIS and mapping, they have done it. However, it costs. I haven't done any production work with ESRI software, although I played with some test stuff a few years back. There are plenty of other providers of this stuff, but I have no direct experience with them.

I don't know of any specific library to interface PHP with a commercial GIS system such as ESRI's, but it can't be too hard. Really, what you want is to query a database, and I'm sure some of these products provide standard database connectivity, such as ODBC or JDBC.

A lot of the raw data that is used in these GIS packages is actually available for free, if you know where to look. Hey our taxes paid for this stuff, so it ought to be freely available from the government. So, if your needs are fairly well-defined (as seems to be the case), it might be worth looking into this. It would require some more work up front, but you would not be bound to any specific vendor, and I'm almost positive it would cost less.

One of the things you have to consider when you host this data locally is performance. This is a large dataset, and if you want to do anything useful with it, you will need to do some real research into server/OS capability with this amount of data.

Hope this helps. I can't help you much beyond this, without doing some real research .

-------------------------------------------

"Now, this might cause some discomfort..."
(http://www.wired.com/news/politics/0,1283,51274,00.html)

RE: Compare Postgresql

(OP)
Alright I've Got more questions..

What are my options for setting up SSL, I'm running FreeBSD/Apache.

Have you worked with OpenSSL "http://www.openssl.org";

Do you know of any Free solutions to SSL, and what are my SSL requirments.

Thank you

RE: Compare Postgresql

(OP)
Thank you, I will await his answer

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close