Replication Slots: The Game Changer

Forget almost everything you know about PostgreSQL replication. The replication slots feature in PostgreSQL 9.4 and beyond will significantly change how you can and should set up replication clusters and incremental backups, with great improvements in robustness and manageability.

Transcript

1. Replication Slots The Game Changer Peter Eisentraut
3. PostgreSQL replication: Why is it so complicated?
4. Timeline 7.1: WAL 8.0: PITR 8.3: p g _ s t a n d b y 9.0: hot standby, streaming replication 9.1: p g _ b a s e b a c k u p , synchronous replication 9.4: replication slots, logical replication
5. Setting up replication
6. i n i t d b
7. p o s t g r e s q l . c o n f
8. m a x _ w a l _ s e n d e r s = 1 0
9. w a l _ l e v e l = h o t _ s t a n d b y
10. h o t _ s t a n d b y = o n
11. p g _ h b a . c o n f
12. # T Y P E D A T A B A S E U S E R A D D R E S S M E T H O D h o s t r e p l i c a t i o n r e p l i c a t i o n 1 0 . 0 . 0 . 0 / 8 m d 5
13. p g _ c t l r e s t a r t
14. C R E A T E U S E R r e p l i c a t i o n R E P L I C A T I O N ; \ p a s s w o r d r e p l i c a t i o n
15. # o n s t a n d b y s u d o - u p o s t g r e s - i e c h o ' r e p l i c a t i o n : T H E P A S S W O R D ' > ~ / . p g p a s s c h m o d 0 6 0 0 ~ / . p g p a s s
16. p g _ b a s e b a c k u p \ - h p r i m a r y h o s t \ - U r e p l i c a t i o n \ - D $ P G D A T A \ - X s t r e a m - P
17. r e c o v e r y . c o n f
18. p r i m a r y _ c o n n i n f o = ' h o s t = p r i m a r y h o s t u s e r = r e p l i c a t i o n ' s t a n d b y _ m o d e = o n
19. p g _ c t l s t a r t
20. p g _ b a s e b a c k u p - R ?
21. Not done!
22. w a l _ k e e p _ s e g m e n t s
23. Setting up archiving
24. p o s t g r e s q l . c o n f a r c h i v e _ m o d e = o n a r c h i v e _ c o m m a n d = ' c p % p / s o m e / w h e r e / % f '
25. r e c o v e r y . c o n f r e s t o r e _ c o m m a n d = ' c p / s o m e / w h e r e / % f % p '
26. Options: local or remote copy scp rsync NFS p g _ a r c h i v e c l e a n u p
27. How about two standbys?
28. a r c h i v e _ c o m m a n d = ' r s y n c % p s t a n d b y 1 : : p g / % f & & r s y n c % p s t a n d b y 2 : : p g / % f '
29. a r c h i v e _ c o m m a n d = ' e c h o s t a n d b y 1 s t a n d b y 2 . . . | x a r g s - d " " - I { } - n 1 - P 0 - r r s y n c % p { } : : p g / % f '
30. What about fsync?
31. fsync capabilities cp: no dd: GNU coreutils SSH: OpenSSH 6.5 sftp-server (Jan 2014) rsync: patch or wrapper NFS: supported
32. Enter replication slots
33. p o s t g r e s q l . c o n f m a x _ r e p l i c a t i o n _ s l o t s = 8
34. S E L E C T * F R O M p g _ c r e a t e _ p h y s i c a l _ r e p l i c a t i o n _ s l o t ( ' n a m e ' ) ;
35. d b = # s e l e c t * f r o m p g _ r e p l i c a t i o n _ s l o t s ; s l o t _ n a m e | p l u g i n | s l o t _ t y p e | d a t o i d | d a t a b a s e | a c t i v e | x m i n | c a t a l o g _ x m i n | r e s t a r t _ l s n - - - - - - - - - - - - + - - - - - - - - + - - - - - - - - - - - + - - - - - - - - + - - - - - - - - - - + - - - - - - - - + - - - - - - + - - - - - - - - - - - - - - + - - - - - - - - - - - - - s t a n d b y 1 | | p h y s i c a l | | | t | | | 0 / 3 A 3 4 3 9 A 0 s t a n d b y 2 | | p h y s i c a l | | | t | | | 0 / 3 A 3 4 3 9 A 0 s t a n d b y 3 | | p h y s i c a l | | | t | | | 0 / 3 A 3 4 3 9 A 0
36. r e c o v e r y . c o n f p r i m a r y _ s l o t _ n a m e = ' f o o '
37. Replication slot benefits keeps (only) necessary WAL separates multiple standbys only one access control setup fsync on receiving side
38. pg_basebackup does not support replication slots (yet) keep w a l _ k e e p _ s e g m e n t s set
39. What about archiving?
40. p g _ r e c e i v e x l o g \ - D a r c h i v e d i r \ - - s l o t m y s l o t \ - h p r i m a r y h o s t - U r e p l i c a t i o n
41. p g _ r e c e i v e x l o g - - s y n c h r o n o u s . . .
42. p g _ r e c e i v e x l o g - - c r e a t e - s l o t m y s l o t
43. What about logical decoding?
44. Homework 1. Upgrade to PostgreSQL 9.4 p g _ u p g r a d e , if possible Londiste 2. Configure replication slots 3. Remove a r c h i v e _ c o m m a n d
45. Development/lobbying required increase default for m a x _ w a l _ s e n d e r s increase default for w a l _ l e v e l change default for h o t _ s t a n d b y integrate r e c o v e r y . c o n f with p o s t g r e s q l . c o n f better p g _ b a s e b a c k u p defaults