In association with heise online

12 September 2011, 16:18

PostgreSQL 9.1 with synchronous replication

  • Twitter
  • Facebook
  • submit to slashdot
  • StumbleUpon
  • submit to reddit

PostgreSQL Logo

The main innovation in version 9.1 of the free database server PostgreSQL, is synchronous replication. Synchronous replication ensures that, for each write transaction, the master waits until at least one slave node has written the data to its transaction log. This increase in reliability comes at the cost of longer response times, but can now be limited to individual transactions. While the superuser used to be the only one who could launch replication, the new privilege replication can be assigned to any user or role.

Another innovation is "unlogged tables", for which the server does not write data into transaction logs, making them a kind of quick buffer storage. For the first time, PostgreSQL 9.1 allows the sorting order for strings ("collation") to be separately determined for each database, table, and column. Up to now, this value was set for the entire server.

Common Table Expressions, which have been in PostgreSQL for some time, can now be used in writing SQL statements. The wiki provides a simple example of removing all data records containing "hello" from one table and pasting them into another:

WITH deleted AS
(DELETE FROM test_trgm
WHERE text_data like '%hello%'
RETURNING text_data)
INSERT INTO old_text_data SELECT * FROM deleted;

A complete list of the changes is provided in the release notes. As usual, sources and executable versions (for FreeBSD, Linux, Mac OS X, Solaris, and Windows) are available from the project's download area. The source code is made available under the terms of the PostgreSQL License, described as a liberal open source licence, similar to the BSD or MIT licences.

See also:


Print Version | Send by email | Permalink:

  • July's Community Calendar

The H Open

The H Security

The H Developer

The H Internet Toolkit