PostgreSQL Replication
eBook: $29.99
Formats: PDF, PacktLib, ePub and Mobi formats
save 15%!
Print + free eBook + free PacktLib access to the book: $79.98    Print cover: $49.99
save 37%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Table of Contents
Sample Chapters
  • Explains the new replication features introduced in PostgreSQL 9
  • Contains easy to understand explanations and lots of screenshots that simplify an advanced topic like replication
  • Teaches PostgreSQL administrators how to maintain consistency between redundant resources and to improve reliability, fault-tolerance, and accessibility

Book Details

Language : English
Paperback : 250 pages [ 235mm x 191mm ]
Release Date : August 2013
ISBN : 1849516723
ISBN 13 : 9781849516723
Author(s) : Zoltan Böszörmenyi, Hans-Jürgen Schönig
Topics and Technologies : All Books, Big Data and Business Intelligence, Data, Open Source

Table of Contents

Chapter 1: Understanding Replication Concepts
Chapter 2: Understanding the PostgreSQL Transaction Log
Chapter 3: Understanding Point-In-Time-Recovery
Chapter 4: Setting up Asynchronous Replication
Chapter 5: Setting up Synchronous Replication
Chapter 6: Monitoring Your Setup
Chapter 7: Understanding Linux High Availability
Chapter 8: Working with pgbouncer
Chapter 9: Working with pgpool
Chapter 10: Configuring Slony
Chapter 11: Using Skytools
Chapter 12: Working with Postgres-XC
Chapter 13: Scaling with PL/Proxy
  • Chapter 1: Understanding Replication Concepts
    • The CAP theory and physical limitations
      • Understanding the CAP theory
      • Why the speed of light matters
      • Long distance transmission
      • Why latency matters
    • Different types of replication
      • Synchronous versus asynchronous replication
        • Understanding replication and data loss
        • Considering the performance issues
      • Single-master versus multi-master replication
      • Logical versus physical replication
        • When to use physical replication
        • When to use logical replication
    • Using sharding and data distribution
      • Understanding the purpose of sharding
        • An example of designing a sharded system
        • An example of querying different fields
      • Pros and cons of sharding
      • Choosing between sharding and redundancy
      • Increasing and decreasing the size of a cluster
      • Combining sharding and replication
      • Various sharding solutions
        • PostgreSQL-based sharding
        • External frameworks/middleware
    • Summary
    • Chapter 2: Understanding the PostgreSQL Transaction Log
      • How PostgreSQL writes data
        • The PostgreSQL disk layout
          • Looking into the data directory
          • PG_VERSION – PostgreSQL version number
          • base – the actual data directory
          • global – the global data
          • pg_clog – the commit log
          • pg_hba.conf – host-based network configuration
          • pg_ident.conf – ident authentication
          • pg_multixact – multi-transaction status data
          • pg_notify – LISTEN/NOTIFY data
          • pg_serial – information about committed serializable transactions
          • pg_snapshot – exported snapshots
          • pg_stat_tmp – temporary statistics data
          • pg_subtrans – subtransaction data
          • pg_tblspc – symbolic links to tablespaces
          • pg_twophase – information about prepared statements
          • pg_XLOG – the PostgreSQL transaction log (WAL)
          • postgresql.conf – the central PostgreSQL configuration file
        • Writing one row of data
          • A simple INSERT statement
        • Read consistency
          • The purpose of the shared buffer
          • Mixed reads and writes
      • The XLOG and replication
      • Understanding consistency and data loss
        • All the way to the disk
          • From memory to memory
          • From memory to the disk
          • One word about batteries
          • Beyond fsync()
        • PostgreSQL consistency levels
      • Tuning checkpoints and the XLOG
        • Understanding the checkpoints
        • Configuring checkpoints
          • About segments and timeouts
          • To write or not to write?
        • Tweaking WAL buffers
      • The internal structure of the XLOG
        • Understanding the XLOG records
          • Making the XLOG deterministic
          • Making the XLOG reliable
        • LSNs and shared buffer interaction
          • Debugging the XLOG and putting it all together
      • Summary
      • Chapter 3: Understanding Point-In-Time-Recovery
        • Understanding the purpose of PITR
          • Moving to the bigger picture
        • Archiving the transaction log
        • Taking base backups
          • Using pg_basebackup
            • Modifying pg_hba.conf
            • Signaling the master server
            • pg_basebackup – basic features
          • Making use of traditional methods to create base backups
          • Tablespace issues
          • Keeping an eye on network bandwidth
        • Replaying the transaction log
          • Performing a basic recovery
          • More sophisticated positioning in the XLOG
          • Cleaning up the XLOG on the way
          • Switching the XLOG files
        • Summary
        • Chapter 4: Setting up Asynchronous Replication
          • Setting up streaming replication
            • Tweaking the config files on the master
            • Handling pg_basebackup and recovery.conf
            • Making the slave readable
            • The underlying protocol
          • Configuring a cascaded replication
          • Turning slaves to masters
          • Mixing streaming and file-based recovery
            • The master configuration
            • The slave configuration
            • Error scenarios
              • Network connection between the master and slave is dead
              • Rebooting the slave
              • Rebooting the master
              • Corrupted XLOG in the archive
          • Making the streaming-only replication more robust
          • Efficient cleanup and the end of recovery
            • Gaining control over the restart points
            • Tweaking the end of your recovery
          • Conflict management
          • Dealing with the timelines
          • Summary
          • Chapter 5: Setting up Synchronous Replication
            • Setting up synchronous replication
              • Understanding the downside of synchronous replication
              • Understanding the application_name parameter
              • Making synchronous replication work
              • Checking replication
              • Understanding performance issues
              • Setting synchronous_commit to on
                • Setting synchronous_commit to remote_write
                • Setting synchronous_commit to off
                • Setting synchronous_commit to local
              • Changing durability settings on the fly
            • Understanding practical implications and performance
            • Redundancy and stopping replication
            • Summary
            • Chapter 6: Monitoring Your Setup
              • Checking your archive
                • Checking the archive_command
                • Monitoring the transaction log archive
              • Checking pg_stat_replication
                • Relevant fields in pg_stat_replication
              • Checking for operating system processes
              • Dealing with monitoring tools
                • Installing check_postgres
                • Deciding on a monitoring strategy
              • Summary
              • Chapter 7: Understanding Linux High Availability
                • Understanding the purpose of high availability
                • Measuring availability
                • History of high-availability software
                  • OpenAIS and Corosync
                  • Linux-HA (Heartbeat) and Pacemaker
                • Terminology and concepts
                • High availability is all about redundancy
                • PostgreSQL and high availability
                  • High availability with quorum
                  • High availability with STONITH
                • Summary
                • Chapter 8: Working with pgbouncer
                  • Understanding fundamental pgbouncer concepts
                  • Installing pgbouncer
                  • Configuring your first pgbouncer setup
                    • Writing a simple config file and starting pgbouncer up
                      • Dispatching requests
                      • More basic settings
                      • Authentication
                    • Connecting to pgbouncer
                      • Java issues
                    • Pool modes
                    • Cleanup issues
                  • Improving performance
                    • A simple benchmark
                  • Maintaining pgbouncer
                    • Configuring the admin interface
                    • Using the management database
                    • Extracting runtime information
                    • Suspending and resuming operations
                  • Summary
                  • Chapter 9: Working with pgpool
                    • Installing pgpool
                      • Installing pgpool-regclass and insert_lock
                    • Understanding pgpool features
                    • Understanding the pgpool architecture
                    • Setting up replication and load balancing
                      • Password authentication
                      • Firing up pgpool and testing the setup
                      • Attaching hosts
                    • Checking replication
                    • Running pgpool with streaming replication
                      • Optimizing pgpool configuration for master/slave mode
                    • Dealing with failovers and high availability
                      • Using PostgreSQL streaming and Linux HA
                      • pgpool mechanisms for high availability and failover
                    • Summary
                    • Chapter 10: Configuring Slony
                      • Installing Slony
                      • Understanding how Slony works
                        • Dealing with logical replication
                        • The slon daemon
                      • Replicating your first database
                      • Deploying DDLs
                      • Adding tables to replication and managing problems
                      • Performing failovers
                        • Planned failovers
                        • Unplanned failovers
                      • Summary
                      • Chapter 11: Using Skytools
                        • Installing skytools
                        • Dissecting skytools
                        • Managing pgq-queues
                          • Running pgq
                            • Creating queues and adding data
                            • Adding consumers
                            • Configuring the ticker
                            • Consuming messages
                            • Dropping queues
                            • Using pgq for large projects
                        • Using londiste to replicate data
                          • Replicating our first table
                        • One word about walmgr
                        • Summary
                        • Chapter 12: Working with Postgres-XC
                          • Understanding the Postgres-XC architecture
                            • Data nodes
                            • GTM – Global Transaction Manager
                            • Coordinators
                            • GTM Proxy
                          • Installing Postgres-XC
                          • Configuring a simple cluster
                            • Creating the GTM
                          • Optimizing for performance
                            • Dispatching the tables
                            • Optimizing the joins
                            • Optimizing for warehousing
                            • Creating a GTM Proxy
                          • Creating the tables and issuing the queries
                          • Adding nodes
                          • Handling failovers and dropping nodes
                            • Handling node failovers
                            • Replacing the nodes
                            • Running a GTM standby
                          • Summary
                          • Chapter 13: Scaling with PL/Proxy
                            • Understanding the basic concepts
                              • Dealing with the bigger picture
                              • Partitioning the data
                            • Setting up PL/Proxy
                              • A basic example
                              • Partitioned reads and writes
                            • Extending and handling clusters in a clever way
                              • Adding and moving partitions
                              • Increasing the availability
                              • Managing the foreign keys
                              • Upgrading the PL/Proxy nodes
                            • Summary

                            Zoltan Böszörmenyi

                            Zoltan Böszörmenyi has over 15 years experience in the software development and IT industry. He started working with PostgreSQL in 1995 and has since been working exclusively developing and implementing solutions using it. Among many other things, he has extended ECPG, the embedded SQL-in-C flavor in PostgreSQL. He has also developed unique solutions for POS hardware. He also occasionally does training on PostgreSQL. He has held senior-level positions but now serves as the CTO of Cybertec Schönig & Schönig GmbH.

                            Hans-Jürgen Schönig

                            Hans-Jürgen Schönig has 15 years of experience with PostgreSQL. He is the CEO of a PostgreSQL consulting and support company called "Cybertec Schönig & Schönig GmbH" (, which has successfully served countless customers around the globe. Before founding Cybertec Schönig & Schönig GmbH in the year 2000, he worked as database developer at a private research company focusing on the Austrian labor market where he was primarily focusing on data mining and forecast models. He has written several books dealing with PostgreSQL already.

                            Submit Errata

                            Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.


                            - 1 submitted: last submission 19 May 2014

                            Errata type: Code | Page no: 166

                            # group tables into sets
                            create set (id=1, origin=1, comment='Our tables');

                            add sequences=true
                            set add table (set id=1, origin=1, id=1,
                              fully qualified name = 'public.t_test',
                            comment='sample table');

                            Sample chapters

                            You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

                            Frequently bought together

                            PostgreSQL Replication +    Node.js Blueprints =
                            50% Off
                            the second eBook
                            Price for both: £27.35

                            Buy both these recommended eBooks together and get 50% off the cheapest eBook.

                            What you will learn from this book

                            • Recover a PostgreSQL database to a certain point in time
                            • Set up asynchronous and synchronous streaming replication
                            • Handle transaction log files efficiently
                            • Learn about Linux High Availability
                            • Get to grips with Slony replication
                            • Get PL/Proxy to scale infinitely
                            • Understand pgpool and pgbouncer to improve speed and reliability
                            • Perform fast on-the-fly backups
                            • Increase reliability and throughput
                            • Improve data security and geographically distribute data

                            In Detail

                            PostgreSQL offers a comprehensive set of replication related features, which can be used to make your database servers more robust and way more scalable. Unleashing the power of PostgreSQL provides the user with countless opportunities and a competitive advantage over other database systems. To make things more powerful, PostgreSQL can be used in conjunction with a handful of sophisticated tools serving various different needs such as queuing, logical replication, or simplified transaction log handling.

                            "PostgreSQL Replication" is a practical, hands-on guide to PostgreSQL replication. It will provide you with the theoretical background as well as simple examples showing you how to make replication work on your system. A broad toolchain will be presented along with mature PostgreSQL-core technology.

                            "PostgreSQL Replication" starts with an introduction to replication concepts as well as the physical limitations of different replication solutions. You will be guided through various techniques such as Point-In-Time-Recovery, transaction-log-based replication and you will be introduced to a set of replication-related tools. In the final chapter you will learn to scale PostgreSQL to many different servers using PL/Proxy.

                            You will learn how to reset PostgreSQL to a certain point in time and figure out how to replicate data in many ways. You will deal with both synchronous as well as asynchronous replication. In addition to that, the book covers important topics, such as Slony, and upgrades with virtually no downtime. We will also cover important performance-related topics to make sure your database setups will provide you with high speed AND high availability.

                            "PostgreSQL Replication" contains all the information you need to design and operate replicated setups. You will learn everything you need to know for your daily work and a lot more.


                            This book has a chapter dedicated to each aspect of replication. The new features of PostgreSQL 9 are introduced and there are lots of practical examples and screenshots.

                            Who this book is for

                            "PostgreSQL Replication" is ideal for PostgreSQL administrators who want to set up and understand replication. If you want to make your databases more robust, more secure, faster, and more scalable, you will find all the information you need in this single book focusing exclusively on PostgreSQL replication. It is assumed that you already have some basic experience with PostgreSQL.

                            Code Download and Errata
                            Packt Anytime, Anywhere
                            Register Books
                            Print Upgrades
                            eBook Downloads
                            Video Support
                            Contact Us
                            Awards Voting Nominations Previous Winners
                            Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
                            Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software