An opinionated guide to PostgreSQL replication

Setting up and managing replicated PostgreSQL instances can be a daunting task. There are many facilities, options, and configuration settings to navigate, with difficult to find guidance.

In this talk, I present specific instructions that will lead to success. Starting from how to install and name things, to which options and settings to use, and which things to forget about, I show a way through the jungle.

Transcript

1. An opinionated guide to PostgreSQL replication Peter Eisentraut peter.eisentraut@2ndquadrant.com @petereisentraut
2. Why replicate? performance/scaling reliability/availability
3. hardware bare metal or virtual lots of RAM, CPU, SSD disks know how to get additionals/replacements
4. network sanity check IP addresses multiple networks firewalls DNS, reverse DNS
5. operating system pick one of these: Debian Ubuntu (LTS) Red Hat EL CentOS
6. operating system consider embracing systemd Debian >= 8/jessie Ubuntu >= 15.04 (not LTS) Red Hat EL >= 7 CentOS >= 7
7. configuration management pick one: Chef, Puppet, Ansible, Salt, ...
8. PostgreSQL version >= PostgreSQL 9.4
9. installation use apt/dnf/yum a p t . p o s t g r e s q l . o r g , y u m . p o s t g r e s q l . o r g for newer versions and more tools configuration management
10. terminology host instance replication group (instance group) primary, secondary, standby, spare
11. naming hosts: db001, db002, db003 replication groups: pg001, pg002, pg003 (cname?) instances: pg001a, pg001b, pg001c (c l u s t e r _ n a m e , data dir path?) don't get attached to names
12. connection routing pgbouncer [ d a t a b a s e s ] a p p n a m e = h o s t = . . . o t h e r a p p = h o s t . . .
13. basic configuration l i s t e n _ a d d r e s s e s p o r t ? m a x _ c o n n e c t i o n s s u p e r u s e r _ r e s e r v e d _ c o n n e c t i o n s s h a r e d _ b u f f e r s w o r k _ m e m e f f e c t i v e _ c a c h e _ s i z e c h e c k p o i n t _ s e g m e n t s /m a x _ w a l _ s i z e c h e c k p o i n t _ t i m e o u t c h e c k p o i n t _ c o m p l e t i o n _ t a r g e t r a n d o m _ p a g e _ c o s t t r a c k _ f u n c t i o n s = a l l s s l = o n ?
14. centralized logging use syslog (rsyslog) to collect logs
15. logging configuration l o g _ d e s t i n a t i o n = ' s y s l o g ' l o g g i n g _ c o l l e c t o r = o n l o g _ m i n _ d u r a t i o n _ s t a t e m e n t = ? l o g _ m i n _ e r r o r _ s t a t e m e n t = w a r n i n g l o g _ l i n e _ p r e f i x = ' % q u s e r = % u , d b = % d , a p p = % a ' # l o g _ l i n e _ p r e f i x = ' % t [ % p ] : [ % l - 1 ] % q u s e r = % u , d b = % d , a p p = % a ' l o g _ c h e c k p o i n t s = o n l o g _ c o n n e c t i o n s = o n l o g _ d i s c o n n e c t i o n s = o n l o g _ l o c k _ w a i t s = o n l o g _ t e m p _ f i l e s = 0
16. replication configuration w a l _ l e v e l = h o t _ s t a n d b y / r e p l i c a m a x _ r e p l i c a t i o n _ s l o t s = 3 2 m a x _ w a l _ s e n d e r s = 3 2 h o t _ s t a n d b y = o n h o t _ s t a n d b y _ f e e d b a c k = o n w a l _ k e e p _ s e g m e n t s = 1 0 2 4 ?
17. don't use a r c h i v e _ m o d e = o f f a r c h i v e _ c o m m a n d = ' ' Use p g _ r e c e i v e x l o g if you need an archive.
18. hba C R E A T E U S E R r e p l u s e r R E P L I C A T I O N ; h o s t r e p l i c a t i o n r e p l u s e r l o c a l h o s t x x x ~ p o s t g r e s / . p g p a s s
19. replication slots S E L E C T 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 ( ' I N S T A N C E _ N A M E ' ) ;
20. base backup p g _ b a s e b a c k u p - c f a s t - P - v \ - D D I R \ - h H O S T - p P O R T - U r e p l u s e r \ - - s l o t I N S T A N C E _ N A M E \ - X s t r e a m
21. recovery.conf s t a n d b y _ m o d e = o n p r i m a r y _ c o n n i n f o = ' h o s t = H O S T p o r t = P O R T u s e r = r e p l u s e r a p p l i c a t i o n _ n a m e = I N S T A N C E _ N A M E ' p r i m a r y _ s l o t _ n a m e = ' I N S T A N C E _ N A M E ' r e c o v e r y _ t a r g e t _ t i m e l i n e = l a t e s t
22. starting secondary s e r v i c e p o s t g r e s q l s t a r t s y s t e m c t l s t a r t p o s t g r e s q l p g _ c t l s t a r t - w
23. monitoring monitor everything: disk space CPU connections errors vacuum long-running queries
24. monitoring replication check p g _ i s _ i n _ r e c o v e r y ( ) study p g _ s t a t _ r e p l i c a t i o n seconds lag: e x t r a c t ( e p o c h f r o m c u r r e n t _ t i m e s t a m p - p g _ l a s t _ x a c t _ r e p l a y _ t i m e s t a m p ( ) ) byte lag: p g _ x l o g _ l o c a t i o n _ d i f f ( p g _ s t a t _ r e p l i c a t i o n . s e n t _ l o c a t i o n , p g _ s t a t _ r e p l i c a t i o n . r e p l a y _ l o c a t i o n )
25. switchover 1. update pgbouncer config 2. s e r v i c e p o s t g r e s q l s t o p / s y s t e m c t l s t o p p o s t g r e s q l / p g _ c t l s t o p - m f a s t - w 3. p g _ c t l p r o m o t e 4. loop until p g _ i s _ i n _ r e c o v e r y ( ) is false 5. prime cache (V A C U U M ?) automate!
26. failover 1. update pgbouncer config 2. p g _ c t l p r o m o t e careful with automatic failover
27. practice, practice, practice become a switchover wizard
28. bonus repmgr consul/etcd pglogical