Pgwatch2

Sissejuhatus

TODO

Tööpõhimõte

Väited

• lihtsalt pgwatch2 kasutaja abil baasi monitoorides saab omajagu infot (nt TPS, tuple ins/upd/delete)
• lisades baasi pg_stat_statements extension'i saab lisaks QPS ja avg_query
• liigutades privileged-päringud tava-päringute alla pgwatch-ui webgui liideses saab lahti logis helper funktsioonide vigadest ning sellega kaasneb ka rollback'ide kadumine

# grep ERROR: /var/log/postgresql/postgresql-14-main_ee.log | grep get_stat_acti | tail -n 5
[from_14_main_ee] 2024-06-02 17:56:04.887 EEST [15721] pgwatch2@orto ERROR:  function get_stat_activity() does not exist at character 98
[from_14_main_ee] 2024-06-02 17:57:04.886 EEST [16438] pgwatch2@orto ERROR:  function get_stat_activity() does not exist at character 98
[from_14_main_ee] 2024-06-02 17:58:04.905 EEST [16787] pgwatch2@orto ERROR:  function get_stat_activity() does not exist at character 98
[from_14_main_ee] 2024-06-02 17:59:04.888 EEST [17190] pgwatch2@orto ERROR:  function get_stat_activity() does not exist at character 98
[from_14_main_ee] 2024-06-02 18:00:04.900 EEST [17456] pgwatch2@orto ERROR:  function get_stat_activity() does not exist at character 98

# date
Sun 02 Jun 2024 06:01:34 PM EEST

TODO

Kahesugused meetrikud on

• tava-metrics
• privilegeeritud-metrics

Tundub, et 2024 aasta kevadel saab hakkama sellise mõtlemisega

• pgwatch2 kasutaja kuulub pg_monitor gruppi
• pgwatch deemon kollektib väärtused baasist pgwatch2 kasutaja kätega
• pgwatch kasutab kollektimisel nn privilegeeritud-metrics päringuid

preset exaustive juurest eemaldada

• '"cpu_load": 60,' - tegeleb veateatega 'ERROR: function get_load_average() does not exist at character 286'

Selleks, et pgwatch2 kasutaja kasutaks privilegeeritud päringuid tuleb pgwatch2-ui webgui rakenduse abil kopi-pasteda mõned olulised pöördumised 'privilged' tulba lahrist 'tava' tulba lahtrisse, nt exaustive puhul

• backends v. 10 - tegeleb 'pg_stat_activity' teemaga
• stat_statements v. 15 - tegeleb 'pg_stat_statements' teemaga
• table_bloat_approx_summary_sql v. 12
• wal_size - tegeleb 'pg_ls_waldir' teemaga
• sequence_health

pgwatch paigaldamine

# cd /srv/pgwatch2
# cat dc/docker-compose-pgwatch2.yml 

services:
  svc_pgwatch2:
    image: cybertec/pgwatch2-postgres:1.13.0
    ports:
     - "3000:3000"
     - "8080:8080"
     - "8081:8081"

    container_name: cn_pgwatch2
  
    networks:
      - nw_pgwatch2
    
    volumes:
      - '/srv/pgwatch2/volumes/pgwatch2/persistent-config:/pgwatch2/persistent-config'
      - '/srv/pgwatch2/volumes/var/lib/postgresql:/var/lib/postgresql'
      - '/srv/pgwatch2/volumes/var/lib/grafana:/var/lib/grafana'
      - '/srv/pgwatch2/volumes/etc/grafana/grafana.ini:/etc/grafana/grafana.ini'

networks:
  nw_pgwatch2:
    name: nw_pgwatch2
    driver: bridge

kus

• TODO

Enne käivitamist tuleb tekitada volume ressusidele vastavad kataloogi dockerhost failisüsteemi

# cd /srv/pgwatch2/volumes
# mkdir -p pgwatch2/persistent-config var/lib/postgresql var/lib/grafana etc/grafana
# chmod 0777 pgwatch2/persistent-config var/lib/postgresql var/lib/grafana etc/grafana

Esmaseks käivitamiseks sobib öelda

# docker compose -f docker-compose-pgwatch2.yml up -d

nginx paigaldamine

# cat /srv/nginx/dc/docker-compose-nginx.yml 
services:
  svc_nginx:
    image: nginx:latest
    ports:
     - "80:80"
     - "443:443"

    container_name: cn_nginx
  
    networks:
      - nw_nginx
    
    volumes:
#      - '/srv/nginx/volumes/usr/share/nginx/html:/usr/share/nginx/html'
      - '/srv/nginx/volumes/etc/nginx/nginx.conf:/etc/nginx/nginx.conf'
      - '/srv/nginx/volumes/etc/nginx/htpasswd:/etc/nginx/htpasswd'
      - '/srv/nginx/volumes/etc/nginx/conf.d/default.conf:/etc/nginx/conf.d/default.conf'
      - '/srv/nginx/volumes/etc/ssl/localcerts/cert.pem:/etc/ssl/localcers/cert.pem'
      - '/srv/nginx/volumes/etc/ssl/localcerts/key.pem:/etc/ssl/localcers/key.pem'

    restart: unless-stopped

networks:
  nw_nginx:
    name: nw_nginx
    driver: bridge

kus

• TODO

nginx seadistusfaili default.conf sisu

# cat /srv/nginx/volumes/etc/nginx/conf.d/default.conf
map $http_upgrade $connection_upgrade {
  default upgrade;
  '' close;
}

server {
    listen 80;
    server_name pgw.auul.pri.ee;
    return 302 https://pgw.auul.pri.ee/;
}


server {
    listen       443 ssl;
    server_name  localhost;

    ssl_certificate         /etc/ssl/localcers/cert.pem;
    ssl_certificate_key     /etc/ssl/localcers/key.pem;

    auth_basic           "basic auth ala";
    auth_basic_user_file /etc/nginx/htpasswd;


   location /api/live/ {
      proxy_http_version 1.1;
      proxy_set_header Upgrade $http_upgrade;
      proxy_set_header Connection $connection_upgrade;
      proxy_set_header Host $host;
      proxy_pass http://192.168.1.100:3000/api/live/;
      proxy_set_header Authorization "";
   }

    location / {
        proxy_pass http://192.168.1.100:3000/;
        proxy_set_header Authorization "";
    }

    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
        root   /usr/share/nginx/html;
    }
}

kus

• TODO

Paketifiltriga ligipääsu piiramine

root@pgw:/srv/nginx/dc# iptables -I DOCKER-USER -p tcp -s 0.0.0.0/0 --dport 3000 -j REJECT
root@pgw:/srv/nginx/dc# iptables -I DOCKER-USER -p tcp -s 0.0.0.0/0 --dport 8080 -j REJECT

Uuendamine

TODO

Paigaldamine

TODO

Kasutamine - andmebaasi ettevalmistamine jälgimiseks

TODO

Kasutamine - kogutud andmetega töötamine

TODO

Kasulikud lisamaterjalid

• TODO