PostgreSQL Replication Features in 9.0 Core - 2ndQuadrant

arizonahoopleΔιαχείριση Δεδομένων

28 Νοε 2012 (πριν από 4 χρόνια και 6 μήνες)

338 εμφανίσεις

©

2ndQuadrant Limited 2010
Simon Riggs
2nd Quadrant
simon@2ndQuadrant.com
PostgreSQL
Replication
Features in 9.0 Core
©

2ndQuadrant Limited 2010
PostgreSQL

Replication in Core

Streaming Replication

Hot Standby

Futures
©

2ndQuadrant Limited 2010
PostgreSQL 9.0

PostgreSQL 9.0

About to be released in Beta 3

On track for production in September

Significant commercial sponsorship

Skype, NTT, many other major users

Major new replication features

Streaming Replication

Hot Standby
©

2ndQuadrant Limited 2010
Teamwork

Primary authors

Masao Fujii
NTT

Simon Riggs
2ndQuadrant

Active review

Heikki Linnakangas
EnterpriseDB

Active test

Wide range of community members
©

2ndQuadrant Limited 2010
Why did we do this?

#1 Question from trade shows

When is Postgres getting replication (in core)?

#1 voted feature from existing users

Hot Standby
©

2ndQuadrant Limited 2010
Reasons

In-core matters to people

Perceived robustness and support

Ease of configuration and use

Total Cost of Ownership

Simple replication allows wider adoption

Overhead per-DDL change is costly

Accessible standby server reduces costs
©

2ndQuadrant Limited 2010
File-based replication in 9.0

Still possible to use file-based replication

...So whatever you had in 8.4 will work in 9.0

Though you may wish to use new features

recovery.conf should specify

standby_mode = off (which is the default)
©

2ndQuadrant Limited 2010
Streaming Replication

Benefits

Reduced loss of data at failover

Higher CPU impact – streaming rather than ftp

No need to use archive_timeout, so archive files
are always fully filled

Options

Walmgr – part of Skytools suite, works 8.2+

Streaming Replication – native in 9.0
©

2ndQuadrant Limited 2010
walmgr

Uses production API built into PostgreSQL 8.2+

Part of SkyTools package

Not as well integrated as Streaming Replication

Discuss: external vs. in-core development
©

2ndQuadrant Limited 2010
Streaming Replication (9.0)

Efficient real-time “log shipping”

Master → Slave replication using full security

Multiple concurrent slaves

Robust bi-modal design
©

2ndQuadrant Limited 2010
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
©

2ndQuadrant Limited 2010
Architecture

2 new processes

WALSender

WALReceiver

WAL Receiver connects to Primary using libpq

No need for additional firewall rules for port numbers
©

2ndQuadrant Limited 2010
Security

primary_conninfo specified in recovery.conf

Requires an explicit entry in pg_hba.conf to enable
standby server to attach for replication to primary. Normal
access rule is not sufficient

e.g. hostssl
replication
user ip-address method

must
not
specify dbname=replication on primary_conninfo

no need to create a database called replication

Access for replication must be a superuser

SSL connections supported, all normal libpq
authentication techniques supported

Connections and failures logged on both
primary and standby
©

2ndQuadrant Limited 2010
WAL Sender
©

2ndQuadrant Limited 2010
WAL Receiver
©

2ndQuadrant Limited 2010
Monitoring

Explicit keepalives not implemented (yet!?)

Can monitor progress using functions to
discover WAL locations on both nodes

Primary

pg_current_log_write_location()

Standby

pg_last_xlog_receive_location()

pg_last_xlog_apply_location()
©

2ndQuadrant Limited 2010
Initial State
©

2ndQuadrant Limited 2010
Archiving
©

2ndQuadrant Limited 2010
Futures

Relay nodes

Synchronous Replication

Additional usability
©

2ndQuadrant Limited 2010
Streaming Relay Nodes
©

2ndQuadrant Limited 2010
Synchronous Replication

Data transport with guarantees

Various modes under discussion

Per-transaction/mixed-mode possible
©

2ndQuadrant Limited 2010
Additional Usability

Switchback

WAL Compression before sending

WAL filtering on apply

Query balancing
©

2ndQuadrant Limited 2010
In-Core Replication

Many requirements, many approaches

No single best way forwards

In-Core facilities essential

Eventually...
©

2ndQuadrant Limited 2010
PostgreSQL