From a947a31ede27fdf995e0a63e766fcd68eb491426 Mon Sep 17 00:00:00 2001 From: Silvino Silva Date: Fri, 7 Feb 2020 03:41:45 +0000 Subject: System configuration update --- tools/conf/srv/pgsql/data/pg_hba.conf | 20 ++--- tools/conf/srv/pgsql/data/postgresql.conf | 129 ++++++++++++++++++++++-------- 2 files changed, 104 insertions(+), 45 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 af37ab4..f60af44 100644 --- a/tools/conf/srv/pgsql/data/pg_hba.conf +++ b/tools/conf/srv/pgsql/data/pg_hba.conf @@ -81,20 +81,14 @@ # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only -#local all all trust +local all postgres scram-sha-256 +#local all postgres trust # IPv4 local connections: -#host all all 127.0.0.1/32 trust +host all postgres 127.0.0.1/32 scram-sha-256 # IPv6 local connections: -#host all all ::1/128 trust +host all postgres ::1/128 scram-sha-256 # Allow replication connections from localhost, by a user with the # replication privilege. -#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 +local replication postgres scram-sha-256 +host replication postgres 127.0.0.1/32 scram-sha-256 +host replication postgres ::1/128 scram-sha-256 diff --git a/tools/conf/srv/pgsql/data/postgresql.conf b/tools/conf/srv/pgsql/data/postgresql.conf index e25ab49..4497df9 100644 --- a/tools/conf/srv/pgsql/data/postgresql.conf +++ b/tools/conf/srv/pgsql/data/postgresql.conf @@ -73,7 +73,7 @@ max_connections = 100 # (change requires restart) #bonjour_name = '' # defaults to the computer name # (change requires restart) -# - TCP Keepalives - +# - TCP settings - # see "man 7 tcp" for details #tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds; @@ -82,12 +82,14 @@ max_connections = 100 # (change requires restart) # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default +#tcp_user_timeout = 0 # TCP_USER_TIMEOUT, in milliseconds; + # 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 +password_encryption = scram-sha-256 # md5 or scram-sha-256 #db_user_namespace = off # GSSAPI using Kerberos @@ -107,6 +109,8 @@ 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_min_protocol_version = 'TLSv1' +#ssl_max_protocol_version = '' #ssl_dh_params_file = '' #ssl_passphrase_command = '' #ssl_passphrase_command_supports_reload = off @@ -131,13 +135,18 @@ 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 +#shared_memory_type = mmap # the default is the first option + # supported by the operating system: + # mmap + # sysv + # windows + # (change requires restart) 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 - @@ -152,7 +161,7 @@ dynamic_shared_memory_type = posix # the default is the first option # - Cost-Based Vacuum Delay - -#vacuum_cost_delay = 0 # 0-100 milliseconds +#vacuum_cost_delay = 0 # 0-100 milliseconds (0 disables) #vacuum_cost_page_hit = 1 # 0-10000 credits #vacuum_cost_page_miss = 10 # 0-10000 credits #vacuum_cost_page_dirty = 20 # 0-10000 credits @@ -203,6 +212,8 @@ dynamic_shared_memory_type = posix # the default is the first option #wal_compression = off # enable compression of full-page writes #wal_log_hints = off # also do full page writes of non-critical updates # (change requires restart) +#wal_init_zero = on # zero-fill new WAL files +#wal_recycle = on # recycle WAL files #wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers # (change requires restart) #wal_writer_delay = 200ms # 1-10000 milliseconds @@ -231,6 +242,42 @@ min_wal_size = 80MB #archive_timeout = 0 # force a logfile segment switch after this # number of seconds; 0 disables +# - Archive Recovery - + +# These are only used in recovery mode. + +#restore_command = '' # command to use to restore an archived logfile segment + # placeholders: %p = path of file to restore + # %f = file name only + # e.g. 'cp /mnt/server/archivedir/%f %p' + # (change requires restart) +#archive_cleanup_command = '' # command to execute at every restartpoint +#recovery_end_command = '' # command to execute at completion of recovery + +# - Recovery Target - + +# Set these only when performing a targeted recovery. + +#recovery_target = '' # 'immediate' to end recovery as soon as a + # consistent state is reached + # (change requires restart) +#recovery_target_name = '' # the named restore point to which recovery will proceed + # (change requires restart) +#recovery_target_time = '' # the time stamp up to which recovery will proceed + # (change requires restart) +#recovery_target_xid = '' # the transaction ID up to which recovery will proceed + # (change requires restart) +#recovery_target_lsn = '' # the WAL LSN up to which recovery will proceed + # (change requires restart) +#recovery_target_inclusive = on # Specifies whether to stop: + # just after the specified recovery target (on) + # just before the recovery target (off) + # (change requires restart) +#recovery_target_timeline = 'latest' # 'current', 'latest', or timeline ID + # (change requires restart) +#recovery_target_action = 'pause' # 'pause', 'promote', 'shutdown' + # (change requires restart) + #------------------------------------------------------------------------------ # REPLICATION @@ -264,6 +311,11 @@ min_wal_size = 80MB # These settings are ignored on a master server. +#primary_conninfo = '' # connection string to sending server + # (change requires restart) +#primary_slot_name = '' # replication slot on sending server + # (change requires restart) +#promote_trigger_file = '' # file name whose presence ends recovery #hot_standby = on # "off" disallows queries during recovery # (change requires restart) #max_standby_archive_delay = 30s # max delay before canceling queries @@ -281,6 +333,7 @@ min_wal_size = 80MB # in milliseconds; 0 disables #wal_retrieve_retry_interval = 5s # time to wait before retrying to # retrieve WAL after a failed attempt +#recovery_min_apply_delay = 0 # minimum delay for applying changes during recovery # - Subscribers - @@ -356,7 +409,10 @@ min_wal_size = 80MB #join_collapse_limit = 8 # 1 disables collapsing of explicit # JOIN clauses #force_parallel_mode = off -#jit = off # allow JIT compilation +#jit = on # allow JIT compilation +jit = off # allow JIT compilation +#plan_cache_mode = auto # auto, force_generic_plan or + # force_custom_plan #------------------------------------------------------------------------------ @@ -365,9 +421,8 @@ min_wal_size = 80MB # - Where to Log - -#log_destination = 'stderr' # Valid values are combinations of -#log_destination = 'stderr,syslog' # Multiple are valide -log_destination = 'syslog' +#log_destination = 'stderr' +#log_destination = 'syslog' # Valid values are combinations of # stderr, csvlog, syslog, and eventlog, # depending on platform. csvlog # requires logging_collector to be on. @@ -400,7 +455,6 @@ log_destination = 'syslog' # 0 disables. # These are relevant when logging to syslog: -#syslog_facility = 'LOCAL0' syslog_facility = 'LOCAL0' syslog_ident = 'postgres' #syslog_sequence_numbers = on @@ -412,17 +466,6 @@ syslog_ident = 'postgres' # - When to Log - -#client_min_messages = notice # values in order of decreasing detail: - # debug5 - # debug4 - # debug3 - # debug2 - # debug1 - # log - # notice - # warning - # error - #log_min_messages = warning # values in order of decreasing detail: # debug5 # debug4 @@ -456,6 +499,9 @@ syslog_ident = 'postgres' # statements running at least this number # of milliseconds +#log_transaction_sample_rate = 0.0 # Fraction of transactions whose statements + # are logged regardless of their duration. 1.0 logs all + # statements from all transactions, 0.0 never logs. # - What to Log - @@ -464,12 +510,15 @@ syslog_ident = 'postgres' #debug_print_plan = off #debug_pretty_print = on #log_checkpoints = off +#log_connections = off log_connections = on +#log_disconnections = off log_disconnections = on -log_duration = on +#log_duration = off #log_error_verbosity = default # terse, default, or verbose messages +#log_hostname = off log_hostname = on -#log_line_prefix = '%m [%p] ' # special values: +log_line_prefix = 'd=$d u=% %m [%p] ' # special values: # %a = application name # %u = user name # %d = database name @@ -492,11 +541,12 @@ log_hostname = on # e.g. '<%u%%%d> ' #log_lock_waits = off # log lock waits >= deadlock_timeout #log_statement = 'none' # none, ddl, mod, all +log_statement = 'mod' # none, ddl, mod, all #log_replication_commands = off #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 = 'Portugal' +log_timezone = 'Europe/Lisbon' #------------------------------------------------------------------------------ # PROCESS TITLE @@ -553,7 +603,7 @@ log_timezone = 'Portugal' #autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age # before forced vacuum # (change requires restart) -#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for +#autovacuum_vacuum_cost_delay = 2ms # default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for @@ -567,11 +617,22 @@ log_timezone = 'Portugal' # - Statement Behavior - +#client_min_messages = notice # values in order of decreasing detail: + # debug5 + # debug4 + # debug3 + # debug2 + # debug1 + # log + # notice + # warning + # error #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 +#default_table_access_method = 'heap' #check_function_bodies = on #default_transaction_isolation = 'read committed' #default_transaction_read_only = off @@ -597,7 +658,7 @@ log_timezone = 'Portugal' datestyle = 'iso, mdy' #intervalstyle = 'postgres' -timezone = 'Portugal' +timezone = 'Europe/Lisbon' #timezone_abbreviations = 'Default' # Select the set of available time zone # abbreviations. Currently, there are # Default @@ -605,7 +666,8 @@ timezone = 'Portugal' # India # You can create your own file in # share/timezonesets/. -#extra_float_digits = 0 # min -15, max 3 +#extra_float_digits = 1 # min -15, max 3; any value >0 actually + # selects precise output mode #client_encoding = sql_ascii # actually, defaults to database # encoding @@ -654,7 +716,6 @@ default_text_search_config = 'pg_catalog.english' #array_nulls = on #backslash_quote = safe_encoding # on, off, or safe_encoding -#default_with_oids = off #escape_string_warning = on #lo_compat_privileges = off #operator_precedence_warning = off @@ -673,6 +734,9 @@ default_text_search_config = 'pg_catalog.english' #exit_on_error = off # terminate session on any error? #restart_after_crash = on # reinitialize after backend crash? +#data_sync_retry = off # retry or panic on failure to fsync + # data? + # (change requires restart) #------------------------------------------------------------------------------ @@ -680,12 +744,13 @@ default_text_search_config = 'pg_catalog.english' #------------------------------------------------------------------------------ # These options allow settings to be loaded from files other than the -# default postgresql.conf. +# default postgresql.conf. Note that these are directives, not variable +# assignments, so they can usefully be given more than once. -#include_dir = 'conf.d' # include files ending in '.conf' from - # directory 'conf.d' -#include_if_exists = 'exists.conf' # include file only if it exists -#include = 'special.conf' # include file +#include_dir = '...' # include files ending in '.conf' from + # a directory, e.g., 'conf.d' +#include_if_exists = '...' # include file only if it exists +#include = '...' # include file #------------------------------------------------------------------------------ -- cgit 1.4.1-2-gfad0