{"id":293,"date":"2025-02-19T16:11:08","date_gmt":"2025-02-19T08:11:08","guid":{"rendered":"http:\/\/www.bigqq.com\/?p=293"},"modified":"2025-02-19T16:11:08","modified_gmt":"2025-02-19T08:11:08","slug":"pg%e8%b7%a8%e7%89%88%e6%9c%ac%e8%b7%a8%e7%b3%bb%e7%bb%9f-%e9%80%bb%e8%be%91%e5%a4%87%e4%bb%bd%e8%bf%98%e5%8e%9f","status":"publish","type":"post","link":"http:\/\/www.bigqq.com\/?p=293","title":{"rendered":"PG\u8de8\u7248\u672c\u8de8\u7cfb\u7edf \u903b\u8f91\u5907\u4efd\u8fd8\u539f"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>\u5c06\u6570\u636e\u5e93\u8bbe\u7f6e\u53ea\u8bfb\nvi postgresql.conf \ndefault_transaction_read_only=on\n \n&amp;&amp;\u6216\u8005\u4f7f\u7528\u547d\u4ee4\n \nALTER SYSTEM SET default_transaction_read_only = 'on';\n\u91cd\u542f\u751f\u6548\n\n\n\n\u6267\u884c\u4ee5\u4e0b\u547d\u4ee4\u6765\u521b\u5efa\u6587\u4ef6\u5b58\u50a8\u5e93\u914d\u7f6e\n sudo sh -c 'echo \"deb http:\/\/apt.postgresql.org\/pub\/repos\/apt $(lsb_release -cs)-pgdg main\" > \/etc\/apt\/sources.list.d\/pgdg.list'\n\u5bfc\u5165\u5b58\u50a8\u5e93\u7b7e\u540d\u5bc6\u94a5\n wget --quiet -O - https:\/\/www.postgresql.org\/media\/keys\/ACCC4CF8.asc | sudo apt-key add -\n\u66f4\u65b0\u8f6f\u4ef6\u5305\u5217\u8868\n sudo apt-get update\n\u5b89\u88c5PostgreSQL\n\u5b89\u88c5\u7279\u5b9a\u7248\u672c:sudo apt-get install postgresql-17\n\n\u5b89\u88c5\u5b8c\u67e5\u770b\u7248\u672cpsql --version\n\u505c\u6b62\u670d\u52a1\uff1asystemctl stop postgresql\n\n\u5230\u914d\u7f6e\u76ee\u5f55\uff1acd \/etc\/postgresql\/17\/main\n\u4fee\u6539\u914d\u7f6e\u6587\u4ef6\uff1avim pg_hba.conf \u589e\u52a0\nhost all all 0.0.0.0\/0 md5\nhost    replication     all            192.168.xxx.1\/24        md5\n\n\n=================pgbackrest\u5b89\u88c5 \u7701\u7565==================\n\n\u4fee\u6539\u914d\u7f6e\u6587\u4ef6\uff1avim postgresql.conf\nlisten_addresses = '*'\nmax_connections = 1000\nshared_buffers = 2GB\nwal_level = replica\nsynchronous_commit = local\nmax_wal_size = 10GB\nmin_wal_size = 100MB\narchive_mode = on\narchive_command = 'pgbackrest --config=\/etc\/pgbackrest.conf --stanza=MES2_DB archive-push %p'\t\t# command to use to archive a logfile segment\narchive_timeout = 600\nmax_wal_senders = 10\nwal_keep_size = 800\nmax_slot_wal_keep_size = 8000\nmax_replication_slots = 10\nhot_standby = on\nmax_standby_streaming_delay = 30s\nwal_receiver_status_interval = 10s\nhot_standby_feedback = off\nlog_timezone = 'Asia\/Shanghai'\ntimezone = 'Asia\/Shanghai'\nshared_preload_libraries = 'pg_stat_statements'\n\n\u4fee\u6539\u5bc6\u7801\nsudo -u postgres psql postgres -p 5432\nALTER USER postgres WITH PASSWORD 'xxx2021';\n\n\n\n\u65b0\u6570\u636e\u5e93\u521b\u5efa \u6570\u636e\u5e93 \u548c \u89d2\u8272\nCREATE ROLE s WITH \n\tSUPERUSER\n\tNOCREATEDB\n\tNOCREATEROLE\n\tINHERIT\n\tLOGIN\n\tNOREPLICATION\n\tNOBYPASSRLS\n\tPASSWORD 'xxx2021'\n\tCONNECTION LIMIT -1;\n\t\n-- DROP ROLE mes2;\n\nCREATE ROLE s2 WITH \n\tSUPERUSER\n\tNOCREATEDB\n\tNOCREATEROLE\n\tINHERIT\n\tLOGIN\n\tNOREPLICATION\n\tNOBYPASSRLS\n\tPASSWORD 'xxx2023'\n\tCONNECTION LIMIT -1;\n\t\n-- DROP ROLE mes2dc;\n\nCREATE ROLE s2dc WITH \n\tSUPERUSER\n\tNOCREATEDB\n\tNOCREATEROLE\n\tNOINHERIT\n\tLOGIN\n\tNOREPLICATION\n\tNOBYPASSRLS\n\tPASSWORD 'xxx2024'\n\tCONNECTION LIMIT -1;\n\t\n-- DROP ROLE mes2read;\n\nCREATE ROLE s2read WITH \n\tNOSUPERUSER\n\tNOCREATEDB\n\tNOCREATEROLE\n\tINHERIT\n\tLOGIN\n\tNOREPLICATION\n\tNOBYPASSRLS\n\tPASSWORD 'xxx2024'\n\tCONNECTION LIMIT -1;\n\n--\u53ea\u8bfb\u8d26\u53f7\u8bbe\u7f6e\nalter user s2read set default_transaction_read_only = on;\n\ngrant connect on database postgres to s2read;\n\ngrant usage on schema public to s2read;\ngrant select on all sequences in schema public to s2read;\ngrant select on all tables in schema public to s2read;\n\nalter default privileges in schema public grant select on tables to s2read;\n\n--\u4e13\u7528\u8d26\u53f7\u8bbe\u7f6e\nALTER DATABASE \"MES2_dd\" OWNER TO mes2dc;\n\nGRANT ALL ON SCHEMA public TO mes2dc;\n\nGrant all on all tables in schema public to mes2dc;\n\nALTER table dc_sn_xxx OWNER TO mes2dc;\n\nALTER table pc_equip_xxxx OWNER TO mes2dc;\n\n\u5907\u4efd\u8fd8\u539f\nsudo pg_dump -h 192.168.xx.xxx -U postgres -d MES2_DB -p 5432 -F c -f \/pgdump\/mes.dump -v\n\nsudo pg_dump -h 192.168.xx.xx -U postgres -d MES2_DB -p 5432 -F d -f \/pgdump\/mes -j 10 -v\n\npostgres\u7528\u6237 pg_restore -d MES2_DB -j 10 \/pgdump\/mes -v\n\n\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","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":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}},"footnotes":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/www.bigqq.com\/index.php?rest_route=\/wp\/v2\/posts\/293"}],"collection":[{"href":"http:\/\/www.bigqq.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.bigqq.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=293"}],"version-history":[{"count":1,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=\/wp\/v2\/posts\/293\/revisions"}],"predecessor-version":[{"id":294,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=\/wp\/v2\/posts\/293\/revisions\/294"}],"wp:attachment":[{"href":"http:\/\/www.bigqq.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=293"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=293"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=293"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}