postgresql standby 配置
首先、配置下inux sysctl.conf配置 64G
For 64-bit systems with 64GB of RAM:
kernel.shmmax=34359738368kernel.shmmni=4096kernel.shmall=16777216vm.nr_hugepages=16384
sysctl -w kernel.shmall=16777216sysctl -w kernel.shmmax=34359738368sysctl -p
简单配置如下：
master
postgresql.conf
# -----------------------------# PostgreSQL configuration file# -----------------------------## This file consists of lines of the form:## name = value## (The "=" is optional.)Whitespace may be used.Comments are introduced with# "#" anywhere on a line.The complete list of parameter names and allowed# values can be found in the PostgreSQL documentation.## The commented-out settings shown in this file represent the default values.# Re-commenting a setting is NOT sufficient to revert it to the default value;# you need to reload the server.## This file is read on server startup and when the server receives a SIGHUP# signal.If you edit the file on a running system, you have to SIGHUP the# server for the changes to take effect, or use "pg_ctl reload".Some# parameters, which are marked below, require a server shutdown and restart to# take effect.## Any parameter can also be given as a command-line option to the server, e.g.,# "postgres -c log_connections=on".Some parameters can be changed at run time# with the "SET" SQL command.## Memory units:kB = kilobytes Time units:ms= milliseconds# MB = megabytes s = seconds# GB = gigabytes min = minutes# h = hours# d = days#------------------------------------------------------------------------------# FILE LOCATIONS#------------------------------------------------------------------------------# The default values of these variables are driven from the -D command-line# option or PGDATA environment variable, represented here as ConfigDir.#data_directory = 'ConfigDir'# use data in another directory# (change requires restart)#hba_file = 'ConfigDir/pg_hba.conf'# host-based authentication file# (change requires restart)#ident_file = 'ConfigDir/pg_ident.conf'# ident configuration file# (change requires restart)# If external_pid_file is not explicitly set, no extra PID file is written.#external_pid_file = '(none)'# write an extra PID file# (change requires restart)#------------------------------------------------------------------------------# CONNECTIONS AND AUTHENTICATION#------------------------------------------------------------------------------# - Connection Settings -listen_addresses = '*'# what IP address(es) to listen on;# comma-separated list of addresses;# defaults to 'localhost', '*' = all# (change requires restart)#port = 5432# (change requires restart)max_connections = 100# (change requires restart)# Note:Increasing max_connections costs ~400 bytes of shared memory per# connection slot, plus lock space (see max_locks_per_transaction).#superuser_reserved_connections = 3# (change requires restart)#unix_socket_directory = ''# (change requires restart)#unix_socket_group = ''# (change requires restart)#unix_socket_permissions = 0777# begin with 0 to use octal notation# (change requires restart)#bonjour = off# advertise server via Bonjour# (change requires restart)#bonjour_name = ''# defaults to the computer name# (change requires restart)# - Security and Authentication -#authentication_timeout = 1min# 1s-600s#ssl = off# (change requires restart)#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'# allowed SSL ciphers# (change requires restart)#ssl_renegotiation_limit = 512MB# amount of data between renegotiations#password_encryption = on#db_user_namespace = off# Kerberos and GSSAPI#krb_server_keyfile = ''#krb_srvname = 'postgres'# (Kerberos only)#krb_caseins_users = off# - TCP Keepalives -# see "man 7 tcp" for details#tcp_keepalives_idle = 0# TCP_KEEPIDLE, in seconds;# 0 selects the system default#tcp_keepalives_interval = 0# TCP_KEEPINTVL, in seconds;# 0 selects the system default#tcp_keepalives_count = 0# TCP_KEEPCNT;# 0 selects the system default#------------------------------------------------------------------------------# RESOURCE USAGE (except WAL)#------------------------------------------------------------------------------# - Memory -shared_buffers = 1024MB# min 128kB# (change requires restart)#temp_buffers = 8MB# min 800kB#max_prepared_transactions = 0# zero disables the feature# (change requires restart)# Note:Increasing max_prepared_transactions costs ~600 bytes of shared memory# per transaction slot, plus lock space (see max_locks_per_transaction).# It is not advisable to set max_prepared_transactions nonzero unless you# actively intend to use prepared transactions.#work_mem = 1MB# min 64kB#maintenance_work_mem = 16MB# min 1MB#max_stack_depth = 2MB# min 100kB# - Kernel Resource Usage -#max_files_per_process = 1000# min 25# (change requires restart)#shared_preload_libraries = 'pg_stat_statements'# (change requires restart)# - Cost-Based Vacuum Delay -#vacuum_cost_delay = 0ms# 0-100 milliseconds#vacuum_cost_page_hit = 1# 0-10000 credits#vacuum_cost_page_miss = 10# 0-10000 credits#vacuum_cost_page_dirty = 20# 0-10000 credits#vacuum_cost_limit = 200# 1-10000 credits# - Background Writer -#bgwriter_delay = 200ms# 10-10000ms between rounds#bgwriter_lru_maxpages = 100# 0-1000 max buffers written/round#bgwriter_lru_multiplier = 2.0# 0-10.0 multipler on buffers scanned/round# - Asynchronous Behavior -#effective_io_concurrency = 1# 1-1000. 0 disables prefetching#------------------------------------------------------------------------------# WRITE AHEAD LOG#------------------------------------------------------------------------------# - Settings -wal_level = hot_standby# minimal, archive, or hot_standby# (change requires restart)#fsync = on# turns forced synchronization on or off#synchronous_commit = on# synchronization level; on, off, or local#wal_sync_method = fsync# the default is the first option# supported by the operating system:# open_datasync# fdatasync (default on Linux)# fsync# fsync_writethrough# open_sync#full_page_writes = on# recover from partial page writes#wal_buffers = -1# min 32kB, -1 sets based on shared_buffers# (change requires restart)#wal_writer_delay = 200ms# 1-10000 milliseconds#commit_delay = 0# range 0-100000, in microseconds#commit_siblings = 5# range 1-1000# - Checkpoints -checkpoint_segments = 128# in logfile segments, min 1, 16MB each#checkpoint_timeout = 5min# range 30s-1h#checkpoint_completion_target = 0.5# checkpoint target duration, 0.0 - 1.0#checkpoint_warning = 30s# 0 disables# - Archiving -archive_mode = on# allows archiving to be done# (change requires restart)archive_command = 'cp -i %p /mnt/pgsql/archivedir/%f </dev/null'# command to use to archive a logfile segmentarchive_timeout = 600# force a logfile segment switch after this# number of seconds; 0 disables#------------------------------------------------------------------------------# REPLICATION#------------------------------------------------------------------------------# - Master Server -# These settings are ignored on a standby servermax_wal_senders = 5# max number of walsender processes# (change requires restart)#wal_sender_delay = 1s# walsender cycle time, 1-10000 millisecondswal_keep_segments = 64# in logfile segments, 16MB each; 0 disables#vacuum_defer_cleanup_age = 0# number of xacts by which cleanup is delayed#replication_timeout = 60s# in milliseconds; 0 disables#synchronous_standby_names = ''# standby servers that provide sync rep# comma-separated list of application_name# from standby(s); '*' = all# - Standby Servers -# These settings are ignored on a master server#hot_standby = on# "on" allows queries during recovery# (change requires restart)#max_standby_archive_delay = 30s# max delay before canceling queries# when reading WAL from archive;# -1 allows indefinite delay#max_standby_streaming_delay = 30s# max delay before canceling queries# when reading streaming WAL;# -1 allows indefinite delay#wal_receiver_status_interval = 10s# send replies at least this often# 0 disables#hot_standby_feedback = off# send info from standby to prevent# query conflicts#------------------------------------------------------------------------------# QUERY TUNING#------------------------------------------------------------------------------# - Planner Method Configuration -#enable_bitmapscan = on#enable_hashagg = on#enable_hashjoin = on#enable_indexscan = on#enable_material = on#enable_mergejoin = on#enable_nestloop = on#enable_seqscan = on#enable_sort = on#enable_tidscan = on# - Planner Cost Constants -#seq_page_cost = 1.0# measured on an arbitrary scalerandom_page_cost = 2.0# same scale as above#cpu_tuple_cost = 0.01# same scale as above#cpu_index_tuple_cost = 0.005# same scale as above#cpu_operator_cost = 0.0025# same scale as above#effective_cache_size = 128MB# - Genetic Query Optimizer -#geqo = on#geqo_threshold = 12#geqo_effort = 5# range 1-10#geqo_pool_size = 0# selects default based on effort#geqo_generations = 0# selects default based on effort#geqo_selection_bias = 2.0# range 1.5-2.0#geqo_seed = 0.0# range 0.0-1.0# - Other Planner Options -#default_statistics_target = 100# range 1-10000#constraint_exclusion = partition# on, off, or partition#cursor_tuple_fraction = 0.1# range 0.0-1.0#from_collapse_limit = 8#join_collapse_limit = 8# 1 disables collapsing of explicit# JOIN clauses#------------------------------------------------------------------------------# ERROR REPORTING AND LOGGING#------------------------------------------------------------------------------# - Where to Log -#log_destination = 'stderr'# Valid values are combinations of# stderr, csvlog, syslog, and eventlog,# depending on platform.csvlog# requires logging_collector to be on.# This is used when logging to stderr:logging_collector = on# Enable capturing of stderr and csvlog# into log files. Required to be on for# csvlogs.# (change requires restart)# These are only used if logging_collector is on:log_directory = 'pg_log'# directory where log files are written,# can be absolute or relative to PGDATAlog_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'# log file name pattern,# can include strftime() escapes#log_file_mode = 0600# creation mode for log files,# begin with 0 to use octal notation#log_truncate_on_rotation = off# If on, an existing log file with the# same name as the new log file will be# truncated rather than appended to.# But such truncation only occurs on# time-driven rotation, not on restarts# or size-driven rotation.Default is# off, meaning append to existing files# in all cases.#log_rotation_age = 1d# Automatic rotation of logfiles will# happen after that time.0 disables.#log_rotation_size = 10MB# Automatic rotation of logfiles will# happen after that much log output.# 0 disables.# These are relevant when logging to syslog:#syslog_facility = 'LOCAL0'#syslog_ident = 'postgres'#silent_mode = off# Run server silently.# DO NOT USE without syslog or# logging_collector# (change requires restart)# - When to Log -#client_min_messages = debug5# values in order of decreasing detail:# debug5# debug4# debug3# debug2# debug1# log# notice# warning# errorlog_min_messages = debug5# values in order of decreasing detail:# debug5# debug4# debug3# debug2# debug1# info# notice# warning# error# log# fatal# panic#log_min_error_statement = error# values in order of decreasing detail: # debug5# debug4# debug3# debug2# debug1 # info# notice# warning# error# log# fatal# panic (effectively off)#log_min_duration_statement = -1# -1 is disabled, 0 logs all statements# and their durations, > 0 logs only# statements running at least this number# of milliseconds# - What to Log -debug_print_parse = offdebug_print_rewritten = offdebug_print_plan = offdebug_pretty_print = onlog_checkpoints = onlog_connections = onlog_disconnections = on#log_duration = off#log_error_verbosity = default# terse, default, or verbose messages#log_hostname = off#log_line_prefix = ''# special values:# %a = application name# %u = user name# %d = database name# %r = remote host and port# %h = remote host# %p = process ID# %t = timestamp without milliseconds# %m = timestamp with milliseconds# %i = command tag# %e = SQL state# %c = session ID# %l = session line number# %s = session start timestamp# %v = virtual transaction ID# %x = transaction ID (0 if none)# %q = stop here in non-session# processes# %% = '%'# e.g. '<%u%%%d> '#log_lock_waits = off# log lock waits >= deadlock_timeout#log_statement = 'none'# none, ddl, mod, all#log_temp_files = -1# log temporary files equal or larger# than the specified size in kilobytes;# -1 disables, 0 logs all temp files#log_timezone = '(defaults to server environment setting)'#------------------------------------------------------------------------------# RUNTIME STATISTICS#------------------------------------------------------------------------------# - Query/Index Statistics Collector -#track_activities = on#track_counts = on#track_functions = none# none, pl, alltrack_activity_query_size = 2048 # (change requires restart)#update_process_title = on#stats_temp_directory = 'pg_stat_tmp'# - Statistics Monitoring -#log_parser_stats = off#log_planner_stats = off#log_executor_stats = off#log_statement_stats = off#------------------------------------------------------------------------------# AUTOVACUUM PARAMETERS#------------------------------------------------------------------------------#autovacuum = on# Enable autovacuum subprocess?'on'# requires track_counts to also be on.#log_autovacuum_min_duration = -1# -1 disables, 0 logs all actions and# their durations, > 0 logs only# actions running at least this number# of milliseconds.#autovacuum_max_workers = 3# max number of autovacuum subprocesses# (change requires restart)#autovacuum_naptime = 1min# time between autovacuum runs#autovacuum_vacuum_threshold = 50# min number of row updates before# vacuum#autovacuum_analyze_threshold = 50# min number of row updates before# analyze#autovacuum_vacuum_scale_factor = 0.2# fraction of table size before vacuum#autovacuum_analyze_scale_factor = 0.1# fraction of table size before analyze#autovacuum_freeze_max_age = 200000000# maximum XID age before forced vacuum# (change requires restart)#autovacuum_vacuum_cost_delay = 20ms# default vacuum cost delay for# autovacuum, in milliseconds;# -1 means use vacuum_cost_delay#autovacuum_vacuum_cost_limit = -1# default vacuum cost limit for# autovacuum, -1 means use# vacuum_cost_limit#------------------------------------------------------------------------------# CLIENT CONNECTION DEFAULTS#------------------------------------------------------------------------------# - Statement Behavior -#search_path = '"$user",public'# schema names#default_tablespace = ''# a tablespace name, '' uses the default#temp_tablespaces = ''# a list of tablespace names, '' uses# only default tablespace#check_function_bodies = on#default_transaction_isolation = 'read committed'#default_transaction_read_only = off#default_transaction_deferrable = off#session_replication_role = 'origin'#statement_timeout = 0# in milliseconds, 0 is disabled#vacuum_freeze_min_age = 50000000#vacuum_freeze_table_age = 150000000#bytea_output = 'hex'# hex, escape#xmlbinary = 'base64'#xmloption = 'content'# - Locale and Formatting -datestyle = 'iso, mdy'#intervalstyle = 'postgres'#timezone = '(defaults to server environment setting)'timezone = 'PRC'timezone_abbreviations = 'Default' # Select the set of available time zone# abbreviations.Currently, there are# Default# Australia# India# You can create your own file in# share/timezonesets/.#extra_float_digits = 0# min -15, max 3#client_encoding = sql_ascii# actually, defaults to database# encoding# These settings are initialized by initdb, but they can be changed.lc_messages = 'en_HK.UTF-8'# locale for system error message# stringslc_monetary = 'en_HK.UTF-8'# locale for monetary formattinglc_numeric = 'en_HK.UTF-8'# locale for number formattinglc_time = 'en_HK.UTF-8'# locale for time formatting# default configuration for text searchdefault_text_search_config = 'pg_catalog.english'# - Other Defaults -#dynamic_library_path = '$libdir'#local_preload_libraries = ''#------------------------------------------------------------------------------# LOCK MANAGEMENT#------------------------------------------------------------------------------#deadlock_timeout = 1s#max_locks_per_transaction = 64# min 10# (change requires restart)# Note:Each lock table slot uses ~270 bytes of shared memory, and there are# max_locks_per_transaction * (max_connections + max_prepared_transactions)# lock table slots.#max_pred_locks_per_transaction = 64# min 10# (change requires restart)#------------------------------------------------------------------------------# VERSION/PLATFORM COMPATIBILITY#------------------------------------------------------------------------------# - Previous PostgreSQL Versions -#array_nulls = on#backslash_quote = safe_encoding# on, off, or safe_encoding#default_with_oids = off#escape_string_warning = on#lo_compat_privileges = off#quote_all_identifiers = off#sql_inheritance = on#standard_conforming_strings = on#synchronize_seqscans = on# - Other Platforms and Clients -#transform_null_equals = off#------------------------------------------------------------------------------# ERROR HANDLING#------------------------------------------------------------------------------#exit_on_error = off# terminate session on any error?#restart_after_crash = on# reinitialize after backend crash?#------------------------------------------------------------------------------# CUSTOMIZED OPTIONS#------------------------------------------------------------------------------#custom_variable_classes = 'pg_stat_statements'# list of custom variable class names#pg_stat_statements.max=1000#pg_stat_statements.track=all
pg_hba.conf
# PostgreSQL Client Authentication Configuration File# ===================================================## Refer to the "Client Authentication" section in the PostgreSQL# documentation for a complete description of this file.A short# synopsis follows.## This file controls: which hosts are allowed to connect, how clients# are authenticated, which PostgreSQL user names they can use, which# databases they can access.Records take one of these forms:## local DATABASEUSERMETHOD# host DATABASEUSERADDRESSMETHOD# hostssl DATABASEUSERADDRESSMETHOD# hostnosslDATABASEUSERADDRESSMETHOD## (The uppercase items must be replaced by actual values.)## The first field is the connection type: "local" is a Unix-domain# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a# plain TCP/IP socket.## DATABASE can be "all", "sameuser", "samerole", "replication", a# database name, or a comma-separated list thereof. The "all"# keyword does not match "replication". Access to replication# must be enabled in a separate record (see example below).## USER can be "all", a user name, a group name prefixed with "+", or a# comma-separated list thereof.In both the DATABASE and USER fields# you can also write a file name prefixed with "@" to include names# from a separate file.## ADDRESS specifies the set of hosts the record matches.It can be a# host name, or it is made up of an IP address and a CIDR mask that is# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that# specifies the number of significant bits in the mask.A host name# that starts with a dot (.) matches a suffix of the actual host name.# Alternatively, you can write an IP address and netmask in separate# columns to specify the set of hosts.Instead of a CIDR-address, you# can write "samehost" to match any of the server's own IP addresses,# or "samenet" to match any address in any subnet that the server is# directly connected to.## METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",# "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert".Note that# "password" sends passwords in clear text; "md5" is preferred since# it sends encrypted passwords.## OPTIONS are a set of options for the authentication in the format# NAME=VALUE.The available options depend on the different# authentication methods -- refer to the "Client Authentication"# section in the documentation for a list of which options are# available for which authentication methods.## Database and user names containing spaces, commas, quotes and other# special characters must be quoted.Quoting one of the keywords# "all", "sameuser", "samerole" or "replication" makes the name lose# its special character, and just match a database or username with# that name.## This file is read on server startup and when the postmaster receives# a SIGHUP signal.If you edit the file on a running system, you have# to SIGHUP the postmaster for the changes to take effect.You can# use "pg_ctl reload" to do that.# Put your actual configuration here# ----------------------------------## If you want to allow non-local connections, you need to add more# "host" records.In that case you will also need to make PostgreSQL# listen on a non-local interface via the listen_addresses# configuration parameter, or via the -i or -h command line switches.# CAUTION: Configuring the system for local "trust" authentication# allows any local user to connect as any PostgreSQL user, including# the database superuser.If you do not trust all your local users,# use another authentication method.# TYPEDATABASE USER ADDRESS METHOD# "local" is for Unix domain socket connections onlylocal all all trust# IPv4 local connections:host all all 127.0.0.1/32 trust# IPv6 local connections:host all all ::1/128 trust# Allow replication connections from localhost, by a user with the# replication privilege.host replication user slaveip/16 md5#local replication postgres trust#host replication postgres 127.0.0.1/32 trust#host replication postgres ::1/128 trust
slave配置
postgresql.conf
# -----------------------------# PostgreSQL configuration file# -----------------------------## This file consists of lines of the form:## name = value## (The "=" is optional.)Whitespace may be used.Comments are introduced with# "#" anywhere on a line.The complete list of parameter names and allowed# values can be found in the PostgreSQL documentation.## The commented-out settings shown in this file represent the default values.# Re-commenting a setting is NOT sufficient to revert it to the default value;# you need to reload the server.## This file is read on server startup and when the server receives a SIGHUP# signal.If you edit the file on a running system, you have to SIGHUP the# server for the changes to take effect, or use "pg_ctl reload".Some# parameters, which are marked below, require a server shutdown and restart to# take effect.## Any parameter can also be given as a command-line option to the server, e.g.,# "postgres -c log_connections=on".Some parameters can be changed at run time# with the "SET" SQL command.## Memory units:kB = kilobytes Time units:ms= milliseconds# MB = megabytes s = seconds# GB = gigabytes min = minutes# h = hours# d = days#------------------------------------------------------------------------------# FILE LOCATIONS#------------------------------------------------------------------------------# The default values of these variables are driven from the -D command-line# option or PGDATA environment variable, represented here as ConfigDir.#data_directory = 'ConfigDir'# use data in another directory# (change requires restart)#hba_file = 'ConfigDir/pg_hba.conf'# host-based authentication file# (change requires restart)#ident_file = 'ConfigDir/pg_ident.conf'# ident configuration file# (change requires restart)# If external_pid_file is not explicitly set, no extra PID file is written.#external_pid_file = '(none)'# write an extra PID file# (change requires restart)#------------------------------------------------------------------------------# CONNECTIONS AND AUTHENTICATION#------------------------------------------------------------------------------# - Connection Settings -listen_addresses = '*'# what IP address(es) to listen on;# comma-separated list of addresses;# defaults to 'localhost', '*' = all# (change requires restart)#port = 5432# (change requires restart)max_connections = 100# (change requires restart)# Note:Increasing max_connections costs ~400 bytes of shared memory per# connection slot, plus lock space (see max_locks_per_transaction).#superuser_reserved_connections = 3# (change requires restart)#unix_socket_directory = ''# (change requires restart)#unix_socket_group = ''# (change requires restart)#unix_socket_permissions = 0777# begin with 0 to use octal notation# (change requires restart)#bonjour = off# advertise server via Bonjour# (change requires restart)#bonjour_name = ''# defaults to the computer name# (change requires restart)# - Security and Authentication -#authentication_timeout = 1min# 1s-600s#ssl = off# (change requires restart)#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'# allowed SSL ciphers# (change requires restart)#ssl_renegotiation_limit = 512MB# amount of data between renegotiations#password_encryption = on#db_user_namespace = off# Kerberos and GSSAPI#krb_server_keyfile = ''#krb_srvname = 'postgres'# (Kerberos only)#krb_caseins_users = off# - TCP Keepalives -# see "man 7 tcp" for details#tcp_keepalives_idle = 0# TCP_KEEPIDLE, in seconds;# 0 selects the system default#tcp_keepalives_interval = 0# TCP_KEEPINTVL, in seconds;# 0 selects the system default#tcp_keepalives_count = 0# TCP_KEEPCNT;# 0 selects the system default#------------------------------------------------------------------------------# RESOURCE USAGE (except WAL)#------------------------------------------------------------------------------# - Memory -shared_buffers = 1024MB# min 128kB# (change requires restart)#temp_buffers = 8MB# min 800kB#max_prepared_transactions = 0# zero disables the feature# (change requires restart)# Note:Increasing max_prepared_transactions costs ~600 bytes of shared memory# per transaction slot, plus lock space (see max_locks_per_transaction).# It is not advisable to set max_prepared_transactions nonzero unless you# actively intend to use prepared transactions.#work_mem = 1MB# min 64kB#maintenance_work_mem = 16MB# min 1MB#max_stack_depth = 2MB# min 100kB# - Kernel Resource Usage -#max_files_per_process = 1000# min 25# (change requires restart)#shared_preload_libraries = 'pg_stat_statements'# (change requires restart)# - Cost-Based Vacuum Delay -#vacuum_cost_delay = 0ms# 0-100 milliseconds#vacuum_cost_page_hit = 1# 0-10000 credits#vacuum_cost_page_miss = 10# 0-10000 credits#vacuum_cost_page_dirty = 20# 0-10000 credits#vacuum_cost_limit = 200# 1-10000 credits# - Background Writer -#bgwriter_delay = 200ms# 10-10000ms between rounds#bgwriter_lru_maxpages = 100# 0-1000 max buffers written/round#bgwriter_lru_multiplier = 2.0# 0-10.0 multipler on buffers scanned/round# - Asynchronous Behavior -#effective_io_concurrency = 1# 1-1000. 0 disables prefetching#------------------------------------------------------------------------------# WRITE AHEAD LOG#------------------------------------------------------------------------------# - Settings -wal_level = hot_standby# minimal, archive, or hot_standby# (change requires restart)#fsync = on# turns forced synchronization on or off#synchronous_commit = on# synchronization level; on, off, or local#wal_sync_method = fsync# the default is the first option# supported by the operating system:# open_datasync# fdatasync (default on Linux)# fsync# fsync_writethrough# open_sync#full_page_writes = on# recover from partial page writes#wal_buffers = -1# min 32kB, -1 sets based on shared_buffers# (change requires restart)#wal_writer_delay = 200ms# 1-10000 milliseconds#commit_delay = 0# range 0-100000, in microseconds#commit_siblings = 5# range 1-1000# - Checkpoints -checkpoint_segments = 128# in logfile segments, min 1, 16MB each#checkpoint_timeout = 5min# range 30s-1h#checkpoint_completion_target = 0.5# checkpoint target duration, 0.0 - 1.0#checkpoint_warning = 30s# 0 disables# - Archiving -#archive_mode = on# allows archiving to be done# (change requires restart)#archive_command = 'cp -i %p /mnt/pgsql/archivedir/%f </dev/null'# command to use to archive a logfile segment#archive_timeout = 600# force a logfile segment switch after this# number of seconds; 0 disables#------------------------------------------------------------------------------# REPLICATION#------------------------------------------------------------------------------# - Master Server -# These settings are ignored on a standby server#max_wal_senders = 5# max number of walsender processes# (change requires restart)#wal_sender_delay = 1s# walsender cycle time, 1-10000 milliseconds#wal_keep_segments = 64# in logfile segments, 16MB each; 0 disables#vacuum_defer_cleanup_age = 0# number of xacts by which cleanup is delayed#replication_timeout = 60s# in milliseconds; 0 disables#synchronous_standby_names = ''# standby servers that provide sync rep# comma-separated list of application_name# from standby(s); '*' = all# - Standby Servers -# These settings are ignored on a master serverhot_standby = on# "on" allows queries during recovery# (change requires restart)#max_standby_archive_delay = 30s# max delay before canceling queries# when reading WAL from archive;# -1 allows indefinite delay#max_standby_streaming_delay = 30s# max delay before canceling queries# when reading streaming WAL;# -1 allows indefinite delay#wal_receiver_status_interval = 10s# send replies at least this often# 0 disables#hot_standby_feedback = off# send info from standby to prevent# query conflicts#------------------------------------------------------------------------------# QUERY TUNING#------------------------------------------------------------------------------# - Planner Method Configuration -#enable_bitmapscan = on#enable_hashagg = on#enable_hashjoin = on#enable_indexscan = on#enable_material = on#enable_mergejoin = on#enable_nestloop = on#enable_seqscan = on#enable_sort = on#enable_tidscan = on# - Planner Cost Constants -#seq_page_cost = 1.0# measured on an arbitrary scalerandom_page_cost = 2.0# same scale as above#cpu_tuple_cost = 0.01# same scale as above#cpu_index_tuple_cost = 0.005# same scale as above#cpu_operator_cost = 0.0025# same scale as above#effective_cache_size = 128MB# - Genetic Query Optimizer -#geqo = on#geqo_threshold = 12#geqo_effort = 5# range 1-10#geqo_pool_size = 0# selects default based on effort#geqo_generations = 0# selects default based on effort#geqo_selection_bias = 2.0# range 1.5-2.0#geqo_seed = 0.0# range 0.0-1.0# - Other Planner Options -#default_statistics_target = 100# range 1-10000#constraint_exclusion = partition# on, off, or partition#cursor_tuple_fraction = 0.1# range 0.0-1.0#from_collapse_limit = 8#join_collapse_limit = 8# 1 disables collapsing of explicit# JOIN clauses#------------------------------------------------------------------------------# ERROR REPORTING AND LOGGING#------------------------------------------------------------------------------# - Where to Log -#log_destination = 'stderr'# Valid values are combinations of# stderr, csvlog, syslog, and eventlog,# depending on platform.csvlog# requires logging_collector to be on.# This is used when logging to stderr:#logging_collector = off# Enable capturing of stderr and csvloglogging_collector = on# into log files. Required to be on for# csvlogs.# (change requires restart)# These are only used if logging_collector is on:log_directory = 'pg_log'# directory where log files are written,# can be absolute or relative to PGDATAlog_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'# log file name pattern,# can include strftime() escapes#log_file_mode = 0600# creation mode for log files,# begin with 0 to use octal notation#log_truncate_on_rotation = off# If on, an existing log file with the# same name as the new log file will be# truncated rather than appended to.# But such truncation only occurs on# time-driven rotation, not on restarts# or size-driven rotation.Default is# off, meaning append to existing files# in all cases.#log_rotation_age = 1d# Automatic rotation of logfiles will# happen after that time.0 disables.#log_rotation_size = 10MB# Automatic rotation of logfiles will# happen after that much log output.# 0 disables.# These are relevant when logging to syslog:#syslog_facility = 'LOCAL0'#syslog_ident = 'postgres'#silent_mode = off# Run server silently.# DO NOT USE without syslog or# logging_collector# (change requires restart)# - When to Log -#client_min_messages = notice# values in order of decreasing detail:# debug5# debug4# debug3# debug2# debug1# log# notice# warning# errorlog_min_messages = debug5# values in order of decreasing detail:# debug5# debug4# debug3# debug2# debug1# info# notice# warning# error# log# fatal# panic#log_min_error_statement = error# values in order of decreasing detail: # debug5# debug4# debug3# debug2# debug1 # info# notice# warning# error# log# fatal# panic (effectively off)#log_min_duration_statement = -1# -1 is disabled, 0 logs all statements# and their durations, > 0 logs only# statements running at least this number# of milliseconds# - What to Log -debug_print_parse = ondebug_print_rewritten = ondebug_print_plan = ondebug_pretty_print = onlog_checkpoints = onlog_connections = onlog_disconnections = on#log_duration = off#log_error_verbosity = default# terse, default, or verbose messages#log_hostname = off#log_line_prefix = ''# special values:# %a = application name# %u = user name# %d = database name# %r = remote host and port# %h = remote host# %p = process ID# %t = timestamp without milliseconds# %m = timestamp with milliseconds# %i = command tag# %e = SQL state# %c = session ID# %l = session line number# %s = session start timestamp# %v = virtual transaction ID# %x = transaction ID (0 if none)# %q = stop here in non-session# processes# %% = '%'# e.g. '<%u%%%d> '#log_lock_waits = off# log lock waits >= deadlock_timeout#log_statement = 'none'# none, ddl, mod, all#log_temp_files = -1# log temporary files equal or larger# than the specified size in kilobytes;# -1 disables, 0 logs all temp files#log_timezone = '(defaults to server environment setting)'#------------------------------------------------------------------------------# RUNTIME STATISTICS#------------------------------------------------------------------------------# - Query/Index Statistics Collector -#track_activities = on#track_counts = on#track_functions = none# none, pl, alltrack_activity_query_size = 2048 # (change requires restart)#update_process_title = on#stats_temp_directory = 'pg_stat_tmp'# - Statistics Monitoring -#log_parser_stats = off#log_planner_stats = off#log_executor_stats = off#log_statement_stats = off#------------------------------------------------------------------------------# AUTOVACUUM PARAMETERS#------------------------------------------------------------------------------#autovacuum = on# Enable autovacuum subprocess?'on'# requires track_counts to also be on.#log_autovacuum_min_duration = -1# -1 disables, 0 logs all actions and# their durations, > 0 logs only# actions running at least this number# of milliseconds.#autovacuum_max_workers = 3# max number of autovacuum subprocesses# (change requires restart)#autovacuum_naptime = 1min# time between autovacuum runs#autovacuum_vacuum_threshold = 50# min number of row updates before# vacuum#autovacuum_analyze_threshold = 50# min number of row updates before# analyze#autovacuum_vacuum_scale_factor = 0.2# fraction of table size before vacuum#autovacuum_analyze_scale_factor = 0.1# fraction of table size before analyze#autovacuum_freeze_max_age = 200000000# maximum XID age before forced vacuum# (change requires restart)#autovacuum_vacuum_cost_delay = 20ms# default vacuum cost delay for# autovacuum, in milliseconds;# -1 means use vacuum_cost_delay#autovacuum_vacuum_cost_limit = -1# default vacuum cost limit for# autovacuum, -1 means use# vacuum_cost_limit#------------------------------------------------------------------------------# CLIENT CONNECTION DEFAULTS#------------------------------------------------------------------------------# - Statement Behavior -#search_path = '"$user",public'# schema names#default_tablespace = ''# a tablespace name, '' uses the default#temp_tablespaces = ''# a list of tablespace names, '' uses# only default tablespace#check_function_bodies = on#default_transaction_isolation = 'read committed'#default_transaction_read_only = off#default_transaction_deferrable = off#session_replication_role = 'origin'#statement_timeout = 0# in milliseconds, 0 is disabled#vacuum_freeze_min_age = 50000000#vacuum_freeze_table_age = 150000000#bytea_output = 'hex'# hex, escape#xmlbinary = 'base64'#xmloption = 'content'# - Locale and Formatting -datestyle = 'iso, mdy'#intervalstyle = 'postgres'#timezone = '(defaults to server environment setting)'timezone = 'PRC'timezone_abbreviations = 'Default' # Select the set of available time zone# abbreviations.Currently, there are# Default# Australia# India# You can create your own file in# share/timezonesets/.#extra_float_digits = 0# min -15, max 3#client_encoding = sql_ascii# actually, defaults to database# encoding# These settings are initialized by initdb, but they can be changed.lc_messages = 'en_HK.UTF-8'# locale for system error message# stringslc_monetary = 'en_HK.UTF-8'# locale for monetary formattinglc_numeric = 'en_HK.UTF-8'# locale for number formattinglc_time = 'en_HK.UTF-8'# locale for time formatting# default configuration for text searchdefault_text_search_config = 'pg_catalog.english'# - Other Defaults -#dynamic_library_path = '$libdir'#local_preload_libraries = ''#------------------------------------------------------------------------------# LOCK MANAGEMENT#------------------------------------------------------------------------------#deadlock_timeout = 1s#max_locks_per_transaction = 64# min 10# (change requires restart)# Note:Each lock table slot uses ~270 bytes of shared memory, and there are# max_locks_per_transaction * (max_connections + max_prepared_transactions)# lock table slots.#max_pred_locks_per_transaction = 64# min 10# (change requires restart)#------------------------------------------------------------------------------# VERSION/PLATFORM COMPATIBILITY#------------------------------------------------------------------------------# - Previous PostgreSQL Versions -#array_nulls = on#backslash_quote = safe_encoding# on, off, or safe_encoding#default_with_oids = off#escape_string_warning = on#lo_compat_privileges = off#quote_all_identifiers = off#sql_inheritance = on#standard_conforming_strings = on#synchronize_seqscans = on# - Other Platforms and Clients -#transform_null_equals = off#------------------------------------------------------------------------------# ERROR HANDLING#------------------------------------------------------------------------------#exit_on_error = off# terminate session on any error?#restart_after_crash = on# reinitialize after backend crash?#------------------------------------------------------------------------------# CUSTOMIZED OPTIONS#------------------------------------------------------------------------------#custom_variable_classes = 'pg_stat_statements'# list of custom variable class names#pg_stat_statements.max=1000#pg_stat_statements.track=all
recovery.conf配置
# -------------------------------# PostgreSQL recovery config file# -------------------------------## Edit this file to provide the parameters that PostgreSQL needs to# perform an archive recovery of a database, or to act as a replication# standby.## If "recovery.conf" is present in the PostgreSQL data directory, it is# read on postmaster startup.After successful recovery, it is renamed# to "recovery.done" to ensure that we do not accidentally re-enter# archive recovery or standby mode.## This file consists of lines of the form:## name = value## Comments are introduced with '#'.## The complete list of option names and allowed values can be found# in the PostgreSQL documentation.##---------------------------------------------------------------------------# ARCHIVE RECOVERY PARAMETERS#---------------------------------------------------------------------------## restore_command## specifies the shell command that is executed to copy log files# back from archival storage.The command string may contain %f,# which is replaced by the name of the desired log file, and %p,# which is replaced by the absolute path to copy the log file to.## This parameter is *required* for an archive recovery, but optional# for streaming replication.## It is important that the command return nonzero exit status on failure.# The command *will* be asked for log files that are not present in the# archive; it must return nonzero when so asked.## NOTE that the basename of %p will be different from %f; do not# expect them to be interchangeable.##restore_command = 'cp /mnt/pgsql/archivedir/%f %p'# e.g. 'cp /mnt/server/archivedir/%f %p'### archive_cleanup_command## specifies an optional shell command to execute at every restartpoint.# This can be useful for cleaning up the archive of a standby server.##archive_cleanup_command = 'pg_archivecleanup /mnt/pgsql/archivedir %r'## recovery_end_command## specifies an optional shell command to execute at completion of recovery.# This can be useful for cleaning up after the restore_command.##recovery_end_command = ''##---------------------------------------------------------------------------# RECOVERY TARGET PARAMETERS#---------------------------------------------------------------------------## By default, recovery will rollforward to the end of the WAL log.# If you want to stop rollforward at a specific point, you# must set a recovery target.## You may set a recovery target either by transactionId, by name,# or by timestamp. Recovery may either include or exclude the# transaction(s) with the recovery target value (ie, stop either# just after or just before the given target, respectively).###recovery_target_name = ''# e.g. 'daily backup 2011-01-26'##recovery_target_time = ''# e.g. '2004-07-14 22:39:00 EST'##recovery_target_xid = ''##recovery_target_inclusive = true### If you want to recover into a timeline other than the "main line" shown in# pg_control, specify the timeline number here, or write 'latest' to get# the latest branch for which there's a history file.##recovery_target_timeline = 'latest'### If pause_at_recovery_target is enabled, recovery will pause when# the recovery target is reached. The pause state will continue until# pg_xlog_replay_resume() is called. This setting has no effect if# hot standby is not enabled, or if no recovery target is set.##pause_at_recovery_target = true##---------------------------------------------------------------------------# STANDBY SERVER PARAMETERS#---------------------------------------------------------------------------## standby_mode## When standby_mode is enabled, the PostgreSQL server will work as a# standby. It will continuously wait for the additional XLOG records, using# restore_command and/or primary_conninfo.#standby_mode = on## primary_conninfo## If set, the PostgreSQL server will try to connect to the primary using this# connection string and receive XLOG records continuously.#primary_conninfo = 'host=masterip port=5432 user=user password=passwd'# e.g. 'host=localhost port=5432'### By default, a standby server keeps restoring XLOG records from the# primary indefinitely. If you want to stop the standby mode, finish recovery# and open the system in read/write mode, specify path to a trigger file.# The server will poll the trigger file path periodically and start as a# primary server when it's found.#trigger_file = '/mnt/pgsql/trigger_activestb'##---------------------------------------------------------------------------# HOT STANDBY PARAMETERS#---------------------------------------------------------------------------## Hot Standby related parameters are listed in postgresql.conf##---------------------------------------------------------------------------
一些详细的配置可以参考：
http://francs3.blog.163.com/blog/static/40576727201108864230/
页:
[1]