Improving and monitoring the replication

Until now, a basic replication setup has been discussed and we can work on improvements, monitoring, as well as slight extensions to the existing concepts.

Keeping an eye on streaming

The most important task of every DBA is to make sure that your setup keeps working—no matter what happens. To achieve this goal it is highly important to keep an eye on replication and to monitor things carefully.

The easiest way to monitor replication is to inspect pg_stat_replication:

test=# \d pg_stat_replication
  View "pg_catalog.pg_stat_replication"
      Column      |           Type           | 
------------------+----------------------------
 pid              | integer                  | 
 usesysid         | oid                      | 
 usename          | name                     | 
 application_name | text                     | 
 client_addr      | inet                     | 
 client_hostname  | text                     | 
 client_port      | integer          ...