The sixth chapter of PostgreSQL Replication Monitoring your settings (1)

In the first few chapters of this book, you have learned about various types of replication and how to quota the various kinds of scenarios. Now is the time to make your setup more reliable by adding monitoring.

In this chapter, you will learn to monitor what is going on and to implement a reasonable monitoring vehicle such as deterioration. You will learn:

• Check your XLOG archive

• Check the pg_stat_replication system view

• Check OS-level replication-related processes

At the end of this chapter you should be able to properly monitor any type of replication settings.

6.1 Check your archive

If you plan to use instant recovery (PITR, point-in-time-recovery) or if you want to use Xlog archiving to help with your streaming settings, things can go wrong, such as:

• Transfer Xlog may fail

• Cleaning up the archive may fail

6.1.1 Check Archive_command

A failed archive_command may be one of the biggest spoilers in your setup.

Archive_command's idea is to teleport Xlog to some archive and store the data there. But what happens if for some reason these xlog cannot be transmitted?

The answer is simple: Master must keep these xlog files to ensure that no xlog files are lost. There must always be a sequence of uninterrupted xlog files-if a file in the sequence file is missing, your slave will not be restored. For example, if your network fails, master accumulates the files and retains them. Logically, you can't do this forever, so at some point you'll be faced with a problem with your master server running out of disk space.

This is dangerous because you are exhausting your disk space and there is no way to keep writing to the database. While it is still possible to read, most of the writes will certainly fail and will seriously interfere with your system. PostgreSQL will not fail, and your instance will be intact after a disk has been filled, but, as previously stated, your service will be interrupted.

To prevent this from happening, it is recommended to monitor your Pg_xlog directory and check:

• Unusually high number of xlog files

• Free disk space for managed pg_xlog partitions

The central question here is: What numbers are checked out to be a reasonable number? More than Checkpoint_segments * 2 + wal_keep_segments should not be used in the standard quota system of PostgreSQL for so many xlog files. If the number of xlog files starts to grow massively, you will encounter some strange problems.

Make sure the Archive_command is working properly.

If you perform these checks correctly, nothing bad happens in this area-if you can't check these parameters, you're not far from the danger.

6.1.2 Monitoring things Log Archive

Disk space exhaustion will not only occur on master. The same thing can happen to your archive. Therefore, it is recommended that you also monitor disk space.

In addition to the disk space to monitor anyway, there is one thing you should be aware of your radar. You must come up with a decent strategy to handle the underlying backup. Keep in mind that you are only allowed to delete xlog that are earlier than the xlog of the oldest base backup you want to save. This little thing will damage your disk space monitoring. Why is it? Because you have to keep a certain amount of data, it's good to know that you're running out of disk space, but can't you do something about it? It is strongly recommended that you ensure that your archive has sufficient spare capacity. This is important in case your database system needs to write a large number of transaction logs.

The sixth chapter of PostgreSQL Replication Monitoring your settings (1)