PG-监控

Sampler

简介

Sampler 是一个用于 shell 命令执行、可视化和警报的工具。配置了一个简单的 YAML 文件。

安装配置

下载地址

https://github.com/sqshq/sampler


curl https://github.com/sqshq/sampler/releases/download/v1.1.0/sampler-1.1.0-linux-amd64 -o sampler

安装

wget https://github.com/sqshq/sampler/releases/download/v1.1.0/sampler-1.1.0-linux-amd64 -O sampler
chmod +x sampler

使用

# 1. 在yaml文件中定义shell命令
cat > config.yml <<-'EOF'
variables:
    PGPASSWORD: pwd
    postgres_connection: psql -h localhost -U postgres --no-align --tuples-only
runcharts:
  - title: Data write(Byte)
    position: [[0, 8], [20, 12]]
    rate-ms: 500
    legend:
        enabled: true
        details: false
    scale: 2
    items:
      - label: background writer
        color: 178
        sample: psql -At -U postgres -c "select 8 * (buffers_checkpoint + buffers_clean
            + buffers_backend)/1024 as total_writen from pg_stat_bgwriter;"
      - label: checkpoint write
        color: 162
        sample: psql -At -U postgres -c "select buffers_checkpoint * 8 / (checkpoints_timed
            + checkpoints_req) as checkpoint_write_avg from pg_stat_bgwriter"
  - title: PostgreSQL connections
    position: [[40, 8], [40, 12]]
    rate-ms: 500
    legend:
        enabled: true
        details: false
    scale: 2
    items:
      - label: active connections
        color: 178
        sample: psql -At -U postgres -c "select count(*) from pg_stat_activity where
            state = 'active' and pid <> pg_backend_pid();"
      - label: idle connections
        color: 162
        sample: psql -At -U postgres -c "select count(*) from pg_stat_activity where
            state = 'idle' and pid <> pg_backend_pid();"
      - label: idle in transaction connections
        color: 32
        sample: psql -At -U postgres -c "select count(*) from pg_stat_activity where
            state = 'idle in transaction' and pid <> pg_backend_pid();"
barcharts:
  - title: PostgreSQL Database Status
    position: [[0, 0], [40, 8]]
    rate-ms: 500
    scale: 0
    items:
      - label: tuple insert
        init: $postgres_connection
        sample: select tup_inserted from pg_stat_database where datname = current_database();
      - label: tuple delete
        init: $postgres_connection
        sample: select tup_deleted from pg_stat_database where datname = current_database();
      - label: tuple update
        init: $postgres_connection
        sample: select tup_updated from pg_stat_database where datname = current_database();
      - label: tuple fetch
        init: $postgres_connection
        sample: select tup_fetched from pg_stat_database where datname = current_database();
gauges:
  - title: PostgreSQL Database Age
    position: [[0, 32], [40, 8]]
    rate-ms: 500
    scale: 2
    color: 122
    percent-only: false
    cur:
        sample: psql -At -U postgres -c "select age(datfrozenxid) from pg_database
            where datname = current_database();"
    max:
        sample: psql -At -U postgres -c "select 210000" --此处为了演示
    min:
        sample: psql -At -U postgres -c "select 100"
sparklines:
  - title: CPU usage
    position: [[0, 20], [40, 12]]
    rate-ms: 200
    scale: 0
    sample: ps -A -o %cpu | awk '{s+=$1} END {print s}'
  - title: PostgreSQL cache hit ratio
    position: [[40, 20], [40, 12]]
    init: $postgres_connection
    sample: select round(sum(blks_hit)*100/sum(blks_hit+blks_read),2)::numeric from
        pg_stat_database where datname = current_database();
  - title: PostgreSQL transaction commit ratio
    position: [[40, 0], [40, 8]]
    init: $postgres_connection
    sample: select round(100*(xact_commit::numeric/(case when xact_commit > 0 then
        xact_commit else 1 end + xact_rollback)),2)::numeric as commit_ratio from
        pg_stat_database where datname = current_database();
textboxes:
  - title: Server status
    position: [[20, 8], [20, 12]]
    rate-ms: 500
    sample: top -bn 1 | head -n 5
asciiboxes:
  - title: PostgreSQL Version
    position: [[40, 32], [40, 8]]
    rate-ms: 500
    color: 43
    sample: psql -At -U postgres -c "select version()"
    border: false
    font: 2d
EOF

# 2. 运行
sampler -c config.yml

运行效果图，如下：

pgsentinel

简介

pgsentinel工具用来记录历史活跃会话, stat_statements快照等. 回看历史问题

安装配置

软件地址

https://github.com/pgsentinel/pgsentinel

软件安装

su - postgres
git clone https://github.com/pgsentinel/pgsentinel.git
cd pgsentinel/src
make
sudo make install
psql DB -c "CREATE EXTENSION pgsentinel;"

配置

配置 $PGDATA/postgres.conf文件

shared_preload_libraries = 'pg_stat_statements,pgsentinel'
# Icncrease the max size of the query strings Postgres records
track_activity_query_size = 2048
# Track statements generated by stored procedures as well
pg_stat_statements.track = all

pgCenter

简介

pgCenter 是一个用于观察和排除 Postgres 故障的命令行管理工具。

安装配置

下载地址

https://github.com/lesovsky/pgcenter
https://pgcenter.org/

wget https://github.com/lesovsky/pgcenter/releases/download/v0.9.1/pgcenter_0.9.1_linux_amd64.tar.gz

配置

安装plperlu插件

su - postgres
# 进入源码包中plperlu模块目录
cd /ups/soft/pgsql/postgresql-12.0/src/pl/plperl
make
make install

ls -lrth  $PGHOME/share/extension/plperl*.control

# 2. 数据库中安装插件
psql -U postgres -c 'CREATE LANGUAGE plperlu'

# 3. 检查确认
psql -U postgres -c "select * from pg_language"

使用

# 用法
pgCenter is a command line admin tool for PostgreSQL.

Usage:
  pgcenter [flags]
  pgcenter [command] [command-flags] [args]

Available commands:
  config	installs or uninstalls pgcenter stats schema to Postgres
  profile	wait events profiler
  record	record stats to file
  report	make report based on previously saved statistics
  top		top-like stats viewer

Flags:
  -?, --help		show this help and exit
      --version		show version information and exit

Use "pgcenter [command] --help" for more information about a command.

# 示例
pgcenter top
pgcenter top -h 1.2.3.4 -U postgres production_db

pgmetrics

pgmetrics

pgmetrics 是一款 postgresql 健康监控指标采集、报告开源软件。适用于PostgreSQL versions 9.3 to 13

结合 pgdash (收费)，可以实现被监控PG实例的可视化，指标值变更告警等功能。

安装

wget https://github.com/rapidloop/pgmetrics/releases/download/v1.11.0/pgmetrics_1.11.0_linux_amd64.tar.gz
tar -xf pgmetrics_1.11.0_linux_amd64.tar.gz
cd pgmetrics_1.11.0_linux_amd64
./pgmetrics --help

使用

用法

$ pgmetrics --help
pgmetrics collects PostgreSQL information and metrics.

Usage:
  pgmetrics [OPTION]... [DBNAME]

General options:
  -t, --timeout=SECS           individual query timeout in seconds (default: 5)
      --lock-timeout=MILLIS    lock timeout in milliseconds (default: 50)
  -i, --input=FILE             don't connect to db, instead read and display
                                   this previously saved JSON file
  -V, --version                output version information, then exit
  -?, --help[=options]         show this help, then exit
      --help=variables         list environment variables, then exit

Collection options:
  -S, --no-sizes               don't collect tablespace and relation sizes
  -c, --schema=REGEXP          collect only from schema(s) matching POSIX regexp
  -C, --exclude-schema=REGEXP  do NOT collect from schema(s) matching POSIX regexp
  -a, --table=REGEXP           collect only from table(s) matching POSIX regexp
  -A, --exclude-table=REGEXP   do NOT collect from table(s) matching POSIX regexp
      --omit=WHAT              do NOT collect the items specified as a comma-separated
                                   list of: "tables", "indexes", "sequences",
                                   "functions", "extensions", "triggers",
                                   "statements", "log", "citus"
      --sql-length=LIMIT       collect only first LIMIT characters of all SQL
                                   queries (default: 500)
      --statements-limit=LIMIT collect only utmost LIMIT number of row from
                                   pg_stat_statements (default: 100)
      --only-listed            collect info only from the databases listed as
                                   command-line args (use with Heroku)
      --all-dbs                collect info from all user databases
      --log-file               location of PostgreSQL log file
      --log-dir                read all the PostgreSQL log files in this directory
      --log-span=MINS          examine the last MINS minutes of logs (default: 5)
      --aws-rds-dbid           AWS RDS/Aurora database instance identifier

Output options:
  -f, --format=FORMAT          output format; "human", "json" or "csv" (default: "human")
  -l, --toolong=SECS           for human output, transactions running longer than
                                   this are considered too long (default: 60)
  -o, --output=FILE            write output to the specified file
      --no-pager               do not invoke the pager for tty output

Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
                                   (default: "/var/run/postgresql")
  -p, --port=PORT              database server port (default: 5432)
  -U, --username=USERNAME      database user name (default: "mdevan")
  -w, --no-password            never prompt for password
      --role=ROLE              do SET ROLE before collection

For more information, visit <https://pgmetrics.io>.


$ pgmetrics --help=variables
Environment variables:
Usage:
  NAME=VALUE [NAME=VALUE] pgmetrics ...

  PAGER              name of external pager program
  PGAPPNAME          the application_name connection parameter
  PGDATABASE         the dbname connection parameter
  PGHOST             the host connection parameter
  PGPORT             the port connection parameter
  PGUSER             the user connection parameter
  PGPASSWORD         connection password (not recommended)
  PGPASSFILE         path to the pgpass password file
  PGSSLMODE          "disable", "require", "verify-ca", "verify-full"
  PGSSLCERT          path to client SSL certificate
  PGSSLKEY           path to secret key for client SSL certificate
  PGSSLROOTCERT      path to SSL root CA
  PGCONNECT_TIMEOUT  connection timeout in seconds

Also, the following libpq-related environment variarables are not
required/used by pgmetrics and are IGNORED:

  PGHOSTADDR, PGSERVICE,     PGSERVICEFILE, PGREALM,  PGREQUIRESSL,
  PGSSLCRL,   PGREQUIREPEER, PGKRBSRVNAME,  PGGSSLIB, PGSYSCONFDIR,
  PGLOCALEDIR

支持输出格式

• text
• JSON
• CSV

简单使用

输出示例

./pgmetrics -h localhost -U postgres -t 3 --no-password --no-pager -f csv -o /tmp/pgmetrics_out.csv postgres 

pgcluu

简介

pgcluu ：PostgreSQL Cluster utilization

用于对 PostgreSQL 集群性能监控和审计工具

软件安装配置

下载地址

https://github.com/darold/pgcluu

wget https://github.com/darold/pgcluu/archive/refs/tags/v3.1.zip

软件安装

mkdir -p /usr/lib/cgi-bin
mkdir -p /etc/apache2/conf-enabled

su - postgres
unzip v3.1.zip
cd pgcluu-3.1
perl Makefile.PL
make 
sudo make install

默认安装配置路径：

The default values for these variables are:

     DESTDIR     => /usr/local
     INSTALLDIRS => site
     CONFDIR     => DESTDIR/etc
     PIDDIR      => /var/run/postgres
     STATDIR     => /var/lib/pgcluu/data
     REPORTDIR   => /var/lib/pgcluu/data
     RSCDIR      => DESTDIR/share/pgcluu
     CGIDIR      => /usr/lib/cgi-bin
     APACHECONF  => /etc/apache2/conf-available
     MANDIR      => DESTDIR/share/man
     DOCDIR      => DESTDIR/share/doc
     SYSTEMDIR   => DESTDIR/lib/systemd/system
     RETENTION   => 0

 If INSTALLDIRS is set to 'vendor':

     CONFDIR     => /etc
     DESTDIR     => /usr

使用

使用 pgcluu_collectd 收集信息

mkdir /tmp/stat_db1/
pgcluu_collectd -D -i 60 /tmp/stat_db1/

# pgcluu_collectd -D -i 60 /tmp/stat_postgres/ -h 127.0.0.1 -U postgres -d postgres  --disable-sar --exclude-time "08:00-09:00"

使用 pgcluu 生成报告

pgcluu_collectd -k
mkdir /tmp/report_postgres/
pgcluu -o /tmp/report_postgres/ /tmp/stat_postgres/

查看报告

http://localhost/cgi-bin/pgcluu.cgi