/**
 * Replication monitoring of primaries and followers.
 * Columns with names starting with standby_ are important to monitor followers.
 * Monitoring itself is done on the current primary.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW monitoring_replication AS
SELECT r.pid
	, r.usesysid AS oid_user
	, r.usename AS user_name
	, r.application_name
	, r.client_addr
	, r.client_hostname
	, r.client_port
	, r.backend_start
	, r.backend_xmin
	, r.state
	, r.sent_lsn AS last_wal_sent
	, r.write_lsn AS standby_lastwal_written
	, r.replay_lsn AS standby_last_wal_replayed
	, r.flush_lsn AS standby_last_wal_flushed
	, r.write_lag AS standby_wal_write_lag
	, r.flush_lag AS standby_flush_lag
	, r.replay_lag  AS standby_wal_replay_lag
	, r.sync_priority AS standby_sync_priority
	, r.sync_state AS standby_sync_state
	, r.reply_time AS last_replay_message_from_standby
FROM pg_catalog.pg_stat_replication AS r
;

COMMENT ON VIEW monitoring_replication IS 'Replication monitoring of primaries and followers. Columns with names starting with standby_ are important to monitor followers. Monitoring itself is done on the current primary.';
COMMENT ON COLUMN monitoring_replication.pid IS 'The process id of the WAL sender process.';
COMMENT ON COLUMN monitoring_replication.oid_user IS 'The OID of the user logged into this WAL sender process.';
COMMENT ON COLUMN monitoring_replication.user_name IS 'The name of user the logged into this WAL sender process.';
COMMENT ON COLUMN monitoring_replication.application_name IS 'The name of the application that is connected to this WAL sender.';
COMMENT ON COLUMN monitoring_replication.client_addr IS 'The IP address of the client connected to this WAL sender. If this field is null, it indicates that the client is connected via a Unix socket on the server machine.';
COMMENT ON COLUMN monitoring_replication.client_hostname IS 'The host name of the connected client, as reported by a reverse DNS lookup of client_addr. This field will only be non-null for IP connections, and only when log_hostname is enabled.';
COMMENT ON COLUMN monitoring_replication.client_port IS 'The TCP port number that the client is using for communication with this WAL sender, or -1 if a Unix socket is used.';
COMMENT ON COLUMN monitoring_replication.backend_start IS 'The time when this process was started, i.e., when the client connected to this WAL sender.';
COMMENT ON COLUMN monitoring_replication.backend_xmin IS 'This standby''s xmin horizon reported by [hot_standby_feedback](https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK).';
COMMENT ON COLUMN monitoring_replication.state IS 'The current WAL sender state. Possible values are: startup: This WAL sender is starting up.  catchup: This WAL sender''s connected standby is catching up with the primary. streaming: This WAL sender is streaming changes after its connected standby server has caught up with the primary. backup: This WAL sender is sending a backup. stopping: This WAL sender is stopping.';
COMMENT ON COLUMN monitoring_replication.last_wal_sent IS 'The last write-ahead log location sent on this connection.';
COMMENT ON COLUMN monitoring_replication.standby_last_wal_replayed IS 'The last write-ahead log location replayed into the database on this standby server';
COMMENT ON COLUMN monitoring_replication.standby_wal_replay_lag IS 'The last write-ahead log location replayed into the database on this standby server.';
COMMENT ON COLUMN monitoring_replication.standby_sync_priority IS 'The priority of this standby server for being chosen as the synchronous standby in a priority-based synchronous replication. This has no effect in a quorum-based synchronous replication.';
COMMENT ON COLUMN monitoring_replication.standby_sync_state IS 'Synchronous state of this standby server. Possible values are: async: This standby server is asynchronous. potential: This standby server is now asynchronous, but can potentially become synchronous if one of current synchronous ones fails. sync: This standby server is synchronous. quorum: This standby server is considered as a candidate for quorum standbys.';
COMMENT ON COLUMN monitoring_replication.last_replay_message_from_standby IS 'The send time of last reply message received from this standby server';