Postgres Replication Alternatives

31 Oct

Replication Related Concepts

  • Clustering vs Replication
    • Used interchangeably and similar concepts in the DBMS World
    • Clustering in the general sense is distributing load across many servers that appear to an outside application as one server
    • Replication in the general sense involves one master server handling mostly writes and one or more slaves handling reads only. A more advanced variation is that of the master-master configuration, which allows to scale writes as well by having several servers writing at the same time.
    • There are several definitions of clustering, but it can generally be viewed as something that has membership, messaging, redundancy, and automatic failover capabilities. Replication, in contrast, is simply a way to send messages (data) from one server to another.
  • Replication Types
    • Synchronous
      • Postgres 9.1 built-in Synchronous support
      • PgPool II
    • Asynchronous
      • Postgres 9.0 Streaming built-in
      • Slony-1
      • Bucardo
  • Replication Modes
    • Master-Master
    • Master-Slave
    • Statement-Based-Middleware
  • Connection Pooling
  • Load Balancing
  • High Availability
    • Automatic Failover
    • Manual Failover
  • Parallel Queries
  • Performance
    • Asynchronous is Faster
    • Synchronous is Slower

Repmgr vs PgPoolII comparison

Feature Repmgr PgPool II
Proxy Process/Service No intermediary Process/Service. Clients Access Database Directly Clients connect to PgPool II Service which delegates to Postgres servers.
Replication Type Postgres 9.1 Synchronous Replication PgPool II Synchronous Replication
Replication Mode Master Slave Statement Based Middleware
Connection Pooling None. But can be used with Pgbouncer Built-in Pgbouncer
Load Balancing None. PgBouncer can use a TCP load balancer such as LVS or HAProxy Built-In Load Balancing
High Availability Yes. Custom Scripts Available and Monitoring Daemon (repmgrd) Yes. Automatic Failover scripts available
Parallel Queries None available out of the box. Avaialble out of the box
Performance Synchronous is generally slower than Asynchronous. Synchronous (using a custom protocol.)

Conclusion

  • PgPoolII has more features out-of-the-box but Repmgr seems a lot more flexible.
  • RepMgr + pgbouncer should give us all the features we are looking for.

References

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: