在 Ubuntu Server 14.04 下安装 PostgreSQL 9.3.5 开源数据库

liangsheng

在 Ubuntu Server 14.04 下安装 PostgreSQL 9.3.5 开源数据库

PostgreSQL 是一个自由开源对象-关系型数据库服务器 (数据库管理系统)，在 BSD 许可下发行。PostgreSQL 可免费使用、修改和分发，不管是私用、商用、还是学术研究用。PostgreSQL 提供了相对其他开源数据库系统 (譬如：MySQL 和 Firebird) 和专有数据库系统 (譬如：Oracle、Sybase、IBM 的 DB2 和 Microsoft SQL Server) 之外的另一种选择。

PostgreSQL 不寻常的名字导致一些读者停下来尝试拼读它，特别是那些把 SQL 拼读为 "sequel" 的人。PostgreSQL 开发者把它拼读为"post-gress-Q-L"。它也经常被简略念为 "postgres"。

PostgreSQL 是以 “加州大学伯克利分校” 计算机系开发的 POSTGRES (现更名为 PostgreSQL) 版本 4.2 为基础的对象关系型数据库管理系统 (ORDBMS)。PostgreSQL 支持大部分 SQL 标准且提供了许多其他现代特性，包括：复杂查询、外键、触发器、视图、事务完整性、MVCC。同样，PostgreSQL 可用许多方法扩展；譬如：通过增加新数据类型、函数、操作符、聚集函数、索引。

10 倍以上效率提升 智能站群 所见即所得 "HTML5 Bootstrap4 网页 IDE" 开发工具

http://ideweb.digitser.cn/

http://forum.digitser.cn/thread-2322-1-1.html

百度网盘

https://pan.baidu.com/s/1i5tKlZB

软件仓库

https://github.com/digitser

https://digitser.sourceforge.io/

https://pan.baidu.com/s/1TV70__Be1ta0ney1-tudFQ

01、以 apt-get 方式安装 PostgreSQL 9.3.5

1. root:# apt-get install postgresql
   
2. 正在读取软件包列表... 完成
   
3. 正在分析软件包的依赖关系树      
   
4. 正在读取状态信息... 完成      
   
5. 将会安装下列额外的软件包：
   
6.   libpq5 postgresql-9.3 postgresql-client-9.3 postgresql-client-common
   
7.   postgresql-common ssl-cert
   
8. 建议安装的软件包：
   
9.   oidentd ident-server locales-all postgresql-doc-9.3 openssl-blacklist
   
10. 下列【新】软件包将被安装：
    
11.   libpq5 postgresql postgresql-9.3 postgresql-client-9.3
    
12.   postgresql-client-common postgresql-common ssl-cert
    
13. 升级了 0 个软件包，新安装了 7 个软件包，要卸载 0 个软件包，有 0 个软件包未被升级。
    
14. 需要下载 3,682 kB 的软件包。
    
15. 解压缩后会消耗掉 15.5 MB 的额外空间。
    
16. 您希望继续执行吗？ [Y/n] y
    
17. 获取：1 [url]http://cn.archive.ubuntu.com/ubuntu/[/url] trusty-updates/main libpq5 amd64 9.3.5-0ubuntu0.14.04.1 [80.6 kB]
    
18. 获取：2 [url]http://cn.archive.ubuntu.com/ubuntu/[/url] trusty/main postgresql-client-common all 154 [25.4 kB]
    
19. 获取：3 [url]http://cn.archive.ubuntu.com/ubuntu/[/url] trusty-updates/main postgresql-client-9.3 amd64 9.3.5-0ubuntu0.14.04.1 [782 kB]
    
20. 获取：4 [url]http://cn.archive.ubuntu.com/ubuntu/[/url] trusty/main ssl-cert all 1.0.33 [16.6 kB]
    
21. 获取：5 [url]http://cn.archive.ubuntu.com/ubuntu/[/url] trusty/main postgresql-common all 154 [103 kB]
    
22. 获取：6 [url]http://cn.archive.ubuntu.com/ubuntu/[/url] trusty-updates/main postgresql-9.3 amd64 9.3.5-0ubuntu0.14.04.1 [2,670 kB]
    
23. 获取：7 [url]http://cn.archive.ubuntu.com/ubuntu/[/url] trusty/main postgresql all 9.3+154 [5,088 B]
    
24. 下载 3,682 kB，耗时 9秒 (402 kB/s)                                             
    
25. 正在预设定软件包 ...
    
26. Selecting previously unselected package libpq5.
    
27. (正在读取数据库 ... 系统当前共安装有 85656 个文件和目录。)
    
28. Preparing to unpack .../libpq5_9.3.5-0ubuntu0.14.04.1_amd64.deb ...
    
29. Unpacking libpq5 (9.3.5-0ubuntu0.14.04.1) ...
    
30. Selecting previously unselected package postgresql-client-common.
    
31. Preparing to unpack .../postgresql-client-common_154_all.deb ...
    
32. Unpacking postgresql-client-common (154) ...
    
33. Selecting previously unselected package postgresql-client-9.3.
    
34. Preparing to unpack .../postgresql-client-9.3_9.3.5-0ubuntu0.14.04.1_amd64.deb ...
    
35. Unpacking postgresql-client-9.3 (9.3.5-0ubuntu0.14.04.1) ...
    
36. Selecting previously unselected package ssl-cert.
    
37. Preparing to unpack .../ssl-cert_1.0.33_all.deb ...
    
38. Unpacking ssl-cert (1.0.33) ...
    
39. Selecting previously unselected package postgresql-common.
    
40. Preparing to unpack .../postgresql-common_154_all.deb ...
    
41. 正在添加 postgresql-common 导致 /usr/bin/pg_config 本地修改至 /usr/bin/pg_config.libpq-dev
    
42. Unpacking postgresql-common (154) ...
    
43. Selecting previously unselected package postgresql-9.3.
    
44. Preparing to unpack .../postgresql-9.3_9.3.5-0ubuntu0.14.04.1_amd64.deb ...
    
45. Unpacking postgresql-9.3 (9.3.5-0ubuntu0.14.04.1) ...
    
46. Selecting previously unselected package postgresql.
    
47. Preparing to unpack .../postgresql_9.3+154_all.deb ...
    
48. Unpacking postgresql (9.3+154) ...
    
49. Processing triggers for man-db (2.6.7.1-1ubuntu1) ...
    
50. Processing triggers for ureadahead (0.100.0-16) ...
    
51. ureadahead will be reprofiled on next reboot
    
52. 正在设置 libpq5 (9.3.5-0ubuntu0.14.04.1) ...
    
53. 正在设置 postgresql-client-common (154) ...
    
54. 正在设置 postgresql-client-9.3 (9.3.5-0ubuntu0.14.04.1) ...
    
55. update-alternatives: using /usr/share/postgresql/9.3/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in 自动模式
    
56. 正在设置 ssl-cert (1.0.33) ...
    
57. 正在设置 postgresql-common (154) ...
    
58. Adding user postgres to group ssl-cert
    
59. 
    
60. Creating config file /etc/logrotate.d/postgresql-common with new version
    
61. Building PostgreSQL dictionaries from installed myspell/hunspell packages...
    
62. Removing obsolete dictionary files:
    
63. * No PostgreSQL clusters exist; see "man pg_createcluster"
    
64. Processing triggers for ureadahead (0.100.0-16) ...
    
65. 正在设置 postgresql-9.3 (9.3.5-0ubuntu0.14.04.1) ...
    
66. Creating new cluster 9.3/main ...
    
67.   config /etc/postgresql/9.3/main
    
68.   data   /var/lib/postgresql/9.3/main
    
69.   locale zh_CN.UTF-8
    
70.   port   5432
    
71. update-alternatives: using /usr/share/postgresql/9.3/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in 自动模式
    
72. * Starting PostgreSQL 9.3 database server                                                                                                        [ OK ]
    
73. 正在设置 postgresql (9.3+154) ...
    
74. Processing triggers for libc-bin (2.19-0ubuntu6.5) ...
    
75. root:#

复制代码

02、设置 Ubuntu Server 14.04 系统 postgres 用户登陆口令

1. root:# passwd postgres
   
2. 输入新的 UNIX 密码：
   
3. 重新输入新的 UNIX 密码：
   
4. passwd: password updated successfully
   
5. root:#

复制代码

03、切换到 postgres 系统用户

1. root:# su - postgres 或 sudo -i -u postgres
   
2. postgres:~$

复制代码

04、登录 postgres 数据库

1. postgres:~$ psql postgres
   
2. psql (9.3.5)
   
3. Type "help" for help.
   
4. 
   
5. postgres=#    #postgres 命令提示符

复制代码

05、将 postgres 数据库用户口令设为 postgres 或其它

1. postgres=# ALTER USER postgres with PASSWORD 'postgres';
   
2. ALTER ROLE
   
3. postgres=# \q
   
4. could not save history to file "/var/lib/postgresql/.psql_history": 没有那个文件或目录    #重新登录就可以了，由于第一次文件是不存在的
   
5. postgres:~$

复制代码

06、再次登陆 postgres 数据库，再将 postgres 数据库用户口令设为 postgres 或其它

1. postgres:~$ psql postgres
   
2. psql (9.3.5)
   
3. Type "help" for help.
   
4. 
   
5. postgres=# ALTER USER postgres with PASSWORD 'postgres';
   
6. ALTER ROLE
   
7. postgres=# \q
   
8. postgres:~$

复制代码

07、修改 PostgresSQL 数据库配置文件 postgresql.conf

1. vi /etc/postgresql/9.3/main/postgresql.conf

复制代码

修改前的配置文件

1. # -----------------------------
   
2. # PostgreSQL configuration file
   
3. # -----------------------------
   
4. #
   
5. # This file consists of lines of the form:
   
6. #
   
7. #   name = value
   
8. #
   
9. # (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
   
10. # "#" anywhere on a line.  The complete list of parameter names and allowed
    
11. # values can be found in the PostgreSQL documentation.
    
12. #
    
13. # The commented-out settings shown in this file represent the default values.
    
14. # Re-commenting a setting is NOT sufficient to revert it to the default value;
    
15. # you need to reload the server.
    
16. #
    
17. # This file is read on server startup and when the server receives a SIGHUP
    
18. # signal.  If you edit the file on a running system, you have to SIGHUP the
    
19. # server for the changes to take effect, or use "pg_ctl reload".  Some
    
20. # parameters, which are marked below, require a server shutdown and restart to
    
21. # take effect.
    
22. #
    
23. # Any parameter can also be given as a command-line option to the server, e.g.,
    
24. # "postgres -c log_connections=on".  Some parameters can be changed at run time
    
25. # with the "SET" SQL command.
    
26. #
    
27. # Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
    
28. #                MB = megabytes                     s   = seconds
    
29. #                GB = gigabytes                     min = minutes
    
30. #                                                   h   = hours
    
31. #                                                   d   = days
    
32. 
    
33. 
    
34. #------------------------------------------------------------------------------
    
35. # FILE LOCATIONS
    
36. #------------------------------------------------------------------------------
    
37. 
    
38. # The default values of these variables are driven from the -D command-line
    
39. # option or PGDATA environment variable, represented here as ConfigDir.
    
40. 
    
41. data_directory = '/var/lib/postgresql/9.3/main'        # use data in another directory
    
42.                     # (change requires restart)
    
43. hba_file = '/etc/postgresql/9.3/main/pg_hba.conf'    # host-based authentication file
    
44.                     # (change requires restart)
    
45. ident_file = '/etc/postgresql/9.3/main/pg_ident.conf'    # ident configuration file
    
46.                     # (change requires restart)
    
47. 
    
48. # If external_pid_file is not explicitly set, no extra PID file is written.
    
49. external_pid_file = '/var/run/postgresql/9.3-main.pid'            # write an extra PID file
    
50.                     # (change requires restart)
    
51. 
    
52. 
    
53. #------------------------------------------------------------------------------
    
54. # CONNECTIONS AND AUTHENTICATION
    
55. #------------------------------------------------------------------------------
    
56. 
    
57. # - Connection Settings -
    
58. 
    
59. #listen_addresses = 'localhost'        # what IP address(es) to listen on;
    
60.                     # comma-separated list of addresses;
    
61.                     # defaults to 'localhost'; use '*' for all
    
62.                     # (change requires restart)
    
63. port = 5432                # (change requires restart)
    
64. max_connections = 100            # (change requires restart)
    
65. # Note:  Increasing max_connections costs ~400 bytes of shared memory per
    
66. # connection slot, plus lock space (see max_locks_per_transaction).
    
67. #superuser_reserved_connections = 3    # (change requires restart)
    
68. unix_socket_directories = '/var/run/postgresql'    # comma-separated list of directories
    
69.                     # (change requires restart)
    
70. #unix_socket_group = ''            # (change requires restart)
    
71. #unix_socket_permissions = 0777        # begin with 0 to use octal notation
    
72.                     # (change requires restart)
    
73. #bonjour = off                # advertise server via Bonjour
    
74.                     # (change requires restart)
    
75. #bonjour_name = ''            # defaults to the computer name
    
76.                     # (change requires restart)
    
77. 
    
78. # - Security and Authentication -
    
79. 
    
80. #authentication_timeout = 1min        # 1s-600s
    
81. ssl = true                # (change requires restart)
    
82. #ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH'    # allowed SSL ciphers
    
83.                     # (change requires restart)
    
84. #ssl_renegotiation_limit = 512MB    # amount of data between renegotiations
    
85. ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'        # (change requires restart)
    
86. ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'        # (change requires restart)
    
87. #ssl_ca_file = ''            # (change requires restart)
    
88. #ssl_crl_file = ''            # (change requires restart)
    
89. #password_encryption = on
    
90. #db_user_namespace = off
    
91. 
    
92. # Kerberos and GSSAPI
    
93. #krb_server_keyfile = ''
    
94. #krb_srvname = 'postgres'        # (Kerberos only)
    
95. #krb_caseins_users = off
    
96. 
    
97. # - TCP Keepalives -
    
98. # see "man 7 tcp" for details
    
99. 
    
100. #tcp_keepalives_idle = 0        # TCP_KEEPIDLE, in seconds;
     
101.                     # 0 selects the system default
     
102. #tcp_keepalives_interval = 0        # TCP_KEEPINTVL, in seconds;
     
103.                     # 0 selects the system default
     
104. #tcp_keepalives_count = 0        # TCP_KEEPCNT;
     
105.                     # 0 selects the system default
     
106. 
     
107. 
     
108. #------------------------------------------------------------------------------
     
109. # RESOURCE USAGE (except WAL)
     
110. #------------------------------------------------------------------------------
     
111. 
     
112. # - Memory -
     
113. 
     
114. shared_buffers = 128MB            # min 128kB
     
115.                     # (change requires restart)
     
116. #temp_buffers = 8MB            # min 800kB
     
117. #max_prepared_transactions = 0        # zero disables the feature
     
118.                     # (change requires restart)
     
119. # Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
     
120. # per transaction slot, plus lock space (see max_locks_per_transaction).
     
121. # It is not advisable to set max_prepared_transactions nonzero unless you
     
122. # actively intend to use prepared transactions.
     
123. #work_mem = 1MB                # min 64kB
     
124. #maintenance_work_mem = 16MB        # min 1MB
     
125. #max_stack_depth = 2MB            # min 100kB
     
126. 
     
127. # - Disk -
     
128. 
     
129. #temp_file_limit = -1            # limits per-session temp file space
     
130.                     # in kB, or -1 for no limit
     
131. 
     
132. # - Kernel Resource Usage -
     
133. 
     
134. #max_files_per_process = 1000        # min 25
     
135.                     # (change requires restart)
     
136. #shared_preload_libraries = ''        # (change requires restart)
     
137. 
     
138. # - Cost-Based Vacuum Delay -
     
139. 
     
140. #vacuum_cost_delay = 0            # 0-100 milliseconds
     
141. #vacuum_cost_page_hit = 1        # 0-10000 credits
     
142. #vacuum_cost_page_miss = 10        # 0-10000 credits
     
143. #vacuum_cost_page_dirty = 20        # 0-10000 credits
     
144. #vacuum_cost_limit = 200        # 1-10000 credits
     
145. 
     
146. # - Background Writer -
     
147. 
     
148. #bgwriter_delay = 200ms            # 10-10000ms between rounds
     
149. #bgwriter_lru_maxpages = 100        # 0-1000 max buffers written/round
     
150. #bgwriter_lru_multiplier = 2.0        # 0-10.0 multipler on buffers scanned/round
     
151. 
     
152. # - Asynchronous Behavior -
     
153. 
     
154. #effective_io_concurrency = 1        # 1-1000; 0 disables prefetching
     
155. 
     
156. 
     
157. #------------------------------------------------------------------------------
     
158. # WRITE AHEAD LOG
     
159. #------------------------------------------------------------------------------
     
160. 
     
161. # - Settings -
     
162. 
     
163. #wal_level = minimal            # minimal, archive, or hot_standby
     
164.                     # (change requires restart)
     
165. #fsync = on                # turns forced synchronization on or off
     
166. #synchronous_commit = on        # synchronization level;
     
167.                     # off, local, remote_write, or on
     
168. #wal_sync_method = fsync        # the default is the first option
     
169.                     # supported by the operating system:
     
170.                     #   open_datasync
     
171.                     #   fdatasync (default on Linux)
     
172.                     #   fsync
     
173.                     #   fsync_writethrough
     
174.                     #   open_sync
     
175. #full_page_writes = on            # recover from partial page writes
     
176. #wal_buffers = -1            # min 32kB, -1 sets based on shared_buffers
     
177.                     # (change requires restart)
     
178. #wal_writer_delay = 200ms        # 1-10000 milliseconds
     
179. 
     
180. #commit_delay = 0            # range 0-100000, in microseconds
     
181. #commit_siblings = 5            # range 1-1000
     
182. 
     
183. # - Checkpoints -
     
184. 
     
185. #checkpoint_segments = 3        # in logfile segments, min 1, 16MB each
     
186. #checkpoint_timeout = 5min        # range 30s-1h
     
187. #checkpoint_completion_target = 0.5    # checkpoint target duration, 0.0 - 1.0
     
188. #checkpoint_warning = 30s        # 0 disables
     
189. 
     
190. # - Archiving -
     
191. 
     
192. #archive_mode = off        # allows archiving to be done
     
193.                 # (change requires restart)
     
194. #archive_command = ''        # command to use to archive a logfile segment
     
195.                 # placeholders: %p = path of file to archive
     
196.                 #               %f = file name only
     
197.                 # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
     
198. #archive_timeout = 0        # force a logfile segment switch after this
     
199.                 # number of seconds; 0 disables
     
200. 
     
201. 
     
202. #------------------------------------------------------------------------------
     
203. # REPLICATION
     
204. #------------------------------------------------------------------------------
     
205. 
     
206. # - Sending Server(s) -
     
207. 
     
208. # Set these on the master and on any standby that will send replication data.
     
209. 
     
210. #max_wal_senders = 0        # max number of walsender processes
     
211.                 # (change requires restart)
     
212. #wal_keep_segments = 0        # in logfile segments, 16MB each; 0 disables
     
213. #wal_sender_timeout = 60s    # in milliseconds; 0 disables
     
214. 
     
215. # - Master Server -
     
216. 
     
217. # These settings are ignored on a standby server.
     
218. 
     
219. #synchronous_standby_names = ''    # standby servers that provide sync rep
     
220.                 # comma-separated list of application_name
     
221.                 # from standby(s); '*' = all
     
222. #vacuum_defer_cleanup_age = 0    # number of xacts by which cleanup is delayed
     
223. 
     
224. # - Standby Servers -
     
225. 
     
226. # These settings are ignored on a master server.
     
227. 
     
228. #hot_standby = off            # "on" allows queries during recovery
     
229.                     # (change requires restart)
     
230. #max_standby_archive_delay = 30s    # max delay before canceling queries
     
231.                     # when reading WAL from archive;
     
232.                     # -1 allows indefinite delay
     
233. #max_standby_streaming_delay = 30s    # max delay before canceling queries
     
234.                     # when reading streaming WAL;
     
235.                     # -1 allows indefinite delay
     
236. #wal_receiver_status_interval = 10s    # send replies at least this often
     
237.                     # 0 disables
     
238. #hot_standby_feedback = off        # send info from standby to prevent
     
239.                     # query conflicts
     
240. #wal_receiver_timeout = 60s        # time that receiver waits for
     
241.                     # communication from master
     
242.                     # in milliseconds; 0 disables
     
243. 
     
244. 
     
245. #------------------------------------------------------------------------------
     
246. # QUERY TUNING
     
247. #------------------------------------------------------------------------------
     
248. 
     
249. # - Planner Method Configuration -
     
250. 
     
251. #enable_bitmapscan = on
     
252. #enable_hashagg = on
     
253. #enable_hashjoin = on
     
254. #enable_indexscan = on
     
255. #enable_indexonlyscan = on
     
256. #enable_material = on
     
257. #enable_mergejoin = on
     
258. #enable_nestloop = on
     
259. #enable_seqscan = on
     
260. #enable_sort = on
     
261. #enable_tidscan = on
     
262. 
     
263. # - Planner Cost Constants -
     
264. 
     
265. #seq_page_cost = 1.0            # measured on an arbitrary scale
     
266. #random_page_cost = 4.0            # same scale as above
     
267. #cpu_tuple_cost = 0.01            # same scale as above
     
268. #cpu_index_tuple_cost = 0.005        # same scale as above
     
269. #cpu_operator_cost = 0.0025        # same scale as above
     
270. #effective_cache_size = 128MB
     
271. 
     
272. # - Genetic Query Optimizer -
     
273. 
     
274. #geqo = on
     
275. #geqo_threshold = 12
     
276. #geqo_effort = 5            # range 1-10
     
277. #geqo_pool_size = 0            # selects default based on effort
     
278. #geqo_generations = 0            # selects default based on effort
     
279. #geqo_selection_bias = 2.0        # range 1.5-2.0
     
280. #geqo_seed = 0.0            # range 0.0-1.0
     
281. 
     
282. # - Other Planner Options -
     
283. 
     
284. #default_statistics_target = 100    # range 1-10000
     
285. #constraint_exclusion = partition    # on, off, or partition
     
286. #cursor_tuple_fraction = 0.1        # range 0.0-1.0
     
287. #from_collapse_limit = 8
     
288. #join_collapse_limit = 8        # 1 disables collapsing of explicit
     
289.                     # JOIN clauses
     
290. 
     
291. 
     
292. #------------------------------------------------------------------------------
     
293. # ERROR REPORTING AND LOGGING
     
294. #------------------------------------------------------------------------------
     
295. 
     
296. # - Where to Log -
     
297. 
     
298. #log_destination = 'stderr'        # Valid values are combinations of
     
299.                     # stderr, csvlog, syslog, and eventlog,
     
300.                     # depending on platform.  csvlog
     
301.                     # requires logging_collector to be on.
     
302. 
     
303. # This is used when logging to stderr:
     
304. #logging_collector = off        # Enable capturing of stderr and csvlog
     
305.                     # into log files. Required to be on for
     
306.                     # csvlogs.
     
307.                     # (change requires restart)
     
308. 
     
309. # These are only used if logging_collector is on:
     
310. #log_directory = 'pg_log'        # directory where log files are written,
     
311.                     # can be absolute or relative to PGDATA
     
312. #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'    # log file name pattern,
     
313.                     # can include strftime() escapes
     
314. #log_file_mode = 0600            # creation mode for log files,
     
315.                     # begin with 0 to use octal notation
     
316. #log_truncate_on_rotation = off        # If on, an existing log file with the
     
317.                     # same name as the new log file will be
     
318.                     # truncated rather than appended to.
     
319.                     # But such truncation only occurs on
     
320.                     # time-driven rotation, not on restarts
     
321.                     # or size-driven rotation.  Default is
     
322.                     # off, meaning append to existing files
     
323.                     # in all cases.
     
324. #log_rotation_age = 1d            # Automatic rotation of logfiles will
     
325.                     # happen after that time.  0 disables.
     
326. #log_rotation_size = 10MB        # Automatic rotation of logfiles will
     
327.                     # happen after that much log output.
     
328.                     # 0 disables.
     
329. 
     
330. # These are relevant when logging to syslog:
     
331. #syslog_facility = 'LOCAL0'
     
332. #syslog_ident = 'postgres'
     
333. 
     
334. # This is only relevant when logging to eventlog (win32):
     
335. #event_source = 'PostgreSQL'
     
336. 
     
337. # - When to Log -
     
338. 
     
339. #client_min_messages = notice        # values in order of decreasing detail:
     
340.                     #   debug5
     
341.                     #   debug4
     
342.                     #   debug3
     
343.                     #   debug2
     
344.                     #   debug1
     
345.                     #   log
     
346.                     #   notice
     
347.                     #   warning
     
348.                     #   error
     
349. 
     
350. #log_min_messages = warning        # values in order of decreasing detail:
     
351.                     #   debug5
     
352.                     #   debug4
     
353.                     #   debug3
     
354.                     #   debug2
     
355.                     #   debug1
     
356.                     #   info
     
357.                     #   notice
     
358.                     #   warning
     
359.                     #   error
     
360.                     #   log
     
361.                     #   fatal
     
362.                     #   panic
     
363. 
     
364. #log_min_error_statement = error    # values in order of decreasing detail:
     
365.                     #   debug5
     
366.                     #   debug4
     
367.                     #   debug3
     
368.                     #   debug2
     
369.                     #   debug1
     
370.                     #   info
     
371.                     #   notice
     
372.                     #   warning
     
373.                     #   error
     
374.                     #   log
     
375.                     #   fatal
     
376.                     #   panic (effectively off)
     
377. 
     
378. #log_min_duration_statement = -1    # -1 is disabled, 0 logs all statements
     
379.                     # and their durations, > 0 logs only
     
380.                     # statements running at least this number
     
381.                     # of milliseconds
     
382. 
     
383. 
     
384. # - What to Log -
     
385. 
     
386. #debug_print_parse = off
     
387. #debug_print_rewritten = off
     
388. #debug_print_plan = off
     
389. #debug_pretty_print = on
     
390. #log_checkpoints = off
     
391. #log_connections = off
     
392. #log_disconnections = off
     
393. #log_duration = off
     
394. #log_error_verbosity = default        # terse, default, or verbose messages
     
395. #log_hostname = off
     
396. log_line_prefix = '%t '            # special values:
     
397.                     #   %a = application name
     
398.                     #   %u = user name
     
399.                     #   %d = database name
     
400.                     #   %r = remote host and port
     
401.                     #   %h = remote host
     
402.                     #   %p = process ID
     
403.                     #   %t = timestamp without milliseconds
     
404.                     #   %m = timestamp with milliseconds
     
405.                     #   %i = command tag
     
406.                     #   %e = SQL state
     
407.                     #   %c = session ID
     
408.                     #   %l = session line number
     
409.                     #   %s = session start timestamp
     
410.                     #   %v = virtual transaction ID
     
411.                     #   %x = transaction ID (0 if none)
     
412.                     #   %q = stop here in non-session
     
413.                     #        processes
     
414.                     #   %% = '%'
     
415.                     # e.g. '<%u%%%d> '
     
416. #log_lock_waits = off            # log lock waits >= deadlock_timeout
     
417. #log_statement = 'none'            # none, ddl, mod, all
     
418. #log_temp_files = -1            # log temporary files equal or larger
     
419.                     # than the specified size in kilobytes;
     
420.                     # -1 disables, 0 logs all temp files
     
421. log_timezone = 'PRC'
     
422. 
     
423. 
     
424. #------------------------------------------------------------------------------
     
425. # RUNTIME STATISTICS
     
426. #------------------------------------------------------------------------------
     
427. 
     
428. # - Query/Index Statistics Collector -
     
429. 
     
430. #track_activities = on
     
431. #track_counts = on
     
432. #track_io_timing = off
     
433. #track_functions = none            # none, pl, all
     
434. #track_activity_query_size = 1024    # (change requires restart)
     
435. #update_process_title = on
     
436. #stats_temp_directory = 'pg_stat_tmp'
     
437. 
     
438. 
     
439. # - Statistics Monitoring -
     
440. 
     
441. #log_parser_stats = off
     
442. #log_planner_stats = off
     
443. #log_executor_stats = off
     
444. #log_statement_stats = off
     
445. 
     
446. 
     
447. #------------------------------------------------------------------------------
     
448. # AUTOVACUUM PARAMETERS
     
449. #------------------------------------------------------------------------------
     
450. 
     
451. #autovacuum = on            # Enable autovacuum subprocess?  'on'
     
452.                     # requires track_counts to also be on.
     
453. #log_autovacuum_min_duration = -1    # -1 disables, 0 logs all actions and
     
454.                     # their durations, > 0 logs only
     
455.                     # actions running at least this number
     
456.                     # of milliseconds.
     
457. #autovacuum_max_workers = 3        # max number of autovacuum subprocesses
     
458.                     # (change requires restart)
     
459. #autovacuum_naptime = 1min        # time between autovacuum runs
     
460. #autovacuum_vacuum_threshold = 50    # min number of row updates before
     
461.                     # vacuum
     
462. #autovacuum_analyze_threshold = 50    # min number of row updates before
     
463.                     # analyze
     
464. #autovacuum_vacuum_scale_factor = 0.2    # fraction of table size before vacuum
     
465. #autovacuum_analyze_scale_factor = 0.1    # fraction of table size before analyze
     
466. #autovacuum_freeze_max_age = 200000000    # maximum XID age before forced vacuum
     
467.                     # (change requires restart)
     
468. #autovacuum_multixact_freeze_max_age = 400000000    # maximum Multixact age
     
469.                     # before forced vacuum
     
470.                     # (change requires restart)
     
471. #autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
     
472.                     # autovacuum, in milliseconds;
     
473.                     # -1 means use vacuum_cost_delay
     
474. #autovacuum_vacuum_cost_limit = -1    # default vacuum cost limit for
     
475.                     # autovacuum, -1 means use
     
476.                     # vacuum_cost_limit
     
477. 
     
478. 
     
479. #------------------------------------------------------------------------------
     
480. # CLIENT CONNECTION DEFAULTS
     
481. #------------------------------------------------------------------------------
     
482. 
     
483. # - Statement Behavior -
     
484. 
     
485. #search_path = '"$user",public'        # schema names
     
486. #default_tablespace = ''        # a tablespace name, '' uses the default
     
487. #temp_tablespaces = ''            # a list of tablespace names, '' uses
     
488.                     # only default tablespace
     
489. #check_function_bodies = on
     
490. #default_transaction_isolation = 'read committed'
     
491. #default_transaction_read_only = off
     
492. #default_transaction_deferrable = off
     
493. #session_replication_role = 'origin'
     
494. #statement_timeout = 0            # in milliseconds, 0 is disabled
     
495. #lock_timeout = 0            # in milliseconds, 0 is disabled
     
496. #vacuum_freeze_min_age = 50000000
     
497. #vacuum_freeze_table_age = 150000000
     
498. #vacuum_multixact_freeze_min_age = 5000000
     
499. #vacuum_multixact_freeze_table_age = 150000000
     
500. #bytea_output = 'hex'            # hex, escape
     
501. #xmlbinary = 'base64'
     
502. #xmloption = 'content'
     
503. 
     
504. # - Locale and Formatting -
     
505. 
     
506. datestyle = 'iso, ymd'
     
507. #intervalstyle = 'postgres'
     
508. timezone = 'PRC'
     
509. #timezone_abbreviations = 'Default'     # Select the set of available time zone
     
510.                     # abbreviations.  Currently, there are
     
511.                     #   Default
     
512.                     #   Australia
     
513.                     #   India
     
514.                     # You can create your own file in
     
515.                     # share/timezonesets/.
     
516. #extra_float_digits = 0            # min -15, max 3
     
517. #client_encoding = sql_ascii        # actually, defaults to database
     
518.                     # encoding
     
519. 
     
520. # These settings are initialized by initdb, but they can be changed.
     
521. lc_messages = 'zh_CN.UTF-8'            # locale for system error message
     
522.                     # strings
     
523. lc_monetary = 'zh_CN'            # locale for monetary formatting
     
524. lc_numeric = 'zh_CN'            # locale for number formatting
     
525. lc_time = 'zh_CN'                # locale for time formatting
     
526. 
     
527. # default configuration for text search
     
528. default_text_search_config = 'pg_catalog.simple'
     
529. 
     
530. # - Other Defaults -
     
531. 
     
532. #dynamic_library_path = '$libdir'
     
533. #local_preload_libraries = ''
     
534. 
     
535. 
     
536. #------------------------------------------------------------------------------
     
537. # LOCK MANAGEMENT
     
538. #------------------------------------------------------------------------------
     
539. 
     
540. #deadlock_timeout = 1s
     
541. #max_locks_per_transaction = 64        # min 10
     
542.                     # (change requires restart)
     
543. # Note:  Each lock table slot uses ~270 bytes of shared memory, and there are
     
544. # max_locks_per_transaction * (max_connections + max_prepared_transactions)
     
545. # lock table slots.
     
546. #max_pred_locks_per_transaction = 64    # min 10
     
547.                     # (change requires restart)
     
548. 
     
549. 
     
550. #------------------------------------------------------------------------------
     
551. # VERSION/PLATFORM COMPATIBILITY
     
552. #------------------------------------------------------------------------------
     
553. 
     
554. # - Previous PostgreSQL Versions -
     
555. 
     
556. #array_nulls = on
     
557. #backslash_quote = safe_encoding    # on, off, or safe_encoding
     
558. #default_with_oids = off
     
559. #escape_string_warning = on
     
560. #lo_compat_privileges = off
     
561. #quote_all_identifiers = off
     
562. #sql_inheritance = on
     
563. #standard_conforming_strings = on
     
564. #synchronize_seqscans = on
     
565. 
     
566. # - Other Platforms and Clients -
     
567. 
     
568. #transform_null_equals = off
     
569. 
     
570. 
     
571. #------------------------------------------------------------------------------
     
572. # ERROR HANDLING
     
573. #------------------------------------------------------------------------------
     
574. 
     
575. #exit_on_error = off            # terminate session on any error?
     
576. #restart_after_crash = on        # reinitialize after backend crash?
     
577. 
     
578. 
     
579. #------------------------------------------------------------------------------
     
580. # CONFIG FILE INCLUDES
     
581. #------------------------------------------------------------------------------
     
582. 
     
583. # These options allow settings to be loaded from files other than the
     
584. # default postgresql.conf.
     
585. 
     
586. #include_dir = 'conf.d'            # include files ending in '.conf' from
     
587.                     # directory 'conf.d'
     
588. #include_if_exists = 'exists.conf'    # include file only if it exists
     
589. #include = 'special.conf'        # include file
     
590. 
     
591. 
     
592. #------------------------------------------------------------------------------
     
593. # CUSTOMIZED OPTIONS
     
594. #------------------------------------------------------------------------------
     
595. 
     
596. # Add settings for extensions here

复制代码

修改后的配置文件

1. # -----------------------------
   
2. # PostgreSQL configuration file
   
3. # -----------------------------
   
4. #
   
5. # This file consists of lines of the form:
   
6. #
   
7. #   name = value
   
8. #
   
9. # (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
   
10. # "#" anywhere on a line.  The complete list of parameter names and allowed
    
11. # values can be found in the PostgreSQL documentation.
    
12. #
    
13. # The commented-out settings shown in this file represent the default values.
    
14. # Re-commenting a setting is NOT sufficient to revert it to the default value;
    
15. # you need to reload the server.
    
16. #
    
17. # This file is read on server startup and when the server receives a SIGHUP
    
18. # signal.  If you edit the file on a running system, you have to SIGHUP the
    
19. # server for the changes to take effect, or use "pg_ctl reload".  Some
    
20. # parameters, which are marked below, require a server shutdown and restart to
    
21. # take effect.
    
22. #
    
23. # Any parameter can also be given as a command-line option to the server, e.g.,
    
24. # "postgres -c log_connections=on".  Some parameters can be changed at run time
    
25. # with the "SET" SQL command.
    
26. #
    
27. # Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
    
28. #                MB = megabytes                     s   = seconds
    
29. #                GB = gigabytes                     min = minutes
    
30. #                                                   h   = hours
    
31. #                                                   d   = days
    
32. 
    
33. 
    
34. #------------------------------------------------------------------------------
    
35. # FILE LOCATIONS
    
36. #------------------------------------------------------------------------------
    
37. 
    
38. # The default values of these variables are driven from the -D command-line
    
39. # option or PGDATA environment variable, represented here as ConfigDir.
    
40. 
    
41. data_directory = '/var/lib/postgresql/9.3/main'        # use data in another directory
    
42.                     # (change requires restart)
    
43. hba_file = '/etc/postgresql/9.3/main/pg_hba.conf'    # host-based authentication file
    
44.                     # (change requires restart)
    
45. ident_file = '/etc/postgresql/9.3/main/pg_ident.conf'    # ident configuration file
    
46.                     # (change requires restart)
    
47. 
    
48. # If external_pid_file is not explicitly set, no extra PID file is written.
    
49. external_pid_file = '/var/run/postgresql/9.3-main.pid'            # write an extra PID file
    
50.                     # (change requires restart)
    
51. 
    
52. 
    
53. #------------------------------------------------------------------------------
    
54. # CONNECTIONS AND AUTHENTICATION
    
55. #------------------------------------------------------------------------------
    
56. 
    
57. # - Connection Settings -
    
58. 
    
59. listen_addresses = '*'        # what IP address(es) to listen on;    #监听任何地址
    
60.                     # comma-separated list of addresses;
    
61.                     # defaults to 'localhost'; use '*' for all
    
62.                     # (change requires restart)
    
63. port = 5432                # (change requires restart)
    
64. max_connections = 100            # (change requires restart)
    
65. # Note:  Increasing max_connections costs ~400 bytes of shared memory per
    
66. # connection slot, plus lock space (see max_locks_per_transaction).
    
67. #superuser_reserved_connections = 3    # (change requires restart)
    
68. unix_socket_directories = '/var/run/postgresql'    # comma-separated list of directories
    
69.                     # (change requires restart)
    
70. #unix_socket_group = ''            # (change requires restart)
    
71. #unix_socket_permissions = 0777        # begin with 0 to use octal notation
    
72.                     # (change requires restart)
    
73. #bonjour = off                # advertise server via Bonjour
    
74.                     # (change requires restart)
    
75. #bonjour_name = ''            # defaults to the computer name
    
76.                     # (change requires restart)
    
77. 
    
78. # - Security and Authentication -
    
79. 
    
80. #authentication_timeout = 1min        # 1s-600s
    
81. ssl = true                # (change requires restart)
    
82. #ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH'    # allowed SSL ciphers
    
83.                     # (change requires restart)
    
84. #ssl_renegotiation_limit = 512MB    # amount of data between renegotiations
    
85. ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'        # (change requires restart)
    
86. ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'        # (change requires restart)
    
87. #ssl_ca_file = ''            # (change requires restart)
    
88. #ssl_crl_file = ''            # (change requires restart)
    
89. password_encryption = on    #启用口令加密
    
90. #db_user_namespace = off
    
91. 
    
92. # Kerberos and GSSAPI
    
93. #krb_server_keyfile = ''
    
94. #krb_srvname = 'postgres'        # (Kerberos only)
    
95. #krb_caseins_users = off
    
96. 
    
97. # - TCP Keepalives -
    
98. # see "man 7 tcp" for details
    
99. 
    
100. #tcp_keepalives_idle = 0        # TCP_KEEPIDLE, in seconds;
     
101.                     # 0 selects the system default
     
102. #tcp_keepalives_interval = 0        # TCP_KEEPINTVL, in seconds;
     
103.                     # 0 selects the system default
     
104. #tcp_keepalives_count = 0        # TCP_KEEPCNT;
     
105.                     # 0 selects the system default
     
106. 
     
107. 
     
108. #------------------------------------------------------------------------------
     
109. # RESOURCE USAGE (except WAL)
     
110. #------------------------------------------------------------------------------
     
111. 
     
112. # - Memory -
     
113. 
     
114. shared_buffers = 128MB            # min 128kB
     
115.                     # (change requires restart)
     
116. #temp_buffers = 8MB            # min 800kB
     
117. #max_prepared_transactions = 0        # zero disables the feature
     
118.                     # (change requires restart)
     
119. # Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
     
120. # per transaction slot, plus lock space (see max_locks_per_transaction).
     
121. # It is not advisable to set max_prepared_transactions nonzero unless you
     
122. # actively intend to use prepared transactions.
     
123. #work_mem = 1MB                # min 64kB
     
124. #maintenance_work_mem = 16MB        # min 1MB
     
125. #max_stack_depth = 2MB            # min 100kB
     
126. 
     
127. # - Disk -
     
128. 
     
129. #temp_file_limit = -1            # limits per-session temp file space
     
130.                     # in kB, or -1 for no limit
     
131. 
     
132. # - Kernel Resource Usage -
     
133. 
     
134. #max_files_per_process = 1000        # min 25
     
135.                     # (change requires restart)
     
136. #shared_preload_libraries = ''        # (change requires restart)
     
137. 
     
138. # - Cost-Based Vacuum Delay -
     
139. 
     
140. #vacuum_cost_delay = 0            # 0-100 milliseconds
     
141. #vacuum_cost_page_hit = 1        # 0-10000 credits
     
142. #vacuum_cost_page_miss = 10        # 0-10000 credits
     
143. #vacuum_cost_page_dirty = 20        # 0-10000 credits
     
144. #vacuum_cost_limit = 200        # 1-10000 credits
     
145. 
     
146. # - Background Writer -
     
147. 
     
148. #bgwriter_delay = 200ms            # 10-10000ms between rounds
     
149. #bgwriter_lru_maxpages = 100        # 0-1000 max buffers written/round
     
150. #bgwriter_lru_multiplier = 2.0        # 0-10.0 multipler on buffers scanned/round
     
151. 
     
152. # - Asynchronous Behavior -
     
153. 
     
154. #effective_io_concurrency = 1        # 1-1000; 0 disables prefetching
     
155. 
     
156. 
     
157. #------------------------------------------------------------------------------
     
158. # WRITE AHEAD LOG
     
159. #------------------------------------------------------------------------------
     
160. 
     
161. # - Settings -
     
162. 
     
163. #wal_level = minimal            # minimal, archive, or hot_standby
     
164.                     # (change requires restart)
     
165. #fsync = on                # turns forced synchronization on or off
     
166. #synchronous_commit = on        # synchronization level;
     
167.                     # off, local, remote_write, or on
     
168. #wal_sync_method = fsync        # the default is the first option
     
169.                     # supported by the operating system:
     
170.                     #   open_datasync
     
171.                     #   fdatasync (default on Linux)
     
172.                     #   fsync
     
173.                     #   fsync_writethrough
     
174.                     #   open_sync
     
175. #full_page_writes = on            # recover from partial page writes
     
176. #wal_buffers = -1            # min 32kB, -1 sets based on shared_buffers
     
177.                     # (change requires restart)
     
178. #wal_writer_delay = 200ms        # 1-10000 milliseconds
     
179. 
     
180. #commit_delay = 0            # range 0-100000, in microseconds
     
181. #commit_siblings = 5            # range 1-1000
     
182. 
     
183. # - Checkpoints -
     
184. 
     
185. #checkpoint_segments = 3        # in logfile segments, min 1, 16MB each
     
186. #checkpoint_timeout = 5min        # range 30s-1h
     
187. #checkpoint_completion_target = 0.5    # checkpoint target duration, 0.0 - 1.0
     
188. #checkpoint_warning = 30s        # 0 disables
     
189. 
     
190. # - Archiving -
     
191. 
     
192. #archive_mode = off        # allows archiving to be done
     
193.                 # (change requires restart)
     
194. #archive_command = ''        # command to use to archive a logfile segment
     
195.                 # placeholders: %p = path of file to archive
     
196.                 #               %f = file name only
     
197.                 # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
     
198. #archive_timeout = 0        # force a logfile segment switch after this
     
199.                 # number of seconds; 0 disables
     
200. 
     
201. 
     
202. #------------------------------------------------------------------------------
     
203. # REPLICATION
     
204. #------------------------------------------------------------------------------
     
205. 
     
206. # - Sending Server(s) -
     
207. 
     
208. # Set these on the master and on any standby that will send replication data.
     
209. 
     
210. #max_wal_senders = 0        # max number of walsender processes
     
211.                 # (change requires restart)
     
212. #wal_keep_segments = 0        # in logfile segments, 16MB each; 0 disables
     
213. #wal_sender_timeout = 60s    # in milliseconds; 0 disables
     
214. 
     
215. # - Master Server -
     
216. 
     
217. # These settings are ignored on a standby server.
     
218. 
     
219. #synchronous_standby_names = ''    # standby servers that provide sync rep
     
220.                 # comma-separated list of application_name
     
221.                 # from standby(s); '*' = all
     
222. #vacuum_defer_cleanup_age = 0    # number of xacts by which cleanup is delayed
     
223. 
     
224. # - Standby Servers -
     
225. 
     
226. # These settings are ignored on a master server.
     
227. 
     
228. #hot_standby = off            # "on" allows queries during recovery
     
229.                     # (change requires restart)
     
230. #max_standby_archive_delay = 30s    # max delay before canceling queries
     
231.                     # when reading WAL from archive;
     
232.                     # -1 allows indefinite delay
     
233. #max_standby_streaming_delay = 30s    # max delay before canceling queries
     
234.                     # when reading streaming WAL;
     
235.                     # -1 allows indefinite delay
     
236. #wal_receiver_status_interval = 10s    # send replies at least this often
     
237.                     # 0 disables
     
238. #hot_standby_feedback = off        # send info from standby to prevent
     
239.                     # query conflicts
     
240. #wal_receiver_timeout = 60s        # time that receiver waits for
     
241.                     # communication from master
     
242.                     # in milliseconds; 0 disables
     
243. 
     
244. 
     
245. #------------------------------------------------------------------------------
     
246. # QUERY TUNING
     
247. #------------------------------------------------------------------------------
     
248. 
     
249. # - Planner Method Configuration -
     
250. 
     
251. #enable_bitmapscan = on
     
252. #enable_hashagg = on
     
253. #enable_hashjoin = on
     
254. #enable_indexscan = on
     
255. #enable_indexonlyscan = on
     
256. #enable_material = on
     
257. #enable_mergejoin = on
     
258. #enable_nestloop = on
     
259. #enable_seqscan = on
     
260. #enable_sort = on
     
261. #enable_tidscan = on
     
262. 
     
263. # - Planner Cost Constants -
     
264. 
     
265. #seq_page_cost = 1.0            # measured on an arbitrary scale
     
266. #random_page_cost = 4.0            # same scale as above
     
267. #cpu_tuple_cost = 0.01            # same scale as above
     
268. #cpu_index_tuple_cost = 0.005        # same scale as above
     
269. #cpu_operator_cost = 0.0025        # same scale as above
     
270. #effective_cache_size = 128MB
     
271. 
     
272. # - Genetic Query Optimizer -
     
273. 
     
274. #geqo = on
     
275. #geqo_threshold = 12
     
276. #geqo_effort = 5            # range 1-10
     
277. #geqo_pool_size = 0            # selects default based on effort
     
278. #geqo_generations = 0            # selects default based on effort
     
279. #geqo_selection_bias = 2.0        # range 1.5-2.0
     
280. #geqo_seed = 0.0            # range 0.0-1.0
     
281. 
     
282. # - Other Planner Options -
     
283. 
     
284. #default_statistics_target = 100    # range 1-10000
     
285. #constraint_exclusion = partition    # on, off, or partition
     
286. #cursor_tuple_fraction = 0.1        # range 0.0-1.0
     
287. #from_collapse_limit = 8
     
288. #join_collapse_limit = 8        # 1 disables collapsing of explicit
     
289.                     # JOIN clauses
     
290. 
     
291. 
     
292. #------------------------------------------------------------------------------
     
293. # ERROR REPORTING AND LOGGING
     
294. #------------------------------------------------------------------------------
     
295. 
     
296. # - Where to Log -
     
297. 
     
298. #log_destination = 'stderr'        # Valid values are combinations of
     
299.                     # stderr, csvlog, syslog, and eventlog,
     
300.                     # depending on platform.  csvlog
     
301.                     # requires logging_collector to be on.
     
302. 
     
303. # This is used when logging to stderr:
     
304. #logging_collector = off        # Enable capturing of stderr and csvlog
     
305.                     # into log files. Required to be on for
     
306.                     # csvlogs.
     
307.                     # (change requires restart)
     
308. 
     
309. # These are only used if logging_collector is on:
     
310. #log_directory = 'pg_log'        # directory where log files are written,
     
311.                     # can be absolute or relative to PGDATA
     
312. #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'    # log file name pattern,
     
313.                     # can include strftime() escapes
     
314. #log_file_mode = 0600            # creation mode for log files,
     
315.                     # begin with 0 to use octal notation
     
316. #log_truncate_on_rotation = off        # If on, an existing log file with the
     
317.                     # same name as the new log file will be
     
318.                     # truncated rather than appended to.
     
319.                     # But such truncation only occurs on
     
320.                     # time-driven rotation, not on restarts
     
321.                     # or size-driven rotation.  Default is
     
322.                     # off, meaning append to existing files
     
323.                     # in all cases.
     
324. #log_rotation_age = 1d            # Automatic rotation of logfiles will
     
325.                     # happen after that time.  0 disables.
     
326. #log_rotation_size = 10MB        # Automatic rotation of logfiles will
     
327.                     # happen after that much log output.
     
328.                     # 0 disables.
     
329. 
     
330. # These are relevant when logging to syslog:
     
331. #syslog_facility = 'LOCAL0'
     
332. #syslog_ident = 'postgres'
     
333. 
     
334. # This is only relevant when logging to eventlog (win32):
     
335. #event_source = 'PostgreSQL'
     
336. 
     
337. # - When to Log -
     
338. 
     
339. #client_min_messages = notice        # values in order of decreasing detail:
     
340.                     #   debug5
     
341.                     #   debug4
     
342.                     #   debug3
     
343.                     #   debug2
     
344.                     #   debug1
     
345.                     #   log
     
346.                     #   notice
     
347.                     #   warning
     
348.                     #   error
     
349. 
     
350. #log_min_messages = warning        # values in order of decreasing detail:
     
351.                     #   debug5
     
352.                     #   debug4
     
353.                     #   debug3
     
354.                     #   debug2
     
355.                     #   debug1
     
356.                     #   info
     
357.                     #   notice
     
358.                     #   warning
     
359.                     #   error
     
360.                     #   log
     
361.                     #   fatal
     
362.                     #   panic
     
363. 
     
364. #log_min_error_statement = error    # values in order of decreasing detail:
     
365.                     #   debug5
     
366.                     #   debug4
     
367.                     #   debug3
     
368.                     #   debug2
     
369.                     #   debug1
     
370.                     #   info
     
371.                     #   notice
     
372.                     #   warning
     
373.                     #   error
     
374.                     #   log
     
375.                     #   fatal
     
376.                     #   panic (effectively off)
     
377. 
     
378. #log_min_duration_statement = -1    # -1 is disabled, 0 logs all statements
     
379.                     # and their durations, > 0 logs only
     
380.                     # statements running at least this number
     
381.                     # of milliseconds
     
382. 
     
383. 
     
384. # - What to Log -
     
385. 
     
386. #debug_print_parse = off
     
387. #debug_print_rewritten = off
     
388. #debug_print_plan = off
     
389. #debug_pretty_print = on
     
390. #log_checkpoints = off
     
391. #log_connections = off
     
392. #log_disconnections = off
     
393. #log_duration = off
     
394. #log_error_verbosity = default        # terse, default, or verbose messages
     
395. #log_hostname = off
     
396. log_line_prefix = '%t '            # special values:
     
397.                     #   %a = application name
     
398.                     #   %u = user name
     
399.                     #   %d = database name
     
400.                     #   %r = remote host and port
     
401.                     #   %h = remote host
     
402.                     #   %p = process ID
     
403.                     #   %t = timestamp without milliseconds
     
404.                     #   %m = timestamp with milliseconds
     
405.                     #   %i = command tag
     
406.                     #   %e = SQL state
     
407.                     #   %c = session ID
     
408.                     #   %l = session line number
     
409.                     #   %s = session start timestamp
     
410.                     #   %v = virtual transaction ID
     
411.                     #   %x = transaction ID (0 if none)
     
412.                     #   %q = stop here in non-session
     
413.                     #        processes
     
414.                     #   %% = '%'
     
415.                     # e.g. '<%u%%%d> '
     
416. #log_lock_waits = off            # log lock waits >= deadlock_timeout
     
417. #log_statement = 'none'            # none, ddl, mod, all
     
418. #log_temp_files = -1            # log temporary files equal or larger
     
419.                     # than the specified size in kilobytes;
     
420.                     # -1 disables, 0 logs all temp files
     
421. log_timezone = 'PRC'
     
422. 
     
423. 
     
424. #------------------------------------------------------------------------------
     
425. # RUNTIME STATISTICS
     
426. #------------------------------------------------------------------------------
     
427. 
     
428. # - Query/Index Statistics Collector -
     
429. 
     
430. #track_activities = on
     
431. #track_counts = on
     
432. #track_io_timing = off
     
433. #track_functions = none            # none, pl, all
     
434. #track_activity_query_size = 1024    # (change requires restart)
     
435. #update_process_title = on
     
436. #stats_temp_directory = 'pg_stat_tmp'
     
437. 
     
438. 
     
439. # - Statistics Monitoring -
     
440. 
     
441. #log_parser_stats = off
     
442. #log_planner_stats = off
     
443. #log_executor_stats = off
     
444. #log_statement_stats = off
     
445. 
     
446. 
     
447. #------------------------------------------------------------------------------
     
448. # AUTOVACUUM PARAMETERS
     
449. #------------------------------------------------------------------------------
     
450. 
     
451. #autovacuum = on            # Enable autovacuum subprocess?  'on'
     
452.                     # requires track_counts to also be on.
     
453. #log_autovacuum_min_duration = -1    # -1 disables, 0 logs all actions and
     
454.                     # their durations, > 0 logs only
     
455.                     # actions running at least this number
     
456.                     # of milliseconds.
     
457. #autovacuum_max_workers = 3        # max number of autovacuum subprocesses
     
458.                     # (change requires restart)
     
459. #autovacuum_naptime = 1min        # time between autovacuum runs
     
460. #autovacuum_vacuum_threshold = 50    # min number of row updates before
     
461.                     # vacuum
     
462. #autovacuum_analyze_threshold = 50    # min number of row updates before
     
463.                     # analyze
     
464. #autovacuum_vacuum_scale_factor = 0.2    # fraction of table size before vacuum
     
465. #autovacuum_analyze_scale_factor = 0.1    # fraction of table size before analyze
     
466. #autovacuum_freeze_max_age = 200000000    # maximum XID age before forced vacuum
     
467.                     # (change requires restart)
     
468. #autovacuum_multixact_freeze_max_age = 400000000    # maximum Multixact age
     
469.                     # before forced vacuum
     
470.                     # (change requires restart)
     
471. #autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
     
472.                     # autovacuum, in milliseconds;
     
473.                     # -1 means use vacuum_cost_delay
     
474. #autovacuum_vacuum_cost_limit = -1    # default vacuum cost limit for
     
475.                     # autovacuum, -1 means use
     
476.                     # vacuum_cost_limit
     
477. 
     
478. 
     
479. #------------------------------------------------------------------------------
     
480. # CLIENT CONNECTION DEFAULTS
     
481. #------------------------------------------------------------------------------
     
482. 
     
483. # - Statement Behavior -
     
484. 
     
485. #search_path = '"$user",public'        # schema names
     
486. #default_tablespace = ''        # a tablespace name, '' uses the default
     
487. #temp_tablespaces = ''            # a list of tablespace names, '' uses
     
488.                     # only default tablespace
     
489. #check_function_bodies = on
     
490. #default_transaction_isolation = 'read committed'
     
491. #default_transaction_read_only = off
     
492. #default_transaction_deferrable = off
     
493. #session_replication_role = 'origin'
     
494. #statement_timeout = 0            # in milliseconds, 0 is disabled
     
495. #lock_timeout = 0            # in milliseconds, 0 is disabled
     
496. #vacuum_freeze_min_age = 50000000
     
497. #vacuum_freeze_table_age = 150000000
     
498. #vacuum_multixact_freeze_min_age = 5000000
     
499. #vacuum_multixact_freeze_table_age = 150000000
     
500. #bytea_output = 'hex'            # hex, escape
     
501. #xmlbinary = 'base64'
     
502. #xmloption = 'content'
     
503. 
     
504. # - Locale and Formatting -
     
505. 
     
506. datestyle = 'iso, ymd'
     
507. #intervalstyle = 'postgres'
     
508. timezone = 'PRC'
     
509. #timezone_abbreviations = 'Default'     # Select the set of available time zone
     
510.                     # abbreviations.  Currently, there are
     
511.                     #   Default
     
512.                     #   Australia
     
513.                     #   India
     
514.                     # You can create your own file in
     
515.                     # share/timezonesets/.
     
516. #extra_float_digits = 0            # min -15, max 3
     
517. #client_encoding = sql_ascii        # actually, defaults to database
     
518.                     # encoding
     
519. 
     
520. # These settings are initialized by initdb, but they can be changed.
     
521. lc_messages = 'zh_CN.UTF-8'            # locale for system error message
     
522.                     # strings
     
523. lc_monetary = 'zh_CN'            # locale for monetary formatting
     
524. lc_numeric = 'zh_CN'            # locale for number formatting
     
525. lc_time = 'zh_CN'                # locale for time formatting
     
526. 
     
527. # default configuration for text search
     
528. default_text_search_config = 'pg_catalog.simple'
     
529. 
     
530. # - Other Defaults -
     
531. 
     
532. #dynamic_library_path = '$libdir'
     
533. #local_preload_libraries = ''
     
534. 
     
535. 
     
536. #------------------------------------------------------------------------------
     
537. # LOCK MANAGEMENT
     
538. #------------------------------------------------------------------------------
     
539. 
     
540. #deadlock_timeout = 1s
     
541. #max_locks_per_transaction = 64        # min 10
     
542.                     # (change requires restart)
     
543. # Note:  Each lock table slot uses ~270 bytes of shared memory, and there are
     
544. # max_locks_per_transaction * (max_connections + max_prepared_transactions)
     
545. # lock table slots.
     
546. #max_pred_locks_per_transaction = 64    # min 10
     
547.                     # (change requires restart)
     
548. 
     
549. 
     
550. #------------------------------------------------------------------------------
     
551. # VERSION/PLATFORM COMPATIBILITY
     
552. #------------------------------------------------------------------------------
     
553. 
     
554. # - Previous PostgreSQL Versions -
     
555. 
     
556. #array_nulls = on
     
557. #backslash_quote = safe_encoding    # on, off, or safe_encoding
     
558. #default_with_oids = off
     
559. #escape_string_warning = on
     
560. #lo_compat_privileges = off
     
561. #quote_all_identifiers = off
     
562. #sql_inheritance = on
     
563. #standard_conforming_strings = on
     
564. #synchronize_seqscans = on
     
565. 
     
566. # - Other Platforms and Clients -
     
567. 
     
568. #transform_null_equals = off
     
569. 
     
570. 
     
571. #------------------------------------------------------------------------------
     
572. # ERROR HANDLING
     
573. #------------------------------------------------------------------------------
     
574. 
     
575. #exit_on_error = off            # terminate session on any error?
     
576. #restart_after_crash = on        # reinitialize after backend crash?
     
577. 
     
578. 
     
579. #------------------------------------------------------------------------------
     
580. # CONFIG FILE INCLUDES
     
581. #------------------------------------------------------------------------------
     
582. 
     
583. # These options allow settings to be loaded from files other than the
     
584. # default postgresql.conf.
     
585. 
     
586. #include_dir = 'conf.d'            # include files ending in '.conf' from
     
587.                     # directory 'conf.d'
     
588. #include_if_exists = 'exists.conf'    # include file only if it exists
     
589. #include = 'special.conf'        # include file
     
590. 
     
591. 
     
592. #------------------------------------------------------------------------------
     
593. # CUSTOMIZED OPTIONS
     
594. #------------------------------------------------------------------------------
     
595. 
     
596. # Add settings for extensions here

复制代码

08、修改 PostgresSQL 数据库配置文件 pg_hba.conf

1. vi /etc/postgresql/9.3/main/pg_hba.conf

复制代码

修改前的配置文件

1. # PostgreSQL Client Authentication Configuration File
   
2. # ===================================================
   
3. #
   
4. # Refer to the "Client Authentication" section in the PostgreSQL
   
5. # documentation for a complete description of this file.  A short
   
6. # synopsis follows.
   
7. #
   
8. # This file controls: which hosts are allowed to connect, how clients
   
9. # are authenticated, which PostgreSQL user names they can use, which
   
10. # databases they can access.  Records take one of these forms:
    
11. #
    
12. # local      DATABASE  USER  METHOD  [OPTIONS]
    
13. # host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
    
14. # hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
    
15. # hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
    
16. #
    
17. # (The uppercase items must be replaced by actual values.)
    
18. #
    
19. # The first field is the connection type: "local" is a Unix-domain
    
20. # socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
    
21. # "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
    
22. # plain TCP/IP socket.
    
23. #
    
24. # DATABASE can be "all", "sameuser", "samerole", "replication", a
    
25. # database name, or a comma-separated list thereof. The "all"
    
26. # keyword does not match "replication". Access to replication
    
27. # must be enabled in a separate record (see example below).
    
28. #
    
29. # USER can be "all", a user name, a group name prefixed with "+", or a
    
30. # comma-separated list thereof.  In both the DATABASE and USER fields
    
31. # you can also write a file name prefixed with "@" to include names
    
32. # from a separate file.
    
33. #
    
34. # ADDRESS specifies the set of hosts the record matches.  It can be a
    
35. # host name, or it is made up of an IP address and a CIDR mask that is
    
36. # an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
    
37. # specifies the number of significant bits in the mask.  A host name
    
38. # that starts with a dot (.) matches a suffix of the actual host name.
    
39. # Alternatively, you can write an IP address and netmask in separate
    
40. # columns to specify the set of hosts.  Instead of a CIDR-address, you
    
41. # can write "samehost" to match any of the server's own IP addresses,
    
42. # or "samenet" to match any address in any subnet that the server is
    
43. # directly connected to.
    
44. #
    
45. # METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
    
46. # "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert".  Note that
    
47. # "password" sends passwords in clear text; "md5" is preferred since
    
48. # it sends encrypted passwords.
    
49. #
    
50. # OPTIONS are a set of options for the authentication in the format
    
51. # NAME=VALUE.  The available options depend on the different
    
52. # authentication methods -- refer to the "Client Authentication"
    
53. # section in the documentation for a list of which options are
    
54. # available for which authentication methods.
    
55. #
    
56. # Database and user names containing spaces, commas, quotes and other
    
57. # special characters must be quoted.  Quoting one of the keywords
    
58. # "all", "sameuser", "samerole" or "replication" makes the name lose
    
59. # its special character, and just match a database or username with
    
60. # that name.
    
61. #
    
62. # This file is read on server startup and when the postmaster receives
    
63. # a SIGHUP signal.  If you edit the file on a running system, you have
    
64. # to SIGHUP the postmaster for the changes to take effect.  You can
    
65. # use "pg_ctl reload" to do that.
    
66. 
    
67. # Put your actual configuration here
    
68. # ----------------------------------
    
69. #
    
70. # If you want to allow non-local connections, you need to add more
    
71. # "host" records.  In that case you will also need to make PostgreSQL
    
72. # listen on a non-local interface via the listen_addresses
    
73. # configuration parameter, or via the -i or -h command line switches.
    
74. 
    
75. 
    
76. 
    
77. 
    
78. # DO NOT DISABLE!
    
79. # If you change this first entry you will need to make sure that the
    
80. # database superuser can access the database using some other method.
    
81. # Noninteractive access to all databases is required during automatic
    
82. # maintenance (custom daily cronjobs, replication, and similar tasks).
    
83. #
    
84. # Database administrative login by Unix domain socket
    
85. local   all             postgres                                peer
    
86. 
    
87. # TYPE  DATABASE        USER            ADDRESS                 METHOD
    
88. 
    
89. # "local" is for Unix domain socket connections only
    
90. local   all             all                                     peer
    
91. # IPv4 local connections:
    
92. host    all             all             127.0.0.1/32            md5
    
93. # IPv6 local connections:
    
94. host    all             all             ::1/128                 md5
    
95. # Allow replication connections from localhost, by a user with the
    
96. # replication privilege.
    
97. #local   replication     postgres                                peer
    
98. #host    replication     postgres        127.0.0.1/32            md5
    
99. #host    replication     postgres        ::1/128                 md5

复制代码

修改后的配置文件

1. # PostgreSQL Client Authentication Configuration File
   
2. # ===================================================
   
3. #
   
4. # Refer to the "Client Authentication" section in the PostgreSQL
   
5. # documentation for a complete description of this file.  A short
   
6. # synopsis follows.
   
7. #
   
8. # This file controls: which hosts are allowed to connect, how clients
   
9. # are authenticated, which PostgreSQL user names they can use, which
   
10. # databases they can access.  Records take one of these forms:
    
11. #
    
12. # local      DATABASE  USER  METHOD  [OPTIONS]
    
13. # host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
    
14. # hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
    
15. # hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
    
16. #
    
17. # (The uppercase items must be replaced by actual values.)
    
18. #
    
19. # The first field is the connection type: "local" is a Unix-domain
    
20. # socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
    
21. # "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
    
22. # plain TCP/IP socket.
    
23. #
    
24. # DATABASE can be "all", "sameuser", "samerole", "replication", a
    
25. # database name, or a comma-separated list thereof. The "all"
    
26. # keyword does not match "replication". Access to replication
    
27. # must be enabled in a separate record (see example below).
    
28. #
    
29. # USER can be "all", a user name, a group name prefixed with "+", or a
    
30. # comma-separated list thereof.  In both the DATABASE and USER fields
    
31. # you can also write a file name prefixed with "@" to include names
    
32. # from a separate file.
    
33. #
    
34. # ADDRESS specifies the set of hosts the record matches.  It can be a
    
35. # host name, or it is made up of an IP address and a CIDR mask that is
    
36. # an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
    
37. # specifies the number of significant bits in the mask.  A host name
    
38. # that starts with a dot (.) matches a suffix of the actual host name.
    
39. # Alternatively, you can write an IP address and netmask in separate
    
40. # columns to specify the set of hosts.  Instead of a CIDR-address, you
    
41. # can write "samehost" to match any of the server's own IP addresses,
    
42. # or "samenet" to match any address in any subnet that the server is
    
43. # directly connected to.
    
44. #
    
45. # METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
    
46. # "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert".  Note that
    
47. # "password" sends passwords in clear text; "md5" is preferred since
    
48. # it sends encrypted passwords.
    
49. #
    
50. # OPTIONS are a set of options for the authentication in the format
    
51. # NAME=VALUE.  The available options depend on the different
    
52. # authentication methods -- refer to the "Client Authentication"
    
53. # section in the documentation for a list of which options are
    
54. # available for which authentication methods.
    
55. #
    
56. # Database and user names containing spaces, commas, quotes and other
    
57. # special characters must be quoted.  Quoting one of the keywords
    
58. # "all", "sameuser", "samerole" or "replication" makes the name lose
    
59. # its special character, and just match a database or username with
    
60. # that name.
    
61. #
    
62. # This file is read on server startup and when the postmaster receives
    
63. # a SIGHUP signal.  If you edit the file on a running system, you have
    
64. # to SIGHUP the postmaster for the changes to take effect.  You can
    
65. # use "pg_ctl reload" to do that.
    
66. 
    
67. # Put your actual configuration here
    
68. # ----------------------------------
    
69. #
    
70. # If you want to allow non-local connections, you need to add more
    
71. # "host" records.  In that case you will also need to make PostgreSQL
    
72. # listen on a non-local interface via the listen_addresses
    
73. # configuration parameter, or via the -i or -h command line switches.
    
74. 
    
75. 
    
76. 
    
77. 
    
78. # DO NOT DISABLE!
    
79. # If you change this first entry you will need to make sure that the
    
80. # database superuser can access the database using some other method.
    
81. # Noninteractive access to all databases is required during automatic
    
82. # maintenance (custom daily cronjobs, replication, and similar tasks).
    
83. #
    
84. # Database administrative login by Unix domain socket
    
85. local   all             postgres                                peer
    
86. 
    
87. # TYPE  DATABASE        USER            ADDRESS                 METHOD
    
88. 
    
89. # "local" is for Unix domain socket connections only
    
90. local   all             all                                     peer
    
91. # IPv4 local connections:
    
92. host    all             all             127.0.0.1/32            md5
    
93. # IPv6 local connections:
    
94. host    all             all             ::1/128                 md5
    
95. # Allow replication connections from localhost, by a user with the
    
96. # replication privilege.
    
97. #local   replication     postgres                                peer
    
98. #host    replication     postgres        127.0.0.1/32            md5
    
99. #host    replication     postgres        ::1/128                 md5
    
100. # to allow your client visiting postgresql server
     
101. host all all 0.0.0.0 0.0.0.0 md5

复制代码

最后一行 host 表示允许类型是主机；第一个 all 是允许访问数据库名称；第二个 all 是允许用户；第一个 0.0.0.0 是允许访问 IP Address；第二个 0.0.0.0 是允许访问 SubNet Mask；最后 md5 表示密码加密方式，如果将 md5 改成 trust，在指定范围的主机访问数据库时无需提供密码。

IP Address 和 SubNet Mask 项也可修改为您的机器 IP 地址 (譬如：10.13.19.53)和子网掩码 (譬如：255.255.255.255)，这样就只有您自己的主机才可远程访问数据库。要使用一个 IP 地址范围，只需要把 SubNet Mask 设置成合适的值。

若把 SubNet Mask 设成 0.0.0.0，则所有主机均可访问数据库 (IP Address 可任意设定)。若将 md5 改成 trust，可让指定范围的主机访问指定数据库时无需提供密码。

09、重启 PostgresSQL 9.3.5

1. root:# /etc/init.d/postgresql restart
   
2. * Restarting PostgreSQL 9.3 database server                                                                                                      [ OK ]
   
3. root:#

复制代码

10、登陆测试 PostgresSQL 9.3.5

1. root:# psql -U postgres -h 127.0.0.1
   
2. Password for user postgres:     #第 06 步设置的 postgres 数据库用户口令
   
3. psql (9.3.5)
   
4. SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
   
5. Type "help" for help.
   
6. 
   
7. postgres=# \q
   
8. could not save history to file "/root/.psql_history": 没有那个文件或目录    #重新登录就可以了，由于第一次文件是不存在的
   
9. root:# psql -U postgres -h 127.0.0.1
   
10. Password for user postgres:     #第 06 步设置的 postgres 数据库用户口令
    
11. psql (9.3.5)
    
12. SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
    
13. Type "help" for help.
    
14. 
    
15. postgres=# \q
    
16. root:#

复制代码

"长按二维码" 或 "扫一扫" 关注 "德云社区" 微信公众号

版权声明：
本文为独家原创稿件，版权归 德云社区，未经许可不得转载；否则，将追究其法律责任。