managing replication of postgresql, simon riggs

© 2ndQuadrant Limited 2010

ManagingPostgreSQLReplication


Who Am I?

• Simon Riggs

• Major Developer on PostgreSQL project

• CTO, 2ndQuadrant


Replication Use Cases

• High Availability

• Scalability

• Protection

• Total Cost of Ownership

• Exploiting success


Replication Mechanisms

• Trigger-based replication– User space solution

• Log-based replication– Integrated development


Log Shipping Developments

• 8.0 – Point in Time Recovery, Full WAL info

• 8.2 – Restartable Recovery, Log Switching, Streaming API

• 8.3 – Full Page Optimization, pg_standby

• 8.4 – BgWriter during Recovery

• 9.0 – Streaming Replication Hot Standby


Streaming Replication (9.0)

• Efficient real-time “log shipping”

• Master → Slave replication using full security

• Multiple concurrent slaves

• Robust bi-modal design


WAL Sender


WAL Receiver


Hot Standby

• Read-only access while “in recovery”

• Equivalent of Oracle 11g Active Data Guard

• Load balanced read scalability

• Query off-load from main write node

• Multiple highly available standby nodes


Capabilities

• High Performance

• Integrated

• Ease of Use

• Query Access

• Relay

• Selectivity

• Query Farm


Capabilities with Tools

• High Performance

• Integrated

• Ease of Use

• Query Access

• Relay

• Selectivity

• Query Farm


Repmgr


Repmgr

• Simplify standby management

• Monitor replication delays

• Provide feedback mechanisms


Future: Sync Replication (9.1)

• User-controlled robustness

• High performance design

• Simplicity

• Additional flexibility through registration


Future: Loose Coupling

• Allows distributed database

• High Availability and Disaster Recovery

• No shared resources – better scalability

• “Cellular” approach


PostgreSQL Books

• http://www.2ndQuadrant.com/books/

managing replication of postgresql, simon riggs

Technology