Slashdot Mirror

Because there are potentially large performance gains to be had in VMs running postgres by running a 3.x kernel, which CentOS doesn't use yet. Fedora or Ubuntu server are what i'm going to look at to see if it's of use to me.

This is an excellent example of why that sort of thing is avoided by risk-adverse deployments. Early adopters of PostgreSQL on various 3.X kernels are still seeing a variety of nasty kernel issues, and many of them are rolling back to the stable RHEL or Debian kernels based on 2.6.32 to avoid them. A good example is High CPU usage / load average after upgrading to Ubuntu 12.04. I'm tracking about 5 such PostgreSQL issues that only show up in 3.X kernels we're trying to get sorted out still. (I'm a PostgreSQL contributor) Yes, the 3.X kernels are faster in general, but they're still not very stable compared to the boring old ones in CentOS.

Do please check out this informative post from Magnus Hagander, one of the PostgreSQL core team members, which clarifies most of the points raised here:

About security updates and repository "lockdown"

I have received a lot of questions since the announcement that we are temporarily shutting down the anonymous git mirror and commit messages. And we're also seeing quite a lot of media coverage.

Let me start by clarifying exactly what we're doing:

• We are shutting down the mirror from our upstream git to our anonymous mirror
• This also, indirectly, shuts down the mirror to github
• We're temporarily placing a hold on all commit messages

There has been some speculation in that we are going to shut down all list traffic for a few days - that is completely wrong. All other channels in the project will operate just as usual. This of course also includes all developers working on separate git repositories (such as a personal fork on github).

We are also not shutting down the repositories themselves. They will remain open, with the same content as today (including patches applied between now and Monday), they will just be frozen in time for a few days.

...continues...

The git source is still available (http://git.postgresql.org/gitweb/?p=postgresql.git;a=summary); it is only the patches for the bug-in-question that are closed off. This seems entirely reasonable given the severity of this vulnerability.

So, go to http://git.postgresql.org/gitweb/?p=postgresql.git;a=summary and look at the source.

What they've taken private is their patches for the problem until they can make it production ready.

You are still fully able to access everything you've always had access to, they've just decided not to share their newest patches for a few days/weeks until people have at least a chance to protect their systems.

Regression tests have to be run, repos need a chance to update their binary packages, all sorts of things can be done in private and made ready so that when the changes are made public ... and it becomes trivial to exploit the bug in unpatched versions since the changes show you the exploit ... users are already able to update to a fixed version.

They just aren't telling the world where the bug is until the patch has been properly distributed. You can still go look for it yourself if you want, unless you want the bad guys to know where its at and you STILL won't have a patch available. Remember, these are the guys who are making the patch so you're waiting on them for the fix regardless.

Since they use git ... I would say that would be what happened.

Linked from their downloads page is this:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=summary

And its still fully accessible.

And from Postgres we have:
http://www.postgresql.org/about/news/1454/

This is a major security issue and it affects *ALL* versions of postgres. Locking it down while updates are being created seems the right way to do it to me...

GPU acceleration might come in handy if you do any sort of video editing.

There is a lot more to GPUs than video and bitcoins.

The ever increasing power of commodity processors is what makes my business of inexpensive data crunching possible. 10 years ago the kinds of things we do would require a supercomputer. Today it requires a moderately prices server-class machine.

However I am drooling at the thought of using something like PGStrom. GPU based database queries.

I created a Reliable Writes page for PostgreSQL that talks about this and gives some known good and bad examples. Intel's 320 and 710 drives are the only two SATA SSDs still on the market that have survived the tests for clean shutdown I've advocated everyone run. They are units with a supercapacitor to enable power failure cleanup. If a drive doesn't have a battery for that sort of purpose, you will lose data at shutdown one day. And, no, a UPS is no cure, because all it takes to ruin a system on one is someone tripping over a cord at the data center to destroy the whole thing.

PostgreSQL has a wonderful wiki todo list. Just pick your task.

My pet peeves are on domains, localisation, derived relations, and integrity constraints.

PostgreSQL has a wonderful wiki todo list. Just pick your task.

My pet peeves are on domains, localisation, derived relations, and integrity constraints.

PostgreSQL has a wonderful wiki todo list. Just pick your task.

My pet peeves are on domains, localisation, derived relations, and integrity constraints.

PostgreSQL has a wonderful wiki todo list. Just pick your task.

My pet peeves are on domains, localisation, derived relations, and integrity constraints.

PostgreSQL has a wonderful wiki todo list. Just pick your task.

My pet peeves are on domains, localisation, derived relations, and integrity constraints.

Postgres has a case-insensitive data type extension available called citext.

The closest thing to case-insensitive collation is the citext data type. It works basically transparently as a case-insensitive replacement for varchar and text.

The major feature keeping $dayjob from using PostgreSQL over MSSQL in new development is the lack of an accent-insensitive collation. Making an index using a custom function marked IMMUTABLE that calls (lower(unaccent(text)), and then calling the same function in nearly every query, is simply too hackish to stomach.

Also, some (almost all?) ODBC and database servers send passwords in the clear.

Many database servers allow encrypted passwords, but there are surely a lot of database installations that don't take advantage of it. In PostgreSQL you can use SSL for the client network connection, which ODBC passes through. Setup SSL as the only way to connect, and encryption has to happen before it hits the wire. MySQL has a similar trick. Both are just using the OpenSSL library under the hood to encrypt the network traffic.

On the commercial side, Oracle does the same thing with ORA_ENCRYPT_LOGIN. SQL Server has client and server settings that enforce encryption. Basically, if your database traffic isn't encrypted, it's more likely because someone didn't think that was important than because it was impossible. It's a simple checkbox to add to database selection requirements, and it's not hard to find a DBMS that has the capability.

I find people who just stuff user passwords into the database (which can be the same passwords as other services) rather than putting password encryption into their application can also leak data. In PostgreSQL using the built-in pgcrypto makes that easy. You also have to be careful to use the same network encryption approach for any replication client, or it's possible to just sniff that instead to get the data. In Postgres those connect with the same encryption possible options as any other client. Most of the tutorials on setting up replication don't cover this though.

Also, some (almost all?) ODBC and database servers send passwords in the clear.

Many database servers allow encrypted passwords, but there are surely a lot of database installations that don't take advantage of it. In PostgreSQL you can use SSL for the client network connection, which ODBC passes through. Setup SSL as the only way to connect, and encryption has to happen before it hits the wire. MySQL has a similar trick. Both are just using the OpenSSL library under the hood to encrypt the network traffic.

On the commercial side, Oracle does the same thing with ORA_ENCRYPT_LOGIN. SQL Server has client and server settings that enforce encryption. Basically, if your database traffic isn't encrypted, it's more likely because someone didn't think that was important than because it was impossible. It's a simple checkbox to add to database selection requirements, and it's not hard to find a DBMS that has the capability.

I find people who just stuff user passwords into the database (which can be the same passwords as other services) rather than putting password encryption into their application can also leak data. In PostgreSQL using the built-in pgcrypto makes that easy. You also have to be careful to use the same network encryption approach for any replication client, or it's possible to just sniff that instead to get the data. In Postgres those connect with the same encryption possible options as any other client. Most of the tutorials on setting up replication don't cover this though.

* SQL language extensions to integrate Facebook and Twitter.

This already exists as a plugin.

There is a Foreign Data Wrapper which allows you to make a twitter feed look like a local table.

FDW's exist for a large number of 3rd party data stores:
http://wiki.postgresql.org/wiki/Foreign_data_wrappers

An early implementation of Materialized Views was just submitted in November to the project. It may not be finished in time for PostgreSQL 9.3, but it will almost certainly be in the next release if not that one.

Materialized views are on the way, hopefully for 9.3. The first pass at this is fairly limited, you have to refresh the matview yourself (i.e. it supports only the "snapshot" type of matview maintenance, per terminology here).

Postgres has a status page on that, fwiw.

Right, suggestions like the Zenoss commentor who says "f you dont want to frack around, just chmod those puppies 777" are the reason why this is a problem. It's sadly common advice in the "I want setup to be easy" land of MySQL priorities.

Note that if you change the directory a PostgreSQL server writes to so that other users are allowed to write there, too, the server will refuse to start until you fix the permissions so that isn't the case. New database installations made with initdb have the right permissions, but the code checks against people "fracking" themselves by making them less secure later. The only way around this is to modify the source code to disable the check!

Many of the items on the PostgreSQL "gotcha" list are annotated to say that they only affect older versions; in one case they mention it affects versions 7.4 and earlier. Versions 7.4, 8.0, 8.1, and 8.2 have all hit end-of-life after five or more years of support. Version 8.3 hits end of support in about three months. That would be a very short list if issues from ancient out-of-support versions were culled from it.

PostgreSQL: Versioning policy

Those lists are 7 years old. Does anyone have a more up-to-date list?

The last time I looked, Postgres was indeed slow at select count(*) from x, but even that's now been fixed: http://wiki.postgresql.org/wiki/Slow_Counting

We've been looking at work, but not for 6 months or so due to another project taking priority. At the moment we use MySQL and a commercial database, and another part of the organisation uses MS SQL Server. That's clearly not optimal. The software using MySQL is the least-complicated stuff, so it's the easiest to switch. We're going to stop using the commercial database (cost, generally unsatisfactory). I think we should move to Postgres, but the rest of my team either aren't bothered, have bigger worries, or are too-scared of change.

When I get time I need to do a proper comparison between SQL Server and Postgres (and MySQL), but that takes quite some time to do properly (i.e. trying to set appropriate configuration settings for each system), and frankly I don't find it very interesting.

PostgreSQL versions from 8.3 to 9.1 did pretty well using up to 16 cores. 9.2 was the version that targeted scalability up to 64 cores, released this September.

The licensing model of commercial databases is one part of why PostgreSQL is become more viable even for traditional "enterprise" markets. PostgreSQL doesn't use processors quite as efficiently as its commercial competitors. The PostgreSQL code is optimized for clarity, portability, and extensibility as well as performance. Commercial databases rarely include its level of extensibility. This is why PostGIS as an add-on to the database is doing well against competitors like Oracle Spatial. And they're often willing to do terrible things to the clarity of their source code in order to chase after higher benchmark results. Those hacks work, but they cost them in terms of bugs and long-term maintainability.

But if the software license scales per-core, nowadays that means you've lost Moore's Law as your cost savings buddy. What I remind people who aren't happy with PostgreSQL's performance per-core is that adding more cores to hardware is pretty cheap now. Use the software license savings to buy a system with twice as many cores, and PostgreSQL's competitive situation against commercial products looks a lot better.

So you want to do that in the DB now. Will you have to change column definition to change that range?

No, the range is data, not part of the column definition, I would say "RTFA", but to be fair the link was mislabelled in TFS as being about "range-restricted types", rather than range types.

But here's the docs on range types. The scheduling use case is the basic example of exclusion constraints on range types (Sect 8.17.10 in the linked doc.)

performance, reliability, ease of development...

see http://www.postgresql.org/

PostgreSQL has had ACID compliance built in from the beginning. MySQL added it much later.

Over the last 18 years I have 3 times gone searching on the Internet for comparisons - each time PostgreSQL came out better than MySQL!

PostgreSQL is more standards compliant than MySQL, and has far fewer gotchas (unintended consequences of doing something that seemed so straightforward).

I have the misfortune to have a client with an application backed by MySQL.

Well, recommending a PL/SQL book as a source for learning SQL is a bit silly IMHO. Moreover, I find the books from Oracle rather bad - there are better sources to learn PL/SQL (e.g. the one from Feuerstein is a much better book).

And in fact there's a great book about administering PostgreSQL from Hannu Krosing - it's called "PostgreSQL 9 Admin Cookbook" [http://www.packtpub.com/postgresql-9-admin-cookbook/book]. It's a great set of recipes for admins for common tasks, not an exhaustive documentation (that's what http://www.postgresql.org/docs/9.1/interactive/index.html is for), but if you want to learn how real pros admin the database, this is the right choice. And yes, I'd recommend it to newbies coming from MySQL.

It might seem that the PostgreSQL community considered MySQL to be a toy database in the past, but it definitely was not a generally shared view. And this definitely changed recently - there's no reason not to join the community mailing lists / IRC channel and start a post with "I'm using a MySQL right now and I don't understand why PostgreSQL does SOMETHING."

No, I'm not aware of such thing ("PostgreSQL for MySQL people" style guide).

The best thing you can do is give it a ride - install it, use http://www.postgresql.org/docs/9.1/interactive/admin.html to do the setup etc.

Basically all you need to do to install and start the PostgreSQL from source code is this (at least on Linux):

$ cd postgresql-9.1.5
$ ./configure --prefix=/path-to-install
$ make install
$ export PATH=/path-to-install/bin:$PATH
$ pg_ctl -D /database-directory init ... fiddle with the config at /database-directory/postgresql.conf
$ pg_ctl -D /database-directory -l /database-directory/postgresql.log start

and then

$ createdb testdb
$ psql testdb

and you're in. If you're installing that from a package (e.g. RPM in RedHat), it might work a bit differently - depends on the packager.

Anyway, use the project mailing lists and IRC channel - there's always someone ready to help / answer novice or complex questions etc.

There are two PostgreSQL books I used a lot in the past: PostgreSQL 9.0 High Performance by Gregory Smith (Packt) and PostgreSQL Second Edition by Douglas Douglas (O'Reilly).

There is an extended list of books listed on the PostgreSQL homepage: http://www.postgresql.org/docs/books/

Problem with all books is, they get outdated too quickly. While a lot of the basic info is still true for the books above, the O'Reilly book is very much based on 8.4 with is pretty ancient already. Perhaps getting an ebook is less a waste of paper.

Intel's early SSDs such as the Intel X25-E were the last time I really got screwed by SATA drives that screwed this up very badly. See the PostgreSQL page on Reliable Writes for a lot more details on this subject.

This is why I am never an early adopter. If there were some tremendous emergency that only an early SSD could solve, and life-and-limb were on the line, I suppose I would take my chances. But I've never had that much of a need for an SSD.

I suppose I have pioneers like you to thank, however, for helping to identify and work out the problems so that people like me who wait a little while have such a good experience. It's like volunteer work, except of course that you had to pay in order to do it.

Intel's early SSDs such as the Intel X25-E were the last time I really got screwed by SATA drives that screwed this up very badly. See the PostgreSQL page on Reliable Writes for a lot more details on this subject.

Then that sheds a lot of light on the situation.

I have access to a shell account across town and I don't have root access (it's not my machine). Whatever I need I build from source and be done with it. You only have to build it once.

The build instructions for postgresql doesn't seem complicated. You just need to know which features you want.

http://www.postgresql.org/docs/current/static/install-procedure.html

--
BMO

http://www.postgresql.org/docs/9.1/static/high-availability.html

For me, the one advantage MySQL (and MariaDB, and even Apache Derby!) have over PostgreSQL is that there are versions that can be run stand-alone "out of the box" as a non-root user. PostgreSQL (AFAIK) needs to be installed, and needs to be installed as root (and you need to create a postgres user, etc.).

There is a reason why no one bothers to make an XAMPP-style "portable" version of PostgrSQL, as they have with MySQL. The reason is that this is dead-simple to accomplish even with the out-of-the-box binaries available on the PostgreSQL site.

On the PostgreSQL download page, you would download the "zip archive of the binaries", rather than the one-click installer. Unzip the archive's contents wherever you like (including on a USB thumb drive), and then refer to this 3-paragraph PostgreSQL article. It tells you to create a BAT file in your base PostgreSQL directory, cut-n-pasting these contents:

@ECHO ON
REM The script sets environment variables helpful for PostgreSQL
@SET PATH="%~dp0\bin";%PATH%
@SET PGDATA=%~dp0\data
@SET PGDATABASE=postgres
@SET PGUSER=postgres
@SET PGPORT=5439
@SET PGLOCALEDIR=%~dp0\share\locale
REM "%~dp0\bin\initdb" -U postgres -A trust
"%~dp0\bin\pg_ctl" -D "%~dp0/data" -l logfile start
ECHO "Click enter to stop"
pause
"%~dp0\bin\pg_ctl" -D "%~dp0/data" stop

The very first time you run this script, you comment-out the bold-face "REM" line... which will initialize a fresh PostgreSQL environment, with admin user "postgres" having a blank password. Then put the "REM" comment back on that line, and you have a complete portable PostgreSQL environment that can be moved from directory to directory and machine to machine.

This information is obviously Windows-centric... but the whole "portable" concept (in the USB thumb drive sense) is Windows-centric in the first place. If you're on Ubuntu, just "sudo apt-get postgres" and then remove it when you're done tinkering! By the way, you don't need administrator privileges to use the one-click installer on Windows.

A lot of the discussion that I'm seeing in this thread has more to do with phpMyAdmin vs. pgAdminIII than with MySQL vs. PostgreSQL themselves. To be perfectly frank, if one's biggest concern is what the admin or SQL workbench tool look like... then it doesn't really matter which of these two databases you use. You'll be fine either way.

The real consideration is whether you need (or would like to explore and learn about) the more "enterprise"-y features offered in PostgreSQL. If you're interested in more enterprise-level functionality, then PostgreSQL is by far the best free game in town. If you're not really interested in that stuff, then you might as well build around MySQL since it's more commonly offered by web hosts and cloud providers.

By "enterprise"-y, I'm talking about the concept of assuming that more than one application might eventually be using your database (and that the applications might be based on more than one language or technology stack). If you are only using your database through one application, and letting its ORM framework (Java JPA, Ruby Rails, PHP Doctrine, Python SQLAlchemy, etc) be responsible for enforcing all the persistence rules and business logic, then it doesn't make much difference to you as an application developer which database lies behind the framework.

However, let's say that you have a Rails web application writing to your database on the front-end, and a Java application working with it on the back-end. Maybe you even have some Python or Perl scripts kicked off by a nightly cron job, which build reports based on the data. To give a very trivial example, let's say that one of your table columns holds "customer type", and must be one of 7

There are one-click installers available for Windows and OS X. On Linux, you would obviously the package management version.

You also don't have to run PostgreSQL as root at all. I develop on OS X and typically run an installation from my home directory. (I also compile my own version, but you don't have to do that.)

There are one-click installers available for Windows and OS X. On Linux, you would obviously the package management version.

You also don't have to run PostgreSQL as root at all. I develop on OS X and typically run an installation from my home directory. (I also compile my own version, but you don't have to do that.)

if they want to use open source database. Try Firebird SQL if you want to go light (lighter than mysql in most cases I've seen), or go with the big boys with PostgreSQL.

So if I have a bunch of floats, those have to be converted to ASCII strings, and sent to the DB, which then converts them back to floats to be stored internally. Or, if I want to store some binary data in some of the records (along with other formatted data, including ints, strings, and floats), that binary data needs to be converted to base64 or some other ascii representation to be inputted into the DB, which then surely converts it right back to binary.

You can avoid the overhead of conversion using parametrized queries (PQexecParams), which also avoids SQL injection attacks. However, I suspect that for almost all use cases the cost of interprocess communication alone is going to dwarf the conversion.

PostgreSQL also has providers of support contracts. I'm a little familiar with EnterpriseDB

Sadly, his post will be missed because everyone's too busy talking about how everything can be done just as easily on a $500,000 server farm running Oracle's latest and greatest turd.

Actually, I was going to talk about how PostgreSQL 9.2 (expected in Q3 of this year) will include JSON support. The database also has non-relational key value storage, and that feature is even available in Heroku deployments now.

PostgreSQL also lets you relax ACID for performance when that makes sense, at the transaction level, using synchronous_commit parameter and unlogged tables.

There are two things PostgreSQL doesn't do as well as MongoDB. It won't do simple key/value lookups quite as fast; I normally eliminate that problem by putting a memcached server in at some level. And you can't split writes among multiple nodes easily yet.

Sadly, his post will be missed because everyone's too busy talking about how everything can be done just as easily on a $500,000 server farm running Oracle's latest and greatest turd.

Actually, I was going to talk about how PostgreSQL 9.2 (expected in Q3 of this year) will include JSON support. The database also has non-relational key value storage, and that feature is even available in Heroku deployments now.

PostgreSQL also lets you relax ACID for performance when that makes sense, at the transaction level, using synchronous_commit parameter and unlogged tables.

There are two things PostgreSQL doesn't do as well as MongoDB. It won't do simple key/value lookups quite as fast; I normally eliminate that problem by putting a memcached server in at some level. And you can't split writes among multiple nodes easily yet.

Sadly, his post will be missed because everyone's too busy talking about how everything can be done just as easily on a $500,000 server farm running Oracle's latest and greatest turd.

Actually, I was going to talk about how PostgreSQL 9.2 (expected in Q3 of this year) will include JSON support. The database also has non-relational key value storage, and that feature is even available in Heroku deployments now.

PostgreSQL also lets you relax ACID for performance when that makes sense, at the transaction level, using synchronous_commit parameter and unlogged tables.

There are two things PostgreSQL doesn't do as well as MongoDB. It won't do simple key/value lookups quite as fast; I normally eliminate that problem by putting a memcached server in at some level. And you can't split writes among multiple nodes easily yet.

Urban Airship went PostgreSQL to MongoDB to Cassandra to PostgreSQL. http://wiki.postgresql.org/images/7/7f/Adam-lowry-postgresopen2011.pdf

It's a good presentation because they're in love with none of them and are moving for specific reasons each time, handling different issues. It's not coders chasing the new hotness.

show an elephant losing his entire database due to a power failure.

The elephant will obviously be using PostgreSQL.

Speaking as someone who contributes to PostgreSQL, one of the projects mentioned in TFA, the easiest way to contribute something useful to that project while having some fun too is to test out new features. Reviewing a patch that hasn't been committed yet is part of the community process for validating what features get committed. And testing recently committed features is useful too, to help flush out bugs in them before release. Working on new features seems to be more attractive to new contributors than trying to fix old problems, and good reviewing skills flow naturally into becoming a code contributor too.

I would recommend checking out PostGIS, which works with PostgreSQL. PostGIS adds functions, data types, and projection definitions to PostgreSQL that turn PostgreSQL into a powerful spatially-enabled database server. I'ved used this combination for a number of projects, and they work great. Both are fully open sourced.

No.

Just imagine, soon we can run a webserver on Windows, and even use PHP! Soon we can even have an open source database on windows. Not to mention an open source office suite! All thanks to the windows 8 store! /sarcasm.

Seriously, why on earth is this news? Windows is not incompatible with open source, you know... Just download and install. Or will MS try to lock Win8 down so much that we seriously expect to get apps from the app store???
In which case: Tnxbutnotnx.

I develop web applications every day with PostgreSQL and Python, both very popular projects which originated in universities. I also depend on the ubiquitous Apache HTTP server which was originally a derivative of a university project. Both my development and production environments are GNU/Linux. GNU and Linux were not projects at universities, but they were non-commercial and inspired by experiences in universities.

Though Unix originated at AT&T, the additions from BSD have profound and lasting effects on all modern operating systems, especially Unix-like ones. The Internet was developed at universities and TCP/IP was originally implemented on BSD Unix.