Replication

If you're looking for help with Replication, you've come to the right place. 2ndQuadrant designed and wrote PostgreSQL's Point in Time Recovery (PITR) and Warm Standby replication features. We've been actively maintaining them over the last 4 major releases of PostgreSQL and we're working on the future too, sometimes with sponsorship from other companies and major users.

We offer architecture & design services, implementation, training and technical support. We also provide low-level database recovery services, for when disaster strikes. The information in the sections below is maintained to assist you.

We also know replication means different things to different people. Our approach is to understand your requirements for High Availability, Data Integration, Archiving etc. and optimise the solution for your business. Whether it's a one-off project or you need permanent infrastructure, we can help. Upgrades, RDBMS Migrations, Exploration Data Marts, Data Warehouses, Straight Through Processing. Whatever the reason, we've got the experience.

Warm Standby Servers for High Availability

PostgreSQL 8.2 and 8.3 provide a simple, fast and stable replication mechanism, known as Warm Standby, though more accurately described as Log Shipping. Data from the transaction log, also known as the Write Ahead Log (WAL) is transported to a standby server where continuous recovery takes place. It's a simple and elegant solution, relying on the underlying recovery code to perform changes on the standby node, so there is only minimal overhead on the primary server.

Data is currently shipped one file at a time, offering file based asynchronous replication. The entire database server is replicated, so there is no additional administration for each table or for each database. Replication can be fast in many circumstances and utilises WAN links effectively. The standby node cannot yet be accessed to perform queries.

We're working on further updates to this replication mechanism, covering

• Data streaming
• Synchronous Replication
• Read Only Standby Servers, also known as Hot Standby
• Increased replication performance

Postgres log shipping works using similar concepts to the following products options in other RDBMS (trademarks acknowledged appropriately)

• Oracle Data Guard
• IBM DB2 HADR
• Microsoft SQL Server Log Shipping
• Informix HDR (On-Bar)

  Back to top

Known Bugs Affecting Log Shipping Replication

All software fails, the question is what we do about it and how quickly.

Various types of bugs can affect the robustness of Postgres replication. These might be bugs in the replication mechanisms themselves, or in one of the Resource Managers on which Postgres databases are built, such as various types of index.

  Back to top

Support

Support is available for Warm Standby replication from 2ndQuadrant, led by the original developer and current maintainer of the code for this critical part of Postgres. 2ndQuadrant is a professional open-source company, so all code fixes will be made available through the Postgres Community edition.

Benefits

• E-mail support during configuration
• Rapid response to failures should they occur Phone support during crisis, development of options and workarounds.
• Fast bug fixes available for hot fix
• Root cause investigation and follow through with Postgres community.
• Analysis of options for data recovery in disaster/data-loss situations.
• Access to bug lists, with notification emails if new bugs discovered
• Access to any enhanced versions of software tools, all covered by support
• Updates on best practice following further research and testing
• Support provides funding for new developments as well as maintenance

Terms and conditions apply. Email support@2ndquadrant.com

  Back to top

Training

2ndQuadrant have developed a 1 or 2 day training class that can be taken via the public schedule, or privately at your site. Remote training classes are also an option using web-based video conferencing, with discounts.

As a 1-day class PostgreSQL Replication & Recovery is a comprehensive introduction to Postgres replication options, with an in-depth look at Slony and Warm Standby replication. As a 2-day class PostgreSQL Replication & Recovery covers everything in the 1-day class, plus extensive practicals to allow attendees to fully understand configuration, failover and various failure modes. Full training details available at www.2ndquadrant.com/training.html

  Back to top

Tools

• pg_standby - Simon Riggs

  The enabler for log shipping replication.

• pg_clearxlogtail - Kevin Grittner

  Writes zeros to end part of WAL file following switch to allow better compression when using lower archive_timeout settings.

• pg_compresslog, pg_decompresslog - Koichi Suzuki

  Allows removal of full page writes from WAL files

  Back to top

What's New in Postgres 8.3

• New boolean configuration parameter, archive_mode, controls archiving. (Simon)

  Previously setting archive_command to an empty string turned off archiving. Now archive_mode turns archiving on and off. This is useful for stopping archiving temporarily, such as changing the archive_command.

• log_restartpoints (Simon)

  Allows checking of the progress of recovery, as well as confirming that the recovery will be restartable.

• %R option in restore_command (Simon)

  Allows the warm standby server to pass the earliest needed WAL file to the recovery script to allow automatic removal of unneeded WAL files.

• Faster recovery when using full_page_writes (Heikki)

  Large recoveries will be less I/O bound when working in full_page_writes mode, in some cases improving performance by up to 50%.

• Change the timestamps recorded in transaction WAL records from time_t to TimestampTz representation (Tom)

  This provides sub-second resolution in WAL, which can be useful for point-in-time recovery.

• Place temporary table TOAST tables in a special schemas named pg_toast_temp_nnn (Tom)

  This allows low-level code to recognize that these tables are temporary, which can then be optimized to not write WAL, even during replication.

• pg_standby part of core distribution for 8.3, includes optional %r support (Simon)

  Back to top