{"id":38073,"date":"2019-10-31T22:21:32","date_gmt":"2019-10-31T19:21:32","guid":{"rendered":"https:\/\/prohoster.info\/blog\/istoriya-aktivnyh-sessij-v-postgresql-novoe-rasshirenie-pgsentinel\/"},"modified":"2019-10-31T22:21:32","modified_gmt":"2019-10-31T19:21:32","slug":"istoriya-aktivnyh-sessij-v-postgresql-novoe-rasshirenie-pgsentinel","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/istoriya-aktivnyh-sessij-v-postgresql-novoe-rasshirenie-pgsentinel","title":{"rendered":"Hist\u00f3ria akt\u00edvnych rel\u00e1ci\u00ed v PostgreSQL \u2013 nov\u00e9 roz\u0161\u00edrenie pgsentinel","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Spolo\u010dnos\u0165 pgsentinel<\/a><\/noindex> uvedla rovnomenn\u00e9 roz\u0161\u00edrenie pgsentinel (github repozit\u00e1r<\/a><\/noindex>), ktor\u00e9 do PostgreSQL prid\u00e1va poh\u013ead pg_active_session_history \u2013 hist\u00f3riu akt\u00edvnych rel\u00e1ci\u00ed (analogicky ku v$active_session_history v Oracle).<\/p>\n

V podstate ide o sn\u00edmky z pg_stat_activity ka\u017ed\u00fa sekundu, ale s\u00fa tu d\u00f4le\u017eit\u00e9 aspekty:<\/p>\n

    \n
  1. V\u0161etky zhroma\u017eden\u00e9 inform\u00e1cie sa uchov\u00e1vaj\u00fa iba v opera\u010dnej pam\u00e4ti a objem spotrebovanej pam\u00e4te sa reguluje po\u010dtom posledn\u00fdch ulo\u017een\u00fdch z\u00e1znamov.<\/li>\n
  2. Prid\u00e1va sa pole queryid \u2013 ten ist\u00fd queryid z roz\u0161\u00edrenia pg_stat_statements (je potrebn\u00e9 predch\u00e1dzaj\u00face nain\u0161talovanie).<\/li>\n
  3. Prid\u00e1va sa pole top_level_query \u2013 text dotazu, z ktor\u00e9ho bol vyvolan\u00fd aktu\u00e1lny dotaz (v pr\u00edpade pou\u017eitia pl\/pgsql)<\/li>\n<\/ol>\n

    <\/a><\/noindex>
    \nCel\u00fd zoznam pol\u00ed pg_active_session_history:<\/b><\/p>\n

    \n      Column      |           Type           \n------------------+--------------------------\n ash_time         | timestamp with time zone \n datid            | oid                      \n datname          | text                     \n pid              | integer                  \n usesysid         | oid                      \n usename          | text                     \n application_name | text                     \n client_addr      | text                     \n client_hostname  | text                     \n client_port      | integer                  \n backend_start    | timestamp with time zone \n xact_start       | timestamp with time zone \n query_start      | timestamp with time zone \n state_change     | timestamp with time zone \n wait_event_type  | text                     \n wait_event       | text                     \n state            | text                     \n backend_xid      | xid                      \n backend_xmin     | xid                      \n top_level_query  | text                     \n query            | text                     \n queryid          | bigint                   \n backend_type     | text                     \n<\/pre>\n
    Hotov\u00fd bal\u00ed\u010dek pre in\u0161tal\u00e1ciu zatia\u013e nie je. Odpor\u00fa\u010da sa stiahnu\u0165 zdrojov\u00e9 k\u00f3dy a skompilova\u0165 kni\u017enicu samostatne. Predt\u00fdm je potrebn\u00e9 nain\u0161talova\u0165 \u201edevel\u201c bal\u00edk pre svoj server a do premennej PATH zap\u00edsa\u0165 cestu k pg_config. Skompilujeme:<\/p>\n
    cd pgsentinel\/src
    \nmake
    \nmake install<\/p><\/blockquote>\n
    \nPrid\u00e1vame parametre do postgres.conf:<\/p>\n
    shared_preload_libraries = 'pg_stat_statements,pgsentinel'
    \ntrack_activity_query_size = 2048
    \npg_stat_statements.track = all<\/p>\n
    # \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0443\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u0435\u043c\u044b\u0445 \u0432 \u043f\u0430\u043c\u044f\u0442\u0438 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0438\u0445 \u0437\u0430\u043f\u0438\u0441\u0435\u0439
    \npgsentinel_ash.max_entries = 10000<\/p><\/blockquote>\n
    \nRe\u0161tartujeme PostgreSQL a vytvor\u00edme roz\u0161\u00edrenie:<\/p>\n
    create extension pgsentinel;<\/p><\/blockquote>\n
    \nZhroma\u017eden\u00e9 inform\u00e1cie umo\u017e\u0148uj\u00fa odpoveda\u0165 na tak\u00e9to ot\u00e1zky, ako s\u00fa:<\/p>\n