{"id":1700,"date":"2025-08-13T10:08:06","date_gmt":"2025-08-13T02:08:06","guid":{"rendered":"https:\/\/www.yeetrack.com\/?p=1700"},"modified":"2025-08-14T07:21:59","modified_gmt":"2025-08-13T23:21:59","slug":"postgresql%e7%9b%91%e6%8e%a7pmm%e4%b9%8b%e9%83%a8%e7%bd%b2%e9%97%ae%e9%a2%98%e8%a7%a3%e5%86%b3","status":"publish","type":"post","link":"https:\/\/www.yeetrack.com\/?p=1700","title":{"rendered":"postgresql\u76d1\u63a7pmm\u4e4b\u90e8\u7f72\u95ee\u9898\u89e3\u51b3"},"content":{"rendered":"

\u516c\u53f8\u6570\u5b57\u5316\u90e8\u95e8\u4e3a\u5de5\u5382\u4ea7\u7ebf\u91c7\u8d2d\u4e86\u67d0MES\u751f\u4ea7\u7cfb\u7edf\uff0c\u7531\u516c\u53f8IT\u63d0\u4f9b\u786c\u4ef6\u548c\u57fa\u7840\u64cd\u4f5c\u7cfb\u7edf\uff0cMES\u4f9b\u5e94\u5546\u8d1f\u8d23\u7cfb\u7edf\u5efa\u8bbe\u548c\u540e\u7eed\u7ef4\u62a4\uff0c\u4f46\u4f7f\u7528\u4e00\u6bb5\u65f6\u95f4\u53d1\u73b0\uff0c\u65e0\u8bba\u662f\u8be5MES\u7cfb\u7edf\u81ea\u8eab\u7684\u6027\u80fd\u3001\u8fd8\u662f\u4f9b\u5e94\u5546\u7684\u8fd0\u7ef4\u80fd\u529b\uff0c\u90fd\u6709\u4e25\u91cd\u95ee\u9898\uff0c\u5bfc\u81f4\u516c\u53f8\u7684IT\u548c\u8fd0\u7ef4\u4e00\u76f4\u5728\u4e3a\u5176\u89e3\u51b3\u95ee\u9898\u3002
\n\u4e00\u4e2a\u8fd0\u884c\u5728K8S\u91cc\u4e2dMES\u7cfb\u7edf\uff0c\u4ece\u7f51\u7edc\u5e95\u5c42\u5230\u670d\u52a1\u4e0a\u5c42\u5b8c\u5168\u6ca1\u6709\u76d1\u63a7\uff0c\u51fa\u95ee\u9898\u5168\u9760\u4ea7\u7ebf\u7684\u5de5\u4eba\u558a\u3002\u4e3a\u89e3\u51b3\u6b64\u95ee\u9898\uff0c\u51b3\u5b9a\u8be5MES\u7cfb\u7edf\u5b8c\u5584\u76d1\u63a7\u62a5\u8b66\uff0c\u8fd9\u6b21\u52a0\u7684postgresql\u7684\u76d1\u63a7\u3002 <\/p>\n

<\/p>\n

\u8be5MES\u7cfb\u7edf\u540e\u7aef\u91c7\u7528\u7684\u662fpostgresql\u6570\u636e\u5e93\uff0c\u4e00\u4e3b\u4e00\u5907\uff0c\u5907\u5e93\u53ea\u7528\u6765\u5907\u4efd\u6570\u636e\uff0c\u5b8c\u5168\u4e0d\u53c2\u4e0e\u4e1a\u52a1\u3002 <\/p>\n

\u6253\u5f00postgresql\u81ea\u8eab\u7684pg_stat_statements\u62d3\u5c55<\/h3>\n

\u8fd1\u671f\u51fa\u73b0\u8fc7\u4e00\u6b21postgresql\u673a\u5668\u88ab\u6253\u6ee1\uff0c\u5bfc\u81f4\u90e8\u7f72\u5728\u8be5\u673a\u5668\u4e0a\u7684haproxy\u7f51\u5173\u53d7\u5f71\u54cd\uff08\u670d\u52a1\u90e8\u7f72\u67b6\u6784\u4e0d\u5408\u7406\uff0c\u8fd9\u91cc\u4e0d\u5c55\u5f00\u4e86\uff09\u3002postgresql\u88ab\u6253\u6ee1\u7684\u539f\u56e0\uff0c\u660e\u663e\u662f\u6162sql\u5bfc\u81f4\uff0c\u4f9b\u5e94\u5546\u7684\u8fd0\u7ef4\u5b8c\u5168\u6ca1\u5934\u7eea\u3002
\n\u767b\u4e0a\u6570\u636e\u5e93\u670d\u52a1\u5668\u53bb\u5e2e\u4ed6\u4eec\u5b9a\u4f4d\u4e0b\u539f\u56e0\uff0c\u53d1\u73b0\u5176postgresql\u5373\u6ca1\u6709\u8bb0\u5f55\u6162sql\u673a\u5236\uff0c\u4e5f\u6ca1\u6709\u6253\u5f00\u81ea\u8eab\u7684\u6027\u80fd\u8bb0\u5f55\u62d3\u5c55\u3002\u6253\u5f00\u65b9\u5f0f\u5982\u4e0b\uff1a
\n\u4fee\u6539postgresql\u914d\u7f6e\u6587\u4ef6postgresql.conf<\/strong>\uff0c
\n\u2460 log_min_duration_statement<\/code>\u53c2\u6570\u4e3a1000\uff08\u6beb\u79d2\uff09\uff0c\u4f1a\u5c06\u6267\u884c\u65f6\u95f4\u8d85\u8fc71\u79d2\u7684sql\u8bb0\u5f55\u5230\u6587\u4ef6\u4e2d\uff1b
\n\u2461\u6253\u5f00pg_stat_statements<\/strong>\u62d3\u5c55<\/p>\n

    shared_preload_libraries = 'pg_stat_statements' # (change requires restart)\n    pg_stat_statements.max = 10000\n    pg_stat_statements.track = all<\/code><\/pre>\n
\u4e0a\u9762\u7684\u8c03\u6574\u9700\u8981\u91cd\u542fpostgresql\u751f\u6548\uff1b<\/p>\n
\u62d3\u5c55\u751f\u6548<\/h3>\n
\u91cd\u542f\u6570\u636e\u5e93\u540e\uff0c\u767b\u5f55\u5230\u6570\u636e\u5e93\u4e2d\u6267\u884cCREATE EXTENSION pg_stat_statements;<\/code>\uff0cpostgresql\u4f1a\u5c06sql\u7684\u6267\u884c\u60c5\u51b5\u8bb0\u5f55\u5230\u8fd9\u4e2a\u8868\u4e2d\uff0c\u65b9\u4fbf\u95ee\u9898\u5b9a\u4f4d\u3002\u53ef\u4ee5select * from pg_stat_statements limit 10<\/code>\u770b\u770b\u8bb0\u5f55\u7684\u5185\u5bb9\u3002  <\/p>\n
\u5b89\u88c5PMM-Server<\/h3>\n
pmm\u662f\u514d\u8d39\u5f00\u6e90\u7684\u4e00\u6b3epostgresql\u76d1\u63a7\u5de5\u5177\uff0c\u5176\u96c6\u6210\u4e86\u5f88\u591a\u6d41\u884c\u7684\u5f00\u6e90\u7ec4\u4ef6\uff0c\u5982Prometheus\u3001grafana\u3001qan-api\u7b49\uff0c\u5f00\u7bb1\u5373\u7528\u3002 \u53ef\u4ee5\u4ece\u5176\u5b98\u7f51\u627e\u5230\u4e00\u952e\u5b89\u88c5\u7684\u547d\u4ee4\uff0c\u4e0d\u8fc7\u6211\u5728\u5b89\u88c5\u8fc7\u7a0b\u4e2d\u9047\u5230qan-api2\u8fdb\u7a0b\u5f02\u5e38\u9000\u51fa\u95ee\u9898\uff0c\u5e94\u8be5\u662fdocker\u6743\u9650\u95ee\u9898\uff0c\u89e3\u51b3\u65b9\u5f0f\u5982\u4e0b\u3002  <\/p>\n
    \n
  1. \n
    docker logs docker_id\u770b\u5230qan-api2\u8fdb\u7a0b\u4e0d\u65ad\u5f02\u5e38\u9000\u51fa  <\/p>\n
    2025-08-11 07:17:50,925 INFO spawned: 'qan-api2' with pid 87 2025-08-11 07:17:51,130 INFO exited: qan-api2 (exit status 1; not expected) 2025-08-11 07:17:53,137 INFO spawned: 'qan-api2' with<\/code><\/pre>\n<\/li>\n
  2. \n
    docker exec -it docker_id bash\uff0c\u53bb\u770b\u5177\u4f53\u7684\u65e5\u5fd7\uff0c\u5177\u4f53\u65e5\u5fd7\u5728\u5bb9\u5668\u4e2d\/srv\/logs\/qan-api2.log<\/strong>\u6587\u4ef6\u4e2d\uff0c\u770b\u5230\u662fdocker\u8fdb\u7a0b\u6ca1\u6709\u8bbf\u95ee\u67d0\u8def\u5f84\u7684\u6743\u9650\u3002    <\/p>\n
    stdlog: Migrations: code: 74, message: Cannot read from file 119, errno: 1, strerror: Operation not permitted: while reading column version at \/srv\/clickhouse\/store\/246\/2460fff4-a704-472f-9f4b-0e9de16b8a5d\/: While executing Log in line 0: SELECT version, dirty FROM <\/code>schema_migrations<\/code> ORDER BY sequence DESC LIMIT 1 stdlog: qan-api2 v3.3.1.  <\/pre>\n<\/li>\n<\/ol>\n
    \u6309\u7406\u8bf4\u662f\u4f7f\u7528pmm-server\u811a\u672c\u4e00\u952e\u5b89\u88c5\uff0c\u4e0d\u5e94\u8be5\u51fa\u73b0\u6b64\u7c7b\u95ee\u9898\uff0c\u53ef\u80fd\u662f\u5bf9linux\u7cfb\u7edf\u517c\u5bb9\u4e0d\u597d\u5427\uff0c\u4ece\u5176github\u4e3b\u9875\u4e0a\u4e5f\u770b\u5230\u6709\u4eba\u63d0\u8fc7\u6b21\u95ee\u9898\uff0c\u5728\u6267\u884cdocker run\u65f6\u8981\u4f7f\u7528--privileged<\/code>\u53c2\u6570\uff0c\u5982\u4e0b\u5728\u5176\u5b89\u88c5\u811a\u672c\u7684494\u884c\u3002    <\/p>\n
        486   if ! run_docker \"volume inspect $volume_name 1> \/dev\/null 2> \/dev\/null\"; then\n    487     if ! run_docker \"volume create $volume_name 1> \/dev\/null\"; then\n    488       die \"${RED}ERROR: cannot create PMM Data Volume${NOFORMAT}\"\n    489     fi\n    490     msg \"Created PMM Data Volume: $volume_name\"\n    491   fi\n    492   msg \"Starting PMM Server...\"\n    493\n    494   run_pmm=\"run --privileged -d -p $port:8443 --volume $volume_name:\/srv --name $container_name --network $network_name $docker_env_flags --restart always $repo:$tag\"\n    495\n    496   run_docker \"$run_pmm 1> \/dev\/null\"\n    497   msg \"Created PMM Server: $container_name\"\n    498   msg \"\\nUse the following command if you ever need to update your container manually:\"\n    499   msg \"\\tdocker pull $repo:$tag \\n\"\n    500   msg \"\\tdocker $run_pmm \\n\"\n    501 }<\/code><\/pre>\n
    \u4fee\u6539\u4e4b\u540e\uff0c\u518d\u6267\u884c\u5c31\u6b63\u5e38\u4e86\u3002  <\/p>\n
    \u5b89\u88c5pmm-agent<\/h3>\n
    \u540c\u6837\u4f7f\u7528\u5b98\u65b9\u63d0\u4f9b\u7684\u547d\u4ee4\u5373\u53ef\uff0c\u9700\u8981\u5148\u542f\u52a8service\uff0c\u7136\u540e\u6ce8\u518cpostgresql\u76d1\u63a7\u3002   <\/p>\n
      \n
    1. \n
      \u6ce8\u518cagent  <\/p>\n
      pmm-admin config --server-insecure-tls --server-url=https:\/\/admin:admin@10.XX.XX.XX:443 --force<\/code><\/pre>\n<\/li>\n
    2. \n
      \u5728postgresql\u6570\u636e\u5e93\u4e0a\uff0c\u521b\u5efa\u9ad8\u6743\u9650\u76d1\u63a7\u8d26\u53f7  <\/p>\n
      CREATE USER pmm WITH SUPERUSER ENCRYPTED PASSWORD 'XXX';<\/code><\/pre>\n<\/li>\n
    3. \n
      \u6ce8\u518cpostgresql\u76d1\u63a7  <\/p>\n
      pmm-admin add postgresql \\\n--username=pmm \\\n--password=XXX \\\n--host=127.0.0.1 \\\n--port=5432 \\\n--query-source=pgstatements \\\n--disable-collectors=walreceiver \\\n--service-name=XXX<\/code><\/pre>\n
      \u4e4b\u6240\u4ee5\u8981\u7981\u7528walreceiver\uff0c\u662f\u5728\u5b9e\u9645\u542f\u52a8\u540e\uff0c\u53d1\u73b0pmm-agent\u5728\u67e5\u8be2\u76d1\u63a7\u65f6\u8001\u662f\u5728\u67e5\u4e00\u4e2a\u4e0d\u5b58\u5728\u7684\u5b57\u6bb5\uff0c\u5bfc\u81f4\u6570\u636e\u5e93\u9891\u7e41\u62a5\u9519\uff0c\u53ef\u80fd\u662fagent\u7248\u672c\u517c\u5bb9\u95ee\u9898\u5427\uff0c\u6545\u5c06\u5176\u7981\u7528\u6389\u3002<\/p>\n<\/li>\n
    4. \n
      \u542f\u52a8agent\u5373\u53ef\uff0c\u542f\u52a8\u4e4b\u540e\u5982\u4e0b\uff1a  <\/p>\n
      root@XXX:\/var\/log\/postgresql# pmm-admin list\nService type        Service name             Address and port        Service ID\nPostgreSQL          jintan-postgresql        127.0.0.1:5432          ba5c020f-8bfb-4c9e-b865-1b393264a32d\n\nAgent type                           Status           Metrics Mode        Agent ID                                    Service ID                                  Port\npmm_agent                            Connected                            959ce03f-286d-48da-bb69-d4ec95d040a8                                                    0\nnode_exporter                        Running          push                34a0ba12-09c7-4965-9102-e7ec0f461859                                                    42000\npostgres_exporter                    Running          push                8eec7ec6-b84d-4b67-a6ce-52a9abca98b1        ba5c020f-8bfb-4c9e-b865-1b393264a32d        42002\npostgresql_pgstatements_agent        Running                              b8d81b08-0ae7-49fa-bfa8-b25b630a2f1e        ba5c020f-8bfb-4c9e-b865-1b393264a32d        0\nvmagent                              Running          push                4717b18c-4239-4372-a21e-0b59c7239387                                                    42001<\/code><\/pre>\n<\/li>\n<\/ol>\n
      \u6700\u7ec8\u76d1\u63a7\u6548\u679c<\/h3>\n
      \"\"<\/a>  <\/p>\n
      \"\"<\/a>  <\/p>\n
      \"\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
      \u516c\u53f8\u6570\u5b57\u5316\u90e8\u95e8\u4e3a\u5de5\u5382\u4ea7\u7ebf\u91c7\u8d2d\u4e86\u67d0MES\u751f\u4ea7\u7cfb\u7edf\uff0c\u7531\u516c\u53f8IT\u63d0\u4f9b\u786c\u4ef6\u548c\u57fa\u7840\u64cd\u4f5c\u7cfb\u7edf\uff0cMES\u4f9b\u5e94\u5546\u8d1f\u8d23\u7cfb\u7edf\u5efa\u8bbe\u548c\u540e\u7eed\u7ef4\u62a4\uff0c\u4f46\u4f7f\u7528\u4e00\u6bb5\u65f6\u95f4\u53d1\u73b0\uff0c\u65e0\u8bba\u662f\u8be5MES\u7cfb\u7edf\u81ea\u8eab\u7684\u6027\u80fd\u3001\u8fd8\u662f\u4f9b\u5e94\u5546\u7684\u8fd0\u7ef4\u80fd\u529b\uff0c\u90fd\u6709\u4e25\u91cd\u95ee...<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"pgc_sgb_lightbox_settings":"","footnotes":""},"categories":[34],"tags":[96,97,95,90],"class_list":["post-1700","post","type-post","status-publish","format-standard","hentry","category-software","tag-grafana","tag-pmm","tag-postgresql","tag-90"],"views":393,"_links":{"self":[{"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=\/wp\/v2\/posts\/1700","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1700"}],"version-history":[{"count":3,"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=\/wp\/v2\/posts\/1700\/revisions"}],"predecessor-version":[{"id":1706,"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=\/wp\/v2\/posts\/1700\/revisions\/1706"}],"wp:attachment":[{"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1700"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1700"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yeetrack.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1700"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}