Slashdot Mirror

To say Postgres does replication isn't fully true. There are projects fro PostgreSQL replication but nothing that is should presently be used in production.

> What exactly is hindering a wider acceptance of SAP-DB

Take a look at data types in SAPdb. While they have, for example, date and time types that Oracle lacks, they are implemented as especialization of totally unrelated character strings. This is an ugly hack.

Now contrast that with PostgreSQL's data types. Elegance speaks for itself.

Postgres is free (you may also hear it referred to as Red Hat DB since RH started bundling it as their default RDBMS)

So you should be able to build a installation package for PostgreSQL that is on par with say ActivePython or ActivePerl for Windows, and sell it (you don't have to release your code modifications) for a reasonable amount.

My comment was referring to this. I just was too lazy to look it up last night.

I agree about the conformance -- PostgreSQL is fairly good in that respect. Just as a matter of taste, I prefer MySQL's way of doing certain things over the ANSI standard. YMMV.

(Don't get me wrong. I'm trying to make the switch from MySQL to PostgreSQL because I want the power PostgreSQL has. I'm just finding it hard, because I keep running into these roadblocks where I think "MySQL does this so much simpler!")

You mention that PostgreSQL is clean compared to MySQL. I suppose that could mean many things. After studying the database syntax, I didn't feel PostgreSQL was nearly as "clean" as MySQL.

One token example is escaping data. Depending on the situation, you may need to escape a character with one, two, or even four backslashes.

With most other databases, there is a prepared statement feature that directly transfers data as a separate blob. With PostgreSQL, good luck getting your binary data in.

RE: Docs. There are two books about PostgreSQL online. Buying them is certainly encouraged, but not necessary!

• Platform independence: SAP DB supports Windows better than PostgreSQL does. They both have good support for unices of various flavors.
• Concurrency: Both databases support ACID transactions. PostgreSQL uses multi-version concurrency control so reads and writes won't interfere with each other, while SAP DB uses row-level locking. In this respect, PostgreSQL v. SAP DB is similar to Oracle v. DB2.
• Curriculum vitae: PostgreSQL originated at Berkeley, and has very cool features like MVCC and functional indexes. SAP DB descends from Adabas D, which was marketed as the PC-sized little brother of Adabas for mainframes, and SAP's focus is on providing robust support for business applications such as SAP's customer-relations and supply-chain products.
• Developers: PostgreSQL is developed by a global community of volunteers. SAP DB is developed by a team of employees (FAQ says 100) in a major software company. Development of both products is active.

Live backups under PostgreSQL are easy. For a small database, just run pg_dumpall > backupfile and you have taken a consistant snapshot of all databases on the system without bringing any database down, or blocking any reads or writes.

If you have a more complex system, you probably want to use pg_dump itself on each database, rather than the wrapper script, so you can chose the dump format that best suits your needs.

• PostgreSQL: Introduction and Concepts by Bruce Momjian
• Practical PostgreSQL by John Worsley and Joshua Drake

• PostgreSQL: Introduction and Concepts by Bruce Momjian
• Practical PostgreSQL by John Worsley and Joshua Drake

O'Reilly has a book on PostgreSQL called Pratical PostgreSQL, which was published at the beginning of this year. A bunch of other PostgreSQL books can be found here; Prime Time Freeware has published printed versions of the online documentation (which I did a brief review on the "Use and Administration" portion here.

O'Reilly has a book on PostgreSQL called Pratical PostgreSQL, which was published at the beginning of this year. A bunch of other PostgreSQL books can be found ; Prime Time Freeware has published printed versions of the online documentation (which I did a brief review on the "Use and Administration" portion here.

It didn't have transactions, and the only way to get them now is to use InnoDB (this is NOT used by default), which isn't completely integrated. InnoDB and BDB are taken from other projects, and would be better on their own.

MySQL developers have made claims that transactions and rollbacks are a bad thing! I kid you not.

They claimed one could have atomicity without rollback. Okay, so what do you do if a SQL statement which is part of a (user emulated) "transaction" fails. You are stuck halfway through, or else you might have to do a SQL statement that undoes what you did (good luck). And if that SQL statement fails, you are hosed.

MySQL does not (by default) support Atomicity, Consistancy, Isolation and Durability (ACID).

Their developers appear to not know the meaning of these terms.

MySQL isn't truly open source.

MySQL isn't a real database, it is a SQL interface to a file system. MySQL isn't much better than using flat files, and due to the complexity, is often worse.

PostgreSQL has none of these problems. And the performance is much better than the old versions.

Only use MySQL if you don't care about your data. Yeah, it might be fine for a web counter where if it gets hosed, big deal.

If you care about your data and need a REAL database which is actually Open Source, use PostgreSQL.

See this article:

Why Not MySQL.

Just out of curiosity, why are you bitching about this when there is a far superior alternative?

MySQL needs to just go away.

I'll grant that I have yet to read the text, but what database implementation hasn't ``broken'' SQL by providing extended APIs & semantics wherever (in)appropriate?

At least with Postgres, each SQL command's documentation has a dedicated Compatibility section indicating it's relative standards compliance (often times the functionality of a command will, in Postgres, be a superset of that defined by SQL92, so if you just stay within the bounds of SQL92, you should be fine for most nonesoteric commands). I've found Postgres's documentation to be quite refreshing in this respect. I know exactly where I'm going off the beaten path and how far before I even try to use the command in an implementation. I wish Oracle's docs were that lucid.

I'll admit, you probably wouldn't want to build and enterprise on-line retail site which has millions of transactions per month with a few instances of Postgres, but the author's comments makes me wonder how much time he's actually spent with these tools.

Master/Slave, Update Anywhere , Workload Partitioning (and more) data ownership models are supported.

Which is why the poor-to-nonexistent support for transactions and foreign key relationships make MySQL a sad also-ran for many purposes compared to the expensive proprietary options. But I still hope...)

Good point because of this: Postgres has referential integrity, mySQL has not.

1) First of all, let us not confuse reliability (which is what database software, with ACID transactions, provides on any machine) with high availability (which is what mainframes claim to provide). The problem with spreading servers (and, consequently, data) around is managing data consistency. As others have shown (there have been lots of papers in SOSP, PODC, etc., about issues like consensus, consistency semantics, etc.), there is a tradeoff between availability, consistency, and partitioning/failures (you can only pick two of three). 2) Even to do this tradeoff in the ACID model cleanly, you need true wide-area online replication (as opposed to store-forward, reconcile, log-based replications), which, currently, no database offers (Postgress is planning one for the next major release, but doing it efficiently, and while preserving the ACID properties is very hard). 3) Even with this "back-end" facility, one must start worrying how to maintain application-level (possibly, even session-level) connectivity semantics, which is another big problem.

Ellison quote from the article:

"The problem with traditional database is that you are constantly in an endless upgrade cycle."

What Larry is saying here is that his current business model doesn't have much steam left in it, and it's time for a change to something "non-traditional" that will, of course, require upgrades to just about everything.

Replace "traditional database" with "traditional database vendor", and the picture becomes clearer. The answer is obvious: migrate to a non-traditional database and break the cycle.

Without knowing more about the type of data you're storing, I would recommend putting it in a database. I like PostgreSQL 7.x myself.

For the software, I would organize it in a directory structure and use rsync+ssh to mirror it as needed.

For backup software, use Amanda.

For file sharing, use Samba.

'Nuff said.

The eventual goal of Reiser is a filesystem that is indistinguishable from a powerful database (if a special purpose database)

Why? Why do we need the all-singing, all-dancing filesystem when we've already got database pacakages that are mature and effective?

A filesystem should be a filesystem. You don't see mail applications trying to add features to remotely configure the server they're sending mail to - that's because they stick to what they need to do, mail.

UNIX - do one thing, and do it well. Leave database functionality to the packages that already do it well and have been for more than 10 years.

Perhaps storing the message, attachments, etc. in an RDBMS would be a better way. Give each user a table-space with a table per folder/directory or just each user with a single table. With a decent RDBMS, storage on disk is no longer your concern. This way web, local text/gui, and remote text/gui clients could easily access the same information. There's probably several solutions out there already (with wrappers for your favorite mail clients).

A year and a half from awareness to patch on one of those vulnerabilities. At least. What can a sysadmin do when faced with that?

Why, switch to PostgreSQL, of course! Faster, more secure, source available for verification or modification, closer to SQL-92 and subsequent standards, portable. What more could you want?

Oh, yes: it's free as well as Free.

Keep in mind that Access XP includes a desktop version of SQL server

This is true, but you need to go back a couple years to get to the root of this (fscking stupid) idea.

Visio 2000 installs it by default as well. I can't remember if anything previous did, but that was my first encounter with this. I would love to buy a bag of whatever those in charge of making this idea a reality, but this is not a small thing. You need to consider the hundreds of thousadns (if not into the millions) that are running software that was created 2 or 3 years ago up to now (and the future holds suit as well).

Can someone please remind me why I have to keep using M$ garbage? OOo is a great package. There are MUCH better webservers out there, and there are MUCH BETTER SQL Servers out there.

I just don't get it...

free alternatives

It doesn't seem like much, does it? If you need a database, use Postgres. I've never used clipart, and I have a library of 14 CDs of clipart. I don't need Asian fonts, or Asian sorting. And, "certain file filters" is "uncertain file filters" until it is explained. (I understand that there are WP8 filters.) That's it? That's the only differences?

Well, offhand I'd say one of the following:

MySQL

PostgreSQL

RHDB

Did you factor in support from one of these organizations in your ROI calculations?

While I agree with you that some of the more advanced features of PostgreSQL would confuse someone who is learning SQL (At least Epochs on queries [1] are no longer an issue!) I feel that this is not a major issue as the fact that the features exist, does not mean that a new user will encounter them.

Someone can start out just using the same basic SQL that would work on MySQL, Oracle, DB2 or any other database without having to worry about transactions (PostgreSQL will wrap each statement in a transaction if you do not set up a transaction block) or other concepts beyond using the database as a simple data store.

When the user is starting to encounter tasks that Referential Integrity, server side functions, or transactions could make easier, they don't have to go through the effort of installing another database system, and moving their projects over. They can continue working in the same enviroment, and start using advanced concepts at their own rate.

I suspect that a lot of people continue to use MySQL for projects that have grown beyond what it is suitable for because they do not want to have to go through the trouble of installing another database system, and instead apply far more effort in the creative use of table locks, and extensive application level logic to simulate the capabilities that a more complete database would give them. I suspect that had they learned on a different database, they would be more likely to realize when the time comes to move to a more complete system, as they would not have had the barrier of a platform change discouraging them from learning about these concepts.

Personally, I think someone should start learning on PostgreSQL until they reach the level where they are using transactions and foreign keys. Then they should play with MySQL, and learn how useful it can be for read dominated tasks with lots of simple queries. After getting used to the speed of MySQL, and seeing under what circumstances it falls down, and PostgreSQL pulls ahead, they should experiment with tuning PostgreSQL to give them an introduction to how to tweak a database. After that, they should be ready for the powerful beast that is Oracle.

1. Postgres used to track all of the changes to the database over it's lifetime. By specifying an Epoch for a query, you could run the query on the database as it appeared at a specific point in time in the past. I think this was removed in the change from Postgres95 to PostgreSQL.

While I agree with you that some of the more advanced features of PostgreSQL would confuse someone who is learning SQL (At least Epochs on queries [1] are no longer an issue!) I feel that this is not a major issue as the fact that the features exist, does not mean that a new user will encounter them.

Someone can start out just using the same basic SQL that would work on MySQL, Oracle, DB2 or any other database without having to worry about transactions (PostgreSQL will wrap each statement in a transaction if you do not set up a transaction block) or other concepts beyond using the database as a simple data store.

When the user is starting to encounter tasks that Referential Integrity, server side functions, or transactions could make easier, they don't have to go through the effort of installing another database system, and moving their projects over. They can continue working in the same enviroment, and start using advanced concepts at their own rate.

I suspect that a lot of people continue to use MySQL for projects that have grown beyond what it is suitable for because they do not want to have to go through the trouble of installing another database system, and instead apply far more effort in the creative use of table locks, and extensive application level logic to simulate the capabilities that a more complete database would give them. I suspect that had they learned on a different database, they would be more likely to realize when the time comes to move to a more complete system, as they would not have had the barrier of a platform change discouraging them from learning about these concepts.

Personally, I think someone should start learning on PostgreSQL until they reach the level where they are using transactions and foreign keys. Then they should play with MySQL, and learn how useful it can be for read dominated tasks with lots of simple queries. After getting used to the speed of MySQL, and seeing under what circumstances it falls down, and PostgreSQL pulls ahead, they should experiment with tuning PostgreSQL to give them an introduction to how to tweak a database. After that, they should be ready for the powerful beast that is Oracle.

1. Postgres used to track all of the changes to the database over it's lifetime. By specifying an Epoch for a query, you could run the query on the database as it appeared at a specific point in time in the past. I think this was removed in the change from Postgres95 to PostgreSQL.

Boston, Massachusetts, USA - Friday, March, 1, 2002 - In a decision handed down today in Boston, US District Judge Patti B. Saris ruled on the preliminary injunction motion in MySQL AB vs. Progress Software Corp.

Too bad that they are fighting in court against MySQL, I really liked ProgreSQL...

Who cares? Use a real database...

Quick pointers to NFS U/GID space solutions:

* rpc.ugidd - easy, but insecure. can leak u/gid info to untrusted parties. only works with userspace nfs server in linux - don't know about other opsystems.
* use the same u/gids on every server - almost certainly not an option.
* use a shared PAM back-end, such as LDAP (what I use), MySQL, or PostgreSQL

PostgreSQL has a much better perl based stored procedure language than mySQL has. Unlike the mySQL hack, the Pg implementation is a complete procedure language -- you can even use the normal DBI syntax to run queries against the database.

Seriously, what's the "preferred" way to add a normal, non super user, only has select, insert, update, and delete access to a given database that can connect from the local machine, and remotely. Is this even possible?

Add something like this to your pg_hba.conf:

local sameuser trust
host sameuser 127.0.0.1 255.255.255.255 trust

That's not authentication! "trust" just allows logins, period. Try "psql -U postgres" as anyone on that machine. You'll instantly be logged in as the superuser.

Something like this works fairly well on Postgresql 7.1:

Then enable TCP/IP connections ("tcpip_socket = true" in postgresql.conf)

Very important: make sure your ident server is trustworthy. Many ident servers have an option to allow a user to fake identification. Turn it off.

Also, the config I posted there will let any user connect to any database. That's the simplest, but not the most secure. The "sameuser" in the database field won't be enough to let the superuser connect to databases. You might add a seperate line for that with an ident map containing only postgres (the file would have only the words "postgres postgres" in it, on one line). And then "all" in the database field with that map. I.e., "host all 127.0.0.1 255.255.255.255 ident postgres"

For remote connections, just make sure they have a password in the database:

create user slamb with password '12345';
alter user bob with password 'newpassword';

There's no authentication method here specified for UNIX domain sockets, so they just don't work. You'll need to set the PGHOSTNAME="localhost" environmental variable for stuff to authenticate correctly. I did this because pgsql 7.1 did not support ident on UNIX domain sockets. pgsql 7.2 now does, on certain platforms. (Just replace "host <db> <ip> <netmask> ident <map>" with "local <db> ident <map>")

pgsql 7.2 adds pam support. If your UNIX and PostgreSQL usernames correspond, it should work.

pgsql 7.2 also adds support of encrypted passwords. There's an option for storing password encrypted in the database and an option for challenge-based encryption. I think these methods are incompatible - good challenge-based encryption requires the password be stored in plaintext on the server.

There has been Kerberos auth for some time. I'm trying to switch over to this now, as I'm setting up Kerberos on my network. It's a more complicated system to set up correctly, though. Get something else working first.

Official docs are here

To call PostgreSQL a "seperate" product is not entirely acurate. The reason for the name change (first to Postgres95, and later to PostgreSQL), was because they dumped the PostQuel query language in favor of good ol' SQL. Under the hood, it's still the same DBMS (although somewhat enhanced and several versions more mature).

Go to PostgreSQL.org, and you will discover that in the opening pages of their tutorial is the following line:

The terms "Postgres" and "PostgreSQL" will be used interchangeably to refer to the software that accompanies this documentation.

So if you have a problem with people saying "postgres" instead of "postgreskew-ell", take it up with the folks writing the documentation.

All information about imporant changes you will need to make to your application are documented at the top of the README (under the "Migration" section).

There are potentially some changes you will need to make depending on which of the database features you are using.

To upgrade you will need to pg_dump your database and reload it with 'psql -f'. Again, check the readme.

I've recently set up a master-master replication environnement on Oracle 9i and I did some research to check if it was possible with postgres.

In fact there are many solutions available (check techdocs.postgresql.org for a list...)

The most advanced guys on the subject seem to be the swiss engineering school in Zürich. Here is a list of their publications.
They seem to have developped a replication scheme (Postgres-R) where they have better than linear performance improvement when they add new masters...Quite impressive

Quentin

The TODO list says "A dash (-) marks changes that will appear in the upcoming 7.3 release" but I don't see any dashes.

Actually, the content of the book is at http://www.postgresql.org/docs/awbook.html and there is an article talking about the book-writing process at http://www.postgresql.org/docs/momjian/bookoneyear .html.

Actually, the content of the book is at http://www.postgresql.org/docs/awbook.html and there is an article talking about the book-writing process at http://www.postgresql.org/docs/momjian/bookoneyear .html.

Options are somtimes considered to be a good thing.

Seriously, what's the "preferred" way to add a normal, non super user, only has select, insert, update, and delete access to a given database that can connect from the local machine, and remotely. Is this even possible?

Add something like this to your pg_hba.conf:

local sameuser trust

host sameuser 127.0.0.1 255.255.255.255 trust

I guess another kind of oddity about the pgsql is that out of the box, it only does ident type local socket authetication, no tcp/ip.

We like the default setup to be reasonably secure.

I've looked forever, but I've yet to find a "mysql to postgresql" quick start guide.

try here

Also, would it be darn nice to include a start/stop script that reads only config files and can be linked from /etc/rc2.d/ etc.

It's in contrib/start-scripts. Or you might as well download the RPMs.

Er, it's on the front page of their site.

There is a WAV/MP3 right on their main page... here is a link for those who are too lazy to look.

Right here... jackass!

Sorry, I couldn't help myself...

Highlights of this release are as follows:

• VACUUM: Vacuuming no longer locks tables, thus allowing normal user access during the vacuum. A new "VACUUM FULL" command does old-style vacuum by locking the table and shrinking the on-disk copy of the table.
• Transactions: There is no longer a problem with installations that exceed four billion transactions.
• OID's: OID's are now optional. Users can now create tables without OID's for cases where OID usage is excessive.
• Optimizer: The system now computes histogram column statistics during "ANALYZE", allowing much better optimizer choices.
• Security: A new MD5 encryption option allows more secure storage and transfer of passwords. A new Unix-domain socket authentication option is available on Linux and BSD systems.
• Statistics: Administrators can use the new table access statistics module to get fine-grained information about table and index usage.
• Internationalization: Program and library messages can now be displayed in several languages.

.. with many many more bug fixes, enhancements and performance related changes ...

Commercial Support List here.

It is spelled PostgreSQL, not PostgresSQL.