Setup for regular users - CHERTS/pgscv GitHub Wiki
In this tutorial we are going to configure system and install pgSCV from .tar.gz archive on Ubuntu 20.04.
- create dedicated monitoring roles in Postgres and Pgbouncer (optional)
- install pgSCV from
tar.gzon Ubuntu 22.04 Finally, pgSCV will be started as a service via systemd under the postgres user
Make sure PostgreSQL service should be installed and running. The ps command should show running Postgres processes:
ps f -u postgres
PID TTY STAT TIME COMMAND
460026 ? Ss 0:00 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf
460027 ? Ss 0:00 \_ postgres: 16/main: checkpointer
460028 ? Ss 0:00 \_ postgres: 16/main: background writer
460030 ? Ss 0:00 \_ postgres: 16/main: walwriter
460031 ? Ss 0:00 \_ postgres: 16/main: autovacuum launcher
460032 ? Ss 0:00 \_ postgres: 16/main: logical replication launcher
Connect to Postgres and create database user for pgSCV. This could be unprivileged user with special server roles which allow pgSCV read statistics and traverse directories and files.
sudo -u postgres psql
postgres=# CREATE ROLE pgscv WITH LOGIN PASSWORD 'SUPERSECRETPASSWORD';
postgres=# GRANT pg_read_server_files, pg_monitor TO pgscv;
postgres=# GRANT EXECUTE on FUNCTION pg_current_logfile() TO pgscv;
Created user should be allowed to connect to Postgres through UNIX sockets and localhost. Add the following lines to pg_hba.conf:
local all pgscv md5
host all pgscv 127.0.0.1/32 md5
Exact path to pg_hba.conf depends on Postgres version. Default path on Ubuntu is version-specific directory inside /etc/postgresql/.
After adding lines to pg_hba.conf, Postgres service should be reloaded. Connect to Postgres and execute pg_reload_conf() function.
sudo -u postgres psql -c 'select pg_reload_conf()'
Now, test the connection using created database role using psql utility. Specify the password in environment variable.
PGPASSWORD=SUPERSECRETPASSWORD psql -h 127.0.0.1 -U pgscv -d postgres -c "SELECT version()"
version
----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 16.3 (Ubuntu 16.3-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
In this example we connect to Postgres and ask its version.
In case of using Pgbouncer, additional configuration have to be made. Add pgscv user to stats_users list in pgbouncer.ini
stats_users = pgscv
After changing pgbouncer.ini, Pgbouncer service should be reloaded.
systemctl reload pgbouncer
Depending on used auth_type user and password should be specified in auth_file. By default, it is userlist.txt. For example for auth_type = md5, user and password could be added in the following way:
echo -n "SUPERSECRETPASSWORD" | md5sum | awk -v user=pgscv '{printf "\"%s\" \"md5%s\"\n", user, $1}' >> /etc/pgbouncer/userlist.txt
Now, make test connection to Pgbouncer. Specify the password in environment variable.
PGPASSWORD=SUPERSECRETPASSWORD psql -h 127.0.0.1 -p 6432 -U pgscv -d pgbouncer -c "SHOW version"
version
------------------
PgBouncer 1.16.1
In this example we connect to Pgbouncer built-in database and ask its version.
Download the tar.gz archive and install it. In this tutorial, v0.9.3 is used, check out the latest version in releases page.
curl -s -L https://github.com/cherts/pgscv/releases/download/v0.9.3/pgscv_0.9.3_linux_amd64.tar.gz -o - | tar xzf - -C /usr/sbin pgscv
Add the services section into /etc/pgscv.yaml and describe the services - specify its type and connection string. Take a look on conninfo string - this is standard libpq keyword/value formatted string.
Examples:
services:
"postgres:5432"
service_type: "postgres"
conninfo: "host=127.0.0.1 port=5432 dbname=postgres user=postgres"
Tag postgres:5432 is considered as unique ID and will be attached as the service_id label to each metric. Hence it is possible to collect metrics from hosts where pgSCV cannot be installed, e.g. Windows hosts or Amazon RDS and so on.
Create pgSCV default configuration in /etc/pgscv.yaml with the credentials created in previous steps.
cat << EOF > /etc/pgscv.yaml
services:
"postgres:5432":
service_type: "postgres"
conninfo: "postgres://pgscv:[email protected]:5432/postgres"
"pgbouncer:6432":
service_type: "pgbouncer"
conninfo: "postgres://pgscv:[email protected]:6432/pgbouncer"
EOF
Change config file owner and set permitions:
chown postgres:postgres /etc/pgscv.yaml
chmod 640 /etc/pgscv.yaml
Create a default environment file for systemd service /etc/default/pgscv:
cat << EOF > /etc/default/pgscv
ARGS='--config-file=/etc/pgscv.yaml'
EOF
Create a unit file for systemd service /etc/systemd/system/pgscv.service with the following content:
cat << EOF > /etc/systemd/system/pgscv.service
[Unit]
Description=pgSCV - PostgreSQL ecosystem metrics collector
Documentation=https://github.com/cherts/pgscv
Requires=network-online.target
After=network-online.target
[Service]
Type=simple
User=postgres
Group=postgres
EnvironmentFile=-/etc/default/pgscv
# Start the agent process
ExecStart=/usr/sbin/pgscv \$ARGS
# Kill all processes in the cgroup
KillMode=control-group
# Wait reasonable amount of time for agent up/down
TimeoutSec=5
# Restart agent if it crashes
Restart=on-failure
RestartSec=10
# if agent leaks during long period of time, let him to be the first person for eviction
OOMScoreAdjust=1000
[Install]
WantedBy=multi-user.target
EOF
Reload systemd and start pgSCV service.
systemctl daemon-reload
systemctl enable pgscv --now
Check pgSCV status using journalctl. There should be no errors.
journalctl -fu pgscv
Jun 28 11:59:27 srv-01.myorg.local systemd[1]: Started pgSCV - PostgreSQL ecosystem metrics collector.
Jun 28 11:59:27 srv-01.myorg.local pgscv[462383]: {"level":"info","service":"pgscv","time":"2024-06-28T11:59:27+03:00","message":"starting pgscv v0.8.4 0d450be0e02745e51a6dcae104b52754d8adcd77-HEAD"}
Jun 28 11:59:27 srv-01.myorg.local pgscv[462383]: {"level":"info","service":"pgscv","time":"2024-06-28T11:59:27+03:00","message":"reading file information /etc/pgscv.yaml"}
Jun 28 11:59:27 srv-01.myorg.local pgscv[462383]: {"level":"info","service":"pgscv","time":"2024-06-28T11:59:27+03:00","message":"read configuration from /etc/pgscv.yaml"}
Jun 28 11:59:27 srv-01.myorg.local pgscv[462383]: {"level":"info","service":"pgscv","time":"2024-06-28T11:59:27+03:00","message":"read configuration from environment"}
Jun 28 11:59:27 srv-01.myorg.local pgscv[462383]: {"level":"info","service":"pgscv","time":"2024-06-28T11:59:27+03:00","message":"no-track disabled, for details check the documentation about 'no_track_mode' option."}
Jun 28 11:59:27 srv-01.myorg.local pgscv[462383]: {"level":"info","service":"pgscv","time":"2024-06-28T11:59:27+03:00","message":"registered new service [system:0]"}
Jun 28 11:59:27 srv-01.myorg.local pgscv[462383]: {"level":"info","service":"pgscv","time":"2024-06-28T11:59:27+03:00","message":"registered new service [postgres:5432]"}
Jun 28 11:59:27 srv-01.myorg.local pgscv[462383]: {"level":"info","service":"pgscv","time":"2024-06-28T11:59:27+03:00","message":"registered new service [pgbouncer:6432]"}
Jun 28 11:59:27 srv-01.myorg.local pgscv[462383]: {"level":"info","service":"pgscv","time":"2024-06-28T11:59:27+03:00","message":"listen on http://127.0.0.1:9890"}
Connect to pgSCV using curl and ask metrics, there should be non-zero counts.
curl -s http://127.0.0.1:9890/metrics | grep -c ^postgres
502
curl -s http://127.0.0.1:9890/metrics | grep -c ^pgbouncer
124
curl -s http://127.0.0.1:9890/metrics | grep -c ^node
331
curl -s http://127.0.0.1:9890/metrics | grep -c ^go
33
In case of errors, see troubleshooting notes