postgresql
postgresql asks a PostgreSQL server a question through the server's own
client program, psql, as MMX's module for PostgreSQL does. The check is
healthy when the server answers and the conditions below hold. A streaming
replica with primary_conninfo set and no row in pg_stat_wal_receiver
fails without a limit. A standby that replays copied WAL
stays healthy without a streaming receiver. The check also measures how far
a replica is behind its primary (replication_lag_seconds), how many of
the server's connections are in use (connections_used_percent), how
many of its block reads its shared buffers served (cache_hit_percent),
how long its write-ahead log waits to be archived
(wal_archive_lag_seconds), and how far its transaction IDs are toward
wraparound (transaction_ids_used_before_wraparound_percent). The check
only asks: it sends one SELECT statement, twice on its first run, and
changes nothing.
A check without inputs, postgresql: {}, asks the server on the node
itself and needs no account, no password, and no setup. A check of a server
on another machine names it in database_server_host and logs in there.
For a MySQL or a MariaDB server, use mysql, which has
the inputs of postgresql, with login_user in place of
login_role.
Set up
The server on the node itself needs nothing: a node that has the server
has psql, and the check asks as the server's own administrator.
A check that logs in, of a server on another machine or of the server on the node, needs:
-
psqlon the node that checks, when the server is on another machine: the distribution's PostgreSQL client package, such aspostgresql-clienton Debian and Ubuntu andpostgresqlin the Red Hat family. -
An account inside the server that can log in and nothing more. In PostgreSQL an account is a role. Run on the database server:
sudo -u postgres createuser mmwatchdog_monitoring sudo -u postgres psql --command "SET password_encryption = 'scram-sha-256'" --command '\password mmwatchdog_monitoring'createusermakes a role that can log in and has no other right.\passwordasks for the role's password twice, so the password stays out of the shell's history and of the process list. PostgreSQL stores the password in the formscram-sha-256, which the line inpg_hba.confbelow needs. PostgreSQL 13 would otherwise keep the password asmd5. The check connects to the databasepostgres, which every role may connect to unless someone took that right away. -
For a replica checked through a login,
mmwatchdog_monitoringalso needspg_read_all_settingsto read whetherprimary_conninfois set:sudo -u postgres psql --command "GRANT pg_read_all_settings TO mmwatchdog_monitoring"MMWatchdog uses the setting to distinguish a lost streaming connection from a standby that only replays copied WAL. Membership in
pg_read_all_settingsalso lets the role read the server's other restricted settings. A primary check does not need membership inpg_read_all_settings. A check through the local socket already runs as the server's administrator. -
A line in the server's
pg_hba.confthat lets the role log in with a password from the node's address. PostgreSQL uses the first line that fits a connection, so put this line above the others:hostssl postgres mmwatchdog_monitoring 192.0.2.10/32 scram-sha-256192.0.2.10is the address of the node that checks.hostssltakes only encrypted connections, whichverified_tlsmakes. For a check withconnection_security: unencrypted, writehost; for the server on the node, write127.0.0.1/32. Debian and Ubuntu already have such a line for127.0.0.1and::1; the Red Hat family does not, and itspg_hba.confis in the data directory,/var/lib/pgsql/data. Then let the server read the file again, withsudo -u postgres psql --command 'SELECT pg_reload_conf()'. A server on another machine must also listen on an address that the node reaches:listen_addressesinpostgresql.confislocalhostunless someone changed it. -
A file on the node with the password on one line, which only root can read. MMWatchdog reads
login_password_fileas root. The second command below reads the password from the terminal: type the password, press Enter, and then Ctrl-D.sudo install -d -m 0700 /etc/mmwatchdog/secrets sudo install -m 0600 /dev/stdin /etc/mmwatchdog/secrets/postgresql-mmwatchdog-monitoringmmwatchdog validaterefuses a password file that its group or other accounts can read without explicit permission (credential files that other accounts can use).
For an exact connections_used_percent, the role also needs the right to
read the statistics of all sessions:
sudo -u postgres psql --command 'GRANT pg_read_all_stats TO mmwatchdog_monitoring'.
Membership in pg_read_all_stats also shows the role the statement that
every session runs, which can hold the application's data. Without
membership in pg_read_all_stats, the reading is a little high
(see connections in use).
Inputs
database_server_host(string; optional): The name or the address of the PostgreSQL server. Without it the check uses the server's local socket on this node, as the operating system account postgres, which the server takes for its own administrator without a password.dangerously_skip_server_certificate_check(boolean; optional): With true, encrypt the connection without checking the server's certificate or identity. By default, verify the server's certificate.database_server_port(integer; optional): Required with database_server_host: the port that the server listens on, such as 5432 for PostgreSQL or 3306 for MySQL.connection_security(verified_tlsorunencrypted; optional): How the connection to database_server_host is protected. Required with database_server_host, and of no use without it. verified_tls encrypts the connection and verifies the server's certificate for database_server_host unless dangerously_skip_server_certificate_check is true; unencrypted never encrypts.database_server_certificate_authority_file(string; optional): With verified_tls: the absolute path of a file of PEM certificates that the check trusts in place of the system's. The client program reads the file as the account mmwatchdog, so every account must be able to read it.login_role(string; optional): The account inside the PostgreSQL server that the check logs in as. Required with database_server_host.login_password_file(string; optional): The absolute path of a file that holds the password of that account, on one line. MMWatchdog reads it for each run. Required with database_server_host.
Measurements
replication_lag_seconds(seconds): How far a replica is behind its primary. A primary is 0 seconds behind, so one check with a limit fits the primary and its replicas.connections_used_percent(percent): The open connections as a share of the connections that the server allows.cache_hit_percent(percent): The share of the blocks that the server read since the run before that its shared buffers held, 0 to 100. A time in which the server read no block has no such measurement.wal_archive_lag_seconds(seconds): How long the oldest finished segment of the write-ahead log has waited to be archived, 0 when none waits. Measured by a check without login_role, on a server that archives: a primary with archive_mode off has no such measurement, and a replica is 0 unless its archive_mode is always.transaction_ids_used_before_wraparound_percent(percent): How far the oldest transaction ID of the server's databases is toward the limit at which PostgreSQL refuses every write until a vacuum finishes, 0 to 100. A healthy server reads about 5 to 10; higher means less room remains.
The module fails a check by itself, and it also reports these measurements. A check sets a limit on a measurement with fail_when_measurement_above or fail_when_measurement_below, and fails when a reading passes its limit. A check with the module always states an alert_severity. The configuration files reference explains limits in checks that measure a number.
When the check fails, and when it cannot check
- The check fails when the server does not answer, when it refuses the
login, and when the answer does not arrive within the check group's
time_limit_per_run. The check also fails whenpsqldoes not accept the server's certificate or the protection of the connection (see how the connection is protected), and when a reading passes a limit. A streaming replica withprimary_conninfoset and no row inpg_stat_wal_receiverfails even without a limit. A standby that replays copied WAL, with noprimary_conninfo, stays healthy without a streaming receiver. When the client reports an error, the message holds the first line of whatpsqlsays. - MMWatchdog cannot check, and the run is unknown, when:
- a replica has no streaming receiver and the login role cannot read
primary_conninfo; grantpg_read_all_settingsas described in Set up, so MMWatchdog can tell whether streaming is required; - the node has no
psql, or MMWatchdog cannot run it as the account that the check needs; - MMWatchdog cannot read the password file;
- with
verified_tls, the node has no file of certificate authorities, or the accountmmwatchdogcannot readdatabase_server_certificate_authority_file; psqlgives an answer that MMWatchdog does not understand;- a limit names
replication_lag_seconds, and the server is a replica that has not applied a change of its primary yet, so MMWatchdog has no number; - a limit names
cache_hit_percent, and the server read no block since the run before (the cache hit ratio); - a limit names
wal_archive_lag_seconds, and at least one of the following conditions holds: the check logs in withlogin_role, or the server is a primary witharchive_modeoff (how long the write-ahead log waits).
- a replica has no streaming receiver and the login role cannot read
A failed run starts or continues the check's failing time. Unknown runs open a separate cannot-check incident after the check's waiting times, as the design's failing time describes. The measurements take a limit in the same way as every measurement does (checks that measure a number).
Examples
The server on this node, and how far behind it is
# /etc/mmwatchdog/check_groups/postgresql.yml
check_groups:
postgresql:
check_every: 1m
time_limit_per_run: 10s
alert_after_failing_for: 3m
recovered_after_healthy_for: 1m
checks:
postgresql_answers:
postgresql: {} # the server on this node
alert_severity: sos
postgresql_replica_is_less_than_60_seconds_behind:
postgresql: {}
fail_when_measurement_above:
replication_lag_seconds: 60
alert_severity: warn
Every minute, MMWatchdog on the node runs psql as the operating system account
postgres and asks the server on the node through its local socket. The
first check is healthy whenever the server answers, with a message such as
the PostgreSQL server answers; it is a primary. When the server does not
answer for 3 minutes, MMWatchdog sends an sos alert.
The second check also fails when the server is a replica that is more than
60 seconds behind its primary, and sends a warn alert after 3 minutes. A
primary is 0 seconds behind, so the check with the replication-lag limit
fits every server of a primary and its replicas, and stays right when a
replica is made the primary.
The database cache, and the archive of the write-ahead log
# /etc/mmwatchdog/check_groups/postgresql_capacity.yml
check_groups:
postgresql_every_hour:
check_every: 1h
time_limit_per_run: 10s
alert_after_failing_for: 0s
recovered_after_healthy_for: 0s
checks:
hot_data_fits_in_the_shared_buffers:
postgresql: {}
fail_when_measurement_below:
cache_hit_percent: 99 # of the blocks read in the hour since the run before
alert_severity: warn
transaction_ids_are_less_than_half_way_to_wraparound:
postgresql: {}
fail_when_measurement_above:
transaction_ids_used_before_wraparound_percent: 50
alert_severity: warn
postgresql_archive:
check_every: 1m
time_limit_per_run: 10s
alert_after_failing_for: 0s
recovered_after_healthy_for: 1m
checks:
write_ahead_log_is_archived:
postgresql: {}
fail_when_measurement_above:
wal_archive_lag_seconds: 300 # how long the oldest finished segment has waited
alert_severity: warn
Every hour, MMWatchdog asks the server on the node how many blocks it read
since the run before, and how many blocks the server's shared buffers held. When
fewer than 99 percent of the hour's reads were in the buffers, the data
that the application uses most no longer fits in the server's memory, and
the cache check sends a warn alert at once: the reading already covers
the hour. The transaction-ID check warns above 50 percent, leaving about
a billion transactions for vacuum to finish. Every minute, the archive
check sends a warn alert when a
finished segment of the write-ahead log has waited more than 5 minutes to
be archived, such as by pgbackrest archive-push. A server without writes
finishes no segment, so the server stays healthy on a quiet day.
A server on another machine
# /etc/mmwatchdog/check_groups/database_machine.yml
check_groups:
database_machine:
check_every: 1m
time_limit_per_run: 10s
alert_after_failing_for: 3m
recovered_after_healthy_for: 1m
checks:
postgresql_on_the_database_machine_answers:
postgresql:
database_server_port: 5432
database_server_host: db-1.internal.example.com
connection_security: verified_tls
login_role: mmwatchdog_monitoring
login_password_file: /etc/mmwatchdog/secrets/postgresql-mmwatchdog-monitoring
fail_when_measurement_above:
connections_used_percent: 80
alert_severity: sos
Every minute, MMWatchdog on the node reads the password file and runs psql as the
unprivileged account mmwatchdog. psql opens an encrypted connection to the
server on db-1.internal.example.com at the standard port 5432, verifies
that the server's certificate is valid for that name, and logs in as the
role mmwatchdog_monitoring. The check fails when the server does not answer, when
the certificate is not valid, and when more than 80 percent of the server's
connections are in use. When the check has failed for 3 minutes because
the server does not answer, the certificate is invalid, or connection use
is above 80 percent, MMWatchdog sends an sos alert.
The server on this node through a login
As Debian and Ubuntu install PostgreSQL, the local
socket logs an account of the operating system in as the role of the same
name (peer), so a login with a password works over a network address
only. A check of the server on the
node that logs in therefore states the address 127.0.0.1, and
connection_security: unencrypted, because that connection stays on the
node. The server's pg_hba.conf needs a line for 127.0.0.1, as
set up says:
# /etc/mmwatchdog/check_groups/postgresql_login.yml
check_groups:
postgresql_login:
check_every: 1m
time_limit_per_run: 10s
alert_after_failing_for: 3m
recovered_after_healthy_for: 1m
checks:
application_role_can_log_in:
postgresql:
database_server_port: 5432
database_server_host: 127.0.0.1
connection_security: unencrypted
login_role: mmwatchdog_monitoring
login_password_file: /etc/mmwatchdog/secrets/postgresql-mmwatchdog-monitoring
alert_severity: warn
MMWatchdog on the node runs psql as the account mmwatchdog. psql
connects to the server on the node at 127.0.0.1, port 5432, and logs in as
mmwatchdog_monitoring. The check fails when the role cannot log in, for example
after the password of mmwatchdog_monitoring changed on the server but
/etc/mmwatchdog/secrets/postgresql-mmwatchdog-monitoring still holds the
old password.
A certificate of your own certificate authority
A server whose certificate a certificate authority of your own signed
needs that authority's certificate on the node. psql reads the file as
the account mmwatchdog, so keep it outside /etc/mmwatchdog/secrets, such as in
/etc/mmwatchdog:
# /etc/mmwatchdog/check_groups/internal_database.yml
check_groups:
internal_database:
check_every: 1m
time_limit_per_run: 10s
alert_after_failing_for: 3m
recovered_after_healthy_for: 1m
checks:
internal_database_answers:
postgresql:
database_server_port: 5432
database_server_host: db-1.internal.example.com
connection_security: verified_tls
database_server_certificate_authority_file: /etc/mmwatchdog/database-authority.pem
login_role: mmwatchdog_monitoring
login_password_file: /etc/mmwatchdog/secrets/postgresql-mmwatchdog-monitoring
alert_severity: sos
MMWatchdog on the node first asks the operating system whether the account mmwatchdog can
read /etc/mmwatchdog/database-authority.pem. psql then trusts only the
certificates in /etc/mmwatchdog/database-authority.pem for the server's
certificate.
How it works
Through the local socket, without a login
A check without database_server_host uses the local socket of the server
on the node, as the server's own administrator. PostgreSQL takes the operating system account postgres
for its role postgres without a password, so MMWatchdog runs psql as
the operating system account postgres.
- Through the local socket, nothing is encrypted, and the check states
no
connection_security(PostgreSQL, MySQL, and MariaDB checks). database_server_portalone names a second server on the node: the local socket of PostgreSQL holds the port in its name, such as/var/run/postgresql/.s.PGSQL.5432.
With a login
A check with login_role and login_password_file logs in with a password,
and MMWatchdog runs psql as the unprivileged account mmwatchdog, which needs no rights
on the node. MMWatchdog uses the account mmwatchdog for a server on
another machine, with database_server_host, and for the server on the
node without database_server_host.
- MMWatchdog reads the password file for each run, so a changed password needs
no reload. MMWatchdog gives the password to
psqlin the environment (PGPASSWORD), never as an argument: the arguments of a program show in the process list. psqlnever asks for a password (--no-password). A server that requires a password that the check does not have fails the check.psqlreads no file of its own settings (--no-psqlrc) and connects to the databasepostgres.- Without
database_server_port,psqluses the standard port, 5432.
How the connection is protected
A check with database_server_host states connection_security. The
rules below are the same for postgresql and mysql.
connection_securityhas no default. Without explicit settings,psql,mariadb, andmysqleach protect the connection in a different way. So MMWatchdog always tells the client program how to protect the connection, and the check shows the choice.database_server_hosttakes a name, or an IPv4 or IPv6 address such as2001:db8::5, without a port.- With certificate checking on,
verified_tlstrusts the node's own certificate authorities, unless the check statesdatabase_server_certificate_authority_file. The node's file is the first of these that exists:/etc/ssl/certs/ca-certificates.crt(Debian, Ubuntu, and Arch Linux),/etc/ssl/ca-bundle.pem(openSUSE), and/etc/pki/ca-trust/extracted/pem/tls-ca-bundle.pem(Rocky Linux, AlmaLinux, and the rest of the Red Hat family).
The following table says what psql gets.
MMWatchdog tells psql the value in its environment:
connection_security |
What psql gets |
|---|---|
verified_tls |
PGSSLMODE=verify-full, and PGSSLROOTCERT with the file of certificate authorities |
verified_tls with dangerously_skip_server_certificate_check: true |
PGSSLMODE=require, with a root-certificate path that cannot exist so libpq loads no default certificate file |
unencrypted |
PGSSLMODE=disable |
- With
database_server_host,psqlalso getsPGGSSENCMODE=disable. For an account with a Kerberos ticket,psqlwould otherwise take the encryption of Kerberos in the place of TLS, and verify no certificate. - With certificate verification enabled, MMWatchdog names a file of certificate authorities
to
psql: the configureddatabase_server_certificate_authority_file, or the node's own file.psqlknows the node's file by itself only from PostgreSQL 16. psqlrefuses a server that does not keep to the value before it logs in: withverify-fulla certificate that is not valid for the name, withrequirea server without encryption. The errors frompsqlare in errors and what they mean.
How far a replica is behind
replication_lag_seconds is how far a replica is behind its primary, in
seconds.
- A primary is 0 seconds behind: it is behind no server. One check with a limit on the lag then fits the primary and its replicas, and stays right when a replica is made the primary. The message says whether the server is a primary or a replica.
- A replica that has applied every change that it received, and that is connected to its primary, is 0 seconds behind, however long ago the primary made its last change.
- Otherwise the reading is the age of the last change that the replica
has applied. So a replica that lost its primary falls further behind
with every second, and a limit catches it. The message then adds
and it is not connected to its primary now. A replica withprimary_conninfoset and no row inpg_stat_wal_receiverfails even without a lag limit. A standby that only replays copied WAL (primary_conninfoempty) does not fail for having no receiver. - A new replica that has not applied a change yet has no reading, never
the 0 of a primary. A check with a limit on the lag is then unknown, with
the PostgreSQL replica has not applied a change of its primary yet.
Connections in use
connections_used_percent is the open connections as a percent of the
server's max_connections, 0 to 100. With the administrator or a member
of pg_read_all_stats, MMWatchdog counts only client connections because
max_connections limits client connections. With a role that cannot read
all sessions' statistics, the count also includes the server's own workers. The extra worker processes
make the reading a little high.
The cache hit ratio
cache_hit_percent is the percentage of block requests served from the
server's shared buffers between consecutive runs, 0 to 100. MMWatchdog
reads PostgreSQL's counters blks_hit and blks_read of pg_stat_database,
summed over the server's databases, because the buffers are one for the
whole server.
cache_hit_percentcovers the time since the previous run, as the hit ratio ofmemcacheddoes, socheck_everysets the window: a check every hour measures the last hour. The first run after MMWatchdog starts samples the counters twice, 0.2 seconds apart.- A block from outside the buffers is not always read from the disk: the page cache of the operating system may hold it. A ratio below about 99 percent on a busy server still means that the data that the application uses most no longer fits in the buffers.
- PostgreSQL adds a session's reads to the counters when its transaction
ends, at most once a second. The reads of a statement that runs for an
hour count in the run after the statement ends. A longer
check_everysmooths the effect of delayed reads. - After the server starts, its buffers are empty, so the ratio is low until the application has read its data again.
- The SQL statement that MMWatchdog sends reads from the buffers too. On a server without other work, each run's connection adds a few hundred hits and no reads, so an idle server reads close to 100 percent.
- A time in which the server read no block has no ratio, never one of 0
or 100. A check with a limit on
cache_hit_percentis then unknown, withthe PostgreSQL server read no blocks in the last 0.200s, so cache_hit_percent cannot be measured. - Counters that fell, after
pg_stat_reset()or a crash of the server, start a new sample, as on a first run.
How long the write-ahead log waits to be archived
wal_archive_lag_seconds is how long the oldest finished segment of the
write-ahead log has waited to be archived, in seconds, and 0 when none
waits. PostgreSQL marks each finished segment that waits with a file in
pg_wal/archive_status, which pg_ls_archive_statusdir() lists with the
time it was made. A segment waits until archive_command, such as
pgbackrest archive-push, has succeeded for it.
- A server without writes finishes no segment, so nothing waits, and
the reading is 0. The time since the last archived segment would grow on
such a server and warn on every quiet day.
wal_archive_lag_secondsgrows only while a segment waits. - After three failed attempts, PostgreSQL waits up to a minute before it tries again, so the reading can stay above 0 for up to a minute after the archive works again.
- A replica is 0 seconds behind unless its
archive_modeisalways, because its primary archives. The check with awal_archive_lag_secondslimit stays right when a replica is made the primary. A former primary that became a replica can still hold segments that waited to be archived at that moment, and no archiver runs on a replica to send them, so they do not count until it is the primary again. Withalways, the replica archives too, and its own wait counts. - A primary with
archive_modeoff has no reading: it archives nothing, so nothing waits, and a 0 would hide that. A check with a limit onwal_archive_lag_secondsis unknown, witharchive_mode is off, so the PostgreSQL server archives no write-ahead log and wal_archive_lag_seconds cannot be measured. - A check with
login_roledoes not measurewal_archive_lag_seconds. PostgreSQL lists the waiting segments only to its administrators and to members ofpg_monitor, and it refuses the whole statement of a role without that right before it runs any of it. The archive belongs to the server on its own node, which a check without a login asks as the administrator.
How far the transaction IDs are from wraparound
PostgreSQL gives each writing transaction a 32-bit number, called a transaction ID. Rows keep transaction IDs so PostgreSQL can tell which changes a reader may see. The counter eventually wraps around and reuses numbers. Vacuum must freeze old rows, marking those rows as visible to all transactions, before the reused numbers could make old rows appear new.
transaction_ids_used_before_wraparound_percent measures the oldest
unfrozen transaction ID across every database, including template databases:
100 * max(age(datfrozenxid)) / 2147483648, from pg_database.
The reading is a percentage from 0 to 100. Near 100, PostgreSQL refuses
writes until a vacuum finishes, while reads keep working.
PostgreSQL starts its own emergency vacuum near 9 percent:
autovacuum_freeze_max_age defaults to 200 million transactions.
A healthy server reads about 5 to 10. A reading that keeps rising means
vacuum is falling behind. A limit of 50 leaves about a billion
transactions. On a busy server, that can mean weeks, giving a vacuum
that may take hours time to finish. The time left depends on how quickly
the server uses transaction IDs.
Every check measures the age, with or without login_role, because every
role may read pg_database. A replica reads the number copied from its
primary. No limit applies unless the check states one. An age that cannot
be parsed has no reading.
PostgreSQL's wraparound guide explains freezing and the refusal of writes. Its vacuum settings explain the emergency vacuum threshold.
Messages
- A healthy run says what the server is:
the PostgreSQL server answers; it is a primary, orthe PostgreSQL server answers; it is a replica, 2.5 seconds behind its primary. With a limit, the message names each reading that has a limit before the message about the server's role and replication lag. - A server that does not answer, and a server that refuses the login,
fail the check with the first line of what
psqlsays, such asthe PostgreSQL server did not answer the check: psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory.psqlputs the reason at the end of its line, so of a line of more than 300 bytes the message keeps the first 100 and the last 200 bytes, with...between them.
Errors and what they mean
The validation errors name the setting, such as
check_groups.database_machine.checks.postgresql_on_the_database_machine_answers.postgresql.connection_security.
In the list below, validation errors start at the module's input name
and omit the check group and check path:
connection_security: required with database_server_host: write connection_security: verified_tls to encrypt the connection, or connection_security: unencrypted to send without encryption. State how the connection to the server is protected. Useverified_tlswherever the server has a certificate that a certificate authority signed.database_server_host: a check of a server on another machine logs in with a password: state login_role and login_password_file.connection_security: has no use without database_server_host: the check uses the server's local socket on this node, which has no encryption and needs none, and the same fordatabase_server_certificate_authority_file. Remove the setting, or adddatabase_server_host.database_server_certificate_authority_file: has a use only with connection_security: verified_tls, which verifies the server's certificate.database_server_host: expected the name or the address of the database server, without a port. Write the port indatabase_server_port.database_server_port: expected a port number from 1 to 65535.login_role: required with login_password_file: the account that the check logs in as, andlogin_role: expected one word of at most 256 bytes.login_password_file: must be absolute, and the same fordatabase_server_certificate_authority_file. Write the whole path.login_password_file: open /etc/mmwatchdog/secrets/postgresql-mmwatchdog-monitoring: no such file or directory, frommmwatchdog validate. A run is unknown withopen /etc/mmwatchdog/secrets/postgresql-mmwatchdog-monitoring: no such file or directory, withoutlogin_password_file:. Create/etc/mmwatchdog/secrets/postgresql-mmwatchdog-monitoring. A file that does not hold exactly one line gives/etc/mmwatchdog/secrets/postgresql-mmwatchdog-monitoring: expected one line with the password: UTF-8 text of at most 1,024 bytes, without a tab or another control character.connection_security: verified_tls needs the certificate authorities that this node trusts, and this node has no file of them: MMWatchdog looked for .... Install the distribution's package of certificate authorities,ca-certificates, or statedatabase_server_certificate_authority_file.database_server_certificate_authority_file: the account mmwatchdog, which runs the client program of the database server, cannot read /etc/mmwatchdog/secrets/database-authority.pem. A certificate is no secret: give every account the permission to read the file and to pass through the directories above it. Move the file to a directory that every account can pass through, such as/etc/mmwatchdog, and let every account read it. A file without certificates givescontains no PEM certificates.SSL error: certificate verify failed.psqldoes not know the certificate authority of the server's certificate. Statedatabase_server_certificate_authority_file, or check that the server shows the right certificate.server certificate for "db-1.internal.example.com" does not match host name "db-1". The certificate is for another name. Write indatabase_server_hosta name that the certificate holds.MMWatchdog cannot run psql, the client program of PostgreSQL: executable file not found in $PATH. The check needs that program on this node. Installpsql.MMWatchdog cannot run psql as the account mmwatchdog: .... The program exists, and the operating system did not startpsqlasmmwatchdog, for example because the node has no accountmmwatchdog. The run is unknown.the PostgreSQL server did not answer the check: psql ended with exit code 2. The client program failed and wrote nothing. The check fails.the PostgreSQL server did not answer within the time limit of the run. The server, or the network to it, is too slow for the check group'stime_limit_per_run.psql gave an answer that MMWatchdog does not understand: .... The run is unknown. The answer follows in the message.a check with login_role does not measure wal_archive_lag_seconds: .... The run is unknown because a limit names the measurement. Check the server on its own node withoutlogin_role.archive_mode is off, so the PostgreSQL server archives no write-ahead log and wal_archive_lag_seconds cannot be measured. The run is unknown because a limit names the measurement. Turn onarchive_mode, or remove the limit from a server that does not archive.
Tested on
- Go tests in
server_checks_test.go, with a stand-in forpsql:TestPostgreSQLCheckcovers the arguments and the environment ofpsqlfor the local socket and for a login, a primary that is 0 seconds behind, a replica that is level, behind, without its primary, and without a change yet, the transaction-ID age as a percentage with and without a login, a limit on that percentage, an unreadable age, the other limits, a refusing server, an answer that MMWatchdog does not understand, a password file that cannot be read, and a node withoutpsql.TestADatabaseCheckProtectsItsConnectionAsItStatescovers whatpsqlgets for eachconnection_security, the node's file of certificate authorities and the check's own, a node without such a file, a file without certificates, and a file that the account of the client cannot read, in a run and in validation on the node. The tests record as which account MMWatchdog runspsql:postgreswithout a login,mmwatchdogwith one.TestPostgreSQLCheckMeasuresCacheHitsAndTheArchiveWaitcovers the cache hit ratio of a first run without reads, of 99 and of 50 percent since the run before against a limit, a segment that has waited 412.5 seconds, a primary witharchive_modeoff, a replica, and a check that logs in. The statements of the check with and without a login ran against a real PostgreSQL 15 server, as its administrator and as a role withoutpg_monitor. - Real servers: the
servicessuite (service_scenarios.py) asks PostgreSQL as each distribution installs it, through the local socket with no setup and through a login with a password, stopped and started. With a certificate that a certificate authority of the test signed, it asks with eachconnection_security: a certificate that the check trusts, one of an authority that the guest does not know, one for another name, encryption without verification, a server with its encryption turned off, a server that takes only encrypted connections, and a file of certificate authorities that the accountmmwatchdogcannot read, for the check and formmwatchdog validate. A second server follows the first as its replica: level, behind while its replay is paused, without its primary, and made a primary. The latest run passed on every target distribution, withpsql13 to 18 (validation record). - Not verified on a real node: a database server named by an address of IP version 6.
Why it works this way
-
Permission refusals belong to validation and reload. Startup warns once per file. A running check continues after a permissions mode change so the permission rule cannot stop monitoring. The credential permission rule owns the settings and the reasons.
-
A disconnected streaming replica fails without a limit. The replica cannot receive new changes; a copied-WAL standby has no streaming connection to require.
-
Certificate checking is the default.
dangerously_skip_server_certificate_check: truemakes the choice to trust any server explicit, while TLS still prevents people who can only observe network traffic from reading the connection's contents. -
A network server's port is required. With
database_server_host, statedatabase_server_portso the connection address is visible even when the server uses its usual port. -
The check asks through
psql, as MMX's module for PostgreSQL does, and has MMX's names for the connection. MMWatchdog needs no library for PostgreSQL, and a node that has the server has the program. -
Without a login, the check asks as the server's own administrator through the local socket. PostgreSQL accepts the operating system account
postgresas the database rolepostgreswithout a password, so the most common check, of the server on the node itself, needs no account, no password, and no setup. The check sends only a statement that reads. -
With a login,
psqlruns as the unprivileged accountmmwatchdog. The check then needs no rights on the node. -
A check with
database_server_hostmust stateconnection_security.psqldecides by itself when nothing tells it, and PostgreSQL as Debian installs it makes a certificate for itself that no certificate authority signed. The design explains the reasons for explicit connection protection and for accounting for an unsigned certificate for every check of a common service that encrypts, in protocols and common server software. -
unencryptedis allowed with a password. A login to the server on the node through127.0.0.1stays on the node. Any host is accepted, including across a private tunnel; the setting makes the choice visible. -
MMWatchdog names a file of certificate authorities when certificate checking is enabled.
psqlknows the node's file by itself only from PostgreSQL 16. -
MMWatchdog checks itself whether
mmwatchdogcan readdatabase_server_certificate_authority_file. Whenmmwatchdogcannot read the certificate-authority file,psqlgives no reason for the failure. -
A primary is 0 seconds behind, so that one check with a limit fits every server of a replication.
-
A replica without a number has no reading, never 0. A 0 would say that the replica is level when MMWatchdog does not know it.
-
The cache hit ratio covers the time since the run before, not the time since the server started. A server that has run for weeks would otherwise show a change in the data that the application reads most often only weeks later.
-
The archive's wait is the age of the oldest segment that waits, not the time since the last archived segment, which grows on a server without writes. A team's first servers often have no traffic for weeks.
-
A primary with
archive_modeoff has no archive reading, never 0. A 0 would say that nothing waits when the server archives nothing. -
The oldest database decides the server's wraparound risk, including templates, because one database with old unfrozen rows can stop writes across the server. The denominator is 2^31 (2147483648), half the 32-bit range, because PostgreSQL compares transaction IDs within half that range to tell older transactions from newer transactions.
-
The message keeps the end of a long line of
psql. The reason is at the end of the line, behind the name and the address of the server.
This page is docs/reference/modules/postgresql.md in the MMWatchdog repository.