Replication, Clustering, and Connection Pooling

From PostgreSQL wiki
Jump to navigationJump to search

Introduction

There are many approaches available to scale PostgreSQL beyond running on a single server. An outline of the terminology and basic technologies involved is at High Availability and Load Balancing. There is a presentation covering some of these solutions.

There is no one-size fits all replication software. You have to understand your requirements and how various approaches fit into that. For example, here are two extremes in the replication problem space:

  • You have a few servers connected to a local network you want to always keep current for failover and load-balancing purposes. Here you would be considering solutions that are synchronous, eager, and therefore conflict-free.
  • Your users take a local copy of the database with them on laptops when they leave the office, make changes while they are away, and need to merge those with the main database when they return. Here you'd want an asynchronous, lazy replication approach, and will be forced to consider how to handle conflicts in cases where the same record has been modified both on the primary server and on a local copy.

These are both database replication problems, but the best way to solve them is very different. And as you can see from these examples, replication has a lot of specific terminology that you'll have to understand to figure out what class of solution makes sense for your requirements. A great source for this background is in the Postgres-R Terms and Definitions for Database Replication. The main theoretical topic it doesn't mention is how to resolve conflict resolution in lazy replication cases like the laptop situation, which involves voting and similar schemes.

Features in the Core of PostgreSQL

  • Hot Standby/Streaming Replication is available as of PostgreSQL 9.0 and provides asynchronous binary replication to one or more standbys. Standbys may also become hot standbys meaning they can be queried as a read-only database. This is the fastest type of replication available as WAL data is sent immediately rather than waiting for a whole segment to be produced and shipped.
  • Warm Standby/Log Shipping is a HA solution which 'replicates' a database cluster to an archive or a warm (can be brought up quickly, but not available for querying) standby server. Overhead is very low and it's easy to set up. This is a simple and appropriate solution if all you care about is continuous backup and short failover times.

Historically, the PostgreSQL core team considered replication and clustering technology outside the scope of the main project's focus but this changed in 2008, see the Core Team's statement. Replication is now a significant focus of ongoing PostgreSQL development.

Comparison matrix

This page is being overhauled at Clustering

Program License Maturity Replication Method Sync Connection Pooling Load Balancing Query Partitioning
PgCluster BSD Not production ready Master-Master Synchronous No Yes No
pgpool-I BSD Stable Statement-Based Middleware Synchronous Yes Yes No
Pgpool-II BSD Recent release Statement-Based Middleware Synchronous Yes Yes Yes
slony BSD Stable Primary-Replica Asynchronous No No No
Bucardo BSD Stable Master-Master, Primary-Replica Asynchronous No No No
Londiste BSD Stable Primary-Replica Asynchronous No No No
Mammoth BSD No longer maintained Primary-Replica Asynchronous No No No
rubyrep MIT No longer maintained Master-Master, Primary-Replica Asynchronous No No No
Bi-Directional Replication PostgreSQL (BSD) Recent release Master-Master
(no triggers needed) 		Asynchronous No No No
pg_shard LGPL Recent release Statement-based Middleware (as an extension) Synchronous No Yes Yes
pglogical PostgreSQL Recent release Primary-Replica Asynchronous No No No
Postgres-XL PostgreSQL Recent release MPP Postgres, scalable writes & reads Synchronous Yes Yes Yes
Citus AGPL Recent release MPP Postgres, scalable writes & reads Asynchronous or Synchronous Yes Yes Yes

Replication

Aside from the in-core streaming replication, mentioned above...


  • Replicator Pro allows table data comparison and sync - even with heterogeneous databases. It is unique in the fact it can replicate changes only even if source is non-relational (CSV, DBF, Excel documents, Paradox...). Replicator has a built-in scheduler for easy periodic change replication.
  • Slony-I: Seems good, single primary only, primary is a single point of failure, no good failover system for electing a new primary or having a failed primary rejoin the cluster. Replica databases are mostly for safety or for parallelizing queries for performance. Suffers from O(N^2) communications (N = cluster size). with reasonable sysadmin you can implement failover system yourself. regarding communications, you can cascade the replication to reduce load on the primary. If you were implementing a large replication cluster, this would probably be a good idea. Slony is powerful, trigger based, and highly configurable.
  • PGCluster: PGCluster (which, incidentally, is not the same as PGCluster-II, a shared-disk solution), which does synchronous multimaster replication. Two single-points failure spots, load balancer and the data replicator. The project was developed as open source (old releases available in PgFoundry archives), then it was commercialized by the Cybertec.at company.
  • http://www.pgpool.net/ pgpool 1/2 is a reasonable solution. it's statement level replication, which has some downsides, but is good for certain things. pgpool 2 has a neat distributed table mechanism which is interesting. You might want to be looking here if you have extremely high ratios of read to write but need to service a huge transaction volume. Supports load-balancing and replication by implementing a proxy that duplicates all updates to all replicas. It can partition data by doing this, and it can semi-intelligently route queries to the appropriate servers.
  • "Mammoth Replicator" - BSD - http://www.commandprompt.com/products/mammothreplicator/ - Former proprietary solution, now open source. Uses a central logging process to distribute data changes amongst nodes. Essentially a fork of Postgres, as the changes are written directly into the backend.
  • "Bucardo" - BSD License - http://bucardo.org/ - Trigger-based, asynchronous, multi-master or primary-replica, written using plperl.
  • Cybertec, an Austrian company, offers a proprietary packaging of PGCluster. They simply call it PostgreSQL Multimaster-Replication, see http://www.cybertec.at.
  • pglogical - pglogical is a logical replication system implemented entirely as a PostgreSQL extension. Fully integrated, it requires no triggers or external programs. This alternative to physical replication is a highly efficient method of replicating data using a publish/subscribe model for selective replication.
  • Postgres-R is still in development. It features eager and thus conflict-free, but async multi-master replication.
  • SymmetricDS is an open-source, web-enabled, database independent, data synchronization software application. It uses web and database technologies to replicate tables between relational databases in near real time. The software was designed to scale for a large number of databases, work across low-bandwidth connections, and withstand periods of network outages. Supports several relational databases, including PostgreSQL. Licensed under Lesser GPL (LGPL).
  • DRBD (http://www.drbd.org/), a device driver that replicates disk blocks to other nodes. This works for failover only, not for scaling reads. Easy migration of devices if combined with an NFS export.
  • Daffodil Replicator. Supports several relational databases, including PostgreSQL. Licensed under GPL.
  • "RubyRep" - MIT License - http://www.rubyrep.org/ - Ruby based, asynchronous, multi-master replication system, which supports Postgres and MySQL.
  • Citus Data develops the pg_shard extension which can transparently shard a PostgreSQL table across many servers and replicate the shards, and Citus which parallelizes queries across many servers and cores for real-time analytics and supports bulk loading, distributed joins, columnar storage and more.
  • EDB Replication Server: Provides single and multi-master solutions for read/write scalability, availability, performance, and data integration with Oracle, SQL Server, and Postgres. EDB Replication Server uses Postgres' fast Logical Decoding technology to support large multi-master clusters and single primary configurations for a wide variety of data center solutions.


Inactive projects

  • Slony-II
  • PGReplication

Clustering

  • Citus - shards and replicates tables across a scalable, high availability cluster of commodity PostgreSQL servers and parallelizes queries for real-time SQL on big data.
  • Greenplum Database - Not so much a replication solution as a way to parallelize queries, and targeted at the data warehousing and big data crowd. Greenplum is tightly integrated with PostgreSQL.
  • HadoopDB - A MapReduce layer put in front of a cluster of postgres back end servers. Shared-nothing clustering.
  • PL/Proxy - database partitioning system implemented as PL language.
  • Postgres Pro Multimaster - part of Postgres Pro Enterprise DBMS. Provides fail-safe shared nothing cluster with transactional integrity and no read overhead. Based on enhanced logical replication.
  • sequoia (jdbc, formerly known as c-jdbc)
  • Postgres-XL is a shared nothing, multi-master clustering solution which can transparently distribute a table on a set of nodes and execute queries in parallel of those nodes. It has a additional component called Global Transaction Manager (GTM) for providing globally consistent view of the cluster. The project is based on the latest available 9.5 release of PostgreSQL. Some companies, such as 2ndQuadrant provides commercial support for the product.

Connection Pooling and Acceleration

Connection pooling programs let you reduce database-related overhead when it's the sheer number of physical connections dragging performance down. This is particularly important on Windows, where system limitations prevent large number of connections; see "I cannot run with more than about 125 connections at once" in Running & Installing PostgreSQL On Native Windows. It's also vital for web applications where the number of connections can get very large.

Some programs that implement connection pooling are:

  • PgBouncer
  • pgpool
  • Heimdall Data - (Proprietary) Provides a multi-vendor solution for database caching, connection pooling, load balancing, query routing, analytics and security. Provides a GUI for simple configuration, as well as a distributed engine for processing queries at scale. Also supports run-time reconfiguration cluster-wide without application restarts. Requires zero code changes to implement.

Some people also or alternately use memcached in various ways to reduce the work the database handles directly by caching popular data. pgmemcache is a PostgreSQL extension for interfacing with memcached servers.

Cluster management

  • pglookout - PostgreSQL replication monitoring and failover daemon
  • repmgr - replication manager for PostgreSQL clusters
  • pg_keeper - Background worker based simple failover daemon.

Other Resources

Sources for more information located but not yet integrated into here.

Articles


Video tutorials

Credits

Sources for the initial information on this page include:

Retrieved from "https://wiki.postgresql.org/index.php?title=Replication,_Clustering,_and_Connection_Pooling&oldid=35132"