postgresql replication

22
Copyright © 2013 NTT DATA Corporation 27 April 2013 Fujii Masao (twitter: @fujii_masao) PostgreSQL replication Inaugural Indian PostgreSQL User Group meetup!

Upload: ntt-data-oss-professional-services

Post on 07-Dec-2014

238 views

Category:

Software


4 download

DESCRIPTION

PostgreSQL replication

TRANSCRIPT

Page 1: PostgreSQL replication

Copyright © 2013 NTT DATA Corporation

27 April 2013 Fujii Masao (twitter: @fujii_masao)

PostgreSQL replication

Inaugural Indian PostgreSQL User Group meetup!

Page 2: PostgreSQL replication

2 Copyright © 2013NTT DATA Corporation

Who am I ?

Database engineer in NTT DATA

• NTT DATA is global IT service and consulting company

PostgreSQL developer since 2008

One of main authors of PostgreSQL replication

Page 3: PostgreSQL replication

3 Copyright © 2013NTT DATA Corporation

Index

What's replication?

History

PostgreSQL replication

1. Features

2. Synchronous vs. Asynchronous

Page 4: PostgreSQL replication

Copyright © 2013 NTT DATA Corporation 4

What's replication?

Page 5: PostgreSQL replication

5 Copyright © 2013 NTT DATA Corporation

What's replication?

Client Client

Change Change

DB servers

Change

Replicate

DB servers

Change

Proxy server

Create a replica of the database on multiple servers

Page 6: PostgreSQL replication

6 Copyright © 2013 NTT DATA Corporation

Why is replication required?

Replication is an essential feature to get the system to work properly 24/7!

High Availability

Load Balancing

If one server crashes, another can keep providing the service We can easily reduce the downtime of the system

The load of query execution can be distributed to multiple servers We can improve the performance of whole system

Client Client

SQL SQL SQL

High Availability Load Balancing

DB servers DB servers

Page 7: PostgreSQL replication

Copyright © 2013 NTT DATA Corporation 7

History

Page 8: PostgreSQL replication

8 Copyright © 2013 NTT DATA Corporation

History of PostgreSQL replication

2007

2008

2009

2010

2012

2011

9.0(Sep 2010 release)

• Async reploication

9.1(Sep 2011 release)

• Synchronous replication • Improve replication monitoring • pg_basebackup

9.2(Sep 2012 release)

• Cascade replication • Backup from standby • New sync rep mode

Slony-I

Bucardo Londiste

Sequoia

PGCluster

PostgresForest

Postgres-R

Mammoth

PL/Proxy

pgpool-II

rubyrep

Postgres-XC

GridSQL

syncreplicator

Replication war !

Historically the community policy has been to avoid putting replication into core. But,

because of large user needs, replication has been introduced in 9.0. Then, replication keeps evolving steady.

Page 9: PostgreSQL replication

Copyright © 2013 NTT DATA Corporation 9

Features

Page 10: PostgreSQL replication

10 Copyright © 2013 NTT DATA Corporation

Single master / Multiple standbys

Single master

Multiple standbys Multiple standbys

Replicate Replicate

Write SQL Read SQL

Read SQL

Read SQL

Replication from single master to multiple standbys

Cascade replication

Only master accepts write query, both accepts read query

Read scalable, not write scalable

Page 11: PostgreSQL replication

11 Copyright © 2013 NTT DATA Corporation

Read-only query on standby

Online backup

– (Logical) pg_dump – (Physical) pg_basebackup

Maintenance command

– VACUUM, ANALYZE ※No need to do VACUUM and

ANALYZE on standby because the result of mantenance execution on master is automatically replicated to standby

Allow Query access

– SELECT – PREPARE, EXECUTE – CURSOR operation

Disallow DML

– INSERT, UPDATE, DELETE – SELECT FOR UPDATE

DDL – CREATE, DROP, ALTER

Temporary table

Page 12: PostgreSQL replication

12 Copyright © 2013 NTT DATA Corporation

Log-shipping

Transaction log (WAL) is shipped from master to standby

Standby is in recovery mode

Standby keeps the database current by replaying shipped WAL

Recovery Master Standby

Client Write SQL

WAL writing

WAL shipping

WAL writing

Page 13: PostgreSQL replication

13 Copyright © 2013 NTT DATA Corporation

Limitation by log-shipping

The followings must be the same between master and standby

H/W and OS architecture

PostgreSQL major version

Slony-I can be used for replication between different architectures and major versions

Master

Standby

64bit OS

PostgreSQL9.2.1

PostgreSQL9.1.0

32bit OS

64bit OS

PostgreSQL9.2.0

NG

NG

OK

2

1

Page 14: PostgreSQL replication

14 Copyright © 2013 NTT DATA Corporation

All database objects are replicated

Per-table granularity is not allowed

Slony-I supports per-table granularity replication

Per database cluster granularity

Per database cluster Per table

Master Standby Master Standby

Page 15: PostgreSQL replication

15 Copyright © 2013 NTT DATA Corporation

SQL distribution

PostgreSQL doesn't provide SQL distribution feature

Implement SQL distribution logic into client application

Use SQL distributor like pgpool-II

Client Client

Read SQL Write SQL

マスタ

Write SQL

Write/Read SQL

Distributor

スタンバイ

Read SQL

Master Standby

Implement logic Use SQL distributor

Page 16: PostgreSQL replication

16 Copyright © 2013 NTT DATA Corporation

Failover

PostgreSQL doesn't provide automatic failover feature

Standby can be promoted to master anytime(pg_ctl promote)

Automatic error detection and faliover requires clusterware

Client Client

Master

pgpool-II

Standby Master スタンバイ

Pacemaker pgpool-II

VIP

Pacemaker supports the resource agent for HA cluster using PostgreSQL replication!

Page 17: PostgreSQL replication

17 Copyright © 2013 NTT DATA Corporation

Monitoring

=# SELECT * FROM pg_stat_replication;

-[ RECORD 1 ]----+------------------------------

procpid | 26531

usesysid | 10

usename | postgres

application_name | tokyo

client_addr | 192.168.1.2

client_hostname |

client_port | 39654

backend_start | 2012-02-01 18:54:49.429459+09

state | streaming

sent_location | 0/406E7CC

write_location | 0/406E7CC

flush_location | 0/406E7CC

replay_location | 0/406E1B0

sync_priority | 1

sync_state | sync

Replication progress How far has master sent WAL? How far has standby written, flushed or replayed WAL?

Replication connection information Standby IP address, Port number, ROLE for replication、 Replication start time, etc

Replication status Current synchronous mode, Standby has already caught up with master?

Page 18: PostgreSQL replication

Copyright © 2013 NTT DATA Corporation 18

Synchronous vs. Asynchronous

Page 19: PostgreSQL replication

19 Copyright © 2013 NTT DATA Corporation

Replication mode

Replication mode is configurable

Asynchronous

Synchronous

Page 20: PostgreSQL replication

20 Copyright © 2013 NTT DATA Corporation

Asynchronous replication

COMMIT doesn't wait for the completion of replication

No guarantee that WAL has already arrived at standby at end of COMMIT

Data loss window on failover

Query on standby may see outdated data

Low performance impact on master

Recovery Master Standby

Client COMMIT

WAL writing

WAL shipping

WAL writing

OK

1

2

3

4

5

6

Committed data gets lost if failover happens between and operations

3 4

Page 21: PostgreSQL replication

21 Copyright © 2013 NTT DATA Corporation

Synchronous replication

COMMIT waits for completion of replication

WAL is guaranteed to be flushed in master and standby at end of COMMIT

No data loss on failover!

Relatively high performance impact on master

Query on standby may see outdated data

「Synchronous ≠ Committed data is available on standby immediately」

Recovery Master Standby

Client COMMIT

WAL writing

WAL shipping

WAL writing

OK

1

2

6

3

4

7

Reply 5

WAL shipping is synchronous, but recovery is asynchronous

Page 22: PostgreSQL replication

Copyright © 2011 NTT DATA Corporation

Copyright © 2013 NTT DATA Corporation

(Please omit notations when unnecessary) This document contains confidential Company information. Do not disclose it to third parties without permission from the Company.