Replication, Clustering, and Connection Pooling
From PostgreSQL wiki
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 master 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.
- PostgreSQL 9.4's Logical Changeset Extraction forms the foundation of the Bi-Directional Replication and Logical Log Streaming Replication features being added to PostgreSQL.
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.
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-II||BSD||Recent release||Statement-Based Middleware||Synchronous||Yes||Yes||Yes|
|Mammoth||BSD||No longer maintained||Master-Slave||Asynchronous||No||No||No|
|rubyrep||MIT||No longer maintained||Master-Master, Master-Slave||Asynchronous||No||No||No|
|Bi-Directional Replication||PostgreSQL (BSD)||Recent release|| Master-Master
(no triggers needed)
|pg_shard||LGPL||Recent release||Statement-based Middleware (as an extension)||Synchronous||No||Yes||Yes|
|Postgres-XL||PostgreSQL||Recent release||MPP Postgres, scalable writes & reads||Synchronous||Yes||Yes||Yes|
Aside from the in-core streaming replication, mentioned above...
- Slony-I: Seems good, single master only, master is a single point of failure, no good failover system for electing a new master or having a failed master rejoin the cluster. Slave 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 master. 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 has historically looked a bit dead, but they just released a new version and http://pgfoundry.org/projects/pgcluster is up to date (at least downloads page) One major downside to PGCluster is that it uses a modified version of PostgreSQL, and it usually lags a few releases behind.
- 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 slaves. 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 master-slave, 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.
- Londiste, a part of Skytools (https://developer.skype.com/SkypeGarage/DbProjects/SkyTools) which is a collection of replication tools from the Skype people. Purports to be simpler to use than Slony.
- BDR (Bi-Directional Replication for PostgreSQL) - multi-master replication based on log streaming logical replication.
- 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.
- Continuent uni/cluster, proprietary and the related Sequoia (jdbc, formerly known as c-jdbc)
- 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.
- "pg_comparator" - BSD License - http://pgfoundry.org/projects/pg-comparator/ - Perl-based, table-level async master-slave "diff" and "patch" method of replication. Low configuration overhead.
- 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 master configurations for a wide variety of data center solutions.
- 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.
- GridSQL for EnterpriseDB Advanced Server (formerly ExtenDB)
- 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.
- pg_shard - extension that shards and replicates table across many servers, can also scale out Amazon RDS
- 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:
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.
- pglookout - PostgreSQL replication monitoring and failover daemon
- repmgr - replication manager for PostgreSQL clusters
Sources for more information located but not yet integrated into here.
Portable Scale-Out Benchmarks for PostgreSQL by Robert Hodgesnot available (2012-10-11)
High Availability and PostgreSQL by Gavin Sherrynot available (2012-10-11)
- Cascade Replication and Delayed servers on PostgreSQL 9.2
- Streaming Replication on PostgreSQL 9.3 by Afra Ahmad
- Replikation - Lösungen für PostgreSQL page covering this topic in German. It translates pretty well through Babelfish.
Sources for the initial information on this page include: