Chapter 12: Replication and Upgrades

Replication isn't magic, though it can be pretty cool! It's even cooler when it works, and that's what this chapter is all about.

Replication requires understanding, effort, and patience. There are a significant number of points to get right. Our emphasis here is on providing simple approaches to get you started, as well as some clear best practices on operational robustness.

PostgreSQL has included some form of native or in-core replication since version 8.2, though that support has steadily improved over time. External projects and tools have always been a significant part of the PostgreSQL landscape, with most of them being written and supported by very skilled PostgreSQL technical developers. Some people with a negative viewpoint have observed that this weakens PostgreSQL or emphasizes shortcomings. Our view is that PostgreSQL has been lucky enough to be supported by a huge range of replication tools together, offering a...

Replication concepts

In this recipe, we do not solve any specific replication problem—or, rather, we try to prevent the generic problem of getting confused when discussing replication. We do that by clarifying in advance the various concepts related to replication.

Indeed, replication technology can be confusing. You might be forgiven for thinking that people have a reason to keep it that way. Our observation is that there are many techniques, each with its own advocates, and their strengths and weaknesses are often hotly debated.

There are some simple, underlying concepts that can help you understand the various options available. The terms used here are designed to avoid favoring any particular technique, and we've used standard industry terms whenever available.

Topics

Database replication is the term we use to describe technology that's used to maintain a copy of a set of data on a remote system.

There are usually two...

Replication best practices

Some general best practices for running replication systems are described in this recipe.

Getting ready

Reading a list of best practices should be the very first thing you do when designing your database architecture. So, the best way to get ready for it is to avoid doing anything and start straight away with the next section, How to do it...

How to do it…

Here are some best practices for replication:

• Use the latest release of PostgreSQL. Replication features are changing fast, with each new release improving on the previous in major ways based on our real-world experience. The idea that earlier releases are somehow more stable, and thus more easily usable, is definitely not the case for replication.
• Use similar hardware and OSs on all systems. Replication allows nodes to switch roles. If we switch over or fail over to different hardware, we may get performance issues, and it will be hard to maintain...

Setting up streaming replication

Physical replication is a technique used by many database management systems. The primary database node records change in a transaction log (WAL), and then the log data is sent from the primary to the standby, where the log is replayed.

In PostgreSQL, PSR transfers WAL data directly from the primary to the standby, giving us integrated security and shorter replication delay.

There are two main ways to set up streaming replication: with or without an additional archive. We present how to set it up without an external archive, as this is simpler and generally more efficient. However, there is one downside, suggesting that the simpler approach may not be appropriate for larger databases, which is explained later in this recipe.

Getting ready

If you haven't read the Replication concepts and Replication best practices recipes at the start of this chapter, go and read them now. Note that streaming replication...

Setting up streaming replication security

Streaming replication is at least as secure as normal user connections to PostgreSQL.

Replication uses standard LibPQ connections, so we have all the normal mechanisms for authentication and SSL support, and all the firewall rules are similar.

Replication must be specifically enabled on both the sender and standby sides. Cascading replication does not require any additional security.

When performing a base backup, the pg_basebackup, pg_receivewal, and pg_recvlogical utilities will use the same type of LibPQ connections as a running, streaming standby. You can use other forms of base backup, such as rsync, though you'll need to set up the security configuration manually.

Note

Standbys are identical copies of the primary, so all users exist on all nodes with identical passwords. All of the data is identical (eventually), and all the permissions are the same too. If you wish to control...

Hot Standby and read scalability

Hot Standby is the name for the PostgreSQL feature that allows us to connect to a standby node and execute read-only queries. Most importantly, Hot Standby allows us to run queries while the standby is being continuously updated through either file-based or streaming replication.

Hot Standby allows you to offload large or long-running queries or parts of your read-only workload to standby nodes. Should you need to switch over or fail over to a standby node, your queries will keep executing during the promotion process to avoid any interruption of service.

You can add additional Hot Standby nodes to scale the read-only workload. There is no hard limit on the number of standby nodes, as long as you ensure that enough server resources are available and parameters are set correctly—10, 20, or more nodes are easily possible.

There are two main capabilities provided by a Hot Standby node. The first is that the standby node...

Managing streaming replication

Replication is great, provided that it works. Replication works well if it's understood, and it works even better if it's tested.

Getting ready

You need to have a plan for the objectives for each individual server in the cluster. Which standby server will be the failover target?

How to do it…

Switchover is a controlled switch from the primary to the standby. If performed correctly, there will be no data loss. To be safe, simply shut down the primary node cleanly, using either the smart or fast shutdown modes. Do not use the immediate mode shutdown because you will almost certainly lose data that way.

Failover is a forced switch from the primary node to a standby because of the loss of the primary. So, in that case, there is no action to perform on the primary; we presume it is not there anymore.

Next, we need to promote one of the standby nodes to be the new primary. A standby...

Using repmgr

As we stated previously, replication is great, provided that it works; it works well if it's understood, and it works even better if it's tested. This is a great reason to use the repmgr utility.

repmgr is an open source tool that was designed specifically for PostgreSQL replication. To get additional information about repmgr, visit http://www.repmgr.org/.

The repmgr utility provides a command-line interface (CLI) and a management process (daemon) that's used to monitor and manage PostgreSQL servers involved in replication. The repmgr utility easily supports more than two nodes with automatic failover detection.

Getting ready

Install the repmgr utility from binary packages on each PostgreSQL node.

Set up replication security and network access between nodes according to the Setting up streaming replication security recipe.

How to do it…

The repmgr ...

Using replication slots

Replication slots allow you to define your replication architecture explicitly. They also allow you to track details of nodes even when they are disconnected. Replication slots work with both PSR and LSR, though they operate slightly differently.

Replication slots ensure that data required by a downstream node persists until the node receives it. They are crash-safe, so if a connection is lost, the slot still continues to exist. By tracking data on downstream nodes, we avoid these problems:

• When a standby disconnects, the feedback data provided by hot_standby_feedback is lost. When the standby reconnects, it may be sent cleanup records that result in query conflicts. Replication slots remember the standby's xmin value even when disconnected, ensuring that cleanup conflicts can be avoided.
• When a standby disconnects, knowledge of which WAL files were required is lost. When the standby reconnects, we may have discarded...

Monitoring replication

Monitoring the status and progress of your replication is essential. We'll start by looking at the server status and then query the progress of replication.

Getting ready

You'll need to start by checking the state of your server(s).

Check whether a server is up using pg_isready or another program that uses the PQping() application programming interface (API) call. You'll get one of the following responses:

• PQPING_OK (return code 0): The server is running and appears to be accepting connections.
• PQPING_REJECT (return code 1): The server is running but is in a state that disallows connections (start up, shutdown, or crash recovery) or a standby that is not enabled with Hot Standby.
• PQPING_NO_RESPONSE (return code 2): The server could not be contacted. This might indicate that the server is not running, there is something wrong with the given connection parameters (for example, wrong port number), or there is a network...

Performance and sync rep

Sync rep allows us to offer a confirmation to the user that a transaction has been committed and fully replicated on at least one standby server. To do that, we must wait for the transaction changes to be sent to at least one standby, and then have that feedback returned to the primary.

The additional time taken for the message's round trip will add elapsed time for the commit of write transactions, which increases in proportion to the distance between servers. PostgreSQL offers a choice to the user as to what balance they would like between durability and response time.

Getting ready

The user application must be connected to a primary to issue transactions that write data. The default level of durability is defined by the synchronous_commit parameter. That parameter is user-settable, so it can be set for different applications, sessions, or even individual transactions. For now, ensure that the user application is using this level:

...

Delaying, pausing, and synchronizing replication

Some advanced features and thoughts for replication are covered here.

Getting ready

If you have multiple standby servers, you may want to have one or more servers operating in a delayed apply state—for example, 1 hour behind the primary. This can be useful to help recover from user errors such as mistaken transactions or dropped tables without having to perform a PITR.

How to do it…

Normally, a standby will apply changes as soon as possible. When you set the recovery_min_apply_delay parameter in recovery.conf, the application of commit records will be delayed by the specified duration. Note that only commit records are delayed, so you may receive Hot Standby cancelations when using this feature. You can prevent that in the usual way by setting hot_standby_feedback to on, but use this with caution since it can cause...

Logical replication

Logical replication allows us to stream logical data changes between two nodes. By logical, we mean streaming changes to data without referring to specific physical attributes such as a block number or row ID.

These are the main benefits of logical replication:

• Performance is roughly two times better than that of the best trigger-based mechanisms.
• Selective replication is supported, so we don't need to replicate the entire database.
• Replication can occur between different major releases, which can allow a zero-downtime upgrade.

PostgreSQL provides a feature called logical decoding, which can be used to stream a set of changes out of a primary server. This allows a primary to become a sending node in logical replication. The receiving node uses a logical replication process to receive and apply those changes, thereby implementing replication between those two nodes.

So far, we have referred to physical replication...

BDR

BDR (Postgres-BDR) is a project aiming to provide multi-master replication with PostgreSQL. There is a range of possible architectures. The first use case we support is all-nodes-to-all-nodes. Postgres-BDR will eventually support a range of complex architectures, which is discussed later.

With Postgres-BDR, the nodes in a cluster can be distributed physically, allowing worldwide access to data as well as DR. Each Postgres-BDR primary node runs individual transactions; there is no globally distributed transaction manager. Postgres-BDR includes replication of data changes such as DML, as well as DDL changes. New tables are added automatically to replication, ensuring that managing BDR is a low-maintenance overhead for applications.

Postgres-BDR also provides global sequences, if you wish to have a sequence that works across a distributed system where each node can generate new IDs. The usual local sequences are not replicated.

One key advantage of Postgres-BDR...

Archiving transaction log data

PSR can send transaction log data to a remote node, even if the node is not a full PostgreSQL server, so that it can be archived. This can be useful for various purposes, such as the following:

• Restoring a hot physical backup
• Investigating the contents of previous transactions

Getting ready

Normally, backups should be taken regularly on a production system; if you have configured Barman already, as described in the Hot physical backup with Barman recipe of Chapter 11, Backup and Recovery, then you are already archiving transaction logs because they are needed to restore a physical backup, so no further action is needed, and you can skip to the How to do it... section of the current recipe.

PostgreSQL includes two client tools to stream transaction data from the server to the client. The tools are designed using a pull model; that is, you run the tools on the node you wish the data to be saved on:

• pg_receivewal...

Upgrading minor releases

Minor release upgrades are released regularly by all software developers, and PostgreSQL has had its share of corrections. When a minor release occurs, we bump the last number, usually by one. So, the first release of a major release such as 14 is 14.0. The first set of bug fixes is 14.1, then 14.2, and so on.

The PostgreSQL community releases new bug fixes quarterly. If you want bug fixes more frequently than that, you will need to subscribe to a PostgreSQL support company. This recipe is about moving from a minor release to a minor release.

Getting ready

First, get hold of the new release, by downloading either the source or fresh binaries.

How to do it…

In most cases, PostgreSQL aims for minor releases to be simple upgrades. We put in great efforts to keep the on-disk format the same for both data/index files and transaction log (WAL) files, but this isn't always the case; some files...

Major upgrades in-place

PostgreSQL provides an additional supplied program, called pg_upgrade, which allows you to migrate between major releases, such as from 9.2 to 9.6, or from 9.6 to 11; alternatively, you can upgrade straight to the latest server version. These upgrades are performed in-place, meaning that we upgrade our database without moving to a new system. That does sound good, but pg_upgrade has a few things that you may wish to consider as potential negatives, which are outlined here:

• The database server must be shut down while the upgrade takes place.
• Your system must be large enough to hold two copies of the database server: old and new copies. If it's not, then you have to use the link option of pg_upgrade, or use the Major upgrades online recipe, coming next in this chapter. If you use the link option on pg_upgrade, then there is no pg_downgrade utility. The only option in that case is...

Major upgrades online

Upgrading between major releases is hard, and should be deferred until you have some good reasons and sufficient time to get it right.

You can use replication tools to minimize the downtime required for an upgrade, so we refer to this recipe as an online upgrade.

How to do it…

The following general steps should be followed, allowing at least a month for the complete process to ensure that everything is tested and everybody understands the implications:

1. Set up a new release of the software on a new test system.
2. Take a standalone backup from the main system and copy it to the test system.
3. Test the applications extensively against the new release on the test system.

When everything works and performs correctly, then proceed to the next step.

4. Set up a connection pooler to the main database (it may be there already).
5. Set up logical replication for all tables from the old to new database servers...

Why subscribe?

• Spend less time learning and more time coding with practical eBooks and Videos from over 4,000 industry professionals
• Improve your learning with Skill Plans built especially for you
• Get a free eBook or video every month
• Fully searchable for easy access to vital information
• Copy and paste, print, and bookmark content

Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at packt.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [e-mail] for more details.

At www.packt.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.