{"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\/en\/blog\/administrirovanie\/istoriya-aktivnyh-sessij-v-postgresql-novoe-rasshirenie-pgsentinel","title":{"rendered":"Active session history in PostgreSQL \u2014 a new pgsentinel extension","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Company pgsentinel<\/a><\/noindex> released its eponymous extension pgsentinel (GitHub repository<\/a><\/noindex>), which adds the pg_active_session_history view to PostgreSQL \u2014 a history of active sessions (similar to Oracle's v$active_session_history).<\/p>\n

Essentially, these are simply snapshots from pg_stat_activity taken every second, but there are important points:<\/p>\n

    \n
  1. All accumulated information is stored only in memory, and the amount of memory consumed is regulated by the number of recent records stored.<\/li>\n
  2. A field named queryid is added \u2014 the same queryid from the pg_stat_statements extension (requires prior installation).<\/li>\n
  3. A field named top_level_query is added \u2014 the text of the query that called the current query (in cases where pl\/pgsql is used)<\/li>\n<\/ol>\n

    <\/a><\/noindex>
    \nComplete list of fields in 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
    There is currently no precompiled package for installation. It is suggested to download the source code and build the library manually. You must first install the 'devel' package for your server and set the path to pg_config in the PATH variable. To build:<\/p>\n
    cd pgsentinel\/src
    \nmake
    \nmake install<\/p><\/blockquote>\n
    \nAdd parameters to 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
    \nRestart PostgreSQL and create the extension:<\/p>\n
    create extension pgsentinel;<\/p><\/blockquote>\n
    \nThe accumulated information allows you to answer questions such as:<\/p>\n