{"id":315,"date":"2025-06-20T13:10:09","date_gmt":"2025-06-20T05:10:09","guid":{"rendered":"http:\/\/www.bigqq.com\/?p=315"},"modified":"2025-06-20T13:49:55","modified_gmt":"2025-06-20T05:49:55","slug":"pg%e6%95%b0%e6%8d%ae%e5%ba%93%e6%b5%81%e5%90%8c%e6%ad%a5%e6%81%a2%e5%a4%8d","status":"publish","type":"post","link":"http:\/\/www.bigqq.com\/?p=315","title":{"rendered":"PG\u6570\u636e\u5e93\u6d41\u540c\u6b65\u6062\u590d"},"content":{"rendered":"\n<p>1\uff0c\u590d\u5236\u7f3a\u5931\u7684WAL\u6587\u4ef6\u6216\u538b\u7f29\u6587\u4ef6\u5230\u4e00\u4e2a\u65b0\u5efa\u6307\u5b9a\u76ee\u5f55\uff0c\u5982\u679c\u662fpgbackrest\u538b\u7f29\u7684\u6587\u4ef6\uff0c\u4e00\u822c\u662f\u5728\/var\/lib\/pgbackrest\/archive\/MES2_DB\/17-1\/<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>cd \/var\/lib\/pgbackrest\/archive\/MES2_DB\/17-1\nls -l \/\/\u67e5\u770b\u76ee\u5f55 \u5e76\u6309\u7167\u6587\u4ef6\u5939\u65f6\u95f4\u627e\u5230\u5bf9\u5e94\u7f3a\u5931\u7684WAL\u8303\u56f4\uff0c\u5982\u6d89\u53ca\u591a\u4e2a\u6587\u4ef6\u5939\uff0c\u6bcf\u4e2a\u6267\u884ccp\ncp * \/pgdump\/backwal\/<\/code><\/pre>\n\n\n\n<p>2\uff0c\u5220\u9664\u5df2\u540c\u6b65\u8fc7\u7684WAL\u6587\u4ef6\uff0c\u5982\u679c\u662f\u538b\u7f29\u6587\u4ef6\uff0c\u9700\u8981\u5bf9\u6587\u4ef6\u8fdb\u884c\u5904\u7406<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>rm 00000001000002980000000*\ngunzip *.gz \/\/\u89e3\u538b\u6240\u6709\uff0c\u89e3\u538b\u540e\u9ed8\u8ba4\u5220\u9664\u539f\u538b\u7f29\u6587\u4ef6<\/code><\/pre>\n\n\n\n<p>3\uff0c\u5982\u662f\u89e3\u538b\u6587\u4ef6\uff0c\u5219\u6267\u884c\u91cd\u547d\u540d\uff0c\u53ef\u4ee5\u53e6\u5916\u5efa\u4e00\u4e2a\u76ee\u5f55\uff0c\u4f7f\u7528.sh\u6279\u91cf\u6267\u884cmv\u547d\u4ee4\u5e76\u91cd\u547d\u540d<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo chmod 777 xxx.sh \/\/\u9700\u8981\u5148\u7ed9\u6267\u884c\u6743\u9650\n.\/xxx.sh \/\/\u6267\u884csh\n\n============.sh\u5185\u5bb9==============\nfor file in backwal\/*; do\n  mv \"$file\" \"cpwal\/${file%%-*}\"\ndone\n\n\/\/${file%%-*} \u8868\u793a\u5c06\u539f\u6587\u4ef6\u540d\u4e2d-\u5b57\u7b26\u4e4b\u540e\u7684\u66ff\u6362\u6389\uff08\u5373\u622a\u53d6-\u524d\u9762\uff09<\/code><\/pre>\n\n\n\n<p>4\uff0c\u5728\u5904\u7406\u540e\u7684\u6587\u4ef6\u5939\u4e2d\uff0c\u6267\u884cscp\u8fdc\u7a0b\u590d\u5236\u547d\u4ee4<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code> scp \/pgdump\/cpwal\/backwal\/* postgres@192.168.xxx.xx:\/var\/lib\/postgresql\/17\/main\/pg_wal\/<\/code><\/pre>\n\n\n\n<p>5\uff0c\u68c0\u67e5\u76ee\u6807\u5907\u673a \/var\/log\/postgresql \u4e0b\u7684\u65e5\u5fd7 \u6216 \u5728\u4e3b\u6570\u636e\u5e93\u67e5\u770b<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select * from pg_stat_replication\n\nSELECT  pg_wal_lsn_diff(A .c1, replay_lsn) \/(1024 * 1024) AS slave_latency_MB\n        FROM\n        \tpg_stat_replication,\n        \tpg_current_wal_lsn() AS A(c1)\n        ORDER BY\n        \tslave_latency_MB\n        LIMIT 1;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>1\uff0c\u590d\u5236\u7f3a\u5931\u7684WAL\u6587\u4ef6\u6216\u538b\u7f29\u6587\u4ef6\u5230\u4e00\u4e2a [&hellip;]<\/p>\n","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\/315"}],"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=315"}],"version-history":[{"count":3,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=\/wp\/v2\/posts\/315\/revisions"}],"predecessor-version":[{"id":318,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=\/wp\/v2\/posts\/315\/revisions\/318"}],"wp:attachment":[{"href":"http:\/\/www.bigqq.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=315"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=315"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.bigqq.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=315"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}