广州市德数机械设备有限公司 ── 专业研发、设计、生产贴标机/不干胶贴标机/包装机/包装生产线/定制非标设备

德云社区

 找回密码
 立即注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

带盖棕色半透明口服液圆形小玻璃瓶
折叠白色、黄色瓦楞纸箱
黑色塑料瓶盖化妆品透明玻璃瓶
黄色纸盒
矩形化妆品透明小玻璃瓶
红色液态生料带瓶
蓝色标签塑料软管
辉瑞伟哥包装小纸盒
查看: 6788|回复: 0

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

[复制链接]

189

主题

307

帖子

1233

积分

版主

Rank: 7Rank: 7Rank: 7

金钱
698
金币
14
威望
0
贡献
0
发表于 2015-1-19 03:01:24 | 显示全部楼层 |阅读模式
|          
在 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 可用许多方法扩展;譬如:通过增加新数据类型、函数、操作符、聚集函数、索引。


001.jpg

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. Creating config file /etc/logrotate.d/postgresql-common with new version
  60. Building PostgreSQL dictionaries from installed myspell/hunspell packages...
  61. Removing obsolete dictionary files:
  62. * No PostgreSQL clusters exist; see "man pg_createcluster"
  63. Processing triggers for ureadahead (0.100.0-16) ...
  64. 正在设置 postgresql-9.3 (9.3.5-0ubuntu0.14.04.1) ...
  65. Creating new cluster 9.3/main ...
  66.   config /etc/postgresql/9.3/main
  67.   data   /var/lib/postgresql/9.3/main
  68.   locale zh_CN.UTF-8
  69.   port   5432
  70. 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 自动模式
  71. * Starting PostgreSQL 9.3 database server                                                                                                        [ OK ]
  72. 正在设置 postgresql (9.3+154) ...
  73. Processing triggers for libc-bin (2.19-0ubuntu6.5) ...
  74. 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. 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. postgres=# ALTER USER postgres with PASSWORD 'postgres';
  5. ALTER ROLE
  6. postgres=# \q
  7. 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. # FILE LOCATIONS
  34. #------------------------------------------------------------------------------

  35. # The default values of these variables are driven from the -D command-line
  36. # option or PGDATA environment variable, represented here as ConfigDir.

  37. data_directory = '/var/lib/postgresql/9.3/main'        # use data in another directory
  38.                     # (change requires restart)
  39. hba_file = '/etc/postgresql/9.3/main/pg_hba.conf'    # host-based authentication file
  40.                     # (change requires restart)
  41. ident_file = '/etc/postgresql/9.3/main/pg_ident.conf'    # ident configuration file
  42.                     # (change requires restart)

  43. # If external_pid_file is not explicitly set, no extra PID file is written.
  44. external_pid_file = '/var/run/postgresql/9.3-main.pid'            # write an extra PID file
  45.                     # (change requires restart)


  46. #------------------------------------------------------------------------------
  47. # CONNECTIONS AND AUTHENTICATION
  48. #------------------------------------------------------------------------------

  49. # - Connection Settings -

  50. #listen_addresses = 'localhost'        # what IP address(es) to listen on;
  51.                     # comma-separated list of addresses;
  52.                     # defaults to 'localhost'; use '*' for all
  53.                     # (change requires restart)
  54. port = 5432                # (change requires restart)
  55. max_connections = 100            # (change requires restart)
  56. # Note:  Increasing max_connections costs ~400 bytes of shared memory per
  57. # connection slot, plus lock space (see max_locks_per_transaction).
  58. #superuser_reserved_connections = 3    # (change requires restart)
  59. unix_socket_directories = '/var/run/postgresql'    # comma-separated list of directories
  60.                     # (change requires restart)
  61. #unix_socket_group = ''            # (change requires restart)
  62. #unix_socket_permissions = 0777        # begin with 0 to use octal notation
  63.                     # (change requires restart)
  64. #bonjour = off                # advertise server via Bonjour
  65.                     # (change requires restart)
  66. #bonjour_name = ''            # defaults to the computer name
  67.                     # (change requires restart)

  68. # - Security and Authentication -

  69. #authentication_timeout = 1min        # 1s-600s
  70. ssl = true                # (change requires restart)
  71. #ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH'    # allowed SSL ciphers
  72.                     # (change requires restart)
  73. #ssl_renegotiation_limit = 512MB    # amount of data between renegotiations
  74. ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'        # (change requires restart)
  75. ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'        # (change requires restart)
  76. #ssl_ca_file = ''            # (change requires restart)
  77. #ssl_crl_file = ''            # (change requires restart)
  78. #password_encryption = on
  79. #db_user_namespace = off

  80. # Kerberos and GSSAPI
  81. #krb_server_keyfile = ''
  82. #krb_srvname = 'postgres'        # (Kerberos only)
  83. #krb_caseins_users = off

  84. # - TCP Keepalives -
  85. # see "man 7 tcp" for details

  86. #tcp_keepalives_idle = 0        # TCP_KEEPIDLE, in seconds;
  87.                     # 0 selects the system default
  88. #tcp_keepalives_interval = 0        # TCP_KEEPINTVL, in seconds;
  89.                     # 0 selects the system default
  90. #tcp_keepalives_count = 0        # TCP_KEEPCNT;
  91.                     # 0 selects the system default


  92. #------------------------------------------------------------------------------
  93. # RESOURCE USAGE (except WAL)
  94. #------------------------------------------------------------------------------

  95. # - Memory -

  96. shared_buffers = 128MB            # min 128kB
  97.                     # (change requires restart)
  98. #temp_buffers = 8MB            # min 800kB
  99. #max_prepared_transactions = 0        # zero disables the feature
  100.                     # (change requires restart)
  101. # Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
  102. # per transaction slot, plus lock space (see max_locks_per_transaction).
  103. # It is not advisable to set max_prepared_transactions nonzero unless you
  104. # actively intend to use prepared transactions.
  105. #work_mem = 1MB                # min 64kB
  106. #maintenance_work_mem = 16MB        # min 1MB
  107. #max_stack_depth = 2MB            # min 100kB

  108. # - Disk -

  109. #temp_file_limit = -1            # limits per-session temp file space
  110.                     # in kB, or -1 for no limit

  111. # - Kernel Resource Usage -

  112. #max_files_per_process = 1000        # min 25
  113.                     # (change requires restart)
  114. #shared_preload_libraries = ''        # (change requires restart)

  115. # - Cost-Based Vacuum Delay -

  116. #vacuum_cost_delay = 0            # 0-100 milliseconds
  117. #vacuum_cost_page_hit = 1        # 0-10000 credits
  118. #vacuum_cost_page_miss = 10        # 0-10000 credits
  119. #vacuum_cost_page_dirty = 20        # 0-10000 credits
  120. #vacuum_cost_limit = 200        # 1-10000 credits

  121. # - Background Writer -

  122. #bgwriter_delay = 200ms            # 10-10000ms between rounds
  123. #bgwriter_lru_maxpages = 100        # 0-1000 max buffers written/round
  124. #bgwriter_lru_multiplier = 2.0        # 0-10.0 multipler on buffers scanned/round

  125. # - Asynchronous Behavior -

  126. #effective_io_concurrency = 1        # 1-1000; 0 disables prefetching


  127. #------------------------------------------------------------------------------
  128. # WRITE AHEAD LOG
  129. #------------------------------------------------------------------------------

  130. # - Settings -

  131. #wal_level = minimal            # minimal, archive, or hot_standby
  132.                     # (change requires restart)
  133. #fsync = on                # turns forced synchronization on or off
  134. #synchronous_commit = on        # synchronization level;
  135.                     # off, local, remote_write, or on
  136. #wal_sync_method = fsync        # the default is the first option
  137.                     # supported by the operating system:
  138.                     #   open_datasync
  139.                     #   fdatasync (default on Linux)
  140.                     #   fsync
  141.                     #   fsync_writethrough
  142.                     #   open_sync
  143. #full_page_writes = on            # recover from partial page writes
  144. #wal_buffers = -1            # min 32kB, -1 sets based on shared_buffers
  145.                     # (change requires restart)
  146. #wal_writer_delay = 200ms        # 1-10000 milliseconds

  147. #commit_delay = 0            # range 0-100000, in microseconds
  148. #commit_siblings = 5            # range 1-1000

  149. # - Checkpoints -

  150. #checkpoint_segments = 3        # in logfile segments, min 1, 16MB each
  151. #checkpoint_timeout = 5min        # range 30s-1h
  152. #checkpoint_completion_target = 0.5    # checkpoint target duration, 0.0 - 1.0
  153. #checkpoint_warning = 30s        # 0 disables

  154. # - Archiving -

  155. #archive_mode = off        # allows archiving to be done
  156.                 # (change requires restart)
  157. #archive_command = ''        # command to use to archive a logfile segment
  158.                 # placeholders: %p = path of file to archive
  159.                 #               %f = file name only
  160.                 # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
  161. #archive_timeout = 0        # force a logfile segment switch after this
  162.                 # number of seconds; 0 disables


  163. #------------------------------------------------------------------------------
  164. # REPLICATION
  165. #------------------------------------------------------------------------------

  166. # - Sending Server(s) -

  167. # Set these on the master and on any standby that will send replication data.

  168. #max_wal_senders = 0        # max number of walsender processes
  169.                 # (change requires restart)
  170. #wal_keep_segments = 0        # in logfile segments, 16MB each; 0 disables
  171. #wal_sender_timeout = 60s    # in milliseconds; 0 disables

  172. # - Master Server -

  173. # These settings are ignored on a standby server.

  174. #synchronous_standby_names = ''    # standby servers that provide sync rep
  175.                 # comma-separated list of application_name
  176.                 # from standby(s); '*' = all
  177. #vacuum_defer_cleanup_age = 0    # number of xacts by which cleanup is delayed

  178. # - Standby Servers -

  179. # These settings are ignored on a master server.

  180. #hot_standby = off            # "on" allows queries during recovery
  181.                     # (change requires restart)
  182. #max_standby_archive_delay = 30s    # max delay before canceling queries
  183.                     # when reading WAL from archive;
  184.                     # -1 allows indefinite delay
  185. #max_standby_streaming_delay = 30s    # max delay before canceling queries
  186.                     # when reading streaming WAL;
  187.                     # -1 allows indefinite delay
  188. #wal_receiver_status_interval = 10s    # send replies at least this often
  189.                     # 0 disables
  190. #hot_standby_feedback = off        # send info from standby to prevent
  191.                     # query conflicts
  192. #wal_receiver_timeout = 60s        # time that receiver waits for
  193.                     # communication from master
  194.                     # in milliseconds; 0 disables


  195. #------------------------------------------------------------------------------
  196. # QUERY TUNING
  197. #------------------------------------------------------------------------------

  198. # - Planner Method Configuration -

  199. #enable_bitmapscan = on
  200. #enable_hashagg = on
  201. #enable_hashjoin = on
  202. #enable_indexscan = on
  203. #enable_indexonlyscan = on
  204. #enable_material = on
  205. #enable_mergejoin = on
  206. #enable_nestloop = on
  207. #enable_seqscan = on
  208. #enable_sort = on
  209. #enable_tidscan = on

  210. # - Planner Cost Constants -

  211. #seq_page_cost = 1.0            # measured on an arbitrary scale
  212. #random_page_cost = 4.0            # same scale as above
  213. #cpu_tuple_cost = 0.01            # same scale as above
  214. #cpu_index_tuple_cost = 0.005        # same scale as above
  215. #cpu_operator_cost = 0.0025        # same scale as above
  216. #effective_cache_size = 128MB

  217. # - Genetic Query Optimizer -

  218. #geqo = on
  219. #geqo_threshold = 12
  220. #geqo_effort = 5            # range 1-10
  221. #geqo_pool_size = 0            # selects default based on effort
  222. #geqo_generations = 0            # selects default based on effort
  223. #geqo_selection_bias = 2.0        # range 1.5-2.0
  224. #geqo_seed = 0.0            # range 0.0-1.0

  225. # - Other Planner Options -

  226. #default_statistics_target = 100    # range 1-10000
  227. #constraint_exclusion = partition    # on, off, or partition
  228. #cursor_tuple_fraction = 0.1        # range 0.0-1.0
  229. #from_collapse_limit = 8
  230. #join_collapse_limit = 8        # 1 disables collapsing of explicit
  231.                     # JOIN clauses


  232. #------------------------------------------------------------------------------
  233. # ERROR REPORTING AND LOGGING
  234. #------------------------------------------------------------------------------

  235. # - Where to Log -

  236. #log_destination = 'stderr'        # Valid values are combinations of
  237.                     # stderr, csvlog, syslog, and eventlog,
  238.                     # depending on platform.  csvlog
  239.                     # requires logging_collector to be on.

  240. # This is used when logging to stderr:
  241. #logging_collector = off        # Enable capturing of stderr and csvlog
  242.                     # into log files. Required to be on for
  243.                     # csvlogs.
  244.                     # (change requires restart)

  245. # These are only used if logging_collector is on:
  246. #log_directory = 'pg_log'        # directory where log files are written,
  247.                     # can be absolute or relative to PGDATA
  248. #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'    # log file name pattern,
  249.                     # can include strftime() escapes
  250. #log_file_mode = 0600            # creation mode for log files,
  251.                     # begin with 0 to use octal notation
  252. #log_truncate_on_rotation = off        # If on, an existing log file with the
  253.                     # same name as the new log file will be
  254.                     # truncated rather than appended to.
  255.                     # But such truncation only occurs on
  256.                     # time-driven rotation, not on restarts
  257.                     # or size-driven rotation.  Default is
  258.                     # off, meaning append to existing files
  259.                     # in all cases.
  260. #log_rotation_age = 1d            # Automatic rotation of logfiles will
  261.                     # happen after that time.  0 disables.
  262. #log_rotation_size = 10MB        # Automatic rotation of logfiles will
  263.                     # happen after that much log output.
  264.                     # 0 disables.

  265. # These are relevant when logging to syslog:
  266. #syslog_facility = 'LOCAL0'
  267. #syslog_ident = 'postgres'

  268. # This is only relevant when logging to eventlog (win32):
  269. #event_source = 'PostgreSQL'

  270. # - When to Log -

  271. #client_min_messages = notice        # values in order of decreasing detail:
  272.                     #   debug5
  273.                     #   debug4
  274.                     #   debug3
  275.                     #   debug2
  276.                     #   debug1
  277.                     #   log
  278.                     #   notice
  279.                     #   warning
  280.                     #   error

  281. #log_min_messages = warning        # values in order of decreasing detail:
  282.                     #   debug5
  283.                     #   debug4
  284.                     #   debug3
  285.                     #   debug2
  286.                     #   debug1
  287.                     #   info
  288.                     #   notice
  289.                     #   warning
  290.                     #   error
  291.                     #   log
  292.                     #   fatal
  293.                     #   panic

  294. #log_min_error_statement = error    # values in order of decreasing detail:
  295.                     #   debug5
  296.                     #   debug4
  297.                     #   debug3
  298.                     #   debug2
  299.                     #   debug1
  300.                     #   info
  301.                     #   notice
  302.                     #   warning
  303.                     #   error
  304.                     #   log
  305.                     #   fatal
  306.                     #   panic (effectively off)

  307. #log_min_duration_statement = -1    # -1 is disabled, 0 logs all statements
  308.                     # and their durations, > 0 logs only
  309.                     # statements running at least this number
  310.                     # of milliseconds


  311. # - What to Log -

  312. #debug_print_parse = off
  313. #debug_print_rewritten = off
  314. #debug_print_plan = off
  315. #debug_pretty_print = on
  316. #log_checkpoints = off
  317. #log_connections = off
  318. #log_disconnections = off
  319. #log_duration = off
  320. #log_error_verbosity = default        # terse, default, or verbose messages
  321. #log_hostname = off
  322. log_line_prefix = '%t '            # special values:
  323.                     #   %a = application name
  324.                     #   %u = user name
  325.                     #   %d = database name
  326.                     #   %r = remote host and port
  327.                     #   %h = remote host
  328.                     #   %p = process ID
  329.                     #   %t = timestamp without milliseconds
  330.                     #   %m = timestamp with milliseconds
  331.                     #   %i = command tag
  332.                     #   %e = SQL state
  333.                     #   %c = session ID
  334.                     #   %l = session line number
  335.                     #   %s = session start timestamp
  336.                     #   %v = virtual transaction ID
  337.                     #   %x = transaction ID (0 if none)
  338.                     #   %q = stop here in non-session
  339.                     #        processes
  340.                     #   %% = '%'
  341.                     # e.g. '<%u%%%d> '
  342. #log_lock_waits = off            # log lock waits >= deadlock_timeout
  343. #log_statement = 'none'            # none, ddl, mod, all
  344. #log_temp_files = -1            # log temporary files equal or larger
  345.                     # than the specified size in kilobytes;
  346.                     # -1 disables, 0 logs all temp files
  347. log_timezone = 'PRC'


  348. #------------------------------------------------------------------------------
  349. # RUNTIME STATISTICS
  350. #------------------------------------------------------------------------------

  351. # - Query/Index Statistics Collector -

  352. #track_activities = on
  353. #track_counts = on
  354. #track_io_timing = off
  355. #track_functions = none            # none, pl, all
  356. #track_activity_query_size = 1024    # (change requires restart)
  357. #update_process_title = on
  358. #stats_temp_directory = 'pg_stat_tmp'


  359. # - Statistics Monitoring -

  360. #log_parser_stats = off
  361. #log_planner_stats = off
  362. #log_executor_stats = off
  363. #log_statement_stats = off


  364. #------------------------------------------------------------------------------
  365. # AUTOVACUUM PARAMETERS
  366. #------------------------------------------------------------------------------

  367. #autovacuum = on            # Enable autovacuum subprocess?  'on'
  368.                     # requires track_counts to also be on.
  369. #log_autovacuum_min_duration = -1    # -1 disables, 0 logs all actions and
  370.                     # their durations, > 0 logs only
  371.                     # actions running at least this number
  372.                     # of milliseconds.
  373. #autovacuum_max_workers = 3        # max number of autovacuum subprocesses
  374.                     # (change requires restart)
  375. #autovacuum_naptime = 1min        # time between autovacuum runs
  376. #autovacuum_vacuum_threshold = 50    # min number of row updates before
  377.                     # vacuum
  378. #autovacuum_analyze_threshold = 50    # min number of row updates before
  379.                     # analyze
  380. #autovacuum_vacuum_scale_factor = 0.2    # fraction of table size before vacuum
  381. #autovacuum_analyze_scale_factor = 0.1    # fraction of table size before analyze
  382. #autovacuum_freeze_max_age = 200000000    # maximum XID age before forced vacuum
  383.                     # (change requires restart)
  384. #autovacuum_multixact_freeze_max_age = 400000000    # maximum Multixact age
  385.                     # before forced vacuum
  386.                     # (change requires restart)
  387. #autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
  388.                     # autovacuum, in milliseconds;
  389.                     # -1 means use vacuum_cost_delay
  390. #autovacuum_vacuum_cost_limit = -1    # default vacuum cost limit for
  391.                     # autovacuum, -1 means use
  392.                     # vacuum_cost_limit


  393. #------------------------------------------------------------------------------
  394. # CLIENT CONNECTION DEFAULTS
  395. #------------------------------------------------------------------------------

  396. # - Statement Behavior -

  397. #search_path = '"$user",public'        # schema names
  398. #default_tablespace = ''        # a tablespace name, '' uses the default
  399. #temp_tablespaces = ''            # a list of tablespace names, '' uses
  400.                     # only default tablespace
  401. #check_function_bodies = on
  402. #default_transaction_isolation = 'read committed'
  403. #default_transaction_read_only = off
  404. #default_transaction_deferrable = off
  405. #session_replication_role = 'origin'
  406. #statement_timeout = 0            # in milliseconds, 0 is disabled
  407. #lock_timeout = 0            # in milliseconds, 0 is disabled
  408. #vacuum_freeze_min_age = 50000000
  409. #vacuum_freeze_table_age = 150000000
  410. #vacuum_multixact_freeze_min_age = 5000000
  411. #vacuum_multixact_freeze_table_age = 150000000
  412. #bytea_output = 'hex'            # hex, escape
  413. #xmlbinary = 'base64'
  414. #xmloption = 'content'

  415. # - Locale and Formatting -

  416. datestyle = 'iso, ymd'
  417. #intervalstyle = 'postgres'
  418. timezone = 'PRC'
  419. #timezone_abbreviations = 'Default'     # Select the set of available time zone
  420.                     # abbreviations.  Currently, there are
  421.                     #   Default
  422.                     #   Australia
  423.                     #   India
  424.                     # You can create your own file in
  425.                     # share/timezonesets/.
  426. #extra_float_digits = 0            # min -15, max 3
  427. #client_encoding = sql_ascii        # actually, defaults to database
  428.                     # encoding

  429. # These settings are initialized by initdb, but they can be changed.
  430. lc_messages = 'zh_CN.UTF-8'            # locale for system error message
  431.                     # strings
  432. lc_monetary = 'zh_CN'            # locale for monetary formatting
  433. lc_numeric = 'zh_CN'            # locale for number formatting
  434. lc_time = 'zh_CN'                # locale for time formatting

  435. # default configuration for text search
  436. default_text_search_config = 'pg_catalog.simple'

  437. # - Other Defaults -

  438. #dynamic_library_path = '$libdir'
  439. #local_preload_libraries = ''


  440. #------------------------------------------------------------------------------
  441. # LOCK MANAGEMENT
  442. #------------------------------------------------------------------------------

  443. #deadlock_timeout = 1s
  444. #max_locks_per_transaction = 64        # min 10
  445.                     # (change requires restart)
  446. # Note:  Each lock table slot uses ~270 bytes of shared memory, and there are
  447. # max_locks_per_transaction * (max_connections + max_prepared_transactions)
  448. # lock table slots.
  449. #max_pred_locks_per_transaction = 64    # min 10
  450.                     # (change requires restart)


  451. #------------------------------------------------------------------------------
  452. # VERSION/PLATFORM COMPATIBILITY
  453. #------------------------------------------------------------------------------

  454. # - Previous PostgreSQL Versions -

  455. #array_nulls = on
  456. #backslash_quote = safe_encoding    # on, off, or safe_encoding
  457. #default_with_oids = off
  458. #escape_string_warning = on
  459. #lo_compat_privileges = off
  460. #quote_all_identifiers = off
  461. #sql_inheritance = on
  462. #standard_conforming_strings = on
  463. #synchronize_seqscans = on

  464. # - Other Platforms and Clients -

  465. #transform_null_equals = off


  466. #------------------------------------------------------------------------------
  467. # ERROR HANDLING
  468. #------------------------------------------------------------------------------

  469. #exit_on_error = off            # terminate session on any error?
  470. #restart_after_crash = on        # reinitialize after backend crash?


  471. #------------------------------------------------------------------------------
  472. # CONFIG FILE INCLUDES
  473. #------------------------------------------------------------------------------

  474. # These options allow settings to be loaded from files other than the
  475. # default postgresql.conf.

  476. #include_dir = 'conf.d'            # include files ending in '.conf' from
  477.                     # directory 'conf.d'
  478. #include_if_exists = 'exists.conf'    # include file only if it exists
  479. #include = 'special.conf'        # include file


  480. #------------------------------------------------------------------------------
  481. # CUSTOMIZED OPTIONS
  482. #------------------------------------------------------------------------------

  483. # 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. # FILE LOCATIONS
  34. #------------------------------------------------------------------------------

  35. # The default values of these variables are driven from the -D command-line
  36. # option or PGDATA environment variable, represented here as ConfigDir.

  37. data_directory = '/var/lib/postgresql/9.3/main'        # use data in another directory
  38.                     # (change requires restart)
  39. hba_file = '/etc/postgresql/9.3/main/pg_hba.conf'    # host-based authentication file
  40.                     # (change requires restart)
  41. ident_file = '/etc/postgresql/9.3/main/pg_ident.conf'    # ident configuration file
  42.                     # (change requires restart)

  43. # If external_pid_file is not explicitly set, no extra PID file is written.
  44. external_pid_file = '/var/run/postgresql/9.3-main.pid'            # write an extra PID file
  45.                     # (change requires restart)


  46. #------------------------------------------------------------------------------
  47. # CONNECTIONS AND AUTHENTICATION
  48. #------------------------------------------------------------------------------

  49. # - Connection Settings -

  50. listen_addresses = '*'        # what IP address(es) to listen on;    #监听任何地址
  51.                     # comma-separated list of addresses;
  52.                     # defaults to 'localhost'; use '*' for all
  53.                     # (change requires restart)
  54. port = 5432                # (change requires restart)
  55. max_connections = 100            # (change requires restart)
  56. # Note:  Increasing max_connections costs ~400 bytes of shared memory per
  57. # connection slot, plus lock space (see max_locks_per_transaction).
  58. #superuser_reserved_connections = 3    # (change requires restart)
  59. unix_socket_directories = '/var/run/postgresql'    # comma-separated list of directories
  60.                     # (change requires restart)
  61. #unix_socket_group = ''            # (change requires restart)
  62. #unix_socket_permissions = 0777        # begin with 0 to use octal notation
  63.                     # (change requires restart)
  64. #bonjour = off                # advertise server via Bonjour
  65.                     # (change requires restart)
  66. #bonjour_name = ''            # defaults to the computer name
  67.                     # (change requires restart)

  68. # - Security and Authentication -

  69. #authentication_timeout = 1min        # 1s-600s
  70. ssl = true                # (change requires restart)
  71. #ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH'    # allowed SSL ciphers
  72.                     # (change requires restart)
  73. #ssl_renegotiation_limit = 512MB    # amount of data between renegotiations
  74. ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'        # (change requires restart)
  75. ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'        # (change requires restart)
  76. #ssl_ca_file = ''            # (change requires restart)
  77. #ssl_crl_file = ''            # (change requires restart)
  78. password_encryption = on    #启用口令加密
  79. #db_user_namespace = off

  80. # Kerberos and GSSAPI
  81. #krb_server_keyfile = ''
  82. #krb_srvname = 'postgres'        # (Kerberos only)
  83. #krb_caseins_users = off

  84. # - TCP Keepalives -
  85. # see "man 7 tcp" for details

  86. #tcp_keepalives_idle = 0        # TCP_KEEPIDLE, in seconds;
  87.                     # 0 selects the system default
  88. #tcp_keepalives_interval = 0        # TCP_KEEPINTVL, in seconds;
  89.                     # 0 selects the system default
  90. #tcp_keepalives_count = 0        # TCP_KEEPCNT;
  91.                     # 0 selects the system default


  92. #------------------------------------------------------------------------------
  93. # RESOURCE USAGE (except WAL)
  94. #------------------------------------------------------------------------------

  95. # - Memory -

  96. shared_buffers = 128MB            # min 128kB
  97.                     # (change requires restart)
  98. #temp_buffers = 8MB            # min 800kB
  99. #max_prepared_transactions = 0        # zero disables the feature
  100.                     # (change requires restart)
  101. # Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
  102. # per transaction slot, plus lock space (see max_locks_per_transaction).
  103. # It is not advisable to set max_prepared_transactions nonzero unless you
  104. # actively intend to use prepared transactions.
  105. #work_mem = 1MB                # min 64kB
  106. #maintenance_work_mem = 16MB        # min 1MB
  107. #max_stack_depth = 2MB            # min 100kB

  108. # - Disk -

  109. #temp_file_limit = -1            # limits per-session temp file space
  110.                     # in kB, or -1 for no limit

  111. # - Kernel Resource Usage -

  112. #max_files_per_process = 1000        # min 25
  113.                     # (change requires restart)
  114. #shared_preload_libraries = ''        # (change requires restart)

  115. # - Cost-Based Vacuum Delay -

  116. #vacuum_cost_delay = 0            # 0-100 milliseconds
  117. #vacuum_cost_page_hit = 1        # 0-10000 credits
  118. #vacuum_cost_page_miss = 10        # 0-10000 credits
  119. #vacuum_cost_page_dirty = 20        # 0-10000 credits
  120. #vacuum_cost_limit = 200        # 1-10000 credits

  121. # - Background Writer -

  122. #bgwriter_delay = 200ms            # 10-10000ms between rounds
  123. #bgwriter_lru_maxpages = 100        # 0-1000 max buffers written/round
  124. #bgwriter_lru_multiplier = 2.0        # 0-10.0 multipler on buffers scanned/round

  125. # - Asynchronous Behavior -

  126. #effective_io_concurrency = 1        # 1-1000; 0 disables prefetching


  127. #------------------------------------------------------------------------------
  128. # WRITE AHEAD LOG
  129. #------------------------------------------------------------------------------

  130. # - Settings -

  131. #wal_level = minimal            # minimal, archive, or hot_standby
  132.                     # (change requires restart)
  133. #fsync = on                # turns forced synchronization on or off
  134. #synchronous_commit = on        # synchronization level;
  135.                     # off, local, remote_write, or on
  136. #wal_sync_method = fsync        # the default is the first option
  137.                     # supported by the operating system:
  138.                     #   open_datasync
  139.                     #   fdatasync (default on Linux)
  140.                     #   fsync
  141.                     #   fsync_writethrough
  142.                     #   open_sync
  143. #full_page_writes = on            # recover from partial page writes
  144. #wal_buffers = -1            # min 32kB, -1 sets based on shared_buffers
  145.                     # (change requires restart)
  146. #wal_writer_delay = 200ms        # 1-10000 milliseconds

  147. #commit_delay = 0            # range 0-100000, in microseconds
  148. #commit_siblings = 5            # range 1-1000

  149. # - Checkpoints -

  150. #checkpoint_segments = 3        # in logfile segments, min 1, 16MB each
  151. #checkpoint_timeout = 5min        # range 30s-1h
  152. #checkpoint_completion_target = 0.5    # checkpoint target duration, 0.0 - 1.0
  153. #checkpoint_warning = 30s        # 0 disables

  154. # - Archiving -

  155. #archive_mode = off        # allows archiving to be done
  156.                 # (change requires restart)
  157. #archive_command = ''        # command to use to archive a logfile segment
  158.                 # placeholders: %p = path of file to archive
  159.                 #               %f = file name only
  160.                 # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
  161. #archive_timeout = 0        # force a logfile segment switch after this
  162.                 # number of seconds; 0 disables


  163. #------------------------------------------------------------------------------
  164. # REPLICATION
  165. #------------------------------------------------------------------------------

  166. # - Sending Server(s) -

  167. # Set these on the master and on any standby that will send replication data.

  168. #max_wal_senders = 0        # max number of walsender processes
  169.                 # (change requires restart)
  170. #wal_keep_segments = 0        # in logfile segments, 16MB each; 0 disables
  171. #wal_sender_timeout = 60s    # in milliseconds; 0 disables

  172. # - Master Server -

  173. # These settings are ignored on a standby server.

  174. #synchronous_standby_names = ''    # standby servers that provide sync rep
  175.                 # comma-separated list of application_name
  176.                 # from standby(s); '*' = all
  177. #vacuum_defer_cleanup_age = 0    # number of xacts by which cleanup is delayed

  178. # - Standby Servers -

  179. # These settings are ignored on a master server.

  180. #hot_standby = off            # "on" allows queries during recovery
  181.                     # (change requires restart)
  182. #max_standby_archive_delay = 30s    # max delay before canceling queries
  183.                     # when reading WAL from archive;
  184.                     # -1 allows indefinite delay
  185. #max_standby_streaming_delay = 30s    # max delay before canceling queries
  186.                     # when reading streaming WAL;
  187.                     # -1 allows indefinite delay
  188. #wal_receiver_status_interval = 10s    # send replies at least this often
  189.                     # 0 disables
  190. #hot_standby_feedback = off        # send info from standby to prevent
  191.                     # query conflicts
  192. #wal_receiver_timeout = 60s        # time that receiver waits for
  193.                     # communication from master
  194.                     # in milliseconds; 0 disables


  195. #------------------------------------------------------------------------------
  196. # QUERY TUNING
  197. #------------------------------------------------------------------------------

  198. # - Planner Method Configuration -

  199. #enable_bitmapscan = on
  200. #enable_hashagg = on
  201. #enable_hashjoin = on
  202. #enable_indexscan = on
  203. #enable_indexonlyscan = on
  204. #enable_material = on
  205. #enable_mergejoin = on
  206. #enable_nestloop = on
  207. #enable_seqscan = on
  208. #enable_sort = on
  209. #enable_tidscan = on

  210. # - Planner Cost Constants -

  211. #seq_page_cost = 1.0            # measured on an arbitrary scale
  212. #random_page_cost = 4.0            # same scale as above
  213. #cpu_tuple_cost = 0.01            # same scale as above
  214. #cpu_index_tuple_cost = 0.005        # same scale as above
  215. #cpu_operator_cost = 0.0025        # same scale as above
  216. #effective_cache_size = 128MB

  217. # - Genetic Query Optimizer -

  218. #geqo = on
  219. #geqo_threshold = 12
  220. #geqo_effort = 5            # range 1-10
  221. #geqo_pool_size = 0            # selects default based on effort
  222. #geqo_generations = 0            # selects default based on effort
  223. #geqo_selection_bias = 2.0        # range 1.5-2.0
  224. #geqo_seed = 0.0            # range 0.0-1.0

  225. # - Other Planner Options -

  226. #default_statistics_target = 100    # range 1-10000
  227. #constraint_exclusion = partition    # on, off, or partition
  228. #cursor_tuple_fraction = 0.1        # range 0.0-1.0
  229. #from_collapse_limit = 8
  230. #join_collapse_limit = 8        # 1 disables collapsing of explicit
  231.                     # JOIN clauses


  232. #------------------------------------------------------------------------------
  233. # ERROR REPORTING AND LOGGING
  234. #------------------------------------------------------------------------------

  235. # - Where to Log -

  236. #log_destination = 'stderr'        # Valid values are combinations of
  237.                     # stderr, csvlog, syslog, and eventlog,
  238.                     # depending on platform.  csvlog
  239.                     # requires logging_collector to be on.

  240. # This is used when logging to stderr:
  241. #logging_collector = off        # Enable capturing of stderr and csvlog
  242.                     # into log files. Required to be on for
  243.                     # csvlogs.
  244.                     # (change requires restart)

  245. # These are only used if logging_collector is on:
  246. #log_directory = 'pg_log'        # directory where log files are written,
  247.                     # can be absolute or relative to PGDATA
  248. #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'    # log file name pattern,
  249.                     # can include strftime() escapes
  250. #log_file_mode = 0600            # creation mode for log files,
  251.                     # begin with 0 to use octal notation
  252. #log_truncate_on_rotation = off        # If on, an existing log file with the
  253.                     # same name as the new log file will be
  254.                     # truncated rather than appended to.
  255.                     # But such truncation only occurs on
  256.                     # time-driven rotation, not on restarts
  257.                     # or size-driven rotation.  Default is
  258.                     # off, meaning append to existing files
  259.                     # in all cases.
  260. #log_rotation_age = 1d            # Automatic rotation of logfiles will
  261.                     # happen after that time.  0 disables.
  262. #log_rotation_size = 10MB        # Automatic rotation of logfiles will
  263.                     # happen after that much log output.
  264.                     # 0 disables.

  265. # These are relevant when logging to syslog:
  266. #syslog_facility = 'LOCAL0'
  267. #syslog_ident = 'postgres'

  268. # This is only relevant when logging to eventlog (win32):
  269. #event_source = 'PostgreSQL'

  270. # - When to Log -

  271. #client_min_messages = notice        # values in order of decreasing detail:
  272.                     #   debug5
  273.                     #   debug4
  274.                     #   debug3
  275.                     #   debug2
  276.                     #   debug1
  277.                     #   log
  278.                     #   notice
  279.                     #   warning
  280.                     #   error

  281. #log_min_messages = warning        # values in order of decreasing detail:
  282.                     #   debug5
  283.                     #   debug4
  284.                     #   debug3
  285.                     #   debug2
  286.                     #   debug1
  287.                     #   info
  288.                     #   notice
  289.                     #   warning
  290.                     #   error
  291.                     #   log
  292.                     #   fatal
  293.                     #   panic

  294. #log_min_error_statement = error    # values in order of decreasing detail:
  295.                     #   debug5
  296.                     #   debug4
  297.                     #   debug3
  298.                     #   debug2
  299.                     #   debug1
  300.                     #   info
  301.                     #   notice
  302.                     #   warning
  303.                     #   error
  304.                     #   log
  305.                     #   fatal
  306.                     #   panic (effectively off)

  307. #log_min_duration_statement = -1    # -1 is disabled, 0 logs all statements
  308.                     # and their durations, > 0 logs only
  309.                     # statements running at least this number
  310.                     # of milliseconds


  311. # - What to Log -

  312. #debug_print_parse = off
  313. #debug_print_rewritten = off
  314. #debug_print_plan = off
  315. #debug_pretty_print = on
  316. #log_checkpoints = off
  317. #log_connections = off
  318. #log_disconnections = off
  319. #log_duration = off
  320. #log_error_verbosity = default        # terse, default, or verbose messages
  321. #log_hostname = off
  322. log_line_prefix = '%t '            # special values:
  323.                     #   %a = application name
  324.                     #   %u = user name
  325.                     #   %d = database name
  326.                     #   %r = remote host and port
  327.                     #   %h = remote host
  328.                     #   %p = process ID
  329.                     #   %t = timestamp without milliseconds
  330.                     #   %m = timestamp with milliseconds
  331.                     #   %i = command tag
  332.                     #   %e = SQL state
  333.                     #   %c = session ID
  334.                     #   %l = session line number
  335.                     #   %s = session start timestamp
  336.                     #   %v = virtual transaction ID
  337.                     #   %x = transaction ID (0 if none)
  338.                     #   %q = stop here in non-session
  339.                     #        processes
  340.                     #   %% = '%'
  341.                     # e.g. '<%u%%%d> '
  342. #log_lock_waits = off            # log lock waits >= deadlock_timeout
  343. #log_statement = 'none'            # none, ddl, mod, all
  344. #log_temp_files = -1            # log temporary files equal or larger
  345.                     # than the specified size in kilobytes;
  346.                     # -1 disables, 0 logs all temp files
  347. log_timezone = 'PRC'


  348. #------------------------------------------------------------------------------
  349. # RUNTIME STATISTICS
  350. #------------------------------------------------------------------------------

  351. # - Query/Index Statistics Collector -

  352. #track_activities = on
  353. #track_counts = on
  354. #track_io_timing = off
  355. #track_functions = none            # none, pl, all
  356. #track_activity_query_size = 1024    # (change requires restart)
  357. #update_process_title = on
  358. #stats_temp_directory = 'pg_stat_tmp'


  359. # - Statistics Monitoring -

  360. #log_parser_stats = off
  361. #log_planner_stats = off
  362. #log_executor_stats = off
  363. #log_statement_stats = off


  364. #------------------------------------------------------------------------------
  365. # AUTOVACUUM PARAMETERS
  366. #------------------------------------------------------------------------------

  367. #autovacuum = on            # Enable autovacuum subprocess?  'on'
  368.                     # requires track_counts to also be on.
  369. #log_autovacuum_min_duration = -1    # -1 disables, 0 logs all actions and
  370.                     # their durations, > 0 logs only
  371.                     # actions running at least this number
  372.                     # of milliseconds.
  373. #autovacuum_max_workers = 3        # max number of autovacuum subprocesses
  374.                     # (change requires restart)
  375. #autovacuum_naptime = 1min        # time between autovacuum runs
  376. #autovacuum_vacuum_threshold = 50    # min number of row updates before
  377.                     # vacuum
  378. #autovacuum_analyze_threshold = 50    # min number of row updates before
  379.                     # analyze
  380. #autovacuum_vacuum_scale_factor = 0.2    # fraction of table size before vacuum
  381. #autovacuum_analyze_scale_factor = 0.1    # fraction of table size before analyze
  382. #autovacuum_freeze_max_age = 200000000    # maximum XID age before forced vacuum
  383.                     # (change requires restart)
  384. #autovacuum_multixact_freeze_max_age = 400000000    # maximum Multixact age
  385.                     # before forced vacuum
  386.                     # (change requires restart)
  387. #autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
  388.                     # autovacuum, in milliseconds;
  389.                     # -1 means use vacuum_cost_delay
  390. #autovacuum_vacuum_cost_limit = -1    # default vacuum cost limit for
  391.                     # autovacuum, -1 means use
  392.                     # vacuum_cost_limit


  393. #------------------------------------------------------------------------------
  394. # CLIENT CONNECTION DEFAULTS
  395. #------------------------------------------------------------------------------

  396. # - Statement Behavior -

  397. #search_path = '"$user",public'        # schema names
  398. #default_tablespace = ''        # a tablespace name, '' uses the default
  399. #temp_tablespaces = ''            # a list of tablespace names, '' uses
  400.                     # only default tablespace
  401. #check_function_bodies = on
  402. #default_transaction_isolation = 'read committed'
  403. #default_transaction_read_only = off
  404. #default_transaction_deferrable = off
  405. #session_replication_role = 'origin'
  406. #statement_timeout = 0            # in milliseconds, 0 is disabled
  407. #lock_timeout = 0            # in milliseconds, 0 is disabled
  408. #vacuum_freeze_min_age = 50000000
  409. #vacuum_freeze_table_age = 150000000
  410. #vacuum_multixact_freeze_min_age = 5000000
  411. #vacuum_multixact_freeze_table_age = 150000000
  412. #bytea_output = 'hex'            # hex, escape
  413. #xmlbinary = 'base64'
  414. #xmloption = 'content'

  415. # - Locale and Formatting -

  416. datestyle = 'iso, ymd'
  417. #intervalstyle = 'postgres'
  418. timezone = 'PRC'
  419. #timezone_abbreviations = 'Default'     # Select the set of available time zone
  420.                     # abbreviations.  Currently, there are
  421.                     #   Default
  422.                     #   Australia
  423.                     #   India
  424.                     # You can create your own file in
  425.                     # share/timezonesets/.
  426. #extra_float_digits = 0            # min -15, max 3
  427. #client_encoding = sql_ascii        # actually, defaults to database
  428.                     # encoding

  429. # These settings are initialized by initdb, but they can be changed.
  430. lc_messages = 'zh_CN.UTF-8'            # locale for system error message
  431.                     # strings
  432. lc_monetary = 'zh_CN'            # locale for monetary formatting
  433. lc_numeric = 'zh_CN'            # locale for number formatting
  434. lc_time = 'zh_CN'                # locale for time formatting

  435. # default configuration for text search
  436. default_text_search_config = 'pg_catalog.simple'

  437. # - Other Defaults -

  438. #dynamic_library_path = '$libdir'
  439. #local_preload_libraries = ''


  440. #------------------------------------------------------------------------------
  441. # LOCK MANAGEMENT
  442. #------------------------------------------------------------------------------

  443. #deadlock_timeout = 1s
  444. #max_locks_per_transaction = 64        # min 10
  445.                     # (change requires restart)
  446. # Note:  Each lock table slot uses ~270 bytes of shared memory, and there are
  447. # max_locks_per_transaction * (max_connections + max_prepared_transactions)
  448. # lock table slots.
  449. #max_pred_locks_per_transaction = 64    # min 10
  450.                     # (change requires restart)


  451. #------------------------------------------------------------------------------
  452. # VERSION/PLATFORM COMPATIBILITY
  453. #------------------------------------------------------------------------------

  454. # - Previous PostgreSQL Versions -

  455. #array_nulls = on
  456. #backslash_quote = safe_encoding    # on, off, or safe_encoding
  457. #default_with_oids = off
  458. #escape_string_warning = on
  459. #lo_compat_privileges = off
  460. #quote_all_identifiers = off
  461. #sql_inheritance = on
  462. #standard_conforming_strings = on
  463. #synchronize_seqscans = on

  464. # - Other Platforms and Clients -

  465. #transform_null_equals = off


  466. #------------------------------------------------------------------------------
  467. # ERROR HANDLING
  468. #------------------------------------------------------------------------------

  469. #exit_on_error = off            # terminate session on any error?
  470. #restart_after_crash = on        # reinitialize after backend crash?


  471. #------------------------------------------------------------------------------
  472. # CONFIG FILE INCLUDES
  473. #------------------------------------------------------------------------------

  474. # These options allow settings to be loaded from files other than the
  475. # default postgresql.conf.

  476. #include_dir = 'conf.d'            # include files ending in '.conf' from
  477.                     # directory 'conf.d'
  478. #include_if_exists = 'exists.conf'    # include file only if it exists
  479. #include = 'special.conf'        # include file


  480. #------------------------------------------------------------------------------
  481. # CUSTOMIZED OPTIONS
  482. #------------------------------------------------------------------------------

  483. # 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. # Put your actual configuration here
  67. # ----------------------------------
  68. #
  69. # If you want to allow non-local connections, you need to add more
  70. # "host" records.  In that case you will also need to make PostgreSQL
  71. # listen on a non-local interface via the listen_addresses
  72. # configuration parameter, or via the -i or -h command line switches.




  73. # DO NOT DISABLE!
  74. # If you change this first entry you will need to make sure that the
  75. # database superuser can access the database using some other method.
  76. # Noninteractive access to all databases is required during automatic
  77. # maintenance (custom daily cronjobs, replication, and similar tasks).
  78. #
  79. # Database administrative login by Unix domain socket
  80. local   all             postgres                                peer

  81. # TYPE  DATABASE        USER            ADDRESS                 METHOD

  82. # "local" is for Unix domain socket connections only
  83. local   all             all                                     peer
  84. # IPv4 local connections:
  85. host    all             all             127.0.0.1/32            md5
  86. # IPv6 local connections:
  87. host    all             all             ::1/128                 md5
  88. # Allow replication connections from localhost, by a user with the
  89. # replication privilege.
  90. #local   replication     postgres                                peer
  91. #host    replication     postgres        127.0.0.1/32            md5
  92. #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. # Put your actual configuration here
  67. # ----------------------------------
  68. #
  69. # If you want to allow non-local connections, you need to add more
  70. # "host" records.  In that case you will also need to make PostgreSQL
  71. # listen on a non-local interface via the listen_addresses
  72. # configuration parameter, or via the -i or -h command line switches.




  73. # DO NOT DISABLE!
  74. # If you change this first entry you will need to make sure that the
  75. # database superuser can access the database using some other method.
  76. # Noninteractive access to all databases is required during automatic
  77. # maintenance (custom daily cronjobs, replication, and similar tasks).
  78. #
  79. # Database administrative login by Unix domain socket
  80. local   all             postgres                                peer

  81. # TYPE  DATABASE        USER            ADDRESS                 METHOD

  82. # "local" is for Unix domain socket connections only
  83. local   all             all                                     peer
  84. # IPv4 local connections:
  85. host    all             all             127.0.0.1/32            md5
  86. # IPv6 local connections:
  87. host    all             all             ::1/128                 md5
  88. # Allow replication connections from localhost, by a user with the
  89. # replication privilege.
  90. #local   replication     postgres                                peer
  91. #host    replication     postgres        127.0.0.1/32            md5
  92. #host    replication     postgres        ::1/128                 md5
  93. # to allow your client visiting postgresql server
  94. 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. postgres=# \q
  7. could not save history to file "/root/.psql_history": 没有那个文件或目录    #重新登录就可以了,由于第一次文件是不存在的
  8. root:# psql -U postgres -h 127.0.0.1
  9. Password for user postgres:     #第 06 步设置的 postgres 数据库用户口令
  10. psql (9.3.5)
  11. SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
  12. Type "help" for help.

  13. postgres=# \q
  14. root:#
复制代码


关注 "德云社区" 微信公众号,了解相关资讯

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

广州市德数机械设备有限公司 ── 专业研发、设计、生产贴标机/不干胶贴标机/包装机/包装生产线/定制非标设备
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|Sitemap|手机版|小黑屋|德云社区    

GMT+8, 2017-9-19 21:38 , Processed in 0.065141 second(s), 18 queries , Apc On.

版权所有 © Guangzhou Digits Cloud Technology Co., Ltd.

工业和信息化部:粤ICP备14079481号-2

快速回复 返回顶部 返回列表