8/6/2019 Replication Postgres

1/21

PostgreSQLReplication Solutions

BRUCEMOMJIAN,

ENTERPRISEDB

April, 2011

AbstractReplication is a complex feature. POSTGRESQL supports a varietyof replication options.

Creative Commons Attribution License http://momjian.us/presentations

8/6/2019 Replication Postgres

2/21

Uses for Replication

Theolotech.com

PostgreSQL Replication Solutions 2

8/6/2019 Replication Postgres

3/21

Fail Over

PostgreSQL Replication Solutions 3

8/6/2019 Replication Postgres

4/21

Load Balancing

PostgreSQL Replication Solutions 4

8/6/2019 Replication Postgres

5/21

Data Warehousing

PostgreSQL Replication Solutions 5

8/6/2019 Replication Postgres

6/21

Remote Servers

PostgreSQL Replication Solutions 6

8/6/2019 Replication Postgres

7/21

Mobile Servers

PostgreSQL Replication Solutions 7

8/6/2019 Replication Postgres

8/21

Replication Solutions

Taotaomona Computing

PostgreSQL Replication Solutions 8

8/6/2019 Replication Postgres

9/21

Shared Storage

NetworkAttachedStorage (NAS)

No overhead

No data loss on fail-over

Slave cannot execute queries

PostgreSQL Replication Solutions 9

8/6/2019 Replication Postgres

10/21

Storage Mirroring

DRBD

No overhead on master

Synchronous or asynchronous

Possible data loss on fail-overwhen using asynchronous

Slave cannot execute queries

PostgreSQL Replication Solutions 10

8/6/2019 Replication Postgres

11/21

Continuous Archiving (PITR)

Write

AheadLog

(WAL)

No overhead on master

Possible data loss on fail-overwhen using asynchronous

Slaves can execute queries inPostgreSQL 9.0

Synchronous option available inPostgreSQL 9.1

PostgreSQL Replication Solutions 11

8/6/2019 Replication Postgres

12/21

Slony

Asynchronous

Triggers add overhead to themaster

Possible data loss on fail-over

Replication possible even over

slow links

Slave can execute read-onlyqueries

Table-level granularity allowscomplex data partitioningconfigurations

PostgreSQL Replication Solutions 12

8/6/2019 Replication Postgres

13/21

Slony - Cascading Slaves

! ! ! !

! ! ! !

! ! ! !

" " " "

" " " "

# # # #

# # # #

$ $ $ $

$ $ $ $

$ $ $ $

$ $ $ $

$ $ $ $

$ $ $ $

% % % %

% % % %

% % % %

% % % %

% % % %

& & & &

& & & &

& & & &

& & & &

' ' ' '

' ' ' '

' ' ' '

( ( ( (

( ( ( (

) ) ) )

) ) ) )

0 0 0 0

0 0 0 0

0 0 0 0

0 0 0 0

0 0 0 0

1 1 1 1

1 1 1 1

1 1 1 1

1 1 1 1

1 1 1 1

2 2 2 2

2 2 2 2

2 2 2 2

3 3 3 3

3 3 3 3

3 3 3 3

4 4 4 4

4 4 4 4

5 5 5 5

5 5 5 5

6

6

6

6

6

6

6

6

6

6

6

6

6

6

8

8

8

8

8

8

8

8

8

8

8

8

8

8

9

9

9

9

9

9

9

9

9

9

9

9

9

9

@

@

@

@

@

@

@

@

@

@

@

@

@

@

A

A

A

A

A

A

A

A

A

A

A

A

A

A

B

B

B

B

B

B

B

B

B

B

B

B

B

B

C

C

C

C

C

C

C

C

C

C

C

C

C

C

D

D

D

D

D

D

D

D

D

D

D

D

D

D

E

E

E

E

E

E

E

E

E

E

E

E

E

E

F

F

F

F

F

F

F

F

F

F

F

F

F

F

G

G

G

G

G

G

G

G

G

G

G

G

G

G

H

H

H

H

H

H

H

H

H

H

H

H

H

H

I

I

I

I

I

I

I

I

I

I

I

I

I

I

P

P

P

P

P

P

P

P

P

P

P

P

P

P

PostgreSQL Replication Solutions 13

8/6/2019 Replication Postgres

14/21

Slony Internals

Subscribers

Other

Slonik

...

CREATE

TABLE

! !

! !

! !

! !

! !

! !

! !

! !

" " "

" " "

" " "

" " "

" " "

" " "

" " "

" " "

# # #

# # #

# # #

# # #

# # #

# # #

# # #

# # #

$ $ $

$ $ $

$ $ $

$ $ $

$ $ $

$ $ $

$ $ $

$ $ $

% % %

% % %

% % %

% % %

% % %

% % %

% % %

% % %

& &

& &

& &

& &

& &

& &

& &

& &

' '

' '

' '

' '

' '

' '

' '

' '

modifications

SubscriberOriginUser User

sl_log sl_log

CREATETRIGGER

fills sl_logTRIGGERCREATE

prevents

PostgreSQL Replication Solutions 14

8/6/2019 Replication Postgres

15/21

Slony Multi-Slave

! ! ! ! ! ! ! ! !

! ! ! ! ! ! ! ! !

! ! ! ! ! ! ! ! !

! ! ! ! ! ! ! ! !

! ! ! ! ! ! ! ! !

! ! ! ! ! ! ! ! !

! ! ! ! ! ! ! ! !

! ! ! ! ! ! ! ! !

" " " " " " " " "

" " " " " " " " "

" " " " " " " " "

" " " " " " " " "

" " " " " " " " "

" " " " " " " " "

" " " " " " " " "

" " " " " " " " "

# # # # # # # # #

# # # # # # # # #

# # # # # # # # #

# # # # # # # # #

# # # # # # # # #

# # # # # # # # #

# # # # # # # # #

# # # # # # # # #

$ $

$ $

$ $

$ $

$ $

$ $

$ $

$ $

% %

% %

% %

% %

% %

% %

% %

% %

& &

& &

& &

& &

& &

& &

& &

& &

' '

' '

' '

' '

' '

' '

' '

' '

( ( (

( ( (

( ( (

( ( (

( ( (

( ( (

( ( (

( ( (

) ) )

) ) )

) ) )

) ) )

) ) )

) ) )

) ) )

) ) )

0 0

0 0

0 0

0 0

0 0

0 0

0 0

0 0

1 1

1 1

1 1

1 1

1 1

1 1

1 1

1 1

2 2

2 2

2 2

2 2

2 2

2 2

2 2

2 2

3 3

3 3

3 3

3 3

3 3

3 3

3 3

3 3

4 4 4

4 4 4

4 4 4

4 4 4

4 4 4

4 4 4

4 4 4

4 4 4

5 5 5

5 5 5

5 5 5

5 5 5

5 5 5

5 5 5

5 5 5

5 5 5

6 6 6

6 6 6

6 6 6

6 6 6

6 6 6

6 6 6

6 6 6

6 6 6

7 7 7

7 7 7

7 7 7

7 7 7

7 7 7

7 7 7

7 7 7

7 7 7

8 8

8 8

8 8

8 8

8 8

8 8

8 8

8 8

9 9

9 9

9 9

9 9

9 9

9 9

9 9

9 9

@ @

@ @

@ @

@ @

@ @

@ @

@ @

@ @

A A

A A

A A

A A

A A

A A

A A

A A

sl_log

SubscriberUser

sl_log

SubscriberUser

sl_log

OriginUser

PostgreSQL Replication Solutions 15

8/6/2019 Replication Postgres

16/21

Slony Master Switching

! ! ! ! ! ! ! !

! ! ! ! ! ! ! !

! ! ! ! ! ! ! !

! ! ! ! ! ! ! !

! ! ! ! ! ! ! !

! ! ! ! ! ! ! !

! ! ! ! ! ! ! !

! ! ! ! ! ! ! !

" " " " " " " "

" " " " " " " "

" " " " " " " "

" " " " " " " "

" " " " " " " "

" " " " " " " "

" " " " " " " "

" " " " " " " "

# # # # # # # #

# # # # # # # #

# # # # # # # #

# # # # # # # #

# # # # # # # #

# # # # # # # #

# # # # # # # #

# # # # # # # #

$ $ $ $ $ $ $ $

$ $ $ $ $ $ $ $

$ $ $ $ $ $ $ $

$ $ $ $ $ $ $ $

$ $ $ $ $ $ $ $

$ $ $ $ $ $ $ $

$ $ $ $ $ $ $ $

$ $ $ $ $ $ $ $

% % % % % % % %

% % % % % % % %

% % % % % % % %

% % % % % % % %

% % % % % % % %

% % % % % % % %

% % % % % % % %

% % % % % % % %

& & & & & & & &

& & & & & & & &

& & & & & & & &

& & & & & & & &

& & & & & & & &

& & & & & & & &

& & & & & & & &

& & & & & & & &

& & & & & & & &

& & & & & & & &

& & & & & & & &

& & & & & & & &

& & & & & & & &

' ' ' ' ' ' ' '

' ' ' ' ' ' ' '

' ' ' ' ' ' ' '

' ' ' ' ' ' ' '

' ' ' ' ' ' ' '

' ' ' ' ' ' ' '

' ' ' ' ' ' ' '

' ' ' ' ' ' ' '

' ' ' ' ' ' ' '

' ' ' ' ' ' ' '

' ' ' ' ' ' ' '

' ' ' ' ' ' ' '

' ' ' ' ' ' ' '

( ( ( ( ( ( ( (

( ( ( ( ( ( ( (

( ( ( ( ( ( ( (

( ( ( ( ( ( ( (

( ( ( ( ( ( ( (

( ( ( ( ( ( ( (

( ( ( ( ( ( ( (

( ( ( ( ( ( ( (

( ( ( ( ( ( ( (

( ( ( ( ( ( ( (

( ( ( ( ( ( ( (

( ( ( ( ( ( ( (

( ( ( ( ( ( ( (

) ) ) ) ) ) ) )

) ) ) ) ) ) ) )

) ) ) ) ) ) ) )

) ) ) ) ) ) ) )

) ) ) ) ) ) ) )

) ) ) ) ) ) ) )

) ) ) ) ) ) ) )

) ) ) ) ) ) ) )

) ) ) ) ) ) ) )

) ) ) ) ) ) ) )

) ) ) ) ) ) ) )

) ) ) ) ) ) ) )

) ) ) ) ) ) ) )

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

1 1 1 1 1 1 1 1

1 1 1 1 1 1 1 1

1 1 1 1 1 1 1 1

1 1 1 1 1 1 1 1

1 1 1 1 1 1 1 1

1 1 1 1 1 1 1 1

1 1 1 1 1 1 1 1

1 1 1 1 1 1 1 1

2 2

2 2

2 2

2 2

2 2

2 2

2 2

2 2

3 3

3 3

3 3

3 3

3 3

3 3

3 3

3 3

4 4

4 4

4 4

4 4

4 4

4 4

4 4

4 4

5 5

5 5

5 5

5 5

5 5

5 5

5 5

5 5

6 6

6 6

6 6

6 6

6 6

6 6

6 6

6 6

7 7

7 7

7 7

7 7

7 7

7 7

7 7

7 7

8 8

8 8

8 8

8 8

8 8

8 8

8 8

8 8

9 9

9 9

9 9

9 9

9 9

9 9

9 9

9 9

@ @

@ @

@ @

@ @

@ @

@ @

@ @

@ @

A A

A A

A A

A A

A A

A A

A A

A A

B B

B B

B B

B B

B B

B B

B B

B B

C C

C C

C C

C C

C C

C C

C C

C C

D D

D D

D D

D D

D D

D D

D D

D D

E E

E E

E E

E E

E E

E E

E E

E E

F F

F F

F F

F F

F F

F F

F F

F F

G G

G G

G G

G G

G G

G G

G G

G G

H H

H H

H H

H H

H H

H H

H H

H H

I I

I I

I I

I I

I I

I I

I I

I I

P P

P P

P P

P P

P P

P P

P P

P P

Q Q

Q Q

Q Q

Q Q

Q Q

Q Q

Q Q

Q Q

R R

R R

R R

R R

R R

R R

R R

R R

S S

S S

S S

S S

S S

S S

S S

S S

T T

T T

T T

T T

T T

T T

T T

T T

U U

U U

U U

U U

U U

U U

U U

U U

User

sl_log sl_log

Subscriber OriginUser User

sl_log sl_log

SubscriberOrigin User

PostgreSQL Replication Solutions 16

8/6/2019 Replication Postgres

17/21

Bucardo

with Conflict Resolution

Asynchronous

Similar to Slony, exceptmulti-master with conflict

resolution

Conflict resolution rules areuser-configurable

PostgreSQL Replication Solutions 17

8/6/2019 Replication Postgres

18/21

Pgpool II

connection daemon

host

to a single

SELECTINSERT, UPDATE,

DELETE to all

hosts

! ! ! !

! ! ! !

! ! ! !

" " " "

" " " "

# # # #

# # # #

$

$

$

$

$

$

$

$

$

$

$

$

$

$

&

&

&

&

&

&

&

&

&

&

&

&

&

&

'

'

'

'

'

'

'

'

'

'

'

'

'

'

(

(

(

(

(

(

(

(

(

(

(

(

(

(

)

)

)

)

)

)

)

)

)

)

)

)

)

)

0

0

0

0

0

0

0

0

0

0

0

0

0

0

1

1

1

1

1

1

1

1

1

1

1

1

1

1

2

2

2

2

2

2

2

2

2

2

2

2

2

2

Automatically load-balances readqueries

Queries with non-deterministicbehavior can cause inconsistency

Allows parallel query executionon all nodes

Also does connection pooling and

query caching

PostgreSQL Replication Solutions 18

8/6/2019 Replication Postgres

19/21

Pgpool II With Master/Slave Replication

connection daemon

host

to any single

SELECTINSERT, UPDATE,

DELETE to master

host

Slave SlaveMaster

replication

replication

! ! ! !

! ! ! !

! ! ! !

" " " "

" " " "

# # # #

# # # #

$

$

$

$

$

$

$

$

$

$

$

$

$

$

&

&

&

&

&

&

&

&

&

&

&

&

&

&

'

'

'

'

'

'

'

'

'

'

'

'

'

'

(

(

(

(

(

(

(

(

(

(

(

(

(

(

)

)

)

)

)

)

)

)

)

)

)

)

)

)

0

0

0

0

0

0

0

0

0

0

0

0

0

0

1

1

1

1

1

1

1

1

1

1

1

1

1

1

2

2

2

2

2

2

2

2

2

2

2

2

2

2

Master/slave replication avoids theproblem of non-deterministic

queries producing different resultson different hosts.

PostgreSQL Replication Solutions 19

8/6/2019 Replication Postgres

20/21

PGCluster

Load Balancer

Cluster

Replicator

! ! ! !

! ! ! !

" " " "

" " " "

" " " "

# # # #

# # # #

# # # #

$ $ $ $

$ $ $ $

$ $ $ $

$ $ $ $

$ $ $ $

$ $ $ $

% % % %

% % % %

% % % %

% % % %

% % % %

& & & &

& & & &

& & & &

& & & &

& & & &

& & & &

' ' ' '

' ' ' '

' ' ' '

' ' ' '

' ' ' '

( ( ( (

( ( ( (

( ( ( (

) ) ) )

) ) ) )

) ) ) )

0

0

0

0

0

0

0

0

0

0

0

0

0

0

2

2

2

2

2

2

2

2

2

2

2

2

2

2

3

3

3

3

3

3

3

3

3

3

3

3

3

3

4

4

4

4

4

4

4

4

4

4

4

4

4

4

5

5

5

5

5

5

5

5

5

5

5

5

5

5

6

6

6

6

6

6

6

6

6

6

6

6

6

6

7

7

7

7

7

7

7

7

7

7

7

7

7

7

8

8

8

8

8

8

8

8

8

8

8

8

8

8

9

9

9

9

9

9

9

9

9

9

9

9

9

9

@

@

@

@

@

@

@

@

@

@

@

@

@

@

High performance cost

Still experimental

PostgreSQL Replication Solutions 20

8/6/2019 Replication Postgres

21/21