{"id":1981,"date":"2020-07-29T21:52:05","date_gmt":"2020-07-29T13:52:05","guid":{"rendered":"http:\/\/39.97.231.10\/?p=1981"},"modified":"2020-07-29T21:52:05","modified_gmt":"2020-07-29T13:52:05","slug":"postgresql%e6%90%ad%e5%bb%ba%e4%b8%bb%e5%a4%87%e6%b5%81%e5%a4%8d%e5%88%b6","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=1981","title":{"rendered":"PostgreSQL\u642d\u5efa\u4e3b\u5907\u6d41\u590d\u5236"},"content":{"rendered":"<div class=\\\"blog_content\\\">\n<div class=\\\"blog_detail\\\">\n<div>\n\u4e3b\u5907\u590d\u5236\u67b6\u6784\u662f\u6570\u636e\u5e93\u4f7f\u7528\u6700\u5e7f\u6cdb\u7684\u4e00\u79cd\u67b6\u6784\uff0c\u5f53\u4e3b\u5e93\u53d1\u751f\u6545\u969c\u65f6\u53ef\u4ee5\u5c06\u5907\u5e93\u63d0\u5347\u4e3a\u4e3b\uff0c\u5feb\u901f\u6062\u590d\u4e1a\u52a1\u3002\u4e3b\u5907\u67b6\u6784\u53e6\u5916\u4e00\u4e2a\u529f\u80fd\u662f\u63d0\u4f9b\u8d1f\u8f7d\u5747\u8861\uff0c\u8bfb\u4e1a\u52a1\u53ef\u4ee5\u8def\u7531\u5230\u5907\u5e93\uff0c\u51cf\u5c11\u4e3b\u5e93\u7684\u8d1f\u8f7d\u3002\u672c\u6587\u4e3b\u8981\u4ecb\u7ecdPostgreSQL\u4e3b\u5907\u6d41\u590d\u5236\u7684\u642d\u5efa\u65b9\u6cd5\u3002<\/p>\n<ul>\n<li>PostgreSQL\u7248\u672c\uff1a11.5<\/li>\n<li>2\u53f0\u673a\u5668\uff0c\u642d\u5efaPG\u4e3b\u5907\u6d41\u590d\u5236\n<ul>\n<li>\u4e3b\u5e93\uff1a192.168.56.101<\/li>\n<li>\u5907\u5e93\uff1a192.168.56.102<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h5>\u4e00\u3001\u73af\u5883\u51c6\u786e\uff08\u4e3b\u5907\u5e93\u90fd\u8981\u6267\u884c\uff09<\/h5>\n<p>\u521b\u5efapg\u7528\u6237<br \/>\nuseradd pg<br \/>\n\u521b\u5efaPG\u6570\u636e\u76ee\u5f55<br \/>\nmkdir -p \/data\/pg\/<br \/>\nchown -R pg:pg \/data\/pg<br \/>\n\u5207\u6362\u5230pg\u7528\u6237<br \/>\nsu &#8211; pg<\/p>\n<h5>\u4e8c\u3001\u521b\u5efaPG\u4e3b\u5e93\uff08\u4e3b\u5e93\u6267\u884c\uff09<\/h5>\n<p>\u521d\u59cb\u5316\u4e3b\u5e93\uff1a<br \/>\ninitdb -D \/data\/pg<br \/>\n\u4fee\u6539\u4e3b\u5e93\u914d\u7f6e\u6587\u4ef6\uff1a<br \/>\nvim \/data\/pg\/postgresql.conf<\/p>\n<pre><code>listen_addresses = \\'*\\'\nport = 5432\nmax_wal_senders = 10\nwal_level = replica\n<\/code><\/pre>\n<p>\u4fee\u6539\u4e3b\u5e93\u914d\u7f6e\u6587\u4ef6\uff0c\u6dfb\u52a0\u914d\u7f6e\u9879\uff1a<br \/>\nvim \/data\/pg\/pg_hba.conf<\/p>\n<pre><code>host replication all 0.0.0.0\/0 md5\n<\/code><\/pre>\n<p>\u542f\u52a8\u4e3b\u5e93\uff1a<br \/>\npg_ctl -D \/data\/pg start<br \/>\n\u521b\u5efa\u590d\u5236\u8d26\u53f7\uff1a<br \/>\nCREATE USER repl replication LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD \\&#8217;123456\\&#8217;;<\/p>\n<h5>\u4e09\u3001\u521b\u5efa\u4e3b\u5e93\u7684\u57fa\u7840\u5907\u4efd\uff08\u5907\u5e93\u6267\u884c\uff09<\/h5>\n<p>\u521b\u5efa\u57fa\u7840\u5907\u4efd\u5230\u672c\u5730\uff1a<br \/>\npg_basebackup -h 192.168.56.101 -U repl -W -p 5432 -D \/data\/pg -l backup_label<br \/>\n-W\u53c2\u6570\u5728\u7ec8\u7aef\u63d0\u793a\u7b26\u4e0b\u8f93\u5165\u5bc6\u7801\u3002<br \/>\n\u8bbe\u7f6e\u5907\u5e93\u914d\u7f6e\u6587\u4ef6\uff1a<br \/>\nvim \/data\/pg\/postgresql.conf<\/p>\n<pre><code>port = 5432\nhot_standby = on\n<\/code><\/pre>\n<p>vim \/data\/pg\/recovery.conf<\/p>\n<pre><code>standby_mode = \\'on\\'\nprimary_conninfo = \\'user=repl host=192.168.56.101 password=123456 port=5432 sslmode=disable sslcompression=0 target_session_attrs=any\\'\n<\/code><\/pre>\n<p>\u542f\u52a8\u5907\u5e93\uff1a<br \/>\npg_ctl -D \/data\/pg start<\/p>\n<h5>\u56db\u3001\u6d4b\u8bd5\u6d41\u590d\u5236<\/h5>\n<p>\u8fde\u63a5\u5230\u4e3b\u5e93(192.168.56.101)\uff0c\u521b\u5efa\u8868\uff0c\u5199\u5165\u6570\u636e\uff1a<br \/>\npsql postgres<br \/>\npostgres=# create database db;<br \/>\npostgres=# \\\\l<br \/>\npostgres=# \\\\c db<br \/>\ndb=# create table tb(id int);<br \/>\ndb=# insert into tb select 1;<br \/>\ndb=# exit<br \/>\n\u8fde\u63a5\u5230\u5907\u5e93(192.168.56.102)\uff0c\u67e5\u8be2\u6570\u636e\uff1a<br \/>\npsql postgres<br \/>\npostgres=# \\\\l<br \/>\npostgres=# \\\\c db<br \/>\ndb=# select * from tb;\n<\/p><\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>\u4e3b\u5907\u590d\u5236\u67b6\u6784\u662f\u6570\u636e\u5e93\u4f7f\u7528\u6700\u5e7f\u6cdb\u7684\u4e00\u79cd\u67b6\u6784\uff0c\u5f53\u4e3b\u5e93\u53d1\u751f\u6545\u969c\u65f6\u53ef\u4ee5\u5c06\u5907\u5e93\u63d0\u5347\u4e3a\u4e3b\uff0c\u5feb\u901f\u6062\u590d\u4e1a\u52a1\u3002\u4e3b\u5907\u67b6\u6784\u53e6\u5916\u4e00\u4e2a\u529f &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"http:\/\/www.weijing.co\/?p=1981\"> <span class=\"screen-reader-text\">PostgreSQL\u642d\u5efa\u4e3b\u5907\u6d41\u590d\u5236<\/span> \u67e5\u770b\u5168\u6587 &raquo;<\/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":1,"uagb_excerpt":"\u4e3b\u5907\u590d\u5236\u67b6\u6784\u662f\u6570\u636e\u5e93\u4f7f\u7528\u6700\u5e7f\u6cdb\u7684\u4e00\u79cd\u67b6\u6784\uff0c\u5f53\u4e3b\u5e93\u53d1\u751f\u6545\u969c\u65f6\u53ef\u4ee5\u5c06\u5907\u5e93\u63d0\u5347\u4e3a\u4e3b\uff0c\u5feb\u901f\u6062\u590d\u4e1a\u52a1\u3002\u4e3b\u5907\u67b6\u6784\u53e6\u5916\u4e00\u4e2a\u529f&hellip;","_links":{"self":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1981"}],"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=1981"}],"version-history":[{"count":0,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1981\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1981"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1981"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1981"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}