{"id":1517,"date":"2018-10-25T17:12:26","date_gmt":"2018-10-25T09:12:26","guid":{"rendered":"https:\/\/www.strongd.net\/?p=1517"},"modified":"2018-10-25T17:12:26","modified_gmt":"2018-10-25T09:12:26","slug":"%e4%b8%80%e5%a4%a9%e5%ad%a6%e4%bc%9apostgresql%e5%ba%94%e7%94%a8%e5%bc%80%e5%8f%91%e4%b8%8e%e7%ae%a1%e7%90%86-1-%e5%a6%82%e4%bd%95%e6%90%ad%e5%bb%ba%e4%b8%80%e5%a5%97%e5%ad%a6%e4%b9%a0%e3%80%81","status":"publish","type":"post","link":"https:\/\/www.strongd.net\/?p=1517","title":{"rendered":"\u4e00\u5929\u5b66\u4f1aPostgreSQL\u5e94\u7528\u5f00\u53d1\u4e0e\u7ba1\u7406 &#8211; 1 \u5982\u4f55\u642d\u5efa\u4e00\u5957\u5b66\u4e60\u3001\u5f00\u53d1PostgreSQL\u7684\u73af\u5883"},"content":{"rendered":"<h2>\u80cc\u666f<\/h2>\n<p>\u4e07\u4e8b\u5f00\u5934\u96be\uff0c\u642d\u5efa\u597d\u4e00\u5957\u5b66\u4e60\u3001\u5f00\u53d1PostgreSQL\u7684\u73af\u5883\uff0c\u662f\u91cd\u4e2d\u4e4b\u91cd\u3002<\/p>\n<p>\u56e0\u4e3a\u5176\u4ed6\u5e73\u53f0(Ubuntu, CentOS, MAC)\u7684\u7528\u6237\u5927\u591a\u6570\u90fd\u5177\u5907\u4e86\u81ea\u884c\u5b89\u88c5\u6570\u636e\u5e93\u7684\u80fd\u529b\uff0c\u5728\u8fd9\u91cc\u6211\u53ea\u5199\u4e00\u4e2a\u9762\u5411Windows\u7528\u6237\u7684\u5b66\u4e60\u73af\u5883\u642d\u5efa\u6587\u6863\u3002<\/p>\n<p>\u5206\u4e3a\u4e09\u4e2a\u90e8\u5206\uff0c\u7528\u6237\u53ef\u4ee5\u81ea\u7531\u9009\u62e9\u3002<\/p>\n<p>\u5982\u679c\u4f60\u60f3\u6df1\u5165\u7684\u5b66\u4e60PostgreSQL\uff0c\u5efa\u8bae\u642d\u5efaPostgreSQL on Linux\u7684\u73af\u5883\u3002\u5982\u679c\u4f60\u53ea\u662f\u60f3\u5c06\u6570\u636e\u5e93\u4f7f\u7528\u5728\u65e5\u5e38\u7684\u5e94\u7528\u5f00\u53d1\u5de5\u4f5c\u4e2d\uff0c\u6709\u4e5f\u4e0d\u9700\u8981PG\u7684\u5176\u4ed6\u9644\u52a0\u63d2\u4ef6\u7684\u529f\u80fd\uff0c\u90a3\u4e48\u4f60\u53ef\u4ee5\u9009\u62e9PostgreSQL on Win\u7684\u73af\u5883\u642d\u5efa\u3002<\/p>\n<p>\u5982\u679c\u4f60\u4e0d\u60f3\u642d\u5efa\u672c\u5730\u7684PostgreSQL\uff0c\u90a3\u4e48\u4f60\u53ef\u4ee5\u4f7f\u7528\u4e91\u6570\u636e\u5e93\u670d\u52a1\uff0c\u6bd4\u5982\u963f\u91cc\u4e91RDS for PostgreSQL\u3002<\/p>\n<h2>\u672c\u7ae0\u5927\u7eb2<\/h2>\n<h2>\u4e00\u3001PostgreSQL on Win\u73af\u5883\u642d\u5efa<\/h2>\n<h3>1 \u73af\u5883\u8981\u6c42<\/h3>\n<h3>2 \u4e0b\u8f7dPostgreSQL\u5b89\u88c5\u5305<\/h3>\n<h3>3 \u89e3\u538bPostgreSQL\u5b89\u88c5\u5305<\/h3>\n<h3>4 \u4e0b\u8f7dpgadmin\u5b89\u88c5\u5305(\u53ef\u9009)<\/h3>\n<h3>5 \u5b89\u88c5pgadmin(\u53ef\u9009)<\/h3>\n<h3>6 \u89c4\u5212\u6570\u636e\u6587\u4ef6\u76ee\u5f55<\/h3>\n<h3>7 \u521d\u59cb\u5316\u6570\u636e\u5e93\u96c6\u7fa4<\/h3>\n<h3>8 \u914d\u7f6epostgresql.conf<\/h3>\n<h3>9 \u914d\u7f6epg_hba.conf(\u53ef\u9009)<\/h3>\n<h3>10 \u542f\u52a8\u3001\u505c\u6b62\u6570\u636e\u5e93\u96c6\u7fa4<\/h3>\n<h3>11 \u5982\u4f55\u81ea\u52a8\u542f\u52a8\u6570\u636e\u5e93\u96c6\u7fa4<\/h3>\n<h3>12 \u4f7f\u7528psql \u547d\u4ee4\u884c\u8fde\u63a5\u6570\u636e\u5e93<\/h3>\n<h3>13 \u65b0\u589e\u7528\u6237<\/h3>\n<h3>14 \u4f7f\u7528psql\u5e2e\u52a9<\/h3>\n<h3>15 \u4f7f\u7528psql\u8bed\u6cd5\u8865\u9f50<\/h3>\n<h3>16 \u4f7f\u7528psql sql\u8bed\u6cd5\u5e2e\u52a9<\/h3>\n<h3>17 \u67e5\u770b\u5f53\u524d\u914d\u7f6e<\/h3>\n<h3>18 \u8bbe\u7f6e\u4f1a\u8bdd\u53c2\u6570<\/h3>\n<h3>19 \u5728psql\u4e2d\u5207\u6362\u5230\u53e6\u4e00\u4e2a\u7528\u6237\u6216\u6570\u636e\u5e93<\/h3>\n<h3>20 \u4f7f\u7528pgadmin4\u8fde\u63a5\u6570\u636e\u5e93<\/h3>\n<h3>21 \u6587\u6863<\/h3>\n<h2>\u4e8c\u3001PostgreSQL on Linux(\u865a\u62df\u673a)\u73af\u5883\u642d\u5efa<\/h2>\n<h3>1 \u73af\u5883\u8981\u6c42<\/h3>\n<h3>2 \u4e0b\u8f7dLinux\u955c\u50cf<\/h3>\n<h3>3 \u5b89\u88c5VMware Workstation(\u8bd5\u7528\u7248\u672c)<\/h3>\n<h3>4 \u5b89\u88c5securecrt(\u8bd5\u7528\u7248\u672c)<\/h3>\n<h3>5 \u5b89\u88c5Linux\u865a\u62df\u673a<\/h3>\n<h3>6 \u914d\u7f6eLinux\u865a\u62df\u673a\u7f51\u7edc<\/h3>\n<h3>7 securecrt\u7ec8\u7aef\u8fde\u63a5Linux<\/h3>\n<h3>8 \u914d\u7f6elinux<\/h3>\n<h3>9 \u914d\u7f6eyum\u4ed3\u5e93(\u53ef\u9009)<\/h3>\n<h3>10 \u521b\u5efa\u666e\u901a\u7528\u6237<\/h3>\n<h3>11 \u89c4\u5212\u6570\u636e\u5e93\u5b58\u50a8\u76ee\u5f55<\/h3>\n<h3>12 \u4e0b\u8f7dPostgreSQL\u6e90\u7801<\/h3>\n<h3>13 \u5b89\u88c5PostgreSQL<\/h3>\n<h3>14 \u914d\u7f6elinux\u7528\u6237\u73af\u5883\u53d8\u91cf<\/h3>\n<h3>15 \u521d\u59cb\u5316\u6570\u636e\u5e93\u96c6\u7fa4<\/h3>\n<h3>16 \u914d\u7f6e\u6570\u636e\u5e93<\/h3>\n<h3>17 \u542f\u52a8\u6570\u636e\u5e93\u96c6\u7fa4<\/h3>\n<h3>18 \u8fde\u63a5\u6570\u636e\u5e93<\/h3>\n<h3>19 \u5b89\u88c5pgadmin(\u53ef\u9009)<\/h3>\n<h3>20 \u914d\u7f6epgadmin(\u53ef\u9009)<\/h3>\n<h3>21 \u4f7f\u7528pgadmin\u8fde\u63a5\u6570\u636e\u5e93(\u53ef\u9009)<\/h3>\n<h2>\u4e09\u3001\u4e91\u6570\u636e\u5e93RDS for PostgreSQL<\/h2>\n<h3>1 \u8d2d\u4e70\u4e91\u6570\u636e\u5e93<\/h3>\n<h3>2 \u8bbe\u7f6e\u5e76\u8bb0\u4f4fRDS for PostgreSQL\u6570\u636e\u5e93\u6839\u7528\u6237\u540d\u548c\u5bc6\u7801<\/h3>\n<h3>3 \u914d\u7f6e\u7f51\u7edc<\/h3>\n<h3>4 \u914d\u7f6e\u767d\u540d\u5355<\/h3>\n<h3>5 \u672c\u5730\u5b89\u88c5pgadmin(\u53ef\u9009)<\/h3>\n<h3>6 \u672c\u5730\u914d\u7f6epgadmin(\u53ef\u9009)<\/h3>\n<h3>7 \u4f7f\u7528pgadmin\u8fde\u63a5RDS PostgreSQL\u6570\u636e\u5e93(\u53ef\u9009)<\/h3>\n<h2>\u4e00\u3001PostgreSQL on Win\u73af\u5883\u642d\u5efa<\/h2>\n<h3>1 \u73af\u5883\u8981\u6c42<\/h3>\n<p>Win 7 x64, 8GB\u4ee5\u4e0a\u5185\u5b58, 4\u6838\u4ee5\u4e0a, SSD\u786c\u76d8(\u63a8\u8350)\uff0c100GB\u4ee5\u4e0a\u5269\u4f59\u7a7a\u95f4, \u53ef\u4ee5\u8bbf\u95ee\u516c\u7f51(10MB\/s\u4ee5\u4e0a\u7f51\u7edc\u5e26\u5bbd)<\/p>\n<h3>2 \u4e0b\u8f7dPostgreSQL\u5b89\u88c5\u5305<\/h3>\n<p><a href=\"https:\/\/www.postgresql.org\/download\/windows\/\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.73\">https:\/\/www.postgresql.org\/download\/windows\/<\/a><\/p>\n<p>\u5efa\u8bae\u4e0b\u8f7d\u9ad8\u7ea7\u5b89\u88c5\u5305\uff0c\u4e0d\u9700\u8981\u5b89\u88c5\uff0c\u76f4\u63a5\u4f7f\u7528\u3002<\/p>\n<p>\u4e0b\u8f7dwin x64\u7684\u7248\u672c(\u5efa\u8bae\u4e0b\u8f7d\u6700\u65b0\u7248\u672c)<\/p>\n<p><a href=\"http:\/\/www.enterprisedb.com\/products\/pgbindownload.do\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.74\">http:\/\/www.enterprisedb.com\/products\/pgbindownload.do<\/a><\/p>\n<p>\u4f8b\u5982<\/p>\n<p><a href=\"https:\/\/get.enterprisedb.com\/postgresql\/postgresql-9.6.2-3-windows-x64-binaries.zip\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.75\">https:\/\/get.enterprisedb.com\/postgresql\/postgresql-9.6.2-3-windows-x64-binaries.zip<\/a><\/p>\n<h3>3 \u89e3\u538bPostgreSQL\u5b89\u88c5\u5305<\/h3>\n<p>postgresql-9.6.2-3-windows-x64-binaries.zip<\/p>\n<p>\u4f8b\u5982\u89e3\u538b\u5230d:\\pgsql<\/p>\n<p><a href=\"https:\/\/github.com\/digoal\/blog\/blob\/master\/201704\/20170411_01_pic_001.jpg\" target=\"_blank\" rel=\"noopener\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.77\"><img decoding=\"async\" src=\"https:\/\/github.com\/digoal\/blog\/raw\/master\/201704\/20170411_01_pic_001.jpg\" alt=\"pic\" \/><\/a><\/p>\n<p>bin: \u4e8c\u8fdb\u5236\u6587\u4ef6<\/p>\n<p>doc: \u6587\u6863<\/p>\n<p>include: \u5934\u6587\u4ef6<\/p>\n<p>lib: \u52a8\u6001\u5e93<\/p>\n<p>pgAdmin 4: \u56fe\u5f62\u5316\u7ba1\u7406\u5de5\u5177<\/p>\n<p>share: \u6269\u5c55\u5e93<\/p>\n<p>StackBuilder: \u6253\u5305\u5e93<\/p>\n<p>symbols: \u7b26\u53f7\u8868<\/p>\n<h3>4 \u4e0b\u8f7dpgadmin\u5b89\u88c5\u5305(\u53ef\u9009)<\/h3>\n<p>\u5982\u679cPostgreSQL\u5305\u4e2d\u6ca1\u6709\u5305\u542bpgAdmin\uff0c\u5efa\u8bae\u81ea\u884c\u4e0b\u8f7d\u4e00\u4e2a<\/p>\n<p>\u5efa\u8bae\u4e0b\u8f7dpgadmin4(pgadmin3\u4e0d\u518d\u7ef4\u62a4)<\/p>\n<p><a href=\"https:\/\/www.pgadmin.org\/index.php\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.79\">https:\/\/www.pgadmin.org\/index.php<\/a><\/p>\n<p><a href=\"https:\/\/www.postgresql.org\/ftp\/pgadmin3\/pgadmin4\/v1.3\/windows\/\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.80\">https:\/\/www.postgresql.org\/ftp\/pgadmin3\/pgadmin4\/v1.3\/windows\/<\/a><\/p>\n<h3>5 \u5b89\u88c5pgadmin(\u53ef\u9009)<\/h3>\n<h3>6 \u89c4\u5212\u6570\u636e\u6587\u4ef6\u76ee\u5f55<\/h3>\n<p>\u4f8b\u5982\u5c06D\u76d8\u7684pgdata\u4f5c\u4e3a\u6570\u636e\u5e93\u76ee\u5f55\u3002<\/p>\n<p>\u65b0\u5efad:\\pgdata\u7a7a\u76ee\u5f55\u3002<\/p>\n<h3>7 \u521d\u59cb\u5316\u6570\u636e\u5e93\u96c6\u7fa4<\/h3>\n<p>\u4ee5\u7ba1\u7406\u5458\u8eab\u4efd\u6253\u5f00cmd.exe<\/p>\n<p><a href=\"https:\/\/github.com\/digoal\/blog\/blob\/master\/201704\/20170411_01_pic_002.jpg\" target=\"_blank\" rel=\"noopener\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.84\"><img decoding=\"async\" src=\"https:\/\/github.com\/digoal\/blog\/raw\/master\/201704\/20170411_01_pic_002.jpg\" alt=\"pic\" \/><\/a><\/p>\n<pre><code>&gt;d:  \r\n  \r\n&gt;cd pgsql  \r\n  \r\n&gt;cd bin  \r\n  \r\n&gt;initdb.exe -D d:\\pgdata -E UTF8 --locale=C -U postgres  \r\n  \r\n\u521d\u59cb\u5316\u65f6\uff0c\u6307\u5b9a\u6570\u636e\u5e93\u6587\u4ef6\u76ee\u5f55\uff0c\u5b57\u7b26\u96c6\uff0c\u672c\u5730\u5316\uff0c\u6570\u636e\u5e93\u8d85\u7ea7\u7528\u6237\u540d  \r\n<\/code><\/pre>\n<p><a href=\"https:\/\/github.com\/digoal\/blog\/blob\/master\/201704\/20170411_01_pic_003.jpg\" target=\"_blank\" rel=\"noopener\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.85\"><img decoding=\"async\" src=\"https:\/\/github.com\/digoal\/blog\/raw\/master\/201704\/20170411_01_pic_003.jpg\" alt=\"pic\" \/><\/a><\/p>\n<p><a href=\"https:\/\/github.com\/digoal\/blog\/blob\/master\/201704\/20170411_01_pic_004.jpg\" target=\"_blank\" rel=\"noopener\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.86\"><img decoding=\"async\" src=\"https:\/\/github.com\/digoal\/blog\/raw\/master\/201704\/20170411_01_pic_004.jpg\" alt=\"pic\" \/><\/a><\/p>\n<h3>8 \u914d\u7f6epostgresql.conf<\/h3>\n<p>\u6570\u636e\u5e93\u914d\u7f6e\u6587\u4ef6\u540d\u5b57postgresql.conf\uff0c\u8fd9\u4e2a\u6587\u4ef6\u5728\u6570\u636e\u6587\u4ef6\u76ee\u5f55D:\\pgdata\u4e2d\u3002<\/p>\n<p>\u5c06\u4ee5\u4e0b\u5185\u5bb9\u8ffd\u52a0\u5230postgresql.conf\u6587\u4ef6\u672b\u5c3e<\/p>\n<pre><code>listen_addresses = '0.0.0.0'  \r\nport = 1921  \r\nmax_connections = 200  \r\ntcp_keepalives_idle = 60  \r\ntcp_keepalives_interval = 10  \r\ntcp_keepalives_count = 6  \r\nshared_buffers = 512MB  \r\nmaintenance_work_mem = 64MB  \r\ndynamic_shared_memory_type = windows  \r\nvacuum_cost_delay = 0  \r\nbgwriter_delay = 10ms  \r\nbgwriter_lru_maxpages = 1000  \r\nbgwriter_lru_multiplier = 5.0  \r\nbgwriter_flush_after = 0  \r\nold_snapshot_threshold = -1  \r\nwal_level = minimal  \r\nsynchronous_commit = off  \r\nfull_page_writes = on  \r\nwal_buffers = 64MB  \r\nwal_writer_delay = 10ms  \r\nwal_writer_flush_after = 4MB  \r\ncheckpoint_timeout = 35min  \r\nmax_wal_size = 2GB  \r\nmin_wal_size = 80MB  \r\ncheckpoint_completion_target = 0.1  \r\ncheckpoint_flush_after = 0  \r\nrandom_page_cost = 1.5  \r\nlog_destination = 'csvlog'  \r\nlogging_collector = on  \r\nlog_directory = 'pg_log'  \r\nlog_truncate_on_rotation = on  \r\nlog_checkpoints = on  \r\nlog_connections = on  \r\nlog_disconnections = on  \r\nlog_error_verbosity = verbose  \r\nlog_temp_files = 8192  \r\nlog_timezone = 'Asia\/Hong_Kong'  \r\nautovacuum = on  \r\nlog_autovacuum_min_duration = 0  \r\nautovacuum_naptime = 20s  \r\nautovacuum_vacuum_scale_factor = 0.05  \r\nautovacuum_freeze_max_age = 1500000000  \r\nautovacuum_multixact_freeze_max_age = 1600000000  \r\nautovacuum_vacuum_cost_delay = 0  \r\nvacuum_freeze_table_age = 1400000000  \r\nvacuum_multixact_freeze_table_age = 1500000000  \r\ndatestyle = 'iso, mdy'  \r\ntimezone = 'Asia\/Hong_Kong'  \r\nlc_messages = 'C'  \r\nlc_monetary = 'C'  \r\nlc_numeric = 'C'  \r\nlc_time = 'C'  \r\ndefault_text_search_config = 'pg_catalog.english'  \r\n<\/code><\/pre>\n<h3>9 \u914d\u7f6epg_hba.conf(\u53ef\u9009)<\/h3>\n<p>\u6570\u636e\u5e93\u9632\u706b\u5899\u6587\u4ef6\u540d\u5b57pg_hba.conf\uff0c\u8fd9\u4e2a\u6587\u4ef6\u5728\u6570\u636e\u6587\u4ef6\u76ee\u5f55D:\\pgdata\u4e2d\u3002<\/p>\n<p>\u5c06\u4ee5\u4e0b\u5185\u5bb9\u8ffd\u52a0\u5230\u6587\u4ef6\u672b\u5c3e\uff0c\u8868\u793a\u5141\u8bb8\u7f51\u7edc\u7528\u6237\u4f7f\u7528\u7528\u6237\u5bc6\u7801\u8fde\u63a5\u4f60\u7684postgresql\u6570\u636e\u5e93.<\/p>\n<pre><code>host all all 0.0.0.0\/0 md5  \r\n<\/code><\/pre>\n<h3>10 \u542f\u52a8\u3001\u505c\u6b62\u6570\u636e\u5e93\u96c6\u7fa4<\/h3>\n<p>\u4f7f\u7528\u547d\u4ee4\u884c\u542f\u52a8\u6570\u636e\u5e93\u96c6\u7fa4<\/p>\n<pre><code>&gt;d:  \r\n  \r\n&gt;cd pgsql  \r\n  \r\n&gt;cd bin  \r\n  \r\nD:\\pgsql\\bin&gt;pg_ctl.exe start -D d:\\pgdata  \r\n\u6b63\u5728\u542f\u52a8\u670d\u52a1\u5668\u8fdb\u7a0b  \r\n  \r\nD:\\pgsql\\bin&gt;LOG:  00000: redirecting log output to logging collector process  \r\nHINT:  Future log output will appear in directory \"pg_log\".  \r\nLOCATION:  SysLogger_Start, syslogger.c:622  \r\n<\/code><\/pre>\n<p>\u4f7f\u7528\u547d\u4ee4\u884c\u505c\u6b62\u6570\u636e\u5e93\u96c6\u7fa4<\/p>\n<pre><code>D:\\pgsql\\bin&gt;pg_ctl.exe stop -m fast -D \"d:\\pgdata\"\r\n\u7b49\u5f85\u670d\u52a1\u5668\u8fdb\u7a0b\u5173\u95ed .... \u5b8c\u6210\r\n\u670d\u52a1\u5668\u8fdb\u7a0b\u5df2\u7ecf\u5173\u95ed\r\n<\/code><\/pre>\n<h3>11 \u5982\u4f55\u81ea\u52a8\u542f\u52a8\u6570\u636e\u5e93\u96c6\u7fa4<\/h3>\n<p>\u914d\u7f6ewindows\u81ea\u52a8\u542f\u52a8\u670d\u52a1.<\/p>\n<h3>12 \u4f7f\u7528psql \u547d\u4ee4\u884c\u8fde\u63a5\u6570\u636e\u5e93<\/h3>\n<p>psql -h IP\u5730\u5740 -p \u7aef\u53e3 -U \u7528\u6237\u540d \u6570\u636e\u5e93\u540d<\/p>\n<pre><code>D:\\pgsql\\bin&gt;psql -h 127.0.0.1 -p 1921 -U postgres postgres  \r\npsql (9.6.2)  \r\n\u8f93\u5165 \"help\" \u6765\u83b7\u53d6\u5e2e\u52a9\u4fe1\u606f.  \r\n  \r\npostgres=# \\dt  \r\n<\/code><\/pre>\n<h3>13 \u65b0\u589e\u7528\u6237<\/h3>\n<p>\u65b0\u5efa\u7528\u6237\u5c5e\u4e8e\u6570\u636e\u5e93\u64cd\u4f5c\uff0c\u5148\u4f7f\u7528psql\u548c\u8d85\u7ea7\u7528\u6237postgres\u8fde\u63a5\u5230\u6570\u636e\u5e93\u3002<\/p>\n<p>\u65b0\u589e\u4e00\u4e2a\u666e\u901a\u7528\u6237<\/p>\n<pre><code>postgres=# create role digoal login encrypted password 'pwd_digoal';  \r\nCREATE ROLE  \r\n<\/code><\/pre>\n<p>\u65b0\u589e\u4e00\u4e2a\u8d85\u7ea7\u7528\u6237<\/p>\n<pre><code>postgres=# create role dba_digoal login superuser encrypted password 'dba_pwd_digoal';  \r\nCREATE ROLE  \r\n<\/code><\/pre>\n<p>\u65b0\u589e\u4e00\u4e2a\u6d41\u590d\u5236\u7528\u6237<\/p>\n<pre><code>postgres=# create role digoal_rep replication login encrypted password 'pwd';  \r\nCREATE ROLE  \r\n<\/code><\/pre>\n<p>\u4f60\u8fd8\u53ef\u4ee5\u5c06\u4e00\u4e2a\u7528\u6237\u5728\u4e0d\u540c\u89d2\u8272\u4e4b\u95f4\u5207\u6362<\/p>\n<p>\u4f8b\u5982\u5c06digoal\u8bbe\u7f6e\u4e3a\u8d85\u7ea7\u7528\u6237<\/p>\n<pre><code>postgres=# alter role digoal superuser;  \r\nALTER ROLE  \r\n<\/code><\/pre>\n<p>\u67e5\u770b\u5df2\u6709\u7528\u6237<\/p>\n<pre><code>postgres=# \\du+  \r\n                                 \u89d2\u8272\u5217\u8868  \r\n  \u89d2\u8272\u540d\u79f0  |                    \u5c5e\u6027                    | \u6210\u5458\u5c5e\u4e8e | \u63cf\u8ff0  \r\n------------+--------------------------------------------+----------+------  \r\n dba_digoal | \u8d85\u7ea7\u7528\u6237                                   | {}       |  \r\n digoal     | \u8d85\u7ea7\u7528\u6237                                   | {}       |  \r\n digoal_rep | \u590d\u5236                                       | {}       |  \r\n postgres   | \u8d85\u7ea7\u7528\u6237, \u5efa\u7acb\u89d2\u8272, \u5efa\u7acb DB, \u590d\u5236, \u7ed5\u8fc7RLS | {}       |  \r\n<\/code><\/pre>\n<h3>14 \u4f7f\u7528psql\u5e2e\u52a9<\/h3>\n<p>psql\u6709\u5f88\u591a\u5feb\u6377\u7684\u547d\u4ee4\uff0c\u4f7f\u7528\\?\u5c31\u53ef\u4ee5\u67e5\u770b\u3002<\/p>\n<pre><code>postgres=# \\?  \r\n\u4e00\u822c\u6027  \r\n  \\copyright            \u663e\u793aPostgreSQL\u7684\u4f7f\u7528\u548c\u53d1\u884c\u8bb8\u53ef\u6761\u6b3e  \r\n  \\errverbose            \u4ee5\u6700\u5197\u957f\u7684\u5f62\u5f0f\u663e\u793a\u6700\u8fd1\u7684\u9519\u8bef\u6d88\u606f  \r\n  \\g [\u6587\u4ef6] or;     \u6267\u884c\u67e5\u8be2 (\u5e76\u628a\u7ed3\u679c\u5199\u5165\u6587\u4ef6\u6216 |\u7ba1\u9053)  \r\n  \\gexec                 \u6267\u884c\u7b56\u7565\uff0c\u7136\u540e\u6267\u884c\u5176\u7ed3\u679c\u4e2d\u7684\u6bcf\u4e2a\u503c  \r\n  \\gset [PREFIX]     \u6267\u884c\u67e5\u8be2\u5e76\u628a\u7ed3\u679c\u5b58\u5230psql\u53d8\u91cf\u4e2d  \r\n  \\q             \u9000\u51fa psql  \r\n  \\crosstabview [COLUMNS] \u6267\u884c\u67e5\u8be2\u5e76\u4e14\u4ee5\u4ea4\u53c9\u8868\u663e\u793a\u7ed3\u679c  \r\n  \\watch [SEC]          \u6bcf\u9694SEC\u79d2\u6267\u884c\u4e00\u6b21\u67e5\u8be2  \r\n  \r\n\u5e2e\u52a9  \r\n  \\? [commands]          \u663e\u793a\u53cd\u659c\u7ebf\u547d\u4ee4\u7684\u5e2e\u52a9  \r\n  \r\n  ......  \r\n  \r\n<\/code><\/pre>\n<h3>15 \u4f7f\u7528psql\u8bed\u6cd5\u8865\u9f50<\/h3>\n<p>\u5982\u679c\u4f60\u7f16\u8bd1PostgreSQL\u4f7f\u7528\u4e86\u8865\u9f50\u9009\u9879\uff0c\u90a3\u4e48\u5728psql\u4e2d\u6309TAB\u952e\uff0c\u53ef\u4ee5\u81ea\u52a8\u8865\u9f50\u547d\u4ee4\u3002<\/p>\n<h3>16 \u4f7f\u7528psql sql\u8bed\u6cd5\u5e2e\u52a9<\/h3>\n<p>\u5982\u679c\u4f60\u5fd8\u8bb0\u4e86\u67d0\u4e2aSQL\u7684\u8bed\u6cd5\uff0c\u4f7f\u7528\\h \u547d\u4ee4\u5373\u53ef\u6253\u5370\u547d\u4ee4\u7684\u5e2e\u52a9<\/p>\n<p>\u4f8b\u5982<\/p>\n<pre><code>postgres=# \\h create table  \r\n\u547d\u4ee4\uff1a       CREATE TABLE  \r\n\u63cf\u8ff0\uff1a       \u5efa\u7acb\u65b0\u7684\u6570\u636e\u8868  \r\n\u8bed\u6cd5\uff1a  \r\nCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI  \r\nSTS ] \u8868\u540d ( [  \r\n  { \u5217\u540d\u79f0 \u6570\u636e_\u7c7b\u578b [ COLLATE \u6821\u5bf9\u89c4\u5219 ] [ \u5217\u7ea6\u675f [ ... ] ]  \r\n    | \u8868\u7ea6\u675f  \r\n    | LIKE \u6e90\u8868 [ like\u9009\u9879 ... ] }  \r\n    [, ... ]  \r\n] )  \r\n  \r\n......  \r\n<\/code><\/pre>\n<h3>17 \u67e5\u770b\u5f53\u524d\u914d\u7f6e<\/h3>\n<p>show \u53c2\u6570\u540d<\/p>\n<pre><code>postgres=# show client_encoding;  \r\n client_encoding  \r\n-----------------  \r\n GBK  \r\n(1 \u884c\u8bb0\u5f55)  \r\n<\/code><\/pre>\n<p>\u67e5\u770bpg_settings<\/p>\n<pre><code>postgres=# select * from pg_settings;  \r\n<\/code><\/pre>\n<h3>18 \u8bbe\u7f6e\u4f1a\u8bdd\u53c2\u6570<\/h3>\n<p>set \u53c2\u6570\u540d=\u503c;<\/p>\n<pre><code>postgres=# set client_encoding='sql_ascii';  \r\nSET  \r\n<\/code><\/pre>\n<h3>19 \u5728psql\u4e2d\u5207\u6362\u5230\u53e6\u4e00\u4e2a\u7528\u6237\u6216\u6570\u636e\u5e93<\/h3>\n<p>\\c \u5207\u6362\u5230\u5176\u4ed6\u7528\u6237\u6216\u6570\u636e\u5e93<\/p>\n<pre><code>postgres=# \\c template1 digoal  \r\n\u60a8\u73b0\u5728\u5df2\u7ecf\u8fde\u63a5\u5230\u6570\u636e\u5e93 \"template1\",\u7528\u6237 \"digoal\".  \r\n<\/code><\/pre>\n<h3>20 \u4f7f\u7528pgadmin4\u8fde\u63a5\u6570\u636e\u5e93<\/h3>\n<p>pgAdmin4\u88ab\u5b89\u88c5\u5728\u8fd9\u4e2a\u76ee\u5f55<\/p>\n<pre><code>d:\\pgsql\\pgAdmin 4\\bin  \r\n<\/code><\/pre>\n<p>\u53cc\u51fbpgAdmin4.exe\u6253\u5f00pgadmin4(\u6709\u70b9\u8017\u65f6\uff0c\u81ea\u52a8\u542f\u52a8HTTPD\u670d\u52a1)<\/p>\n<p>\u70b9\u51fbserver\uff0c\u53f3\u952e\uff0c\u521b\u5efaserver.<\/p>\n<p>\u914d\u7f6eserver\u522b\u540d\uff0c\u8fde\u63a5\u6570\u636e\u5e93\u7684 IP\uff0c\u7aef\u53e3\uff0c\u7528\u6237\uff0c\u5bc6\u7801\uff0c\u6570\u636e\u5e93\u540d<\/p>\n<p><a href=\"https:\/\/github.com\/digoal\/blog\/blob\/master\/201704\/20170411_01_pic_005.jpg\" target=\"_blank\" rel=\"noopener\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.100\"><img decoding=\"async\" src=\"https:\/\/github.com\/digoal\/blog\/raw\/master\/201704\/20170411_01_pic_005.jpg\" alt=\"pic\" \/><\/a><\/p>\n<h3>21 \u6587\u6863<\/h3>\n<p>PostgreSQL\u7684\u5b89\u88c5\u5305\u4e2d\u5305\u542b\u4e86pgadmin, PostgreSQL\u7684\u6587\u6863\uff0c\u627e\u5230\u5bf9\u5e94\u7684doc\u76ee\u5f55\uff0c\u6253\u5f00index.html\u3002<\/p>\n<h2>\u4e8c\u3001PostgreSQL on Linux(\u865a\u62df\u673a)\u73af\u5883\u642d\u5efa<\/h2>\n<h3>1 \u73af\u5883\u8981\u6c42<\/h3>\n<p>Win 7 x64, 8GB\u4ee5\u4e0a\u5185\u5b58, 4\u6838\u4ee5\u4e0a, SSD\u786c\u76d8(\u63a8\u8350)\uff0c100GB\u4ee5\u4e0a\u5269\u4f59\u7a7a\u95f4, \u53ef\u4ee5\u8bbf\u95ee\u516c\u7f51(10MB\/s\u4ee5\u4e0a\u7f51\u7edc\u5e26\u5bbd)<\/p>\n<h3>2 \u4e0b\u8f7dLinux\u955c\u50cf<\/h3>\n<p><a href=\"http:\/\/isoredirect.centos.org\/centos\/6\/isos\/x86_64\/\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.105\">http:\/\/isoredirect.centos.org\/centos\/6\/isos\/x86_64\/<\/a><\/p>\n<p><a href=\"http:\/\/mirrors.163.com\/centos\/6.9\/isos\/x86_64\/CentOS-6.9-x86_64-minimal.iso\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.106\">http:\/\/mirrors.163.com\/centos\/6.9\/isos\/x86_64\/CentOS-6.9-x86_64-minimal.iso<\/a><\/p>\n<h3>3 \u5b89\u88c5VMware Workstation(\u8bd5\u7528\u7248\u672c)<\/h3>\n<p><a href=\"http:\/\/www.vmware.com\/cn\/products\/workstation\/workstation-evaluation.html\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.108\">http:\/\/www.vmware.com\/cn\/products\/workstation\/workstation-evaluation.html<\/a><\/p>\n<h3>4 \u5b89\u88c5securecrt(\u8bd5\u7528\u7248\u672c)<\/h3>\n<p>securecrt\u53ef\u4ee5\u7528\u6765\u8fde\u63a5Linux\u7ec8\u7aef\uff0c\u65b9\u4fbf\u4f7f\u7528<\/p>\n<p><a href=\"https:\/\/www.vandyke.com\/products\/securecrt\/windows.html\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.110\">https:\/\/www.vandyke.com\/products\/securecrt\/windows.html<\/a><\/p>\n<h3>5 \u5b89\u88c5Linux\u865a\u62df\u673a<\/h3>\n<p>\u6253\u5f00vmware, \u521b\u5efa\u865a\u62df\u673a, \u9009\u62e9CentOS 6 x64\u7248\u672c.<\/p>\n<p>1. \u914d\u7f6e\u5efa\u8bae\uff1a<\/p>\n<p>4G\u5185\u5b58\uff0c40G\u78c1\u76d8\uff0c2\u6838\u4ee5\u4e0a\uff0cNAT\u7f51\u7edc\u6a21\u5f0f\u3002<\/p>\n<p>2. \u5b89\u88c5\u5efa\u8bae\uff1a<\/p>\n<p>minimal\u6700\u5c0f\u5316\u5b89\u88c5\u3002<\/p>\n<p>3. root\u5bc6\u7801\uff1a<\/p>\n<p>\u8bb0\u4f4f\u4f60\u8bbe\u7f6e\u7684root\u5bc6\u7801\u3002<\/p>\n<p>4. Linux\u5b89\u88c5\u914d\u7f6e\u5efa\u8bae<\/p>\n<p>\u914d\u7f6e\u4e3b\u673a\u540d\uff0c\u914d\u7f6e\u7f51\u7edc\uff08\u6839\u636e\u4f60\u7684vmware NAT\u7f51\u7edc\u8fdb\u884c\u914d\u7f6e\uff09\uff0c\u5173\u95edselinux\uff0c\u5173\u95ed\u9632\u706b\u5899\u6216\u5f00\u653essh\u7aef\u53e3(\u6d4b\u8bd5\u73af\u5883)\u3002<\/p>\n<h3>6 \u914d\u7f6eLinux\u865a\u62df\u673a\u7f51\u7edc<\/h3>\n<p>vmware\u7a97\u53e3\u8fde\u63a5linux<\/p>\n<p>\u4f8b\u5b50\uff0c192.168.150 \u8bf7\u53c2\u8003\u4f60\u7684vmware NAT\u7f51\u7edc\u4fee\u6539\u4e00\u4e0b\u3002<\/p>\n<p>\u914d\u7f6e\u7f51\u5173<\/p>\n<pre><code>vi \/etc\/sysconfig\/network  \r\n  \r\nNETWORKING=yes  \r\nHOSTNAME=digoal01  \r\nGATEWAY=192.168.150.2  \r\n<\/code><\/pre>\n<p>\u914d\u7f6eIP<\/p>\n<pre><code>cat \/etc\/sysconfig\/network-scripts\/ifcfg-eth0   \r\n  \r\nDEVICE=eth0  \r\nTYPE=Ethernet  \r\nUUID=d28f566a-b0b9-4bde-95e7-20488af19eb6  \r\nONBOOT=yes  \r\nNM_CONTROLLED=yes  \r\nBOOTPROTO=static  \r\nHWADDR=00:0C:29:5D:6D:9C  \r\nIPADDR=192.168.150.133  \r\nPREFIX=24  \r\nGATEWAY=192.168.150.2  \r\nDNS1=192.168.150.2  \r\nDEFROUTE=yes  \r\nIPV4_FAILURE_FATAL=yes  \r\nIPV6INIT=no  \r\nNAME=\"System eth0\"  \r\n<\/code><\/pre>\n<p>\u914d\u7f6eDNS<\/p>\n<pre><code>cat \/etc\/resolv.conf  \r\n  \r\nnameserver 192.168.150.2  \r\n<\/code><\/pre>\n<p>\u91cd\u542f\u7f51\u7edc\u670d\u52a1<\/p>\n<pre><code>service network restart  \r\n<\/code><\/pre>\n<h3>7 securecrt\u7ec8\u7aef\u8fde\u63a5Linux<\/h3>\n<p>\u6dfb\u52a0\u4e00\u4e2asession\uff0c\u8fde\u63a5\u5230Linux\u865a\u62df\u673a\u3002<\/p>\n<p><a href=\"https:\/\/github.com\/digoal\/blog\/blob\/master\/201704\/20170411_01_pic_006.jpg\" target=\"_blank\" rel=\"noopener\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.114\"><img decoding=\"async\" src=\"https:\/\/github.com\/digoal\/blog\/raw\/master\/201704\/20170411_01_pic_006.jpg\" alt=\"pic\" \/><\/a><\/p>\n<h3>8 \u914d\u7f6elinux<\/h3>\n<p>1. \/etc\/sysctl.conf<\/p>\n<pre data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.i0.7e6cHsYzHsYza2\"><code data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.i1.7e6cHsYzHsYza2\">vi \/etc\/sysctl.conf  \r\n  \r\n\u8ffd\u52a0\u5230\u6587\u4ef6\u672b\u5c3e  \r\n  \r\nkernel.shmall = 4294967296  \r\nkernel.shmmax=135497418752  \r\nkernel.shmmni = 4096  \r\nkernel.sem = 50100 64128000 50100 1280  \r\nfs.file-max = 7672460  \r\nfs.aio-max-nr = 1048576  \r\nnet.ipv4.ip_local_port_range = 9000 65000  \r\nnet.core.rmem_default = 262144  \r\nnet.core.rmem_max = 4194304  \r\nnet.core.wmem_default = 262144  \r\nnet.core.wmem_max = 4194304  \r\nnet.ipv4.tcp_max_syn_backlog = 4096  \r\nnet.core.netdev_max_backlog = 10000  \r\nnet.ipv4.netfilter.ip_conntrack_max = 655360  \r\nnet.ipv4.tcp_timestamps = 0  \r\nnet.ipv4.tcp_tw_recycle=1  \r\nnet.ipv4.tcp_timestamps=1  \r\nnet.ipv4.tcp_keepalive_time = 72   \r\nnet.ipv4.tcp_keepalive_probes = 9   \r\nnet.ipv4.tcp_keepalive_intvl = 7  \r\nvm.zone_reclaim_mode=0  \r\nvm.dirty_background_bytes = 40960000  \r\nvm.dirty_ratio = 80  \r\nvm.dirty_expire_centisecs = 6000  \r\nvm.dirty_writeback_centisecs = 50  \r\nvm.swappiness=0  \r\nvm.overcommit_memory = 0  \r\nvm.overcommit_ratio = 90  \r\n<\/code><\/pre>\n<p>\u751f\u6548<\/p>\n<pre><code>sysctl -p  \r\n<\/code><\/pre>\n<p>2. \/etc\/security\/limits.conf<\/p>\n<pre><code data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.i2.7e6cHsYzHsYza2\">vi \/etc\/security\/limits.conf   \r\n  \r\n* soft    nofile  131072  \r\n* hard    nofile  131072  \r\n* soft    nproc   131072  \r\n* hard    nproc   131072  \r\n* soft    core    unlimited  \r\n* hard    core    unlimited  \r\n* soft    memlock 500000000  \r\n* hard    memlock 500000000  \r\n<\/code><\/pre>\n<p>3. \/etc\/security\/limits.d\/*<\/p>\n<pre><code>rm -f \/etc\/security\/limits.d\/*  \r\n<\/code><\/pre>\n<p>4. \u5173\u95edselinux<\/p>\n<pre><code># vi \/etc\/sysconfig\/selinux   \r\n  \r\nSELINUX=disabled  \r\nSELINUXTYPE=targeted  \r\n<\/code><\/pre>\n<p>5. \u914d\u7f6eOS\u9632\u706b\u5899<br \/>\n\uff08\u5efa\u8bae\u6309\u4e1a\u52a1\u573a\u666f\u8bbe\u7f6e\uff0c\u6211\u8fd9\u91cc\u5148\u6e05\u6389\uff09<\/p>\n<pre><code>iptables -F  \r\n<\/code><\/pre>\n<p>\u914d\u7f6e\u8303\u4f8b<\/p>\n<pre><code># \u79c1\u6709\u7f51\u6bb5  \r\n-A INPUT -s 192.168.0.0\/16 -j ACCEPT  \r\n-A INPUT -s 10.0.0.0\/8 -j ACCEPT  \r\n-A INPUT -s 172.16.0.0\/16 -j ACCEPT  \r\n<\/code><\/pre>\n<p>\u91cd\u542flinux\u3002<\/p>\n<pre><code>reboot  \r\n<\/code><\/pre>\n<h3>9 \u914d\u7f6eyum\u4ed3\u5e93(\u53ef\u9009)<\/h3>\n<p>\u5728linux\u865a\u62df\u673a\u4e2d\uff0c\u627e\u4e00\u4e2a\u6709\u8db3\u591f\u7a7a\u95f4\u7684\u5206\u533a\uff0c\u4e0b\u8f7dISO\u955c\u50cf<\/p>\n<pre><code>wget http:\/\/mirrors.163.com\/centos\/6.9\/isos\/x86_64\/CentOS-6.9-x86_64-bin-DVD1.iso  \r\n  \r\nwget http:\/\/mirrors.163.com\/centos\/6.9\/isos\/x86_64\/CentOS-6.9-x86_64-bin-DVD2.iso  \r\n<\/code><\/pre>\n<p>\u65b0\u5efaISO\u6302\u8f7d\u70b9\u76ee\u5f55<\/p>\n<pre><code>mkdir \/mnt\/cdrom1  \r\nmkdir \/mnt\/cdrom2  \r\n<\/code><\/pre>\n<p>\u6302\u8f7dISO<\/p>\n<pre><code>mount -o loop,defaults,ro \/u01\/CentOS-6.8-x86_64-bin-DVD1.iso \/mnt\/cdrom1  \r\nmount -o loop,defaults,ro \/u01\/CentOS-6.8-x86_64-bin-DVD2.iso \/mnt\/cdrom2  \r\n<\/code><\/pre>\n<p>\u5907\u4efd\u5e76\u5220\u9664\u539f\u6709\u7684YUM\u914d\u7f6e\u6587\u4ef6<\/p>\n<pre><code>mkdir \/tmp\/yum.bak  \r\ncd \/etc\/yum.repos.d\/  \r\nmv * \/tmp\/yum.bak\/  \r\n<\/code><\/pre>\n<p>\u65b0\u589eYUM\u914d\u7f6e\u6587\u4ef6<\/p>\n<pre><code>cd \/etc\/yum.repos.d\/  \r\n  \r\nvi local.repo  \r\n  \r\n[local-yum]  \r\nname=Local Repository  \r\nbaseurl=file:\/\/\/mnt\/cdrom1  \r\nenabled=1  \r\ngpgcheck=0  \r\n<\/code><\/pre>\n<p>\u5237\u65b0YUM\u7f13\u5b58<\/p>\n<pre><code>yum clean all  \r\n<\/code><\/pre>\n<p>\u6d4b\u8bd5<\/p>\n<pre><code>yum list  \r\n  \r\nyum install createrepo   -- \u65b9\u4fbf\u540e\u9762\u6d4b\u8bd5  \r\n<\/code><\/pre>\n<p>\u4fee\u6539YUM\u914d\u7f6e\uff0c\u4fee\u6539\u8def\u5f84\u4e3a\u4e0a\u5c42\u76ee\u5f55<\/p>\n<pre><code>cd \/etc\/yum.repos.d\/  \r\n  \r\nvi local.repo  \r\n  \r\n[local-yum]  \r\nname=Local Repository  \r\nbaseurl=file:\/\/\/mnt\/  \r\nenabled=1  \r\ngpgcheck=0  \r\n<\/code><\/pre>\n<p>\u521b\u5efaYUM\u7d22\u5f15<\/p>\n<pre><code>cd \/mnt\/  \r\ncreaterepo .  \r\n<\/code><\/pre>\n<p>\u5237\u65b0YUM\u7f13\u5b58\uff0c\u6d4b\u8bd5<\/p>\n<pre><code>yum clean all  \r\n  \r\nyum list  \r\n  \r\nyum install vim  \r\n<\/code><\/pre>\n<h3>10 \u521b\u5efa\u666e\u901a\u7528\u6237<\/h3>\n<pre><code>useradd digoal  \r\n<\/code><\/pre>\n<h3>11 \u89c4\u5212\u6570\u636e\u5e93\u5b58\u50a8\u76ee\u5f55<\/h3>\n<p>\u5047\u8bbe\/home\u5206\u533a\u6709\u8db3\u591f\u7684\u7a7a\u95f4, \/home\/digoal\/pgdata\u89c4\u5212\u4e3a\u6570\u636e\u6587\u4ef6\u76ee\u5f55<\/p>\n<pre><code>Filesystem      Size  Used Avail Use% Mounted on  \r\n\/dev\/sda3        14G  5.7G  7.2G  45% \/  \r\n<\/code><\/pre>\n<h3 data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.i3.7e6cHsYzHsYza2\">12 \u4e0b\u8f7dPostgreSQL\u6e90\u7801<\/h3>\n<p data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.i4.7e6cHsYzHsYza2\"><a href=\"https:\/\/www.postgresql.org\/ftp\/source\/?spm=a2c4e.11153940.blogcont73647.120.7e6cHsYzHsYza2\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.120\">https:\/\/www.postgresql.org\/ftp\/source\/<\/a><\/p>\n<pre><code>su - digoal  \r\n  \r\nwget https:\/\/ftp.postgresql.org\/pub\/source\/v9.6.2\/postgresql-9.6.2.tar.bz2  \r\n<\/code><\/pre>\n<h3>13 \u5b89\u88c5PostgreSQL<\/h3>\n<p>\u5b89\u88c5\u4f9d\u8d56\u5305<\/p>\n<pre><code data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.i5.7e6cHsYzHsYza2\">root\u7528\u6237\u4e0b\uff0c\u4f7f\u7528yum \u5b89\u88c5\u4f9d\u8d56\u5305  \r\n  \r\nyum -y install coreutils glib2 lrzsz mpstat dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make smartmontools flex bison perl-devel perl-Ext  \r\nUtils* openldap-devel jadetex  openjade bzip2  \r\n<\/code><\/pre>\n<p>\u7f16\u8bd1\u5b89\u88c5PostgreSQL<\/p>\n<pre><code>digoal\u7528\u6237\u4e0b\uff0c\u7f16\u8bd1\u5b89\u88c5PostgreSQL  \r\n  \r\ntar -jxvf postgresql-9.6.2.tar.bz2  \r\ncd postgresql-9.6.2  \r\n.\/configure --prefix=\/home\/digoal\/pgsql9.6  \r\nmake world -j 8  \r\nmake install-world  \r\n<\/code><\/pre>\n<h3>14 \u914d\u7f6elinux\u7528\u6237\u73af\u5883\u53d8\u91cf<\/h3>\n<p>digoal\u7528\u6237\u4e0b\uff0c\u914d\u7f6e\u73af\u5883\u53d8\u91cf<\/p>\n<pre data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.i7.7e6cHsYzHsYza2\"><code data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.i6.7e6cHsYzHsYza2\">su - digoal  \r\nvi ~\/.bash_profile  \r\n  \r\n\u8ffd\u52a0  \r\n  \r\nexport PS1=\"$USER@`\/bin\/hostname -s`-&gt; \"  \r\nexport PGPORT=1921  \r\nexport PGDATA=\/home\/digoal\/pgdata  \r\nexport LANG=en_US.utf8  \r\nexport PGHOME=\/home\/digoal\/pgsql9.6  \r\nexport LD_LIBRARY_PATH=$PGHOME\/lib:\/lib64:\/usr\/lib64:\/usr\/local\/lib64:\/lib:\/usr\/lib:\/usr\/local\/lib:$LD_LIBRARY_PATH  \r\nexport PATH=$PGHOME\/bin:$PATH:.  \r\nexport DATE=`date +\"%Y%m%d%H%M\"`  \r\nexport MANPATH=$PGHOME\/share\/man:$MANPATH  \r\nexport PGHOST=$PGDATA  \r\nexport PGUSER=postgres  \r\nexport PGDATABASE=postgres  \r\nalias rm='rm -i'  \r\nalias ll='ls -lh'  \r\nunalias vi  \r\n<\/code><\/pre>\n<p>\u91cd\u65b0\u767b\u5f55digoal\u7528\u6237\uff0c\u914d\u7f6e\u751f\u6548<\/p>\n<pre><code>exit  \r\n  \r\nsu - digoal  \r\n<\/code><\/pre>\n<h3>15 \u521d\u59cb\u5316\u6570\u636e\u5e93\u96c6\u7fa4<\/h3>\n<pre><code>initdb -D $PGDATA -E UTF8 --locale=C -U postgres  \r\n<\/code><\/pre>\n<h3>16 \u914d\u7f6e\u6570\u636e\u5e93<\/h3>\n<p>\u914d\u7f6e\u6587\u4ef6\u5728$PGDATA\u76ee\u5f55\u4e2d<\/p>\n<p>1. \u914d\u7f6epostgresql.conf<\/p>\n<pre><code data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.i8.7e6cHsYzHsYza2\">\u8ffd\u52a0  \r\n  \r\nlisten_addresses = '0.0.0.0'  \r\nport = 1921  \r\nmax_connections = 200  \r\nunix_socket_directories = '.'  \r\ntcp_keepalives_idle = 60  \r\ntcp_keepalives_interval = 10  \r\ntcp_keepalives_count = 10  \r\nshared_buffers = 512MB  \r\ndynamic_shared_memory_type = posix  \r\nvacuum_cost_delay = 0  \r\nbgwriter_delay = 10ms  \r\nbgwriter_lru_maxpages = 1000  \r\nbgwriter_lru_multiplier = 10.0  \r\nbgwriter_flush_after = 0   \r\nold_snapshot_threshold = -1  \r\nbackend_flush_after = 0   \r\nwal_level = minimal  \r\nsynchronous_commit = off  \r\nfull_page_writes = on  \r\nwal_buffers = 16MB  \r\nwal_writer_delay = 10ms  \r\nwal_writer_flush_after = 0   \r\ncheckpoint_timeout = 30min   \r\nmax_wal_size = 2GB  \r\nmin_wal_size = 128MB  \r\ncheckpoint_completion_target = 0.05    \r\ncheckpoint_flush_after = 0    \r\nrandom_page_cost = 1.3   \r\nlog_destination = 'csvlog'  \r\nlogging_collector = on  \r\nlog_truncate_on_rotation = on  \r\nlog_checkpoints = on  \r\nlog_connections = on  \r\nlog_disconnections = on  \r\nlog_error_verbosity = verbose  \r\nautovacuum = on  \r\nlog_autovacuum_min_duration = 0  \r\nautovacuum_naptime = 20s  \r\nautovacuum_vacuum_scale_factor = 0.05  \r\nautovacuum_freeze_max_age = 1500000000  \r\nautovacuum_multixact_freeze_max_age = 1600000000  \r\nautovacuum_vacuum_cost_delay = 0  \r\nvacuum_freeze_table_age = 1400000000  \r\nvacuum_multixact_freeze_table_age = 1500000000  \r\ndatestyle = 'iso, mdy'  \r\ntimezone = 'PRC'  \r\nlc_messages = 'C'  \r\nlc_monetary = 'C'  \r\nlc_numeric = 'C'  \r\nlc_time = 'C'  \r\ndefault_text_search_config = 'pg_catalog.english'  \r\nshared_preload_libraries='pg_stat_statements'  \r\n<\/code><\/pre>\n<p>2. \u914d\u7f6epg_hba.conf<\/p>\n<pre><code data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.i9.7e6cHsYzHsYza2\">\u8ffd\u52a0  \r\n  \r\nhost all all 0.0.0.0\/0 md5  \r\n<\/code><\/pre>\n<h3>17 \u542f\u52a8\u6570\u636e\u5e93\u96c6\u7fa4<\/h3>\n<pre><code>su - digoal  \r\n  \r\npg_ctl start  \r\n<\/code><\/pre>\n<h3>18 \u8fde\u63a5\u6570\u636e\u5e93<\/h3>\n<pre><code>su - digoal  \r\n  \r\npsql  \r\npsql (9.6.2)  \r\nType \"help\" for help.  \r\n  \r\npostgres=#   \r\n<\/code><\/pre>\n<h3>19 \u5b89\u88c5pgadmin(\u53ef\u9009)<\/h3>\n<p>\u5728windows \u673a\u5668\u4e0a\uff0c\u5b89\u88c5pgadmin<\/p>\n<p><a href=\"https:\/\/www.pgadmin.org\/download\/windows4.php\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.128\">https:\/\/www.pgadmin.org\/download\/windows4.php<\/a><\/p>\n<h3>20 \u914d\u7f6epgadmin(\u53ef\u9009)<\/h3>\n<p>\u53c2\u8003\u7ae0\u82821<\/p>\n<h3>21 \u4f7f\u7528pgadmin\u8fde\u63a5\u6570\u636e\u5e93(\u53ef\u9009)<\/h3>\n<p>\u53c2\u8003\u7ae0\u82821<\/p>\n<h2>\u4e09\u3001\u4e91\u6570\u636e\u5e93RDS for PostgreSQL<\/h2>\n<h3>1 \u8d2d\u4e70\u4e91\u6570\u636e\u5e93<\/h3>\n<p><a href=\"https:\/\/www.aliyun.com\/product\/rds\/postgresql\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.133\">https:\/\/www.aliyun.com\/product\/rds\/postgresql<\/a><\/p>\n<h3>2 \u8bbe\u7f6e\u5e76\u8bb0\u4f4fRDS for PostgreSQL\u6570\u636e\u5e93\u6839\u7528\u6237\u540d\u548c\u5bc6\u7801<\/h3>\n<p>\u5728RDS \u63a7\u5236\u53f0\u64cd\u4f5c\u3002<\/p>\n<h3>3 \u914d\u7f6e\u7f51\u7edc<\/h3>\n<p>\u5728RDS \u63a7\u5236\u53f0\u64cd\u4f5c\uff0c\u914d\u7f6e\u8fde\u63a5\u6570\u636e\u5e93\u7684URL\u548c\u7aef\u53e3\u3002<\/p>\n<h3>4 \u914d\u7f6e\u767d\u540d\u5355<\/h3>\n<p>\u5728RDS \u63a7\u5236\u53f0\u64cd\u4f5c\uff0c\u914d\u7f6e\u6765\u6e90IP\u7684\u767d\u540d\u5355\uff0c\u5982\u679c\u6765\u6e90IP\u4e3a\u52a8\u6001IP\uff0c\u767d\u540d\u5355\u8bbe\u7f6e\u4e3a0.0.0.0\u3002<\/p>\n<p>(\u6570\u636e\u5e93\u5f00\u653e\u516c\u7f51\u8fde\u63a5\u6709\u98ce\u9669\uff0c\u8bf7\u8c28\u614e\u8bbe\u7f6e\uff0c\u672c\u6587\u4ec5\u4e3a\u6d4b\u8bd5\u73af\u5883\u3002)<\/p>\n<h3>5 \u672c\u5730\u5b89\u88c5pgadmin(\u53ef\u9009)<\/h3>\n<p>\u5728windows \u673a\u5668\u4e0a\uff0c\u5b89\u88c5pgadmin<\/p>\n<p><a href=\"https:\/\/www.pgadmin.org\/download\/windows4.php\" data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.138\">https:\/\/www.pgadmin.org\/download\/windows4.php<\/a><\/p>\n<h3>6 \u672c\u5730\u914d\u7f6epgadmin(\u53ef\u9009)<\/h3>\n<p>\u53c2\u8003\u7ae0\u82821<\/p>\n<h3>7 \u4f7f\u7528pgadmin\u8fde\u63a5RDS PostgreSQL\u6570\u636e\u5e93(\u53ef\u9009)<\/h3>\n<p data-spm-anchor-id=\"a2c4e.11153940.blogcont73647.i10.7e6cHsYzHsYza2\">\u53c2\u8003\u7ae0\u82821<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u80cc\u666f \u4e07\u4e8b\u5f00\u5934\u96be\uff0c\u642d\u5efa\u597d\u4e00\u5957\u5b66\u4e60\u3001\u5f00\u53d1PostgreSQL\u7684\u73af\u5883\uff0c\u662f\u91cd\u4e2d\u4e4b\u91cd\u3002 \u56e0\u4e3a\u5176\u4ed6\u5e73\u53f0(Ubuntu, CentOS, MAC)\u7684\u7528\u6237\u5927\u591a\u6570\u90fd\u5177\u5907\u4e86\u81ea\u884c\u5b89\u88c5\u6570\u636e\u5e93\u7684\u80fd\u529b\uff0c\u5728\u8fd9\u91cc\u6211\u53ea\u5199\u4e00\u4e2a\u9762\u5411Windows\u7528\u6237\u7684\u5b66\u4e60\u73af\u5883\u642d\u5efa\u6587\u6863\u3002 \u5206\u4e3a\u4e09\u4e2a\u90e8\u5206\uff0c\u7528\u6237\u53ef\u4ee5\u81ea\u7531\u9009\u62e9\u3002 \u5982\u679c\u4f60\u60f3\u6df1\u5165\u7684\u5b66\u4e60PostgreSQL\uff0c\u5efa\u8bae\u642d\u5efaPostgreSQL on Linux\u7684\u73af\u5883\u3002\u5982\u679c\u4f60\u53ea\u662f\u60f3\u5c06\u6570\u636e\u5e93\u4f7f\u7528\u5728\u65e5\u5e38\u7684\u5e94\u7528\u5f00\u53d1\u5de5\u4f5c\u4e2d\uff0c\u6709\u4e5f\u4e0d\u9700\u8981PG\u7684\u5176\u4ed6\u9644\u52a0\u63d2\u4ef6\u7684\u529f\u80fd\uff0c\u90a3\u4e48\u4f60\u53ef\u4ee5\u9009\u62e9PostgreSQL on Win\u7684\u73af\u5883\u642d\u5efa\u3002 \u5982\u679c\u4f60\u4e0d\u60f3\u642d\u5efa\u672c\u5730\u7684PostgreSQL\uff0c\u90a3\u4e48\u4f60\u53ef\u4ee5\u4f7f\u7528\u4e91\u6570\u636e\u5e93\u670d\u52a1\uff0c\u6bd4\u5982\u963f\u91cc\u4e91RDS for PostgreSQL\u3002 \u672c\u7ae0\u5927\u7eb2 \u4e00\u3001PostgreSQL on Win\u73af\u5883\u642d\u5efa 1 \u73af\u5883\u8981\u6c42 2 \u4e0b\u8f7dPostgreSQL\u5b89\u88c5\u5305 3 \u89e3\u538bPostgreSQL\u5b89\u88c5\u5305 4 \u4e0b\u8f7dpgadmin\u5b89\u88c5\u5305(\u53ef\u9009) 5 \u5b89\u88c5pgadmin(\u53ef\u9009) 6 \u89c4\u5212\u6570\u636e\u6587\u4ef6\u76ee\u5f55 7 \u521d\u59cb\u5316\u6570\u636e\u5e93\u96c6\u7fa4 8 \u914d\u7f6epostgresql.conf 9 \u914d\u7f6epg_hba.conf(\u53ef\u9009) 10 \u542f\u52a8\u3001\u505c\u6b62\u6570\u636e\u5e93\u96c6\u7fa4 11 \u5982\u4f55\u81ea\u52a8\u542f\u52a8\u6570\u636e\u5e93\u96c6\u7fa4 12 \u4f7f\u7528psql \u547d\u4ee4\u884c\u8fde\u63a5\u6570\u636e\u5e93 13 \u65b0\u589e\u7528\u6237 14 \u4f7f\u7528psql\u5e2e\u52a9 15 \u4f7f\u7528psql\u8bed\u6cd5\u8865\u9f50 16 \u4f7f\u7528psql sql\u8bed\u6cd5\u5e2e\u52a9 17 \u67e5\u770b\u5f53\u524d\u914d\u7f6e 18 &hellip; <a href=\"https:\/\/www.strongd.net\/?p=1517\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">\u4e00\u5929\u5b66\u4f1aPostgreSQL\u5e94\u7528\u5f00\u53d1\u4e0e\u7ba1\u7406 &#8211; 1 \u5982\u4f55\u642d\u5efa\u4e00\u5957\u5b66\u4e60\u3001\u5f00\u53d1PostgreSQL\u7684\u73af\u5883<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[233,20],"tags":[],"class_list":["post-1517","post","type-post","status-publish","format-standard","hentry","category-postgresql","category-20"],"_links":{"self":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts\/1517","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1517"}],"version-history":[{"count":1,"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts\/1517\/revisions"}],"predecessor-version":[{"id":1518,"href":"https:\/\/www.strongd.net\/index.php?rest_route=\/wp\/v2\/posts\/1517\/revisions\/1518"}],"wp:attachment":[{"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1517"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1517"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.strongd.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1517"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}