Once streaming replication has been set up, it works flawlessly without too much administrator intervention. However, in some cases, it might make sense to halt replication and resume it at a later point. Why would anybody want to do that?

Consider the following use case: you are in charge of a master/slave setup, which is running a rubbish content management system (CMS) or some dubious forum software. Suppose you want to update your application from the awful CMS 1.0 to the dreadful CMS 2.0. Some changes will be executed in your database, which will instantly be replicated to the slave database. What if the upgrade process does something wrong? The error will be instantly replicated to both nodes due to streaming.

To avoid instant replication, we can halt replication and resume as needed. In the case of our CMS update, we could simply do the following things:

1. Halt replication.
2. Perform the app update on the master.
3. Check that our application still works. If yes, resume replication. If not, failover to the replica, which still has the old data.

With this mechanism, we can protect our data because we can fall back to the data as it was before the problem. Later in this chapter, we will learn how to promote a slave to become the new master server.

The main question now is this: how can we halt replication? Here's how it works. Execute the following line on the standby:

test=# SELECT pg_wal_replay_pause();

This line will halt replication. Note that the transaction log will still flow from the master to the slave—only the replay process is halted. Your data is still protected as it is persisted on the slave. In the case of a server crash, no data will be lost.

Keep in mind that the replay process has to be halted on the slave. Otherwise, an error will be thrown by PostgreSQL:

ERROR: recovery is not in progress
HINT: Recovery control functions can only be executed during recovery.

Once replication is to be resumed, the following line will be needed on the slave:

SELECT pg_wal_replay_resume();

PostgreSQL will start to replay WAL again.