postgresai/pgwatch2 repository overview

shared_preload_libraries = pg_stat_statements

create extension if not exists pg_stat_statements;

PG_VERSION=16; sudo apt install -y \
  postgresql-$PG_VERSION-pg-stat-kcache \
  postgresql-$PG_VERSION-pg-wait-sampling

shared_preload_libraries = pg_stat_statements,pg_stat_kcache,pg_wait_sampling

create extension if not exists pg_stat_statements;
create extension if not exists pg_stat_kcache; -- N/A on managed services such as RDS
create extension if not exists pg_wait_sampling; -- N/A on managed services such as RDS (available on CloudSQL!)

begin;

create role pgai_monitoring with login password '{{secure_password}}';

create schema pgai_monitoring;

create view pgai_monitoring.pg_statistic as
 select pg_statistic.stawidth,
    pg_statistic.stanullfrac,
    pg_statistic.starelid,
    pg_statistic.staattnum
   from pg_catalog.pg_statistic;

grant connect on database {{PROD_DB_NAME}} to pgai_monitoring;
grant pg_monitor to pgai_monitoring;
grant usage on schema public to pgai_monitoring;
grant usage on schema pgai_monitoring to pgai_monitoring;
grant select on pgai_monitoring.pg_statistic to pgai_monitoring;

alter user pgai_monitoring set search_path = pgai_monitoring, pg_catalog, public;

commit;

-- Environment-dependent steps
grant execute on function pg_ls_dir(text) to pgai_monitoring; -- skip it if you're on a managed Postgres such as RDS
grant execute on function pg_wait_sampling_reset_profile() to pgai_monitoring; -- if pg_wait_sampling is installed

sudo mkdir -p /etc/pgwatch2/config
sudo curl https://gitlab.com/postgres-ai/pgwatch2/-/raw/master/config/instances.yaml \
  --output /etc/pgwatch2/config/instances.yaml

sudo nano /etc/pgwatch2/config/instances.yaml

sudo docker run -d --name pgwatch2-postgresai \
  -p 3000:3000 -p 8081:8081 \
  -v /etc/pgwatch2/config:/etc/pgwatch2/config:ro \
  -v pgwatch2:/etc/pgwatch2/persistent-config \
  -v pgwatch2_postgres:/var/lib/postgresql \
  -v pgwatch2_grafana:/var/lib/grafana \
  -e PW2_GRAFANANOANONYMOUS=true \
  -e PW2_GRAFANAUSER="admin" \
  -e PW2_GRAFANAPASSWORD="MY_SECRET_PASS" \
  -e PW2_DATASTORE="postgres" \
  -e PW2_PG_SCHEMA_TYPE="timescale" \
  -e PW2_PG_RETENTION_DAYS=14 \
  -e PW2_TIMESCALE_CHUNK_HOURS=1 \
  -e PW2_TIMESCALE_COMPRESS_HOURS=1 \
  --restart=unless-stopped \
  --shm-size=2g \
  postgresai/pgwatch2:latest

sudo docker exec pgwatch2-postgresai ls -lh /var/log/supervisor/
sudo docker exec pgwatch2-postgresai tail -n 50 /var/log/supervisor/pgwatch2-stderr---supervisor-21oji6p3.log