postgresql revision

2 files changed, 153 insertions, 77 deletions

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