github.com/1aal/kubeblocks@v0.0.0-20231107070852-e1c03e598921/deploy/apecloud-postgresql/config/pg12-config.tpl (about)

     1  # - Connection Settings -
     2  
     3  {{- $buffer_unit := "B" }}
     4  {{- $shared_buffers := 1073741824 }}
     5  {{- $max_connections := 10000 }}
     6  {{- $autovacuum_max_workers := 3 }}
     7  {{- $phy_memory := getContainerMemory ( index $.podSpec.containers 0 ) }}
     8  {{- $phy_cpu := getContainerCPU ( index $.podSpec.containers 0 ) }}
     9  {{- if gt $phy_memory 0 }}
    10  {{- $shared_buffers = div $phy_memory 4 }}
    11  {{- $max_connections = min ( div $phy_memory 9531392 ) 5000 }}
    12  {{- $autovacuum_max_workers = min ( max ( div $phy_memory 17179869184 ) 3 ) 10 }}
    13  {{- end }}
    14  
    15  {{- if ge $shared_buffers 1024 }}
    16  {{- $shared_buffers = div $shared_buffers 1024 }}
    17  {{- $buffer_unit = "kB" }}
    18  {{- end }}
    19  
    20  {{- if ge $shared_buffers 1024 }}
    21  {{- $shared_buffers = div $shared_buffers 1024 }}
    22  {{- $buffer_unit = "MB" }}
    23  {{- end }}
    24  
    25  {{- if ge $shared_buffers 1024 }}
    26  {{- $shared_buffers = div $shared_buffers 1024 }}
    27  {{ $buffer_unit = "GB" }}
    28  {{- end }}
    29  
    30  listen_addresses = '*'
    31  port = '5432'
    32  archive_command = '/bin/true'
    33  archive_mode = 'on'
    34  auto_explain.log_analyze = 'False'
    35  auto_explain.log_buffers = 'False'
    36  auto_explain.log_format = 'text'
    37  auto_explain.log_min_duration = '-1'
    38  auto_explain.log_nested_statements = 'False'
    39  auto_explain.log_timing = 'True'
    40  auto_explain.log_triggers = 'False'
    41  auto_explain.log_verbose = 'False'
    42  auto_explain.sample_rate = '1'
    43  autovacuum_analyze_scale_factor = '0.1'
    44  autovacuum_analyze_threshold = '50'
    45  autovacuum_freeze_max_age = '200000000'
    46  autovacuum_max_workers = '{{ $autovacuum_max_workers }}'
    47  autovacuum_multixact_freeze_max_age = '400000000'
    48  autovacuum_naptime = '15s'
    49  autovacuum_vacuum_cost_delay = '2'
    50  autovacuum_vacuum_cost_limit = '200'
    51  autovacuum_vacuum_scale_factor = '0.05'
    52  autovacuum_vacuum_threshold = '50'
    53  {{- if gt $phy_memory 0 }}
    54  autovacuum_work_mem = '{{ printf "%dkB" ( max ( div $phy_memory 65536 ) 131072 ) }}'
    55  {{- end }}
    56  backend_flush_after = '0'
    57  backslash_quote = 'safe_encoding'
    58  bgwriter_delay = '200ms'
    59  bgwriter_flush_after = '64'
    60  bgwriter_lru_maxpages = '1000'
    61  bgwriter_lru_multiplier = '10.0'
    62  bytea_output = 'hex'
    63  check_function_bodies = 'True'
    64  checkpoint_completion_target = '0.9'
    65  checkpoint_flush_after = '32'
    66  checkpoint_timeout = '15min'
    67  checkpoint_warning = '30s'
    68  client_min_messages = 'notice'
    69  # commit_delay = '20'
    70  commit_siblings = '5'
    71  constraint_exclusion = 'partition'
    72  # extension: pg_cron
    73  cron.database_name = 'postgres'
    74  cron.log_statement = 'on'
    75  cron.max_running_jobs = '32'
    76  cursor_tuple_fraction = '0.1'
    77  datestyle = 'ISO,YMD'
    78  deadlock_timeout = '1000ms'
    79  debug_pretty_print = 'True'
    80  debug_print_parse = 'False'
    81  debug_print_plan = 'False'
    82  debug_print_rewritten = 'False'
    83  default_statistics_target = '100'
    84  default_transaction_deferrable = 'False'
    85  default_transaction_isolation = 'read committed'
    86  {{- if gt $phy_memory 0 }}
    87  effective_cache_size = '{{ printf "%dMB" ( div ( div $phy_memory 16384 ) 128 ) }}'
    88  {{- end }}
    89  effective_io_concurrency = '1'
    90  enable_bitmapscan = 'True'
    91  enable_gathermerge = 'True'
    92  enable_hashagg = 'True'
    93  enable_hashjoin = 'True'
    94  enable_indexonlyscan = 'True'
    95  enable_indexscan = 'True'
    96  enable_material = 'True'
    97  enable_mergejoin = 'True'
    98  enable_nestloop = 'True'
    99  enable_parallel_append = 'True'
   100  enable_parallel_hash = 'True'
   101  enable_partition_pruning = 'True'
   102  enable_partitionwise_aggregate = 'True'
   103  enable_partitionwise_join = 'True'
   104  enable_seqscan = 'True'
   105  enable_sort = 'True'
   106  enable_tidscan = 'True'
   107  escape_string_warning = 'True'
   108  extra_float_digits = '1'
   109  force_parallel_mode = '0'
   110  from_collapse_limit = '8'
   111  # fsync=off
   112  # full_page_writes=off
   113  geqo = 'True'
   114  geqo_effort = '5'
   115  geqo_generations = '0'
   116  geqo_pool_size = '0'
   117  geqo_seed = '0'
   118  geqo_selection_bias = '2'
   119  geqo_threshold = '12'
   120  gin_fuzzy_search_limit = '0'
   121  gin_pending_list_limit = '4096kB'
   122  hot_standby_feedback = 'False'
   123  huge_pages = 'try'
   124  idle_in_transaction_session_timeout = '3600000ms'
   125  index_adviser.enable_log = 'on'
   126  index_adviser.max_aggregation_column_count = '10'
   127  index_adviser.max_candidate_index_count = '500'
   128  intervalstyle = 'postgres'
   129  join_collapse_limit = '8'
   130  lc_monetary = 'C'
   131  lc_numeric = 'C'
   132  lc_time = 'C'
   133  lock_timeout = '0'
   134  log_autovacuum_min_duration = '10000'
   135  log_checkpoints = 'True'
   136  log_connections = 'False'
   137  log_disconnections = 'False'
   138  log_duration = 'False'
   139  log_executor_stats = 'False'
   140  {{- block "logsBlock" . }}
   141  {{- if hasKey $.component "enabledLogs" }}
   142  {{- if mustHas "running" $.component.enabledLogs }}
   143  logging_collector = 'True'
   144  log_destination = 'csvlog'
   145  log_directory = 'log'
   146  log_filename = 'postgresql-%Y-%m-%d.log'
   147  {{ end -}}
   148  {{ end -}}
   149  {{ end }}
   150  # log_lock_waits = 'True'
   151  log_min_duration_statement = '1000'
   152  log_parser_stats = 'False'
   153  log_planner_stats = 'False'
   154  log_replication_commands = 'False'
   155  log_statement = 'ddl'
   156  log_statement_stats = 'False'
   157  log_temp_files = '128kB'
   158  log_transaction_sample_rate = '0'
   159  #maintenance_work_mem = '3952MB'
   160  max_connections = '{{ $max_connections }}'
   161  max_files_per_process = '1000'
   162  max_logical_replication_workers = '32'
   163  max_locks_per_transaction = '64'
   164  max_parallel_maintenance_workers = '{{ max ( div $phy_cpu 2 ) 2 }}'
   165  max_parallel_workers = '{{ max ( div ( mul $phy_cpu 3 ) 4 ) 8 }}'
   166  max_parallel_workers_per_gather = '{{ max ( div $phy_cpu 2 ) 2 }}'
   167  max_pred_locks_per_page = '2'
   168  max_pred_locks_per_relation = '-2'
   169  max_pred_locks_per_transaction = '64'
   170  max_prepared_transactions = '100'
   171  max_replication_slots = '16'
   172  max_stack_depth = '2MB'
   173  max_standby_archive_delay = '300000ms'
   174  max_standby_streaming_delay = '300000ms'
   175  max_sync_workers_per_subscription = '2'
   176  max_wal_senders = '64'
   177  max_wal_size = '{{ printf "%dMB" ( min ( max ( div $phy_memory 2097152 ) 4096 ) 32768 ) }}'
   178  max_worker_processes = '{{ max $phy_cpu 8 }}'
   179  min_parallel_index_scan_size = '512kB'
   180  min_parallel_table_scan_size = '8MB'
   181  {{- if gt $phy_memory 0 }}
   182  min_wal_size = '{{ printf "%dMB" ( min ( max ( div $phy_memory 8388608 ) 2048 ) 8192 ) }}'
   183  {{- end }}
   184  old_snapshot_threshold = '-1'
   185  parallel_leader_participation = 'True'
   186  password_encryption = 'md5'
   187  pg_stat_statements.max = '5000'
   188  pg_stat_statements.save = 'False'
   189  pg_stat_statements.track = 'top'
   190  # pg_stat_statements.track_planning = 'False'
   191  pg_stat_statements.track_utility = 'False'
   192  # extension: pgaudit
   193  pgaudit.log_catalog = 'True'
   194  pgaudit.log_level = 'log'
   195  pgaudit.log_parameter = 'False'
   196  pgaudit.log_relation = 'False'
   197  pgaudit.log_statement_once = 'False'
   198  # pgaudit.role = ''
   199  #extension: pglogical
   200  pglogical.batch_inserts = 'True'
   201  pglogical.conflict_log_level = 'log'
   202  pglogical.conflict_resolution = 'apply_remote'
   203  # pglogical.extra_connection_options = ''
   204  pglogical.synchronous_commit = 'False'
   205  pglogical.use_spi = 'False'
   206  plan_cache_mode = 'auto'
   207  quote_all_identifiers = 'False'
   208  random_page_cost = '1.1'
   209  row_security = 'True'
   210  session_replication_role = 'origin'
   211  # extension: sql_firewall
   212  sql_firewall.firewall = 'disable'
   213  shared_buffers = '{{ printf "%d%s" $shared_buffers $buffer_unit }}'
   214  # shared_preload_libraries = 'pg_stat_statements,auto_explain,bg_mon,pgextwlist,pg_auth_mon,set_user,pg_cron,pg_stat_kcache'
   215  {{- if $.component.tls }}
   216  {{- $ca_file := getCAFile }}
   217  {{- $cert_file := getCertFile }}
   218  {{- $key_file := getKeyFile }}
   219  ssl = 'True'
   220  ssl_ca_file = '{{ $ca_file }}'
   221  ssl_cert_file = '{{ $cert_file }}'
   222  ssl_key_file = '{{ $key_file }}'
   223  {{- end }}
   224  ssl_min_protocol_version = 'TLSv1'
   225  standard_conforming_strings = 'True'
   226  statement_timeout = '0'
   227  superuser_reserved_connections = '20'
   228  synchronize_seqscans = 'True'
   229  synchronous_commit = 'off'
   230  # synchronous_standby_names=''
   231  tcp_keepalives_count = '10'
   232  tcp_keepalives_idle = '45s'
   233  tcp_keepalives_interval = '10s'
   234  temp_buffers = '8MB'
   235  {{- if gt $phy_memory 0 }}
   236  temp_file_limit = '{{ printf "%dkB" ( div $phy_memory 1024 ) }}'
   237  {{- end }}
   238  # extension: timescaledb
   239  # timescaledb.max_background_workers = '6'
   240  # timescaledb.telemetry_level = 'off'
   241  # TODO timezone
   242  # timezone=Asia/Shanghai
   243  track_activity_query_size = '4096'
   244  track_commit_timestamp = 'False'
   245  track_functions = 'pl'
   246  track_io_timing = 'True'
   247  transform_null_equals = 'False'
   248  vacuum_cost_delay = '0'
   249  vacuum_cost_limit = '10000'
   250  vacuum_cost_page_dirty = '20'
   251  vacuum_cost_page_hit = '1'
   252  vacuum_cost_page_miss = '2'
   253  vacuum_defer_cleanup_age = '0'
   254  vacuum_freeze_min_age = '50000000'
   255  vacuum_freeze_table_age = '200000000'
   256  vacuum_multixact_freeze_min_age = '5000000'
   257  vacuum_multixact_freeze_table_age = '200000000'
   258  wal_buffers = '{{ printf "%dMB" ( div ( min ( max ( div $phy_memory 2097152 ) 2048) 16384 ) 128 ) }}'
   259  wal_compression = 'True'
   260  wal_init_zero = off
   261  wal_level = 'replica'
   262  wal_log_hints = 'False'
   263  wal_receiver_status_interval = '1s'
   264  wal_receiver_timeout = '60000'
   265  wal_sender_timeout = '60000'
   266  wal_writer_delay = '200ms'
   267  wal_writer_flush_after = '1MB'
   268  work_mem = '{{ printf "%dkB" ( max ( div $phy_memory 4194304 ) 4096 ) }}'
   269  xmlbinary = 'base64'
   270  xmloption = 'content'
   271  
   272  ## the following parameters have been deprecated in postgresql 14
   273  operator_precedence_warning = 'off'
   274  vacuum_cleanup_index_scale_factor = '0.1'
   275  wal_keep_segments = '0'