{"id":41650,"date":"2022-12-21T14:16:24","date_gmt":"2023-06-21T01:55:07","guid":{"rendered":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0\/"},"modified":"2024-04-29T06:20:26","modified_gmt":"2024-04-28T22:20:26","slug":"postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0\/","title":{"rendered":"PostgreSQL\u6570\u636e\u5e93\u7efc\u8ff0"},"content":{"rendered":"

\u5b66\u4e60PostgreSQL<\/h1>\n

\u5173\u4e8ePostgreSQL\u7684\u8bbe\u8ba1\u3001\u6784\u5efa\u548c\u6027\u80fd\u8c03\u4f18\u7684\u7b14\u8bb0\u3002<\/p>\n

\u5b89\u88c5\u548c\u521d\u59cb\u8bbe\u7f6e<\/h1>\n

1. \u67b6\u8bbe yum \u4ed3\u5e93\u5e76\u5b89\u88c5 PostgreSQL\u3002<\/p>\n

yum<\/span> install<\/span> https<\/span>:\/<\/span>\/<\/span>download<\/span>.<\/span>postgresql<\/span>.<\/span>org<\/span>\/<\/span>pub<\/span>\/<\/span>repos<\/span>\/<\/span>yum<\/span>\/<\/span>11<\/span>\/<\/span>redhat<\/span>\/<\/span>rhel<\/span>-<\/span>7<\/span>-<\/span>x86_64<\/span>\/<\/span>pgdg<\/span>-<\/span>redhat<\/span>-<\/span>repo<\/span>-<\/span>latest<\/span>.<\/span>noarch<\/span>.<\/span>rpm<\/span>\r\nyum<\/span> -<\/span>y<\/span> install<\/span> postgresql11<\/span>-<\/span>server<\/span>\r\n\r\n##\u30d1\u30c3\u30b1\u30fc\u30b8\u3092\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb\u3059\u308b\u3068\u4e0b\u8a18postgres\u30e6\u30fc\u30b6\u3084postgres\u30b0\u30eb\u30fc\u30d7\u304c\u4f5c\u6210\u3055\u308c\u308b\u3002<\/span>\r\n[<\/span>root<\/span>@postgredb<\/span> ~<\/span>]<\/span># id postgres<\/span>\r\nuid<\/span>=<\/span>26<\/span>(<\/span>postgres<\/span>)<\/span> gid<\/span>=<\/span>26<\/span>(<\/span>postgres<\/span>)<\/span> groups<\/span>=<\/span>26<\/span>(<\/span>postgres<\/span>)<\/span>\r\n<\/code><\/pre>\n
\u6784\u5efa\u6570\u636e\u5e93\u96c6\u7fa4<\/p>\n
#\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u30af\u30e9\u30b9\u30bf\u3092\u30c7\u30d5\u30a9\u30eb\u30c8\u306e\/var\/lib\/pgsql\/11\/data\u304b\u3089\u5909\u66f4\u3059\u308b<\/span>\r\ncp<\/span> -<\/span>p<\/span> \/usr\/<\/span>lib<\/span>\/<\/span>systemd<\/span>\/<\/span>system<\/span>\/<\/span>postgresql<\/span>-<\/span>11<\/span>.<\/span>service<\/span> \/etc\/s<\/span>ystemd<\/span>\/<\/span>system<\/span>\/<\/span>\r\nsed<\/span> -<\/span>i<\/span> 's:\/var\/lib\/pgsql\/11\/data:\/postgres\/data:g'<\/span> \/<\/span>etc<\/span>\/<\/span>systemd<\/span>\/<\/span>system<\/span>\/<\/span>postgresql<\/span>-<\/span>11<\/span>.<\/span>service<\/span>\r\nsystemctl<\/span> daemon<\/span>-<\/span>reload<\/span>\r\n\r\nsed<\/span> -<\/span>i<\/span> 's:\/var\/lib\/pgsql\/11\/data:\/postgres\/data:g'<\/span> \/<\/span>var<\/span>\/<\/span>lib<\/span>\/<\/span>pgsql<\/span>\/<\/span>.<\/span>bash_profile<\/span> \r\necho<\/span> 'export PATH=\/usr\/pgsql-11\/bin:${PATH}'<\/span> >><\/span> \/var\/<\/span>lib<\/span>\/<\/span>pgsql<\/span>\/<\/span>.<\/span>bash_profile<\/span> \r\n\r\n\r\n#\u521d\u671f\u30bb\u30c3\u30c8\u30a2\u30c3\u30d7(=\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u30af\u30e9\u30b9\u30bf\u3092\u6240\u5b9a\u306e\u5834\u6240\u306b\u4f5c\u308b)<\/span>\r\nPGSETUP_INITDB_OPTIONS<\/span>=<\/span>\"-E UTF8 --locale=C\"<\/span> \/<\/span>usr<\/span>\/<\/span>pgsql<\/span>-<\/span>11<\/span>\/<\/span>bin<\/span>\/<\/span>postgresql<\/span>-<\/span>11<\/span>-<\/span>setup<\/span> initdb<\/span>\r\nsystemctl<\/span> enable<\/span> postgresql<\/span>-<\/span>11<\/span>.<\/span>service<\/span> --<\/span>now<\/span>\r\n<\/code><\/pre>\n
3. \u4f7f\u7528\u9ed8\u8ba4\u7528\u6237\u8fdb\u884c\u901a\u8baf\u786e\u8ba4\u5e76\u91cd\u65b0\u8bbe\u7f6e\u5bc6\u7801<\/p>\n
su<\/span> -<\/span> postgres<\/span>\r\n-<\/span>bash<\/span>-<\/span>4.2<\/span>$<\/span> psql<\/span> -<\/span>U<\/span> postgres<\/span>\r\npsql<\/span> (<\/span>11.4<\/span>)<\/span>\r\nType<\/span> \"help\"<\/span> for<\/span> help<\/span>.<\/span>\r\n\r\npostgres<\/span>=<\/span># \\l<\/span>\r\n                             List<\/span> of<\/span> databases<\/span>\r\n   Name<\/span>    |<\/span>  Owner<\/span>   |<\/span> Encoding<\/span> |<\/span> Collate<\/span> |<\/span> Ctype<\/span> |<\/span>   Access<\/span> privileges<\/span>   \r\n-----------+----------+----------+---------+-------+-----------------------<\/span>\r\n postgres<\/span>  |<\/span> postgres<\/span> |<\/span> UTF8<\/span>     |<\/span> C<\/span>       |<\/span> C<\/span>     |<\/span> \r\n template0<\/span> |<\/span> postgres<\/span> |<\/span> UTF8<\/span>     |<\/span> C<\/span>       |<\/span> C<\/span>     |<\/span> =<\/span>c<\/span>\/<\/span>postgres<\/span>          +<\/span>\r\n           |<\/span>          |<\/span>          |<\/span>         |<\/span>       |<\/span> postgres<\/span>=<\/span>CTc<\/span>\/<\/span>postgres<\/span>\r\n template1<\/span> |<\/span> postgres<\/span> |<\/span> UTF8<\/span>     |<\/span> C<\/span>       |<\/span> C<\/span>     |<\/span> =<\/span>c<\/span>\/<\/span>postgres<\/span>          +<\/span>\r\n           |<\/span>          |<\/span>          |<\/span>         |<\/span>       |<\/span> postgres<\/span>=<\/span>CTc<\/span>\/<\/span>postgres<\/span>\r\n(<\/span>3<\/span> rows<\/span>)<\/span>\r\n\r\npostgres<\/span>=<\/span># \\q<\/span>\r\n\r\npsql<\/span> -<\/span>c<\/span> \"alter user postgres with password 'P#ssw0rd'\"<\/span> \r\n<\/code><\/pre>\n
\u521b\u5efa\u7528\u6237\u548c\u6570\u636e\u5e93<\/p>\n
# DB\u30e6\u30fc\u30b6\u30fc [cent] \u3092\u767b\u9332<\/span>\r\ncreateuser<\/span> cent<\/span>\r\n\r\n# \u30c6\u30b9\u30c8\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u4f5c\u6210 (\u30aa\u30fc\u30ca\u30fc\u306f\u4e0a\u8a18\u30e6\u30fc\u30b6\u30fc)<\/span>\r\ncreatedb<\/span> testdb<\/span> -<\/span>O<\/span> cent<\/span>\r\n\r\n#\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u63a5\u7d9a<\/span>\r\npsql<\/span> testdb<\/span> -<\/span>U<\/span> cent<\/span>\r\n<\/code><\/pre>\n
\u53c2\u6570\u8c03\u6574\uff08postgresql.conf\uff09<\/h1>\n
\u5728Oracle\u4e2d\uff0c\u6709\u4e00\u4e2a\u79f0\u4e3a\u201c\u521d\u59cb\u5316\u53c2\u6570\u201d\u7684\u6982\u5ff5\uff0c\u800c\u5728Postgre\u4e2d\u4e5f\u5b58\u5728\u7c7b\u4f3c\u7684\u8bbe\u7f6e\u53c2\u6570\u3002
\n\u57fa\u672c\u4e0a\uff0c\u8fd9\u4e9b\u53c2\u6570\u662f\u5728postgresql.conf\u6587\u4ef6\u6216\u5305\u542b\u5728postgresql.conf\u6587\u4ef6\u4e2d\u7684\u6587\u4ef6\u4e2d\u8fdb\u884c\u6301\u4e45\u7ba1\u7406\u7684\u3002<\/p>\n
select<\/span> name<\/span>,<\/span>setting<\/span>,<\/span>unit<\/span>,<\/span>context<\/span> from<\/span> pg_settings<\/span>;<\/span>\r\n<\/code><\/pre>\n
\u8fd8\u53ef\u4ee5\u901a\u8fc7\u53c2\u6570\u5c06\u8bbe\u7f6e\u548c\u5e94\u7528\u7684\u65f6\u673a\u5206\u5f00\u7ba1\u7406\u3002<\/p>\n
\n
\u30bf\u30a4\u30df\u30f3\u30b0pg_settings\u30b7\u30b9\u30c6\u30e0\u30d3\u30e5\u30fc\u306bcontext\u5024\u8aac\u660eSET\u6587\u5b9f\u884c\u6642user\u307e\u305f\u306fsuperuser\u767a\u884c\u3057\u305f\u30bb\u30c3\u30b7\u30e7\u30f3\u5185\u3067\u5373\u6642\u9069\u7528\u3002SET LOCAL\u306e\u5834\u5408\u306f\u767a\u884c\u3057\u305f\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u5185\u306e\u307f\u3002SIGHUP\u30b7\u30b0\u30ca\u30eb\u767a\u884c\u6642sighupPostgreSQL\u30b5\u30fc\u30d0\u30d7\u30ed\u30bb\u30b9\u304cSIGHUP\u30b7\u30b0\u30ca\u30eb\u3092\u53d7\u3051\u53d6\u3063\u305f\u6642PostgreSQL\u8d77\u52d5\u6642postmasterPostgreSQL\u8d77\u52d5\u6642\u306e\u307f<\/div>\n<\/div>\n
*\u53ea\u9700\u8981\u4e00\u79cd\u9009\u9879\uff0c\u8bf7\u5c06\u4ee5\u4e0b\u5185\u5bb9\u4ee5\u4e2d\u6587\u672c\u5730\u8bed\u8a00\u8fdb\u884c\u91ca\u4e49\uff1a
\n\u203b\u5728PostgreSQL\u4e2d\uff0cSIGHUP\u4fe1\u53f7\u6216\u91cd\u65b0\u542f\u52a8\u547d\u4ee4\uff08\u800c\u4e0d\u662fsystemd\u547d\u4ee4\uff09\u7684\u7528\u6cd5\u3002<\/p>\n
\uff08\u53f9\u6c14\uff09
\npg_ctl reload -D PD_DATA\u76ee\u5f55\u540d
\n\uff08\u91cd\u65b0\u542f\u52a8\uff09
\npg_ctl restart -D PD_DATA\u76ee\u5f55\u540d<\/p><\/blockquote>\n
\u6839\u636e\u53c2\u6570\u7684\u4e0d\u540c\uff0c\u53ef\u80fd\u6709\u5404\u79cd\u9650\u5236\uff0c\u5982\u5141\u8bb8\u52a8\u6001\u66f4\u6539\uff0c\u6216\u9700\u8981\u91cd\u542f\u624d\u80fd\u8fdb\u884c\u66f4\u6539\u7b49\u3002\u4ee5\u4e0b\u662f\u7528SQL\u5224\u65ad\u8fd9\u4e9b\u9650\u5236\u7684\u65b9\u6cd5\u3002<\/p>\n
#1.\u52d5\u7684\u5909\u66f4\u53ef\u80fd\u306a\u30d1\u30e9\u30e1\u30fc\u30bf(SET\u6587\u3067\u540c\u4e00\u30bb\u30c3\u30b7\u30e7\u30f3\u5185\u3067\u306e\u5909\u66f4\u3082\u53ef)<\/span>\r\nselect<\/span> name<\/span>,<\/span>setting<\/span>,<\/span>unit<\/span>,<\/span>context<\/span> from<\/span> pg_settings<\/span> where<\/span> context<\/span> in<\/span> (<\/span>'user'<\/span>,<\/span>'superuser'<\/span>);<\/span>\r\n\r\n#2.SIGHUP\u30b7\u30b0\u30ca\u30eb\u53d7\u4fe1\u6642\u306b\u53cd\u6620<\/span>\r\nselect<\/span> name<\/span>,<\/span>setting<\/span>,<\/span>unit<\/span>,<\/span>context<\/span> from<\/span> pg_settings<\/span> where<\/span> context<\/span>=<\/span>'sighup'<\/span>;<\/span>\r\n\r\n#3.Posgre\u518d\u8d77\u52d5\u6642\u306e\u307f\u53cd\u6620<\/span>\r\nselect<\/span> name<\/span>,<\/span>setting<\/span>,<\/span>unit<\/span>,<\/span>context<\/span> from<\/span> pg_settings<\/span> where<\/span> context<\/span>=<\/span>'postmaster'<\/span>;<\/span>\r\n\r\n<\/code><\/pre>\n
\u6b64\u5916\uff0c\u5728\u5373\u65f6\u53cd\u6620\u4e4b\u5916\uff0c\u8fd8\u53ef\u4ee5\u901a\u8fc7\u5728psql\u63d0\u793a\u7b26\u4e0a\u4f7f\u7528ALTER SYSTEM\u8bed\u53e5\u8fdb\u884c\u8bbe\u7f6e\u66f4\u6539\uff0c\u4f46\u8fd9\u4e9b\u66f4\u6539\u5c06\u5728\u4e0b\u6b21\u91cd\u65b0\u542f\u52a8\u65f6\u624d\u751f\u6548\u3002\u6267\u884cALTER SYSTEM\u8bed\u53e5\u540e\uff0c\u503c\u5c06\u88ab\u9644\u52a0\u5230postgresql.auto.conf\u6587\u4ef6\u4e2d\uff0c\u5e76\u8986\u76d6\u5e76\u53cd\u6620\u5728postgresql.conf\u6587\u4ef6\u4e2d\u3002\u4f7f\u7528\u6b64\u65b9\u6cd5\u53ef\u4ee5\u5728\u6267\u884cALTER SYSTEM\u8bed\u53e5\u65f6\u68c0\u67e5\u8bbe\u7f6e\u9519\u8bef\uff0c\u5e76\u4e14\u7531\u80fd\u591f\u8fdc\u7a0b\u767b\u5f55\u7684\u8d85\u7ea7\u7528\u6237\u53ef\u4ee5\u4ece\u8fdc\u7a0b\u4fee\u6539\u8bbe\u7f6e\uff0c\u8fd9\u662f\u4e00\u4e2a\u4f18\u70b9\u3002<\/p>\n
#\u30d1\u30e9\u30e1\u30fc\u30bf\u306e\u52d5\u7684\u5909\u66f4(superuser\u3067\u306a\u3044\u3068\u5b9f\u65bd\u4e0d\u53ef\u80fd)<\/span>\r\npostgres<\/span>=<\/span># alter system set shared_buffers=\"1024MB\";<\/span>\r\nALTER<\/span> SYSTEM<\/span>\r\n\r\n-<\/span>bash<\/span>-<\/span>4.2<\/span>$<\/span> cat<\/span> \/postgres\/<\/span>data<\/span>\/<\/span>postgresql<\/span>.<\/span>auto<\/span>.<\/span>conf<\/span>\r\n# Do not edit this file manually!<\/span>\r\n# It will be overwritten by the ALTER SYSTEM command.<\/span>\r\nshared_buffers<\/span> =<\/span> '1024MB'<\/span>\r\n\r\n#\u30d1\u30e9\u30e1\u30fc\u30bf\u306e\u8a73\u7d30\u60c5\u5831\u95b2\u89a7<\/span>\r\nselect<\/span> name<\/span>,<\/span>setting<\/span>,<\/span>unit<\/span> from<\/span> pg_settings<\/span>;<\/span>\r\n<\/code><\/pre>\n
\u5bf9\u4e8e\u5404\u4e2a\u53c2\u6570\u503c\u7684\u5ba1\u67e5\u548c\u91c7\u7528\u653f\u7b56\uff08postgresql.conf\uff09<\/p>\n
\u76d1\u542c\u5730\u5740
\n\u7ed1\u5b9a\u7aef\u53e3\u7684\u5730\u5740\u3002
\n\u5982\u679c\u5141\u8bb8\u8fdc\u7a0b\u8fde\u63a5\u6216\u8005\u5141\u8bb8\u8bbf\u95ee\uff0c\u5219\u586b\u5199\u76f8\u5e94\u7684IP\u5730\u5740\u3002
\n\u4f8b\u5982\uff09listen_addresses=”<\/p>\n
\u7aef\u53e3
\n\u7b49\u5f85\u63a5\u53d7\u8fde\u63a5\u7684\u7aef\u53e3\u53f7
\n\u4f8b\uff09\u7aef\u53e3=5432<\/p>\n
\u6700\u5927\u8fde\u63a5\u6570(max_connections)\u662f\u4e00\u4e2a\u8bbe\u7f6e\u9879\uff0c\u7528\u4e8e\u9650\u5236\u53ef\u4ee5\u4e0e\u5ba2\u6237\u7aef\u5efa\u7acb\u7684\u6700\u5927\u8fde\u63a5\u6570\u91cf\u3002\u8be5\u503c\u4e0d\u5305\u62ecPostgreSQL\u81ea\u8eab\u7684\u540e\u53f0\u8fdb\u7a0b\u3002\u4f8b\u5982\uff0c\u8bbe\u7f6emax_connection\u4e3a300\u3002<\/p>\n
shared_buffers\u662f\u7528\u6765\u786e\u5b9a\u5206\u914d\u7ed9PostgreSQL\u6570\u636e\u7f13\u5b58\u7684\u5185\u5b58\u91cf\u7684\u8bbe\u7f6e\u53c2\u6570\u3002
\n\u5982\u679c\u7cfb\u7edf\u6709\u8d85\u8fc71GB\u7684RAM\uff0cshared_buffers\u7684\u7406\u60f3\u521d\u59cb\u503c\u662f\u7cfb\u7edf\u5185\u5b58\u76841\/4\u4f5c\u4e3a\u63a8\u8350\u503c\u3002
\n\u5982\u679cSHMMAX\u6bd4\u8fd9\u4e2a\u503c\u5c0f\uff0c\u5c06\u5728PostgreSQL\u542f\u52a8\u65f6\u51fa\u73b0\u9519\u8bef\u3002
\n\u793a\u4f8b\uff1ashared_buffers=’1024MB’<\/p>\n
\u6709\u6548\u7f13\u5b58\u5927\u5c0f\uff0c\u6307\u7684\u662f\u7528\u4e8ePostgreSQL\u7684\u7f13\u51b2\u533a\uff0c\u5305\u62ec\u5185\u6838\u548cPostgreSQL\u5171\u4eab\u7f13\u51b2\u533a\u4ee5\u53ca\u53ef\u7528\u4f5c\u78c1\u76d8\u7f13\u5b58\u7684\u4f30\u8ba1\u5927\u5c0f\u3002\u8be5\u503c\u7531\u4f18\u5316\u5668\u4f7f\u7528\uff0c\u5e76\u4f5c\u4e3a\u4f30\u8ba1\u4f7f\u7528\u7d22\u5f15\u7684\u6210\u672c\u7684\u56e0\u7d20\u3002\u5982\u679c\u5c06\u503c\u8bbe\u7f6e\u5f97\u66f4\u9ad8\uff0c\u5c06\u66f4\u591a\u5730\u4f7f\u7528\u7d22\u5f15\u626b\u63cf\uff0c\u800c\u5982\u679c\u5c06\u5176\u8bbe\u7f6e\u5f97\u8f83\u5c0f\uff0c\u5219\u66f4\u591a\u5730\u4f7f\u7528\u987a\u5e8f\u626b\u63cf\u3002\u5982\u679c\u5e0c\u671b\u66f4\u591a\u5730\u4f7f\u7528\u7d22\u5f15\u626b\u63cf\uff0c\u5219\u63a8\u8350\u5c06\u6b64\u503c\u8bbe\u7f6e\u4e3aOS\u5185\u5b58\u768450%\u4ee5\u4e0a\uff0c75%\u4ee5\u4e0b\u3002\u7136\u800c\uff0c\u8be5\u8bbe\u7f6e\u4ec5\u4f5c\u4e3a\u4f18\u5316\u5668\u5224\u65ad\u6267\u884c\u8ba1\u5212\u7684\u53c2\u8003\u4f4d\u7f6e\uff0c\u5e76\u4e0d\u4f1a\u7279\u522b\u5360\u7528\u5185\u5b58\u3002<\/p>\n
\u5de5\u4f5c\u5185\u90e8\u6709\u5e8f\u6574\u7406\u548c\u54c8\u5e0c\u8868\u64cd\u4f5c\u6240\u4f7f\u7528\u7684\u5185\u5b58\u5927\u5c0f\u3002
\n\u5728\u6267\u884c\u9700\u8981\u6392\u5e8f\u3001\u8868\u8fde\u63a5\u548c\u805a\u5408\u7b49\u590d\u6742\u67e5\u8be2\u65f6\u4f7f\u7528\u3002<\/p>\n
\u6bcf\u4e2a\u64cd\u4f5c\u4f7f\u7528\u503c\u7684\u6570\u91cf\u51b3\u5b9a\uff0c\u5982\u679c\u5728\u4e00\u6b21\u67e5\u8be2\u4e2d\u6267\u884c\u591a\u4e2a\u76f8\u5173\u64cd\u4f5c\uff0c\u5c06\u4f7f\u7528\u6b64\u503c\u4e58\u4ee5\u500d\u6570\u7684\u5185\u5b58\u3002
\n\u57fa\u4e8e\u57fa\u672c\u7684\u601d\u8def\uff0c\u5982\u679c\u5c06\u5176\u89c6\u4e3a\u4e13\u7528\u4e8e\u6570\u636e\u5e93\u7684\u670d\u52a1\u5668\uff0c\u5219\u53ef\u4ee5\u8ba4\u4e3a (\u64cd\u4f5c\u7cfb\u7edf\u5185\u5b58-shared_buffers)\/max_connection=work_mem \u662f\u5408\u7406\u7684\u3002
\n\u5185\u5b58\u5360\u7528\u53d1\u751f\u5728\u6267\u884c\u5904\u7406\u65f6\uff0c\u56e0\u6b64\u4e0d\u4f1a\u5728 PostgreSQL \u542f\u52a8\u671f\u95f4\u4e00\u76f4\u5b58\u5728\u3002
\n\u4f8b\u5982) work_mem=’5MB’<\/p>\n
\u6307\u5b9a\u4e00\u4e2a\u7528\u4e8e\u7ef4\u62a4\u578b\u64cd\u4f5c\uff08\u5982VACUUM\u3001CREATE INDEX\u548cALTER TABLE ADD FOREIGN KEY\uff09\u7684\u6700\u5927\u5185\u5b58\u91cf\u3002\u9ed8\u8ba4\u503c\u8f83\u4f4e\uff0c\u5efa\u8bae\u589e\u52a0\u3002\u6839\u636e\u7ecf\u9a8c\uff0c\u5927\u7ea6256MB\u662f\u4e00\u4e2a\u5408\u9002\u7684\u53c2\u8003\u503c\u3002\u4f8b\u5982\uff0c\u5c06maintenance_work_mem\u8bbe\u4e3a’256MB’\u3002<\/p>\n
wal_buffers\u662f\u7528\u4e8e\u5c1a\u672a\u5199\u5165\u78c1\u76d8\u7684WAL\u6570\u636e\u7684\u5171\u4eab\u5185\u5b58\u5bb9\u91cf\u3002\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0c\u5b83\u4f1a\u81ea\u52a8\u8c03\u6574\u4e3ashared_buffers\u76841\/32\u5927\u5c0f\uff0c\u56e0\u6b64\u5982\u679c\u4e0d\u8d85\u8fc7\u51e0\u5146\u5b57\u8282\uff0c\u53ef\u4ee5\u4f7f\u7528\u9ed8\u8ba4\u503c\u3002\u4f8b\u5982\uff0cwal_buffers = 16MB\u3002<\/p>\n
\u540c\u6b65\u63d0\u4ea4
\n\u7528\u4e8e\u6307\u5b9a\u4e8b\u52a1\u5728\u5411\u5ba2\u6237\u7aef\u8fd4\u56de”success”\u4e4b\u524d\uff0c\u662f\u5426\u7b49\u5f85WAL\u8bb0\u5f55\u88ab\u5199\u5165\u78c1\u76d8\u3002\u9ed8\u8ba4\u4e14\u5b89\u5168\u7684\u8bbe\u7f6e\u662f\u5f00\u542f\u3002<\/p>\n
\u5173\u4e8eoff\u7684\u60c5\u51b5\uff0c\u5728\u5411\u5ba2\u6237\u7aef\u62a5\u544a\u6210\u529f\u7684\u65f6\u523b\u548c\u4e8b\u52a1\u786e\u5b9e\u53d8\u5f97\u5b89\u5168\u4ee5\u5bf9\u6297\u670d\u52a1\u5668\u5d29\u6e83\u4e4b\u95f4\u53ef\u80fd\u4f1a\u53d1\u751f\u5ef6\u8fdf\u3002<\/p>\n
\u5c3d\u7ba1\u5b58\u5728\u6570\u636e\u5e93\u5d29\u6e83\u53ef\u80fd\u5bfc\u81f4\u6700\u8fd1\u63d0\u4ea4\u7684\u67d0\u4e9b\u4e8b\u52a1\u90e8\u5206\u4e22\u5931\u7684\u98ce\u9669\uff0c\u4f46\u5982\u679c\u6027\u80fd\u6bd4\u6570\u636e\u4e00\u81f4\u6027\u66f4\u4e3a\u91cd\u8981\uff0c\u90a3\u4e48\u53ef\u4ee5\u5c06\u5176\u8bbe\u4e3aoff\u4ee5\u52a0\u901f\u6027\u80fd\u3002\u6b64\u5916\uff0c\u8fd8\u53ef\u4ee5\u5728\u4f1a\u8bdd\u7ea7\u522b\u8fdb\u884c\u6307\u5b9a\uff0c\u4ec5\u9002\u7528\u4e8e\u66f4\u91cd\u8981\u7684\u6027\u80fd\u8981\u6c42\u4f1a\u8bdd\u3002<\/p>\n
\u4f8b\u5982\uff0csynchronous_commit=’on’\u3002<\/p>\n
\u968f\u673a\u9875\u9762\u6210\u672c
\n\u8868\u793a\u5728\u5c06\u987a\u5e8f\u8bbf\u95ee\u65f6\u95f4\u8bbe\u5b9a\u4e3a\u57fa\u51c6\u503c1.0\u65f6\uff0c\u968f\u673a\u8bbf\u95ee\u6240\u82b1\u8d39\u7684\u65f6\u95f4\u6bd4\u7387\u3002
\n\u968f\u673a\u8bbf\u95ee\u5305\u62ec\u7d22\u5f15\u626b\u63cf\u7b49\u3002\u5982\u679c\u4f7f\u7528\u50cfAWS\u7684EBS\u4e4b\u7c7b\u7684\u9ad8\u901f\u5b58\u50a8\u4ecb\u8d28\uff08\u5982SSD\uff09\uff0c\u7531\u4e8e\u968f\u673a\u8bbf\u95ee\u4e5f\u8db3\u591f\u5feb\uff0c\u5c06\u8be5\u503c\u8bbe\u4e3a1\u4f1a\u589e\u52a0\u9009\u62e9\u7d22\u5f15\u626b\u63cf\u7684\u53ef\u80fd\u6027\uff0c\u4ece\u800c\u63d0\u9ad8\u6027\u80fd\u3002
\n\u793a\u4f8b\uff09random_page_cost = 1<\/p>\n
\u6709\u6548io\u5e76\u53d1\u662f\u6307\u5047\u8bbePostgreSQL\u53ef\u4ee5\u540c\u65f6\u6267\u884c\u7684\u5e76\u53d1\u78c1\u76d8\u8f93\u5165\/\u8f93\u51fa\u64cd\u4f5c\u7684\u6570\u91cf\u3002
\n\u901a\u5e38\u60c5\u51b5\u4e0b\uff0c\u6267\u884c\u4e00\u6b21\u4ece\u78c1\u76d8\u8bfb\u53d6\u4e00\u4e2a\u5757\uff08\u6bcf\u6b21IO = 1\uff09\u7684\u64cd\u4f5c\uff0c\u4f46\u5f53\u8bbe\u7f6e\u5927\u4e8e1\u65f6\uff0c\u53ef\u4ee5\u9884\u8bfb\u6570\u636e\u5e76\u540c\u65f6\u83b7\u53d6\u591a\u4e2a\u5757\u3002
\n\u4e00\u822c\u5e94\u8be5\u6307\u5b9a\u78c1\u76d8\u9a71\u52a8\u5668\u7684\u6570\u91cf\u3002\u4f8b\u5982\uff0c\u5982\u679c\u5c06\u4e24\u4e2aEBS\u7ec4\u5408\u8fdb\u884c\u6761\u5e26\u5316\uff0c\u5219\u8bbe\u7f6e\u4e3a2\u3002
\n\u793a\u4f8b\uff09random_page_cost = 2<\/p>\n
\u8bf7\u53c2\u8003\u4ee5\u4e0b\u94fe\u63a5\u6765\u4f18\u5316\u60a8\u7684PostgreSQL\u670d\u52a1\u5668\uff1ahttps:\/\/wiki.postgresql.org\/wiki\/Tuning_Your_PostgreSQL_Server<\/p>\n
\u6570\u636e\u5e93\u7ba1\u7406<\/h1>\n
\u4ee5\u4e0b\u662f\u521b\u5efa\u548c\u5220\u9664\u6570\u636e\u5e93\u7684\u57fa\u672c\u547d\u4ee4\u3002\u5728Oracle\u4e2d\uff0c\u5b9e\u4f8b\uff08\u5728RAC\u4e2d\u662f\u6570\u636e\u5e93\uff09\u76f8\u5f53\u4e8ePostgresql\u4e2d\u7684\u6570\u636e\u5e93\u3002LC_COLLATE\u548cLC_CTYPE\u5728\u521b\u5efa\u540e\u65e0\u6cd5\u66f4\u6539\u3002\u5728AWS\u7684RDS\u4e0a\uff0c\u7531\u4e8e\u9ed8\u8ba4\u521b\u5efa\u7684\u662fen_US.UTF-8\u7684LC_COLLATE\u548cLC_CTYPE\uff0c\u5982\u679c\u9884\u671f\u4e0d\u540c\uff0c\u5219\u9700\u8981\u7a0d\u540e\u91cd\u65b0\u521b\u5efa\u3002<\/p>\n
#\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u4f5c\u6210<\/span>\r\nCREATE<\/span> DATABASE<\/span> db01<\/span> \r\nOWNER<\/span> =<\/span> app<\/span>\r\nTEMPLATE<\/span> =<\/span> template0<\/span> \r\nENCODING<\/span> =<\/span> 'UTF8'<\/span> \r\nLC_COLLATE<\/span> =<\/span> 'C'<\/span> \r\nLC_CTYPE<\/span> =<\/span> 'C'<\/span>;<\/span>\r\n\r\n#\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u524a\u9664<\/span>\r\nDROP<\/span> DATABASE<\/span> db01<\/span>\r\n\r\n#\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u89a7<\/span>\r\npostgres<\/span>=<\/span># \\l<\/span>\r\n                             List<\/span> of<\/span> databases<\/span>\r\n   Name<\/span>    |<\/span>  Owner<\/span>   |<\/span> Encoding<\/span> |<\/span> Collate<\/span> |<\/span> Ctype<\/span> |<\/span>   Access<\/span> privileges<\/span>   \r\n-----------+----------+----------+---------+-------+-----------------------<\/span>\r\n postgres<\/span>  |<\/span> postgres<\/span> |<\/span> UTF8<\/span>     |<\/span> C<\/span>       |<\/span> C<\/span>     |<\/span> \r\n template0<\/span> |<\/span> postgres<\/span> |<\/span> UTF8<\/span>     |<\/span> C<\/span>       |<\/span> C<\/span>     |<\/span> =<\/span>c<\/span>\/<\/span>postgres<\/span>          +<\/span>\r\n           |<\/span>          |<\/span>          |<\/span>         |<\/span>       |<\/span> postgres<\/span>=<\/span>CTc<\/span>\/<\/span>postgres<\/span>\r\n template1<\/span> |<\/span> postgres<\/span> |<\/span> UTF8<\/span>     |<\/span> C<\/span>       |<\/span> C<\/span>     |<\/span> =<\/span>c<\/span>\/<\/span>postgres<\/span>          +<\/span>\r\n           |<\/span>          |<\/span>          |<\/span>         |<\/span>       |<\/span> postgres<\/span>=<\/span>CTc<\/span>\/<\/span>postgres<\/span>\r\n testdb<\/span>    |<\/span> pguser01<\/span> |<\/span> UTF8<\/span>     |<\/span> C<\/span>       |<\/span> C<\/span>     |<\/span> \r\n(<\/span>4<\/span> rows<\/span>)<\/span>\r\n<\/code><\/pre>\n
\u7ba1\u7406\u7528\u6237\u3001\u89d2\u8272\u548c\u67b6\u6784\u3002<\/h1>\n
\u4e0d\u4ec5\u9650\u4e8ePostgreSQL\uff0c\u4e00\u822c\u7684\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf\uff08DBMS\uff09\u4e2d\u5b58\u5728\u4ee5\u4e0b\u6982\u5ff5\uff0c\u8fdb\u884c\u8be6\u7ec6\u89e3\u91ca\u3002<\/p>\n
\u7528\u6237\u662f\u6307\u767b\u5f55\u6570\u636e\u5e93\u5e76\u53d1\u51fa\u4efb\u4f55\u67e5\u8be2\u7684\u4e3b\u4f53\u3002\u6b64\u5916\uff0c\u5b83\u4e0d\u5c5e\u4e8e\u7279\u5b9a\u7684\u6570\u636e\u5e93\uff0c\u800c\u662f\u4f5c\u4e3a\u6574\u4e2a\u6570\u636e\u5e93\u96c6\u7fa4\u6765\u5b9a\u4e49\u3002<\/p>\n
\u89d2\u8272\u662f\u5c06\u6743\u9650\u8fdb\u884c\u6574\u5408\u7684\u6982\u5ff5\u3002\u901a\u8fc7\u7ed9\u7528\u6237\u5206\u914d\u89d2\u8272\uff0c\u7528\u6237\u53ef\u4ee5\u884c\u4f7f\u4e0e\u89d2\u8272\u76f8\u5173\u7684\u6743\u9650\u3002\uff08\u4e5f\u53ef\u4ee5\u76f4\u63a5\u7ed9\u7528\u6237\u5206\u914d\u6743\u9650\u800c\u4e0d\u662f\u5206\u914d\u7ed9\u89d2\u8272\uff09\u540c\u65f6\uff0c\u5b83\u4e0d\u5c5e\u4e8e\u7279\u5b9a\u7684\u6570\u636e\u5e93\uff0c\u800c\u662f\u5b9a\u4e49\u5728\u6574\u4e2a\u6570\u636e\u5e93\u96c6\u7fa4\u4e2d\u3002<\/p>\n
\u67b6\u6784
\n\u4e00\u4e2a\u8868\u793a\u5bf9\u8c61\u6240\u5c5e\u7684\u6982\u5ff5\u3002\u901a\u8fc7\u67b6\u6784\u540d\u79f0.\u8868\u540d\u79f0\u7684\u5f62\u5f0f\u6307\u793a\u7279\u5b9a\u7684\u5bf9\u8c61\u3002\u5728\u540c\u4e00\u4e2a\u6570\u636e\u5e93\u4e2d\uff0c\u5c5e\u4e8e\u540c\u4e00\u67b6\u6784\u540d\u79f0.\u5bf9\u8c61\u540d\u79f0\u7684\u5bf9\u8c61\u662f\u4e0d\u5b58\u5728\u7684\u3002\u5b83\u540c\u65f6\u5c5e\u4e8e\u7279\u5b9a\u7684\u6570\u636e\u5e93\u3002<\/p>\n
\"image.png\"<\/div>\n
\u6ce8\u610f\u3059\u308b\u3079\u304dPostgreSQL\u306e\u70b9\u306f\u3001\u30e6\u30fc\u30b6\u3068\u30ed\u30fc\u30eb\u304c\u307b\u307c\u540c\u3058\u3068\u9593\u9055\u308f\u308c\u3084\u3059\u3044\u3053\u3068\u3067\u3059\u3002Oracle\u3067\u306f\u306f\u3063\u304d\u308a\u3068\u5206\u3051\u3089\u308c\u3066\u304a\u308a\u3001\u30ed\u30fc\u30eb\u306b\u3088\u308b\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3078\u306e\u30ed\u30b0\u30a4\u30f3\u306f\u3067\u304d\u307e\u305b\u3093\u304c\u3001PostgreSQL\u3067\u306f\u30ed\u30fc\u30eb\u306bLOGIN\u6a29\u9650\u304c\u3042\u308c\u3070\u3001\u30ed\u30fc\u30eb\u3067\u30ed\u30b0\u30a4\u30f3\u3059\u308b\u3053\u3068\u304c\u53ef\u80fd\u3067\u3059\u3002\u305d\u306e\u305f\u3081\u3001\u6700\u521d\u306f\u79c1\u3082\u30ed\u30fc\u30eb\u3068\u30e6\u30fc\u30b6\u304c\u540c\u3058\u3082\u306e\u304b\u3068\u8003\u3048\u307e\u3057\u305f\u304c\u3001\u660e\u78ba\u306b\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u8003\u3048\u308b\u65b9\u304c\u826f\u3055\u305d\u3046\u3067\u3059\u3002<\/p>\n
\"image.png\"<\/div>\n
\u5728\u4e2a\u4eba\u770b\u6765\uff0c\u6211\u5e0c\u671b\u660e\u786e\u5730\u533a\u5206\u89d2\u8272\u548c\u7528\u6237\uff0c\u6240\u4ee5\u4e0d\u4f1a\u7ed9\u89d2\u8272\u8d4b\u4e88LOGIN\u6743\u9650\uff0c\u800c\u662f\u5c06\u6743\u9650\u901a\u8fc7\u89d2\u8272\u8fdb\u884c\u6c47\u603b\uff0c\u7136\u540e\u518d\u5206\u914d\u7ed9\u7528\u6237\uff0c\u8fd9\u662f\u6211\u60f3\u8981\u7684\u8bbe\u8ba1\u3002<\/p>\n
#Postgres\u3067\u306f\u30ed\u30fc\u30eb=\u30e6\u30fc\u30b6\u306e\u6982\u5ff5?<\/span>\r\n\r\n--<\/span>\u30e6\u30fc\u30b6\u4f5c\u6210<\/span>(<\/span>ROLE<\/span>\u3068\u51fa\u3066\u304f\u308b\u306e\u3067\u30df\u30b9\u30ea\u30fc\u30c9\u3057\u3084\u3059\u3044<\/span>)<\/span>\r\ntestdb<\/span>=><\/span> create<\/span> user<\/span> pguser01<\/span> with<\/span> login<\/span> password<\/span> 'P#ssw0rd'<\/span>;<\/span>\r\n\r\nCREATE<\/span> ROLE<\/span>\r\n\r\n--<\/span>\u30ed\u30fc\u30eb\u4f5c\u6210<\/span>\r\nCREATE<\/span> ROLE<\/span> pguser01<\/span>;<\/span>\r\n\r\n--<\/span>\u30ed\u30fc\u30eb\u4f5c\u6210<\/span>(<\/span>\u30d1\u30b9\u30ef\u30fc\u30c9\u3082\u4ed8\u4e0e\u3059\u308b\u3068\u30ed\u30fc\u30eb\u3067\u30ed\u30b0\u30a4\u30f3\u5316\u306e\u4ed5\u7d44\u307f<\/span>)<\/span>\r\nCREATE<\/span> ROLE<\/span> pguser01<\/span> with<\/span> login<\/span> password<\/span> 'P#ssw0rd'<\/span>;<\/span>\r\n\r\n--<\/span>\u30e6\u30fc\u30b6\u3078\u6a29\u9650\u4ed8\u4e0e<\/span>\r\nALTER<\/span> USER<\/span> pguser01<\/span> SUPERUSER<\/span>;<\/span>\r\n\r\n--<\/span>\u30ed\u30fc\u30eb\u3078\u6a29\u9650\u4ed8\u4e0e<\/span>\r\nALTER<\/span> ROLE<\/span> pguser01<\/span> SUPERUSER<\/span>;<\/span>\r\n\r\n--<\/span>\u30e6\u30fc\u30b6\u524a\u9664<\/span>\r\ntestdb<\/span>=><\/span> drop<\/span> user<\/span> pguser01<\/span>;<\/span>\r\nDROP<\/span> ROLE<\/span>\r\n\r\n--<\/span>\u30ed\u30fc\u30eb\u524a\u9664<\/span>\r\ndrop<\/span> role<\/span> pguser01<\/span>;<\/span>\r\n\r\n--<\/span>\u73fe\u5728\u306e\u30ed\u30b0\u30a4\u30f3\u30e6\u30fc\u30b6<\/span>(<\/span>\u30ed\u30fc\u30eb<\/span>)<\/span>\u78ba\u8a8d<\/span>\r\nselect<\/span> current_user<\/span>;<\/span>\r\n\r\n--<\/span>\u30e6\u30fc\u30b6\u4e00\u89a7<\/span>(<\/span>\u30ed\u30fc\u30eb\u3082\u4e00\u7dd2\u306b\u5404\u30ec\u30b3\u30fc\u30c9\u3068\u3057\u3066\u51fa\u3066\u304f\u308b\u306e\u3067\u6df7\u5728<\/span>)<\/span>\r\npostgres<\/span>=<\/span># \\du<\/span>\r\n                                   List<\/span> of<\/span> roles<\/span>\r\n Role<\/span> name<\/span> |<\/span>                         Attributes<\/span>                         |<\/span> Member<\/span> of<\/span> \r\n-----------+------------------------------------------------------------+-----------<\/span>\r\n pguser01<\/span>  |<\/span> Superuser<\/span>                                                  |<\/span> {}<\/span>\r\n postgres<\/span>  |<\/span> Superuser<\/span>,<\/span> Create<\/span> role<\/span>,<\/span> Create<\/span> DB<\/span>,<\/span> Replication<\/span>,<\/span> Bypass<\/span> RLS<\/span> |<\/span> {}<\/span>\r\n\r\n<\/code><\/pre>\n
\u203b\u7cfb\u7edf\u6743\u9650\u6e05\u5355\uff08\u8fd9\u4e9b\u6743\u9650\u4e0d\u80fd\u901a\u8fc7\u89d2\u8272\u95f4\u63a5\u6388\u4e88\uff09<\/p>\n
\n
\u4ed8\u4e0e\u5265\u596a\u5099\u8003SUPERUSERNOSUPERUER\u30b9\u30fc\u30d1\u30fc\u30e6\u30fc\u30b6\u30fc\u306e\u6a29\u9650CREATEDBNOCREATEDB\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u4f5c\u6210\u306e\u6a29\u9650CREATEROLENOCREATEROLE\u30ed\u30fc\u30eb\u4f5c\u6210\u306e\u6a29\u9650\u3002CREATEROLE\u6a29\u9650\u3092\u6301\u3064\u30ed\u30fc\u30eb\u306f\u3001\u30b9\u30fc\u30d1\u30fc\u30e6\u30fc\u30b6\u4ee5\u5916\u306e\u30ed\u30fc\u30eb\u306b\u5bfe\u3057\u3066\u305d\u306e\u540d\u524d\u3092\u5909\u66f4\u3067\u304d\u307e\u3059CREATEUSERNOCREATEUSER\u30e6\u30fc\u30b6\u30fc\u4f5c\u6210\u306e\u6a29\u9650LOGINNOLOGIN\u30ed\u30b0\u30a4\u30f3\u6a29\u9650REPLICATIONNOREPLICATION\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u306e\u4f5c\u6210\u6a29\u9650<\/div>\n<\/div>\n
--<\/span>\u30b9\u30ad\u30fc\u30de\u4f5c\u6210<\/span>\r\npostgres<\/span>=<\/span># create schema XXX;<\/span>\r\nCREATE<\/span> SCHEMA<\/span>\r\n\r\n--!!!<\/span>\u30b9\u30ad\u30fc\u30de\u3092\u4f5c\u6210\u3059\u308b\u306b\u306f\u30b3\u30de\u30f3\u30c9\u3092\u5b9f\u884c\u3059\u308b\u30ed\u30fc\u30eb\u304c\u30b9\u30fc\u30d1\u30fc\u30e6\u30fc\u30b6\u30fc\u304b\u3001\u30b9\u30ad\u30fc\u30de\u3092\u4f5c\u6210\u3059\u308b\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3067<\/span> CREATE<\/span> \u6a29\u9650\u3092\u6301\u3063\u3066\u3044\u308b\u5fc5\u8981\u304c\u3042\u308b\u3002<\/span>\r\n\r\n#\u30b9\u30ad\u30fc\u30de\u524a\u9664<\/span>\r\npostgres<\/span>=<\/span># drop schema XXX;<\/span>\r\nDROP<\/span> SCHEMA<\/span>\r\n\r\n#\u73fe\u5728\u306e\u30b9\u30ad\u30fc\u30de\u78ba\u8a8d<\/span>\r\nselect<\/span> current_schema<\/span>;<\/span>\r\n\r\n#\u30ab\u30ec\u30f3\u30c8\u30b9\u30ad\u30fc\u30de\u5909\u66f4<\/span>\r\nSET<\/span> search_path<\/span> =<\/span> XXX<\/span>;<\/span>\r\n\r\n#\u30b9\u30ad\u30fc\u30de\u4e00\u89a7<\/span>\r\npostgres<\/span>=<\/span># \\dn<\/span>\r\n List<\/span> of<\/span> schemas<\/span>\r\n Name<\/span>  |<\/span>  Owner<\/span>   \r\n-------+----------<\/span>\r\n xwork<\/span> |<\/span> postgres<\/span>\r\n(<\/span>1<\/span> row<\/span>)<\/span>\r\n<\/code><\/pre>\n
\u6743\u9650\u7ba1\u7406<\/h1>\n
\u4ee5\u4e0b\u662f\u57fa\u672c\u7684\u6743\u9650\u76f8\u5173\u6307\u4ee4\u3002\u5305\u62ec\u6388\u6743\u3001\u5220\u9664\u548c\u786e\u8ba4\u3002
\n\u603b\u7684\u6765\u8bf4\uff0c\u6709\u5206\u4e3a\u6388\u4e88\u89d2\u8272\u548c\u6388\u4e88\u5bf9\u8c61\u6743\u9650\u4e24\u79cd\u60c5\u51b5\u3002
\n\u6b64\u5916\uff0c\u89d2\u8272\u8fd8\u53ef\u4ee5\u7ee7\u627f\u3002\u89d2\u8272\u7684\u7ee7\u627f\u662f\u6307\u7ee7\u627f\u6e90\u89d2\u8272\u5177\u6709\u7684\u6743\u9650\u76f4\u63a5\u4f20\u9012\u7ed9\u7ee7\u627f\u76ee\u6807\u89d2\u8272\u3002
\n\u901a\u8fc7\u8fd9\u79cd\u7ee7\u627f\u8d4b\u4e88\u7684\u6743\u9650\u5728\u4e0a\u8ff0\u6743\u9650\u786e\u8ba4\u573a\u666f\u4e2d\u4e0d\u4f1a\u663e\u793a\u3002<\/p>\n
--<\/span>pguser01<\/span>\u30ed\u30fc\u30eb\u306e\u6a29\u9650\u3092<\/span>pguser03<\/span>\u30ed\u30fc\u30eb\u3078\u3068\u4ed8\u4e0e<\/span>\r\ngrant<\/span> pguser01<\/span> to<\/span> pguser03<\/span>;<\/span>\r\n<\/code><\/pre>\n
--<\/span>\u7279\u5b9a\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3078\u306e\u6a29\u9650\u4ed8\u4e0e<\/span>\r\n--<\/span>test01<\/span>\u30c6\u30fc\u30d6\u30eb\u306b\u5bfe\u3059\u308b<\/span>select<\/span>,<\/span>insert<\/span>\u6a29\u9650\u3092<\/span>pguser03<\/span>\u30ed\u30fc\u30eb\u306b\u4ed8\u4e0e<\/span>\r\ngrant<\/span> select<\/span>,<\/span>insert<\/span> on<\/span> test01<\/span> to<\/span> pguser03<\/span>;<\/span>\r\n\r\n--<\/span>\u7279\u5b9a\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u304b\u3089\u306e\u6a29\u9650\u524a\u9664<\/span>\r\n###test01\u30c6\u30fc\u30d6\u30eb\u306b\u5bfe\u3059\u308bdelete\u6a29\u9650\u3092pguser03\u30ed\u30fc\u30eb\u304b\u3089\u524a\u9664<\/span>\r\nrevoke<\/span> delete<\/span> on<\/span> test01<\/span> from<\/span> pguser03<\/span>;<\/span>\r\n\r\n---<\/span>\u6a29\u9650\u78ba\u8a8d<\/span>\r\n###test01\u30c6\u30fc\u30d6\u30eb\u306b\u5bfe\u3059\u308bselect,insert\u6a29\u9650\u304cpguser0\uff13\u30ed\u30fc\u30eb\u306b\u4ed8\u4e0e\u3055\u308c\u3066\u3044\u308b<\/span>\r\n\u00a5<\/span>z<\/span>\r\n                                  \u30a2\u30af\u30bb\u30b9\u6a29<\/span>\r\n \u30b9\u30ad\u30fc\u30de<\/span> |<\/span>   \u540d\u524d<\/span>   |<\/span>    \u578b<\/span>    |<\/span>        \u30a2\u30af\u30bb\u30b9\u6a29<\/span>         |<\/span> \u5217\u306e\u30a2\u30af\u30bb\u30b9\u6a29\u9650<\/span> \r\n----------+----------+----------+---------------------------+------------------<\/span>\r\n public<\/span>   |<\/span> test1<\/span>    |<\/span> \u30c6\u30fc\u30d6\u30eb<\/span> |<\/span> pguser01<\/span>=<\/span>arwdDxt<\/span>\/<\/span>pguser01<\/span>+|<\/span> \r\n          |<\/span>          |<\/span>          |<\/span> pguser03<\/span>=<\/span>ar<\/span>\/<\/span>pguser01<\/span>      |<\/span> \r\n\r\n--<\/span>\u7279\u5b9a\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3078\u306e\u5168\u6a29\u9650\u4ed8\u4e0e<\/span>\r\n###test01\u30c6\u30fc\u30d6\u30eb\u306b\u5bfe\u3059\u308b\u5168\u6a29\u9650\u3092pguser03\u30ed\u30fc\u30eb\u306b\u4ed8\u4e0e<\/span>\r\ngrant<\/span> all<\/span> on<\/span> test01<\/span> to<\/span> pguser03<\/span>;<\/span>\r\n<\/code><\/pre>\n
\u6743\u9650\u7684\u89c2\u70b9 de<\/p>\n
\n
\u6587\u5b57\u8aac\u660eaINSERT\u53ef\u80fd(apend)rSELECT\u53ef\u80fd(read)wUPDATE\u53ef\u80fd(write)dDELETE\u53ef\u80fd(delete)DTRUNCATExREFERENCEStTRIGGER\/yyyy\u3053\u306e\u6a29\u9650\u3092\u4ed8\u4e0e\u3057\u305f\u30ed\u30fc\u30eb<\/div>\n<\/div>\n
\u6b64\u5916\uff0c\u6211\u8ba4\u4e3a\u6709\u65f6\u5019\u6211\u4eec\u4e5f\u53ef\u80fd\u5e0c\u671b\u65e0\u6761\u4ef6\u5730\u64cd\u4f5c\u7279\u5b9a\u6a21\u5f0f\u6216\u6570\u636e\u5e93\u7684\u6240\u6709\u5bf9\u8c61\u3002\u5728\u90a3\u79cd\u60c5\u51b5\u4e0b\uff0c\u8bf7\u6267\u884c\u4ee5\u4e0b\u6b65\u9aa4\uff1a
\nhttps:\/\/www.postgresql.jp\/document\/10\/html\/sql-grant.html<\/p>\n
--<\/span>xxxwork<\/span>\u306e\u5168\u30c6\u30fc\u30d6\u30eb\u3078\u306e\u6a29\u9650\u4ed8\u4e0e<\/span>\r\nGRANT<\/span> ALL<\/span> ON<\/span> ALL<\/span> TABLES<\/span> IN<\/span> SCHEMA<\/span> xxxwork<\/span> TO<\/span> user02<\/span>;<\/span>\r\n\r\n--<\/span>xxxwork<\/span>\u306e\u5168\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u306e\u6a29\u9650\u5265\u596a<\/span>\r\nREVOKE<\/span> ALL<\/span> ON<\/span> ALL<\/span> TABLES<\/span> IN<\/span> SCHEMA<\/span> xxxwork<\/span> TO<\/span> user02<\/span>;<\/span>\r\n\r\n--<\/span>xxxwork<\/span>\u30b9\u30ad\u30fc\u30de\u3078\u306e\u5168\u6a29\u9650\u4ed8\u4e0e<\/span>\r\nGRANT<\/span> ALL<\/span> ON<\/span> SCHEMA<\/span> xxxwork<\/span> TO<\/span> user02<\/span>;<\/span>\r\n\r\n--<\/span>xxxwork<\/span>\u30b9\u30ad\u30fc\u30de\u304b\u3089\u306e\u5168\u6a29\u9650\u5265\u596a<\/span>\r\nREVOKE<\/span> ALL<\/span> ON<\/span> SCHEMA<\/span> xxxwork<\/span> FROM<\/span> user02<\/span>;<\/span>\r\n\r\n--<\/span>db01<\/span>\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3078\u306e\u5168\u6a29\u9650\u4ed8\u4e0e<\/span>\r\nGRANT<\/span> ALL<\/span> ON<\/span> DATABSE<\/span> db01<\/span> TO<\/span> user02<\/span>;<\/span>\r\n\r\n--<\/span>db01<\/span>\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u304b\u3089\u306e\u5168\u6a29\u9650\u5265\u596a<\/span>\r\nREVOKE<\/span> ALL<\/span> ON<\/span> DATABSE<\/span> db01<\/span> FROM<\/span> user02<\/span>;<\/span>\r\n<\/code><\/pre>\n
\u65e5\u5fd7\u7ba1\u7406<\/h1>\n
\u5728PostgreSQL\u4e2d\uff0c\u53ef\u4ee5\u8f93\u51fa\u8bb0\u5f55\u9519\u8bef\u3001\u8b66\u544a\u7b49\u95ee\u9898\u7684\u670d\u52a1\u5668\u65e5\u5fd7\u3002\u901a\u5e38\u53ef\u4ee5\u9009\u62e9syslog\u8f93\u51fa\u683c\u5f0f\u548c\u5355\u72ec\u7684\u6587\u672c\u6587\u4ef6\u4e24\u79cd\u65b9\u5f0f\uff0c\u4f46\u8003\u8651\u5230\u5355\u72ec\u7684\u6587\u672c\u6587\u4ef6\u4e0d\u4f1a\u4e0e\u64cd\u4f5c\u7cfb\u7edf\u7684\u4fe1\u606f\u6df7\u5728\u4e00\u8d77\uff0c\u56e0\u6b64\u8ba4\u4e3a\u8fd9\u79cd\u65b9\u5f0f\u66f4\u5408\u9002\u3002<\/p>\n
\u670d\u52a1\u5668\u65e5\u5fd7\u8bbe\u8ba1<\/h2>\n
\u7528\u4e8e\u57fa\u672c\u670d\u52a1\u5668\u65e5\u5fd7\u8f93\u51fa\u7684\u53c2\u6570\u5019\u9009\u3002\u5728postgresql.conf\u4e2d\u8bbe\u7f6e\u3002<\/p>\n
\u65e5\u5fd7\u76ee\u6807\u3001\u65e5\u5fd7\u6536\u96c6\u5668
\nlog_destination\u662f\u51b3\u5b9a\u5c06\u65e5\u5fd7\u8f93\u51fa\u5230\u54ea\u91cc\u7684\u53c2\u6570\uff0c\u53ef\u4ee5\u8bbe\u7f6e\u4e3asyslog\u3001stderr\uff08\u6807\u51c6\u9519\u8bef\u65e5\u5fd7\uff09\u6216csvlog\u3002\u5982\u679c\u8bbe\u7f6e\u4e3asyslog\uff0c\u5219\u4f1a\u8f93\u51fa\u5230\u64cd\u4f5c\u7cfb\u7edf\u7684syslog\u4e2d\u3002\u5982\u679c\u8bbe\u7f6e\u4e3astderr\u6216csvlog\uff0c\u5e76\u4e14\u540c\u65f6\u5c06log_collector\u8bbe\u7f6e\u4e3aon\uff0c\u5219\u4f1a\u5c06\u65e5\u5fd7\u8f93\u51fa\u5230\u6587\u672c\u6587\u4ef6\u6216csv\u4e2d\u3002
\nlog_destination=’stderr’
\nlog_collector=’on’<\/p>\n
\u5f53\u5c06og_destination\u8bbe\u7f6e\u4e3a’stderr’\u6216’csv’\u5e76\u542f\u7528logging_collector\u4ee5\u8fdb\u884cPostgreSQL\u72ec\u7acb\u8bb0\u5f55\u65f6\uff0c\u53ef\u4ee5\u901a\u8fc7\u8bbe\u7f6e\u6765\u786e\u5b9a\u5c06\u65e5\u5fd7\u6587\u4ef6\u8f93\u51fa\u5230\u54ea\u4e2a\u76ee\u5f55\u4ee5\u53ca\u4ee5\u4f55\u79cd\u540d\u79f0\u3002
\nlog_directory=’\/var\/log\/pgsql’
\nlog_filename=’postgresql-%Y-%m-%d-%H-%M-%S.log’<\/p>\n
log_line_prefix = ‘[%t][%p][%u][%d] ‘\u662f\u4e00\u4e2a\u7528\u4e8e\u6307\u5b9a\u8f93\u51fa\u65e5\u5fd7\u6d88\u606f\u524d\u7f00\u7684\u9009\u9879\u3002\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0c\u53ea\u8f93\u51fa\u6d88\u606f\u672c\u8eab\uff0c\u56e0\u6b64\u5efa\u8bae\u8bbe\u7f6e\u4e3a%t\uff08\u65f6\u95f4\uff09\u3001%u\uff08\u7528\u6237\u540d\uff09\u3001%d\uff08\u6570\u636e\u5e93\u540d\uff09\u548c%p\uff08\u8fdb\u7a0bID\uff09\u6765\u83b7\u5f97\u66f4\u597d\u7684\u7ed3\u679c\u3002<\/p>\n
\u65e5\u5fd7\u8f6e\u8f6c\u5e74\u9f84\uff0c\u65e5\u5fd7\u8f6e\u8f6c\u5927\u5c0f\u662f\u6307\u5b9a\u65e5\u5fd7\u8f6e\u8f6c\u7684\u65f6\u95f4\u548c\u5927\u5c0f\u7684\u53c2\u6570\u3002log_rotation_age\u662f\u4ee5\u5206\u949f\u4e3a\u5355\u4f4d\u6307\u5b9a\u7684\u65f6\u95f4\u53c2\u6570\uff0clog_rotation_size\u662f\u4ee5\u5343\u5b57\u8282\u4e3a\u5355\u4f4d\u6307\u5b9a\u7684\u5927\u5c0f\u53c2\u6570\uff0c\u53ef\u4ee5\u6839\u636e\u5927\u5c0f\u6765\u5207\u6362\u65e5\u5fd7\u3002log_rotation_age\u7684\u5207\u6362\u65f6\u95f4\u662f\u4ee5PostgreSQL\u542f\u52a8\u7684\u65e5\u671f\u76840\u65f60\u52060\u79d2\u4f5c\u4e3a\u57fa\u51c6\u8fdb\u884c\u8ba1\u7b97\u7684\u3002
\nlog_rotation_age=1\u5929
\nlog_rotation_size=102400 \u5343\u5b57\u8282<\/p>\n
log_min_error_statement\u63a7\u5236\u662f\u5426\u5c06\u5bfc\u81f4\u9519\u8bef\u6761\u4ef6\u7684SQL\u8bed\u53e5\u8bb0\u5f55\u5728\u670d\u52a1\u5668\u65e5\u5fd7\u4e2d\u3002\u6240\u6709\u5bfc\u81f4\u8bbe\u7f6e\u7684\u9519\u8bef\u7ea7\u522b\u6216\u66f4\u9ad8\u7ea7\u522b\u7684\u9519\u8bef\u7684SQL\u8bed\u53e5\u90fd\u4f1a\u88ab\u8bb0\u5f55\u5728\u65e5\u5fd7\u4e2d\u3002\u662f\u5426\u5c06\u5176\u8bbe\u7f6e\u4e3a’error’\u4f1a\u66f4\u5408\u9002\u3002<\/p>\n
\u5c06log_hostname\u8bbe\u7f6e\u4e3aoff\u8868\u793a\u901a\u5e38\u60c5\u51b5\u4e0b\u4e0d\u4f1a\u8bb0\u5f55\u8fde\u63a5\u5230\u6570\u636e\u5e93\u7684\u539f\u59cb\u8bbf\u95ee\u6765\u6e90IP\u5730\u5740\uff0c\u4f46\u8fde\u63a5\u4e3b\u673a\u7684\u4e3b\u673a\u540d\u4ecd\u4f1a\u901a\u8fc7\u53cd\u5411\u89e3\u6790\u663e\u793a\u5728\u65e5\u5fd7\u4e2d\u3002\u7136\u800c\uff0c\u7531\u4e8e\u53cd\u5411\u89e3\u6790IP\u5730\u5740\u53ef\u80fd\u6210\u4e3a\u74f6\u9888\uff0c\u56e0\u6b64\u5c06\u5176\u8bbe\u7f6e\u4e3aoff\u662f\u5408\u9002\u7684\u3002<\/p>\n
\u8bbe\u8ba1\u957f\u65f6\u95f4\u5316\u5904\u7406\u68c0\u6d4b\u4ee5\u53ca\u5176\u4ed6\u5904\u7406\u8bb0\u5f55\u5728\u65e5\u5fd7\u4e2d\u3002<\/h2>\n
log_autovacuum_min_duration\u53c2\u6570\u7528\u4e8e\u8bbe\u7f6eautovacuum\u6d3b\u52a8\u7684\u6700\u5c0f\u6267\u884c\u65f6\u95f4\uff08\u5355\u4f4d\u4e3a\u6beb\u79d2\uff09\uff0c\u5f53\u8fbe\u5230\u8be5\u65f6\u95f4\u65f6\uff0c\u4f1a\u5c06\u6bcf\u4e2aautovacuum\u6d3b\u52a8\u7684\u65e5\u5fd7\u8bb0\u5f55\u4e0b\u6765\u3002\u8fd9\u5bf9\u4e8e\u8ffd\u8e2aautovacuum\u6d3b\u52a8\u975e\u5e38\u6709\u7528\u3002\u4f8b\u5982\uff0c\u8bbe\u7f6e\u4e3a5000\u8868\u793a\u53ea\u8bb0\u5f55\u6267\u884c\u65f6\u95f4\u8d85\u8fc75\u79d2\u7684\u6d3b\u52a8\u3002<\/p>\n
log_min_duration_statement\u662f\u7528\u4e8e\u6307\u5b9a\u4ee5\u6beb\u79d2\u4e3a\u5355\u4f4d\u8f93\u51fa\u6162\u67e5\u8be2\u7684\u9608\u503c\u3002
\n\u5982\u679c\u8bbe\u7f6e\u4e3a-1\uff0c\u5219\u4e0d\u8f93\u51fa\u3002
\n\u4f8b\u5982\uff1a\u68c0\u6d4b\u8d85\u8fc71\u5206\u949f\u7684\u67e5\u8be2\u3002
\nlog_min_duration_statement=60000<\/p>\n
log_checkpoints
\n\u5c06\u68c0\u67e5\u70b9\u548c\u91cd\u542f\u70b9\u8bb0\u5f55\u5230\u670d\u52a1\u5668\u65e5\u5fd7\u4e2d\u3002\u6b64\u65e5\u5fd7\u6d88\u606f\u5305\u542b\u4e00\u4e9b\u7edf\u8ba1\u4fe1\u606f\uff0c\u5982\u5199\u5165\u7684\u7f13\u51b2\u533a\u6570\u548c\u6240\u9700\u7684\u5199\u5165\u65f6\u95f4\u3002
\nlog_checkpoints = \u6253\u5f00<\/p>\n
\u5ba1\u8ba1\u65e5\u5fd7\u8bbe\u8ba1<\/h2>\n
\u5728\u5f53\u524d\u60c5\u51b5\u4e0b\uff0c\u4f7f\u7528\u6570\u636e\u5e93\u6765\u83b7\u53d6\u5ba1\u8ba1\u65e5\u5fd7\u5df2\u7ecf\u6210\u4e3a\u5b89\u5168\u8981\u6c42\u7684\u5e38\u89c1\u505a\u6cd5\u3002\u6211\u60f3\u6574\u7406\u4e00\u4e0b\u5ba1\u8ba1\u65e5\u5fd7\u83b7\u53d6\u7684\u65b9\u6cd5\u3002<\/p>\n
\u767b\u5f55\u5ba1\u8ba1
\nlog_connections
\n\u542f\u7528\u6b64\u9009\u9879\u53ef\u4ee5\u5c06\u5ba2\u6237\u7aef\u8ba4\u8bc1\u6210\u529f\u6216\u8fde\u63a5\u670d\u52a1\u5668\u7684\u5c1d\u8bd5\u8bb0\u5f55\u5728\u65e5\u5fd7\u4e2d\u3002
\nlog_connections=on<\/p>\n
\u542f\u7528log_disconnections\u53ef\u4ee5\u5c06\u5ba2\u6237\u7aef\u8fde\u63a5\u7684\u65ad\u5f00\u8bb0\u5f55\u5728\u65e5\u5fd7\u4e2d\u3002log_disconnections=on\u3002<\/p>\n
SQL\u5ba1\u8ba1
\nlog_statement_all
\n\u6307\u5b9a\u8981\u8f93\u51fa\u5230\u65e5\u5fd7\u7684SQL\u8bed\u53e5\u3002
\n\u5982\u679c\u6307\u5b9a\u4e3a”none”\uff0c\u5219\u65e0\u8f93\u51fa\u3002
\n\u5982\u679c\u6307\u5b9a\u4e3a”ddl”\uff0c\u5219\u8f93\u51fa\u5305\u62ecCREATE\u3001ALTER\u3001DROP\u7b49DDL\u64cd\u4f5c\u7684\u8bed\u53e5\u3002
\n\u5982\u679c\u6307\u5b9a\u4e3a”mod”\uff0c\u5219\u5728DDL\u8f93\u51fa\u7684\u57fa\u7840\u4e0a\u8fd8\u8f93\u51fa\u5305\u62ecINSERT\u3001UPDATE\u3001DELETE\u7b49DML\u64cd\u4f5c\u7684\u8bed\u53e5\u3002
\n\u5982\u679c\u6307\u5b9a\u4e3a”all”\uff0c\u5219\u8f93\u51fa\u6240\u6709SQL\u8bed\u53e5\u3002
\n\u203b\u4f46\u662f\uff0c\u5982\u679c\u660e\u663e\u5b58\u5728\u8bed\u6cd5\u9519\u8bef\u7684SQL\u8bed\u53e5\u88ab\u6267\u884c\uff0c\u5219\u4e0d\u4f1a\u8f93\u51fa\u5230\u65e5\u5fd7\u3002
\n\u5982\u679c\u8981\u8f93\u51fa\u8fd9\u6837\u7684\u9519\u8bef\u4fe1\u606f\uff0c\u8bf7\u901a\u8fc7\u5b9a\u4e49log_min_error_statement\u4e3a”error”\u6765\u8f93\u51fa\u4fe1\u606f\u3002<\/p>\n
\u5728\u4e0a\u8ff0\u5185\u5bb9\u4e2d\uff0c\u53ef\u4ee5\u63a7\u5236PostgreSQL\u7684SQL\u5ba1\u8ba1\u65e5\u5fd7\uff0c\u53ef\u4ee5\u5206\u522b\u5728\u7cfb\u7edf\u7ea7\u3001\u6570\u636e\u5e93\u7ea7\u548c\u7528\u6237\u7ea7\u8fdb\u884c\u63a7\u5236\u3002<\/p>\n
1<\/span>)<\/span>\u30b7\u30b9\u30c6\u30e0\u5358\u4f4d<\/span>\r\nlog_statement_all<\/span>\u3092<\/span>postgresql<\/span>.<\/span>conf<\/span>\u306b\u8a2d\u5b9a<\/span>\r\n\r\n2<\/span>)<\/span>\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u5358\u4f4d<\/span>\r\n#\u8ffd\u52a0<\/span>\r\nALTER<\/span> DATABASE<\/span> <<\/span>DB<\/span>\u540d<\/span>><\/span> SET<\/span> log_statement<\/span> =<\/span> '<none|ddl|mod|all>'<\/span>;<\/span>\r\n#\u524a\u9664<\/span>\r\nALTER<\/span> DATABASE<\/span> <<\/span>DB<\/span>\u540d<\/span>><\/span> RESET<\/span> log_statement<\/span>\r\n#\u78ba\u8a8d<\/span>\r\nSELECT<\/span> *<\/span> FROM<\/span> pg_db_role_setting<\/span>;<\/span>\r\n\r\n3<\/span>)<\/span>\u30e6\u30fc\u30b6\u5358\u4f4d<\/span>\r\n#\u8ffd\u52a0<\/span>\r\nALTER<\/span> ROLE<\/span> <<\/span>\u30e6\u30fc\u30b6\u540d<\/span>><\/span> SET<\/span> log_statement<\/span> =<\/span> '<none|ddl|mod|all>'<\/span>;<\/span>\r\n#\u524a\u9664<\/span>\r\nALTER<\/span> ROLE<\/span> <<\/span>\u30e6\u30fc\u30b6\u540d<\/span>><\/span> RESET<\/span> log_statement<\/span> ;<\/span>\r\n#\u78ba\u8a8d<\/span>\r\nSELECT<\/span> *<\/span> FROM<\/span> pg_db_role_setting<\/span>;<\/span>\r\n<\/code><\/pre>\n
\u88dc\u5145\uff1a\u300cpg_db_role_setting\u300d\u662f\u5b58\u653e\u6570\u636e\u5e93\u548c\u89d2\u8272\u7684\u53c2\u6570\u8bbe\u7f6e\u4fe1\u606f\u7684\u7cfb\u7edf\u76ee\u5f55\u3002<\/p>\n
\u5c06CSV\u6570\u636e\u8f93\u5165\u7684\u65b9\u6cd5<\/h2>\n
\u867d\u7136\u53ef\u4ee5\u7b80\u5355\u5730\u6267\u884c\u63d2\u5165\u64cd\u4f5c\uff0c\u4f46\u662f PostgreSQL \u6709\u4e00\u4e2a\u4e13\u95e8\u7528\u4e8e\u5927\u91cf\u6570\u636e\u5bfc\u5165\u7684\u547d\u4ee4 COPY FROM\u3002COPY \u547d\u4ee4\u53ef\u4ee5\u4ece\u5916\u90e8\u8bfb\u53d6\u5236\u8868\u7b26\u5206\u9694(.tsv)\u6216\u9017\u53f7\u5206\u9694(.csv)\u7684\u6587\u4ef6\uff0c\u5e76\u4e14\u6bd4 INSERT \u66f4\u5feb\u3002
\n*\u5728\u521d\u59cb\u52a0\u8f7d\u65f6\uff0c\u901a\u5e38\u5728\u521b\u5efa\u7d22\u5f15\u4e4b\u524d\u52a0\u8f7d\u800c\u4e0d\u662f\u521b\u5efa\u7d22\u5f15\uff0c\u540e\u7eed\u518d\u6267\u884c CREATE INDEX \u7684\u901f\u5ea6\u66f4\u5feb\u3002
\n1. \u521b\u5efa\u8868 (\u4ec5\u5305\u62ec\u8868\u5b9a\u4e49\u548c CHECK \u7ea6\u675f)
\n2. \u4f7f\u7528 COPY FROM \u8fdb\u884c\u6570\u636e\u52a0\u8f7d
\n3. \u6dfb\u52a0\u7d22\u5f15\u3001\u5305\u542b\u7d22\u5f15\u7684\u7ea6\u675f\u548c\u5916\u952e\u7ea6\u675f<\/p>\n
COPY<\/span> <<\/span>tbl<\/span>\u540d<\/span>><\/span> FROM<\/span> 'CSV\u30d5\u30a1\u30a4\u30eb\u30d1\u30b9'<\/span> with<\/span> DELIMITER<\/span> ','<\/span> csv<\/span> header<\/span>;<\/span> \r\n\r\n\u203b<\/span>delimiter<\/span> '\u533a\u5207\u308a\u6587\u5b57'<\/span>  \u533a\u5207\u308a\u6587\u5b57\u3092\u8868\u73fe<\/span>\r\n\u203b<\/span>csv<\/span> header<\/span>             \u30d8\u30c3\u30c0\u30fc\u304c\u3042\u308b\u3053\u3068\u3092\u8868\u73fe<\/span>\r\n\r\n###RDS\u3067\u306f\u7ba1\u7406\u8005\u6a29\u9650(superuser)\u304c\u4f7f\u3048\u306a\u3044\u305f\u3081\u901a\u5e38\u306ecopy\u306f\u5229\u7528\u3067\u304d\u306a\u3044\u3002\u4e0b\u8a18\u3067\u4ee3\u66ff\u3002<\/span>\r\n\\<\/span>copy<\/span> source<\/span>-<\/span>table<\/span> from<\/span> 'test.csv'<\/span> with<\/span> DELIMITER<\/span> ','<\/span> csv<\/span> header<\/span>;<\/span> \r\n<\/code><\/pre>\n
\u2714\ufe0e\u5927\u89c4\u6a21\u751f\u6210\u6d4b\u8bd5\u6570\u636e\u7684\u6280\u5de7<\/h2>\n
\u4f5c\u4e3a\u5907\u5fd8\u5f55\uff0c\u8bb0\u5f55\u5728PostgreSQL\u4e2d\u521b\u5efa\u5927\u91cf\u6d4b\u8bd5\u6570\u636e\u7684\u65b9\u6cd5\u3002\u56e0\u4e3a\u7ecf\u5e38\u9700\u8981\u6570\u636e\u6765\u8fdb\u884c\u9a8c\u8bc1\u3002<\/p>\n
    generate_series\u95a2\u6570\u3092\u5229\u7528<\/ul>\n
    ---<\/span>10000000<\/span>\u4ef6\u306e\u30c6\u30b9\u30c8\u30c7\u30fc\u30bf\u4f5c\u6210\u3002\u51e1\u305d<\/span>600<\/span>M<\/span>\u7a0b\u5ea6\u306e\u5927\u304d\u3055\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u306a\u308b<\/span>\r\ncreate<\/span> table<\/span> tbl1<\/span> (<\/span>col1<\/span> integer<\/span>,<\/span>col2<\/span> char<\/span>(<\/span>20<\/span>));<\/span>\r\ninsert<\/span> into<\/span>  tbl1<\/span> select<\/span> generate_series<\/span>(<\/span>1<\/span>,<\/span>10000000<\/span>),<\/span>'XXXXXXXXXXXXXXXXXXXX'<\/span>;<\/span>\r\n<\/code><\/pre>\n
    \u8ba9CPU\u4fdd\u6301\u9ad8\u6548\u5de5\u4f5c\u7684SQL\u6280\u5de7<\/h1>\n
    \u4e3a\u4e86\u5728\u6d4b\u8bd5\u7b49\u573a\u666f\u4e0b\u5c06CPU\u5229\u7528\u7387\u4fdd\u6301\u5728\u6700\u9ad8\u6c34\u5e73\uff0c\u4f7f\u7528PostgreSQL\u7684SQL\u8bed\u53e5<\/p>\n
      \n
    • \n
        generate_series\u95a2\u6570\u3092\u5229\u7528<\/ul>\n<\/li>\n<\/ul>\n
         <\/p>\n
          \u4ea4\u5dee\u7d50\u5408\u3092\u5229\u7528<\/ul>\n
          \u7531\u65bc\u5728\u540c\u4e00\u500b\u8868\u4e2d\u5c07\u6bcf\u4e00\u884c\u8207\u8a72\u8868\u7684\u6240\u6709\u884c\u9032\u884c\u7d50\u5408\uff0c\u6240\u4ee5\u884c\u6578\u8d8a\u591a\uff0c\u7d50\u5408\u8655\u7406\u5c31\u6703\u8b8a\u5f97\u76f8\u7576\u8907\u96dc\u3002\u7d50\u5408\u5f8c\u7684\u884c\u6578\uff0c\u5c31\u7c21\u55ae\u5730\u8b8a\u70ba\u539f\u59cb\u8868\u683c\u7684\u5e73\u65b9\u3002\u5982\u679c\u8981\u4f7f\u7528\u591a\u500b\u6838\u5fc3\u9032\u884c\u52a0\u901f\uff0c\u53ea\u9700\u518d\u57f7\u884c\u985e\u4f3c\u7684SQL\u8a9e\u53e5\u5373\u53ef\u3002<\/p>\n
          --<\/span>10000000<\/span>\u4ef6\u306e\u30c6\u30b9\u30c8\u30c7\u30fc\u30bf\u3092\u81ea\u30c6\u30fc\u30d6\u30eb\u3068\u4ea4\u5dee\u7d50\u5408\u3055\u305b\u308b\u3002<\/span>\r\ncreate<\/span> table<\/span> tbl1<\/span> (<\/span>col1<\/span> integer<\/span>,<\/span>col2<\/span> char<\/span>(<\/span>20<\/span>));<\/span>\r\ninsert<\/span> into<\/span>  tbl1<\/span> select<\/span> generate_series<\/span>(<\/span>1<\/span>,<\/span>10000000<\/span>),<\/span>'XXXXXXXXXXXXXXXXXXXX'<\/span>;<\/span>\r\nselect<\/span> count<\/span>(<\/span>*<\/span>)<\/span> from<\/span> tbl1<\/span> x<\/span>,<\/span>tbl1<\/span> y<\/span>;<\/span>\r\n<\/code><\/pre>\n
          \u8c03\u67e5\u5bb9\u91cf<\/h1>\n
          \u65e2\u7136\u5728\u8fd0\u8425\u6570\u636e\u5e93\uff0c\u5bf9\u4e8e\u8868\u7684\u5927\u5c0f\u548c\u8bb0\u5f55\u6570\uff0c\u4ee5\u53ca\u6570\u636e\u5e93\u672c\u8eab\u7684\u5927\u5c0f\u8fdb\u884c\u7edf\u8ba1\u662f\u7ecf\u5e38\u9700\u8981\u7684\u3002\u5173\u4e8e PostgreSQL\uff0c\u4ee5\u4e0b\u662f\u4e00\u79cd\u8c03\u67e5\u65b9\u6cd5\u3002<\/p>\n
          --<\/span>\u30c7\u30fc\u30bf\u30d6\u30ed\u30c3\u30af\u306e\u30b5\u30a4\u30ba\u3092\u78ba\u8a8d<\/span>\r\nshow<\/span> block_size<\/span>;<\/span>\r\n\r\n--<\/span>\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u306e\u30b5\u30a4\u30ba\u964d\u9806<\/span>\r\nSELECT<\/span> relowner<\/span>,<\/span>relname<\/span>,<\/span>reltuples<\/span> as<\/span> rows<\/span>,(<\/span>relpages<\/span>::<\/span>int8<\/span> *<\/span> 8192<\/span>)<\/span> as<\/span> bytes<\/span> FROM<\/span> pg_class<\/span> order<\/span> by<\/span> bytes<\/span> desc<\/span> ;<\/span>\r\n\r\n--<\/span>\u5404\u30c6\u30fc\u30d6\u30eb\u306e\u30b5\u30a4\u30ba<\/span>(<\/span>byte<\/span>)<\/span>\u3068\u4ef6\u6570\u3092\u51fa\u529b\u3002\u4e0a\u8a18\u7d50\u679c\u3092<\/span>relpages<\/span>\u306e\u4e57\u6570\u3068\u3057\u3066\u5165\u308c\u308b\u3002<\/span>\r\nSELECT<\/span> relowner<\/span>,<\/span>relname<\/span>,<\/span>reltuples<\/span> as<\/span> rows<\/span>,(<\/span>relpages<\/span>::<\/span>int8<\/span> *<\/span> 8192<\/span>)<\/span> as<\/span> bytes<\/span> FROM<\/span> pg_class<\/span> WHERE<\/span> relname<\/span> =<\/span> '\u30c6\u30fc\u30d6\u30eb\u540d'<\/span>\r\n<\/code><\/pre>\n
          SELECT<\/span> datname<\/span>,<\/span> pg_size_pretty<\/span>(<\/span>pg_database_size<\/span>(<\/span>datname<\/span>))<\/span> FROM<\/span> pg_database<\/span>;<\/span>\r\n<\/code><\/pre>\n
          \u8bf7\u53c2\u8003\u4ee5\u4e0b\u94fe\u63a5\u6765\u4e86\u89e3\u66f4\u591a\u4fe1\u606f\uff1a
          \nhttps:\/\/www.postgresql.jp\/document\/10\/html\/catalog-pg-class.html<\/p>\n
          \u5e8f\u5217\u63a7\u5236<\/h1>\n
          \u5728PostgreSQL\u4e2d\u4f7f\u7528\u7b80\u5355\u987a\u5e8f\u53f7\u7684\u65b9\u6cd5\u3002\u4f8b\u5982\uff0c\u53ef\u4ee5\u7528\u4e8e\u5bf9\u8868\u7684ID\u5217\u8fdb\u884c\u7f16\u53f7\u3002
          \n\u4e0eMySQL\u4e0d\u540c\uff0c\u6ca1\u6709auto_increment\u8fd9\u6837\u7684\u5217\u5b9a\u4e49\u8bed\u6cd5\u3002<\/p>\n
          \/* PostgreSQL\u306e\u5834\u5408 *\/<\/span>\r\nDROP<\/span> SEQUENCE<\/span> t_id_seq<\/span>;<\/span>\r\nCREATE<\/span> SEQUENCE<\/span> t_id_seq<\/span>\r\n    START<\/span> WITH<\/span> 1<\/span>\r\n    INCREMENT<\/span> BY<\/span> 1<\/span>\r\n    NO<\/span> MAXVALUE<\/span>\r\n    NO<\/span> MINVALUE<\/span>\r\n    CACHE<\/span> 1<\/span>;<\/span>\r\n\r\nDROP<\/span> TABLE<\/span> product<\/span>;<\/span>\r\nCREATE<\/span> TABLE<\/span> product<\/span> (<\/span>\r\n    id<\/span> int<\/span> DEFAULT<\/span> nextval<\/span>(<\/span>'t_id_seq'<\/span>)<\/span> PRIMARY<\/span> KEY<\/span> NOT<\/span> NULL<\/span>,<\/span>\r\n    name<\/span> text<\/span> not<\/span> null<\/span>,<\/span>\r\n    price<\/span> int<\/span> not<\/span> null<\/span>\r\n);<\/span>\r\n<\/code><\/pre>\n
          ##\u30bb\u30c3\u30b7\u30e7\u30f3\u5185\u3067\u306e\u76f4\u8fd1\u306e\u30b7\u30fc\u30b1\u30f3\u30b9\u5024\u3092\u78ba\u8a8d<\/span>\r\nselect<\/span> currval<\/span>(<\/span>'t_id_seq'<\/span>);<\/span>\r\n\r\n##\u6b21\u306b\u767a\u884c\u3055\u308c\u308b\u30b7\u30fc\u30b1\u30f3\u30b9\u5024\u3092\u78ba\u8a8d(\u5b9f\u969b\u306e\u30b7\u30fc\u30b1\u30f3\u30b9\u5024\u3082\u66f4\u65b0\u3055\u308c\u308b)<\/span>\r\nselect<\/span> nextval<\/span>(<\/span>'t_id_seq'<\/span>);<\/span>\r\n\r\n<\/code><\/pre>\n
          ##\u30b7\u30fc\u30b1\u30f3\u30b9\u5024\u3092\u30ea\u30bb\u30c3\u30c8(\u73fe\u5728\u306e\u30c6\u30fc\u30d6\u30eb\u306eID\u5217\u306eMAX\u5024\u306bcurrval\u3092\u30bb\u30c3\u30c8\u3059\u308b<\/span>\r\nSELECT<\/span> setval<\/span>(<\/span>'t_id_seq'<\/span>,<\/span> (<\/span>SELECT<\/span> MAX<\/span>(<\/span>id<\/span>)<\/span> FROM<\/span> product<\/span>));<\/span>\r\n<\/code><\/pre>\n
          SSL\u8fde\u63a5<\/h1>\n
          \u4e3a\u4e86\u52a0\u5bc6\u4e0ePostgresql\u7684\u8fde\u63a5\uff0c\u5fc5\u987b\u8fdb\u884c\u8bbe\u7f6e\u3002
          \n\u5982\u679c\u6ca1\u6709\u52a0\u5bc6\uff0c\u4e0e\u6570\u636e\u5e93\u8fdb\u884c\u901a\u4fe1\u7684\u6570\u636e\u5c06\u4ee5\u660e\u6587\u5f62\u5f0f\u4f20\u8f93\uff0c\u56e0\u6b64\u5efa\u8bae\u8fdb\u884c\u5b89\u5168\u63aa\u65bd\u3002\u4ec5\u4ec5\u662f\u7f51\u7edc\u901a\u4fe1\u7684\u52a0\u5bc6\u3002<\/p>\n
            \n
          • \n
              \u307e\u305a\u306fSSL\u8a3c\u660e\u66f8\u3092\u30b5\u30fc\u30d0\u5074(DB\u5074)\u3067\u4f5c\u6210\u3059\u308b\u3053\u3068\u304c\u5fc5\u8981<\/ul>\n<\/li>\n<\/ul>\n
              SSL\u79d8\u5bc6\u9375\u3001CRS\u3001\u8a3c\u660e\u66f8\u3092\u4f5c\u6210
              \nSSL\u79d8\u5bc6\u9375\u306e\u30d1\u30b9\u30ef\u30fc\u30c9\u306f\u524a\u9664\u3002\u3053\u308c\u3092\u5b9f\u65bd\u3057\u306a\u3044\u3068\u3001\u8d77\u52d5\u6642\u306b\u6bce\u56de\u30d1\u30b9\u30ef\u30fc\u30c9\u3092\u805e\u304b\u308c\u308b<\/p>\n
              #\u30b5\u30fc\u30d0\u79d8\u5bc6\u9375\u306e\u4f5c\u6210<\/span>\r\n-<\/span>RSA<\/span>\u5f62\u5f0f\u3001<\/span>256<\/span>bit<\/span>\u306e<\/span>AES<\/span>\u5f62\u5f0f\u3001\u304b\u3064<\/span>2048<\/span>bit<\/span>\u306e\u9375\u9577\u3067\u4f5c\u6210<\/span>\r\nopenssl<\/span> genrsa<\/span> -<\/span>aes256<\/span> 2048<\/span> ><\/span> server<\/span>.<\/span>key<\/span>\r\n\r\n#CSR\u306e\u4f5c\u6210<\/span>\r\nreq<\/span> CSR<\/span>\u30d5\u30a1\u30a4\u30eb\u4f5c\u6210<\/span>\r\n-<\/span>new<\/span> \u65b0\u898f\u306b<\/span>CSR<\/span>\u4f5c\u6210<\/span>\r\n-<\/span>key<\/span> \u79d8\u5bc6\u9375\u30d5\u30a1\u30a4\u30eb\u6307\u5b9a<\/span>\r\nopenssl<\/span> req<\/span> -<\/span>new<\/span> -<\/span>key<\/span> server<\/span>.<\/span>key<\/span> ><\/span> server<\/span>.<\/span>csr<\/span>\r\n\r\n#\u30b5\u30fc\u30d0\u8a3c\u660e\u66f8\u4f5c\u6210<\/span>\r\nx509<\/span>           X<\/span>.<\/span>509<\/span>\u5f62\u5f0f\u8a3c\u660e\u66f8<\/span>\r\n-<\/span>in<\/span> CSR<\/span>\u30d5\u30a1\u30a4\u30eb<\/span>  CSR<\/span>\u30d5\u30a1\u30a4\u30eb\u6307\u5b9a<\/span>\r\n-<\/span>days<\/span>          \u8a3c\u660e\u66f8\u6709\u52b9\u671f\u9650<\/span>\r\n-<\/span>req<\/span>           \u5165\u529b\u30d5\u30a1\u30a4\u30eb\u304c<\/span>CSR<\/span>\u3067\u3042\u308b\u3053\u3068\u660e\u793a<\/span>\r\n-<\/span>signkey<\/span>\u79d8\u5bc6\u9375<\/span>  \u79d8\u5bc6\u9375\u30d5\u30a1\u30a4\u30eb\u6307\u5b9a<\/span>      \r\nopenssl<\/span> x509<\/span> -<\/span>in<\/span> server<\/span>.<\/span>csr<\/span> -<\/span>days<\/span> 3650<\/span> -<\/span>req<\/span> -<\/span>signkey<\/span> server<\/span>.<\/span>key<\/span> ><\/span> server<\/span>.<\/span>crt<\/span>\r\n\r\n Tips<\/span>:<\/span>\u30b5\u30fc\u30d0\u79d8\u5bc6\u9375\u304b\u3089\u30d1\u30b9\u30d5\u30ec\u30fc\u30ba\u524a\u9664<\/span>\r\ncp<\/span> -<\/span>p<\/span> server<\/span>.<\/span>key<\/span> server<\/span>.<\/span>key_org<\/span>\r\nopenssl<\/span> rsa<\/span> -<\/span>in<\/span> server<\/span>.<\/span>key_org<\/span> ><\/span> server<\/span>.<\/span>key<\/span>\r\n<\/code><\/pre>\n
              \u63a5\u4e0b\u6765\uff0c\u5c06\u751f\u6210\u7684\u79c1\u94a5\u548c\u8bc1\u4e66\u6ce8\u518c\u5230conf\u4e2d\u3002\u6587\u4ef6\u7684\u8def\u5f84\u53ef\u4ee5\u662f\u76f8\u5bf9\u4e8e\u6570\u636e\u76ee\u5f55\u7684\u76f8\u5bf9\u8def\u5f84\u3002<\/p>\n
              ssl = ‘on’
              \nssl_cert_file = ‘ssl\/server.crt’
              \nssl_key_file = ‘ssl\/server.key’
              \nssl_ciphers = ‘ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384’<\/p>\n
              SSL\u5f00\u542f
              \nSSL\u8bc1\u4e66\u6587\u4ef6 = ‘ssl\/server.crt’
              \nSSL\u5bc6\u94a5\u6587\u4ef6 = ‘ssl\/server.key’
              \nSSL\u5bc6\u7801\u5957\u4ef6 = ‘ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384’<\/p>\n
              \u8bf7\u95eeSSL\u52a0\u5bc6\u534f\u8bae\u5e94\u8be5\u6839\u636e\u8fd9\u4e2a\u63a8\u8350\u8fdb\u884c\u8bbe\u7f6e\u5417\uff1f
              \nhttps:\/\/wiki.mozilla.org\/Security\/Server_Side_TLS#Recommended_configurations
              \nPostgreSQL\u7684psql\u9ed8\u8ba4\u4f7f\u7528SSL\u3002\u5982\u679c\u670d\u52a1\u5668\u4e0d\u652f\u6301SSL\uff0c\u5219\u4f1a\u4f7f\u7528\u975eSSL\u8fde\u63a5\u3002\u5982\u679c\u9700\u8981\u5f3a\u5236\u7981\u7528SSL\uff0c\u5219\u9700\u5c06PGSSLMODE=disable\u8bbe\u7f6e\u4e3a\u73af\u5883\u53d8\u91cf\u3002<\/p>\n
              \u203b\u4f7f\u7528SSL\u8fde\u63a5\u65f6\uff0c\u5c06\u4f1a\u663e\u793a\u4e0eSSL\u8fde\u63a5\u76f8\u5173\u7684\u4fe1\u606f\u5982\u4e0b\u3002<\/p>\n
              [root@vm1 ~]# psql -U pguser01 -h db1 db01<\/p>\n
              \u4f7f\u7528pguser01\u7528\u6237\u901a\u8fc7TLSv1.2\u534f\u8bae\u548cECDHE-RSA-AES256-GCM-SHA384\u52a0\u5bc6\u65b9\u5f0f\u8fde\u63a5\u5230db1\u670d\u52a1\u5668\u3002
              \n\u6570\u636e\u5e93db01\u5df2\u6210\u529f\u8fde\u63a5\u3002
              \n\u8bf7\u8f93\u5165\u547d\u4ee4\u6216\u8f93\u5165”help”\u83b7\u53d6\u5e2e\u52a9\u3002
              \ndb01=><\/p><\/blockquote>\n
              \u4e8b\u52a1\u9694\u79bb\u7ea7\u522b<\/h1>\n
              \u76f4\u767d\u5730\u8bf4\uff0c\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b\u662f\u6307\u5728\u4fdd\u6301\u5173\u7cfb\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf\uff08RDBMS\uff09\u53ef\u9760\u6027\uff08ACID\uff09\u7684\u7279\u6027\u4e2d\uff0c\u5bb9\u8bb8\u591a\u5c11\u7a0b\u5ea6\u7684\u6570\u636e\u4e0d\u4e00\u81f4\u95ee\u9898\u3002<\/p>\n
              \u9694\u79bb\u6027\u662f\u6307\u5728\u4e8b\u52a1\u8fc7\u7a0b\u4e2d\u7684\u64cd\u4f5c\u4e0d\u4f1a\u5bf9\u5176\u4ed6\u4e8b\u52a1\u4ea7\u751f\u5f71\u54cd\u7684\u7279\u6027\u3002\u6362\u53e5\u8bdd\u8bf4\uff0c\u6bcf\u4e2a\u4e8b\u52a1\u5fc5\u987b\u5728\u5206\u79bb\u7684\u72b6\u6001\u4e0b\u8fdb\u884c\u64cd\u4f5c\u3002<\/p>\n
              \u5982\u679c\u4e0d\u9075\u5b88\u8fd9\u4e00\u70b9\uff0c\u53ef\u80fd\u4f1a\u53d1\u751f\u4ee5\u4e0b\u7c7b\u4f3c\u7684\u6570\u636e\u4e0d\u4e00\u81f4\u60c5\u51b5\u3002\u6b63\u5982\u5f00\u5934\u6240\u8ff0\uff0c\u5b9a\u4e49\u4e86\u5728\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b\u4e2d\u5bb9\u8bb8\u591a\u5c11\u8fd9\u79cd\u95ee\u9898\u7684\u7a0b\u5ea6\u3002<\/p>\n
              -<\/span>\u30c0\u30fc\u30c6\u30a3\u30ea\u30fc\u30c9<\/span> (<\/span>Dirty<\/span> Read<\/span>)<\/span>\r\n\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3<\/span>B<\/span>\u3067\u30b3\u30df\u30c3\u30c8\u3055\u308c\u3066\u3044\u306a\u3044\u30c7\u30fc\u30bf\u3092\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3<\/span>A<\/span>\u3067\u8aad\u307f\u53d6\u3063\u3066\u3057\u307e\u3046\u554f\u984c\u304c\u767a\u751f\u3059\u308b\u3002<\/span>\r\n\r\n-<\/span> \u53cd\u5fa9\u4e0d\u80fd\u8aad\u307f\u53d6\u308a<\/span>\/<\/span>\u30d5\u30a1\u30b8\u30fc\u30ea\u30fc\u30c9<\/span>\/<\/span>\u30ce\u30f3\u30ea\u30d4\u30fc\u30bf\u30d6\u30eb\u30ea\u30fc\u30c9<\/span> (<\/span>Fuzzy<\/span> Read<\/span> \/<\/span> Non<\/span>-<\/span>Repeatable<\/span> Read<\/span>)<\/span>\r\n\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3<\/span>A<\/span>\u3067\u30c7\u30fc\u30bf\u3092\u8907\u6570\u56de\u8aad\u307f\u53d6\u3063\u3066\u3044\u308b\u9014\u4e2d\u3067\u3001\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3<\/span>B<\/span>\u3067\u30c7\u30fc\u30bf\u3092\u66f4\u65b0<\/span>(<\/span>update<\/span>)<\/span>\u3057\u3066\u30b3\u30df\u30c3\u30c8\u3057\u305f\u5834\u5408\u3001\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3<\/span>A<\/span>\u3067\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u5185\u306e\u524d\u56de\u3068\u9055\u3046\u7d50\u679c\u306e\u30c7\u30fc\u30bf\u3092\u8aad\u307f\u53d6\u3063\u3066\u3057\u307e\u3046\u554f\u984c\u304c\u767a\u751f<\/span>\r\n\r\n-<\/span> \u30d5\u30a1\u30f3\u30c8\u30e0\u30ea\u30fc\u30c9<\/span> (<\/span>Phantom<\/span> Read<\/span>)<\/span>\r\n\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3<\/span>A<\/span>\u3067\u4e00\u5b9a\u306e\u7bc4\u56f2\u306e\u30ec\u30b3\u30fc\u30c9\u306b\u5bfe\u3057\u3066\u51e6\u7406\u3092\u884c\u3063\u3066\u3044\u308b\u9014\u4e2d\u3067\u3001\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3<\/span>B<\/span>\u3067\u30c7\u30fc\u30bf\u3092\u8ffd\u52a0<\/span>(<\/span>insert<\/span>)<\/span>\u30fb\u524a\u9664<\/span>(<\/span>delete<\/span>)<\/span>\u3057\u3066\u30b3\u30df\u30c3\u30c8\u3057\u305f\u5834\u5408\u3001\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3<\/span>A<\/span>\u3067\u5e7b\u5f71\u306e\u3088\u3046\u306b\u30c7\u30fc\u30bf\u304c\u53cd\u6620\u3055\u308c\u308b\u305f\u3081\u3001\u51e6\u7406\u306e\u7d50\u679c\u304c\u5909\u308f\u3063\u3066\u3057\u307e\u3046\u554f\u984c\u304c\u767a\u751f\u3002<\/span>\r\n\r\n<\/code><\/pre>\n
              \u5c3d\u7ba1\u4e00\u5f00\u59cb\u5f88\u96be\u533a\u5206\u4e0d\u53ef\u91cd\u590d\u8bfb\u4e0e\u5e7b\u8bfb\u7684\u533a\u522b\uff0c\u4f46\u524d\u8005\u4e3b\u8981\u5173\u6ce8\u540c\u4e00\u4e8b\u52a1\u5185\u8bb0\u5f55\u503c\u7684\u4e0d\u4e00\u81f4\u6027\u95ee\u9898\uff0c\u800c\u540e\u8005\u9664\u6b64\u4e4b\u5916\u8fd8\u5173\u6ce8\u8bb0\u5f55\u6570\u91cf\u7684\u4e00\u81f4\u6027\u95ee\u9898\u3002<\/p>\n
              \u5728Postgresql\u4e2d\uff0c\u53ef\u4ee5\u6839\u64da\u61c9\u7528\u7a0b\u5f0f\u7684\u9700\u6c42\u4f86\u9078\u64c7\u4ee5\u4e0b\u5206\u96e2\u5c64\u7d1a\u4f86\u61c9\u5c0d\u4e0a\u8ff0\u554f\u984c\u3002\u5206\u96e2\u5c64\u7d1a\u8d8a\u4f4e\uff0c\u53ef\u80fd\u6703\u51fa\u73fe\u8655\u7406\u5e8f\u5217\u5931\u6557\u7684\u60c5\u6cc1\uff0c\u56e0\u6b64\u9700\u8981\u8003\u616e\u5931\u6557\u4e8b\u52d9\u7684\u91cd\u65b0\u57f7\u884c\u7b49\u6062\u5fa9\u63aa\u65bd\u3002\u5927\u591a\u6578\u60c5\u6cc1\u4e0b\uff0c\u4f7f\u7528Read Commited\u53ef\u4ee5\u6eff\u8db3\u61c9\u7528\u7a0b\u5f0f\u7684\u9700\u6c42\u3002\u6b64\u5916\uff0c\u9084\u53ef\u4ee5\u901a\u904e\u5728\u6bcf\u500b\u4e8b\u52d9\u4e2d\u4f7f\u7528BEGIN TRANSACTION ISOLATION LEVEL\u5b50\u53e5\u4f86\u6307\u5b9a\u5206\u96e2\u5c64\u7d1a\uff0c\u9019\u6a23\u5c31\u53ef\u4ee5\u5728\u5b58\u5728\u6578\u64da\u5eab\u9ed8\u8a8d\u7684\u60c5\u6cc1\u4e0b\uff0c\u70ba\u7279\u5b9a\u7684\u4e8b\u52d9\u9032\u884c\u66f4\u6539\u4ee5\u9069\u61c9\u9700\u6c42\u3002<\/p>\n
              \n
              \u5206\u96e2\u30ec\u30d9\u30eb\u610f\u5473\u6291\u6b62\u53ef\u80fd\u306a\u5206\u96e2\u30ec\u30d9\u30eb\u30ea\u30fc\u30c9\u30a2\u30f3\u30b3\u30df\u30c3\u30c6\u30a3\u30c9(Read UNCOMMITED)\u5225\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u3067\u30b3\u30df\u30c3\u30c8\u3055\u308c\u3066\u3044\u306a\u3044\u30c7\u30fc\u30bf\u3082\u53c2\u7167\u3057\u3066\u3057\u307e\u3046-\u30ea\u30fc\u30c9\u30b3\u30df\u30c3\u30c6\u30a3\u30c9(Read Commitd)\u554f\u3044\u5408\u308f\u305b(\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u5185\u306e\u5404SELECT)\u304c\u5b9f\u884c\u3055\u308c\u308b\u76f4\u524d\u307e\u3067\u306b\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u5185\u3067\u30b3\u30df\u30c3\u30c8\u3055\u308c\u305f\u30c7\u30fc\u30bf\u3092\u53c2\u7167\u3059\u308b(PostgreSQL\u3067\u306f\u30c7\u30d5\u30a9\u30eb\u30c8)\u30c0\u30fc\u30c6\u30a3\u30ea\u30fc\u30c9\u30ea\u30d4\u30fc\u30bf\u30d6\u30eb\u30ea\u30fc\u30c9(Repeatable Read)\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u304c\u958b\u59cb\u3055\u308c\u308b\u76f4\u524d\u307e\u3067\u306b\u30b3\u30df\u30c3\u30c8\u3055\u308c\u305f\u30c7\u30fc\u30bf\u3092\u53c2\u7167\u3059\u308b\u3002\u5358\u4e00\u3055\u308c\u305f\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u5185\u306e\u9023\u7d9a\u306eSELECT\u306f\u5e38\u306b\u540c\u3058\u7d50\u679c\u3092\u8fd4\u3059\u53cd\u5fa9\u4e0d\u80fd\u8aad\u307f\u53d6\u308a\u30b7\u30ea\u30a2\u30e9\u30a4\u30b6\u30d6\u30eb(Serializable)\u3082\u3063\u3068\u3082\u53b3\u3057\u3044\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u5206\u96e2\u30ec\u30d9\u30eb\u3002\u4e26\u5217\u5b9f\u884c\u3055\u308c\u305f\u8907\u6570\u306e\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u306e\u5b9f\u884c\u3067\u3042\u3063\u3066\u3082\u9010\u6b21\u7684\u306b\u6271\u308f\u308c\u305f\u3082\u306e\u3068\u540c\u3058\u7d50\u679c\u3092\u8fd4\u3059\u3002\u8981\u3059\u308b\u306b\u3001\u5404\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u304c1\u3064\u305a\u3064\u9806\u756a\u306b\u884c\u308f\u308c\u3066\u3044\u308b\u72b6\u614b\u3092\u524d\u63d0\u3068\u3057\u3066\u7d50\u679c\u3092\u8fd4\u3059\u30d5\u30a1\u30f3\u30c8\u30e0\u30ea\u30fc\u30c9<\/div>\n<\/div>\n
              \"image.png\"<\/div>\n
              \u2714\ufe0f\u5907\u4efd<\/h2>\n
              \u5173\u4e8e\u5728PostgreSQL\u4e2d\u5b9e\u73b0\u6570\u636e\u5907\u4efd\u7684\u65b9\u6cd5\u3002
              \n\u4f5c\u4e3a\u5de5\u5177\uff0c\u53ef\u4ee5\u4f7f\u7528pg_dump\u548cpg_dumpall\u3002<\/p>\n
              \u6700\u5927\u3067\u3001\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u5358\u4f4d\u3067\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u3059\u308b\u30e6\u30fc\u30c6\u30a3\u30ea\u30c6\u30a3\u3002<\/span>\r\n\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3092\u4f7f\u7528\u4e2d\u3067\u3042\u3063\u3066\u3082\u4e00\u8cab\u6027\u306e\u3042\u308b\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u3092\u4f5c\u6210\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u308b\u3002<\/span> pg_dump<\/span>\u306f\u4ed6\u306e\u30e6\u30fc\u30b6\u306b\u3088\u308b\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3078\u306e\u30a2\u30af\u30bb\u30b9\uff08\u8aad\u307f\u66f8\u304d\uff09\u3092\u30d6\u30ed\u30c3\u30af\u3057\u307e\u305b\u3093\u3002\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u306e\u7bc4\u56f2\u306f\u7d30\u304b\u304f\u30aa\u30d7\u30b7\u30e7\u30f3\u3067\u6307\u5b9a\u304c\u53ef\u80fd\u3002<\/span>\r\nhttps<\/span>:\/<\/span>\/<\/span>www<\/span>.<\/span>postgresql<\/span>.<\/span>jp<\/span>\/<\/span>document<\/span>\/<\/span>10<\/span>\/<\/span>html<\/span>\/<\/span>app<\/span>-<\/span>pgdump<\/span>.<\/span>html<\/span>\r\nhttps<\/span>:\/<\/span>\/<\/span>www<\/span>.<\/span>postgresql<\/span>.<\/span>jp<\/span>\/<\/span>document<\/span>\/<\/span>11<\/span>\/<\/span>html<\/span>\/<\/span>app<\/span>-<\/span>pgrestore<\/span>.<\/span>html<\/span>\r\n<\/code><\/pre>\n
              1) \u7eaf\u6587\u672c\u683c\u5f0f
              \n\u521b\u5efa\u4f7f\u7528\u7eaf\u6587\u672c\u7684SQL\u811a\u672c\u3002\u8fd9\u662f\u9ed8\u8ba4\u64cd\u4f5c\u3002\u6062\u590d\u64cd\u4f5c\u53ef\u4ee5\u4f7f\u7528psql\u547d\u4ee4\u6267\u884c\u3002
              \n\u4f8b\u5982\uff0c\u53ef\u4ee5\u6267\u884c\u5982\u4e0b\u793a\u4f8b\u3002<\/p>\n
              pg_dump<\/span> -<\/span>v<\/span> --<\/span>format<\/span>=<\/span>plain<\/span> -<\/span>h<\/span> kai1<\/span> -<\/span>U<\/span> pguser01<\/span> testdb<\/span> mydb<\/span> ><\/span> mydb_dump<\/span>.<\/span>sql<\/span>\r\n<\/code><\/pre>\n
              2) \u81ea\u5b9a\u4e49\u5b58\u6863\u683c\u5f0f\uff08\u63a8\u8350\uff09
              \n\u521b\u5efa\u4e8c\u8fdb\u5236\u683c\u5f0f\u7684\u5907\u4efd\u3002
              \n\u8981\u6062\u590d\u4f7f\u7528\u8be5\u683c\u5f0f\u521b\u5efa\u7684\u5907\u4efd\uff0c\u9700\u8981\u4f7f\u7528 pg_restore \u547d\u4ee4\u3002
              \n\u4f18\u70b9\u662f\u53ef\u4ee5\u538b\u7f29\uff0c\u5360\u7528\u7a7a\u95f4\u8f83\u5c11\u3002\u6b64\u5916\uff0c\u53ef\u4ee5\u4ec5\u5206\u79bb\u6240\u9700\u7684\u8868\u548c\u6a21\u5f0f\u5b9a\u4e49\u8fdb\u884c\u6062\u590d\u3002
              \n\u4f8b\u5982\uff0c\u53ef\u6309\u4ee5\u4e0b\u65b9\u5f0f\u6267\u884c\u3002<\/p>\n
              #\u30d0\u30c3\u30af\u30a2\u30c3\u30d7<\/span>\r\npg_dump<\/span> -<\/span>v<\/span> --<\/span>format<\/span>=<\/span>custom<\/span> -<\/span>h<\/span> kai1<\/span> -<\/span>U<\/span> pguser01<\/span> testdb<\/span> mydb<\/span> ><\/span> mydb_dump<\/span>.<\/span>custom<\/span>\r\n#\u30ea\u30b9\u30c8\u30a2(\u30d5\u30eb\u30ea\u30b9\u30c8\u30a2)<\/span>\r\npg_restore<\/span> -<\/span>C<\/span> -<\/span>d<\/span> postgres<\/span> <<\/span> mydb_dump<\/span>.<\/span>tar<\/span>\r\n<\/code><\/pre>\n
              \u521b\u5efa\u53ef\u4ee5\u5728pg_restore\u4e2d\u8fdb\u884c\u6062\u590d\u7684\u5907\u4efd\u6587\u4ef6\uff0c\u7c7b\u4f3c\u4e8e\u81ea\u5b9a\u4e49\u5b58\u6863\u6587\u4ef6\u683c\u5f0f\u3002
              \n\u4f18\u70b9\u662f\u5728\u67e5\u770btar\u6587\u4ef6\u5185\u5bb9\u65f6\u53ef\u4ee5\u4e86\u89e3\u64cd\u4f5c\u7cfb\u7edf\u4e2d\u5b58\u50a8\u7684\u8868\u7ed3\u6784\u7b49\u4fe1\u606f\u3002
              \n\u8981\u6062\u590d\u4f7f\u7528\u6b64\u683c\u5f0f\u521b\u5efa\u7684\u5907\u4efd\uff0c\u9700\u8981\u4f7f\u7528pg_restore\u547d\u4ee4\u3002
              \n\u4f8b\u5982\uff0c\u53ef\u4ee5\u6267\u884c\u4ee5\u4e0b\u547d\u4ee4\u4f5c\u4e3a\u793a\u4f8b\u3002<\/p>\n
              #\u30d0\u30c3\u30af\u30a2\u30c3\u30d7<\/span>\r\npg_dump<\/span> --<\/span>format<\/span>=<\/span>tar<\/span> -<\/span>h<\/span> kai1<\/span> -<\/span>U<\/span> pguser01<\/span> testdb<\/span> mydb<\/span> ><\/span> mydb_dump<\/span>.<\/span>tar<\/span>\r\n\r\n#\u30ea\u30b9\u30c8\u30a2(\u30d5\u30eb\u30ea\u30b9\u30c8\u30a2)<\/span>\r\npg_restore<\/span> -<\/span>C<\/span> -<\/span>d<\/span> postgres<\/span> <<\/span> mydb_dump<\/span>.<\/span>tar<\/span>\r\n\r\n<\/code><\/pre>\n
              \u2714\ufe0f\u9501\u4f4f (Su\u01d2 zh\u00f9)<\/h2>\n
              \u8868\u7ea7\u9501<\/h3>\n
              PostgreSQL\u53ef\u4ee5\u4e3a\u8868\u83b7\u53d6\u660e\u786e\u7684\u9501\uff0c\u4ee5\u786e\u4fdd\u4e8b\u52a1\u7684\u5e76\u53d1\u6267\u884c\u3002<\/p>\n
              \"image.png\"<\/div>\n
              \u884c\u7ea7\u9501<\/h3>\n
              \u884c\u7ea7\u9501\u4e0d\u4f1a\u5bf9\u6570\u636e\u67e5\u8be2\u4ea7\u751f\u5f71\u54cd\u3002
              \n\u884c\u7ea7\u9501\u4ec5\u4f1a\u963b\u585e\u540c\u4e00\u884c\u7684\u5199\u5165\u548c\u9501\u5b9a\u64cd\u4f5c\u3002\u6bd4\u5982\uff0c\u5728\u6267\u884cUPDATE\u8bed\u53e5\u65f6\uff0c\u66f4\u65b0\u4f4d\u4e8e\u540c\u4e00\u884c\u7684\u6570\u636e\u4f1a\u5bfc\u81f4\u7b49\u5f85\u9501\u7684\u60c5\u51b5\u53d1\u751f\u3002<\/p>\n
              \u2714\ufe0e\u6309\u7167\u987a\u5e8f\u6bd4\u5bf9<\/h2>\n
              \u5728PostgreSQL\u4e2d\uff0c\u901a\u8fc7LC_COLLATE\u6765\u63a7\u5236\u6392\u5e8f\u987a\u5e8f\u3002
              \n\u6211\u4eec\u521b\u5efa\u4e86\u4e09\u79cd\u4e0d\u540c\u7684\u6570\u636e\u5e93\uff0c\u5e76\u9a8c\u8bc1\u4e86\u6392\u5e8f\u987a\u5e8f\u7684\u5f71\u54cd\u3002<\/p>\n
              ---<\/span>C<\/span>\r\nCREATE<\/span> DATABASE<\/span> \"pgtestC\"<\/span> WITH<\/span> OWNER<\/span> =<\/span> \"pguser01\"<\/span> ENCODING<\/span> =<\/span> 'UTF8'<\/span>\r\nLC_COLLATE<\/span> =<\/span> 'C'<\/span> LC_CTYPE<\/span> =<\/span> 'C'<\/span> TEMPLATE<\/span> =<\/span> template0<\/span> CONNECTION<\/span> LIMIT<\/span> =<\/span> -<\/span>1<\/span>;<\/span>\r\n\r\n---<\/span>en_US<\/span>\r\nCREATE<\/span> DATABASE<\/span> \"pgtestUS\"<\/span> WITH<\/span> OWNER<\/span> =<\/span> \"pguser01\"<\/span> ENCODING<\/span> =<\/span> 'UTF8'<\/span>\r\nLC_COLLATE<\/span> =<\/span> 'en_US.UTF-8'<\/span> LC_CTYPE<\/span> =<\/span> 'en_US.UTF-8'<\/span> TEMPLATE<\/span> =<\/span> template0<\/span> CONNECTION<\/span> LIMIT<\/span> =<\/span> -<\/span>1<\/span>;<\/span>\r\n\r\n---<\/span>ja_JP<\/span>\r\nCREATE<\/span> DATABASE<\/span> \"pgtestJP\"<\/span> WITH<\/span> OWNER<\/span> =<\/span> \"pguser01\"<\/span> ENCODING<\/span> =<\/span> 'UTF8'<\/span>\r\nLC_COLLATE<\/span> =<\/span> 'ja_JP.UTF8'<\/span> LC_CTYPE<\/span> =<\/span> 'ja_JP.UTF-8'<\/span> TEMPLATE<\/span> =<\/span> template0<\/span> CONNECTION<\/span> LIMIT<\/span> =<\/span> -<\/span>1<\/span>;<\/span>\r\n<\/code><\/pre>\n
              testdb<\/span>=<\/span># select * from moji;<\/span>\r\n col1<\/span> |<\/span>  col2<\/span>  |<\/span>    col3<\/span>    |<\/span>    col4<\/span>    |<\/span> col5<\/span> |<\/span>     col6<\/span>      |<\/span>          col7<\/span>          |<\/span>         col8<\/span>         |<\/span>   col9<\/span>   |<\/span>   col10<\/span>\r\n------+--------+------------+------------+------+---------------+------------------------+----------------------+----------+------------<\/span>\r\n    1<\/span> |<\/span> \u68ee\u5cf6<\/span>   |<\/span> \u30e2\u30ea\u30b7\u30de<\/span>   |<\/span> 1965<\/span>-<\/span>05<\/span>-<\/span>03<\/span> |<\/span> 54<\/span>   |<\/span> \u68ee\u5cf6<\/span> \u7d50\u4f9d<\/span>     |<\/span> rKtddmA4d<\/span>@test<\/span>.<\/span>net<\/span>     |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>co<\/span>.<\/span>jp<\/span> |<\/span> \u6ecb\u8cc0\u770c<\/span>   |<\/span> \u3042\u3044\u3046\u3048\u304a<\/span>\r\n    2<\/span> |<\/span> \u6c5f\u982d<\/span>   |<\/span> \u30a8\u30c8\u30a6<\/span>     |<\/span> 1987<\/span>-<\/span>04<\/span>-<\/span>09<\/span> |<\/span> 32<\/span>   |<\/span> \u6c5f\u982d<\/span> \u4fe1\u7537<\/span>     |<\/span> X7gXd<\/span>@sample<\/span>.<\/span>com<\/span>       |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>com<\/span>    |<\/span> \u798f\u5ca1\u770c<\/span>   |<\/span> \u304b\u304d\u304f\u3051\u3053<\/span>\r\n    3<\/span> |<\/span> \u8c4a\u6c38<\/span>   |<\/span> \u30c8\u30e8\u30ca\u30ac<\/span>   |<\/span> 1988<\/span>-<\/span>02<\/span>-<\/span>19<\/span> |<\/span> 31<\/span>   |<\/span> \u8c4a\u6c38<\/span> \u76db\u592b<\/span>     |<\/span> WsG5TfdE<\/span>@example<\/span>.<\/span>jp<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>net<\/span>   |<\/span> \u5bcc\u5c71\u770c<\/span>   |<\/span> \u3055\u3057\u3059\u305b\u305d<\/span>\r\n    4<\/span> |<\/span> \u5c71\u4e2d<\/span>   |<\/span> \u30e4\u30de\u30ca\u30ab<\/span>   |<\/span> 1989<\/span>-<\/span>03<\/span>-<\/span>20<\/span> |<\/span> 30<\/span>   |<\/span> \u5c71\u4e2d<\/span> \u745e\u8cb4<\/span>     |<\/span> MT01FvOa<\/span>@example<\/span>.<\/span>net<\/span>   |<\/span> http<\/span>:\/<\/span>\/<\/span>test<\/span>.<\/span>net<\/span>      |<\/span> \u5e83\u5cf6\u770c<\/span>   |<\/span> \u305f\u3061\u3064\u3066\u3068<\/span>\r\n    5<\/span> |<\/span> \u718a\u8c37<\/span>   |<\/span> \u30af\u30de\u30bf\u30cb<\/span>   |<\/span> 1972<\/span>-<\/span>01<\/span>-<\/span>12<\/span> |<\/span> 47<\/span>   |<\/span> \u718a\u8c37<\/span> \u52dd\u7f8e<\/span>     |<\/span> LhUlXDUhCV<\/span>@example<\/span>.<\/span>jp<\/span>  |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>net<\/span>    |<\/span> \u798f\u4e95\u770c<\/span>   |<\/span> \u306a\u306b\u306c\u306d\u306e<\/span>\r\n    6<\/span> |<\/span> \u6625\u540d<\/span>   |<\/span> \u30cf\u30eb\u30ca<\/span>     |<\/span> 1988<\/span>-<\/span>10<\/span>-<\/span>08<\/span> |<\/span> 30<\/span>   |<\/span> \u6625\u540d<\/span> \u91d1\u6cbb<\/span>     |<\/span> etKwM<\/span>@sample<\/span>.<\/span>org<\/span>       |<\/span> http<\/span>:\/<\/span>\/<\/span>test<\/span>.<\/span>net<\/span>      |<\/span> \u9577\u5d0e\u770c<\/span>   |<\/span> \u306f\u3072\u3075\u3078\u307b<\/span>\r\n    7<\/span> |<\/span> \u5c0f\u6cb3<\/span>   |<\/span> \u30aa\u30ac\u30ef<\/span>     |<\/span> 1954<\/span>-<\/span>06<\/span>-<\/span>14<\/span> |<\/span> 65<\/span>   |<\/span> \u5c0f\u6cb3<\/span> \u52dd\u5229<\/span>     |<\/span> jzSxIcn0Hq<\/span>@test<\/span>.<\/span>org<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>com<\/span>   |<\/span> \u5c71\u53e3\u770c<\/span>   |<\/span> \u307e\u307f\u3080\u3081\u3082<\/span>\r\n    8<\/span> |<\/span> \u5c0f\u677e\u5d0e<\/span> |<\/span> \u30b3\u30de\u30c4\u30b6\u30ad<\/span> |<\/span> 1997<\/span>-<\/span>09<\/span>-<\/span>28<\/span> |<\/span> 21<\/span>   |<\/span> \u5c0f\u677e\u5d0e<\/span> \u65e5\u83dc\u5b50<\/span> |<\/span> hpbo4P<\/span>@test<\/span>.<\/span>net<\/span>        |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>co<\/span>.<\/span>jp<\/span>  |<\/span> \u9ce5\u53d6\u770c<\/span>   |<\/span> \u3084\u3086\u3088<\/span>\r\n    9<\/span> |<\/span> \u7c73\u539f<\/span>   |<\/span> \u30b3\u30e1\u30cf\u30e9<\/span>   |<\/span> 1995<\/span>-<\/span>12<\/span>-<\/span>29<\/span> |<\/span> 23<\/span>   |<\/span> \u7c73\u539f<\/span> \u5c06\u6587<\/span>     |<\/span> VNKuCm<\/span>@sample<\/span>.<\/span>co<\/span>.<\/span>jp<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>com<\/span>   |<\/span> \u795e\u5948\u5ddd\u770c<\/span> |<\/span> \u3089\u308a\u308b\u308c\u308d<\/span>\r\n   10<\/span> |<\/span> \u4e00\u702c<\/span>   |<\/span> \u30ab\u30ba\u30bb<\/span>     |<\/span> 2006<\/span>-<\/span>11<\/span>-<\/span>11<\/span> |<\/span> 12<\/span>   |<\/span> \u4e00\u702c<\/span> \u8449\u5948<\/span>     |<\/span> NMpwQIu_<\/span>@example<\/span>.<\/span>co<\/span>.<\/span>jp<\/span> |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>com<\/span>    |<\/span> \u5c90\u961c\u770c<\/span>   |<\/span> \u308f\u3092\u3093<\/span>\r\n(<\/span>10<\/span> rows<\/span>)<\/span>\r\n\r\n<\/code><\/pre>\n
              \u5728\u6b64\u72b6\u6001\u4e0b\uff0c\u5c1d\u8bd5\u5728pgtestC\u3001pgtestJP\u548cpgtestUS\u4e09\u4e2a\u6570\u636e\u5e93\u4e0a\u5206\u522b\u6267\u884c\u76f8\u540c\u7684SQL\u8bed\u53e5\uff08select * from moji order by colX\uff09\uff0c\u5e76\u6bd4\u8f83\u5b83\u4eec\u7684\u8f93\u51fa\u7ed3\u679c\u3002\u7ed3\u679c\u663e\u793a\uff0c\u5bf9\u4e8ecol1\u3001col4\u3001col5\u548ccol8\uff0c\u4e09\u4e2a\u6570\u636e\u5e93\u7684\u6392\u5e8f\u987a\u5e8f\u662f\u76f8\u540c\u7684\uff0c\u4f46\u5728\u4ee5\u4e0b\u5217\u4e0a\u5b58\u5728\u5dee\u5f02\u3002<\/p>\n
              \u53ea\u6709ja JP\u7684DB\u7684\u6392\u5e8f\u987a\u5e8f\u4e0e\u5176\u4ed6\u4e0d\u540c\u3002C\u548cen_US\u76f8\u540c\u3002
              \n\u2192 \u672a\u77e5\uff1f\u662f\u5426\u6309\u7167\u5b57\u7b26\u7f16\u7801\u987a\u5e8f\u6392\u5e8f\uff1f<\/p>\n
              \u4ec5\u4ee5\u4e00\u79cd\u4e2d\u6587\u9009\u9879\u8fdb\u884c\u91ca\u4e49\uff1a
              \nen_US\u7684\u6392\u5e8f\u987a\u5e8f\u5728\u6570\u636e\u5e93\u4e2d\u4e0e\u5176\u4ed6\u6392\u5e8f\u987a\u5e8f\u6709\u6240\u4e0d\u540c\u3002\u800cja_JP\u548cC\u662f\u76f8\u540c\u7684\u3002
              \n\u2192ja_JP\/C\u6309\u7167\u65e5\u8bed\u7684\u3042\u3044\u3046\u3048\u304a\uff08\u30a2\u30a4\u30a6\u30a8\u30aa\uff09\u987a\u5e8f\u8fdb\u884c\u6392\u5e8f\u3002en_US\u5219\u6309\u7167\u5b57\u8282\u6570\u8f83\u5c11\u7684\u987a\u5e8f\u8fdb\u884c\u6392\u5e8f\u3002<\/p>\n
              [<\/span>root<\/span>@base<\/span> ~<\/span>]<\/span># psql -h kai1 -U pguser01 -c 'select * from moji order by 3' pgtestJP<\/span>\r\n col1<\/span> |<\/span>  col2<\/span>  |<\/span>    col3<\/span>    |<\/span>    col4<\/span>    |<\/span> col5<\/span> |<\/span>     col6<\/span>      |<\/span>          col7<\/span>          |<\/span>         col8<\/span>         |<\/span>   col9<\/span>   |<\/span>   col10<\/span>\r\n------+--------+------------+------------+------+---------------+------------------------+----------------------+----------+------------<\/span>\r\n    2<\/span> |<\/span> \u6c5f\u982d<\/span>   |<\/span> \u30a8\u30c8\u30a6<\/span>     |<\/span> 1987<\/span>-<\/span>04<\/span>-<\/span>09<\/span> |<\/span> 32<\/span>   |<\/span> \u6c5f\u982d<\/span> \u4fe1\u7537<\/span>     |<\/span> X7gXd<\/span>@sample<\/span>.<\/span>com<\/span>       |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>com<\/span>    |<\/span> \u798f\u5ca1\u770c<\/span>   |<\/span> \u304b\u304d\u304f\u3051\u3053<\/span>\r\n    7<\/span> |<\/span> \u5c0f\u6cb3<\/span>   |<\/span> \u30aa\u30ac\u30ef<\/span>     |<\/span> 1954<\/span>-<\/span>06<\/span>-<\/span>14<\/span> |<\/span> 65<\/span>   |<\/span> \u5c0f\u6cb3<\/span> \u52dd\u5229<\/span>     |<\/span> jzSxIcn0Hq<\/span>@test<\/span>.<\/span>org<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>com<\/span>   |<\/span> \u5c71\u53e3\u770c<\/span>   |<\/span> \u307e\u307f\u3080\u3081\u3082<\/span>\r\n   10<\/span> |<\/span> \u4e00\u702c<\/span>   |<\/span> \u30ab\u30ba\u30bb<\/span>     |<\/span> 2006<\/span>-<\/span>11<\/span>-<\/span>11<\/span> |<\/span> 12<\/span>   |<\/span> \u4e00\u702c<\/span> \u8449\u5948<\/span>     |<\/span> NMpwQIu_<\/span>@example<\/span>.<\/span>co<\/span>.<\/span>jp<\/span> |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>com<\/span>    |<\/span> \u5c90\u961c\u770c<\/span>   |<\/span> \u308f\u3092\u3093<\/span>\r\n    5<\/span> |<\/span> \u718a\u8c37<\/span>   |<\/span> \u30af\u30de\u30bf\u30cb<\/span>   |<\/span> 1972<\/span>-<\/span>01<\/span>-<\/span>12<\/span> |<\/span> 47<\/span>   |<\/span> \u718a\u8c37<\/span> \u52dd\u7f8e<\/span>     |<\/span> LhUlXDUhCV<\/span>@example<\/span>.<\/span>jp<\/span>  |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>net<\/span>    |<\/span> \u798f\u4e95\u770c<\/span>   |<\/span> \u306a\u306b\u306c\u306d\u306e<\/span>\r\n    8<\/span> |<\/span> \u5c0f\u677e\u5d0e<\/span> |<\/span> \u30b3\u30de\u30c4\u30b6\u30ad<\/span> |<\/span> 1997<\/span>-<\/span>09<\/span>-<\/span>28<\/span> |<\/span> 21<\/span>   |<\/span> \u5c0f\u677e\u5d0e<\/span> \u65e5\u83dc\u5b50<\/span> |<\/span> hpbo4P<\/span>@test<\/span>.<\/span>net<\/span>        |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>co<\/span>.<\/span>jp<\/span>  |<\/span> \u9ce5\u53d6\u770c<\/span>   |<\/span> \u3084\u3086\u3088<\/span>\r\n    9<\/span> |<\/span> \u7c73\u539f<\/span>   |<\/span> \u30b3\u30e1\u30cf\u30e9<\/span>   |<\/span> 1995<\/span>-<\/span>12<\/span>-<\/span>29<\/span> |<\/span> 23<\/span>   |<\/span> \u7c73\u539f<\/span> \u5c06\u6587<\/span>     |<\/span> VNKuCm<\/span>@sample<\/span>.<\/span>co<\/span>.<\/span>jp<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>com<\/span>   |<\/span> \u795e\u5948\u5ddd\u770c<\/span> |<\/span> \u3089\u308a\u308b\u308c\u308d<\/span>\r\n    3<\/span> |<\/span> \u8c4a\u6c38<\/span>   |<\/span> \u30c8\u30e8\u30ca\u30ac<\/span>   |<\/span> 1988<\/span>-<\/span>02<\/span>-<\/span>19<\/span> |<\/span> 31<\/span>   |<\/span> \u8c4a\u6c38<\/span> \u76db\u592b<\/span>     |<\/span> WsG5TfdE<\/span>@example<\/span>.<\/span>jp<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>net<\/span>   |<\/span> \u5bcc\u5c71\u770c<\/span>   |<\/span> \u3055\u3057\u3059\u305b\u305d<\/span>\r\n    6<\/span> |<\/span> \u6625\u540d<\/span>   |<\/span> \u30cf\u30eb\u30ca<\/span>     |<\/span> 1988<\/span>-<\/span>10<\/span>-<\/span>08<\/span> |<\/span> 30<\/span>   |<\/span> \u6625\u540d<\/span> \u91d1\u6cbb<\/span>     |<\/span> etKwM<\/span>@sample<\/span>.<\/span>org<\/span>       |<\/span> http<\/span>:\/<\/span>\/<\/span>test<\/span>.<\/span>net<\/span>      |<\/span> \u9577\u5d0e\u770c<\/span>   |<\/span> \u306f\u3072\u3075\u3078\u307b<\/span>\r\n    1<\/span> |<\/span> \u68ee\u5cf6<\/span>   |<\/span> \u30e2\u30ea\u30b7\u30de<\/span>   |<\/span> 1965<\/span>-<\/span>05<\/span>-<\/span>03<\/span> |<\/span> 54<\/span>   |<\/span> \u68ee\u5cf6<\/span> \u7d50\u4f9d<\/span>     |<\/span> rKtddmA4d<\/span>@test<\/span>.<\/span>net<\/span>     |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>co<\/span>.<\/span>jp<\/span> |<\/span> \u6ecb\u8cc0\u770c<\/span>   |<\/span> \u3042\u3044\u3046\u3048\u304a<\/span>\r\n    4<\/span> |<\/span> \u5c71\u4e2d<\/span>   |<\/span> \u30e4\u30de\u30ca\u30ab<\/span>   |<\/span> 1989<\/span>-<\/span>03<\/span>-<\/span>20<\/span> |<\/span> 30<\/span>   |<\/span> \u5c71\u4e2d<\/span> \u745e\u8cb4<\/span>     |<\/span> MT01FvOa<\/span>@example<\/span>.<\/span>net<\/span>   |<\/span> http<\/span>:\/<\/span>\/<\/span>test<\/span>.<\/span>net<\/span>      |<\/span> \u5e83\u5cf6\u770c<\/span>   |<\/span> \u305f\u3061\u3064\u3066\u3068<\/span>\r\n(<\/span>10<\/span> rows<\/span>)<\/span>\r\n\r\n[<\/span>root<\/span>@base<\/span> ~<\/span>]<\/span># psql -h kai1 -U pguser01 -c 'select * from moji order by 3' pgtestUS<\/span>\r\n col1<\/span> |<\/span>  col2<\/span>  |<\/span>    col3<\/span>    |<\/span>    col4<\/span>    |<\/span> col5<\/span> |<\/span>     col6<\/span>      |<\/span>          col7<\/span>          |<\/span>         col8<\/span>         |<\/span>   col9<\/span>   |<\/span>   col10<\/span>\r\n------+--------+------------+------------+------+---------------+------------------------+----------------------+----------+------------<\/span>\r\n    2<\/span> |<\/span> \u6c5f\u982d<\/span>   |<\/span> \u30a8\u30c8\u30a6<\/span>     |<\/span> 1987<\/span>-<\/span>04<\/span>-<\/span>09<\/span> |<\/span> 32<\/span>   |<\/span> \u6c5f\u982d<\/span> \u4fe1\u7537<\/span>     |<\/span> X7gXd<\/span>@sample<\/span>.<\/span>com<\/span>       |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>com<\/span>    |<\/span> \u798f\u5ca1\u770c<\/span>   |<\/span> \u304b\u304d\u304f\u3051\u3053<\/span>\r\n    7<\/span> |<\/span> \u5c0f\u6cb3<\/span>   |<\/span> \u30aa\u30ac\u30ef<\/span>     |<\/span> 1954<\/span>-<\/span>06<\/span>-<\/span>14<\/span> |<\/span> 65<\/span>   |<\/span> \u5c0f\u6cb3<\/span> \u52dd\u5229<\/span>     |<\/span> jzSxIcn0Hq<\/span>@test<\/span>.<\/span>org<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>com<\/span>   |<\/span> \u5c71\u53e3\u770c<\/span>   |<\/span> \u307e\u307f\u3080\u3081\u3082<\/span>\r\n   10<\/span> |<\/span> \u4e00\u702c<\/span>   |<\/span> \u30ab\u30ba\u30bb<\/span>     |<\/span> 2006<\/span>-<\/span>11<\/span>-<\/span>11<\/span> |<\/span> 12<\/span>   |<\/span> \u4e00\u702c<\/span> \u8449\u5948<\/span>     |<\/span> NMpwQIu_<\/span>@example<\/span>.<\/span>co<\/span>.<\/span>jp<\/span> |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>com<\/span>    |<\/span> \u5c90\u961c\u770c<\/span>   |<\/span> \u308f\u3092\u3093<\/span>\r\n    6<\/span> |<\/span> \u6625\u540d<\/span>   |<\/span> \u30cf\u30eb\u30ca<\/span>     |<\/span> 1988<\/span>-<\/span>10<\/span>-<\/span>08<\/span> |<\/span> 30<\/span>   |<\/span> \u6625\u540d<\/span> \u91d1\u6cbb<\/span>     |<\/span> etKwM<\/span>@sample<\/span>.<\/span>org<\/span>       |<\/span> http<\/span>:\/<\/span>\/<\/span>test<\/span>.<\/span>net<\/span>      |<\/span> \u9577\u5d0e\u770c<\/span>   |<\/span> \u306f\u3072\u3075\u3078\u307b<\/span>\r\n    5<\/span> |<\/span> \u718a\u8c37<\/span>   |<\/span> \u30af\u30de\u30bf\u30cb<\/span>   |<\/span> 1972<\/span>-<\/span>01<\/span>-<\/span>12<\/span> |<\/span> 47<\/span>   |<\/span> \u718a\u8c37<\/span> \u52dd\u7f8e<\/span>     |<\/span> LhUlXDUhCV<\/span>@example<\/span>.<\/span>jp<\/span>  |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>net<\/span>    |<\/span> \u798f\u4e95\u770c<\/span>   |<\/span> \u306a\u306b\u306c\u306d\u306e<\/span>\r\n    9<\/span> |<\/span> \u7c73\u539f<\/span>   |<\/span> \u30b3\u30e1\u30cf\u30e9<\/span>   |<\/span> 1995<\/span>-<\/span>12<\/span>-<\/span>29<\/span> |<\/span> 23<\/span>   |<\/span> \u7c73\u539f<\/span> \u5c06\u6587<\/span>     |<\/span> VNKuCm<\/span>@sample<\/span>.<\/span>co<\/span>.<\/span>jp<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>com<\/span>   |<\/span> \u795e\u5948\u5ddd\u770c<\/span> |<\/span> \u3089\u308a\u308b\u308c\u308d<\/span>\r\n    3<\/span> |<\/span> \u8c4a\u6c38<\/span>   |<\/span> \u30c8\u30e8\u30ca\u30ac<\/span>   |<\/span> 1988<\/span>-<\/span>02<\/span>-<\/span>19<\/span> |<\/span> 31<\/span>   |<\/span> \u8c4a\u6c38<\/span> \u76db\u592b<\/span>     |<\/span> WsG5TfdE<\/span>@example<\/span>.<\/span>jp<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>net<\/span>   |<\/span> \u5bcc\u5c71\u770c<\/span>   |<\/span> \u3055\u3057\u3059\u305b\u305d<\/span>\r\n    1<\/span> |<\/span> \u68ee\u5cf6<\/span>   |<\/span> \u30e2\u30ea\u30b7\u30de<\/span>   |<\/span> 1965<\/span>-<\/span>05<\/span>-<\/span>03<\/span> |<\/span> 54<\/span>   |<\/span> \u68ee\u5cf6<\/span> \u7d50\u4f9d<\/span>     |<\/span> rKtddmA4d<\/span>@test<\/span>.<\/span>net<\/span>     |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>co<\/span>.<\/span>jp<\/span> |<\/span> \u6ecb\u8cc0\u770c<\/span>   |<\/span> \u3042\u3044\u3046\u3048\u304a<\/span>\r\n    4<\/span> |<\/span> \u5c71\u4e2d<\/span>   |<\/span> \u30e4\u30de\u30ca\u30ab<\/span>   |<\/span> 1989<\/span>-<\/span>03<\/span>-<\/span>20<\/span> |<\/span> 30<\/span>   |<\/span> \u5c71\u4e2d<\/span> \u745e\u8cb4<\/span>     |<\/span> MT01FvOa<\/span>@example<\/span>.<\/span>net<\/span>   |<\/span> http<\/span>:\/<\/span>\/<\/span>test<\/span>.<\/span>net<\/span>      |<\/span> \u5e83\u5cf6\u770c<\/span>   |<\/span> \u305f\u3061\u3064\u3066\u3068<\/span>\r\n    8<\/span> |<\/span> \u5c0f\u677e\u5d0e<\/span> |<\/span> \u30b3\u30de\u30c4\u30b6\u30ad<\/span> |<\/span> 1997<\/span>-<\/span>09<\/span>-<\/span>28<\/span> |<\/span> 21<\/span>   |<\/span> \u5c0f\u677e\u5d0e<\/span> \u65e5\u83dc\u5b50<\/span> |<\/span> hpbo4P<\/span>@test<\/span>.<\/span>net<\/span>        |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>co<\/span>.<\/span>jp<\/span>  |<\/span> \u9ce5\u53d6\u770c<\/span>   |<\/span> \u3084\u3086\u3088<\/span>\r\n<\/code><\/pre>\n
              \u4ec5\u9700\u8981\u4e00\u4e2a\u9009\u9879\uff0c\u4e0b\u9762\u662f\u5bf9\u8be5\u53e5\u5b50\u7684\u4e2d\u6587\u4fee\u6539:<\/p>\n
              en_US\u7684\u7167\u5408\u987a\u5e8f\u7684\u6570\u636e\u5e93\u7684\u6392\u5e8f\u987a\u5e8f\u6709\u6240\u4e0d\u540c\u3002C\u548cja_JP\u76f8\u540c\u3002
              \n\u2192ja_JP\/C\u7684\u6392\u5e8f\u987a\u5e8f\u662f\u5927\u5199\u5b57\u6bcd\u2192\u5c0f\u5199\u5b57\u6bcd\u987a\u5e8f\u3002en_US\u7684\u6392\u5e8f\u987a\u5e8f\u662f\u5c0f\u5199\u5b57\u6bcd\u2192\u5927\u5199\u5b57\u6bcd\u987a\u5e8f\u3002<\/p>\n
              [<\/span>root<\/span>@base<\/span> ~<\/span>]<\/span># psql -h kai1 -U pguser01 -c 'select * from moji order by 7' pgtestJP<\/span>\r\n col1<\/span> |<\/span>  col2<\/span>  |<\/span>    col3<\/span>    |<\/span>    col4<\/span>    |<\/span> col5<\/span> |<\/span>     col6<\/span>      |<\/span>          col7<\/span>          |<\/span>         col8<\/span>         |<\/span>   col9<\/span>   |<\/span>   col10<\/span>\r\n------+--------+------------+------------+------+---------------+------------------------+----------------------+----------+------------<\/span>\r\n    5<\/span> |<\/span> \u718a\u8c37<\/span>   |<\/span> \u30af\u30de\u30bf\u30cb<\/span>   |<\/span> 1972<\/span>-<\/span>01<\/span>-<\/span>12<\/span> |<\/span> 47<\/span>   |<\/span> \u718a\u8c37<\/span> \u52dd\u7f8e<\/span>     |<\/span> LhUlXDUhCV<\/span>@example<\/span>.<\/span>jp<\/span>  |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>net<\/span>    |<\/span> \u798f\u4e95\u770c<\/span>   |<\/span> \u306a\u306b\u306c\u306d\u306e<\/span>\r\n    4<\/span> |<\/span> \u5c71\u4e2d<\/span>   |<\/span> \u30e4\u30de\u30ca\u30ab<\/span>   |<\/span> 1989<\/span>-<\/span>03<\/span>-<\/span>20<\/span> |<\/span> 30<\/span>   |<\/span> \u5c71\u4e2d<\/span> \u745e\u8cb4<\/span>     |<\/span> MT01FvOa<\/span>@example<\/span>.<\/span>net<\/span>   |<\/span> http<\/span>:\/<\/span>\/<\/span>test<\/span>.<\/span>net<\/span>      |<\/span> \u5e83\u5cf6\u770c<\/span>   |<\/span> \u305f\u3061\u3064\u3066\u3068<\/span>\r\n   10<\/span> |<\/span> \u4e00\u702c<\/span>   |<\/span> \u30ab\u30ba\u30bb<\/span>     |<\/span> 2006<\/span>-<\/span>11<\/span>-<\/span>11<\/span> |<\/span> 12<\/span>   |<\/span> \u4e00\u702c<\/span> \u8449\u5948<\/span>     |<\/span> NMpwQIu_<\/span>@example<\/span>.<\/span>co<\/span>.<\/span>jp<\/span> |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>com<\/span>    |<\/span> \u5c90\u961c\u770c<\/span>   |<\/span> \u308f\u3092\u3093<\/span>\r\n    9<\/span> |<\/span> \u7c73\u539f<\/span>   |<\/span> \u30b3\u30e1\u30cf\u30e9<\/span>   |<\/span> 1995<\/span>-<\/span>12<\/span>-<\/span>29<\/span> |<\/span> 23<\/span>   |<\/span> \u7c73\u539f<\/span> \u5c06\u6587<\/span>     |<\/span> VNKuCm<\/span>@sample<\/span>.<\/span>co<\/span>.<\/span>jp<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>com<\/span>   |<\/span> \u795e\u5948\u5ddd\u770c<\/span> |<\/span> \u3089\u308a\u308b\u308c\u308d<\/span>\r\n    3<\/span> |<\/span> \u8c4a\u6c38<\/span>   |<\/span> \u30c8\u30e8\u30ca\u30ac<\/span>   |<\/span> 1988<\/span>-<\/span>02<\/span>-<\/span>19<\/span> |<\/span> 31<\/span>   |<\/span> \u8c4a\u6c38<\/span> \u76db\u592b<\/span>     |<\/span> WsG5TfdE<\/span>@example<\/span>.<\/span>jp<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>net<\/span>   |<\/span> \u5bcc\u5c71\u770c<\/span>   |<\/span> \u3055\u3057\u3059\u305b\u305d<\/span>\r\n    2<\/span> |<\/span> \u6c5f\u982d<\/span>   |<\/span> \u30a8\u30c8\u30a6<\/span>     |<\/span> 1987<\/span>-<\/span>04<\/span>-<\/span>09<\/span> |<\/span> 32<\/span>   |<\/span> \u6c5f\u982d<\/span> \u4fe1\u7537<\/span>     |<\/span> X7gXd<\/span>@sample<\/span>.<\/span>com<\/span>       |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>com<\/span>    |<\/span> \u798f\u5ca1\u770c<\/span>   |<\/span> \u304b\u304d\u304f\u3051\u3053<\/span>\r\n    6<\/span> |<\/span> \u6625\u540d<\/span>   |<\/span> \u30cf\u30eb\u30ca<\/span>     |<\/span> 1988<\/span>-<\/span>10<\/span>-<\/span>08<\/span> |<\/span> 30<\/span>   |<\/span> \u6625\u540d<\/span> \u91d1\u6cbb<\/span>     |<\/span> etKwM<\/span>@sample<\/span>.<\/span>org<\/span>       |<\/span> http<\/span>:\/<\/span>\/<\/span>test<\/span>.<\/span>net<\/span>      |<\/span> \u9577\u5d0e\u770c<\/span>   |<\/span> \u306f\u3072\u3075\u3078\u307b<\/span>\r\n    8<\/span> |<\/span> \u5c0f\u677e\u5d0e<\/span> |<\/span> \u30b3\u30de\u30c4\u30b6\u30ad<\/span> |<\/span> 1997<\/span>-<\/span>09<\/span>-<\/span>28<\/span> |<\/span> 21<\/span>   |<\/span> \u5c0f\u677e\u5d0e<\/span> \u65e5\u83dc\u5b50<\/span> |<\/span> hpbo4P<\/span>@test<\/span>.<\/span>net<\/span>        |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>co<\/span>.<\/span>jp<\/span>  |<\/span> \u9ce5\u53d6\u770c<\/span>   |<\/span> \u3084\u3086\u3088<\/span>\r\n    7<\/span> |<\/span> \u5c0f\u6cb3<\/span>   |<\/span> \u30aa\u30ac\u30ef<\/span>     |<\/span> 1954<\/span>-<\/span>06<\/span>-<\/span>14<\/span> |<\/span> 65<\/span>   |<\/span> \u5c0f\u6cb3<\/span> \u52dd\u5229<\/span>     |<\/span> jzSxIcn0Hq<\/span>@test<\/span>.<\/span>org<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>com<\/span>   |<\/span> \u5c71\u53e3\u770c<\/span>   |<\/span> \u307e\u307f\u3080\u3081\u3082<\/span>\r\n    1<\/span> |<\/span> \u68ee\u5cf6<\/span>   |<\/span> \u30e2\u30ea\u30b7\u30de<\/span>   |<\/span> 1965<\/span>-<\/span>05<\/span>-<\/span>03<\/span> |<\/span> 54<\/span>   |<\/span> \u68ee\u5cf6<\/span> \u7d50\u4f9d<\/span>     |<\/span> rKtddmA4d<\/span>@test<\/span>.<\/span>net<\/span>     |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>co<\/span>.<\/span>jp<\/span> |<\/span> \u6ecb\u8cc0\u770c<\/span>   |<\/span> \u3042\u3044\u3046\u3048\u304a<\/span>\r\n(<\/span>10<\/span> rows<\/span>)<\/span>\r\n\r\n[<\/span>root<\/span>@base<\/span> ~<\/span>]<\/span># psql -h kai1 -U pguser01 -c 'select * from moji order by 7' pgtestUS<\/span>\r\n col1<\/span> |<\/span>  col2<\/span>  |<\/span>    col3<\/span>    |<\/span>    col4<\/span>    |<\/span> col5<\/span> |<\/span>     col6<\/span>      |<\/span>          col7<\/span>          |<\/span>         col8<\/span>         |<\/span>   col9<\/span>   |<\/span>   col10<\/span>\r\n------+--------+------------+------------+------+---------------+------------------------+----------------------+----------+------------<\/span>\r\n    6<\/span> |<\/span> \u6625\u540d<\/span>   |<\/span> \u30cf\u30eb\u30ca<\/span>     |<\/span> 1988<\/span>-<\/span>10<\/span>-<\/span>08<\/span> |<\/span> 30<\/span>   |<\/span> \u6625\u540d<\/span> \u91d1\u6cbb<\/span>     |<\/span> etKwM<\/span>@sample<\/span>.<\/span>org<\/span>       |<\/span> http<\/span>:\/<\/span>\/<\/span>test<\/span>.<\/span>net<\/span>      |<\/span> \u9577\u5d0e\u770c<\/span>   |<\/span> \u306f\u3072\u3075\u3078\u307b<\/span>\r\n    8<\/span> |<\/span> \u5c0f\u677e\u5d0e<\/span> |<\/span> \u30b3\u30de\u30c4\u30b6\u30ad<\/span> |<\/span> 1997<\/span>-<\/span>09<\/span>-<\/span>28<\/span> |<\/span> 21<\/span>   |<\/span> \u5c0f\u677e\u5d0e<\/span> \u65e5\u83dc\u5b50<\/span> |<\/span> hpbo4P<\/span>@test<\/span>.<\/span>net<\/span>        |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>co<\/span>.<\/span>jp<\/span>  |<\/span> \u9ce5\u53d6\u770c<\/span>   |<\/span> \u3084\u3086\u3088<\/span>\r\n    7<\/span> |<\/span> \u5c0f\u6cb3<\/span>   |<\/span> \u30aa\u30ac\u30ef<\/span>     |<\/span> 1954<\/span>-<\/span>06<\/span>-<\/span>14<\/span> |<\/span> 65<\/span>   |<\/span> \u5c0f\u6cb3<\/span> \u52dd\u5229<\/span>     |<\/span> jzSxIcn0Hq<\/span>@test<\/span>.<\/span>org<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>com<\/span>   |<\/span> \u5c71\u53e3\u770c<\/span>   |<\/span> \u307e\u307f\u3080\u3081\u3082<\/span>\r\n    5<\/span> |<\/span> \u718a\u8c37<\/span>   |<\/span> \u30af\u30de\u30bf\u30cb<\/span>   |<\/span> 1972<\/span>-<\/span>01<\/span>-<\/span>12<\/span> |<\/span> 47<\/span>   |<\/span> \u718a\u8c37<\/span> \u52dd\u7f8e<\/span>     |<\/span> LhUlXDUhCV<\/span>@example<\/span>.<\/span>jp<\/span>  |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>net<\/span>    |<\/span> \u798f\u4e95\u770c<\/span>   |<\/span> \u306a\u306b\u306c\u306d\u306e<\/span>\r\n    4<\/span> |<\/span> \u5c71\u4e2d<\/span>   |<\/span> \u30e4\u30de\u30ca\u30ab<\/span>   |<\/span> 1989<\/span>-<\/span>03<\/span>-<\/span>20<\/span> |<\/span> 30<\/span>   |<\/span> \u5c71\u4e2d<\/span> \u745e\u8cb4<\/span>     |<\/span> MT01FvOa<\/span>@example<\/span>.<\/span>net<\/span>   |<\/span> http<\/span>:\/<\/span>\/<\/span>test<\/span>.<\/span>net<\/span>      |<\/span> \u5e83\u5cf6\u770c<\/span>   |<\/span> \u305f\u3061\u3064\u3066\u3068<\/span>\r\n   10<\/span> |<\/span> \u4e00\u702c<\/span>   |<\/span> \u30ab\u30ba\u30bb<\/span>     |<\/span> 2006<\/span>-<\/span>11<\/span>-<\/span>11<\/span> |<\/span> 12<\/span>   |<\/span> \u4e00\u702c<\/span> \u8449\u5948<\/span>     |<\/span> NMpwQIu_<\/span>@example<\/span>.<\/span>co<\/span>.<\/span>jp<\/span> |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>com<\/span>    |<\/span> \u5c90\u961c\u770c<\/span>   |<\/span> \u308f\u3092\u3093<\/span>\r\n    1<\/span> |<\/span> \u68ee\u5cf6<\/span>   |<\/span> \u30e2\u30ea\u30b7\u30de<\/span>   |<\/span> 1965<\/span>-<\/span>05<\/span>-<\/span>03<\/span> |<\/span> 54<\/span>   |<\/span> \u68ee\u5cf6<\/span> \u7d50\u4f9d<\/span>     |<\/span> rKtddmA4d<\/span>@test<\/span>.<\/span>net<\/span>     |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>co<\/span>.<\/span>jp<\/span> |<\/span> \u6ecb\u8cc0\u770c<\/span>   |<\/span> \u3042\u3044\u3046\u3048\u304a<\/span>\r\n    9<\/span> |<\/span> \u7c73\u539f<\/span>   |<\/span> \u30b3\u30e1\u30cf\u30e9<\/span>   |<\/span> 1995<\/span>-<\/span>12<\/span>-<\/span>29<\/span> |<\/span> 23<\/span>   |<\/span> \u7c73\u539f<\/span> \u5c06\u6587<\/span>     |<\/span> VNKuCm<\/span>@sample<\/span>.<\/span>co<\/span>.<\/span>jp<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>com<\/span>   |<\/span> \u795e\u5948\u5ddd\u770c<\/span> |<\/span> \u3089\u308a\u308b\u308c\u308d<\/span>\r\n    3<\/span> |<\/span> \u8c4a\u6c38<\/span>   |<\/span> \u30c8\u30e8\u30ca\u30ac<\/span>   |<\/span> 1988<\/span>-<\/span>02<\/span>-<\/span>19<\/span> |<\/span> 31<\/span>   |<\/span> \u8c4a\u6c38<\/span> \u76db\u592b<\/span>     |<\/span> WsG5TfdE<\/span>@example<\/span>.<\/span>jp<\/span>    |<\/span> http<\/span>:\/<\/span>\/<\/span>example<\/span>.<\/span>net<\/span>   |<\/span> \u5bcc\u5c71\u770c<\/span>   |<\/span> \u3055\u3057\u3059\u305b\u305d<\/span>\r\n    2<\/span> |<\/span> \u6c5f\u982d<\/span>   |<\/span> \u30a8\u30c8\u30a6<\/span>     |<\/span> 1987<\/span>-<\/span>04<\/span>-<\/span>09<\/span> |<\/span> 32<\/span>   |<\/span> \u6c5f\u982d<\/span> \u4fe1\u7537<\/span>     |<\/span> X7gXd<\/span>@sample<\/span>.<\/span>com<\/span>       |<\/span> http<\/span>:\/<\/span>\/<\/span>sample<\/span>.<\/span>com<\/span>    |<\/span> \u798f\u5ca1\u770c<\/span>   |<\/span> \u304b\u304d\u304f\u3051\u3053<\/span>\r\n(<\/span>10<\/span> rows<\/span>)<\/span>\r\n<\/code><\/pre>\n
              \u5c1d\u8bd5\u4e86\u5404\u79cd\u65b9\u6cd5\uff0c\u4f46\u53ef\u80fd\u6d89\u53ca\u6570\u636e\u4f9d\u8d56\uff0c\u800c\u5177\u4f53\u7684\u673a\u5236\u5e94\u53c2\u8003\u4e0b\u9762\u7684\u94fe\u63a5\uff0c\u8fd9\u662f\u5bf9\u533a\u57df\u8bbe\u7f6e\u89c4\u8303\u7684\u66f4\u51c6\u786e\u89e3\u91ca\uff1a
              \nhttps:\/\/lets.postgresql.jp\/documents\/technical\/text-processing\/2<\/p>\n
              \u2714\u4f5c\u4e3a\u76d1\u89c6\u9879\u76ee\uff0c\u5e94\u8be5\u83b7\u53d6\u548c\u4fdd\u5b58\u597d\u6027\u80fd\u4fe1\u606f\u3002<\/h2>\n
              \u6211\u8ba4\u4e3a\u65e0\u8bba\u662f\u5c06\u5176\u8bbe\u4e3a\u5e38\u9a7bShell\u8fd8\u662f\u4f5c\u4e3a\u76d1\u63a7\u7528\u7684Zabbix\u7b49\u8f6f\u4ef6\uff0c\u90fd\u5e94\u8be5\u5b9a\u671f\u83b7\u53d6\u6709\u5173PostgreSQL\u7684\u4fe1\u606f\u3002<\/p>\n
              ###DB\u5168\u4f53<\/span>\r\nSELECT<\/span> datname<\/span>,<\/span>round<\/span>(<\/span>blks_hit<\/span>*<\/span>100<\/span>\/<\/span>(<\/span>blks_hit<\/span>+<\/span>blks_read<\/span>),<\/span> 2<\/span>)<\/span> AS<\/span> cache_hit_ratio<\/span> FROM<\/span> pg_stat_database<\/span> \r\nWHERE<\/span> blks_read<\/span> ><\/span> 0<\/span>;<\/span>\r\n\r\n###\u30c6\u30fc\u30d6\u30eb\u3054\u3068<\/span>\r\nSELECT<\/span> relname<\/span>,<\/span>round<\/span>(<\/span>heap_blks_hit<\/span>*<\/span>100<\/span>\/<\/span>(<\/span>heap_blks_hit<\/span>+<\/span>heap_blks_read<\/span>),<\/span> 2<\/span>)<\/span> AS<\/span> cache_hit_ratio<\/span> FROM<\/span> pg_statio_user_tables<\/span>\r\nWHERE<\/span> heap_blks_read<\/span> ><\/span> 0<\/span> ORDER<\/span> BY<\/span> cache_hit_ratio<\/span>;<\/span>\r\n\r\n\r\n###\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3054\u3068<\/span>\r\nSELECT<\/span> relname<\/span>,<\/span> indexrelname<\/span>,<\/span>round<\/span>(<\/span>idx_blks_hit<\/span>*<\/span>100<\/span>\/<\/span>(<\/span>idx_blks_hit<\/span>+<\/span>idx_blks_read<\/span>),<\/span> 2<\/span>)<\/span> AS<\/span> cache_hit_ratio<\/span> FROM<\/span> pg_statio_user_indexes<\/span> \r\nWHERE<\/span> idx_blks_read<\/span> ><\/span> 0<\/span> ORDER<\/span> BY<\/span> cache_hit_ratio<\/span>;<\/span>\r\n<\/code><\/pre>\n
              SELECT<\/span> datname<\/span>,<\/span> xact_commit<\/span>,<\/span> xact_rollback<\/span>,<\/span> (<\/span>xact_commit<\/span> +<\/span> xact_rollback<\/span>)<\/span>  as<\/span> sum_transaction<\/span> FROM<\/span> pg_stat_database<\/span>;<\/span>\r\n<\/code><\/pre>\n
              ###\u30d0\u30c3\u30af\u30b0\u30e9\u30a6\u30f3\u30c9\u542b\u3080<\/span>\r\nSELECT<\/span> count<\/span>(<\/span>*<\/span>)<\/span> FROM<\/span> pg_stat_activity<\/span> ;<\/span>\r\n\r\n###\u30d0\u30c3\u30af\u30b0\u30e9\u30a6\u30f3\u30c9\u542b\u307e\u306a\u3044<\/span>\r\nSELECT<\/span> count<\/span>(<\/span>*<\/span>)<\/span> FROM<\/span> pg_stat_activity<\/span> where<\/span> datname<\/span> is<\/span> not<\/span> null<\/span>;<\/span>\r\n\r\n<\/code><\/pre>\n
              select<\/span> datname<\/span>,<\/span> temp_files<\/span>,<\/span> pg_size_pretty<\/span>(<\/span>temp_bytes<\/span>)<\/span> as<\/span> temp_bytes<\/span>,<\/span> pg_size_pretty<\/span>(<\/span>round<\/span>(<\/span>temp_bytes<\/span>\/<\/span>temp_files<\/span>,<\/span>2<\/span>))<\/span> as<\/span> temp_file_size<\/span>\r\nfrom<\/span> pg_stat_database<\/span> where<\/span> temp_files<\/span> ><\/span> 0<\/span>;<\/span>\r\n<\/code><\/pre>\n
                \n
              • \n
                  temp_files: \u4f5c\u6210\u3055\u308c\u305f\u4e00\u6642\u30d5\u30a1\u30a4\u30eb\u306e\u6570<\/ul>\n<\/li>\n<\/ul>\n
                   <\/p>\n
                    \n
                  • \n
                      pg_size_pretty(temp_bytes): \u4e00\u6642\u30d5\u30a1\u30a4\u30eb\u306e\u5408\u8a08\u30b5\u30a4\u30ba<\/ul>\n<\/li>\n<\/ul>\n
                       <\/p>\n
                        pg_size_pretty(round(temp_bytes\/temp_files,2)): \u4e00\u6642\u30d5\u30a1\u30a4\u30eb\u306e\u5e73\u5747\u30b5\u30a4\u30ba<\/ul>\n
                        select<\/span> pg_size_pretty<\/span>(<\/span>pg_database_size<\/span>(<\/span>'testdb'<\/span>))<\/span> as<\/span> database_size<\/span>;<\/span>\r\n<\/code><\/pre>\n
                        select<\/span> lock<\/span>.<\/span>locktype<\/span>,<\/span> class<\/span>.<\/span>relname<\/span>,<\/span> lock<\/span>.<\/span>pid<\/span>,<\/span> lock<\/span>.<\/span>mode<\/span>\r\nfrom<\/span> pg_locks<\/span> lock<\/span>\r\n  left<\/span> outer<\/span> join<\/span> pg_stat_activity<\/span> act<\/span> on<\/span> lock<\/span>.<\/span>pid<\/span> =<\/span> act<\/span>.<\/span>pid<\/span> left<\/span> outer<\/span>\r\n  join<\/span> pg_class<\/span> class<\/span> on<\/span> lock<\/span>.<\/span>relation<\/span> =<\/span> class<\/span>.<\/span>oid<\/span> where<\/span> not<\/span> lock<\/span>.<\/span>granted<\/span>\r\norder<\/span> by<\/span> lock<\/span>.<\/span>pid<\/span>;<\/span>\r\n<\/code><\/pre>\n
                        \u2714\ufe0f\u68c0\u67e5\u70b9<\/h2>\n
                        \u5728PostgreSQL\u4e2d\uff0c\u5f53commit\u5b8c\u6210\u65f6\uff0c\u57fa\u672c\u4e0a\u4f1a\u5c06\u66f4\u65b0\u5185\u5bb9\u5237\u65b0\u5230WAL\uff0c\u56e0\u6b64\u4e0d\u4f1a\u4e22\u5931\u6570\u636e\uff0c\u4f46\u4e5f\u4f1a\u5b9a\u671f\u5c06\u810f\u9875\u6570\u636e\u5237\u65b0\u5230\u78c1\u76d8\u4e0a\u3002\u5982\u679c\u4e0d\u8fd9\u6837\u505a\uff0c\u5f53\u64cd\u4f5c\u7cfb\u7edf\u7a81\u7136\u65ad\u7535\u7b49\u60c5\u51b5\u53d1\u751f\u65f6\uff0c\u670d\u52a1\u5668\u4f1a\u82b1\u8d39\u5f88\u957f\u65f6\u95f4\u6765\u8fdb\u884c\u5d29\u6e83\u6062\u590d\uff0c\u800c\u8fd9\u4e2a\u8fc7\u7a0b\u88ab\u79f0\u4e3a\u68c0\u67e5\u70b9\u3002\u5bf9\u4e8e\u5df2\u7ecf\u5b8c\u6210\u68c0\u67e5\u70b9\u7684\u90e8\u5206\uff0c\u53ef\u4ee5\u4fdd\u8bc1\u5b83\u4eec\u5b58\u5728\u4e8e\u78c1\u76d8\u4e0a\uff0c\u56e0\u6b64\u4e0d\u9700\u8981\u8fdb\u884c\u5d29\u6e83\u6062\u590d\u3002<\/p>\n
                        \u68c0\u67e5\u70b9\u662f\u4e00\u79cd\u6210\u672c\u9ad8\u6602\u7684\u5904\u7406\u65b9\u5f0f\uff0c\u6709\u4ee5\u4e0b\u4e24\u4e2a\u542b\u4e49\u3002<\/p>\n
                          \n
                        • \n
                            \u73fe\u6642\u70b9\u306e\u5168\u3066\u306e\u30c0\u30fc\u30c6\u30a3\u30d0\u30c3\u30d5\u30a1\u3092\u66f8\u304d\u51fa\u3059\u5fc5\u8981\u304c\u3042\u308b\u305f\u3081\u3002<\/ul>\n<\/li>\n<\/ul>\n
                            full_page_writes \u304c\u6709\u52b9\u5316\u3055\u308c\u3066\u3044\u308b\u5834\u5408\u3001\u5404\u30c1\u30a7\u30c3\u30af\u30dd\u30a4\u30f3\u30c8\u5bfe\u8c61\u306e\u5404\u30da\u30fc\u30b8\u306b\u95a2\u3057\u3066\u306f\u3001\u305d\u306e\u5f8c\u521d\u56de\u306eWAL\u66f8\u304d\u8fbc\u307f\u306e\u969b\u306b\u30da\u30fc\u30b8\u5168\u4f53\u304cWAL\u306b\u66f8\u304d\u8fbc\u307e\u308c\u308b\u305f\u3081\u3001<\/p>\n
                            \u4e3a\u6b64\uff0c\u5efa\u8bae\u5c06\u68c0\u67e5\u70b9\u53c2\u6570\u8bbe\u7f6e\u5f97\u8f83\u9ad8\uff0c\u4ee5\u786e\u4fdd\u68c0\u67e5\u70b9\u4e0d\u4f1a\u8fc7\u4e8e\u9891\u7e41\u53d1\u751f\u3002\u5173\u4e8e\u68c0\u67e5\u70b9\uff0c\u53ef\u4ee5\u901a\u8fc7\u63a7\u5236\u65f6\u95f4\u548c\u66f4\u65b0\u7684WAL\u91cf\u6765\u63a7\u5236\u5176\u53d1\u751f\u9891\u7387\u3002\u5b9e\u9645\u4e0a\uff0c\u53ef\u4ee5\u901a\u8fc7\u8bbe\u7f6e\u8fd9\u4e9b\u53c2\u6570\u5e76\u89c2\u5bdf\u4e00\u6bb5\u65f6\u95f4\u6765\u786e\u8ba4\u6ca1\u6709\u95ee\u9898\u3002\u6b64\u5916\uff0c\u5728\u66f4\u65b0\u91cf\u8fbe\u5230\u5cf0\u503c\u65f6\uff0c\u53ef\u4ee5\u5047\u8bbe\u670d\u52a1\u5668\u5d29\u6e83\u5e76\u8fdb\u884c\u5d29\u6e83\u6062\u590d\u65f6\u95f4\u7684\u6d4b\u8bd5\uff0c\u4ee5\u786e\u8ba4\u6062\u590d\u65f6\u95f4\u6ca1\u6709\u95ee\u9898\u3002\u8fdb\u884c\u8fd9\u6837\u7684\u6d4b\u8bd5\u662f\u5408\u7406\u7684\u3002<\/p>\n
                            checkpoint_timeout<\/p>\n
                            \u3053\u306e\u6642\u9593\u304c\u524d\u56de\u306e\u30c1\u30a7\u30c3\u30af\u30dd\u30a4\u30f3\u30c8\u304b\u3089\u7d4c\u904e\u3059\u308b\u3068\u30c1\u30a7\u30c3\u30af\u30dd\u30a4\u30f3\u30c8\u5b9f\u884c\u3002<\/p>\n
                            max_wal_size<\/p>\n
                            \u3053\u306e\u66f4\u65b0\u91cf\u304c\u524d\u56de\u306e\u30c1\u30a7\u30c3\u30af\u30dd\u30a4\u30f3\u30c8\u304b\u3089\u767a\u751f\u3059\u308b\u3068\u30c1\u30a7\u30c3\u30af\u30dd\u30a4\u30f3\u30c8\u5b9f\u884c<\/p>\n
                            \u63a7\u5236\u6bcf\u4e2a\u68c0\u67e5\u70b9\u8282\u70b9\u7684\u5b8c\u6210\u65f6\u95f4\uff08\u4ece\u68c0\u67e5\u70b9\u6267\u884c\u5230\u810f\u9875\u5237\u65b0\u5b8c\u6210\uff09\u7684\u53c2\u6570\u662fcheckpoint_completion_target\u3002\u9ed8\u8ba4\u503c\u4e3a0.5\uff0c\u8fd9\u610f\u5473\u7740\u68c0\u67e5\u70b9\u5c06\u5728\u4ece\u4e00\u6b21\u6267\u884c\u5230\u4e0b\u4e00\u6b21\u6267\u884c\u4e4b\u95f4\u7684\u65f6\u95f4\u95f4\u9694\u7684\u4e00\u534a\u5185\u5b8c\u6210\u3002\u8be5\u53c2\u6570\u5b58\u5728\u7684\u539f\u56e0\u662f\uff0c\u5982\u679c\u810f\u9875\u5728\u68c0\u67e5\u70b9\u6267\u884c\u540e\u7acb\u5373\u88ab\u4e00\u6b21\u6027\u5237\u65b0\u5230\u78c1\u76d8\u4e0a\uff0c\u4f1a\u5bfc\u81f4\u4e34\u65f6\u7684\u8d1f\u8f7d\u6fc0\u589e\u3002<\/p>\n
                            \u53e6\u5916\uff0c\u4f5c\u4e3a\u8bb0\u5f55\u9891\u7e41\u68c0\u67e5\u70b9\u53d1\u751f\u7684\u65b9\u6cd5\uff0c\u53ef\u4ee5\u4f7f\u7528checkpoint_warning\u51fd\u6570\u5c06\u53c2\u6570\u8bbe\u7f6e\u4e3a\u6307\u5b9a\u8303\u56f4\u5185\u7684\u65f6\u95f4\u3002\u5f53\u68c0\u67e5\u70b9\uff08\u5373\u8d85\u8fc7max_wal_size\u9650\u5236\u7684\u66f4\u65b0\uff09\u5728\u6b64\u65f6\u95f4\u5185\u53d1\u751f\u65f6\uff0c\u5c06\u5728\u65e5\u5fd7\u4e2d\u8bb0\u5f55\u76f8\u5173\u4fe1\u606f\u3002\u901a\u5e38\u60c5\u51b5\u4e0b\uff0c\u53ef\u4ee5\u8bbe\u7f6e\u4e00\u4e2a\u4f4e\u4e8echeckpoint_timeout\u7684\u503c\u8fdb\u884c\u89c2\u5bdf\uff0c\u5982\u679c\u9891\u7e41\u5728\u65e5\u5fd7\u4e2d\u8bb0\u5f55\uff0c\u53ef\u4ee5\u8003\u8651\u589e\u52a0max_wal_size\u4ee5\u7f13\u89e3\u9891\u7e41\u68c0\u67e5\u70b9\u5904\u7406\u6240\u5e26\u6765\u7684\u8d1f\u8377\u589e\u52a0\u3002<\/p>\n
                            \u5728\u6267\u884c\u68c0\u67e5\u70b9\u5e76\u751f\u6210\u65e5\u5fd7\u540e\uff0c\u68c0\u67e5\u70b9\u7684\u4f4d\u7f6e\u5c06\u4fdd\u5b58\u5728pg_control\u6587\u4ef6\u4e2d\u3002\u56e0\u6b64\uff0c\u5728\u53d1\u751f\u5d29\u6e83\u540e\u8fdb\u884c\u6062\u590d\u65f6\uff0cPostgreSQL\u9996\u5148\u53c2\u8003pg_control\uff0c\u7136\u540e\u8bfb\u53d6\u68c0\u67e5\u70b9\u8bb0\u5f55\u3002\u7136\u540e\uff0c\u901a\u8fc7\u4ece\u68c0\u67e5\u70b9\u8bb0\u5f55\u5185\u6307\u793a\u7684\u65e5\u5fd7\u4f4d\u7f6e\u5411\u524d\u8fdb\u884c\u626b\u63cf\uff0c\u8fdb\u884c\u5d29\u6e83\u6062\u590d\u65f6\u7684REDO\u5904\u7406\u3002<\/p>\n
                            \u8bf7\u53c2\u8003\u4ee5\u4e0b\u94fe\u63a5\uff0c\u5176\u4e2d\u5305\u542b\u4e86\u5173\u4e8ePostgreSQL WAL\u914d\u7f6e\u548c\u5185\u90e8\u539f\u7406\u7684\u6587\u6863\uff1a<\/p>\n
                            1. PostgreSQL WAL\u914d\u7f6e\u6587\u6863\uff1ahttps:\/\/www.postgresql.jp\/document\/11\/html\/wal-configuration.html<\/p>\n
                            2. PostgreSQL WAL\u5185\u90e8\u539f\u7406\u6587\u6863\uff1ahttps:\/\/www.postgresql.jp\/document\/11\/html\/wal-internals.html<\/p>\n
                            \u2714\ufe0f \u9875\u9762\u7f13\u5b58<\/h2>\n
                            \u68c0\u67e5\u5728 shared_buffer \u4e0a\u7684\u5bf9\u8c61\u7684\u65b9\u6cd5\u3002\u4f7f\u7528\u6269\u5c55\u529f\u80fd pg_buffercache\u3002
                            \n\u9996\u5148\uff0c\u5728\u76ee\u6807\u6570\u636e\u5e93\u4e2d\u6267\u884c create extension pg_buffercache\u3002<\/p>\n
                            SELECT<\/span> c<\/span>.<\/span>relname<\/span>,<\/span> count<\/span>(<\/span>*<\/span>)<\/span> AS<\/span> buffers<\/span>\r\n             FROM<\/span> pg_buffercache<\/span> b<\/span> INNER<\/span> JOIN<\/span> pg_class<\/span> c<\/span>\r\n             ON<\/span> b<\/span>.<\/span>relfilenode<\/span> =<\/span> pg_relation_filenode<\/span>(<\/span>c<\/span>.<\/span>oid<\/span>)<\/span> AND<\/span>\r\n                b<\/span>.<\/span>reldatabase<\/span> IN<\/span> (<\/span>0<\/span>,<\/span> (<\/span>SELECT<\/span> oid<\/span> FROM<\/span> pg_database<\/span>\r\n                                      WHERE<\/span> datname<\/span> =<\/span> current_database<\/span>()))<\/span>\r\n             GROUP<\/span> BY<\/span> c<\/span>.<\/span>relname<\/span>\r\n             ORDER<\/span> BY<\/span> 2<\/span> DESC<\/span>\r\n             LIMIT<\/span> 10<\/span>;<\/span>\r\n<\/code><\/pre>\n
                            select<\/span> c<\/span>.<\/span>relname<\/span>,<\/span>b<\/span>.<\/span>relblocknumber<\/span>,<\/span>b<\/span>.<\/span>isdirty<\/span>,<\/span>b<\/span>.<\/span>pinning_backends<\/span> \r\nfrom<\/span> pg_buffercache<\/span> b<\/span> INNER<\/span> JOIN<\/span> pg_class<\/span> c<\/span>  ON<\/span> b<\/span>.<\/span>relfilenode<\/span> =<\/span> pg_relation_filenode<\/span>(<\/span>c<\/span>.<\/span>oid<\/span>)<\/span> AND<\/span> b<\/span>.<\/span>reldatabase<\/span> IN<\/span> (<\/span>0<\/span>,<\/span> (<\/span>SELECT<\/span> oid<\/span> FROM<\/span> pg_database<\/span> WHERE<\/span> datname<\/span> =<\/span> current_database<\/span>()))<\/span> \r\nwhere<\/span> c<\/span>.<\/span>relname<\/span>=<\/span>'\u30ea\u30ec\u30fc\u30b7\u30e7\u30f3\u540d'<\/span> order<\/span> by<\/span> 1<\/span>,<\/span>2<\/span>;<\/span>\r\n<\/code><\/pre>\n
                            \u5173\u4e8e\u5c06\u5757\u56fa\u5b9a\u5728\u7f13\u51b2\u533a\u4e2d\uff0c\u8bf7\u7ed9\u51fa\u66f4\u591a\u8be6\u7ec6\u4fe1\u606f\u3002\u3002\u3002<\/p>\n
                            \u5728\u5173\u7cfb\u4e0a\u7684\u5757\u88ab\u52a0\u8f7d\u5230\u5171\u4eab\u5185\u5b58\u7f13\u51b2\u533a\u4e2d\uff0c\u4f46\u5728\u64cd\u4f5c\u8fc7\u7a0b\u4e2d\u9700\u8981\u5c06\u8fd9\u79cd\u5173\u8054\u5173\u7cfb\u56fa\u5b9a\u3002\u5728PostgreSQL\u5b9e\u4f8b\u4e2d\uff0c\u7531\u5176\u4ed6\u4f1a\u8bdd\u6216\u540e\u53f0\u8fdb\u7a0b\u8fd0\u884c\u7684\u8fdb\u7a0b\u53ef\u4ee5\u52a0\u8f7d\u4e0d\u540c\u7684\u5757\u5230\u5171\u4eab\u5185\u5b58\u7f13\u51b2\u533a\uff0c\u5e76\u4e14\u53ef\u80fd\u5728\u4e0d\u77e5\u4e0d\u89c9\u4e2d\u53d1\u751f\u66ff\u6362\u5e76\u88ab\u9a71\u9010\u3002\u901a\u8fc7\u56fa\u5b9a\u7f13\u51b2\u533a\uff0c\u53ef\u4ee5\u9632\u6b62\u4efb\u610f\u66ff\u6362\u7684\u53d1\u751f\u3002<\/p>\n
                            [\u53c2\u8003]
                            \nhttp:\/\/www.nminoru.jp\/~nminoru\/postgresql\/pg-table-and-block-structure.html<\/p>\n
                            \u3010\u4ec5\u63d0\u4f9b\u4e00\u4e2a\u9009\u9879\u3011\uff1a
                            \n\u8bf7\u53c2\u8003\u4ee5\u4e0b\u94fe\u63a5\uff1a
                            \nhttp:\/\/www.nminoru.jp\/~nminoru\/postgresql\/pg-table-and-block-structure.html<\/p>\n
                            \u2714\ufe0f\u5438\u5c18\u5668<\/h2>\n
                            gaiy\u014d (\u5438\u5c18\u6982\u8981)<\/h3>\n
                            \u5728PostgreSQL\u4e2d\uff0c\u9700\u8981\u5b9a\u671f\u6267\u884c\u4e00\u79cd\u79f0\u4e3a\u201cvacuum\u201d\u7684\u5904\u7406\u3002<\/p>\n
                            PostgreSQL\u91c7\u7528\u4e86\u8ffd\u52a0\u5f0f\u67b6\u6784\uff0c\u5373\u5728\u6267\u884cupdate\u8bed\u53e5\u65f6\uff0c\u5e76\u4e0d\u4f1a\u66f4\u65b0\u73b0\u6709\u884c\uff0c\u800c\u662f\u4ee5\u63d2\u5165\u8bed\u53e5\u7684\u5f62\u5f0f\u5c06\u66f4\u65b0\u540e\u7684\u884c\u6dfb\u52a0\u5230\u8868\u7684\u672b\u5c3e\u3002\u901a\u8fc7\u8fd9\u79cd\u65b9\u5f0f\uff0c\u53ef\u4ee5\u907f\u514d\u5728\u66f4\u65b0\u65f6\u83b7\u53d6\u884c\u9501\uff0c\u4ece\u800c\u9632\u6b62\u6267\u884c\u66f4\u65b0\u64cd\u4f5c\u7684\u4e8b\u52a1\u5931\u8d25\u3002\u5bf9\u4e8e\u73b0\u6709\u884c\uff0c\u5f53\u5b83\u4eec\u4e0d\u518d\u88ab\u4efb\u4f55\u4e8b\u52a1\u5f15\u7528\u65f6\uff0c\u5c06\u6807\u8bb0\u4e3a\u5220\u9664\uff0c\u5e76\u4e14\u4ee5\u540e\u5c06\u4e0d\u518d\u88ab\u5f15\u7528\u3002<\/p>\n
                            \u7136\u800c\uff0c\u5c3d\u7ba1\u53ef\u80fd\u4e0d\u4f1a\u88ab\u5f15\u7528\uff0c\u4f46\u5e76\u4e0d\u610f\u5473\u7740\u5b83\u4eec\u5728\u7269\u7406\u4e0a\u88ab\u5220\u9664\u4e3a\u78c1\u76d8\u7a7a\u95f4\uff0c\u4e5f\u4e0d\u662f\u53ef\u91cd\u65b0\u5229\u7528\u7684\u7a7a\u95f4\u3002\u901a\u8fc7\u8fdb\u884c\u6e05\u7406\uff0c\u6211\u4eec\u53ef\u4ee5\u7269\u7406\u4e0a\u5220\u9664\u8fd9\u4e9b\u4e0d\u5fc5\u8981\u7684\u7a7a\u95f4\u6216\u5c06\u5176\u8f6c\u5316\u4e3a\u53ef\u91cd\u65b0\u5229\u7528\u7684\u7a7a\u95f4\u3002<\/p>\n
                            \u5438\u5c18\u5668\u901a\u5e38\u5206\u4e3a\u4e24\u79cd\u4e0d\u540c\u7684\u7c7b\u578b\u3002<\/p>\n
                              \n
                            • \n
                                FULL VACUUM<\/ul>\n<\/li>\n<\/ul>\n
                                 <\/p>\n
                                  \n
                                • \n
                                    \u4e0d\u8981\u30bf\u30d7\u30eb\u3092\u7269\u7406\u7684\u306b\u524a\u9664\u3057\u3066\u30c7\u30a3\u30b9\u30af\u9818\u57df\u3092OS\u306b\u8fd4\u5374\u3002OS\u304b\u3089\u898b\u305f\u3068\u304d\u306b\u7269\u7406\u7684\u306b\u30c7\u30a3\u30b9\u30af\u5bb9\u91cf\u304c\u7a7a\u304f\u3002<\/ul>\n<\/li>\n<\/ul>\n
                                     <\/p>\n
                                      \u30c7\u30e1\u30ea\u30c3\u30c8\u3057\u3066\u306f\u3001\u5b9f\u884c\u6642\u5bfe\u8c61\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u5bfe\u3057\u3066\u30c6\u30fc\u30d6\u30eb\u5168\u4f53\u306e\u6392\u4ed6\u30ed\u30c3\u30af\u304c\u304b\u304b\u308b\u3001\u304b\u3064\u9577\u6642\u9593\u304b\u304b\u308b\u3002<\/ul>\n
                                      ###\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u5168\u4f53\u306b\u5bfe\u3057\u3066\u5b9f\u884c<\/span>\r\nVACUUM<\/span> FULL<\/span>;<\/span>\r\n\r\n###\u7279\u5b9a\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u5bfe\u3057\u3066\u5b9f\u884c<\/span>\r\nVACUUM<\/span> FULL<\/span> tbl1<\/span>;<\/span>\r\n\r\n###\u8a73\u7d30\u51fa\u529b\u30e2\u30fc\u30c9<\/span>\r\nVACUUM<\/span> FULL<\/span> VERBOSE<\/span> tbl1<\/span>;<\/span>\r\n<\/code><\/pre>\n
                                        \n
                                      • \n
                                          \u901a\u5e38VACUUM(Concurrent Vacuum)<\/ul>\n<\/li>\n<\/ul>\n
                                           <\/p>\n
                                            \n
                                          • \n
                                              \u4e0d\u8981\u30bf\u30d7\u30eb\u3092\u7269\u7406\u7684\u306b\u306f\u524a\u9664\u3057\u306a\u3044\u304c\u3001DBMS\u3068\u3057\u3066\u518d\u5229\u7528\u306a\u9818\u57df\u3068\u3057\u3066\u5b9a\u7fa9\u3059\u308b\u3002\u5b9f\u884c\u6642\u7279\u306b\u30ed\u30c3\u30af\u7b49\u306f\u304b\u304b\u3089\u305aFULL VACUUM\u306b\u6bd4\u3079\u3066\u77ed\u6642\u9593\u3067\u5b8c\u4e86\u3059\u308b\u3002\u307e\u305f\u57fa\u672c\u7684\u306b\u524d\u56de\u306evacuum\u304b\u3089\u66f4\u65b0\u306e\u3042\u3063\u305f\u884c\u306b\u5bfe\u3057\u3066\u306e\u307f\u5b9f\u884c\u3059\u308b\u306e\u3067\u65e9\u3044\u3002AutoVacuum\u3067\u5b9f\u884c\u3055\u308c\u308b\u306e\u306f\u3053\u3061\u3089\u306e\u9818\u57df\u3002<\/ul>\n<\/li>\n<\/ul>\n
                                               <\/p>\n
                                                \n
                                              • \n
                                                  \u30c7\u30e1\u30ea\u30c3\u30c8\u3068\u3057\u3066\u306f\u3001<\/ul>\n<\/li>\n<\/ul>\n
                                                  \u30c7\u30a3\u30b9\u30af\u9818\u57df\u3092OS\u306b\u8fd4\u3059\u8a33\u3067\u306f\u306a\u304fOS\u304b\u3089\u898b\u305f\u30c7\u30a3\u30b9\u30af\u5bb9\u91cf\u304c\u67af\u6e07\u306e\u53ef\u80fd\u6027\u304c\u3042\u308b\u70b9.
                                                  \n\u518d\u5229\u7528\u53ef\u80fd\u306a\u9818\u57df\u306fSELECT\u6642\u306b\u30c7\u30a3\u30b9\u30af\u8d70\u67fb\u3055\u308c\u308b\u305f\u3081\u3001FULL VACUUM\u306e\u3088\u3046\u306bOS\u306b\u30c7\u30a3\u30b9\u30af\u9818\u57df\u3068\u3057\u3066\u8fd4\u3057\u305f\u6642\u3068\u6bd4\u3079\u3066\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u304c\u60aa\u304f\u306a\u308b\u70b9\u3002(\u4f8b\u3048\u3070\u3001\u30c7\u30fc\u30bf\u304c\uff11\u4ef6\u3057\u304b\u5165\u3063\u3066\u3044\u306a\u3044\u30c6\u30fc\u30d6\u30eb\u306e\u30d5\u30eb\u30b9\u30ad\u30e3\u30f3\u304c\u901a\u5e38\u901f\u653b\u7d42\u308f\u308b\u306e\u306b\u3001\u518d\u5229\u7528\u53ef\u80fd\u306a\u9818\u57df\u304c10000000\u4ef6\u3042\u308b\u3068\u3088\u308a\u6642\u9593\u304c\u304b\u304b\u308b\u306a\u3069)<\/p>\n
                                                  ###\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u5168\u4f53\u306b\u5bfe\u3057\u3066\u5b9f\u884c<\/span>\r\nVACUUM<\/span>;<\/span>\r\n\r\n###\u7279\u5b9a\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u5bfe\u3057\u3066\u5b9f\u884c<\/span>\r\nVACUUM<\/span> tbl1<\/span>;<\/span>\r\n\r\n###\u8a73\u7d30\u51fa\u529b\u30e2\u30fc\u30c9<\/span>\r\nVACUUM<\/span> VERBOSE<\/span> tbl1<\/span>;<\/span>\r\n<\/code><\/pre>\n
                                                  \u5728\u901a\u5e38\u60c5\u51b5\u4e0b\uff0c\u6211\u4eec\u901a\u5e38\u4f7f\u7528\u5e38\u89c4\u7684VACUUM\u64cd\u4f5c\u3002\u4f46\u5728\u7d27\u6025\u60c5\u51b5\u4e0b\uff0c\u6bd4\u5982\u78c1\u76d8\u7a7a\u95f4\u538b\u529b\u8fc7\u5927\uff0c\u6216\u8005\u5bf9\u4e8e\u6570\u636e\u4e0d\u518d\u589e\u957f\u4e14\u53ef\u91cd\u7528\u7a7a\u95f4\u7684\u6bd4\u4f8b\u59cb\u7ec8\u8fc7\u9ad8\u7684\u8868\uff0c\u5219\u5e94\u5f53\u8003\u8651\u6267\u884cFULL VACUUM\u64cd\u4f5c\uff0c\u4ee5\u907f\u514d\u89e6\u53d1\u8868\u9501\u5b9a\u3002<\/p>\n
                                                  \u300cAuto VACUUM\u300d\u7684\u6bcf\u4e2a\u53c2\u6570\u7684\u8be6\u7ec6\u4fe1\u606f\u3002<\/h3>\n
                                                  select<\/span> schemaname<\/span>,<\/span>relname<\/span>,<\/span>n_live_tup<\/span>,<\/span>n_dead_tup<\/span>,<\/span>last_vacuum<\/span>,<\/span>last_autovacuum<\/span>,<\/span>last_analyze<\/span>,<\/span>last_autoanalyze<\/span> from<\/span> pg_stat_user_tables<\/span>;<\/span>\r\n<\/code><\/pre>\n
                                                  \u5173\u4e8e\u5171\u4eab\u7f13\u51b2\u533a\u7684\u8be6\u7ec6\u4fe1\u606f<\/h3>\n
                                                  \u4ee3\u7801\u793a\u4f8b<\/h1>\n
                                                  JDBC\u6d4b\u8bd5\u7528\uff08Java\u3002\u622a\u65ad\u21d2\u4eceCSV\u63d2\u5165\u21d2\u67e5\u8be2\u7ed3\u679c\uff09<\/h2>\n
                                                  import<\/span> java<\/span>.<\/span>io<\/span>.<\/span>BufferedReader<\/span>;<\/span>\r\nimport<\/span> java<\/span>.<\/span>io<\/span>.<\/span>File<\/span>;<\/span>\r\nimport<\/span> java<\/span>.<\/span>io<\/span>.<\/span>FileInputStream<\/span>;<\/span>\r\nimport<\/span> java<\/span>.<\/span>io<\/span>.<\/span>IOException<\/span>;<\/span>\r\nimport<\/span> java<\/span>.<\/span>io<\/span>.<\/span>InputStreamReader<\/span>;<\/span>\r\nimport<\/span> java<\/span>.<\/span>sql<\/span>.<\/span>Connection<\/span>;<\/span>\r\nimport<\/span> java<\/span>.<\/span>sql<\/span>.<\/span>DriverManager<\/span>;<\/span>\r\nimport<\/span> java<\/span>.<\/span>sql<\/span>.<\/span>PreparedStatement<\/span>;<\/span>\r\nimport<\/span> java<\/span>.<\/span>sql<\/span>.<\/span>ResultSet<\/span>;<\/span>\r\nimport<\/span> java<\/span>.<\/span>sql<\/span>.<\/span>SQLException<\/span>;<\/span>\r\nimport<\/span> java<\/span>.<\/span>sql<\/span>.<\/span>Statement<\/span>;<\/span>\r\n\r\npublic<\/span> class<\/span> mojia<\/span> {<\/span>\r\n    public<\/span> static<\/span> void<\/span> main<\/span>(<\/span>String<\/span>[]<\/span> args<\/span>)<\/span> {<\/span>\r\n\r\n        String<\/span> URL<\/span>  =<\/span> \"jdbc:postgresql:\/\/kai1:5432\/testdb\"<\/span>;<\/span>\r\n        String<\/span> USER<\/span> =<\/span> \"pguser01\"<\/span>;<\/span>\r\n        String<\/span> PASS<\/span> =<\/span> \"P#ssw0rd\"<\/span>;<\/span>\r\n        String<\/span> SQL1<\/span> =<\/span> \"truncate table MOJI\"<\/span>;<\/span>\r\n        String<\/span> SQL2<\/span> =<\/span> \"select * from MOJI\"<\/span>;<\/span>\r\n\r\n        \/\/<\/span>TRUNCATE<\/span> &<\/span> INSERT<\/span>\r\n        try<\/span> {<\/span>\r\n        Connection<\/span> conn<\/span> =<\/span> DriverManager<\/span>.<\/span>getConnection<\/span>(<\/span>URL<\/span>,<\/span> USER<\/span>,<\/span> PASS<\/span>);<\/span>\r\n        Statement<\/span> stmt<\/span> =<\/span> conn<\/span>.<\/span>createStatement<\/span>();<\/span>\r\n\r\n        try<\/span>{<\/span>\r\n            stmt<\/span>.<\/span>executeUpdate<\/span>(<\/span>SQL1<\/span>);<\/span>\r\n            File<\/span> file<\/span> =<\/span> new<\/span> File<\/span>(<\/span>\"\/root\/csv\/utf8.csv\"<\/span>);<\/span>\r\n            FileInputStream<\/span> input<\/span> =<\/span> new<\/span> FileInputStream<\/span>(<\/span>file<\/span>);<\/span>\r\n            InputStreamReader<\/span> stream<\/span> =<\/span> new<\/span> InputStreamReader<\/span>(<\/span>input<\/span>,<\/span>\"UTF8\"<\/span>);<\/span>\r\n            BufferedReader<\/span> br<\/span> =<\/span> new<\/span> BufferedReader<\/span>(<\/span>stream<\/span>);<\/span>\r\n            String<\/span> s<\/span>;<\/span>\r\n            while<\/span> ((<\/span> s<\/span> =<\/span> br<\/span>.<\/span>readLine<\/span>())<\/span> !=<\/span> null<\/span> )<\/span> {<\/span>\r\n                String<\/span>[]<\/span> data<\/span> =<\/span> s<\/span>.<\/span>split<\/span>(<\/span>\",\"<\/span>,<\/span> 0<\/span>);<\/span>\r\n                stmt<\/span>.<\/span>executeUpdate<\/span>(<\/span>\"INSERT INTO MOJI VALUES ('\"<\/span>\r\n                        +<\/span> data<\/span>[<\/span>0<\/span>]<\/span> +<\/span> \"','\"<\/span>\r\n                        +<\/span> data<\/span>[<\/span>1<\/span>]<\/span> +<\/span> \"','\"<\/span>\r\n                        +<\/span> data<\/span>[<\/span>2<\/span>]<\/span> +<\/span> \"','\"<\/span>\r\n                        +<\/span> data<\/span>[<\/span>3<\/span>]<\/span> +<\/span> \"','\"<\/span>\r\n                        +<\/span> data<\/span>[<\/span>4<\/span>]<\/span> +<\/span> \"','\"<\/span>\r\n                        +<\/span> data<\/span>[<\/span>5<\/span>]<\/span> +<\/span> \"','\"<\/span>\r\n                        +<\/span> data<\/span>[<\/span>6<\/span>]<\/span> +<\/span> \"','\"<\/span>\r\n                        +<\/span> data<\/span>[<\/span>7<\/span>]<\/span> +<\/span> \"','\"<\/span>\r\n                        +<\/span> data<\/span>[<\/span>8<\/span>]<\/span> +<\/span> \"','\"<\/span>\r\n                        +<\/span> data<\/span>[<\/span>9<\/span>]<\/span> +<\/span> \"')\"<\/span>\r\n                        );<\/span>\r\n            }<\/span>\r\n            br<\/span>.<\/span>close<\/span>();<\/span>\r\n            }<\/span> catch<\/span> (<\/span>IOException<\/span> e<\/span>)<\/span> {<\/span>\r\n                System<\/span>.<\/span>out<\/span>.<\/span>println<\/span>(<\/span>e<\/span>);<\/span>\r\n            }<\/span>\r\n        stmt<\/span>.<\/span>close<\/span>();<\/span>\r\n        conn<\/span>.<\/span>close<\/span>();<\/span>\r\n        }<\/span>\r\n        catch<\/span> (<\/span>Exception<\/span> e<\/span>)<\/span> {<\/span>\r\n            e<\/span>.<\/span>printStackTrace<\/span>();<\/span>\r\n            }<\/span>\r\n\r\n        \/\/<\/span>SELECT<\/span>\r\n        try<\/span>(<\/span>Connection<\/span> conn<\/span> =<\/span>\r\n                DriverManager<\/span>.<\/span>getConnection<\/span>(<\/span>URL<\/span>,<\/span> USER<\/span>,<\/span> PASS<\/span>);<\/span>\r\n                PreparedStatement<\/span> ps<\/span> =<\/span> conn<\/span>.<\/span>prepareStatement<\/span>(<\/span>SQL2<\/span>)){<\/span>\r\n\r\n                try<\/span>(<\/span>ResultSet<\/span> rs<\/span> =<\/span> ps<\/span>.<\/span>executeQuery<\/span>()){<\/span>\r\n                    int<\/span> i<\/span>=<\/span>0<\/span>;<\/span>\r\n                    while<\/span> (<\/span>rs<\/span>.<\/span>next<\/span>())<\/span> {<\/span>\r\n                        System<\/span>.<\/span>out<\/span>.<\/span>println<\/span>(<\/span>\r\n                            rs<\/span>.<\/span>getInt<\/span>(<\/span>\"col1\"<\/span>)<\/span>    +<\/span> \",\"<\/span> +<\/span>\r\n                            rs<\/span>.<\/span>getString<\/span>(<\/span>\"col2\"<\/span>)<\/span> +<\/span> \",\"<\/span> +<\/span>\r\n                            rs<\/span>.<\/span>getString<\/span>(<\/span>\"col3\"<\/span>)<\/span> +<\/span> \",\"<\/span> +<\/span>\r\n                            rs<\/span>.<\/span>getDate<\/span>(<\/span>\"col4\"<\/span>)<\/span>   +<\/span> \",\"<\/span> +<\/span>\r\n                            rs<\/span>.<\/span>getString<\/span>(<\/span>\"col5\"<\/span>)<\/span> +<\/span> \",\"<\/span> +<\/span>\r\n                            rs<\/span>.<\/span>getString<\/span>(<\/span>\"col6\"<\/span>)<\/span> +<\/span> \",\"<\/span> +<\/span>\r\n                            rs<\/span>.<\/span>getString<\/span>(<\/span>\"col7\"<\/span>)<\/span> +<\/span> \",\"<\/span> +<\/span>\r\n                            rs<\/span>.<\/span>getString<\/span>(<\/span>\"col8\"<\/span>)<\/span> +<\/span> \",\"<\/span> +<\/span>\r\n                            rs<\/span>.<\/span>getString<\/span>(<\/span>\"col9\"<\/span>)<\/span> +<\/span> \",\"<\/span> +<\/span>\r\n                            rs<\/span>.<\/span>getString<\/span>(<\/span>\"col10\"<\/span>));<\/span>\r\n                    }<\/span>\r\n                    rs<\/span>.<\/span>close<\/span>();<\/span>\r\n                    ps<\/span>.<\/span>close<\/span>();<\/span>\r\n                    conn<\/span>.<\/span>close<\/span>();<\/span>\r\n                };<\/span>\r\n            }<\/span> catch<\/span> (<\/span>SQLException<\/span> e<\/span>)<\/span> {<\/span>\r\n                e<\/span>.<\/span>printStackTrace<\/span>();<\/span>\r\n            }<\/span> catch<\/span> (<\/span>Exception<\/span> e<\/span>)<\/span> {<\/span>\r\n                e<\/span>.<\/span>printStackTrace<\/span>();<\/span>\r\n            }<\/span>\r\n        }<\/span>\r\n    }<\/span>\r\n<\/code><\/pre>\n
                                                  PHP PDO\u8fde\u63a5\u6d4b\u8bd5<\/h2>\n
                                                  <<\/span>?<\/span>php<\/span>\r\n\r\n\/\/<\/span>DB<\/span>\u63a5\u7d9a\u5b9a\u7fa9<\/span>\r\n$dsn<\/span> =<\/span> 'pgsql:host=xxxxx;port=5432;dbname=testdb'<\/span>;<\/span>\r\n$user<\/span>=<\/span>'pguser01'<\/span>;<\/span>\r\n$password<\/span>=<\/span>'P#ssw0rd'<\/span>;<\/span>\r\n$dbh<\/span> =<\/span> new<\/span> PDO<\/span>(<\/span>$dsn<\/span>,<\/span> $user<\/span>,<\/span> $password<\/span>);<\/span>\r\n\r\n\/\/<\/span>SQL<\/span>\u5b9f\u884c\u90e8\u5206<\/span>\r\n$sth<\/span> =<\/span> $dbh<\/span>-><\/span>query<\/span>(<\/span>'select * from AAA'<\/span>);<\/span>\r\nforeach<\/span>(<\/span>$sth<\/span> as<\/span> $row<\/span>){<\/span>\r\n        print<\/span>(<\/span>$row<\/span>[<\/span>'col1'<\/span>]<\/span>.<\/span>\",\"<\/span>.<\/span>$row<\/span>[<\/span>'col2'<\/span>]<\/span>.<\/span>\"<\/span>\\n<\/span>\"<\/span>);<\/span>\r\n}<\/span>\r\n?><\/span>\r\n<\/code><\/pre>\n
                                                  \u8bf7\u63d0\u4f9b\u76f8\u5173\u94fe\u63a5<\/p>\n
                                                    \n
                                                  • \n
                                                      https:\/\/aws.amazon.com\/jp\/blogs\/news\/managing-postgresql-users-and-roles\/<\/ul>\n<\/li>\n<\/ul>\n
                                                       <\/p>\n
                                                        \n
                                                      • \n
                                                          https:\/\/employment.en-japan.com\/engineerhub\/entry\/2017\/09\/05\/110000<\/ul>\n<\/li>\n<\/ul>\n
                                                           <\/p>\n
                                                            \n
                                                          • \n
                                                              \u5185\u90e8\u69cb\u9020\u304b\u3089\u5b66\u3076PostgreSQL \u8a2d\u8a08\u30fb\u904b\u7528\u8a08\u753b\u306e\u9244\u5247 4.5<\/ul>\n<\/li>\n<\/ul>\n
                                                               <\/p>\n
                                                                \n
                                                              • \n
                                                                  https:\/\/lets.postgresql.jp\/documents\/technical\/statistics\/2#db_level_stats<\/ul>\n<\/li>\n<\/ul>\n
                                                                   <\/p>\n
                                                                    https:\/\/www.percona.com\/blog\/2018\/08\/10\/tuning-autovacuum-in-postgresql-and-autovacuum-internals\/<\/ul>\n","protected":false},"excerpt":{"rendered":"
                                                                    \u5b66\u4e60PostgreSQL \u5173\u4e8ePostgreSQL\u7684\u8bbe\u8ba1\u3001\u6784\u5efa\u548c\u6027\u80fd\u8c03\u4f18\u7684\u7b14\u8bb0\u3002 \u5b89\u88c5\u548c\u521d\u59cb\u8bbe\u7f6e 1. \u67b6\u8bbe  […]<\/p>\n","protected":false},"author":11,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-41650","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nPostgreSQL\u6570\u636e\u5e93\u7efc\u8ff0 - Blog - Silicon Cloud<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql\u6570\u636e\u5e93\u7efc\u8ff0\/\" \/>\n<meta property=\"og:locale\" content=\"zh_CN\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL\u6570\u636e\u5e93\u7efc\u8ff0\" \/>\n<meta property=\"og:description\" content=\"\u5b66\u4e60PostgreSQL \u5173\u4e8ePostgreSQL\u7684\u8bbe\u8ba1\u3001\u6784\u5efa\u548c\u6027\u80fd\u8c03\u4f18\u7684\u7b14\u8bb0\u3002 \u5b89\u88c5\u548c\u521d\u59cb\u8bbe\u7f6e 1. \u67b6\u8bbe […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql\u6570\u636e\u5e93\u7efc\u8ff0\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:published_time\" content=\"2023-06-21T01:55:07+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-04-28T22:20:26+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/cdn.silicloud.com\/blog-img\/blog\/img\/657d479c37434c4406ca8d92\/45-0.png\" \/>\n<meta name=\"author\" content=\"\u65b0, \u97f5\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u4f5c\u8005\" \/>\n\t<meta name=\"twitter:data1\" content=\"\u65b0, \u97f5\" \/>\n\t<meta name=\"twitter:label2\" content=\"\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4\" \/>\n\t<meta name=\"twitter:data2\" content=\"15 \u5206\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0\/\",\"url\":\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0\/\",\"name\":\"PostgreSQL\u6570\u636e\u5e93\u7efc\u8ff0 - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#website\"},\"datePublished\":\"2023-06-21T01:55:07+00:00\",\"dateModified\":\"2024-04-28T22:20:26+00:00\",\"author\":{\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/4ba4019495123db3038fd0809e6959c9\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0\/#breadcrumb\"},\"inLanguage\":\"zh-Hans\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"\u9996\u9875\",\"item\":\"https:\/\/www.silicloud.com\/zh\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL\u6570\u636e\u5e93\u7efc\u8ff0\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/zh\/blog\/\",\"name\":\"Blog - Silicon Cloud\",\"description\":\"\",\"inLanguage\":\"zh-Hans\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/4ba4019495123db3038fd0809e6959c9\",\"name\":\"\u65b0, \u97f5\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"zh-Hans\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d484b6c6e4ae82e8a9efea989e1d2af46d9b6ef128101e63b18f559fca0ae627?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d484b6c6e4ae82e8a9efea989e1d2af46d9b6ef128101e63b18f559fca0ae627?s=96&d=mm&r=g\",\"caption\":\"\u65b0, \u97f5\"},\"url\":\"https:\/\/www.silicloud.com\/zh\/blog\/author\/yunxin\/\"},{\"@type\":\"ImageObject\",\"inLanguage\":\"zh-Hans\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0\/#local-main-organization-logo\",\"url\":\"\",\"contentUrl\":\"\",\"caption\":\"Blog - Silicon Cloud\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL\u6570\u636e\u5e93\u7efc\u8ff0 - Blog - Silicon Cloud","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql\u6570\u636e\u5e93\u7efc\u8ff0\/","og_locale":"zh_CN","og_type":"article","og_title":"PostgreSQL\u6570\u636e\u5e93\u7efc\u8ff0","og_description":"\u5b66\u4e60PostgreSQL \u5173\u4e8ePostgreSQL\u7684\u8bbe\u8ba1\u3001\u6784\u5efa\u548c\u6027\u80fd\u8c03\u4f18\u7684\u7b14\u8bb0\u3002 \u5b89\u88c5\u548c\u521d\u59cb\u8bbe\u7f6e 1. \u67b6\u8bbe […]","og_url":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql\u6570\u636e\u5e93\u7efc\u8ff0\/","og_site_name":"Blog - Silicon Cloud","article_published_time":"2023-06-21T01:55:07+00:00","article_modified_time":"2024-04-28T22:20:26+00:00","og_image":[{"url":"https:\/\/cdn.silicloud.com\/blog-img\/blog\/img\/657d479c37434c4406ca8d92\/45-0.png"}],"author":"\u65b0, \u97f5","twitter_card":"summary_large_image","twitter_misc":{"\u4f5c\u8005":"\u65b0, \u97f5","\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4":"15 \u5206"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0\/","url":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0\/","name":"PostgreSQL\u6570\u636e\u5e93\u7efc\u8ff0 - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/zh\/blog\/#website"},"datePublished":"2023-06-21T01:55:07+00:00","dateModified":"2024-04-28T22:20:26+00:00","author":{"@id":"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/4ba4019495123db3038fd0809e6959c9"},"breadcrumb":{"@id":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0\/#breadcrumb"},"inLanguage":"zh-Hans","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"\u9996\u9875","item":"https:\/\/www.silicloud.com\/zh\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL\u6570\u636e\u5e93\u7efc\u8ff0"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/zh\/blog\/#website","url":"https:\/\/www.silicloud.com\/zh\/blog\/","name":"Blog - Silicon Cloud","description":"","inLanguage":"zh-Hans"},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/4ba4019495123db3038fd0809e6959c9","name":"\u65b0, \u97f5","image":{"@type":"ImageObject","inLanguage":"zh-Hans","@id":"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d484b6c6e4ae82e8a9efea989e1d2af46d9b6ef128101e63b18f559fca0ae627?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d484b6c6e4ae82e8a9efea989e1d2af46d9b6ef128101e63b18f559fca0ae627?s=96&d=mm&r=g","caption":"\u65b0, \u97f5"},"url":"https:\/\/www.silicloud.com\/zh\/blog\/author\/yunxin\/"},{"@type":"ImageObject","inLanguage":"zh-Hans","@id":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e7%bb%bc%e8%bf%b0\/#local-main-organization-logo","url":"","contentUrl":"","caption":"Blog - Silicon Cloud"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/posts\/41650","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/comments?post=41650"}],"version-history":[{"count":2,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/posts\/41650\/revisions"}],"predecessor-version":[{"id":83567,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/posts\/41650\/revisions\/83567"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/media?parent=41650"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/categories?post=41650"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/tags?post=41650"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}