ablog

不器用で落着きのない技術者のメモ

postgresql.conf で設定可能なパラメータ一覧を確認する方法

PostgreSQLの設定は多くの異なる方法で設定することができます。 しかし普通はpostgresql.confファイルで設定することになるでしょう。 固有のオプションはリリースごとに異なります。 使用中のPostgreSQLのsrc/backend/utils/misc/guc.cというソースコードに利用可能なリストがあります。 (しかし普通はpg_settingsビューを参照することで十分だと思います。)

Tuning Your PostgreSQL Server/ja - PostgreSQL wiki
  • pg_settings ビューで確認する
$  psql "host=aurora-postgres124.cluster-********.ap-northeast-1.rds.amazonaws.com user=awsuser dbname=postgres port=5432" 
psql (13.4, server 12.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>  \pset pager
Pager usage is off.
postgres=> select name from pg_settings;
                   name
------------------------------------------
 allow_system_table_mods
 ansi_constraint_trigger_ordering
 ansi_force_foreign_key_checks
 ansi_qualified_update_set_target
 apg_buffer_invalid_lookup_strategy
 apg_enable_batch_mode_function_execution
 apg_enable_correlated_any_transform
 apg_enable_function_migration
 apg_enable_not_in_transform
 apg_enable_remove_redundant_inner_joins
 apg_enable_semijoin_push_down
 apg_force_full_key_semijoin
 apg_force_semijoin_push_down
 apg_force_single_key_semijoin
 application_name
 archive_cleanup_command
 archive_command
 archive_mode
 archive_timeout
 array_nulls
 async_notifications_cache_size
 authentication_timeout
 autovacuum
 autovacuum_analyze_scale_factor
 autovacuum_analyze_threshold
 autovacuum_freeze_max_age
 autovacuum_max_workers
 autovacuum_multixact_freeze_max_age
 autovacuum_naptime
 autovacuum_vacuum_cost_delay
 autovacuum_vacuum_cost_limit
 autovacuum_vacuum_scale_factor
 autovacuum_vacuum_threshold
 autovacuum_work_mem
 backend_flush_after
 backslash_quote
 bgwriter_delay
 bgwriter_flush_after
 bgwriter_lru_maxpages
 bgwriter_lru_multiplier
 block_size
 bonjour
 bonjour_name
 bytea_output
 check_function_bodies
 checkpoint_completion_target
 checkpoint_flush_after
 checkpoint_timeout
 checkpoint_warning
 client_encoding
 client_min_messages
 cluster_name
 commit_delay
 commit_siblings
 commit_timestamp_cache_size
 config_file
 constraint_exclusion
 cpu_index_tuple_cost
 cpu_operator_cost
 cpu_tuple_cost
 cursor_tuple_fraction
 data_checksums
 data_directory
 data_directory_mode
 data_sync_retry
 DateStyle
 db_user_namespace
 deadlock_timeout
 debug_assertions
 debug_pretty_print
 debug_print_parse
 debug_print_plan
 debug_print_rewritten
 default_statistics_target
 default_table_access_method
 default_tablespace
 default_text_search_config
 default_transaction_deferrable
 default_transaction_isolation
 default_transaction_read_only
 dynamic_library_path
 dynamic_shared_memory_type
 effective_cache_size
 effective_io_concurrency
 enable_bitmapscan
 enable_gathermerge
 enable_hashagg
 enable_hashjoin
 enable_indexonlyscan
 enable_indexscan
 enable_material
 enable_mergejoin
 enable_nestloop
 enable_parallel_append
 enable_parallel_hash
 enable_partition_pruning
 enable_partitionwise_aggregate
 enable_partitionwise_join
 enable_seqscan
 enable_sort
 enable_tidscan
 escape_string_warning
 event_source
 eventpoints_mode
 exit_on_error
 external_pid_file
 extra_float_digits
 force_parallel_mode
 from_collapse_limit
 fsync
 full_page_writes
 geqo
 geqo_effort
 geqo_generations
 geqo_pool_size
 geqo_seed
 geqo_selection_bias
 geqo_threshold
 gin_fuzzy_search_limit
 gin_pending_list_limit
 hba_file
 hot_standby
 hot_standby_feedback
 huge_pages
 ident_file
 idle_in_transaction_session_timeout
 ignore_checksum_failure
 ignore_system_indexes
 integer_datetimes
 IntervalStyle
 jit
 jit_above_cost
 jit_debugging_support
 jit_dump_bitcode
 jit_expressions
 jit_inline_above_cost
 jit_optimize_above_cost
 jit_profiling_support
 jit_provider
 jit_tuple_deforming
 join_collapse_limit
 krb_caseins_users
 krb_server_keyfile
 lc_collate
 lc_ctype
 lc_messages
 lc_monetary
 lc_numeric
 lc_time
 listen_addresses
 lo_compat_privileges
 local_preload_libraries
 lock_timeout
 log_autovacuum_min_duration
 log_checkpoints
 log_connections
 log_destination
 log_directory
 log_disconnections
 log_duration
 log_error_verbosity
 log_executor_stats
 log_file_mode
 log_filename
 log_hostname
 log_line_prefix
 log_lock_waits
 log_min_duration_statement
 log_min_error_statement
 log_min_messages
 log_parser_stats
 log_planner_stats
 log_replication_commands
 log_rotation_age
 log_rotation_size
 log_statement
 log_statement_stats
 log_temp_files
 log_timezone
 log_transaction_sample_rate
 log_truncate_on_rotation
 logging_collector
 logical_decoding_work_mem
 maintenance_work_mem
 max_connections
 max_files_per_process
 max_function_args
 max_identifier_length
 max_index_keys
 max_locks_per_transaction
 max_logical_replication_workers
 max_parallel_maintenance_workers
 max_parallel_workers
 max_parallel_workers_per_gather
 max_pred_locks_per_page
 max_pred_locks_per_relation
 max_pred_locks_per_transaction
 max_prepared_transactions
 max_replication_slots
 max_stack_depth
 max_standby_archive_delay
 max_standby_streaming_delay
 max_sync_workers_per_subscription
 max_wal_senders
 max_wal_size
 max_worker_processes
 min_parallel_index_scan_size
 min_parallel_table_scan_size
 min_wal_size
 multixact_members_cache_size
 multixact_offsets_cache_size
 old_snapshot_threshold
 operator_precedence_warning
 parallel_leader_participation
 parallel_setup_cost
 parallel_tuple_cost
 password_encryption
 pg_stat_statements.max
 pg_stat_statements.save
 pg_stat_statements.track
 pg_stat_statements.track_utility
 plan_cache_mode
 port
 post_auth_delay
 pre_auth_delay
 primary_conninfo
 primary_slot_name
 promote_trigger_file
 quote_all_identifiers
 random_page_cost
 rds.enable_global_db_rpo
 rds.extensions
 rds.force_admin_logging_level
 rds.force_autovacuum_logging_level
 rds.global_db_rpo
 rds.internal_databases
 rds.logical_replication
 rds.rds_superuser_reserved_connections
 rds.restrict_logical_slot_creation
 rds.restrict_password_commands
 rds.superuser_variables
 rds.tablespace_path_prefix
 recovery_end_command
 recovery_min_apply_delay
 recovery_target
 recovery_target_action
 recovery_target_inclusive
 recovery_target_lsn
 recovery_target_name
 recovery_target_time
 recovery_target_timeline
 recovery_target_xid
 restart_after_crash
 restore_command
 row_security
 search_path
 segment_size
 seq_page_cost
 server_encoding
 server_version
 server_version_num
 session_preload_libraries
 session_replication_role
 shared_buffers
 shared_memory_type
 shared_preload_libraries
 slru_cache_mode
 ssl
 ssl_ca_file
 ssl_cert_file
 ssl_ciphers
 ssl_crl_file
 ssl_dh_params_file
 ssl_ecdh_curve
 ssl_key_file
 ssl_library
 ssl_max_protocol_version
 ssl_max_protocol_version
 ssl_min_protocol_version
 ssl_min_protocol_version
 ssl_passphrase_command
 ssl_passphrase_command_supports_reload
 ssl_prefer_server_ciphers
 standard_conforming_strings
 statement_timeout
 stats_temp_directory
 subtrans_cache_size
 superuser_reserved_connections
 survivable_cache_mode
 synchronize_seqscans
 synchronous_commit
 synchronous_standby_names
 syslog_facility
 syslog_ident
 syslog_sequence_numbers
 syslog_split_messages
 tcp_keepalives_count
 tcp_keepalives_idle
 tcp_keepalives_interval
 tcp_user_timeout
 temp_buffers
 temp_file_limit
 temp_tablespaces
 TimeZone
 timezone_abbreviations
 trace_notify
 trace_recovery_messages
 trace_sort
 track_activities
 track_activity_query_size
 track_commit_timestamp
 track_counts
 track_functions
 track_io_timing
 transaction_deferrable
 transaction_isolation
 transaction_read_only
 transform_null_equals
 unix_socket_directories
 unix_socket_group
 unix_socket_permissions
 update_process_title
 vacuum_cleanup_index_scale_factor
 vacuum_cost_delay
 vacuum_cost_limit
 vacuum_cost_page_dirty
 vacuum_cost_page_hit
 vacuum_cost_page_miss
 vacuum_defer_cleanup_age
 vacuum_freeze_min_age
 vacuum_freeze_table_age
 vacuum_multixact_freeze_min_age
 vacuum_multixact_freeze_table_age
 wal_block_size
 wal_buffers
 wal_compression
 wal_consistency_checking
 wal_init_zero
 wal_keep_segments
 wal_level
 wal_log_hints
 wal_receiver_status_interval
 wal_receiver_timeout
 wal_recycle
 wal_retrieve_retry_interval
 wal_segment_size
 wal_sender_timeout
 wal_sync_method
 wal_writer_delay
 wal_writer_flush_after
 work_mem
 xmlbinary
 xmloption
 zero_damaged_pages
(354 rows)

参考

パス 内容
src/backend/utils その他色々なコード(設定パラメータ、カタログキャッシュ、メモリ管理、各データ型の実装など)
PostgreSQLのソースコードの構造