This section describes how to check the status of the database server.
Check the multiplexed database status by executing the mc_ctl command in status mode.
Additionally, errors can be detected by monitoring the Mirroring Controller messages. If the status or messages are monitored periodically, you can react quickly following an automatic switch failure.
Checking the status of the multiplexing database
When the mc_ctl command is executed, the details of the multiplexing configuration, information about whether switch is possible following the error, and location and details of the error that caused the switch or disconnection are displayed.
After starting database multiplexing mode, execute the mc_ctl command in status mode to check the multiplexing status.
An example of the status displayed when the mc_ctl command is executed is shown below.
Example)
$ mc_ctl status -M /mcdir/inst1 mirroring status ---------------- switchable server_id host_role host host_status db_proc_status disk_status -------------------------------------------------------------------------------------------------------- server1 primary 192.0.2.100 normal normal normal server2 standby 192.0.2.110 normal normal normal
Checking the status of connection to the Mirroring Controller arbitration process
When the arbitration server is used for automatic degradation, the status of the connection to the Mirroring Controller arbitration process can be checked by specifying the --arbiter option. If the output status is "online", it indicates that an arbitration request can be made from the database server to the arbitration server. When the arbitration server is used for automatic degradation, regularly execute the command in status mode with the --arbiter option specified and check that the output status is "online".
Example) The mc_ctl command is executed with the --arbiter option specified, and the status is output.
$ mc_ctl status --arbiter -M /mcdir/inst1 arbiter_id host status --------------------------------------- arbiter 192.0.3.120 online
Checking the status of data synchronization
Additionally, by referencing the pg_stat_replication statistics view on the primary server, the data synchronization status can be confirmed. However, when creating the monitoring program, note that the content of pg_stat_replication may be changed in the future.
The following example shows that the locations of the transaction log after it is sent and received (sent_lsn, replay_lsn) match, and that they are fully synchronized.
Example)
postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 10651 usesysid | 10 usename | fsep application_name | standby client_addr | 192.0.2.210 client_hostname | client_port | 55098 backend_start | 2022-03-23 11:17:49.628793+09 backend_xmin | state | streaming sent_lsn | 0/3000060 write_lsn | 0/3000060 flush_lsn | 0/3000060 replay_lsn | 0/3000060 write_lag | flush_lag | replay_lag | sync_priority | 1 sync_state | sync reply_time | 2022-03-23 11:23:27.703366+09
See
Refer to "mc_ctl" in Reference for information on the command.
Refer to "Notes on Application Compatibility" in the Application Development Guide for information on retaining application compatibility.
Refer to "The Statistics Collector" in "Server Administration" in the PostgreSQL Documentation for details on pg_stat_replication.
