From 3cfaf62fe7d06d6b5a23fd1b40df072a25321ecf Mon Sep 17 00:00:00 2001 From: Frank Brehm Date: Thu, 22 Aug 2019 00:12:21 +0200 Subject: [PATCH] daily autocommit --- postgresql/11/main/pg_hba.conf | 26 ++-- postgresql/11/main/postgresql.conf | 195 +++++++++++----------------- postgresql/9.6/main/postgresql.conf | 2 +- postgresql/9.6/main/start.conf | 11 +- 4 files changed, 92 insertions(+), 142 deletions(-) diff --git a/postgresql/11/main/pg_hba.conf b/postgresql/11/main/pg_hba.conf index f07bb73..84119e3 100644 --- a/postgresql/11/main/pg_hba.conf +++ b/postgresql/11/main/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", "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. +# 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. # # 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 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()". -# +# 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 # ---------------------------------- # @@ -87,6 +87,8 @@ local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only +local all root md5 +local all icinga2 md5 local all all peer # IPv4 local connections: host all all 127.0.0.1/32 md5 @@ -94,6 +96,6 @@ host all all 127.0.0.1/32 md5 host all all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. -local replication all peer -host replication all 127.0.0.1/32 md5 -host replication all ::1/128 md5 +#local replication postgres peer +#host replication postgres 127.0.0.1/32 md5 +#host replication postgres ::1/128 md5 diff --git a/postgresql/11/main/postgresql.conf b/postgresql/11/main/postgresql.conf index d57d694..11d1914 100644 --- a/postgresql/11/main/postgresql.conf +++ b/postgresql/11/main/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, 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. +# 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 @@ -60,7 +60,7 @@ external_pid_file = '/var/run/postgresql/11-main.pid' # write an extra PID fil # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) -port = 5433 # (change requires restart) +port = 5432 # (change requires restart) max_connections = 100 # (change requires restart) #superuser_reserved_connections = 3 # (change requires restart) unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories @@ -73,39 +73,35 @@ unix_socket_directories = '/var/run/postgresql' # comma-separated list of direct #bonjour_name = '' # defaults to the computer name # (change requires restart) -# - 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 - -# - Authentication - +# - Security and Authentication - #authentication_timeout = 1min # 1s-600s -#password_encryption = md5 # md5 or scram-sha-256 +ssl = true # (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/ssl-cert-snakeoil.pem' # (change requires restart) +ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.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 -# - SSL - +# - TCP Keepalives - +# see "man 7 tcp" for details -ssl = on -#ssl_ca_file = '' -ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' -#ssl_crl_file = '' -ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.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 +#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 #------------------------------------------------------------------------------ @@ -125,6 +121,7 @@ shared_buffers = 128MB # min 128kB # you actively intend to use prepared transactions. #work_mem = 4MB # min 64kB #maintenance_work_mem = 64MB # min 1MB +#replacement_sort_tuples = 150000 # limits use of replacement selection sort #autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem #max_stack_depth = 2MB # min 100kB dynamic_shared_memory_type = posix # the default is the first option @@ -134,17 +131,17 @@ dynamic_shared_memory_type = posix # the default is the first option # windows # mmap # use none to disable dynamic shared memory - # (change requires restart) # - Disk - #temp_file_limit = -1 # limits per-process temp file space # in kB, or -1 for no limit -# - Kernel Resources - +# - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 # (change requires restart) +#shared_preload_libraries = '' # (change requires restart) # - Cost-Based Vacuum Delay - @@ -157,7 +154,7 @@ dynamic_shared_memory_type = posix # the default is the first option # - Background Writer - #bgwriter_delay = 200ms # 10-10000ms between rounds -#bgwriter_lru_maxpages = 100 # max buffers written/round, 0 disables +#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round #bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round #bgwriter_flush_after = 512kB # measured in pages, 0 disables @@ -165,27 +162,23 @@ dynamic_shared_memory_type = posix # the default is the first option #effective_io_concurrency = 1 # 1-1000; 0 disables prefetching #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 +#max_parallel_workers_per_gather = 0 # taken from max_worker_processes #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 = replica # minimal, replica, or logical +#wal_level = minimal # minimal, replica, or logical # (change requires restart) #fsync = on # flush data to disk for crash safety - # (turning this off can cause - # unrecoverable data corruption) + # (turning this off can cause + # unrecoverable data corruption) #synchronous_commit = on # synchronization level; # off, local, remote_write, remote_apply, or on #wal_sync_method = fsync # the default is the first option @@ -210,8 +203,8 @@ dynamic_shared_memory_type = posix # the default is the first option # - Checkpoints - #checkpoint_timeout = 5min # range 30s-1d -max_wal_size = 1GB -min_wal_size = 80MB +#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 @@ -232,16 +225,16 @@ min_wal_size = 80MB # REPLICATION #------------------------------------------------------------------------------ -# - Sending Servers - +# - Sending Server(s) - # Set these on the master and on any standby that will send replication data. -#max_wal_senders = 10 # max number of walsender processes +#max_wal_senders = 0 # max number of walsender processes # (change requires restart) -#wal_keep_segments = 0 # in logfile segments; 0 disables +#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables #wal_sender_timeout = 60s # in milliseconds; 0 disables -#max_replication_slots = 10 # max number of replication slots +#max_replication_slots = 0 # max number of replication slots # (change requires restart) #track_commit_timestamp = off # collect timestamp of transaction commit # (change requires restart) @@ -251,8 +244,7 @@ min_wal_size = 80MB # These settings are ignored on a standby server. #synchronous_standby_names = '' # standby servers that provide sync rep - # method to choose sync standbys, number of sync standbys, - # and comma-separated list of application_name + # 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 @@ -260,7 +252,7 @@ min_wal_size = 80MB # These settings are ignored on a master server. -#hot_standby = on # "off" disallows queries during recovery +#hot_standby = off # "on" allows queries during recovery # (change requires restart) #max_standby_archive_delay = 30s # max delay before canceling queries # when reading WAL from archive; @@ -278,14 +270,6 @@ min_wal_size = 80MB #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 @@ -301,14 +285,9 @@ min_wal_size = 80MB #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 - @@ -319,18 +298,7 @@ min_wal_size = 80MB #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 +#min_parallel_relation_size = 8MB #effective_cache_size = 4GB # - Genetic Query Optimizer - @@ -352,11 +320,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 #------------------------------------------------------------------------------ -# REPORTING AND LOGGING +# ERROR REPORTING AND LOGGING #------------------------------------------------------------------------------ # - Where to Log - @@ -373,7 +340,7 @@ min_wal_size = 80MB # (change requires restart) # These are only used if logging_collector is on: -#log_directory = 'log' # directory where log files are written, +#log_directory = 'pg_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 @@ -400,11 +367,21 @@ min_wal_size = 80MB #syslog_split_messages = on # This is only relevant when logging to eventlog (win32): -# (change requires restart) #event_source = 'PostgreSQL' # - 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 @@ -451,7 +428,7 @@ min_wal_size = 80MB #log_duration = off #log_error_verbosity = default # terse, default, or verbose messages #log_hostname = off -log_line_prefix = '%m [%p] %q%u@%d ' # special values: +log_line_prefix = '%m [%p] %q%u@%d ' # special values: # %a = application name # %u = user name # %d = database name @@ -478,11 +455,10 @@ log_line_prefix = '%m [%p] %q%u@%d ' # special values: #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 = 'Europe/Berlin' +log_timezone = 'localtime' -#------------------------------------------------------------------------------ -# PROCESS TITLE -#------------------------------------------------------------------------------ + +# - Process Title - cluster_name = '11/main' # added to process titles if nonempty # (change requires restart) @@ -490,10 +466,10 @@ cluster_name = '11/main' # added to process titles if nonempty #------------------------------------------------------------------------------ -# STATISTICS +# RUNTIME STATISTICS #------------------------------------------------------------------------------ -# - Query and Index Statistics Collector - +# - Query/Index Statistics Collector - #track_activities = on #track_counts = on @@ -503,7 +479,7 @@ cluster_name = '11/main' # added to process titles if nonempty stats_temp_directory = '/var/run/postgresql/11-main.pg_stat_tmp' -# - Monitoring - +# - Statistics Monitoring - #log_parser_stats = off #log_planner_stats = off @@ -512,7 +488,7 @@ stats_temp_directory = '/var/run/postgresql/11-main.pg_stat_tmp' #------------------------------------------------------------------------------ -# AUTOVACUUM +# AUTOVACUUM PARAMETERS #------------------------------------------------------------------------------ #autovacuum = on # Enable autovacuum subprocess? 'on' @@ -549,18 +525,7 @@ stats_temp_directory = '/var/run/postgresql/11-main.pg_stat_tmp' # - 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 @@ -571,14 +536,11 @@ stats_temp_directory = '/var/run/postgresql/11-main.pg_stat_tmp' #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 +#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' @@ -589,7 +551,7 @@ stats_temp_directory = '/var/run/postgresql/11-main.pg_stat_tmp' datestyle = 'iso, dmy' #intervalstyle = 'postgres' -timezone = 'Europe/Berlin' +timezone = 'localtime' #timezone_abbreviations = 'Default' # Select the set of available time zone # abbreviations. Currently, there are # Default @@ -611,16 +573,11 @@ lc_time = 'de_DE.UTF-8' # locale for time formatting # default configuration for text search default_text_search_config = 'pg_catalog.german' -# - Shared Library Preloading - - -#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' +#local_preload_libraries = '' +#session_preload_libraries = '' #------------------------------------------------------------------------------ @@ -632,14 +589,10 @@ default_text_search_config = 'pg_catalog.german' # (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 AND PLATFORM COMPATIBILITY +# VERSION/PLATFORM COMPATIBILITY #------------------------------------------------------------------------------ # - Previous PostgreSQL Versions - @@ -651,6 +604,7 @@ default_text_search_config = 'pg_catalog.german' #lo_compat_privileges = off #operator_precedence_warning = off #quote_all_identifiers = off +#sql_inheritance = on #standard_conforming_strings = on #synchronize_seqscans = on @@ -665,9 +619,6 @@ default_text_search_config = 'pg_catalog.german' #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) #------------------------------------------------------------------------------ @@ -677,10 +628,10 @@ default_text_search_config = 'pg_catalog.german' # These options allow settings to be loaded from files other than the # default postgresql.conf. -include_dir = 'conf.d' # include files ending in '.conf' from - # a directory, e.g., 'conf.d' -#include_if_exists = '' # include file only if it exists -#include = '' # include file +#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 #------------------------------------------------------------------------------ diff --git a/postgresql/9.6/main/postgresql.conf b/postgresql/9.6/main/postgresql.conf index d7a0ae9..0500e74 100644 --- a/postgresql/9.6/main/postgresql.conf +++ b/postgresql/9.6/main/postgresql.conf @@ -60,7 +60,7 @@ external_pid_file = '/var/run/postgresql/9.6-main.pid' # write an extra PID fi # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) -port = 5432 # (change requires restart) +port = 5433 # (change requires restart) max_connections = 100 # (change requires restart) #superuser_reserved_connections = 3 # (change requires restart) unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories diff --git a/postgresql/9.6/main/start.conf b/postgresql/9.6/main/start.conf index b0f3256..5bbbe20 100644 --- a/postgresql/9.6/main/start.conf +++ b/postgresql/9.6/main/start.conf @@ -1,8 +1,5 @@ -# Automatic startup configuration -# auto: automatically start the cluster -# manual: manual startup with pg_ctlcluster/postgresql@.service only -# disabled: refuse to start cluster -# See pg_createcluster(1) for details. When running from systemd, -# invoke 'systemctl daemon-reload' after editing this file. +# This cluster was upgraded to a newer major version. The old +# cluster has been preserved for backup purposes, but is not started +# automatically. -auto +manual -- 2.39.5