From 1203067a4e33c9ca6897dfa40a13a80454a40220 Mon Sep 17 00:00:00 2001 From: Silvino Silva Date: Wed, 6 Mar 2019 21:19:54 +0000 Subject: postgresql revision --- tools/conf/srv/pgsql/data/pg_hba.conf | 36 +++--- tools/conf/srv/pgsql/data/postgresql.conf | 194 ++++++++++++++++++++---------- 2 files changed, 153 insertions(+), 77 deletions(-) (limited to 'tools/conf/srv/pgsql/data') diff --git a/tools/conf/srv/pgsql/data/pg_hba.conf b/tools/conf/srv/pgsql/data/pg_hba.conf index 55ce3f3..af37ab4 100644 --- a/tools/conf/srv/pgsql/data/pg_hba.conf +++ b/tools/conf/srv/pgsql/data/pg_hba.conf @@ -42,10 +42,10 @@ # 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", -# "ident", "peer", "pam", "ldap", "radius" or "cert". Note that -# "password" sends passwords in clear text; "md5" is preferred since -# it sends encrypted passwords. +# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256", +# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert". +# Note that "password" sends passwords in clear text; "md5" or +# "scram-sha-256" are preferred since they send encrypted passwords. # # OPTIONS are a set of options for the authentication in the format # NAME=VALUE. The available options depend on the different @@ -59,11 +59,11 @@ # 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. - +# 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, run "pg_ctl reload", +# or execute "SELECT pg_reload_conf()". +# # Put your actual configuration here # ---------------------------------- # @@ -82,15 +82,19 @@ # "local" is for Unix domain socket connections only #local all all trust -local all postgres ident # IPv4 local connections: -host all all 127.0.0.1/32 trust -#hostssl all all 192.168.0.0/32 md5 - +#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. -#local replication postgres trust -#host replication postgres 127.0.0.1/32 trust -#host replication postgres ::1/128 trust +#local replication all trust +#host replication all 127.0.0.1/32 trust +#host replication all ::1/128 trust + +# TYPE DATABASE USER ADDRESS METHOD +local postgres postgres trust +host postgres postgres 127.0.0.1/32 trust +host db_flyspray flyspray 127.0.0.1/32 md5 +host all all 127.0.0.1/32 scram-sha-256 +host all all 0.0.0.0/0 reject diff --git a/tools/conf/srv/pgsql/data/postgresql.conf b/tools/conf/srv/pgsql/data/postgresql.conf index df3525c..e25ab49 100644 --- a/tools/conf/srv/pgsql/data/postgresql.conf +++ b/tools/conf/srv/pgsql/data/postgresql.conf @@ -16,9 +16,9 @@ # # 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. +# server for the changes to take effect, run "pg_ctl reload", or execute +# "SELECT pg_reload_conf()". 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 @@ -73,26 +73,6 @@ max_connections = 100 # (change requires restart) #bonjour_name = '' # defaults to the computer name # (change requires restart) -# - Security and Authentication - - -#authentication_timeout = 1min # 1s-600s -ssl = on # (change requires restart) -#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers - # (change requires restart) -#ssl_prefer_server_ciphers = on # (change requires restart) -#ssl_ecdh_curve = 'prime256v1' # (change requires restart) -ssl_cert_file = '/etc/ssl/certs/pg.crt' # (change requires restart) -ssl_key_file = '/etc/ssl/keys/pg.key' # (change requires restart) -#ssl_ca_file = '' # (change requires restart) -#ssl_crl_file = '' # (change requires restart) -password_encryption = on -#db_user_namespace = off -#row_security = on - -# GSSAPI using Kerberos -#krb_server_keyfile = '' -#krb_caseins_users = off - # - TCP Keepalives - # see "man 7 tcp" for details @@ -103,6 +83,34 @@ password_encryption = on #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default +# - Authentication - + +#authentication_timeout = 1min # 1s-600s +#password_encryption = md5 # md5 or scram-sha-256 +password_encryption = scram-sha-256 # md5 or scram-sha-256 +#db_user_namespace = off + +# GSSAPI using Kerberos +#krb_server_keyfile = '' +#krb_caseins_users = off + +# - SSL - + +#ssl = off +ssl = on +#ssl_ca_file = '' +#ssl_cert_file = 'server.crt' +ssl_cert_file = '/etc/ssl/certs/pg.crt' +#ssl_crl_file = '' +#ssl_key_file = 'server.key' +ssl_key_file = '/etc/ssl/keys/pg.key' +#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers +#ssl_prefer_server_ciphers = on +#ssl_ecdh_curve = 'prime256v1' +#ssl_dh_params_file = '' +#ssl_passphrase_command = '' +#ssl_passphrase_command_supports_reload = off + #------------------------------------------------------------------------------ # RESOURCE USAGE (except WAL) @@ -123,24 +131,24 @@ shared_buffers = 128MB # min 128kB #maintenance_work_mem = 64MB # min 1MB #autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem #max_stack_depth = 2MB # min 100kB -dynamic_shared_memory_type = sysv # the default is the first option +dynamic_shared_memory_type = posix # the default is the first option # supported by the operating system: # posix # sysv # windows # mmap # use none to disable dynamic shared memory + # (change requires restart) # - Disk - -#temp_file_limit = -1 # limits per-session temp file space +#temp_file_limit = -1 # limits per-process temp file space # in kB, or -1 for no limit -# - Kernel Resource Usage - +# - Kernel Resources - #max_files_per_process = 1000 # min 25 # (change requires restart) -#shared_preload_libraries = '' # (change requires restart) # - Cost-Based Vacuum Delay - @@ -153,26 +161,37 @@ dynamic_shared_memory_type = sysv # the default is the first option # - 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 +#bgwriter_lru_maxpages = 100 # max buffers written/round, 0 disables +#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round +#bgwriter_flush_after = 512kB # measured in pages, 0 disables # - Asynchronous Behavior - #effective_io_concurrency = 1 # 1-1000; 0 disables prefetching -#max_worker_processes = 8 +#max_worker_processes = 8 # (change requires restart) +#max_parallel_maintenance_workers = 2 # taken from max_parallel_workers +#max_parallel_workers_per_gather = 2 # taken from max_parallel_workers +#parallel_leader_participation = on +#max_parallel_workers = 8 # maximum number of max_worker_processes that + # can be used in parallel operations +#old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate + # (change requires restart) +#backend_flush_after = 0 # measured in pages, 0 disables #------------------------------------------------------------------------------ -# WRITE AHEAD LOG +# WRITE-AHEAD LOG #------------------------------------------------------------------------------ # - Settings - -#wal_level = minimal # minimal, archive, hot_standby, or logical +#wal_level = replica # minimal, replica, or logical # (change requires restart) -#fsync = on # turns forced synchronization on or off +#fsync = on # flush data to disk for crash safety + # (turning this off can cause + # unrecoverable data corruption) #synchronous_commit = on # synchronization level; - # off, local, remote_write, or on + # off, local, remote_write, remote_apply, or on #wal_sync_method = fsync # the default is the first option # supported by the operating system: # open_datasync @@ -187,16 +206,18 @@ dynamic_shared_memory_type = sysv # the default is the first option #wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers # (change requires restart) #wal_writer_delay = 200ms # 1-10000 milliseconds +#wal_writer_flush_after = 1MB # measured in pages, 0 disables #commit_delay = 0 # range 0-100000, in microseconds #commit_siblings = 5 # range 1-1000 # - Checkpoints - -#checkpoint_timeout = 5min # range 30s-1h -#max_wal_size = 1GB -#min_wal_size = 80MB +#checkpoint_timeout = 5min # range 30s-1d +max_wal_size = 1GB +min_wal_size = 80MB #checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0 +#checkpoint_flush_after = 256kB # measured in pages, 0 disables #checkpoint_warning = 30s # 0 disables # - Archiving - @@ -215,16 +236,16 @@ dynamic_shared_memory_type = sysv # the default is the first option # REPLICATION #------------------------------------------------------------------------------ -# - Sending Server(s) - +# - Sending Servers - # Set these on the master and on any standby that will send replication data. -#max_wal_senders = 0 # max number of walsender processes +#max_wal_senders = 10 # max number of walsender processes # (change requires restart) -#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables +#wal_keep_segments = 0 # in logfile segments; 0 disables #wal_sender_timeout = 60s # in milliseconds; 0 disables -#max_replication_slots = 0 # max number of replication slots +#max_replication_slots = 10 # max number of replication slots # (change requires restart) #track_commit_timestamp = off # collect timestamp of transaction commit # (change requires restart) @@ -234,7 +255,8 @@ dynamic_shared_memory_type = sysv # the default is the first option # These settings are ignored on a standby server. #synchronous_standby_names = '' # standby servers that provide sync rep - # comma-separated list of application_name + # method to choose sync standbys, number of sync standbys, + # and comma-separated list of application_name # from standby(s); '*' = all #vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed @@ -242,7 +264,7 @@ dynamic_shared_memory_type = sysv # the default is the first option # These settings are ignored on a master server. -#hot_standby = off # "on" allows queries during recovery +#hot_standby = on # "off" disallows queries during recovery # (change requires restart) #max_standby_archive_delay = 30s # max delay before canceling queries # when reading WAL from archive; @@ -260,6 +282,14 @@ dynamic_shared_memory_type = sysv # the default is the first option #wal_retrieve_retry_interval = 5s # time to wait before retrying to # retrieve WAL after a failed attempt +# - Subscribers - + +# These settings are ignored on a publisher. + +#max_logical_replication_workers = 4 # taken from max_worker_processes + # (change requires restart) +#max_sync_workers_per_subscription = 2 # taken from max_logical_replication_workers + #------------------------------------------------------------------------------ # QUERY TUNING @@ -275,9 +305,14 @@ dynamic_shared_memory_type = sysv # the default is the first option #enable_material = on #enable_mergejoin = on #enable_nestloop = on +#enable_parallel_append = on #enable_seqscan = on #enable_sort = on #enable_tidscan = on +#enable_partitionwise_join = off +#enable_partitionwise_aggregate = off +#enable_parallel_hash = on +#enable_partition_pruning = on # - Planner Cost Constants - @@ -286,6 +321,20 @@ dynamic_shared_memory_type = sysv # the default is the first option #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 +#parallel_tuple_cost = 0.1 # same scale as above +#parallel_setup_cost = 1000.0 # same scale as above + +#jit_above_cost = 100000 # perform JIT compilation if available + # and query more expensive than this; + # -1 disables +#jit_inline_above_cost = 500000 # inline small functions if query is + # more expensive than this; -1 disables +#jit_optimize_above_cost = 500000 # use expensive JIT optimizations if + # query is more expensive than this; + # -1 disables + +#min_parallel_table_scan_size = 8MB +#min_parallel_index_scan_size = 512kB #effective_cache_size = 4GB # - Genetic Query Optimizer - @@ -306,15 +355,19 @@ dynamic_shared_memory_type = sysv # the default is the first option #from_collapse_limit = 8 #join_collapse_limit = 8 # 1 disables collapsing of explicit # JOIN clauses +#force_parallel_mode = off +#jit = off # allow JIT compilation #------------------------------------------------------------------------------ -# ERROR REPORTING AND LOGGING +# REPORTING AND LOGGING #------------------------------------------------------------------------------ # - Where to Log - #log_destination = 'stderr' # Valid values are combinations of +#log_destination = 'stderr,syslog' # Multiple are valide +log_destination = 'syslog' # stderr, csvlog, syslog, and eventlog, # depending on platform. csvlog # requires logging_collector to be on. @@ -326,7 +379,7 @@ dynamic_shared_memory_type = sysv # the default is the first option # (change requires restart) # These are only used if logging_collector is on: -#log_directory = 'pg_log' # directory where log files are written, +#log_directory = 'log' # directory where log files are written, # can be absolute or relative to PGDATA #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern, # can include strftime() escapes @@ -348,9 +401,13 @@ dynamic_shared_memory_type = sysv # the default is the first option # These are relevant when logging to syslog: #syslog_facility = 'LOCAL0' -#syslog_ident = 'postgres' +syslog_facility = 'LOCAL0' +syslog_ident = 'postgres' +#syslog_sequence_numbers = on +#syslog_split_messages = on # This is only relevant when logging to eventlog (win32): +# (change requires restart) #event_source = 'PostgreSQL' # - When to Log - @@ -407,12 +464,12 @@ dynamic_shared_memory_type = sysv # the default is the first option #debug_print_plan = off #debug_pretty_print = on #log_checkpoints = off -#log_connections = off -#log_disconnections = off -#log_duration = off +log_connections = on +log_disconnections = on +log_duration = on #log_error_verbosity = default # terse, default, or verbose messages -#log_hostname = off -#log_line_prefix = '' # special values: +log_hostname = on +#log_line_prefix = '%m [%p] ' # special values: # %a = application name # %u = user name # %d = database name @@ -421,6 +478,7 @@ dynamic_shared_memory_type = sysv # the default is the first option # %p = process ID # %t = timestamp without milliseconds # %m = timestamp with milliseconds + # %n = timestamp with milliseconds (as a Unix epoch) # %i = command tag # %e = SQL state # %c = session ID @@ -440,8 +498,9 @@ dynamic_shared_memory_type = sysv # the default is the first option # -1 disables, 0 logs all temp files log_timezone = 'Portugal' - -# - Process Title - +#------------------------------------------------------------------------------ +# PROCESS TITLE +#------------------------------------------------------------------------------ #cluster_name = '' # added to process titles if nonempty # (change requires restart) @@ -449,10 +508,10 @@ log_timezone = 'Portugal' #------------------------------------------------------------------------------ -# RUNTIME STATISTICS +# STATISTICS #------------------------------------------------------------------------------ -# - Query/Index Statistics Collector - +# - Query and Index Statistics Collector - #track_activities = on #track_counts = on @@ -462,7 +521,7 @@ log_timezone = 'Portugal' #stats_temp_directory = 'pg_stat_tmp' -# - Statistics Monitoring - +# - Monitoring - #log_parser_stats = off #log_planner_stats = off @@ -471,7 +530,7 @@ log_timezone = 'Portugal' #------------------------------------------------------------------------------ -# AUTOVACUUM PARAMETERS +# AUTOVACUUM #------------------------------------------------------------------------------ #autovacuum = on # Enable autovacuum subprocess? 'on' @@ -509,6 +568,7 @@ log_timezone = 'Portugal' # - Statement Behavior - #search_path = '"$user", public' # schema names +#row_security = on #default_tablespace = '' # a tablespace name, '' uses the default #temp_tablespaces = '' # a list of tablespace names, '' uses # only default tablespace @@ -519,10 +579,14 @@ log_timezone = 'Portugal' #session_replication_role = 'origin' #statement_timeout = 0 # in milliseconds, 0 is disabled #lock_timeout = 0 # in milliseconds, 0 is disabled +#idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled #vacuum_freeze_min_age = 50000000 #vacuum_freeze_table_age = 150000000 #vacuum_multixact_freeze_min_age = 5000000 #vacuum_multixact_freeze_table_age = 150000000 +#vacuum_cleanup_index_scale_factor = 0.1 # fraction of total number of tuples + # before index cleanup, 0 always performs + # index cleanup #bytea_output = 'hex' # hex, escape #xmlbinary = 'base64' #xmloption = 'content' @@ -555,11 +619,16 @@ lc_time = 'C' # locale for time formatting # default configuration for text search default_text_search_config = 'pg_catalog.english' -# - Other Defaults - +# - Shared Library Preloading - -#dynamic_library_path = '$libdir' +#shared_preload_libraries = '' # (change requires restart) #local_preload_libraries = '' #session_preload_libraries = '' +#jit_provider = 'llvmjit' # JIT library to use + +# - Other Defaults - + +#dynamic_library_path = '$libdir' #------------------------------------------------------------------------------ @@ -571,10 +640,14 @@ default_text_search_config = 'pg_catalog.english' # (change requires restart) #max_pred_locks_per_transaction = 64 # min 10 # (change requires restart) +#max_pred_locks_per_relation = -2 # negative values mean + # (max_pred_locks_per_transaction + # / -max_pred_locks_per_relation) - 1 +#max_pred_locks_per_page = 2 # min 0 #------------------------------------------------------------------------------ -# VERSION/PLATFORM COMPATIBILITY +# VERSION AND PLATFORM COMPATIBILITY #------------------------------------------------------------------------------ # - Previous PostgreSQL Versions - @@ -586,7 +659,6 @@ default_text_search_config = 'pg_catalog.english' #lo_compat_privileges = off #operator_precedence_warning = off #quote_all_identifiers = off -#sql_inheritance = on #standard_conforming_strings = on #synchronize_seqscans = on -- cgit 1.4.1-2-gfad0