Skip to content

Setup for regular users

Jump to bottom
Mikhail Grigorev edited this page Nov 26, 2024 · 25 revisions

Installing pgSCV from tar.gz on Ubuntu 22.04.

TLDR

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.gz on Ubuntu 22.04 Finally, pgSCV will be started as a service via systemd under the postgres user

Content:

Create database 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.

Create pgbouncer user

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.

Install pgSCV

Download the tar.gz archive and install it. In this tutorial, v0.9.6 is used, check out the latest version in releases page.

curl -s -L https://github.com/cherts/pgscv/releases/download/v0.9.6/pgscv_0.9.6_linux_$(uname -m).tar.gz -o - | tar xzf - -C /usr/sbin pgscv

Base configuring 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:SUPERSECRETPASSWORD@127.0.0.1:5432/postgres"
  "pgbouncer:6432": 
    service_type: "pgbouncer"
    conninfo: "postgres://pgscv:SUPERSECRETPASSWORD@127.0.0.1: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

Clone this wiki locally