{"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\/es\/blog\/administrirovanie\/istoriya-aktivnyh-sessij-v-postgresql-novoe-rasshirenie-pgsentinel","title":{"rendered":"El historial de sesiones activas en PostgreSQL \u2014 nueva extensi\u00f3n pgsentinel","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Empresa pgsentinel<\/a><\/noindex> lanz\u00f3 la extensi\u00f3n hom\u00f3nima pgsentinel (repositorio de github<\/a><\/noindex>), que a\u00f1ade a PostgreSQL la vista pg_active_session_history \u2014 el historial de sesiones activas (similar a la v$active_session_history de Oracle).<\/p>\n

En esencia, son simplemente capturas por segundo de pg_stat_activity, pero hay puntos importantes:<\/p>\n

    \n
  1. Toda la informaci\u00f3n acumulada se almacena solo en la memoria RAM, y el volumen de memoria consumido se regula por la cantidad de \u00faltimos registros almacenados.<\/li>\n
  2. Se a\u00f1ade el campo queryid \u2014 el mismo queryid de la extensi\u00f3n pg_stat_statements (se requiere instalaci\u00f3n previa).<\/li>\n
  3. Se a\u00f1ade el campo top_level_query \u2014 el texto de la consulta desde la que se invoc\u00f3 la consulta actual (en caso de usar pl\/plpgsql)<\/li>\n<\/ol>\n

    <\/a><\/noindex>
    \nLista completa de campos de pg_active_session_history:<\/b><\/p>\n

    \n      Columna      |           Tipo           \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
    A\u00fan no hay un paquete listo para la instalaci\u00f3n. Se sugiere descargar el c\u00f3digo fuente y compilar la biblioteca por cuenta propia. Se requiere instalar el paquete 'devel' para su servidor y a\u00f1adir la ruta a pg_config a la variable PATH. Compilamos:<\/p>\n
    cd pgsentinel\/src
    \nmake
    \nmake install<\/p><\/blockquote>\n
    \nA\u00f1adimos par\u00e1metros en 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
    \nReiniciamos PostgreSQL y creamos la extensi\u00f3n:<\/p>\n
    create extension pgsentinel;<\/p><\/blockquote>\n
    \nLa informaci\u00f3n acumulada permite responder preguntas como:<\/p>\n