Pgwatch2: erinevus redaktsioonide vahel

Allikas: Imre kasutab arvutit
Mine navigeerimisribaleMine otsikasti
Resümee puudub
 
(ei näidata sama kasutaja 11 vahepealset redaktsiooni)
4. rida: 4. rida:
   
 
===Tööpõhimõte===
  
===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
  +
  +
<pre>
  +
# 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
  +
</pre>
   
 
TODO
  
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====
  
====pgwatch paigaldamine====
11. rida: 52. rida:
 
<pre>
  
<pre>
 
# cd /srv/pgwatch2
  
# cd /srv/pgwatch2
−
# cat dc/docker-compose.yml
+
# cat dc/docker-compose-pgwatch2.yml
−
version: '2'
  
   
 
services:
  
services:
  +
svc_pgwatch2:
−
service_pgwatch2:
  
−
image: cybertec/pgwatch2-postgres:latest
 +
image: cybertec/pgwatch2-postgres:1.13.0
 
ports:
  
ports:
 
- "3000:3000"
  
- "3000:3000"
22. rida: 62. rida:
 
- "8081:8081"
  
- "8081:8081"
   
−
container_name: dc_pgwatch2
 +
container_name: cn_pgwatch2
 
 
 
networks:
  
networks:
35. rida: 75. rida:
 
networks:
  
networks:
 
nw_pgwatch2:
  
nw_pgwatch2:
  +
name: nw_pgwatch2
  +
driver: bridge
 
</pre>
  
</pre>
  +
  +
kus
  +
  +
* TODO
   
 
Enne käivitamist tuleb tekitada volume ressusidele vastavad kataloogi dockerhost failisüsteemi
  
Enne käivitamist tuleb tekitada volume ressusidele vastavad kataloogi dockerhost failisüsteemi
47. rida: 93. rida:
 
Esmaseks käivitamiseks sobib öelda
  
Esmaseks käivitamiseks sobib öelda
   
−
# docker compose up -d
 +
# docker compose -f docker-compose-pgwatch2.yml up -d
   
 
====nginx paigaldamine====
  
====nginx paigaldamine====
   
 
<pre>
  
<pre>
−
# cat docker-compose.yml
+
# cat /srv/nginx/dc/docker-compose-nginx.yml
−
version: '2'
  
−  
 
services:
  
services:
 
svc_nginx:
  
svc_nginx:
−
image: nginx
 +
image: nginx:latest
 
ports:
  
ports:
  +
- "80:80"
 
- "443:443"
  
- "443:443"
   
−
container_name: dc_nginx
 +
container_name: cn_nginx
 
 
 
networks:
  
networks:
67. rida: 112. rida:
 
 
 
volumes:
  
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/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/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/cert.pem:/etc/ssl/localcers/cert.pem'
 
- '/srv/nginx/volumes/etc/ssl/localcerts/key.pem:/etc/ssl/localcers/key.pem'
  
- '/srv/nginx/volumes/etc/ssl/localcerts/key.pem:/etc/ssl/localcers/key.pem'
  +
  +
restart: unless-stopped
   
 
networks:
  
networks:
 
nw_nginx:
  
nw_nginx:
  +
name: nw_nginx
  +
driver: bridge
 
</pre>
  
</pre>
  +
  +
kus
  +
  +
* TODO
  +
  +
nginx seadistusfaili default.conf sisu
  +
  +
<pre>
  +
# 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;
  +
}
  +
}
  +
</pre>
  +
  +
kus
  +
  +
* TODO
   
 
====Paketifiltriga ligipääsu piiramine====
  
====Paketifiltriga ligipääsu piiramine====
   
 
<pre>
  
<pre>
−
root@vis3-pgw-01:/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 3000 -j REJECT
−
root@vis3-pgw-01:/srv/nginx/dc# iptables -I DOCKER-USER -p tcp -s 0.0.0.0/0 --dport 8080 -j REJECT
 +
root@pgw:/srv/nginx/dc# iptables -I DOCKER-USER -p tcp -s 0.0.0.0/0 --dport 8080 -j REJECT
 
</pre>
  
</pre>
   

Viimane redaktsioon: 3. juuni 2024, kell 02:48

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
Pärit leheküljelt "https://www.auul.pri.ee/wiki/index.php?title=Pgwatch2&oldid=997"