#! /bin/sh #------------------------------------------------------------------------------ # /var/install/config.d/postgresql18.sh - apply configuration # # Creation : 2017-05-28 daniel # Last Update: 2025-12-16 23:07:04 # # Copyright (c) 2025 the eisfair team, team(at)eisfair(dot)org # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. #------------------------------------------------------------------------------ #------------------------------------------------------------------------------ # include eisfair script library and configuration #------------------------------------------------------------------------------ package='postgresql18' . /var/install/include/eislib . /etc/config.d/${package} crontab_path="/var/cron/etc/root" crontab_file=${crontab_path}/${package} spacestr=" " inst_base='/usr/lib64/pgsql' inst_dir='/usr/lib64/pgsql/18' # --------------------------------------------------------------------------- # read command line for quietmode option # --------------------------------------------------------------------------- quietmode=false quietopt="" while [ ${#} -gt 0 ] do case ${1} in --quiet) quietmode=true quietopt="--quiet" shift ;; esac done # ----------------------------------------------------------------------------- # update timer for backup job # ----------------------------------------------------------------------------- update_backup_timer() { mkdir -p /etc/systemd/system/${package}-backup.timer.d # we must write this file even if START_POSTGRESQL18 != yes to ensure # a valid setting. Otherwise systemd complains { echo "[Timer]" echo "OnCalendar=${POSTGRESQL18_BACKUP_TIMER}" } > "/etc/systemd/system/${package}-backup.timer.d/${package}-backup.conf" if [ "${START_POSTGRESQL18}" = "yes" ] && \ (([ "${POSTGRESQL18_BACKUP_DATABASES}" = "yes" ] && [ "$POSTGRESQL18_BACKUP_N" -gt "0" ]) || \ [ "${POSTGRESQL18_BACKUP_CLUSTER}" = "yes" ]) then # enable and start timer if /usr/sbin/service is-enabled ${package}-backup.timer then /usr/sbin/service daemon-reload /usr/sbin/service ${quietopt} restart ${package}-backup.timer else /usr/sbin/service enable ${package}-backup.timer /usr/sbin/service ${quietopt} start ${package}-backup.timer fi else # stop and disable timer if /usr/sbin/service is-enabled ${package}-backup.timer then /usr/sbin/service daemon-reload /usr/sbin/service ${quietopt} stop ${package}-backup.timer /usr/sbin/service disable ${package}-backup.timer fi fi } #------------------------------------------------------------------------------ # Write a value into a field with a specified width # $1 value # $2 length of field #------------------------------------------------------------------------------ printfield() { value=$1 fieldwidth=$2 if [ -z "${value}" ] then echo -n "${spacestr:0:$fieldwidth}" else vallen=${#value} strlen=`expr $fieldwidth - $vallen` if [ "$strlen" -lt "1" ] then strlen="1" fi echo -n "$value${spacestr:0:$strlen}" fi } #------------------------------------------------------------------------------ # Check if SSL settings are valid #------------------------------------------------------------------------------ verify_ssl_settings() { if [ "${POSTGRESQL18_ENABLE_SSL}" = "yes" ] then if [ -f "/etc/ssl/private/${POSTGRESQL18_SSL_KEY_FILE}" ] then chmod 400 "/etc/ssl/private/${POSTGRESQL18_SSL_KEY_FILE}" chown postgres:postgres "/etc/ssl/private/${POSTGRESQL18_SSL_KEY_FILE}" fi fi } #------------------------------------------------------------------------------ # Write host access table #------------------------------------------------------------------------------ write_hba_conf() { # write file { echo "#----------------------------------------------------------------------------" echo "# PostgreSQL Client Authentication Configuration File" echo "# generated by /var/install/config.d/${package}.sh" echo "#" echo "# Creation date: `date`" echo "#----------------------------------------------------------------------------" echo "" echo "# TYPE DATABASE USER CIDR-ADDRESS METHOD" idx=1 while [ "${idx}" -le "${POSTGRESQL18_HOST_N}" ] do eval type='${POSTGRESQL18_HOST_'$idx'_TYPE}' eval network='${POSTGRESQL18_HOST_'$idx'_NETWORK}' eval userauth='${POSTGRESQL18_HOST_'$idx'_USERAUTH}' eval database='${POSTGRESQL18_HOST_'$idx'_DATABASE}' eval user='${POSTGRESQL18_HOST_'$idx'_USER}' eval clientcert='${POSTGRESQL18_HOST_'$idx'_CLIENTCERT}' local auth_option="" if [ "${POSTGRESQL18_ENABLE_SSL}" = "yes" ] && [ "${POSTGRESQL18_SSL_CLIENT_VERIFY}" = "yes" ] && [ "${clientcert}" != "none" ] && [ "${type}" == "hostssl" ] then auth_option="clientcert=${clientcert}" fi printfield "${type}" 8 printfield "${database}" 12 printfield "${user}" 12 printfield "${network}" 22 case "${userauth}" in ident) printfield "${userauth} sameuser" 1 ;; pam) printfield "${userauth} ${package}" 1 ;; *) printfield "${userauth} ${auth_option}" 1 ;; esac echo "" idx=$((${idx} + 1)) done } > ${POSTGRESQL18_DATADIR}/pg_hba.conf chown postgres:postgres ${POSTGRESQL18_DATADIR}/pg_hba.conf chmod 600 ${POSTGRESQL18_DATADIR}/pg_hba.conf } #------------------------------------------------------------------------------ # Write configuration file #------------------------------------------------------------------------------ write_postgresql_conf() { # ---------------------------------------------------- # do some memory calculation first # ---------------------------------------------------- case "${POSTGRESQL18_MEMORY_LAYOUT}" in medium) # medium memory layout shared_buffers="128MB" temp_buffers="8MB" work_mem="4MB" maintenance_work_mem="64MB" ;; large) # large memory layout shared_buffers="256MB" temp_buffers="16MB" work_mem="8MB" maintenance_work_mem="128MB" ;; huge) # huge memory layout shared_buffers="512MB" temp_buffers="32MB" work_mem="16MB" maintenance_work_mem="256MB" ;; *) # small memory layout shared_buffers="16MB" temp_buffers="4MB" work_mem="1MB" maintenance_work_mem="8MB" ;; esac # check max connection parameter if [ "${POSTGRESQL18_MEMORY_LAYOUT}" = "small" ] then if [ "${POSTGRESQL18_CONNECTIONS}" -gt "1024" ] then POSTGRESQL18_CONNECTIONS=1024 fi fi # have one prepared connection for every possible connection max_prepared_transactions="${POSTGRESQL18_CONNECTIONS}" # calculate stack depth limit max_stack_depth="$(( ($(ulimit -s) - 1) / 1024))MB" # ---------------------------------------------------- # determine WAL sync method # ---------------------------------------------------- case "${POSTGRESQL18_WRITE_MODE}" in normal) # normal write mode fsync="on" synchronous_commit="on" commit_delay="100" ;; fast) # fast (asyncronous) write mode fsync="on" synchronous_commit="off" commit_delay="1000" ;; nosync) fsync="off" synchronous_commit="off" commit_delay="0" ;; *) fsync="on" synchronous_commit="yes" commit_delay="0" ;; esac # write file { echo "#----------------------------------------------------------------------------" echo "# PostgreSQL configuration file" echo "# generated by /var/install/config.d/${package}.sh" echo "#" echo "# Creation date: `date`" echo "#----------------------------------------------------------------------------" echo "" echo "#------------------------------------------------------------------------------" echo "# FILE LOCATIONS" echo "#------------------------------------------------------------------------------" echo "" echo "# The default values of these variables are driven from the -D command-line" echo "# option or PGDATA environment variable, represented here as ConfigDir." echo "" echo "#data_directory = 'ConfigDir' # use data in another directory" echo " # (change requires restart)" echo "#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file" echo " # (change requires restart)" echo "#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file" echo " # (change requires restart)" echo "" echo "# If external_pid_file is not explicitly set, no extra PID file is written." echo "external_pid_file = '${POSTGRESQL18_DATADIR}/postmaster.pid'" echo " # write an extra PID file" echo " # (change requires restart)" echo "" echo "" echo "#---------------------------------------------------------------------------" echo "# CONNECTIONS AND AUTHENTICATION" echo "#---------------------------------------------------------------------------" echo "" echo "# - Connection Settings -" echo "" if [ "$POSTGRESQL18_NETWORKING" = "yes" ] then echo "listen_addresses = '*' # what IP address(es) to listen on;" echo " # comma-separated list of addresses;" echo " # defaults to 'localhost', '*' = all" echo " # (change requires restart)" else echo "listen_addresses = 'localhost' # what IP address(es) to listen on;" echo " # comma-separated list of addresses;" echo " # defaults to 'localhost', '*' = all" echo " # (change requires restart)" fi echo "port = ${POSTGRESQL18_CONNECT_PORT} # (change requires restart)" echo "max_connections = ${POSTGRESQL18_CONNECTIONS} # (change requires restart)" echo "#superuser_reserved_connections = 3 # (change requires restart)" echo "unix_socket_directories = '/run/postgresql,/tmp'" echo " # comma-separated list of directories" echo " # (change requires restart)" echo "#unix_socket_group = '' # (change requires restart)" echo "#unix_socket_permissions = 0777 # octal" echo " # (change requires restart)" echo "#bonjour = off # advertise server via Bonjour" echo " # (change requires restart)" echo "#bonjour_name = '' # defaults to the computer name" echo " # (change requires restart)" echo "" echo "# - TCP settings -" echo "# see "man tcp" for details" echo "" echo "#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;" echo " # 0 selects the system default" echo "#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;" echo " # 0 selects the system default" echo "#tcp_keepalives_count = 0 # TCP_KEEPCNT;" echo " # 0 selects the system default" echo "#tcp_user_timeout = 0 # TCP_USER_TIMEOUT, in milliseconds;" echo " # 0 selects the system default" echo "" echo "#client_connection_check_interval = 0 # time between checks for client" echo " # disconnection while running queries;" echo " # 0 for never" echo "" echo "# - Authentication -" echo "" echo "#authentication_timeout = 1min # 1s-600s" echo "#password_encryption = scram-sha-256 # scram-sha-256 or md5" echo "#scram_iterations = 4096" echo "#db_user_namespace = off" echo "" echo "# GSSAPI using Kerberos" echo "#krb_server_keyfile = 'FILE:\${sysconfdir}/krb5.keytab'" echo "#krb_caseins_users = off" echo "#gss_accept_delegation = off" echo "" echo "# - SSL -" echo "" if [ "${POSTGRESQL18_ENABLE_SSL}" = "yes" ] then echo "ssl = on" echo "ssl_cert_file = '/etc/ssl/certs/${POSTGRESQL18_SSL_CERT_FILE}'" echo "ssl_key_file = '/etc/ssl/private/${POSTGRESQL18_SSL_KEY_FILE}'" if [ "${POSTGRESQL18_SSL_CLIENT_VERIFY}" = "yes" ] then echo "ssl_ca_file = '/etc/ssl/certs/${POSTGRESQL18_SSL_CA_FILE}'" else echo "#ssl_ca_file = ''" fi else echo "#ssl = off" echo "#ssl_ca_file = ''" echo "#ssl_cert_file = 'server.crt'" echo "#ssl_key_file = 'server.key'" fi echo "#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed TLSv1.2 ciphers" echo "#ssl_tls13_ciphers = '' # allowed TLSv1.3 cipher suites, blank for default" echo "#ssl_prefer_server_ciphers = on" echo "#ssl_groups = 'X25519:prime256v1'" echo "#ssl_min_protocol_version = 'TLSv1.2'" echo "#ssl_max_protocol_version = ''" echo "#ssl_dh_params_file = ''" echo "#ssl_passphrase_command = ''" echo "#ssl_passphrase_command_supports_reload = off" echo "" echo "" echo "#---------------------------------------------------------------------------" echo "# RESOURCE USAGE (except WAL)" echo "#---------------------------------------------------------------------------" echo "" echo "# - Memory -" echo "" echo "shared_buffers = ${shared_buffers} # min 128kB" echo " # (change requires restart)" echo "#huge_pages = try # on, off, or try" echo " # (change requires restart)" echo "#huge_page_size = 0 # zero for system default" echo " # (change requires restart)" echo "temp_buffers = ${temp_buffers} # min 800kB" echo "max_prepared_transactions = 0 # zero disables the feature" echo " # (change requires restart)" echo "# Caution: it is not advisable to set max_prepared_transactions nonzero unless" echo "# you actively intend to use prepared transactions." echo "work_mem = ${work_mem} # min 64kB" echo "#hash_mem_multiplier = 2.0 # 1-1000.0 multiplier on hash table work_mem" echo "maintenance_work_mem = ${maintenance_work_mem} # min 1MB" echo "#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem" echo "#logical_decoding_work_mem = 64MB # min 64kB" echo "max_stack_depth = ${max_stack_depth}" echo "#shared_memory_type = mmap # the default is the first option" echo " # supported by the operating system:" echo " # mmap" echo " # sysv" echo " # windows" echo " # (change requires restart)" echo "dynamic_shared_memory_type = posix # the default is the first option" echo " # supported by the operating system:" echo " # posix" echo " # sysv" echo " # windows" echo " # mmap" echo " # (change requires restart)" echo "#min_dynamic_shared_memory = 0MB # (change requires restart)" echo "#vacuum_buffer_usage_limit = 256kB # size of vacuum and analyze buffer access strategy ring;" echo " # 0 to disable vacuum buffer access strategy;" echo " # range 128kB to 16GB" echo "" echo "# SLRU buffers (change requires restart)" echo "#commit_timestamp_buffers = 0 # memory for pg_commit_ts (0 = auto)" echo "#multixact_offset_buffers = 16 # memory for pg_multixact/offsets" echo "#multixact_member_buffers = 32 # memory for pg_multixact/members" echo "#notify_buffers = 16 # memory for pg_notify" echo "#serializable_buffers = 32 # memory for pg_serial" echo "#subtransaction_buffers = 0 # memory for pg_subtrans (0 = auto)" echo "#transaction_buffers = 0 # memory for pg_xact (0 = auto)" echo "" echo "# - Disk -" echo "" echo "#temp_file_limit = -1 # limits per-process temp file space" echo " # in kilobytes, or -1 for no limit" echo "" echo "#file_copy_method = copy # copy, clone (if supported by OS)" echo "" echo "#max_notify_queue_pages = 1048576 # limits the number of SLRU pages allocated" echo " # for NOTIFY / LISTEN queue" echo "" echo "# - Kernel Resources -" echo "" echo "#max_files_per_process = 1000 # min 64" echo " # (change requires restart)" echo "" echo "# - Background Writer -" echo "" echo "#bgwriter_delay = 200ms # 10-10000ms between rounds" echo "#bgwriter_lru_maxpages = 100 # max buffers written/round, 0 disables" echo "#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round" echo "#bgwriter_flush_after = 512kB # measured in pages, 0 disables" echo "" echo "# - I/O -" echo "" echo "#backend_flush_after = 0 # measured in pages, 0 disables" echo "#effective_io_concurrency = 16 # 1-1000; 0 disables issuing multiple simultaneous IO requests" echo "#maintenance_io_concurrency = 16 # 1-1000; same as effective_io_concurrency" echo "#io_max_combine_limit = 128kB # usually 1-128 blocks (depends on OS)" echo " # (change requires restart)" echo "#io_combine_limit = 128kB # usually 1-128 blocks (depends on OS)" echo "" echo "#io_method = worker # worker, io_uring, sync" echo " # (change requires restart)" echo "#io_max_concurrency = -1 # Max number of IOs that one process" echo " # can execute simultaneously" echo " # -1 sets based on shared_buffers" echo " # (change requires restart)" echo "#io_workers = 3 # 1-32;" echo "" echo "# - Worker Processes -" echo "" echo "#max_worker_processes = 8 # (change requires restart)" echo "#max_parallel_workers_per_gather = 2 # limited by max_parallel_workers" echo "#max_parallel_maintenance_workers = 2 # limited by max_parallel_workers" echo "#max_parallel_workers = 8 # number of max_worker_processes that" echo " # can be used in parallel operations" echo "#parallel_leader_participation = on" echo "" echo "" echo "#---------------------------------------------------------------------------" echo "# WRITE-AHEAD LOG" echo "#---------------------------------------------------------------------------" echo "" echo "# - Settings -" echo "" echo "wal_level = minimal # minimal, replica, or logical" echo " # (change requires restart)" echo "fsync = ${fsync} # flush data to disk for crash safety" echo " # (turning this off can cause" echo " # unrecoverable data corruption)" echo "synchronous_commit = ${synchronous_commit} # synchronization level;" echo " # off, local, remote_write, remote_apply, or on" echo "wal_sync_method = fdatasync # the default is the first option" echo " # supported by the operating system:" echo " # open_datasync" echo " # fdatasync (default on Linux and FreeBSD)" echo " # fsync" echo " # fsync_writethrough" echo " # open_sync" echo "#full_page_writes = on # recover from partial page writes" echo "#wal_log_hints = off # also do full page writes of non-critical updates" echo " # (change requires restart)" echo "#wal_compression = off # enable compression of full-page writes;" echo " # off, pglz, lz4, zstd, or on" echo "#wal_init_zero = on # zero-fill new WAL files" echo "#wal_recycle = on # recycle WAL files" echo "#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers" echo " # (change requires restart)" echo "#wal_writer_delay = 200ms # 1-10000 milliseconds" echo "#wal_writer_flush_after = 1MB # measured in pages, 0 disables" echo "#wal_skip_threshold = 2MB" echo "" echo "commit_delay = ${commit_delay} # range 0-100000, in microseconds" echo "#commit_siblings = 5 # range 1-1000" echo "" echo "# - Checkpoints -" echo "" echo "#checkpoint_timeout = 5min # range 30s-1d" echo "#checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0" echo "#checkpoint_flush_after = 256kB # measured in pages, 0 disables" echo "#checkpoint_warning = 30s # 0 disables" echo "max_wal_size = 1GB" echo "min_wal_size = 80MB" echo "" echo "# - Prefetching during recovery -" echo "" echo "#recovery_prefetch = try # prefetch pages referenced in the WAL?" echo "#wal_decode_buffer_size = 512kB # lookahead window used for prefetching" echo " # (change requires restart)" echo "" echo "# - Archiving -" echo "" echo "#archive_mode = off # enables archiving; off, on, or always" echo " # (change requires restart)" echo "#archive_library = '' # library to use to archive a WAL file" echo " # (empty string indicates archive_command should" echo " # be used)" echo "#archive_command = '' # command to use to archive a WAL file" echo " # placeholders: %p = path of file to archive" echo " # %f = file name only" echo " # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'" echo "#archive_timeout = 0 # force a WAL file switch after this" echo " # number of seconds; 0 disables" echo "" echo "# - Archive Recovery -" echo "" echo "# These are only used in recovery mode." echo "" echo "#restore_command = '' # command to use to restore an archived WAL file" echo " # placeholders: %p = path of file to restore" echo " # %f = file name only" echo " # e.g. 'cp /mnt/server/archivedir/%f %p'" echo "#archive_cleanup_command = '' # command to execute at every restartpoint" echo "#recovery_end_command = '' # command to execute at completion of recovery" echo "" echo "# - Recovery Target -" echo "" echo "# Set these only when performing a targeted recovery." echo "" echo "#recovery_target = '' # 'immediate' to end recovery as soon as a" echo " # consistent state is reached" echo " # (change requires restart)" echo "#recovery_target_name = '' # the named restore point to which recovery will proceed" echo " # (change requires restart)" echo "#recovery_target_time = '' # the time stamp up to which recovery will proceed" echo " # (change requires restart)" echo "#recovery_target_xid = '' # the transaction ID up to which recovery will proceed" echo " # (change requires restart)" echo "#recovery_target_lsn = '' # the WAL LSN up to which recovery will proceed" echo " # (change requires restart)" echo "#recovery_target_inclusive = on # Specifies whether to stop:" echo " # just after the specified recovery target (on)" echo " # just before the recovery target (off)" echo " # (change requires restart)" echo "#recovery_target_timeline = 'latest' # 'current', 'latest', or timeline ID" echo " # (change requires restart)" echo "#recovery_target_action = 'pause' # 'pause', 'promote', 'shutdown'" echo " # (change requires restart)" echo "" echo "# - WAL Summarization -" echo "" echo "#summarize_wal = off # run WAL summarizer process?" echo "#wal_summary_keep_time = '10d' # when to remove old summary files, 0 = never" echo "" echo "#------------------------------------------------------------------------------" echo "# REPLICATION" echo "#------------------------------------------------------------------------------" echo "" echo "# - Sending Servers -" echo "" echo "# Set these on the primary and on any standby that will send replication data." echo "" echo "max_wal_senders = 0 # max number of walsender processes" echo " # (change requires restart)" echo "#max_replication_slots = 10 # max number of replication slots" echo " # (change requires restart)" echo "#wal_keep_size = 0 # in megabytes; 0 disables" echo "#max_slot_wal_keep_size = -1 # in megabytes; -1 disables" echo "#wal_sender_timeout = 60s # in milliseconds; 0 disables" echo "#track_commit_timestamp = off # collect timestamp of transaction commit" echo " # (change requires restart)" echo "" echo "# - Primary Server -" echo "" echo "# These settings are ignored on a standby server." echo "" echo "#synchronous_standby_names = '' # standby servers that provide sync rep" echo " # method to choose sync standbys, number of sync standbys," echo " # and comma-separated list of application_name" echo " # from standby(s); '*' = all" echo "#synchronized_standby_slots = '' # streaming replication standby server slot" echo " # names that logical walsender processes will wait for" echo "" echo "# - Standby Servers -" echo "" echo "# These settings are ignored on a primary server." echo "" echo "#primary_conninfo = '' # connection string to sending server" echo "#primary_slot_name = '' # replication slot on sending server" echo "#hot_standby = on # 'off' disallows queries during recovery" echo " # (change requires restart)" echo "#max_standby_archive_delay = 30s # max delay before canceling queries" echo " # when reading WAL from archive;" echo " # -1 allows indefinite delay" echo "#max_standby_streaming_delay = 30s # max delay before canceling queries" echo " # when reading streaming WAL;" echo " # -1 allows indefinite delay" echo "#wal_receiver_create_temp_slot = off # create temp slot if primary_slot_name" echo " # is not set" echo "#wal_receiver_status_interval = 10s # send replies at least this often" echo " # 0 disables" echo "#hot_standby_feedback = off # send info from standby to prevent" echo " # query conflicts" echo "#wal_receiver_timeout = 60s # time that receiver waits for" echo " # communication from primary" echo " # in milliseconds; 0 disables" echo "#wal_retrieve_retry_interval = 5s # time to wait before retrying to" echo " # retrieve WAL after a failed attempt" echo "#recovery_min_apply_delay = 0 # minimum delay for applying changes during recovery" echo "#sync_replication_slots = off # enables slot synchronization on the physical standby from the primary" echo "" echo "# - Subscribers -" echo "" echo "# These settings are ignored on a publisher." echo "" echo "#max_active_replication_origins = 10 # max number of active replication origins" echo " # (change requires restart)" echo "#max_logical_replication_workers = 4 # taken from max_worker_processes" echo " # (change requires restart)" echo "#max_sync_workers_per_subscription = 2 # taken from max_logical_replication_workers" echo "#max_parallel_apply_workers_per_subscription = 2 # taken from max_logical_replication_workers" echo "" echo "#---------------------------------------------------------------------------" echo "# QUERY TUNING" echo "#---------------------------------------------------------------------------" echo "" echo "# - Planner Method Configuration -" echo "" echo "#enable_async_append = on" echo "#enable_bitmapscan = on" echo "#enable_gathermerge = on" echo "#enable_hashagg = on" echo "#enable_hashjoin = on" echo "#enable_incremental_sort = on" echo "#enable_indexscan = on" echo "#enable_indexonlyscan = on" echo "#enable_material = on" echo "#enable_memoize = on" echo "#enable_mergejoin = on" echo "#enable_nestloop = on" echo "#enable_parallel_append = on" echo "#enable_parallel_hash = on" echo "#enable_partition_pruning = on" echo "#enable_partitionwise_join = off" echo "#enable_partitionwise_aggregate = off" echo "#enable_presorted_aggregate = on" echo "#enable_seqscan = on" echo "#enable_sort = on" echo "#enable_tidscan = on" echo "#enable_group_by_reordering = on" echo "#enable_distinct_reordering = on" echo "#enable_self_join_elimination = on" echo "" echo "# - Planner Cost Constants -" echo "" echo "#seq_page_cost = 1.0 # measured on an arbitrary scale" echo "#random_page_cost = 4.0 # same scale as above" echo "#cpu_tuple_cost = 0.01 # same scale as above" echo "#cpu_index_tuple_cost = 0.005 # same scale as above" echo "#cpu_operator_cost = 0.0025 # same scale as above" echo "#parallel_setup_cost = 1000.0 # same scale as above" echo "#parallel_tuple_cost = 0.1 # same scale as above" echo "#min_parallel_table_scan_size = 8MB" echo "#min_parallel_index_scan_size = 512kB" echo "#effective_cache_size = 4GB" echo "" echo "#jit_above_cost = 100000 # perform JIT compilation if available" echo " # and query more expensive than this;" echo " # -1 disables" echo "#jit_inline_above_cost = 500000 # inline small functions if query is" echo " # more expensive than this; -1 disables" echo "#jit_optimize_above_cost = 500000 # use expensive JIT optimizations if" echo " # query is more expensive than this;" echo " # -1 disables" echo "" echo "# - Genetic Query Optimizer -" echo "" echo "#geqo = on" echo "#geqo_threshold = 12" echo "#geqo_effort = 5 # range 1-10" echo "#geqo_pool_size = 0 # selects default based on effort" echo "#geqo_generations = 0 # selects default based on effort" echo "#geqo_selection_bias = 2.0 # range 1.5-2.0" echo "#geqo_seed = 0.0 # range 0.0-1.0" echo "" echo "# - Other Planner Options -" echo "" echo "#default_statistics_target = 100 # range 1-1000" echo "#constraint_exclusion = partition # on, off, or partition" echo "#cursor_tuple_fraction = 0.1 # range 0.0-1.0" echo "#from_collapse_limit = 8" echo "#jit = on # allow JIT compilation" echo "#join_collapse_limit = 8 # 1 disables collapsing of explicit" echo " # JOINs" echo "#plan_cache_mode = auto # auto, force_generic_plan or" echo " # force_custom_plan" echo "#recursive_worktable_factor = 10.0 # range 0.001-1000000" echo "" echo "#---------------------------------------------------------------------------" echo "# REPORTING AND LOGGING" echo "#---------------------------------------------------------------------------" echo "" echo "# - Where to Log -" echo "" echo "log_destination = 'stderr' # Valid values are combinations of" echo " # stderr, csvlog, jsonlog, syslog, and" echo " # eventlog, depending on platform." echo " # csvlog and jsonlog require" echo " # logging_collector to be on." echo "" echo "# This is used when logging to stderr:" echo "logging_collector = on # Enable capturing of stderr, jsonlog," echo " # and csvlog into log files. Required" echo " # to be on for csvlogs and jsonlogs." echo " # (change requires restart)" echo "" echo "# These are only used if logging_collector is on:" echo "log_directory = '${POSTGRESQL18_DATADIR}/log' # directory where log files are written" echo " # can be absolute or relative to PGDATA" echo "log_filename = '${package}-%Y-%m-%d_%H%M%S.log'" echo " # log file name pattern," echo " # can include strftime() escapes" echo "#log_file_mode = 0600 # creation mode for log files," echo " # begin with 0 to use octal notation" echo "log_rotation_age = 1d # Automatic rotation of logfiles will" echo " # happen after that time. 0 disables." echo "log_rotation_size = 10MB # Automatic rotation of logfiles will" echo " # happen after that much log output." echo " # 0 disables." echo "log_truncate_on_rotation = on # If on, an existing log file with the" echo " # same name as the new log file will be" echo " # truncated rather than appended to." echo " # But such truncation only occurs on" echo " # time-driven rotation, not on restarts" echo " # or size-driven rotation. Default is" echo " # off, meaning append to existing files" echo " # in all cases." echo "" echo "# These are relevant when logging to syslog:" echo "#syslog_facility = 'LOCAL0'" echo "#syslog_ident = 'postgres'" echo "#syslog_sequence_numbers = on" echo "#syslog_split_messages = on" echo "" echo "# This is only relevant when logging to eventlog (Windows):" echo "# (change requires restart)" echo "#event_source = 'PostgreSQL'" echo "" echo "# - When to Log -" echo "" if [ "${POSTGRESQL18_LOG_SETTINGS}" == "yes" ] then echo "log_min_messages = ${POSTGRESQL18_SERVER_LOG_LEVEL}" else echo "#log_min_messages = warning # values in order of decreasing detail:" fi echo " # debug5" echo " # debug4" echo " # debug3" echo " # debug2" echo " # debug1" echo " # info" echo " # notice" echo " # warning" echo " # error" echo " # log" echo " # fatal" echo " # panic" echo "" echo "#log_min_error_statement = error # values in order of decreasing detail:" echo " # debug5" echo " # debug4" echo " # debug3" echo " # debug2" echo " # debug1" echo " # info" echo " # notice" echo " # warning" echo " # error" echo " # log" echo " # fatal" echo " # panic (effectively off)" echo "" echo "#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements" echo " # and their durations, > 0 logs only" echo " # statements running at least this number" echo " # of milliseconds" echo "" echo "#log_min_duration_sample = -1 # -1 is disabled, 0 logs a sample of statements" echo " # and their durations, > 0 logs only a sample of" echo " # statements running at least this number" echo " # of milliseconds;" echo " # sample fraction is determined by log_statement_sample_rate" echo "" echo "#log_statement_sample_rate = 1.0 # fraction of logged statements exceeding" echo " # log_min_duration_sample to be logged;" echo " # 1.0 logs all such statements, 0.0 never logs" echo "" echo "" echo "#log_transaction_sample_rate = 0.0 # fraction of transactions whose statements" echo " # are logged regardless of their duration; 1.0 logs all" echo " # statements from all transactions, 0.0 never logs" echo "" echo "#log_startup_progress_interval = 10s # Time between progress updates for" echo " # long-running startup operations." echo " # 0 disables the feature, > 0 indicates" echo " # the interval in milliseconds." echo "" echo "# - What to Log -" echo "" echo "#debug_print_parse = off" echo "#debug_print_rewritten = off" echo "#debug_print_plan = off" echo "#debug_pretty_print = on" echo "#log_autovacuum_min_duration = 10min # log autovacuum activity;" echo " # -1 disables, 0 logs all actions and" echo " # their durations, > 0 logs only" echo " # actions running at least this number" echo " # of milliseconds." echo "#log_checkpoints = on" echo "#log_connections = off" echo "#log_disconnections = off" echo "#log_duration = off" if [ "${POSTGRESQL18_LOG_SETTINGS}" == "yes" ] && \ [ "${POSTGRESQL18_LOG_VERBOSE}" == "yes" ] then echo "log_error_verbosity = verbose" else echo "#log_error_verbosity = default # terse, default, or verbose messages" fi echo "#log_hostname = off" echo "#log_line_prefix = '%m [%p] ' # special values:" echo " # %a = application name" echo " # %u = user name" echo " # %d = database name" echo " # %r = remote host and port" echo " # %h = remote host" echo " # %b = backend type" echo " # %p = process ID" echo " # %P = process ID of parallel group leader" echo " # %t = timestamp without milliseconds" echo " # %m = timestamp with milliseconds" echo " # %n = timestamp with milliseconds (as a Unix epoch)" echo " # %Q = query ID (0 if none or not computed)" echo " # %i = command tag" echo " # %e = SQL state" echo " # %c = session ID" echo " # %l = session line number" echo " # %s = session start timestamp" echo " # %v = virtual transaction ID" echo " # %x = transaction ID (0 if none)" echo " # %q = stop here in non-session" echo " # processes" echo " # %% = '%'" echo " # e.g. '<%u%%%d> '" echo "#log_lock_waits = off # log lock waits >= deadlock_timeout" echo "#log_recovery_conflict_waits = off # log standby recovery conflict waits" echo " # >= deadlock_timeout" echo "#log_parameter_max_length = -1 # when logging statements, limit logged" echo " # bind-parameter values to N bytes;" echo " # -1 means print in full, 0 disables" echo "#log_parameter_max_length_on_error = 0 # when logging an error, limit logged" echo " # bind-parameter values to N bytes;" echo " # -1 means print in full, 0 disables" if [ "${POSTGRESQL18_LOG_SETTINGS}" == "yes" ] then if [ "${POSTGRESQL18_LOG_STATEMENTS}" == "yes" ] then echo "log_statement = 'all' # none, mod, ddl, all" else echo "log_statement = 'none' # none, mod, ddl, all" fi else echo "#log_statement = 'none' # none, mod, ddl, all" fi echo "#log_replication_commands = off" echo "#log_temp_files = -1 # log temporary files equal or larger" echo " # than the specified size in kilobytes;" echo " # -1 disables, 0 logs all temp files" echo "#log_timezone = unknown # actually, defaults to TZ environment" echo "" echo "# - Process Title -" echo "" echo "#cluster_name = '' # added to process titles if nonempty" echo " # (change requires restart)" echo "#update_process_title = on" echo "" echo "" echo "#---------------------------------------------------------------------------" echo "# STATISTICS" echo "#---------------------------------------------------------------------------" echo "" echo "# - Cumulative Query and Index Statistics -" echo "" echo "#track_activities = on" echo "#track_activity_query_size = 1024 # (change requires restart)" echo "#track_counts = on" echo "#track_io_timing = off" echo "#track_wal_io_timing = off" echo "#track_functions = none # none, pl, all" echo "#stats_fetch_consistency = cache # cache, none, snapshot" echo "" echo "# - Monitoring -" echo "" echo "#compute_query_id = auto" echo "#log_statement_stats = off" echo "#log_parser_stats = off" echo "#log_planner_stats = off" echo "#log_executor_stats = off" echo "" echo "#---------------------------------------------------------------------------" echo "# VACUUMING" echo "#---------------------------------------------------------------------------" echo "" echo "# - Automatic Vacuuming -" echo "" if [ "${POSTGRESQL18_AUTOVACUUM}" == "yes" ] then echo "autovacuum = on # enable autovacuum subprocess?" else echo "autovacuum = off # enable autovacuum subprocess?" fi echo " # requires track_counts to also be on." echo "autovacuum_worker_slots = 16 # autovacuum worker slots to allocate" echo " # (change requires restart)" echo "#autovacuum_max_workers = 3 # max number of autovacuum subprocesses" echo " # (change requires restart)" echo "#autovacuum_naptime = 1min # time between autovacuum runs" echo "#autovacuum_vacuum_threshold = 50 # min number of row updates before" echo " # vacuum" echo "#autovacuum_vacuum_insert_threshold = 1000 # min number of row inserts" echo " # before vacuum; -1 disables insert" echo " # vacuums" echo "#autovacuum_analyze_threshold = 50 # min number of row updates before" echo " # analyze" echo "#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum" echo "#autovacuum_vacuum_insert_scale_factor = 0.2 # fraction of inserts over table" echo " # size before insert vacuum" echo "#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze" echo "#autovacuum_vacuum_max_threshold = 100000000 # max number of row updates" echo " # before vacuum; -1 disables max" echo " # threshold" echo "#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum" echo " # (change requires restart)" echo "#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age" echo " # before forced vacuum" echo " # (change requires restart)" echo "#autovacuum_vacuum_cost_delay = 2ms # default vacuum cost delay for" echo " # autovacuum, in milliseconds;" echo " # -1 means use vacuum_cost_delay" echo "#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for" echo " # autovacuum, -1 means use" echo " # vacuum_cost_limit" echo "" echo "# - Cost-Based Vacuum Delay -" echo "" echo "#vacuum_cost_delay = 0 # 0-100 milliseconds (0 disables)" echo "#vacuum_cost_page_hit = 1 # 0-10000 credits" echo "#vacuum_cost_page_miss = 2 # 0-10000 credits" echo "#vacuum_cost_page_dirty = 20 # 0-10000 credits" echo "#vacuum_cost_limit = 200 # 1-10000 credits" echo "" echo "# - Default Behavior -" echo "" echo "#vacuum_truncate = on # enable truncation after vacuum" echo "" echo "# - Freezing -" echo "" echo "#vacuum_freeze_table_age = 150000000" echo "#vacuum_freeze_min_age = 50000000" echo "#vacuum_failsafe_age = 1600000000" echo "#vacuum_multixact_freeze_table_age = 150000000" echo "#vacuum_multixact_freeze_min_age = 5000000" echo "#vacuum_multixact_failsafe_age = 1600000000" echo "#vacuum_max_eager_freeze_failure_rate = 0.03 # 0 disables eager scanning" echo "" echo "#---------------------------------------------------------------------------" echo "# CLIENT CONNECTION DEFAULTS" echo "#---------------------------------------------------------------------------" echo "" echo "# - Statement Behavior -" echo "" if [ "${POSTGRESQL18_LOG_SETTINGS}" == "yes" ] then echo "client_min_messages = ${POSTGRESQL18_CLIENT_LOG_LEVEL}" else echo "#client_min_messages = notice # values in order of decreasing detail:" fi echo " # debug5" echo " # debug4" echo " # debug3" echo " # debug2" echo " # debug1" echo " # log" echo " # notice" echo " # warning" echo " # error" echo "#search_path = '\"\$user\",public' # schema names" echo "#row_security = on" echo "#default_table_access_method = 'heap'" echo "#default_tablespace = '' # a tablespace name, '' uses the default" echo "#default_toast_compression = 'pglz' # 'pglz' or 'lz4'" echo "#temp_tablespaces = '' # a list of tablespace names, '' uses" echo " # only default tablespace" echo "#check_function_bodies = on" echo "#default_transaction_isolation = 'read committed'" echo "#default_transaction_read_only = off" echo "#default_transaction_deferrable = off" echo "#session_replication_role = 'origin'" echo "#statement_timeout = 0 # in milliseconds, 0 is disabled" echo "#transaction_timeout = 0 # in milliseconds, 0 is disabled" echo "#lock_timeout = 0 # in milliseconds, 0 is disabled" echo "#idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled" echo "#idle_session_timeout = 0 # in milliseconds, 0 is disabled" echo "#bytea_output = 'hex' # hex, escape" echo "#xmlbinary = 'base64'" echo "#xmloption = 'content'" echo "#gin_pending_list_limit = 4MB" echo "#createrole_self_grant = '' # set and/or inherit" echo "#event_triggers = on" echo "" echo "# - Locale and Formatting -" echo "" echo "datestyle = 'iso, mdy'" echo "#intervalstyle = 'postgres'" echo "#timezone = unknown # actually, defaults to TZ" echo " # environment setting" echo "#timezone_abbreviations = 'Default' # select the set of available timezone" echo " # abbreviations. Currently, there are" echo " # Default" echo " # Australia" echo " # India" echo " # You can create your own file in" echo " # share/timezonesets/." echo "#extra_float_digits = 1 # min -15, max 3; any value >0 actually" echo " # selects precise output mode" echo "#client_encoding = sql_ascii # actually, defaults to database" echo " # encoding" echo "" echo "# These settings are initialized by initdb, but they can be changed." echo "" echo "lc_messages = 'C' # locale for system error message" echo " # strings" echo "lc_monetary = 'C' # locale for monetary formatting" echo "lc_numeric = 'C' # locale for number formatting" echo "lc_time = 'C' # locale for time formatting" echo "" echo "#icu_validation_level = warning # report ICU locale validation" echo " # errors at the given level" echo "" echo "# default configuration for text search" echo "default_text_search_config = 'pg_catalog.english'" echo "" echo "# - Shared Library Preloading -" echo "" echo "#local_preload_libraries = ''" echo "#session_preload_libraries = ''" echo "#shared_preload_libraries = '' # (change requires restart)" echo "#jit_provider = 'llvmjit' # JIT library to use" echo "" echo "# - Other Defaults -" echo "" echo "#dynamic_library_path = '\$libdir'" echo "#extension_control_path = '$system'" echo "#gin_fuzzy_search_limit = 0" echo "" echo "" echo "#---------------------------------------------------------------------------" echo "# LOCK MANAGEMENT" echo "#---------------------------------------------------------------------------" echo "" echo "#deadlock_timeout = 1s" echo "#max_locks_per_transaction = 64 # min 10" echo " # (change requires restart)" echo "#max_pred_locks_per_transaction = 64 # min 10" echo " # (change requires restart)" echo "#max_pred_locks_per_relation = -2 # negative values mean" echo " # (max_pred_locks_per_transaction" echo " # / -max_pred_locks_per_relation) - 1" echo "#max_pred_locks_per_page = 2 # min 0" echo "" echo "#---------------------------------------------------------------------------" echo "# VERSION AND PLATFORM COMPATIBILITY" echo "#---------------------------------------------------------------------------" echo "" echo "# - Previous PostgreSQL Versions -" echo "" echo "#array_nulls = on" echo "#backslash_quote = safe_encoding # on, off, or safe_encoding" echo "#escape_string_warning = on" echo "#lo_compat_privileges = off" echo "#quote_all_identifiers = off" echo "#standard_conforming_strings = on" echo "#synchronize_seqscans = on" echo "" echo "# - Other Platforms and Clients -" echo "" echo "#transform_null_equals = off" echo "#allow_alter_system = on" echo "" echo "#------------------------------------------------------------------------------" echo "# ERROR HANDLING" echo "#------------------------------------------------------------------------------" echo "" echo "#exit_on_error = off # terminate session on any error?" echo "#restart_after_crash = on # reinitialize after backend crash?" echo "#data_sync_retry = off # retry or panic on failure to fsync?" echo " # data?" echo " # (change requires restart)" echo "#recovery_init_sync_method = fsync # fsync, syncfs (Linux 5.8+)" echo "" echo "" echo "#------------------------------------------------------------------------------" echo "# CONFIG FILE INCLUDES" echo "#------------------------------------------------------------------------------" echo "" echo "# These options allow settings to be loaded from files other than the" echo "# default postgresql.conf. Note that these are directives, not variable" echo "# assignments, so they can usefully be given more than once." echo "" echo "#include_dir = '...' # include files ending in '.conf' from" echo " # a directory, e.g., 'conf.d'" echo "#include_if_exists = '...' # include file only if it exists" echo "#include = '...' # include file" echo "" echo "#---------------------------------------------------------------------------" echo "# CUSTOMIZED OPTIONS" echo "#---------------------------------------------------------------------------" echo "" } > ${POSTGRESQL18_DATADIR}/postgresql.conf chown postgres:postgres ${POSTGRESQL18_DATADIR}/postgresql.conf chmod 600 ${POSTGRESQL18_DATADIR}/postgresql.conf } #------------------------------------------------------------------------------ # Main #------------------------------------------------------------------------------ update_backup_timer #----------------------------------------------------------------------------- # initialize database cluster in /var/lib/pgsql18 #----------------------------------------------------------------------------- if [ "${START_POSTGRESQL18}" = "yes" ] then if [ ! -d "${POSTGRESQL18_DATADIR}" ] then mecho -info "Creating database cluster in '${POSTGRESQL18_DATADIR}'..." # start initdb mkdir -p "${POSTGRESQL18_DATADIR}" chown postgres:postgres "${POSTGRESQL18_DATADIR}" su postgres -c "${inst_dir}/bin/initdb --locale=C --encoding=${POSTGRESQL18_ENCODING} -D ${POSTGRESQL18_DATADIR}" > /tmp/$$ 2>&1 # evaluate exit code if [ "$?" != "0" ] then cat /tmp/$$ fi rm -f /tmp/$$ # fix file permissions chown root:root ${inst_dir} chmod 0755 ${inst_dir} fi # verify SSL settings verify_ssl_settings # write host access table write_hba_conf # write configuration write_postgresql_conf fi # create/remove start link if [ "${START_POSTGRESQL18}" = "yes" ] then { echo "POSTGRESQL_DATADIR=${POSTGRESQL18_DATADIR}" } > /etc/default/${package} /usr/sbin/service enable ${package} else /usr/sbin/service disable ${package} fi # remove old logrotate file rm -f /etc/logrotate.d/${package} #---------------------------------------------------------------------------------------- # End #---------------------------------------------------------------------------------------- exit 0