PostgreSQL 9 Administration Cookbook Second Edition

Table of ContentsPreface xiChapter 1: Understanding the Concepts of Replication 1The CAP theorem and physical limitations of replication 2Understanding the CAP theorem 2Understanding the limits of physics 4Different types of replication 5Synchronous versus asynchronous replication 6ConsideriPostgre SQL ReplicationSecond editionCopyright o 2015 Packt PublishingAll rights reserved No part of this book may be reproduced stored in a retrievalsystem, or transmitted in any form or by any means, without the prior writtenpermission of the publisher, except in the case of brief quotations embedded incritical articles or reviewsEvery effort has been made in the preparation of this book to ensure the accuracyof the information presented. However, the information contained in this bookis sold without warranty either express or implied. Neither the author nor packtPublishing, and its dealers and distributors will be held liable for any damagescaused or alleged to be caused directlv or indirectly by this bookPackt Publishing has endeavored to provide trademark information about all of thecompanies and products mentioned in this book by the appropriate use of capitalsHowever, Packt Publishing cannot guarantee the accuracy of this informationFirst published: August 2013cond edition: July 2015Production reference: 1240715Published by Packt Publishing LtdLivery place35 Livery streetBirmingham B3 2PB, UKISBN978-178355-060-9www.packtpub.comwww.allitebooks.comCreditsAuthorProject CoordinatorHans-Jurgen Schonigjay KushlaniReviewersProofreaderSwathi KurunjiSafis EditingJeff lawsonMauricio linharesIndexerShaun m. thomasPriya SaneTomas∨ ondraGraphicsSheetal AuteCommissioning Editorartikey FandeyProduction coordinatorKomal ramchandaniAcquisition EditorLarissa pintoCover workKomal ramchandaniContent Development EditorNikhil potdukheTechnical editorManali gonsalvesCopy EditDipti MankameVikrant phadkewww.allitebooks.comabout the authorHans-Jurgen Schonig has 15 years of experience with PostgreSQL. He is theCEO of a PostgreSQL consulting and support company called Cybertec Schonig&schoniggmbh(www.postGresql-support.de).IThassuccessfullyservedcountless customers around the globeBefore founding Cybertec Schonig schonig gmbH in 2000, he worked as adatabase developer at a private research company focusing on the Austrian labormarket, where he primarily focused on data mining and forecast models. He hasalso written several books a bout postgresqlThis book is dedicated to all the members of the Cybertec familywho have supported me over the years and have proven to be trueprofessionals. Without my fellow technicians here at Cybertec, thisbook would not have existed I especially want to thank ants aasmafor his technical input and Florian Ziegler for helping out with theproofreading and graphical stuffSpecial thanks also go to my girl, Sonja Stadtner, who has given meall the personal support. Somehow, she managed to make me go tosleep when I was up late at night working on the initial draftswww.allitebooks.comabout the reviewersSwathi Kurunji is a software engineer at Actian Corporation She recentlycompleted her PhD in computer science from the University of MassachusettsLowell (UMass Lowell), USA. She has a keen interest in database systems. Her PhDresearch involved query optimization, big data analysis, data warehousing, andcloud computing. Swathi has shown excellence in her field of study through researchpublications at international conferences and in journals. She has received awardsand scholarships from UMass lowell for research and academicsSwathi also has a masters of science degree in computer science from UMass Lowelland a bachelor,'s of engineering degree in information science from KVGCE in IndiaDuring her studies at UMass Lowell, she worked as a teaching assistant, helpingprofessors in teaching classes and labs, designing projects, and grading examsShe has worked as a software development intern with IT companies such as EMCand SAP. At EMC, she gained experience on Apache Cassandra data modeling andperformance analysis. At SAP, she gained experience on the infrastructure/clustermanagement components of the Sybase IQ product. She has also worked withWipro Technologies in India as a project engineer, managing application serversShe has extensive experience with database systems such as Apache Cassandra,Sybase iq, Oracle, mysql, and ms access. Her interests include softwaredesign and development, big data analysis, optimization of databases, and cloudcomputingHerlinkedinprofileishttp://www.linkedincom/pub/swathikurunji/49/578/30a/Swathi has previously reviewed two books, Cassandra data Modeling and analysisand Mastering Apache Cassandra, both by Packt Publishingi would like to thank my husband and my family for alltheir supportwww.allitebooks.comJeff Lawson has been a user and fan of PostgreSQL since he noticed it in 2001.Overthe years, he has also developed and deployed applications for IBM DB2, OracleMySQL, Microsoft sQL Server, Sybase, and others, but he has always preferredPostgresql because of its balance of features and openness. much of his experiencehas spanned development for Internet-facing websites and projects that requiredhighly scalable databases with high availability or provisions for disaster recoveryJeff currently works as the director of sottware development at Flight Aware, whichis an airplane tracking website that uses PostgreSQL and other pieces of open sourcesoftware to store and analyze the positions of thousands of flights that fly worldwideevery day. He has extensive experience in software architecture, data security, andnetworking protocol design because of his roles as a software engineer at Univa/United Devices, Microsoft, NASA's Jet Propulsion Laboratory, and WolfeTech. Hewas a founder of distributed net, which pioneered distributed computing in the1990s, and continues to serve as the chief of operations and a member of the boardHe earned a BSc in computer science from I larvey Mudd CollegeJeff is fond of cattle, holds an FAA private pilot certificate, and owns an airplanein houston texasMauricio Linhares is a technical leader of the parsing and machine learningtcam at The neat Company. At neat, he helps his team scale their solutions onthe cloud and deliver fast results to customers he is the creator and maintainer ofasyncaScala-basedPostgresqldatabasedriver(https://github.com/mauricio/postgresql-async), and has been a PostgreSQL user and proponent for many yearsShaun m. Thomas has been working with PostgreSQL since late 2000. He hapresented at Postgres open conferences in 2011, 2012, and 2014 on topics suchas handling extreme throughput high availability server redundancy failovertechniques, and system monitoring With the recent publication of Packt PublishingsPostgresqL 9 High Availability Cookbook he hopes to make life easier for DBAs usingPostgresQl in enterprise environmentsCurrently, Shaun serves as the database architect at Peak6, an options trading firmwith a PostgresQL constellation of over 100 instances, one of which is over 15 tBIn sizeHe wants to prove that Postgre SQL is more than ready for major installationswww.allitebooks.comTomas Vondra has been working with PostgreSQL since 2003, and although hehad worked with various other databases -both open-source and proprietary -heinstantly fell in love with Postgre SQL and the community around itHe is currently working as an engineer at 2ndQuadrant, one of the companies thaprovide support, training, and other services rclated to PostgreSQL. Previously,he worked as a PostgresQL specialist for GoodData, a company that operates a bicloud platform built on postgreSQL. He has extensive experience with performancetroubleshooting, tuning, and benchmarkingIn his free time, he usually writes postgre SQL extensions or patches or he hackssomething related to PostgreSQLwww.allitebooks.comWww. Packtpub. comSupport files, eBooks, discount offers, and moreForsupportfilesanddownloadsrelatedtoyourbookpleasevisitwww.packtpub.cOmDid you know that Packt offers e Book versions of every book published, with PDFandepuBfilesavailable?Youcanupgradetotheebookversionatwww.Packtpubcom and as a print book customer, you are entitled to a discount on the e Book copyGetintouchwithusatservice@packtpub.comformoredetailsAtwww.Packtpub.comyoucanalsoreadacollectionoffreetechnicalarticlessign up for a range of free newsletters and receive exclusive discounts and offerson packt books and ebooksPACKTLIBhttps://www2.packtpub.com/books/subscription/packtlibDo you need instant solutions to your ll questions? PacktLib is Packt's online digitalbook library. Here, you can search, access, and read Packt's entire library of booksWhy subscribe?Fully searchable across every book published by PacktCopy and paste, print, and bookmark contentOn demand and accessible via a web browserFree access for packt account holdersIfyouhaveanaccountwithpacktatwww.packtpubcomyoucanusethistoaccessPacktlib today and view 9 entirely free books. Simply use your login credentials forimmediate accesswww.allitebooks.comTable of contentsPrefaceChapter 1: Understanding the Concepts of ReplicationThe cap theorem and physical limitations of replicationUnderstanding the CAP theoremUnderstanding the limits of physicsDifferent types of replication245Synchronous versus asynchronous replicationConsidering performance issuesUnderstanding replication and data lossSingle-master versus multimaster replication67789ogical versus physical replicationWhen to use physical replication10When to use logical replication10Using sharding and data distributionUnderstanding the purpose of shardingDesigning a sharded systemQuerying different fields12Pros and cons of shardingChoosing between sharding and redundancy14Increasing and decreasing the size of a cluster15Combining sharding and replicationVarious sharding solutions18PostgreSQL-based sharding19Summary19www.allitebooks.com