Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

Feature wise, I don't think there are many things that MySQL can do that PostgreSQL can't given a proper configuration. The main difference is that PostgreSQL doesn't include things like clustering as part of the core distribution like MySQL. They are often released and maintained as third-party add-ons to the core platform.

For instance, PostgreSQL has a number of projects that facilitate clustering. Slony-I is a stable open source replication framework (http://www.slony.info/). PGCluster provides a multi-master replication facility (think Oracle RAC). PGpool allows front end load balancing of connections.



AFAIK, none of the add-on replication mechanisms you mentioned can guarantee total consistency across multiple database backends: OIDs are not replicated, and there are no hard guarantees against external modifications taking your databases out of sync.

As an alternative, you can use Point-In-Time-Recovery (PITR), also known as "log shipping", to transfer copies of your write-ahead log files from a master server to a warm standby. PITR allows you to have an exact replica of your master database, modulo whatever delay you incur in the log transfer itself.

For those who haven't aren't familiar with write-ahead logs, they function much like the logging in modern filesystems: changes are written there before being flushed to disk, so any incomplete transactions can be replayed from the logs if the server goes down unexpectedly.

Of course, the log-shipping method doesn't allow your secondary server to serve as a read-only replica as MySQL's built-in replication does. Largely for that reason, (depending on your workload and requirements, of course) MySQL may be a better choice. However, I've found Postgres PITR to be an excellent option for low to medium-traffic databases in those cases where I'm willing to trade the possibility of the loss of a few seconds' updates for guarantees that my DDL, object identities, and constraints will be maintained consistently between my replicas.

For those who are curious, the PostgreSQL docs online have a decent introduction into the use of write-ahead logs for replication and backups:

http://www.postgresql.org/docs/8.2/static/wal.html




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: