{"id":1967,"date":"2020-07-29T21:47:16","date_gmt":"2020-07-29T13:47:16","guid":{"rendered":"http:\/\/39.97.231.10\/?p=1967"},"modified":"2020-07-29T21:47:16","modified_gmt":"2020-07-29T13:47:16","slug":"pgbouncer-%e4%bd%bf%e7%94%a8%e6%96%b9%e6%b3%95","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=1967","title":{"rendered":"PgBouncer \u4f7f\u7528\u65b9\u6cd5"},"content":{"rendered":"
\n
\n\u4e00\u3001PgBouncer\u7b80\u4ecb<\/span>\n<\/div>\n<\/div>\n
\n
\nPgBouncer\u662f\u4e3aPostgreSQL\u6570\u636e\u5e93\u63d0\u4f9b\u7684\u4e00\u4e2a\u8f7b\u91cf\u7ea7\u8fde\u63a5\u6c60\u5de5\u5177\u3002PostgreSQL\u6570\u636e\u5e93\u662f\u57fa\u4e8e\u8fdb\u7a0b\u7684\u67b6\u6784\uff0c\u5e94\u7528\u6bcf\u6b21\u8fde\u63a5\u5230PG\uff0cPG\u90fd\u4f1a\u521b\u5efa\u4e00\u4e2a\u65b0\u7684\u8fdb\u7a0b\u6765\u4e3a\u5e94\u7528\u670d\u52a1\uff0c\u670d\u52a1\u5b8c\u6210\u4e4b\u540e\uff0c\u5173\u95ed\u8fde\u63a5\uff0c\u8fdb\u7a0b\u88ab\u9500\u6bc1\u3002\u9891\u7e41\u5730\u521b\u5efa\u3001\u9500\u6bc1\u8fdb\u7a0b\u5f00\u9500\u5f88\u5927\uff0c\u8017\u8d39\u8d44\u6e90\u3002PgBouncer \u8fde\u63a5\u6c60\u628a\u4e0e\u6570\u636e\u5e93\u7684\u8fde\u63a5\u8fdb\u884c\u7f13\u5b58\uff0c\u5e94\u7528\u8bf7\u6c42\u5230\u6765\u65f6\uff0c\u5206\u914d\u4e00\u4e2a\u7a7a\u95f2\u7684\u8fde\u63a5\u4f7f\u7528\uff0c\u5e94\u7528\u6267\u884c\u5b8c\u6210\u540e\uff0c\u8fde\u63a5\u4e0d\u4f1a\u76f4\u63a5\u5173\u95ed\uff0c\u800c\u662f\u88ab\u653e\u5165\u8fde\u63a5\u6c60\uff0c\u7b49\u5f85\u4e0b\u6b21\u88ab\u4f7f\u7528\uff0c\u8fd9\u6837\u964d\u4f4e\u4e86\u8d44\u6e90\u7684\u6d88\u8017\uff0c\u63d0\u9ad8\u4e86\u7cfb\u7edf\u6027\u80fd\u3002<\/p>\n

\u4e8c\u3001PgBouncer\u652f\u6301\u4e09\u79cd\u8fde\u63a5\u6c60\u6a21\u578b<\/h4>\n
    \n
  1. session\uff0c\u4f1a\u8bdd\u7ea7\u8fde\u63a5\uff0c\u5728\u5ba2\u6237\u7aef\u8fde\u63a5\u7684\u751f\u547d\u5468\u671f\u5185\uff0c\u8fde\u63a5\u6c60\u5206\u914d\u4e00\u4e2a\u8fde\u63a5\u7ed9\u5b83\uff0c\u76f4\u5230\u5ba2\u6237\u7aef\u65ad\u5f00\u8fde\u63a5\uff0c\u5206\u914d\u7684\u8fde\u63a5\u624d\u4f1a\u56de\u5230\u8fde\u63a5\u6c60\u4e2d\u3002<\/li>\n
  2. transaction\uff0c\u4e8b\u52a1\u7ea7\u8fde\u63a5\uff0c\u5f53\u5ba2\u6237\u7aef\u7684\u6bcf\u4e2a\u4e8b\u52a1\u7ed3\u675f\u65f6\uff0c\u6570\u636e\u5e93\u8fde\u63a5\u88ab\u91ca\u653e\u5230\u8fde\u63a5\u6c60\u4e2d\uff0c\u518d\u6b21\u6267\u884c\u4e00\u4e2a\u4e8b\u52a1\u65f6\uff0c\u9700\u8981\u518d\u4ece\u8fde\u63a5\u6c60\u83b7\u53d6\u4e00\u4e2a\u8fde\u63a5\u3002<\/li>\n
  3. statement\uff0c\u8bed\u53e5\u7ea7\u8fde\u63a5\uff0c\u6267\u884c\u5b8c\u4e00\u4e2aSQL\u8bed\u53e5\uff0c\u6570\u636e\u5e93\u8fde\u63a5\u5c31\u4f1a\u88ab\u91ca\u653e\u5230\u8fde\u63a5\u6c60\u4e2d\uff0c\u518d\u6b21\u6267\u884c\u4e00\u4e2aSQL\u8bed\u53e5\uff0c\u9700\u8981\u518d\u4ece\u8fde\u63a5\u6c60\u83b7\u53d6\u4e00\u4e2a\u8fde\u63a5\uff0c\u8fd9\u79cd\u6a21\u5f0f\u610f\u5473\u7740\u5ba2\u6237\u7aef\u5f3a\u5236autocommit\u6a21\u5f0f\u3002<\/li>\n<\/ol>\n

    \u4e09\u3001\u5b89\u88c5PgBouncer<\/h4>\n

    3.1 yum install \u5b89\u88c5<\/h5>\n

    yum install -y pgbouncer<\/p>\n

    3.2 \u6e90\u7801\u7f16\u8bd1\u5b89\u88c5<\/h5>\n

    \u6e90\u7801\u5730\u5740\uff1a
    \nhttps:\/\/github.com\/pgbouncer\/pgbouncer<\/a><\/p>\n

    git clone https:\/\/github.com\/pgbouncer\/pgbouncer.git\ncd pgbouncer\ngit submodule init\ngit submodule update\n.\/autogen.sh\n.\/configure ...\nmake\nmake install\n<\/code><\/pre>\n
    \u56db\u3001PgBouncer\u4f7f\u7528\u65b9\u6cd5<\/h4>\n
    4.1 \u521b\u5efa\u914d\u7f6e\u6587\u4ef6<\/h5>\n
    useradd pg
    \nmkdir \/opt\/pgbouncer
    \nchown -R pg:pg \/opt\/pgbouncer
    \nsu – pg
    \ncd \/opt\/pgbouncer
    \n\u7f16\u8f91\u914d\u7f6e\u6587\u4ef6\uff1apgbouncer.ini<\/p>\n
    [databases]\n* = host=127.0.0.1 port=5432\n [pgbouncer]\n listen_port = 6432\n listen_addr = *\n auth_type = md5\n auth_file = userlist.txt\n logfile = \/opt\/pgbouncer\/pgbouncer.log\n pidfile = \/opt\/pgbouncer\/pgbouncer.pid\n admin_users = admin\n min_pool_size=10\n<\/code><\/pre>\n
    \u7f16\u8f91\u7528\u6237\u5bc6\u7801\u914d\u7f6e\u6587\u4ef6\uff1auserlist.txt<\/p>\n
    \\\"admin\\\" \\\"md5b9d11b3be25f5a1a7dc8ca04cd310b28\\\"\n<\/code><\/pre>\n
    \u7528\u6237\u5bc6\u7801\u53ef\u4ee5\u901a\u8fc7\u8fde\u63a5\u5230PostgreSQL\u6570\u636e\u5e93\uff0c\u6267\u884c\u4ee5\u4e0bSQL\u83b7\u53d6\uff1a
    \nselect usename,passwd from pg_shadow;
    \n\u914d\u7f6e\u6587\u4ef6\u6240\u5728\u76ee\u5f55\u7684\u5c5e\u4e3b\u9700\u8981\u4e0epgbouncer\u8fdb\u7a0b\u7684\u542f\u52a8\u7528\u6237\u4fdd\u6301\u4e00\u81f4\u3002<\/p>\n
    4.2 \u542f\u52a8PgBouncer<\/h5>\n
    pgbouncer -d pgbouncer.ini
    \npgbouncer\u5fc5\u987b\u4f7f\u7528\u975eroot\u7528\u6237\u542f\u52a8\uff0cpgbouncer.ini \u914d\u7f6e\u6587\u4ef6\u6240\u5728\u76ee\u5f55\u5c5e\u4e3b\u9700\u8981\u4e0epgbouncer\u542f\u52a8\u7528\u6237\u4e00\u81f4\u3002<\/p>\n
    4.3 \u8fde\u63a5PgBouncer<\/h5>\n
    psql -p 6432 -U admin pg<\/p>\n
    4.4 \u7ba1\u7406PgBouncer<\/h5>\n
    psql -p 6432 -U admin pgbouncer
    \n\u6307\u5b9a\u4e00\u4e2a\u7279\u6b8a\u7684\u5e93\u540dpgbouncer\uff0c\u4f7f\u7528SHOW HELP; \u547d\u4ee4\u5373\u53ef\u67e5\u770b\u5404\u79cd\u7ba1\u7406\u547d\u4ee4\uff0c\u5982\u4e0b\uff1a<\/p>\n
    $ psql -p 6432 -U admin pgbouncer\nPassword for user admin:\npsql (11.5, server 1.13.0\/bouncer)\nType \\\"help\\\" for help.\npgbouncer=# show help;\nNOTICE:  Console usage\nDETAIL:\n        SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION\n        SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM\n        SHOW DNS_HOSTS|DNS_ZONES\n        SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS\n        SET key = arg\n        RELOAD\n        PAUSE [<db>]\n        RESUME [<db>]\n        DISABLE <db>\n        ENABLE <db>\n        RECONNECT [<db>]\n        KILL <db>\n        SUSPEND\n        SHUTDOWN\n<\/code><\/pre>\n
    4.5 \u91cd\u8f7d\u914d\u7f6e\u6587\u4ef6<\/h5>\n
    \u5982\u679c\u4fee\u6539\u4e86 pgbouncer.ini \u6587\u4ef6\uff0c\u4f7f\u7528reload\u547d\u4ee4\u91cd\u8f7d\u914d\u7f6e\u6587\u4ef6\u3002
    \npgbouncer=# RELOAD;<\/p>\n
    \u4e94\u3001\u66f4\u591a\u8d44\u6599<\/h4>\n
    \u66f4\u591a\u7684\u53c2\u6570\u4ecb\u7ecd\uff0c\u66f4\u8be6\u7ec6\u7684\u4f7f\u7528\u65b9\u6cd5\uff0c\u53c2\u8003\u5b98\u65b9\u6587\u6863\uff1a
    \n    https:\/\/www.pgbouncer.org\/<\/a>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
    \u4e00\u3001PgBouncer\u7b80\u4ecb PgBouncer\u662f\u4e3aPostgreSQL\u6570\u636e\u5e93\u63d0\u4f9b\u7684\u4e00\u4e2a\u8f7b\u91cf\u7ea7\u8fde\u63a5\u6c60\u5de5\u5177\u3002Pos …<\/p>\n
      PgBouncer \u4f7f\u7528\u65b9\u6cd5<\/span> \u67e5\u770b\u5168\u6587 »<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[134,88],"tags":[],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"mailpoet_newsletter_max":false,"woocommerce_thumbnail":false,"woocommerce_single":false,"woocommerce_gallery_thumbnail":false,"shop_catalog":false,"shop_single":false,"shop_thumbnail":false},"uagb_author_info":{"display_name":"weijing","author_link":"http:\/\/www.weijing.co\/?author=4"},"uagb_comment_info":0,"uagb_excerpt":"\u4e00\u3001PgBouncer\u7b80\u4ecb PgBouncer\u662f\u4e3aPostgreSQL\u6570\u636e\u5e93\u63d0\u4f9b\u7684\u4e00\u4e2a\u8f7b\u91cf\u7ea7\u8fde\u63a5\u6c60\u5de5\u5177\u3002Pos…","_links":{"self":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1967"}],"collection":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1967"}],"version-history":[{"count":0,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1967\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1967"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1967"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1967"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}