API tools faq
paste
Advertisement
Guest User

postgresql.conf

a guest
Jul 24th, 2014
297
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 27.20 KB | None | 0 0
raw download clone embed print report
  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 = 'ConfigDir' # use data in another directory
  42. # (change requires restart)
  43. hba_file = '9.3/data/conf.d/pg_hba.conf' # host-based authentication file
  44. # (change requires restart)
  45. #ident_file = 'ConfigDir/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 = '' # 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 = 4000 # (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 = '/tmp' # 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 = off # (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 = 'server.crt' # (change requires restart)
  86. #ssl_key_file = 'server.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 = 4GB # min 128kB
  115. # (change requires restart)
  116. temp_buffers = 64MB # 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 = 64MB # min 64kB
  124. maintenance_work_mem = 1024MB # 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 = 'pg_stat_statements' #,pg_statsinfo' # (change requires restart)
  137.  
  138. pg_stat_statements.max = 10000
  139. pg_stat_statements.track = all
  140.  
  141.  
  142. # - Cost-Based Vacuum Delay -
  143.  
  144. vacuum_cost_delay = 0 # 0-100 milliseconds
  145. #vacuum_cost_page_hit = 1 # 0-10000 credits
  146. #vacuum_cost_page_miss = 10 # 0-10000 credits
  147. #vacuum_cost_page_dirty = 20 # 0-10000 credits
  148. vacuum_cost_limit = 1000 # 1-10000 credits
  149.  
  150. # - Background Writer -
  151.  
  152. #bgwriter_delay = 200ms # 10-10000ms between rounds
  153. #bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
  154. #bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round
  155.  
  156. # - Asynchronous Behavior -
  157.  
  158. #effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
  159.  
  160.  
  161. #------------------------------------------------------------------------------
  162. # WRITE AHEAD LOG
  163. #------------------------------------------------------------------------------
  164.  
  165. # - Settings -
  166.  
  167. wal_level = hot_standby # minimal, archive, or hot_standby
  168. # (change requires restart)
  169. fsync = on # turns forced synchronization on or off
  170. synchronous_commit = on # synchronization level;
  171. # off, local, remote_write, or on
  172. #wal_sync_method = fsync # the default is the first option
  173. # supported by the operating system:
  174. # open_datasync
  175. # fdatasync (default on Linux)
  176. # fsync
  177. # fsync_writethrough
  178. # open_sync
  179. full_page_writes = on # recover from partial page writes
  180. #wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
  181. # (change requires restart)
  182. #wal_writer_delay = 200ms # 1-10000 milliseconds
  183.  
  184. #commit_delay = 0 # range 0-100000, in microseconds
  185. #commit_siblings = 5 # range 1-1000
  186.  
  187. # - Checkpoints -
  188.  
  189. checkpoint_segments = 100500 # in logfile segments, min 1, 16MB each
  190. checkpoint_timeout = 1h # range 30s-1h
  191. checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
  192. #checkpoint_warning = 30s # 0 disables
  193.  
  194. # - Archiving -
  195.  
  196. archive_mode = off # allows archiving to be done
  197. # (change requires restart)
  198. archive_command = 'test ! -f /var/lib/pgsql/9.3/archive/%f && cp %p /var/lib/pgsql/9.3/archive/%f'
  199. # command to use to archive a logfile segment
  200. # placeholders: %p = path of file to archive
  201. # %f = file name only
  202. # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
  203. #archive_timeout = 0 # force a logfile segment switch after this
  204. # number of seconds; 0 disables
  205.  
  206.  
  207. #------------------------------------------------------------------------------
  208. # REPLICATION
  209. #------------------------------------------------------------------------------
  210.  
  211. # - Sending Server(s) -
  212.  
  213. # Set these on the master and on any standby that will send replication data.
  214.  
  215. max_wal_senders = 10 # max number of walsender processes
  216. # (change requires restart)
  217. wal_keep_segments = 64 # in logfile segments, 16MB each; 0 disables
  218. wal_sender_timeout = 3s # in milliseconds; 0 disables
  219.  
  220. # - Master Server -
  221.  
  222. # These settings are ignored on a standby server.
  223.  
  224. synchronous_standby_names = '' # standby servers that provide sync rep
  225. # comma-separated list of application_name
  226. # from standby(s); '*' = all
  227. vacuum_defer_cleanup_age = 100000 # number of xacts by which cleanup is delayed
  228.  
  229. # - Standby Servers -
  230.  
  231. # These settings are ignored on a master server.
  232.  
  233. hot_standby = on # "on" allows queries during recovery
  234. # (change requires restart)
  235. #max_standby_archive_delay = 30s # max delay before canceling queries
  236. # when reading WAL from archive;
  237. # -1 allows indefinite delay
  238. #max_standby_streaming_delay = 30s # max delay before canceling queries
  239. # when reading streaming WAL;
  240. # -1 allows indefinite delay
  241. wal_receiver_status_interval = 1s # send replies at least this often
  242. # 0 disables
  243. hot_standby_feedback = on # send info from standby to prevent
  244. # query conflicts
  245. #wal_receiver_timeout = 60s # time that receiver waits for
  246. # communication from master
  247. # in milliseconds; 0 disables
  248.  
  249.  
  250. #------------------------------------------------------------------------------
  251. # QUERY TUNING
  252. #------------------------------------------------------------------------------
  253.  
  254. # - Planner Method Configuration -
  255.  
  256. #enable_bitmapscan = on
  257. #enable_hashagg = on
  258. #enable_hashjoin = on
  259. #enable_indexscan = on
  260. #enable_indexonlyscan = on
  261. #enable_material = on
  262. #enable_mergejoin = on
  263. #enable_nestloop = on
  264. #enable_seqscan = on
  265. #enable_sort = on
  266. #enable_tidscan = on
  267.  
  268. # - Planner Cost Constants -
  269.  
  270. #seq_page_cost = 1.0 # measured on an arbitrary scale
  271. #random_page_cost = 4 # same scale as above
  272. #cpu_tuple_cost = 0.01 # same scale as above
  273. #cpu_index_tuple_cost = 0.005 # same scale as above
  274. #cpu_operator_cost = 0.0025 # same scale as above
  275. effective_cache_size = 100GB
  276.  
  277. # - Genetic Query Optimizer -
  278.  
  279. #geqo = on
  280. #geqo_threshold = 12
  281. #geqo_effort = 5 # range 1-10
  282. #geqo_pool_size = 0 # selects default based on effort
  283. #geqo_generations = 0 # selects default based on effort
  284. #geqo_selection_bias = 2.0 # range 1.5-2.0
  285. #geqo_seed = 0.0 # range 0.0-1.0
  286.  
  287. # - Other Planner Options -
  288.  
  289. #default_statistics_target = 100 # range 1-10000
  290. #constraint_exclusion = partition # on, off, or partition
  291. #cursor_tuple_fraction = 0.1 # range 0.0-1.0
  292. #from_collapse_limit = 8
  293. #join_collapse_limit = 8 # 1 disables collapsing of explicit
  294. # JOIN clauses
  295.  
  296.  
  297. #------------------------------------------------------------------------------
  298. # ERROR REPORTING AND LOGGING
  299. #------------------------------------------------------------------------------
  300.  
  301. # - Where to Log -
  302.  
  303. log_destination = 'stderr' # Valid values are combinations of
  304. # stderr, csvlog, syslog, and eventlog,
  305. # depending on platform. csvlog
  306. # requires logging_collector to be on.
  307.  
  308. # This is used when logging to stderr:
  309. logging_collector = on # Enable capturing of stderr and csvlog
  310. # into log files. Required to be on for
  311. # csvlogs.
  312. # (change requires restart)
  313.  
  314. # These are only used if logging_collector is on:
  315. log_directory = 'pg_log' # directory where log files are written,
  316. # can be absolute or relative to PGDATA
  317. log_filename = 'postgresql.log' # pg_statsinfo need this log_filename setting
  318.  
  319. # log file name pattern,
  320. # can include strftime() escapes
  321. #log_file_mode = 0600 # creation mode for log files,
  322. # begin with 0 to use octal notation
  323. log_truncate_on_rotation = on # If on, an existing log file with the
  324. # same name as the new log file will be
  325. # truncated rather than appended to.
  326. # But such truncation only occurs on
  327. # time-driven rotation, not on restarts
  328. # or size-driven rotation. Default is
  329. # off, meaning append to existing files
  330. # in all cases.
  331. log_rotation_age = 1d # Automatic rotation of logfiles will
  332. # happen after that time. 0 disables.
  333. log_rotation_size = 0 # Automatic rotation of logfiles will
  334. # happen after that much log output.
  335. # 0 disables.
  336.  
  337. # These are relevant when logging to syslog:
  338. #syslog_facility = 'LOCAL0'
  339. #syslog_ident = 'postgres'
  340.  
  341. # This is only relevant when logging to eventlog (win32):
  342. #event_source = 'PostgreSQL'
  343.  
  344. # - When to Log -
  345.  
  346. #client_min_messages = notice # values in order of decreasing detail:
  347. # debug5
  348. # debug4
  349. # debug3
  350. # debug2
  351. # debug1
  352. # log
  353. # notice
  354. # warning
  355. # error
  356.  
  357. #log_min_messages = warning # values in order of decreasing detail:
  358. # debug5
  359. # debug4
  360. # debug3
  361. # debug2
  362. # debug1
  363. # info
  364. # notice
  365. # warning
  366. # error
  367. # log
  368. # fatal
  369. # panic
  370.  
  371. #log_min_error_statement = error # values in order of decreasing detail:
  372. # debug5
  373. # debug4
  374. # debug3
  375. # debug2
  376. # debug1
  377. # info
  378. # notice
  379. # warning
  380. # error
  381. # log
  382. # fatal
  383. # panic (effectively off)
  384.  
  385. log_min_duration_statement = 100 # -1 is disabled, 0 logs all statements
  386. # and their durations, > 0 logs only
  387. # statements running at least this number
  388. # of milliseconds
  389.  
  390.  
  391. # - What to Log -
  392.  
  393. #debug_print_parse = off
  394. #debug_print_rewritten = off
  395. #debug_print_plan = off
  396. #debug_pretty_print = on
  397. log_checkpoints = on
  398. #log_connections = off
  399. #log_disconnections = off
  400. #log_duration = off
  401. #log_error_verbosity = default # terse, default, or verbose messages
  402. log_hostname = on
  403. log_line_prefix = '< %m >' # special values:
  404. # %a = application name
  405. # %u = user name
  406. # %d = database name
  407. # %r = remote host and port
  408. # %h = remote host
  409. # %p = process ID
  410. # %t = timestamp without milliseconds
  411. # %m = timestamp with milliseconds
  412. # %i = command tag
  413. # %e = SQL state
  414. # %c = session ID
  415. # %l = session line number
  416. # %s = session start timestamp
  417. # %v = virtual transaction ID
  418. # %x = transaction ID (0 if none)
  419. # %q = stop here in non-session
  420. # processes
  421. # %% = '%'
  422. # e.g. '<%u%%%d> '
  423. log_lock_waits = on # log lock waits >= deadlock_timeout
  424. #log_statement = 'none' # none, ddl, mod, all
  425. #log_temp_files = -1 # log temporary files equal or larger
  426. # than the specified size in kilobytes;
  427. # -1 disables, 0 logs all temp files
  428. log_timezone = 'Europe/Moscow'
  429.  
  430.  
  431. #------------------------------------------------------------------------------
  432. # RUNTIME STATISTICS
  433. #------------------------------------------------------------------------------
  434.  
  435. # - Query/Index Statistics Collector -
  436.  
  437. track_activities = on
  438. track_counts = on
  439. track_io_timing = on
  440. track_functions = all # none, pl, all
  441. #track_activity_query_size = 1024 # (change requires restart)
  442. #update_process_title = on
  443. #stats_temp_directory = 'pg_stat_tmp'
  444.  
  445.  
  446. # - Statistics Monitoring -
  447.  
  448. #log_parser_stats = off
  449. #log_planner_stats = off
  450. #log_executor_stats = off
  451. #log_statement_stats = off
  452.  
  453.  
  454. #------------------------------------------------------------------------------
  455. # AUTOVACUUM PARAMETERS
  456. #------------------------------------------------------------------------------
  457.  
  458. autovacuum = on # Enable autovacuum subprocess? 'on'
  459. # requires track_counts to also be on.
  460. log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
  461. # their durations, > 0 logs only
  462. # actions running at least this number
  463. # of milliseconds.
  464. autovacuum_max_workers = 10 # max number of autovacuum subprocesses
  465. # (change requires restart)
  466. #autovacuum_naptime = 1min # time between autovacuum runs
  467. #autovacuum_vacuum_threshold = 50 # min number of row updates before
  468. # vacuum
  469. #autovacuum_analyze_threshold = 50 # min number of row updates before
  470. # analyze
  471. autovacuum_vacuum_scale_factor = 0.01 # fraction of table size before vacuum
  472. autovacuum_analyze_scale_factor = 0.01 # fraction of table size before analyze
  473. #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
  474. # (change requires restart)
  475. autovacuum_vacuum_cost_delay = 5ms # default vacuum cost delay for
  476. # autovacuum, in milliseconds;
  477. # -1 means use vacuum_cost_delay
  478. autovacuum_vacuum_cost_limit = 1000 # default vacuum cost limit for
  479. # autovacuum, -1 means use
  480. # vacuum_cost_limit
  481.  
  482.  
  483. #------------------------------------------------------------------------------
  484. # CLIENT CONNECTION DEFAULTS
  485. #------------------------------------------------------------------------------
  486.  
  487. # - Statement Behavior -
  488.  
  489. #search_path = '"$user",public' # schema names
  490. #default_tablespace = '' # a tablespace name, '' uses the default
  491. #temp_tablespaces = '' # a list of tablespace names, '' uses
  492. # only default tablespace
  493. #check_function_bodies = on
  494. #default_transaction_isolation = 'read committed'
  495. #default_transaction_read_only = off
  496. #default_transaction_deferrable = off
  497. #session_replication_role = 'origin'
  498. #statement_timeout = 0 # in milliseconds, 0 is disabled
  499. #lock_timeout = 0 # in milliseconds, 0 is disabled
  500. #vacuum_freeze_min_age = 50000000
  501. #vacuum_freeze_table_age = 150000000
  502. #bytea_output = 'hex' # hex, escape
  503. #xmlbinary = 'base64'
  504. #xmloption = 'content'
  505.  
  506. # - Locale and Formatting -
  507.  
  508. datestyle = 'iso, dmy'
  509. #intervalstyle = 'postgres'
  510. timezone = 'Europe/Moscow'
  511. #timezone_abbreviations = 'Default' # Select the set of available time zone
  512. # abbreviations. Currently, there are
  513. # Default
  514. # Australia
  515. # India
  516. # You can create your own file in
  517. # share/timezonesets/.
  518. #extra_float_digits = 0 # min -15, max 3
  519. #client_encoding = sql_ascii # actually, defaults to database
  520. # encoding
  521.  
  522. # These settings are initialized by initdb, but they can be changed.
  523. lc_messages = 'en_US.UTF-8' # locale for system error message
  524. # strings
  525. lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
  526. lc_numeric = 'en_US.UTF-8' # locale for number formatting
  527. lc_time = 'en_US.UTF-8' # locale for time formatting
  528.  
  529. # default configuration for text search
  530. default_text_search_config = 'pg_catalog.english'
  531.  
  532. # - Other Defaults -
  533.  
  534. #dynamic_library_path = '$libdir'
  535. #local_preload_libraries = ''
  536.  
  537.  
  538. #------------------------------------------------------------------------------
  539. # LOCK MANAGEMENT
  540. #------------------------------------------------------------------------------
  541.  
  542. #deadlock_timeout = 1s
  543. #max_locks_per_transaction = 64 # min 10
  544. # (change requires restart)
  545. # Note: Each lock table slot uses ~270 bytes of shared memory, and there are
  546. # max_locks_per_transaction * (max_connections + max_prepared_transactions)
  547. # lock table slots.
  548. #max_pred_locks_per_transaction = 64 # min 10
  549. # (change requires restart)
  550.  
  551.  
  552. #------------------------------------------------------------------------------
  553. # VERSION/PLATFORM COMPATIBILITY
  554. #------------------------------------------------------------------------------
  555.  
  556. # - Previous PostgreSQL Versions -
  557.  
  558. #array_nulls = on
  559. #backslash_quote = safe_encoding # on, off, or safe_encoding
  560. #default_with_oids = off
  561. #escape_string_warning = on
  562. #lo_compat_privileges = off
  563. #quote_all_identifiers = off
  564. #sql_inheritance = on
  565. #standard_conforming_strings = on
  566. #synchronize_seqscans = on
  567.  
  568. # - Other Platforms and Clients -
  569.  
  570. #transform_null_equals = off
  571.  
  572.  
  573. #------------------------------------------------------------------------------
  574. # ERROR HANDLING
  575. #------------------------------------------------------------------------------
  576.  
  577. #exit_on_error = off # terminate session on any error?
  578. #restart_after_crash = on # reinitialize after backend crash?
  579.  
  580.  
  581. #------------------------------------------------------------------------------
  582. # CONFIG FILE INCLUDES
  583. #------------------------------------------------------------------------------
  584.  
  585. # These options allow settings to be loaded from files other than the
  586. # default postgresql.conf.
  587.  
  588. #include_dir = 'conf.d' # include files ending in '.conf' from
  589. # directory 'conf.d'
  590. #include_if_exists = 'exists.conf' # include file only if it exists
  591. #include = 'special.conf' # include file
  592.  
  593.  
  594. #------------------------------------------------------------------------------
  595. # CUSTOMIZED OPTIONS
  596. #------------------------------------------------------------------------------
  597.  
  598. # Add settings for extensions here
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!