Slashdot Mirror

At one point tracking the entire .org domain was done with PG as well

Still is; even more than that actually, .info too. See Afilias Global Registry for a full list. Afilias sponsors quite a bit of PostgreSQL related work, including employing some of the major contributors to Slony, one of the most popular PostgreSQL replication solutions.

this may work:

http://www.pgadmin.org/docs/1.6/slony/failover.html

http://slony.info/

have you assessed this for your needs?

http://slony.info/

The subject of this article, EnterpriseDB, is trying to target this market with GridSQL. As it's new in it's current form, impossible to say how reliable systems built with it will be quite yet. Those looking for reasons behind the IBM investment might consider whether GridSQL might one day talk to DB2 databases as well.

The closest fully open-source PostgreSQL solution to your requirements that's been around a bit is pgpool-II. It think it's still too immature to be considered five-nines quality though, and there are some restrictions you have to observe. A PostgreSQL replication solution that is very robust and proven is slony but it's not a load-balancing solution in the way I suspect you want.

There's also the Greenplum Database, which isn't free or open-source but is rooted in PostgreSQL technology.

Good enterprise-grade clustering with load-balancing is still on the PostgreSQL work in progress list rather than being here right now. I expect the core infrastructure piece needed to really make it work well (support for read-only warm-standby slaves) will make it into PostgreSQL 8.4 and be released around a year from now. I started a comparison page of the replication solutions currently available that's on the PostgreSQL wiki now that is trying to track progress in this area. Much like core PostgreSQL support for enabling replication, it still needs some work .

The best option for asynchronous PostgreSQL replication is Slony-I. It's extremely flexible and so far has proven to work very well. The devs supporting it are also very good. I do admit that there is a bit of a learning curve to get it configured and running, but this is something they are working on for the next major revision.

If you need to cluster PostgreSQL, have a look at pgpool and pgpool-II.

Yes, there are a few options.

Slony-I is a BSD licensed Master-Slave system; its successor, Slony-II, is still in development.

Various outfits like EnterpriseDB and Greenplum offer commercial Postgres derivatives with multi-master replication and clustering.

There are also open-source, less-popular options like PGPool and PGCluster.

Cheers,
-J

Now, the reason I wouldn't use PostgreSQL in some instances, PostgreSQL still does not have any clustering features that I am aware of.

It sure does:
* Slony-I: master-slave, table-level backup. Works across postgresql versions, which means it's very convenient to use to upgrade. Very good software, well tested in live environments (that's what serves the .org in slashdot.org, and every other .org or .info).
* PgPool: Query based replication software and network management software. This can work as multi-master by distributing queries, or work with Slony.
* Also, 8.1 has the very nifty feature called "Two Phase Commit" (a.k.a. 2PC). This allows network management software to synchronously commit on multiple machines to create a multi-master solution.

gives real-world usable examples of each documented function,

If something is lacking in the documentation, please write to the mailing lists. I'd be happy to write a few documentation pages in my spare time. However, what might be obvious to a PostgreSQL person might not be to someone coming from another database background. So, I don't know what about the documentation needs work.

Easy setup and administration?
Yeah, postgres is really hard to setup.
apt-get install postgresql
sudo su - postgres
createuser noob
^D
createdb its_just_not_that_tricky

Or you could just download and install one of the gui tools if command lines scare you.

Handy SQL extensions?
Oh, you mean like how there aren't any procedural languages supported? You know, the ones that would obviate the need for hackish extensions that only half solve a given problem? Or perhaps you're talking about all the dumb foot cannons that MySQL AB thought was clever?

Enterprise features for those who want them and not for those who don't?
Like... replication? Oh wait, that's a postgres feature that MySQL hasn't even dreamed of supporting. MySQL only just recently figured out what a transaction is a couple of years ago. They still haven't figured out NULL. I'm not sure exactly what enterprise features you're refering to, but then I don't think you are either since MySQL hasn't got any. Which is probably why nobody uses MySQL in enterprise environments whereas Postgres a non-trivial chunk of the internet.

Yahoo! uses MySQL for minor, readonly stuff. All writes are written to InnoDB tables, and those are slow. That is replicated to MyISAM tables, which are used as caches.

PostgreSQL replication? is here. BSD licensed too.

Oh, and .org uses PostgreSQL as a backend. Let me know when Yahoo! starts using MySQL for its financial accounting stuff.

* Easy replication on MySQL/ Not so easy on PostgreSQL

Check out Slony if you haven't recently. Not exactly what I'd call "easy", mind you, but certainly not unreasonably difficult to set up.

Besides, given the complexities inherent in the whole concept of database replication, I'd be somewhat suspicious of a solution that was *easy*.

and the .info registry

Both are run by Afilias, which is a big user and big developer of PostgreSQL. They're the ones that did the work on the Slony-I replication server.

All I now need is the Postgres-R (replication) stuff to work out of the box (like it does for mysql). I don't know if transaction speeds might be hit by replication or not.

From the interview:

One other question that I would like to answer is replication, because I get this question all the time: unlike some other database systems within PostgreSQL, replication is an add-in. It's a separate application. That isn't an accident. It's done on purpose.

There are several reasons for that. One is that replication is actually not a single feature. It is a set of four or five different related implementations, which serve four or five different needs. As a result, we don't want to bundle one particular kind of replication with the main database, because that's not suitable to all users. Our leading replication project, in terms of popularity, is something called Slony-I, lead by Jan Wieck, who is also on the Core Team. That has actually been quite popular as one of the leading master-slave high availability replication systems of any kind. Jan is currently working on Slony-II, which will be synchronous multi-master replication for database server clusters. Based on the pace of his past work, I would anticipate that it would be available in about a year or so. But don't look for that information in the main release notes for PostgreSQL, because it will always be a separate parallel project. [emphasis added]

In other words, just use Slony-I. From the overview:

Slony-I was born from an idea to create a replication system that was not tied to a specific version of PostgreSQL, and allowed to be started and stopped on an existing database with out the need for a dump/reload cycle.

Slony-I is a "master to multiple slaves" replication system with cascading and slave promotion. The big picture for the development of Slony-I is a master-slave system that includes all features and capabilities needed to replicate large databases to a reasonably limited number of slave systems. Slony-I is a system for data centers and backup sites, where the normal mode of operation is that all nodes are available.

See also the basic documentation. For more technical details dewnload the design document (PDF). For an excellent introduction make sure to read Introducing Slony by A. Elein Mustain on ONLamp:

Slony-I, the first iteration of the project, is an asynchronous replicator of a single master database to multiple replicas, which in turn may have cascaded replicas. It will include all features required to replicate large databases with a reasonable number of replicas. Jan has targeted Slony-I toward data centers and backup sites, implying that all nodes in the network are always available.

The master is the primary database with which the applications interact. Replicas are replications, or copies of the primary database. Since the master database is always changing, data replication is the system that enables the updates of secondary, or replica, databases as the master database updates. In synchronous replication systems, the master and the replica are consistent exact copies. The client does not receive a commit until all replicas have the transaction in question. Asynchronous replication loosens that binding and allows the replica to copy transactions from the master, rolling forward, at its own pace. The server issues a commit to the master client based on the state of the master database tra

The earliest version of PostgreSQL with which Slony-I is compatible is version 7.3.3. That's because Slony-I needs namespace support, which didn't really solidify until then.

Rod Taylor has reported that he "hacked together" a version for 7.2, but the only way I'd be inclined to use that is if I was paying Rod to set it up...

When I did upgrades of 7.2 systems to 7.4, I used eRServer, a Java-based replication system from which Slony-I inherited many of its design ideas. (The name "Slony" is a conscious nod to Vadim Mikheev, one of the main creators of erServer...)

I'll have to add documentation on how one would do this to the Slony-I FAQ ...

Check out the new Slony replication engine:

http://www.slony.info/

It is probably the best master->slave data replication engine for PostgreSQL at the moment. It is free and developed by one of the core developers.

-Dom

First, I'd like to point you to my favorite book about database theory, even though it's not exactly what you asked for:

"An Introduction to Database Systems" by C.J. Date. It's a great book and I liked it so much I bought another by the same author: "Foundation for Future Database Systems - The Third Manifesto". Both are very theoretical and also very precise.

On a more practical level:
* Stored Procedures: In postgres, these are the same as functions. They have several distinct purposes:

(1) Produce data in the form of a relation that you might not otherwise be able to produce with a query alone. An example might be some kind of query that is hard to express otherwise (if there are lots of bizarre if..then's or loops that are based on the contents of the records), or if you want to make a socket connection in the function and return a table of the current headlines from slashdot.

(2) functions are vital to making a proper user-defined data type: you need the input and output functions, the comparison functions (if you want it to be indexable), etc.

(3) Making a user-defined aggregate function is very useful if you need something other than the basics, and an aggregate function requires several other functions (accumulator, for instance).

(4) Basic batching of statements so you don't have to send so many queries.

(5) if you need to execute several statements and try to recover from various errors in a graceful way, without having to have client-side logic. A simple example might be to make a function that tries to INSERT, and if it gets a duplicate key violation, than it does UPDATE.

(6) triggers require functions also

(7) Stored procedures/functions allow you to express your logic in languages other than SQL, like perl, for instance.

(8) functional indexes are very useful for query optimization.

* Views: In postgresql, views are a rewriting rule that is very useful for being a DBA, because you can optimize a query or change the physical storage without modifying the app at all (or even interrupting it). However, some databases go beyond that and have materialized views, which can improve performance by holding the data seperately, ready to access.

* Triggers: two main types
(1) Constraint triggers: very powerful constraint tool that allows you to check the database for any inconsistencies before committing the transaction. This is very helpful when you have complex business rules, especially when you have several applications accessing the same database. Each application will be expecting the data in a certain way, and if the constraints weren't there, you could have a very elusive bug.

(2) Regular Triggers: I don't find myself using these very often, but sometimes they're necessary. Some have developed very powerful packages, like http://www.slony.info/, which is a very advanced replication system that wouldn't be possible without triggers. Triggers are a very powerful tool, although not used in day-to-day stuff. When you find yourself thinking "I know what I need to do, but I don't know if I can make my database do that", think triggers. Remember since triggers use functions, you can do pretty much anything, like emailing yourself.

-- To find examples and more explanation, the postgresql.org docs are good. If you have more specific questions, email the postgres lists and I'll be there.

If stored procs are a bad thing, wouldn't the following features also be bad?:

(1) triggers
(2) constraint triggers
(3) functional indexes using a user-defined function, or indexing a user-defined data type
(4) user-defined aggregate function
(5) user-defined data types

Those are very difficult problems to simply work around. For #3 you just have to take the performance hit and not use the index. For #4 you have to read the entire amount of data into the application and post-process it to get the data you actually need. #1, 2, and 5 all speak for themselves, being basic principles of an RDBMS.

And PostgreSQL 8.0.0beta1 has PITR (point in time recovery) meaning that it should be reasonably simple to revert to a previous state. Hopefully that makes it easier for you to fix it when someone screws it up.

I'd also like to point out that a very advanced replication system, Slony-I, was developed for PostgreSQL using triggers and stored procedures (to work with the replication daemon). So I'm not speaking in abstract terms about potential usefulness, but real-world business needs. What would you do to implement such a replication engine in MySQL? Write some code in the MySQL server and recompile I suppose. Slony actually works across PostgreSQL versions and works on versions of PostgreSQL that were released before Slony was ever conceived. That's the power of stored procedures.

I understand that these features need to be used with care. I understand that some people don't need these features (unfortunately, some people realize they are needed too late). What I don't understand is how software can be missing these features and still call itself an RDBMS. What surprises me even more is when people make a blanket statement condemning these features with absolutely no acknowledgement of their actual uses.

One of the points of Slony-I is to provide an answer to this very problem. Slony-I supports versions 7.3, 7.4, and 8.0, and may be used to support a short-outage upgrade path.

Suppose you have a 7.3 database, and want an 8.0 one. You set up replication between the 7.3 database and the new 8.0 one. It may take a couple of days for the new one to get up to date, but you don't have to shut the 7.3 one down.

Once the databases are more or less in sync, you do a MOVE SET to change the "master" to be the 8.0 database. Since they are nearly in sync, this should only take a few seconds. Presto! The 8.0 database is the "master," and you can switch over to it with whatever brief outage is needed to get your application to point to a new server.