以下の手順で構築したpostgres16について、基本的なメタコマンドの結果を掲載します。
OSSDB Goldの対策時に、通勤時間でコマンド結果が見たいと思った自分用のまとめです。
v3は14ですが、新しめのバージョンで確認してます。
設定一覧、反映方法確認
pg_settings
現在の設定は、showコマンドや、View "pg_catalog.pg_settings"で確認可能です。
例えばcontextがsighupなら、サーバを再起動することなくpostgresql.conf内を変更、再度読み込みで、変更を適用可能です。
pg_ctl reloadなどで、postmasterにSIGHUPシグナルを送信すれば反映されます。
https://www.postgresql.jp/document/16/html/view-pg-settings.html
postgres=# show all;
name | setting | description
---------------------------------------------+----------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------
allow_in_place_tablespaces | off | Allows tablespaces directly inside pg_tblspc, for testing.
allow_system_table_mods | off | Allows modifications of the structure of system tables.
application_name | psql | Sets the application name to be reported in statistics and logs.
archive_cleanup_command | | Sets the shell command that will be executed at every restart point.
archive_command | cp %p /var/wal/%f | Sets the shell command that will be called to archive a WAL file.
archive_library | | Sets the library that will be called to archive a WAL file.
archive_mode | on | Allows archiving of WAL files using archive_command.
archive_timeout | 0 | Sets the amount of time to wait before forcing a switch to the next WAL file.
array_nulls | on | Enable input of NULL elements in arrays.
authentication_timeout | 1min | Sets the maximum allowed time to complete client authentication.
autovacuum | on | Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates, or deletes prior to analyze.
autovacuum_freeze_max_age | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_multixact_freeze_max_age | 400000000 | Multixact age at which to autovacuum a table to prevent multixact wraparound.
autovacuum_naptime | 1min | Time to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay | 2ms | Vacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit | -1 | Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_insert_scale_factor | 0.2 | Number of tuple inserts prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_insert_threshold | 1000 | Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums.
autovacuum_vacuum_scale_factor | 0.2 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold | 50 | Minimum number of tuple updates or deletes prior to vacuum.
autovacuum_work_mem | -1 | Sets the maximum memory to be used by each autovacuum worker process.
backend_flush_after | 0 | Number of pages after which previously performed writes are flushed to disk.
backslash_quote | safe_encoding | Sets whether "\'" is allowed in string literals.
backtrace_functions | | Log backtrace for errors in these functions.
bgwriter_delay | 200ms | Background writer sleep time between rounds.
bgwriter_flush_after | 512kB | Number of pages after which previously performed writes are flushed to disk.
bgwriter_lru_maxpages | 100 | Background writer maximum number of LRU pages to flush per round.
bgwriter_lru_multiplier | 2 | Multiple of the average buffer usage to free per round.
block_size | 8192 | Shows the size of a disk block.
bonjour | off | Enables advertising the server via Bonjour.
bonjour_name | | Sets the Bonjour service name.
bytea_output | hex | Sets the output format for bytea.
check_function_bodies | on | Check routine bodies during CREATE FUNCTION and CREATE PROCEDURE.
checkpoint_completion_target | 0.9 | Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.
checkpoint_flush_after | 256kB | Number of pages after which previously performed writes are flushed to disk.
checkpoint_timeout | 5min | Sets the maximum time between automatic WAL checkpoints.
checkpoint_warning | 30s | Sets the maximum time before warning if checkpoints triggered by WAL volume happen too frequently.
client_connection_check_interval | 0 | Sets the time interval between checks for disconnection while running queries.
client_encoding | UTF8 | Sets the client's character set encoding.
client_min_messages | notice | Sets the message levels that are sent to the client.
cluster_name | | Sets the name of the cluster, which is included in the process title.
commit_delay | 0 | Sets the delay in microseconds between transaction commit and flushing WAL to disk.
commit_siblings | 5 | Sets the minimum number of concurrent open transactions required before performing commit_delay.
compute_query_id | auto | Enables in-core computation of query identifiers.
config_file | /var/lib/pgsql/16/data/postgresql.conf | Sets the server's main configuration file.
constraint_exclusion | partition | Enables the planner to use constraints to optimize queries.
cpu_index_tuple_cost | 0.005 | Sets the planner's estimate of the cost of processing each index entry during an index scan.
cpu_operator_cost | 0.0025 | Sets the planner's estimate of the cost of processing each operator or function call.
cpu_tuple_cost | 0.01 | Sets the planner's estimate of the cost of processing each tuple (row).
createrole_self_grant | | Sets whether a CREATEROLE user automatically grants the role to themselves, and with which options.
cursor_tuple_fraction | 0.1 | Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.
data_checksums | off | Shows whether data checksums are turned on for this cluster.
data_directory | /var/lib/pgsql/16/data | Sets the server's data directory.
data_directory_mode | 0700 | Shows the mode of the data directory.
data_sync_retry | off | Whether to continue running after a failure to sync data files.
DateStyle | ISO, MDY | Sets the display format for date and time values.
db_user_namespace | off | Enables per-database user names.
deadlock_timeout | 1s | Sets the time to wait on a lock before checking for deadlock.
debug_assertions | off | Shows whether the running server has assertion checks enabled.
debug_discard_caches | 0 | Aggressively flush system caches for debugging purposes.
debug_io_direct | | Use direct I/O for file access.
debug_logical_replication_streaming | buffered | Forces immediate streaming or serialization of changes in large transactions.
debug_parallel_query | off | Forces the planner's use parallel query nodes.
debug_pretty_print | on | Indents parse and plan tree displays.
debug_print_parse | off | Logs each query's parse tree.
debug_print_plan | off | Logs each query's execution plan.
debug_print_rewritten | off | Logs each query's rewritten parse tree.
default_statistics_target | 100 | Sets the default statistics target.
default_table_access_method | heap | Sets the default table access method for new tables.
default_tablespace | | Sets the default tablespace to create tables and indexes in.
default_text_search_config | pg_catalog.english | Sets default text search configuration.
default_toast_compression | pglz | Sets the default compression method for compressible values.
default_transaction_deferrable | off | Sets the default deferrable status of new transactions.
default_transaction_isolation | read committed | Sets the transaction isolation level of each new transaction.
default_transaction_read_only | off | Sets the default read-only status of new transactions.
dynamic_library_path | $libdir | Sets the path for dynamically loadable modules.
dynamic_shared_memory_type | posix | Selects the dynamic shared memory implementation used.
effective_cache_size | 4GB | Sets the planner's assumption about the total size of the data caches.
effective_io_concurrency | 1 | Number of simultaneous requests that can be handled efficiently by the disk subsystem.
enable_async_append | on | Enables the planner's use of async append plans.
enable_bitmapscan | on | Enables the planner's use of bitmap-scan plans.
enable_gathermerge | on | Enables the planner's use of gather merge plans.
enable_hashagg | on | Enables the planner's use of hashed aggregation plans.
enable_hashjoin | on | Enables the planner's use of hash join plans.
enable_incremental_sort | on | Enables the planner's use of incremental sort steps.
enable_indexonlyscan | on | Enables the planner's use of index-only-scan plans.
enable_indexscan | on | Enables the planner's use of index-scan plans.
enable_material | on | Enables the planner's use of materialization.
enable_memoize | on | Enables the planner's use of memoization.
enable_mergejoin | on | Enables the planner's use of merge join plans.
enable_nestloop | on | Enables the planner's use of nested-loop join plans.
enable_parallel_append | on | Enables the planner's use of parallel append plans.
enable_parallel_hash | on | Enables the planner's use of parallel hash plans.
enable_partition_pruning | on | Enables plan-time and execution-time partition pruning.
enable_partitionwise_aggregate | off | Enables partitionwise aggregation and grouping.
enable_partitionwise_join | off | Enables partitionwise join.
enable_presorted_aggregate | on | Enables the planner's ability to produce plans that provide presorted input for ORDER BY / DISTINCT aggregate functions.
enable_seqscan | on | Enables the planner's use of sequential-scan plans.
enable_sort | on | Enables the planner's use of explicit sort steps.
enable_tidscan | on | Enables the planner's use of TID scan plans.
escape_string_warning | on | Warn about backslash escapes in ordinary string literals.
event_source | PostgreSQL | Sets the application name used to identify PostgreSQL messages in the event log.
exit_on_error | off | Terminate session on any error.
external_pid_file | | Writes the postmaster PID to the specified file.
extra_float_digits | 1 | Sets the number of digits displayed for floating-point values.
from_collapse_limit | 8 | Sets the FROM-list size beyond which subqueries are not collapsed.
fsync | on | Forces synchronization of updates to disk.
full_page_writes | on | Writes full pages to WAL when first modified after a checkpoint.
geqo | on | Enables genetic query optimization.
geqo_effort | 5 | GEQO: effort is used to set the default for other GEQO parameters.
geqo_generations | 0 | GEQO: number of iterations of the algorithm.
geqo_pool_size | 0 | GEQO: number of individuals in the population.
geqo_seed | 0 | GEQO: seed for random path selection.
geqo_selection_bias | 2 | GEQO: selective pressure within the population.
geqo_threshold | 12 | Sets the threshold of FROM items beyond which GEQO is used.
gin_fuzzy_search_limit | 0 | Sets the maximum allowed result for exact search by GIN.
gin_pending_list_limit | 4MB | Sets the maximum size of the pending list for GIN index.
gss_accept_delegation | off | Sets whether GSSAPI delegation should be accepted from the client.
hash_mem_multiplier | 2 | Multiple of work_mem to use for hash tables.
hba_file | /var/lib/pgsql/16/data/pg_hba.conf | Sets the server's "hba" configuration file.
hot_standby | on | Allows connections and queries during recovery.
hot_standby_feedback | off | Allows feedback from a hot standby to the primary that will avoid query conflicts.
huge_page_size | 0 | The size of huge page that should be requested.
huge_pages | try | Use of huge pages on Linux or Windows.
icu_validation_level | warning | Log level for reporting invalid ICU locale strings.
ident_file | /var/lib/pgsql/16/data/pg_ident.conf | Sets the server's "ident" configuration file.
idle_in_transaction_session_timeout | 0 | Sets the maximum allowed idle time between queries, when in a transaction.
idle_session_timeout | 0 | Sets the maximum allowed idle time between queries, when not in a transaction.
ignore_checksum_failure | off | Continues processing after a checksum failure.
ignore_invalid_pages | off | Continues recovery after an invalid pages failure.
ignore_system_indexes | off | Disables reading from system indexes.
in_hot_standby | off | Shows whether hot standby is currently active.
integer_datetimes | on | Shows whether datetimes are integer based.
IntervalStyle | postgres | Sets the display format for interval values.
jit | on | Allow JIT compilation.
jit_above_cost | 100000 | Perform JIT compilation if query is more expensive.
jit_debugging_support | off | Register JIT-compiled functions with debugger.
jit_dump_bitcode | off | Write out LLVM bitcode to facilitate JIT debugging.
jit_expressions | on | Allow JIT compilation of expressions.
jit_inline_above_cost | 500000 | Perform JIT inlining if query is more expensive.
jit_optimize_above_cost | 500000 | Optimize JIT-compiled functions if query is more expensive.
jit_profiling_support | off | Register JIT-compiled functions with perf profiler.
jit_provider | llvmjit | JIT provider to use.
jit_tuple_deforming | on | Allow JIT compilation of tuple deforming.
join_collapse_limit | 8 | Sets the FROM-list size beyond which JOIN constructs are not flattened.
krb_caseins_users | off | Sets whether Kerberos and GSSAPI user names should be treated as case-insensitive.
krb_server_keyfile | FILE:/etc/sysconfig/pgsql/krb5.keytab | Sets the location of the Kerberos server key file.
lc_messages | C.UTF-8 | Sets the language in which messages are displayed.
lc_monetary | C.UTF-8 | Sets the locale for formatting monetary amounts.
lc_numeric | C.UTF-8 | Sets the locale for formatting numbers.
lc_time | C.UTF-8 | Sets the locale for formatting date and time values.
listen_addresses | * | Sets the host name or IP address(es) to listen to.
lo_compat_privileges | off | Enables backward compatibility mode for privilege checks on large objects.
local_preload_libraries | | Lists unprivileged shared libraries to preload into each backend.
lock_timeout | 0 | Sets the maximum allowed duration of any wait for a lock.
log_autovacuum_min_duration | 10min | Sets the minimum execution time above which autovacuum actions will be logged.
log_checkpoints | on | Logs each checkpoint.
log_connections | off | Logs each successful connection.
log_destination | stderr | Sets the destination for server log output.
log_directory | log | Sets the destination directory for log files.
log_disconnections | off | Logs end of a session, including duration.
log_duration | off | Logs the duration of each completed SQL statement.
log_error_verbosity | default | Sets the verbosity of logged messages.
log_executor_stats | off | Writes executor performance statistics to the server log.
log_file_mode | 0600 | Sets the file permissions for log files.
log_filename | postgresql-%a.log | Sets the file name pattern for log files.
log_hostname | off | Logs the host name in the connection logs.
log_line_prefix | %m [%p] | Controls information prefixed to each log line.
log_lock_waits | off | Logs long lock waits.
log_min_duration_sample | -1 | Sets the minimum execution time above which a sample of statements will be logged. Sampling is determined by log_statement_sample_rate.
log_min_duration_statement | -1 | Sets the minimum execution time above which all statements will be logged.
log_min_error_statement | error | Causes all statements generating error at or above this level to be logged.
log_min_messages | warning | Sets the message levels that are logged.
log_parameter_max_length | -1 | Sets the maximum length in bytes of data logged for bind parameter values when logging statements.
log_parameter_max_length_on_error | 0 | Sets the maximum length in bytes of data logged for bind parameter values when logging statements, on error.
log_parser_stats | off | Writes parser performance statistics to the server log.
log_planner_stats | off | Writes planner performance statistics to the server log.
log_recovery_conflict_waits | off | Logs standby recovery conflict waits.
log_replication_commands | off | Logs each replication command.
log_rotation_age | 1d | Sets the amount of time to wait before forcing log file rotation.
log_rotation_size | 0 | Sets the maximum size a log file can reach before being rotated.
log_startup_progress_interval | 10s | Time between progress updates for long-running startup operations.
log_statement | none | Sets the type of statements logged.
log_statement_sample_rate | 1 | Fraction of statements exceeding log_min_duration_sample to be logged.
log_statement_stats | off | Writes cumulative performance statistics to the server log.
log_temp_files | -1 | Log the use of temporary files larger than this number of kilobytes.
log_timezone | Asia/Tokyo | Sets the time zone to use in log messages.
log_transaction_sample_rate | 0 | Sets the fraction of transactions from which to log all statements.
log_truncate_on_rotation | on | Truncate existing log files of same name during log rotation.
logging_collector | on | Start a subprocess to capture stderr, csvlog and/or jsonlog into log files.
logical_decoding_work_mem | 64MB | Sets the maximum memory to be used for logical decoding.
maintenance_io_concurrency | 10 | A variant of effective_io_concurrency that is used for maintenance work.
maintenance_work_mem | 64MB | Sets the maximum memory to be used for maintenance operations.
max_connections | 100 | Sets the maximum number of concurrent connections.
max_files_per_process | 1000 | Sets the maximum number of simultaneously open files for each server process.
max_function_args | 100 | Shows the maximum number of function arguments.
max_identifier_length | 63 | Shows the maximum identifier length.
max_index_keys | 32 | Shows the maximum number of index keys.
max_locks_per_transaction | 64 | Sets the maximum number of locks per transaction.
max_logical_replication_workers | 4 | Maximum number of logical replication worker processes.
max_parallel_apply_workers_per_subscription | 2 | Maximum number of parallel apply workers per subscription.
max_parallel_maintenance_workers | 2 | Sets the maximum number of parallel processes per maintenance operation.
max_parallel_workers | 8 | Sets the maximum number of parallel workers that can be active at one time.
max_parallel_workers_per_gather | 2 | Sets the maximum number of parallel processes per executor node.
max_pred_locks_per_page | 2 | Sets the maximum number of predicate-locked tuples per page.
max_pred_locks_per_relation | -2 | Sets the maximum number of predicate-locked pages and tuples per relation.
max_pred_locks_per_transaction | 64 | Sets the maximum number of predicate locks per transaction.
max_prepared_transactions | 0 | Sets the maximum number of simultaneously prepared transactions.
max_replication_slots | 10 | Sets the maximum number of simultaneously defined replication slots.
max_slot_wal_keep_size | -1 | Sets the maximum WAL size that can be reserved by replication slots.
max_stack_depth | 2MB | Sets the maximum stack depth, in kilobytes.
max_standby_archive_delay | 30s | Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data.
max_standby_streaming_delay | 30s | Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data.
max_sync_workers_per_subscription | 2 | Maximum number of table synchronization workers per subscription.
max_wal_senders | 3 | Sets the maximum number of simultaneously running WAL sender processes.
max_wal_size | 1GB | Sets the WAL size that triggers a checkpoint.
max_worker_processes | 8 | Maximum number of concurrent worker processes.
min_dynamic_shared_memory | 0 | Amount of dynamic shared memory reserved at startup.
min_parallel_index_scan_size | 512kB | Sets the minimum amount of index data for a parallel scan.
min_parallel_table_scan_size | 8MB | Sets the minimum amount of table data for a parallel scan.
min_wal_size | 80MB | Sets the minimum size to shrink the WAL to.
old_snapshot_threshold | -1 | Time before a snapshot is too old to read pages changed after the snapshot was taken.
parallel_leader_participation | on | Controls whether Gather and Gather Merge also run subplans.
parallel_setup_cost | 1000 | Sets the planner's estimate of the cost of starting up worker processes for parallel query.
parallel_tuple_cost | 0.1 | Sets the planner's estimate of the cost of passing each tuple (row) from worker to leader backend.
password_encryption | scram-sha-256 | Chooses the algorithm for encrypting passwords.
plan_cache_mode | auto | Controls the planner's selection of custom or generic plan.
port | 5432 | Sets the TCP port the server listens on.
post_auth_delay | 0 | Sets the amount of time to wait after authentication on connection startup.
pre_auth_delay | 0 | Sets the amount of time to wait before authentication on connection startup.
primary_conninfo | | Sets the connection string to be used to connect to the sending server.
primary_slot_name | | Sets the name of the replication slot to use on the sending server.
quote_all_identifiers | off | When generating SQL fragments, quote all identifiers.
random_page_cost | 4 | Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
recovery_end_command | | Sets the shell command that will be executed once at the end of recovery.
recovery_init_sync_method | fsync | Sets the method for synchronizing the data directory before crash recovery.
recovery_min_apply_delay | 0 | Sets the minimum delay for applying changes during recovery.
recovery_prefetch | try | Prefetch referenced blocks during recovery.
recovery_target | | Set to "immediate" to end recovery as soon as a consistent state is reached.
recovery_target_action | pause | Sets the action to perform upon reaching the recovery target.
recovery_target_inclusive | on | Sets whether to include or exclude transaction with recovery target.
recovery_target_lsn | | Sets the LSN of the write-ahead log location up to which recovery will proceed.
recovery_target_name | | Sets the named restore point up to which recovery will proceed.
recovery_target_time | | Sets the time stamp up to which recovery will proceed.
recovery_target_timeline | latest | Specifies the timeline to recover into.
recovery_target_xid | | Sets the transaction ID up to which recovery will proceed.
recursive_worktable_factor | 10 | Sets the planner's estimate of the average size of a recursive query's working table.
remove_temp_files_after_crash | on | Remove temporary files after backend crash.
reserved_connections | 0 | Sets the number of connection slots reserved for roles with privileges of pg_use_reserved_connections.
restart_after_crash | on | Reinitialize server after backend crash.
restore_command | | Sets the shell command that will be called to retrieve an archived WAL file.
restrict_nonsystem_relation_kind | | Prohibits access to non-system relations of specified kinds.
row_security | on | Enable row security.
scram_iterations | 4096 | Sets the iteration count for SCRAM secret generation.
search_path | "$user", public | Sets the schema search order for names that are not schema-qualified.
segment_size | 1GB | Shows the number of pages per disk file.
send_abort_for_crash | off | Send SIGABRT not SIGQUIT to child processes after backend crash.
send_abort_for_kill | off | Send SIGABRT not SIGKILL to stuck child processes.
seq_page_cost | 1 | Sets the planner's estimate of the cost of a sequentially fetched disk page.
server_encoding | UTF8 | Shows the server (database) character set encoding.
server_version | 16.10 | Shows the server version.
server_version_num | 160010 | Shows the server version as an integer.
session_preload_libraries | | Lists shared libraries to preload into each backend.
session_replication_role | origin | Sets the session's behavior for triggers and rewrite rules.
shared_buffers | 128MB | Sets the number of shared memory buffers used by the server.
shared_memory_size | 143MB | Shows the size of the server's main shared memory area (rounded up to the nearest MB).
shared_memory_size_in_huge_pages | 72 | Shows the number of huge pages needed for the main shared memory area.
shared_memory_type | mmap | Selects the shared memory implementation used for the main shared memory region.
shared_preload_libraries | | Lists shared libraries to preload into server.
ssl | off | Enables SSL connections.
ssl_ca_file | | Location of the SSL certificate authority file.
ssl_cert_file | server.crt | Location of the SSL server certificate file.
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL | Sets the list of allowed SSL ciphers.
ssl_crl_dir | | Location of the SSL certificate revocation list directory.
ssl_crl_file | | Location of the SSL certificate revocation list file.
ssl_dh_params_file | | Location of the SSL DH parameters file.
ssl_ecdh_curve | prime256v1 | Sets the curve to use for ECDH.
ssl_key_file | server.key | Location of the SSL server private key file.
ssl_library | OpenSSL | Shows the name of the SSL library.
ssl_max_protocol_version | | Sets the maximum SSL/TLS protocol version to use.
ssl_min_protocol_version | TLSv1.2 | Sets the minimum SSL/TLS protocol version to use.
ssl_passphrase_command | | Command to obtain passphrases for SSL.
ssl_passphrase_command_supports_reload | off | Controls whether ssl_passphrase_command is called during server reload.
ssl_prefer_server_ciphers | on | Give priority to server ciphersuite order.
standard_conforming_strings | on | Causes '...' strings to treat backslashes literally.
statement_timeout | 0 | Sets the maximum allowed duration of any statement.
stats_fetch_consistency | cache | Sets the consistency of accesses to statistics data.
superuser_reserved_connections | 3 | Sets the number of connection slots reserved for superusers.
synchronize_seqscans | on | Enable synchronized sequential scans.
synchronous_commit | on | Sets the current transaction's synchronization level.
synchronous_standby_names | s1 | Number of synchronous standbys and list of names of potential synchronous ones.
syslog_facility | local0 | Sets the syslog "facility" to be used when syslog enabled.
syslog_ident | postgres | Sets the program name used to identify PostgreSQL messages in syslog.
syslog_sequence_numbers | on | Add sequence number to syslog messages to avoid duplicate suppression.
syslog_split_messages | on | Split messages sent to syslog by lines and to fit into 1024 bytes.
tcp_keepalives_count | 0 | Maximum number of TCP keepalive retransmits.
tcp_keepalives_idle | 0 | Time between issuing TCP keepalives.
tcp_keepalives_interval | 0 | Time between TCP keepalive retransmits.
tcp_user_timeout | 0 | TCP user timeout.
temp_buffers | 8MB | Sets the maximum number of temporary buffers used by each session.
temp_file_limit | -1 | Limits the total size of all temporary files used by each process.
temp_tablespaces | | Sets the tablespace(s) to use for temporary tables and sort files.
TimeZone | Asia/Tokyo | Sets the time zone for displaying and interpreting time stamps.
timezone_abbreviations | Default | Selects a file of time zone abbreviations.
trace_notify | off | Generates debugging output for LISTEN and NOTIFY.
trace_recovery_messages | log | Enables logging of recovery-related debugging information.
trace_sort | off | Emit information about resource usage in sorting.
track_activities | on | Collects information about executing commands.
track_activity_query_size | 1kB | Sets the size reserved for pg_stat_activity.query, in bytes.
track_commit_timestamp | off | Collects transaction commit time.
track_counts | on | Collects statistics on database activity.
track_functions | none | Collects function-level statistics on database activity.
track_io_timing | off | Collects timing statistics for database I/O activity.
track_wal_io_timing | off | Collects timing statistics for WAL I/O activity.
transaction_deferrable | off | Whether to defer a read-only serializable transaction until it can be executed with no possible serialization failures.
transaction_isolation | read committed | Sets the current transaction's isolation level.
transaction_read_only | off | Sets the current transaction's read-only status.
transform_null_equals | off | Treats "expr=NULL" as "expr IS NULL".
unix_socket_directories | /run/postgresql, /tmp | Sets the directories where Unix-domain sockets will be created.
unix_socket_group | | Sets the owning group of the Unix-domain socket.
unix_socket_permissions | 0777 | Sets the access permissions of the Unix-domain socket.
update_process_title | on | Updates the process title to show the active SQL command.
vacuum_buffer_usage_limit | 256kB | Sets the buffer pool size for VACUUM, ANALYZE, and autovacuum.
vacuum_cost_delay | 0 | Vacuum cost delay in milliseconds.
vacuum_cost_limit | 200 | Vacuum cost amount available before napping.
vacuum_cost_page_dirty | 20 | Vacuum cost for a page dirtied by vacuum.
vacuum_cost_page_hit | 1 | Vacuum cost for a page found in the buffer cache.
vacuum_cost_page_miss | 2 | Vacuum cost for a page not found in the buffer cache.
vacuum_failsafe_age | 1600000000 | Age at which VACUUM should trigger failsafe to avoid a wraparound outage.
vacuum_freeze_min_age | 50000000 | Minimum age at which VACUUM should freeze a table row.
vacuum_freeze_table_age | 150000000 | Age at which VACUUM should scan whole table to freeze tuples.
vacuum_multixact_failsafe_age | 1600000000 | Multixact age at which VACUUM should trigger failsafe to avoid a wraparound outage.
vacuum_multixact_freeze_min_age | 5000000 | Minimum age at which VACUUM should freeze a MultiXactId in a table row.
vacuum_multixact_freeze_table_age | 150000000 | Multixact age at which VACUUM should scan whole table to freeze tuples.
wal_block_size | 8192 | Shows the block size in the write ahead log.
wal_buffers | 4MB | Sets the number of disk-page buffers in shared memory for WAL.
wal_compression | off | Compresses full-page writes written in WAL file with specified method.
wal_consistency_checking | | Sets the WAL resource managers for which WAL consistency checks are done.
wal_decode_buffer_size | 512kB | Buffer size for reading ahead in the WAL during recovery.
wal_init_zero | on | Writes zeroes to new WAL files before first use.
wal_keep_size | 0 | Sets the size of WAL files held for standby servers.
wal_level | replica | Sets the level of information written to the WAL.
wal_log_hints | off | Writes full pages to WAL when first modified after a checkpoint, even for a non-critical modification.
wal_receiver_create_temp_slot | off | Sets whether a WAL receiver should create a temporary replication slot if no permanent slot is configured.
wal_receiver_status_interval | 10s | Sets the maximum interval between WAL receiver status reports to the sending server.
wal_receiver_timeout | 1min | Sets the maximum wait time to receive data from the sending server.
wal_recycle | on | Recycles WAL files by renaming them.
wal_retrieve_retry_interval | 5s | Sets the time to wait before retrying to retrieve WAL after a failed attempt.
wal_segment_size | 16MB | Shows the size of write ahead log segments.
wal_sender_timeout | 1min | Sets the maximum time to wait for WAL replication.
wal_skip_threshold | 2MB | Minimum size of new file to fsync instead of writing WAL.
wal_sync_method | fdatasync | Selects the method used for forcing WAL updates to disk.
wal_writer_delay | 200ms | Time between WAL flushes performed in the WAL writer.
wal_writer_flush_after | 1MB | Amount of WAL written out by WAL writer that triggers a flush.
work_mem | 4MB | Sets the maximum memory to be used for query workspaces.
xmlbinary | base64 | Sets how binary values are to be encoded in XML.
xmloption | content | Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments.
zero_damaged_pages | off | Continues processing past damaged page headers.
(361 rows)
postgres=# select name, setting, category, context from pg_settings;
name | setting | category | context
---------------------------------------------+----------------------------------------+-------------------------------------------------------------------+-------------------
allow_in_place_tablespaces | off | Developer Options | superuser
allow_system_table_mods | off | Developer Options | superuser
application_name | psql | Reporting and Logging / What to Log | user
archive_cleanup_command | | Write-Ahead Log / Archive Recovery | sighup
archive_command | cp %p /var/wal/%f | Write-Ahead Log / Archiving | sighup
archive_library | | Write-Ahead Log / Archiving | sighup
archive_mode | on | Write-Ahead Log / Archiving | postmaster
archive_timeout | 0 | Write-Ahead Log / Archiving | sighup
array_nulls | on | Version and Platform Compatibility / Previous PostgreSQL Versions | user
authentication_timeout | 60 | Connections and Authentication / Authentication | sighup
autovacuum | on | Autovacuum | sighup
autovacuum_analyze_scale_factor | 0.1 | Autovacuum | sighup
autovacuum_analyze_threshold | 50 | Autovacuum | sighup
autovacuum_freeze_max_age | 200000000 | Autovacuum | postmaster
autovacuum_max_workers | 3 | Autovacuum | postmaster
autovacuum_multixact_freeze_max_age | 400000000 | Autovacuum | postmaster
autovacuum_naptime | 60 | Autovacuum | sighup
autovacuum_vacuum_cost_delay | 2 | Autovacuum | sighup
autovacuum_vacuum_cost_limit | -1 | Autovacuum | sighup
autovacuum_vacuum_insert_scale_factor | 0.2 | Autovacuum | sighup
autovacuum_vacuum_insert_threshold | 1000 | Autovacuum | sighup
autovacuum_vacuum_scale_factor | 0.2 | Autovacuum | sighup
autovacuum_vacuum_threshold | 50 | Autovacuum | sighup
autovacuum_work_mem | -1 | Resource Usage / Memory | sighup
backend_flush_after | 0 | Resource Usage / Asynchronous Behavior | user
backslash_quote | safe_encoding | Version and Platform Compatibility / Previous PostgreSQL Versions | user
backtrace_functions | | Developer Options | superuser
bgwriter_delay | 200 | Resource Usage / Background Writer | sighup
bgwriter_flush_after | 64 | Resource Usage / Background Writer | sighup
bgwriter_lru_maxpages | 100 | Resource Usage / Background Writer | sighup
bgwriter_lru_multiplier | 2 | Resource Usage / Background Writer | sighup
block_size | 8192 | Preset Options | internal
bonjour | off | Connections and Authentication / Connection Settings | postmaster
bonjour_name | | Connections and Authentication / Connection Settings | postmaster
bytea_output | hex | Client Connection Defaults / Statement Behavior | user
check_function_bodies | on | Client Connection Defaults / Statement Behavior | user
checkpoint_completion_target | 0.9 | Write-Ahead Log / Checkpoints | sighup
checkpoint_flush_after | 32 | Write-Ahead Log / Checkpoints | sighup
checkpoint_timeout | 300 | Write-Ahead Log / Checkpoints | sighup
checkpoint_warning | 30 | Write-Ahead Log / Checkpoints | sighup
client_connection_check_interval | 0 | Connections and Authentication / TCP Settings | user
client_encoding | UTF8 | Client Connection Defaults / Locale and Formatting | user
client_min_messages | notice | Client Connection Defaults / Statement Behavior | user
cluster_name | | Reporting and Logging / Process Title | postmaster
commit_delay | 0 | Write-Ahead Log / Settings | superuser
commit_siblings | 5 | Write-Ahead Log / Settings | user
compute_query_id | auto | Statistics / Monitoring | superuser
config_file | /var/lib/pgsql/16/data/postgresql.conf | File Locations | postmaster
constraint_exclusion | partition | Query Tuning / Other Planner Options | user
cpu_index_tuple_cost | 0.005 | Query Tuning / Planner Cost Constants | user
cpu_operator_cost | 0.0025 | Query Tuning / Planner Cost Constants | user
cpu_tuple_cost | 0.01 | Query Tuning / Planner Cost Constants | user
createrole_self_grant | | Client Connection Defaults / Statement Behavior | user
cursor_tuple_fraction | 0.1 | Query Tuning / Other Planner Options | user
data_checksums | off | Preset Options | internal
data_directory | /var/lib/pgsql/16/data | File Locations | postmaster
data_directory_mode | 0700 | Preset Options | internal
data_sync_retry | off | Error Handling | postmaster
DateStyle | ISO, MDY | Client Connection Defaults / Locale and Formatting | user
db_user_namespace | off | Connections and Authentication / Authentication | sighup
deadlock_timeout | 1000 | Lock Management | superuser
debug_assertions | off | Preset Options | internal
debug_discard_caches | 0 | Developer Options | superuser
debug_io_direct | | Developer Options | postmaster
debug_logical_replication_streaming | buffered | Developer Options | user
debug_parallel_query | off | Developer Options | user
debug_pretty_print | on | Reporting and Logging / What to Log | user
debug_print_parse | off | Reporting and Logging / What to Log | user
debug_print_plan | off | Reporting and Logging / What to Log | user
debug_print_rewritten | off | Reporting and Logging / What to Log | user
default_statistics_target | 100 | Query Tuning / Other Planner Options | user
default_table_access_method | heap | Client Connection Defaults / Statement Behavior | user
default_tablespace | | Client Connection Defaults / Statement Behavior | user
default_text_search_config | pg_catalog.english | Client Connection Defaults / Locale and Formatting | user
default_toast_compression | pglz | Client Connection Defaults / Statement Behavior | user
default_transaction_deferrable | off | Client Connection Defaults / Statement Behavior | user
default_transaction_isolation | read committed | Client Connection Defaults / Statement Behavior | user
default_transaction_read_only | off | Client Connection Defaults / Statement Behavior | user
dynamic_library_path | $libdir | Client Connection Defaults / Other Defaults | superuser
dynamic_shared_memory_type | posix | Resource Usage / Memory | postmaster
effective_cache_size | 524288 | Query Tuning / Planner Cost Constants | user
effective_io_concurrency | 1 | Resource Usage / Asynchronous Behavior | user
enable_async_append | on | Query Tuning / Planner Method Configuration | user
enable_bitmapscan | on | Query Tuning / Planner Method Configuration | user
enable_gathermerge | on | Query Tuning / Planner Method Configuration | user
enable_hashagg | on | Query Tuning / Planner Method Configuration | user
enable_hashjoin | on | Query Tuning / Planner Method Configuration | user
enable_incremental_sort | on | Query Tuning / Planner Method Configuration | user
enable_indexonlyscan | on | Query Tuning / Planner Method Configuration | user
enable_indexscan | on | Query Tuning / Planner Method Configuration | user
enable_material | on | Query Tuning / Planner Method Configuration | user
enable_memoize | on | Query Tuning / Planner Method Configuration | user
enable_mergejoin | on | Query Tuning / Planner Method Configuration | user
enable_nestloop | on | Query Tuning / Planner Method Configuration | user
enable_parallel_append | on | Query Tuning / Planner Method Configuration | user
enable_parallel_hash | on | Query Tuning / Planner Method Configuration | user
enable_partition_pruning | on | Query Tuning / Planner Method Configuration | user
enable_partitionwise_aggregate | off | Query Tuning / Planner Method Configuration | user
enable_partitionwise_join | off | Query Tuning / Planner Method Configuration | user
enable_presorted_aggregate | on | Query Tuning / Planner Method Configuration | user
enable_seqscan | on | Query Tuning / Planner Method Configuration | user
enable_sort | on | Query Tuning / Planner Method Configuration | user
enable_tidscan | on | Query Tuning / Planner Method Configuration | user
escape_string_warning | on | Version and Platform Compatibility / Previous PostgreSQL Versions | user
event_source | PostgreSQL | Reporting and Logging / Where to Log | postmaster
exit_on_error | off | Error Handling | user
external_pid_file | | File Locations | postmaster
extra_float_digits | 1 | Client Connection Defaults / Locale and Formatting | user
from_collapse_limit | 8 | Query Tuning / Other Planner Options | user
fsync | on | Write-Ahead Log / Settings | sighup
full_page_writes | on | Write-Ahead Log / Settings | sighup
geqo | on | Query Tuning / Genetic Query Optimizer | user
geqo_effort | 5 | Query Tuning / Genetic Query Optimizer | user
geqo_generations | 0 | Query Tuning / Genetic Query Optimizer | user
geqo_pool_size | 0 | Query Tuning / Genetic Query Optimizer | user
geqo_seed | 0 | Query Tuning / Genetic Query Optimizer | user
geqo_selection_bias | 2 | Query Tuning / Genetic Query Optimizer | user
geqo_threshold | 12 | Query Tuning / Genetic Query Optimizer | user
gin_fuzzy_search_limit | 0 | Client Connection Defaults / Other Defaults | user
gin_pending_list_limit | 4096 | Client Connection Defaults / Statement Behavior | user
gss_accept_delegation | off | Connections and Authentication / Authentication | sighup
hash_mem_multiplier | 2 | Resource Usage / Memory | user
hba_file | /var/lib/pgsql/16/data/pg_hba.conf | File Locations | postmaster
hot_standby | on | Replication / Standby Servers | postmaster
hot_standby_feedback | off | Replication / Standby Servers | sighup
huge_page_size | 0 | Resource Usage / Memory | postmaster
huge_pages | try | Resource Usage / Memory | postmaster
icu_validation_level | warning | Client Connection Defaults / Locale and Formatting | user
ident_file | /var/lib/pgsql/16/data/pg_ident.conf | File Locations | postmaster
idle_in_transaction_session_timeout | 0 | Client Connection Defaults / Statement Behavior | user
idle_session_timeout | 0 | Client Connection Defaults / Statement Behavior | user
ignore_checksum_failure | off | Developer Options | superuser
ignore_invalid_pages | off | Developer Options | postmaster
ignore_system_indexes | off | Developer Options | backend
in_hot_standby | off | Preset Options | internal
integer_datetimes | on | Preset Options | internal
IntervalStyle | postgres | Client Connection Defaults / Locale and Formatting | user
jit | on | Query Tuning / Other Planner Options | user
jit_above_cost | 100000 | Query Tuning / Planner Cost Constants | user
jit_debugging_support | off | Developer Options | superuser-backend
jit_dump_bitcode | off | Developer Options | superuser
jit_expressions | on | Developer Options | user
jit_inline_above_cost | 500000 | Query Tuning / Planner Cost Constants | user
jit_optimize_above_cost | 500000 | Query Tuning / Planner Cost Constants | user
jit_profiling_support | off | Developer Options | superuser-backend
jit_provider | llvmjit | Client Connection Defaults / Shared Library Preloading | postmaster
jit_tuple_deforming | on | Developer Options | user
join_collapse_limit | 8 | Query Tuning / Other Planner Options | user
krb_caseins_users | off | Connections and Authentication / Authentication | sighup
krb_server_keyfile | FILE:/etc/sysconfig/pgsql/krb5.keytab | Connections and Authentication / Authentication | sighup
lc_messages | C.UTF-8 | Client Connection Defaults / Locale and Formatting | superuser
lc_monetary | C.UTF-8 | Client Connection Defaults / Locale and Formatting | user
lc_numeric | C.UTF-8 | Client Connection Defaults / Locale and Formatting | user
lc_time | C.UTF-8 | Client Connection Defaults / Locale and Formatting | user
listen_addresses | * | Connections and Authentication / Connection Settings | postmaster
lo_compat_privileges | off | Version and Platform Compatibility / Previous PostgreSQL Versions | superuser
local_preload_libraries | | Client Connection Defaults / Shared Library Preloading | user
lock_timeout | 0 | Client Connection Defaults / Statement Behavior | user
log_autovacuum_min_duration | 600000 | Reporting and Logging / What to Log | sighup
log_checkpoints | on | Reporting and Logging / What to Log | sighup
log_connections | off | Reporting and Logging / What to Log | superuser-backend
log_destination | stderr | Reporting and Logging / Where to Log | sighup
log_directory | log | Reporting and Logging / Where to Log | sighup
log_disconnections | off | Reporting and Logging / What to Log | superuser-backend
log_duration | off | Reporting and Logging / What to Log | superuser
log_error_verbosity | default | Reporting and Logging / What to Log | superuser
log_executor_stats | off | Statistics / Monitoring | superuser
log_file_mode | 0600 | Reporting and Logging / Where to Log | sighup
log_filename | postgresql-%a.log | Reporting and Logging / Where to Log | sighup
log_hostname | off | Reporting and Logging / What to Log | sighup
log_line_prefix | %m [%p] | Reporting and Logging / What to Log | sighup
log_lock_waits | off | Reporting and Logging / What to Log | superuser
log_min_duration_sample | -1 | Reporting and Logging / When to Log | superuser
log_min_duration_statement | -1 | Reporting and Logging / When to Log | superuser
log_min_error_statement | error | Reporting and Logging / When to Log | superuser
log_min_messages | warning | Reporting and Logging / When to Log | superuser
log_parameter_max_length | -1 | Reporting and Logging / What to Log | superuser
log_parameter_max_length_on_error | 0 | Reporting and Logging / What to Log | user
log_parser_stats | off | Statistics / Monitoring | superuser
log_planner_stats | off | Statistics / Monitoring | superuser
log_recovery_conflict_waits | off | Reporting and Logging / What to Log | sighup
log_replication_commands | off | Reporting and Logging / What to Log | superuser
log_rotation_age | 1440 | Reporting and Logging / Where to Log | sighup
log_rotation_size | 0 | Reporting and Logging / Where to Log | sighup
log_startup_progress_interval | 10000 | Reporting and Logging / When to Log | sighup
log_statement | none | Reporting and Logging / What to Log | superuser
log_statement_sample_rate | 1 | Reporting and Logging / When to Log | superuser
log_statement_stats | off | Statistics / Monitoring | superuser
log_temp_files | -1 | Reporting and Logging / What to Log | superuser
log_timezone | Asia/Tokyo | Reporting and Logging / What to Log | sighup
log_transaction_sample_rate | 0 | Reporting and Logging / When to Log | superuser
log_truncate_on_rotation | on | Reporting and Logging / Where to Log | sighup
logging_collector | on | Reporting and Logging / Where to Log | postmaster
logical_decoding_work_mem | 65536 | Resource Usage / Memory | user
maintenance_io_concurrency | 10 | Resource Usage / Asynchronous Behavior | user
maintenance_work_mem | 65536 | Resource Usage / Memory | user
max_connections | 100 | Connections and Authentication / Connection Settings | postmaster
max_files_per_process | 1000 | Resource Usage / Kernel Resources | postmaster
max_function_args | 100 | Preset Options | internal
max_identifier_length | 63 | Preset Options | internal
max_index_keys | 32 | Preset Options | internal
max_locks_per_transaction | 64 | Lock Management | postmaster
max_logical_replication_workers | 4 | Replication / Subscribers | postmaster
max_parallel_apply_workers_per_subscription | 2 | Replication / Subscribers | sighup
max_parallel_maintenance_workers | 2 | Resource Usage / Asynchronous Behavior | user
max_parallel_workers | 8 | Resource Usage / Asynchronous Behavior | user
max_parallel_workers_per_gather | 2 | Resource Usage / Asynchronous Behavior | user
max_pred_locks_per_page | 2 | Lock Management | sighup
max_pred_locks_per_relation | -2 | Lock Management | sighup
max_pred_locks_per_transaction | 64 | Lock Management | postmaster
max_prepared_transactions | 0 | Resource Usage / Memory | postmaster
max_replication_slots | 10 | Replication / Sending Servers | postmaster
max_slot_wal_keep_size | -1 | Replication / Sending Servers | sighup
max_stack_depth | 2048 | Resource Usage / Memory | superuser
max_standby_archive_delay | 30000 | Replication / Standby Servers | sighup
max_standby_streaming_delay | 30000 | Replication / Standby Servers | sighup
max_sync_workers_per_subscription | 2 | Replication / Subscribers | sighup
max_wal_senders | 3 | Replication / Sending Servers | postmaster
max_wal_size | 1024 | Write-Ahead Log / Checkpoints | sighup
max_worker_processes | 8 | Resource Usage / Asynchronous Behavior | postmaster
min_dynamic_shared_memory | 0 | Resource Usage / Memory | postmaster
min_parallel_index_scan_size | 64 | Query Tuning / Planner Cost Constants | user
min_parallel_table_scan_size | 1024 | Query Tuning / Planner Cost Constants | user
min_wal_size | 80 | Write-Ahead Log / Checkpoints | sighup
old_snapshot_threshold | -1 | Resource Usage / Asynchronous Behavior | postmaster
parallel_leader_participation | on | Resource Usage / Asynchronous Behavior | user
parallel_setup_cost | 1000 | Query Tuning / Planner Cost Constants | user
parallel_tuple_cost | 0.1 | Query Tuning / Planner Cost Constants | user
password_encryption | scram-sha-256 | Connections and Authentication / Authentication | user
plan_cache_mode | auto | Query Tuning / Other Planner Options | user
port | 5432 | Connections and Authentication / Connection Settings | postmaster
post_auth_delay | 0 | Developer Options | backend
pre_auth_delay | 0 | Developer Options | sighup
primary_conninfo | | Replication / Standby Servers | sighup
primary_slot_name | | Replication / Standby Servers | sighup
quote_all_identifiers | off | Version and Platform Compatibility / Previous PostgreSQL Versions | user
random_page_cost | 4 | Query Tuning / Planner Cost Constants | user
recovery_end_command | | Write-Ahead Log / Archive Recovery | sighup
recovery_init_sync_method | fsync | Error Handling | sighup
recovery_min_apply_delay | 0 | Replication / Standby Servers | sighup
recovery_prefetch | try | Write-Ahead Log / Recovery | sighup
recovery_target | | Write-Ahead Log / Recovery Target | postmaster
recovery_target_action | pause | Write-Ahead Log / Recovery Target | postmaster
recovery_target_inclusive | on | Write-Ahead Log / Recovery Target | postmaster
recovery_target_lsn | | Write-Ahead Log / Recovery Target | postmaster
recovery_target_name | | Write-Ahead Log / Recovery Target | postmaster
recovery_target_time | | Write-Ahead Log / Recovery Target | postmaster
recovery_target_timeline | latest | Write-Ahead Log / Recovery Target | postmaster
recovery_target_xid | | Write-Ahead Log / Recovery Target | postmaster
recursive_worktable_factor | 10 | Query Tuning / Other Planner Options | user
remove_temp_files_after_crash | on | Developer Options | sighup
reserved_connections | 0 | Connections and Authentication / Connection Settings | postmaster
restart_after_crash | on | Error Handling | sighup
restore_command | | Write-Ahead Log / Archive Recovery | sighup
restrict_nonsystem_relation_kind | | Client Connection Defaults / Statement Behavior | user
row_security | on | Client Connection Defaults / Statement Behavior | user
scram_iterations | 4096 | Connections and Authentication / Authentication | user
search_path | "$user", public | Client Connection Defaults / Statement Behavior | user
segment_size | 131072 | Preset Options | internal
send_abort_for_crash | off | Developer Options | sighup
send_abort_for_kill | off | Developer Options | sighup
seq_page_cost | 1 | Query Tuning / Planner Cost Constants | user
server_encoding | UTF8 | Preset Options | internal
server_version | 16.10 | Preset Options | internal
server_version_num | 160010 | Preset Options | internal
session_preload_libraries | | Client Connection Defaults / Shared Library Preloading | superuser
session_replication_role | origin | Client Connection Defaults / Statement Behavior | superuser
shared_buffers | 16384 | Resource Usage / Memory | postmaster
shared_memory_size | 143 | Preset Options | internal
shared_memory_size_in_huge_pages | 72 | Preset Options | internal
shared_memory_type | mmap | Resource Usage / Memory | postmaster
shared_preload_libraries | | Client Connection Defaults / Shared Library Preloading | postmaster
ssl | off | Connections and Authentication / SSL | sighup
ssl_ca_file | | Connections and Authentication / SSL | sighup
ssl_cert_file | server.crt | Connections and Authentication / SSL | sighup
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL | Connections and Authentication / SSL | sighup
ssl_crl_dir | | Connections and Authentication / SSL | sighup
ssl_crl_file | | Connections and Authentication / SSL | sighup
ssl_dh_params_file | | Connections and Authentication / SSL | sighup
ssl_ecdh_curve | prime256v1 | Connections and Authentication / SSL | sighup
ssl_key_file | server.key | Connections and Authentication / SSL | sighup
ssl_library | OpenSSL | Preset Options | internal
ssl_max_protocol_version | | Connections and Authentication / SSL | sighup
ssl_min_protocol_version | TLSv1.2 | Connections and Authentication / SSL | sighup
ssl_passphrase_command | | Connections and Authentication / SSL | sighup
ssl_passphrase_command_supports_reload | off | Connections and Authentication / SSL | sighup
ssl_prefer_server_ciphers | on | Connections and Authentication / SSL | sighup
standard_conforming_strings | on | Version and Platform Compatibility / Previous PostgreSQL Versions | user
statement_timeout | 0 | Client Connection Defaults / Statement Behavior | user
stats_fetch_consistency | cache | Statistics / Cumulative Query and Index Statistics | user
superuser_reserved_connections | 3 | Connections and Authentication / Connection Settings | postmaster
synchronize_seqscans | on | Version and Platform Compatibility / Previous PostgreSQL Versions | user
synchronous_commit | on | Write-Ahead Log / Settings | user
synchronous_standby_names | s1 | Replication / Primary Server | sighup
syslog_facility | local0 | Reporting and Logging / Where to Log | sighup
syslog_ident | postgres | Reporting and Logging / Where to Log | sighup
syslog_sequence_numbers | on | Reporting and Logging / Where to Log | sighup
syslog_split_messages | on | Reporting and Logging / Where to Log | sighup
tcp_keepalives_count | 0 | Connections and Authentication / TCP Settings | user
tcp_keepalives_idle | 0 | Connections and Authentication / TCP Settings | user
tcp_keepalives_interval | 0 | Connections and Authentication / TCP Settings | user
tcp_user_timeout | 0 | Connections and Authentication / TCP Settings | user
temp_buffers | 1024 | Resource Usage / Memory | user
temp_file_limit | -1 | Resource Usage / Disk | superuser
temp_tablespaces | | Client Connection Defaults / Statement Behavior | user
TimeZone | Asia/Tokyo | Client Connection Defaults / Locale and Formatting | user
timezone_abbreviations | Default | Client Connection Defaults / Locale and Formatting | user
trace_notify | off | Developer Options | user
trace_recovery_messages | log | Developer Options | sighup
trace_sort | off | Developer Options | user
track_activities | on | Statistics / Cumulative Query and Index Statistics | superuser
track_activity_query_size | 1024 | Statistics / Cumulative Query and Index Statistics | postmaster
track_commit_timestamp | off | Replication / Sending Servers | postmaster
track_counts | on | Statistics / Cumulative Query and Index Statistics | superuser
track_functions | none | Statistics / Cumulative Query and Index Statistics | superuser
track_io_timing | off | Statistics / Cumulative Query and Index Statistics | superuser
track_wal_io_timing | off | Statistics / Cumulative Query and Index Statistics | superuser
transaction_deferrable | off | Client Connection Defaults / Statement Behavior | user
transaction_isolation | read committed | Client Connection Defaults / Statement Behavior | user
transaction_read_only | off | Client Connection Defaults / Statement Behavior | user
transform_null_equals | off | Version and Platform Compatibility / Other Platforms and Clients | user
unix_socket_directories | /run/postgresql, /tmp | Connections and Authentication / Connection Settings | postmaster
unix_socket_group | | Connections and Authentication / Connection Settings | postmaster
unix_socket_permissions | 0777 | Connections and Authentication / Connection Settings | postmaster
update_process_title | on | Reporting and Logging / Process Title | superuser
vacuum_buffer_usage_limit | 256 | Resource Usage / Memory | user
vacuum_cost_delay | 0 | Resource Usage / Cost-Based Vacuum Delay | user
vacuum_cost_limit | 200 | Resource Usage / Cost-Based Vacuum Delay | user
vacuum_cost_page_dirty | 20 | Resource Usage / Cost-Based Vacuum Delay | user
vacuum_cost_page_hit | 1 | Resource Usage / Cost-Based Vacuum Delay | user
vacuum_cost_page_miss | 2 | Resource Usage / Cost-Based Vacuum Delay | user
vacuum_failsafe_age | 1600000000 | Client Connection Defaults / Statement Behavior | user
vacuum_freeze_min_age | 50000000 | Client Connection Defaults / Statement Behavior | user
vacuum_freeze_table_age | 150000000 | Client Connection Defaults / Statement Behavior | user
vacuum_multixact_failsafe_age | 1600000000 | Client Connection Defaults / Statement Behavior | user
vacuum_multixact_freeze_min_age | 5000000 | Client Connection Defaults / Statement Behavior | user
vacuum_multixact_freeze_table_age | 150000000 | Client Connection Defaults / Statement Behavior | user
wal_block_size | 8192 | Preset Options | internal
wal_buffers | 512 | Write-Ahead Log / Settings | postmaster
wal_compression | off | Write-Ahead Log / Settings | superuser
wal_consistency_checking | | Developer Options | superuser
wal_decode_buffer_size | 524288 | Write-Ahead Log / Recovery | postmaster
wal_init_zero | on | Write-Ahead Log / Settings | superuser
wal_keep_size | 0 | Replication / Sending Servers | sighup
wal_level | replica | Write-Ahead Log / Settings | postmaster
wal_log_hints | off | Write-Ahead Log / Settings | postmaster
wal_receiver_create_temp_slot | off | Replication / Standby Servers | sighup
wal_receiver_status_interval | 10 | Replication / Standby Servers | sighup
wal_receiver_timeout | 60000 | Replication / Standby Servers | sighup
wal_recycle | on | Write-Ahead Log / Settings | superuser
wal_retrieve_retry_interval | 5000 | Replication / Standby Servers | sighup
wal_segment_size | 16777216 | Preset Options | internal
wal_sender_timeout | 60000 | Replication / Sending Servers | user
wal_skip_threshold | 2048 | Write-Ahead Log / Settings | user
wal_sync_method | fdatasync | Write-Ahead Log / Settings | sighup
wal_writer_delay | 200 | Write-Ahead Log / Settings | sighup
wal_writer_flush_after | 128 | Write-Ahead Log / Settings | sighup
work_mem | 4096 | Resource Usage / Memory | user
xmlbinary | base64 | Client Connection Defaults / Statement Behavior | user
xmloption | content | Client Connection Defaults / Statement Behavior | user
zero_damaged_pages | off | Developer Options | superuser
(361 rows)
postgres=# select name, setting, category, context from pg_settings order by context, name;
name | setting | category | context
---------------------------------------------+----------------------------------------+-------------------------------------------------------------------+-------------------
ignore_system_indexes | off | Developer Options | backend
post_auth_delay | 0 | Developer Options | backend
block_size | 8192 | Preset Options | internal
data_checksums | off | Preset Options | internal
data_directory_mode | 0700 | Preset Options | internal
debug_assertions | off | Preset Options | internal
in_hot_standby | off | Preset Options | internal
integer_datetimes | on | Preset Options | internal
max_function_args | 100 | Preset Options | internal
max_identifier_length | 63 | Preset Options | internal
max_index_keys | 32 | Preset Options | internal
segment_size | 131072 | Preset Options | internal
server_encoding | UTF8 | Preset Options | internal
server_version | 16.10 | Preset Options | internal
server_version_num | 160010 | Preset Options | internal
shared_memory_size | 143 | Preset Options | internal
shared_memory_size_in_huge_pages | 72 | Preset Options | internal
ssl_library | OpenSSL | Preset Options | internal
wal_block_size | 8192 | Preset Options | internal
wal_segment_size | 16777216 | Preset Options | internal
archive_mode | on | Write-Ahead Log / Archiving | postmaster
autovacuum_freeze_max_age | 200000000 | Autovacuum | postmaster
autovacuum_max_workers | 3 | Autovacuum | postmaster
autovacuum_multixact_freeze_max_age | 400000000 | Autovacuum | postmaster
bonjour | off | Connections and Authentication / Connection Settings | postmaster
bonjour_name | | Connections and Authentication / Connection Settings | postmaster
cluster_name | | Reporting and Logging / Process Title | postmaster
config_file | /var/lib/pgsql/16/data/postgresql.conf | File Locations | postmaster
data_directory | /var/lib/pgsql/16/data | File Locations | postmaster
data_sync_retry | off | Error Handling | postmaster
debug_io_direct | | Developer Options | postmaster
dynamic_shared_memory_type | posix | Resource Usage / Memory | postmaster
event_source | PostgreSQL | Reporting and Logging / Where to Log | postmaster
external_pid_file | | File Locations | postmaster
hba_file | /var/lib/pgsql/16/data/pg_hba.conf | File Locations | postmaster
hot_standby | on | Replication / Standby Servers | postmaster
huge_page_size | 0 | Resource Usage / Memory | postmaster
huge_pages | try | Resource Usage / Memory | postmaster
ident_file | /var/lib/pgsql/16/data/pg_ident.conf | File Locations | postmaster
ignore_invalid_pages | off | Developer Options | postmaster
jit_provider | llvmjit | Client Connection Defaults / Shared Library Preloading | postmaster
listen_addresses | * | Connections and Authentication / Connection Settings | postmaster
logging_collector | on | Reporting and Logging / Where to Log | postmaster
max_connections | 100 | Connections and Authentication / Connection Settings | postmaster
max_files_per_process | 1000 | Resource Usage / Kernel Resources | postmaster
max_locks_per_transaction | 64 | Lock Management | postmaster
max_logical_replication_workers | 4 | Replication / Subscribers | postmaster
max_pred_locks_per_transaction | 64 | Lock Management | postmaster
max_prepared_transactions | 0 | Resource Usage / Memory | postmaster
max_replication_slots | 10 | Replication / Sending Servers | postmaster
max_wal_senders | 3 | Replication / Sending Servers | postmaster
max_worker_processes | 8 | Resource Usage / Asynchronous Behavior | postmaster
min_dynamic_shared_memory | 0 | Resource Usage / Memory | postmaster
old_snapshot_threshold | -1 | Resource Usage / Asynchronous Behavior | postmaster
port | 5432 | Connections and Authentication / Connection Settings | postmaster
recovery_target | | Write-Ahead Log / Recovery Target | postmaster
recovery_target_action | pause | Write-Ahead Log / Recovery Target | postmaster
recovery_target_inclusive | on | Write-Ahead Log / Recovery Target | postmaster
recovery_target_lsn | | Write-Ahead Log / Recovery Target | postmaster
recovery_target_name | | Write-Ahead Log / Recovery Target | postmaster
recovery_target_time | | Write-Ahead Log / Recovery Target | postmaster
recovery_target_timeline | latest | Write-Ahead Log / Recovery Target | postmaster
recovery_target_xid | | Write-Ahead Log / Recovery Target | postmaster
reserved_connections | 0 | Connections and Authentication / Connection Settings | postmaster
shared_buffers | 16384 | Resource Usage / Memory | postmaster
shared_memory_type | mmap | Resource Usage / Memory | postmaster
shared_preload_libraries | | Client Connection Defaults / Shared Library Preloading | postmaster
superuser_reserved_connections | 3 | Connections and Authentication / Connection Settings | postmaster
track_activity_query_size | 1024 | Statistics / Cumulative Query and Index Statistics | postmaster
track_commit_timestamp | off | Replication / Sending Servers | postmaster
unix_socket_directories | /run/postgresql, /tmp | Connections and Authentication / Connection Settings | postmaster
unix_socket_group | | Connections and Authentication / Connection Settings | postmaster
unix_socket_permissions | 0777 | Connections and Authentication / Connection Settings | postmaster
wal_buffers | 512 | Write-Ahead Log / Settings | postmaster
wal_decode_buffer_size | 524288 | Write-Ahead Log / Recovery | postmaster
wal_level | replica | Write-Ahead Log / Settings | postmaster
wal_log_hints | off | Write-Ahead Log / Settings | postmaster
archive_cleanup_command | | Write-Ahead Log / Archive Recovery | sighup
archive_command | cp %p /var/wal/%f | Write-Ahead Log / Archiving | sighup
archive_library | | Write-Ahead Log / Archiving | sighup
archive_timeout | 0 | Write-Ahead Log / Archiving | sighup
authentication_timeout | 60 | Connections and Authentication / Authentication | sighup
autovacuum | on | Autovacuum | sighup
autovacuum_analyze_scale_factor | 0.1 | Autovacuum | sighup
autovacuum_analyze_threshold | 50 | Autovacuum | sighup
autovacuum_naptime | 60 | Autovacuum | sighup
autovacuum_vacuum_cost_delay | 2 | Autovacuum | sighup
autovacuum_vacuum_cost_limit | -1 | Autovacuum | sighup
autovacuum_vacuum_insert_scale_factor | 0.2 | Autovacuum | sighup
autovacuum_vacuum_insert_threshold | 1000 | Autovacuum | sighup
autovacuum_vacuum_scale_factor | 0.2 | Autovacuum | sighup
autovacuum_vacuum_threshold | 50 | Autovacuum | sighup
autovacuum_work_mem | -1 | Resource Usage / Memory | sighup
bgwriter_delay | 200 | Resource Usage / Background Writer | sighup
bgwriter_flush_after | 64 | Resource Usage / Background Writer | sighup
bgwriter_lru_maxpages | 100 | Resource Usage / Background Writer | sighup
bgwriter_lru_multiplier | 2 | Resource Usage / Background Writer | sighup
checkpoint_completion_target | 0.9 | Write-Ahead Log / Checkpoints | sighup
checkpoint_flush_after | 32 | Write-Ahead Log / Checkpoints | sighup
checkpoint_timeout | 300 | Write-Ahead Log / Checkpoints | sighup
checkpoint_warning | 30 | Write-Ahead Log / Checkpoints | sighup
db_user_namespace | off | Connections and Authentication / Authentication | sighup
fsync | on | Write-Ahead Log / Settings | sighup
full_page_writes | on | Write-Ahead Log / Settings | sighup
gss_accept_delegation | off | Connections and Authentication / Authentication | sighup
hot_standby_feedback | off | Replication / Standby Servers | sighup
krb_caseins_users | off | Connections and Authentication / Authentication | sighup
krb_server_keyfile | FILE:/etc/sysconfig/pgsql/krb5.keytab | Connections and Authentication / Authentication | sighup
log_autovacuum_min_duration | 600000 | Reporting and Logging / What to Log | sighup
log_checkpoints | on | Reporting and Logging / What to Log | sighup
log_destination | stderr | Reporting and Logging / Where to Log | sighup
log_directory | log | Reporting and Logging / Where to Log | sighup
log_file_mode | 0600 | Reporting and Logging / Where to Log | sighup
log_filename | postgresql-%a.log | Reporting and Logging / Where to Log | sighup
log_hostname | off | Reporting and Logging / What to Log | sighup
log_line_prefix | %m [%p] | Reporting and Logging / What to Log | sighup
log_recovery_conflict_waits | off | Reporting and Logging / What to Log | sighup
log_rotation_age | 1440 | Reporting and Logging / Where to Log | sighup
log_rotation_size | 0 | Reporting and Logging / Where to Log | sighup
log_startup_progress_interval | 10000 | Reporting and Logging / When to Log | sighup
log_timezone | Asia/Tokyo | Reporting and Logging / What to Log | sighup
log_truncate_on_rotation | on | Reporting and Logging / Where to Log | sighup
max_parallel_apply_workers_per_subscription | 2 | Replication / Subscribers | sighup
max_pred_locks_per_page | 2 | Lock Management | sighup
max_pred_locks_per_relation | -2 | Lock Management | sighup
max_slot_wal_keep_size | -1 | Replication / Sending Servers | sighup
max_standby_archive_delay | 30000 | Replication / Standby Servers | sighup
max_standby_streaming_delay | 30000 | Replication / Standby Servers | sighup
max_sync_workers_per_subscription | 2 | Replication / Subscribers | sighup
max_wal_size | 1024 | Write-Ahead Log / Checkpoints | sighup
min_wal_size | 80 | Write-Ahead Log / Checkpoints | sighup
pre_auth_delay | 0 | Developer Options | sighup
primary_conninfo | | Replication / Standby Servers | sighup
primary_slot_name | | Replication / Standby Servers | sighup
recovery_end_command | | Write-Ahead Log / Archive Recovery | sighup
recovery_init_sync_method | fsync | Error Handling | sighup
recovery_min_apply_delay | 0 | Replication / Standby Servers | sighup
recovery_prefetch | try | Write-Ahead Log / Recovery | sighup
remove_temp_files_after_crash | on | Developer Options | sighup
restart_after_crash | on | Error Handling | sighup
restore_command | | Write-Ahead Log / Archive Recovery | sighup
send_abort_for_crash | off | Developer Options | sighup
send_abort_for_kill | off | Developer Options | sighup
ssl | off | Connections and Authentication / SSL | sighup
ssl_ca_file | | Connections and Authentication / SSL | sighup
ssl_cert_file | server.crt | Connections and Authentication / SSL | sighup
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL | Connections and Authentication / SSL | sighup
ssl_crl_dir | | Connections and Authentication / SSL | sighup
ssl_crl_file | | Connections and Authentication / SSL | sighup
ssl_dh_params_file | | Connections and Authentication / SSL | sighup
ssl_ecdh_curve | prime256v1 | Connections and Authentication / SSL | sighup
ssl_key_file | server.key | Connections and Authentication / SSL | sighup
ssl_max_protocol_version | | Connections and Authentication / SSL | sighup
ssl_min_protocol_version | TLSv1.2 | Connections and Authentication / SSL | sighup
ssl_passphrase_command | | Connections and Authentication / SSL | sighup
ssl_passphrase_command_supports_reload | off | Connections and Authentication / SSL | sighup
ssl_prefer_server_ciphers | on | Connections and Authentication / SSL | sighup
synchronous_standby_names | s1 | Replication / Primary Server | sighup
syslog_facility | local0 | Reporting and Logging / Where to Log | sighup
syslog_ident | postgres | Reporting and Logging / Where to Log | sighup
syslog_sequence_numbers | on | Reporting and Logging / Where to Log | sighup
syslog_split_messages | on | Reporting and Logging / Where to Log | sighup
trace_recovery_messages | log | Developer Options | sighup
wal_keep_size | 0 | Replication / Sending Servers | sighup
wal_receiver_create_temp_slot | off | Replication / Standby Servers | sighup
wal_receiver_status_interval | 10 | Replication / Standby Servers | sighup
wal_receiver_timeout | 60000 | Replication / Standby Servers | sighup
wal_retrieve_retry_interval | 5000 | Replication / Standby Servers | sighup
wal_sync_method | fdatasync | Write-Ahead Log / Settings | sighup
wal_writer_delay | 200 | Write-Ahead Log / Settings | sighup
wal_writer_flush_after | 128 | Write-Ahead Log / Settings | sighup
allow_in_place_tablespaces | off | Developer Options | superuser
allow_system_table_mods | off | Developer Options | superuser
backtrace_functions | | Developer Options | superuser
commit_delay | 0 | Write-Ahead Log / Settings | superuser
compute_query_id | auto | Statistics / Monitoring | superuser
deadlock_timeout | 1000 | Lock Management | superuser
debug_discard_caches | 0 | Developer Options | superuser
dynamic_library_path | $libdir | Client Connection Defaults / Other Defaults | superuser
ignore_checksum_failure | off | Developer Options | superuser
jit_dump_bitcode | off | Developer Options | superuser
lc_messages | C.UTF-8 | Client Connection Defaults / Locale and Formatting | superuser
lo_compat_privileges | off | Version and Platform Compatibility / Previous PostgreSQL Versions | superuser
log_duration | off | Reporting and Logging / What to Log | superuser
log_error_verbosity | default | Reporting and Logging / What to Log | superuser
log_executor_stats | off | Statistics / Monitoring | superuser
log_lock_waits | off | Reporting and Logging / What to Log | superuser
log_min_duration_sample | -1 | Reporting and Logging / When to Log | superuser
log_min_duration_statement | -1 | Reporting and Logging / When to Log | superuser
log_min_error_statement | error | Reporting and Logging / When to Log | superuser
log_min_messages | warning | Reporting and Logging / When to Log | superuser
log_parameter_max_length | -1 | Reporting and Logging / What to Log | superuser
log_parser_stats | off | Statistics / Monitoring | superuser
log_planner_stats | off | Statistics / Monitoring | superuser
log_replication_commands | off | Reporting and Logging / What to Log | superuser
log_statement | none | Reporting and Logging / What to Log | superuser
log_statement_sample_rate | 1 | Reporting and Logging / When to Log | superuser
log_statement_stats | off | Statistics / Monitoring | superuser
log_temp_files | -1 | Reporting and Logging / What to Log | superuser
log_transaction_sample_rate | 0 | Reporting and Logging / When to Log | superuser
max_stack_depth | 2048 | Resource Usage / Memory | superuser
session_preload_libraries | | Client Connection Defaults / Shared Library Preloading | superuser
session_replication_role | origin | Client Connection Defaults / Statement Behavior | superuser
temp_file_limit | -1 | Resource Usage / Disk | superuser
track_activities | on | Statistics / Cumulative Query and Index Statistics | superuser
track_counts | on | Statistics / Cumulative Query and Index Statistics | superuser
track_functions | none | Statistics / Cumulative Query and Index Statistics | superuser
track_io_timing | off | Statistics / Cumulative Query and Index Statistics | superuser
track_wal_io_timing | off | Statistics / Cumulative Query and Index Statistics | superuser
update_process_title | on | Reporting and Logging / Process Title | superuser
wal_compression | off | Write-Ahead Log / Settings | superuser
wal_consistency_checking | | Developer Options | superuser
wal_init_zero | on | Write-Ahead Log / Settings | superuser
wal_recycle | on | Write-Ahead Log / Settings | superuser
zero_damaged_pages | off | Developer Options | superuser
jit_debugging_support | off | Developer Options | superuser-backend
jit_profiling_support | off | Developer Options | superuser-backend
log_connections | off | Reporting and Logging / What to Log | superuser-backend
log_disconnections | off | Reporting and Logging / What to Log | superuser-backend
DateStyle | ISO, MDY | Client Connection Defaults / Locale and Formatting | user
IntervalStyle | postgres | Client Connection Defaults / Locale and Formatting | user
TimeZone | Asia/Tokyo | Client Connection Defaults / Locale and Formatting | user
application_name | psql | Reporting and Logging / What to Log | user
array_nulls | on | Version and Platform Compatibility / Previous PostgreSQL Versions | user
backend_flush_after | 0 | Resource Usage / Asynchronous Behavior | user
backslash_quote | safe_encoding | Version and Platform Compatibility / Previous PostgreSQL Versions | user
bytea_output | hex | Client Connection Defaults / Statement Behavior | user
check_function_bodies | on | Client Connection Defaults / Statement Behavior | user
client_connection_check_interval | 0 | Connections and Authentication / TCP Settings | user
client_encoding | UTF8 | Client Connection Defaults / Locale and Formatting | user
client_min_messages | notice | Client Connection Defaults / Statement Behavior | user
commit_siblings | 5 | Write-Ahead Log / Settings | user
constraint_exclusion | partition | Query Tuning / Other Planner Options | user
cpu_index_tuple_cost | 0.005 | Query Tuning / Planner Cost Constants | user
cpu_operator_cost | 0.0025 | Query Tuning / Planner Cost Constants | user
cpu_tuple_cost | 0.01 | Query Tuning / Planner Cost Constants | user
createrole_self_grant | | Client Connection Defaults / Statement Behavior | user
cursor_tuple_fraction | 0.1 | Query Tuning / Other Planner Options | user
debug_logical_replication_streaming | buffered | Developer Options | user
debug_parallel_query | off | Developer Options | user
debug_pretty_print | on | Reporting and Logging / What to Log | user
debug_print_parse | off | Reporting and Logging / What to Log | user
debug_print_plan | off | Reporting and Logging / What to Log | user
debug_print_rewritten | off | Reporting and Logging / What to Log | user
default_statistics_target | 100 | Query Tuning / Other Planner Options | user
default_table_access_method | heap | Client Connection Defaults / Statement Behavior | user
default_tablespace | | Client Connection Defaults / Statement Behavior | user
default_text_search_config | pg_catalog.english | Client Connection Defaults / Locale and Formatting | user
default_toast_compression | pglz | Client Connection Defaults / Statement Behavior | user
default_transaction_deferrable | off | Client Connection Defaults / Statement Behavior | user
default_transaction_isolation | read committed | Client Connection Defaults / Statement Behavior | user
default_transaction_read_only | off | Client Connection Defaults / Statement Behavior | user
effective_cache_size | 524288 | Query Tuning / Planner Cost Constants | user
effective_io_concurrency | 1 | Resource Usage / Asynchronous Behavior | user
enable_async_append | on | Query Tuning / Planner Method Configuration | user
enable_bitmapscan | on | Query Tuning / Planner Method Configuration | user
enable_gathermerge | on | Query Tuning / Planner Method Configuration | user
enable_hashagg | on | Query Tuning / Planner Method Configuration | user
enable_hashjoin | on | Query Tuning / Planner Method Configuration | user
enable_incremental_sort | on | Query Tuning / Planner Method Configuration | user
enable_indexonlyscan | on | Query Tuning / Planner Method Configuration | user
enable_indexscan | on | Query Tuning / Planner Method Configuration | user
enable_material | on | Query Tuning / Planner Method Configuration | user
enable_memoize | on | Query Tuning / Planner Method Configuration | user
enable_mergejoin | on | Query Tuning / Planner Method Configuration | user
enable_nestloop | on | Query Tuning / Planner Method Configuration | user
enable_parallel_append | on | Query Tuning / Planner Method Configuration | user
enable_parallel_hash | on | Query Tuning / Planner Method Configuration | user
enable_partition_pruning | on | Query Tuning / Planner Method Configuration | user
enable_partitionwise_aggregate | off | Query Tuning / Planner Method Configuration | user
enable_partitionwise_join | off | Query Tuning / Planner Method Configuration | user
enable_presorted_aggregate | on | Query Tuning / Planner Method Configuration | user
enable_seqscan | on | Query Tuning / Planner Method Configuration | user
enable_sort | on | Query Tuning / Planner Method Configuration | user
enable_tidscan | on | Query Tuning / Planner Method Configuration | user
escape_string_warning | on | Version and Platform Compatibility / Previous PostgreSQL Versions | user
exit_on_error | off | Error Handling | user
extra_float_digits | 1 | Client Connection Defaults / Locale and Formatting | user
from_collapse_limit | 8 | Query Tuning / Other Planner Options | user
geqo | on | Query Tuning / Genetic Query Optimizer | user
geqo_effort | 5 | Query Tuning / Genetic Query Optimizer | user
geqo_generations | 0 | Query Tuning / Genetic Query Optimizer | user
geqo_pool_size | 0 | Query Tuning / Genetic Query Optimizer | user
geqo_seed | 0 | Query Tuning / Genetic Query Optimizer | user
geqo_selection_bias | 2 | Query Tuning / Genetic Query Optimizer | user
geqo_threshold | 12 | Query Tuning / Genetic Query Optimizer | user
gin_fuzzy_search_limit | 0 | Client Connection Defaults / Other Defaults | user
gin_pending_list_limit | 4096 | Client Connection Defaults / Statement Behavior | user
hash_mem_multiplier | 2 | Resource Usage / Memory | user
icu_validation_level | warning | Client Connection Defaults / Locale and Formatting | user
idle_in_transaction_session_timeout | 0 | Client Connection Defaults / Statement Behavior | user
idle_session_timeout | 0 | Client Connection Defaults / Statement Behavior | user
jit | on | Query Tuning / Other Planner Options | user
jit_above_cost | 100000 | Query Tuning / Planner Cost Constants | user
jit_expressions | on | Developer Options | user
jit_inline_above_cost | 500000 | Query Tuning / Planner Cost Constants | user
jit_optimize_above_cost | 500000 | Query Tuning / Planner Cost Constants | user
jit_tuple_deforming | on | Developer Options | user
join_collapse_limit | 8 | Query Tuning / Other Planner Options | user
lc_monetary | C.UTF-8 | Client Connection Defaults / Locale and Formatting | user
lc_numeric | C.UTF-8 | Client Connection Defaults / Locale and Formatting | user
lc_time | C.UTF-8 | Client Connection Defaults / Locale and Formatting | user
local_preload_libraries | | Client Connection Defaults / Shared Library Preloading | user
lock_timeout | 0 | Client Connection Defaults / Statement Behavior | user
log_parameter_max_length_on_error | 0 | Reporting and Logging / What to Log | user
logical_decoding_work_mem | 65536 | Resource Usage / Memory | user
maintenance_io_concurrency | 10 | Resource Usage / Asynchronous Behavior | user
maintenance_work_mem | 65536 | Resource Usage / Memory | user
max_parallel_maintenance_workers | 2 | Resource Usage / Asynchronous Behavior | user
max_parallel_workers | 8 | Resource Usage / Asynchronous Behavior | user
max_parallel_workers_per_gather | 2 | Resource Usage / Asynchronous Behavior | user
min_parallel_index_scan_size | 64 | Query Tuning / Planner Cost Constants | user
min_parallel_table_scan_size | 1024 | Query Tuning / Planner Cost Constants | user
parallel_leader_participation | on | Resource Usage / Asynchronous Behavior | user
parallel_setup_cost | 1000 | Query Tuning / Planner Cost Constants | user
parallel_tuple_cost | 0.1 | Query Tuning / Planner Cost Constants | user
password_encryption | scram-sha-256 | Connections and Authentication / Authentication | user
plan_cache_mode | auto | Query Tuning / Other Planner Options | user
quote_all_identifiers | off | Version and Platform Compatibility / Previous PostgreSQL Versions | user
random_page_cost | 4 | Query Tuning / Planner Cost Constants | user
recursive_worktable_factor | 10 | Query Tuning / Other Planner Options | user
restrict_nonsystem_relation_kind | | Client Connection Defaults / Statement Behavior | user
row_security | on | Client Connection Defaults / Statement Behavior | user
scram_iterations | 4096 | Connections and Authentication / Authentication | user
search_path | "$user", public | Client Connection Defaults / Statement Behavior | user
seq_page_cost | 1 | Query Tuning / Planner Cost Constants | user
standard_conforming_strings | on | Version and Platform Compatibility / Previous PostgreSQL Versions | user
statement_timeout | 0 | Client Connection Defaults / Statement Behavior | user
stats_fetch_consistency | cache | Statistics / Cumulative Query and Index Statistics | user
synchronize_seqscans | on | Version and Platform Compatibility / Previous PostgreSQL Versions | user
synchronous_commit | on | Write-Ahead Log / Settings | user
tcp_keepalives_count | 0 | Connections and Authentication / TCP Settings | user
tcp_keepalives_idle | 0 | Connections and Authentication / TCP Settings | user
tcp_keepalives_interval | 0 | Connections and Authentication / TCP Settings | user
tcp_user_timeout | 0 | Connections and Authentication / TCP Settings | user
temp_buffers | 1024 | Resource Usage / Memory | user
temp_tablespaces | | Client Connection Defaults / Statement Behavior | user
timezone_abbreviations | Default | Client Connection Defaults / Locale and Formatting | user
trace_notify | off | Developer Options | user
trace_sort | off | Developer Options | user
transaction_deferrable | off | Client Connection Defaults / Statement Behavior | user
transaction_isolation | read committed | Client Connection Defaults / Statement Behavior | user
transaction_read_only | off | Client Connection Defaults / Statement Behavior | user
transform_null_equals | off | Version and Platform Compatibility / Other Platforms and Clients | user
vacuum_buffer_usage_limit | 256 | Resource Usage / Memory | user
vacuum_cost_delay | 0 | Resource Usage / Cost-Based Vacuum Delay | user
vacuum_cost_limit | 200 | Resource Usage / Cost-Based Vacuum Delay | user
vacuum_cost_page_dirty | 20 | Resource Usage / Cost-Based Vacuum Delay | user
vacuum_cost_page_hit | 1 | Resource Usage / Cost-Based Vacuum Delay | user
vacuum_cost_page_miss | 2 | Resource Usage / Cost-Based Vacuum Delay | user
vacuum_failsafe_age | 1600000000 | Client Connection Defaults / Statement Behavior | user
vacuum_freeze_min_age | 50000000 | Client Connection Defaults / Statement Behavior | user
vacuum_freeze_table_age | 150000000 | Client Connection Defaults / Statement Behavior | user
vacuum_multixact_failsafe_age | 1600000000 | Client Connection Defaults / Statement Behavior | user
vacuum_multixact_freeze_min_age | 5000000 | Client Connection Defaults / Statement Behavior | user
vacuum_multixact_freeze_table_age | 150000000 | Client Connection Defaults / Statement Behavior | user
wal_sender_timeout | 60000 | Replication / Sending Servers | user
wal_skip_threshold | 2048 | Write-Ahead Log / Settings | user
work_mem | 4096 | Resource Usage / Memory | user
xmlbinary | base64 | Client Connection Defaults / Statement Behavior | user
xmloption | content | Client Connection Defaults / Statement Behavior | user
(361 rows)
postgres=# select context, count(1) from pg_settings group by context order by context;
context | count
-------------------+-------
backend | 2
internal | 18
postmaster | 57
sighup | 94
superuser | 44
superuser-backend | 4
user | 142
(7 rows)
postgres=#
DB確認
pg_database
先ずはDBの確認。
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 16.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
(1 row)
postgres=# \timing
Timing is on.
postgres=#postgres=# \pset null '[null]'
Null display is "[null]".
postgres=# \x auto
Expanded display is used automatically.
postgres=# select * from pg_database;
-[ RECORD 1 ]--+------------------------------------
oid | 5
datname | postgres
datdba | 10
encoding | 6
datlocprovider | c
datistemplate | f
datallowconn | t
datconnlimit | -1
datfrozenxid | 722
datminmxid | 1
dattablespace | 1663
datcollate | C.UTF-8
datctype | C.UTF-8
daticulocale | [null]
daticurules | [null]
datcollversion | [null]
datacl | [null]
-[ RECORD 2 ]--+------------------------------------
oid | 1
datname | template1
datdba | 10
encoding | 6
datlocprovider | c
datistemplate | t
datallowconn | t
datconnlimit | -1
datfrozenxid | 722
datminmxid | 1
dattablespace | 1663
datcollate | C.UTF-8
datctype | C.UTF-8
daticulocale | [null]
daticurules | [null]
datcollversion | [null]
datacl | {=c/postgres,postgres=CTc/postgres}
-[ RECORD 3 ]--+------------------------------------
oid | 4
datname | template0
datdba | 10
encoding | 6
datlocprovider | c
datistemplate | t
datallowconn | f
datconnlimit | -1
datfrozenxid | 722
datminmxid | 1
dattablespace | 1663
datcollate | C.UTF-8
datctype | C.UTF-8
daticulocale | [null]
daticurules | [null]
datcollversion | [null]
datacl | {=c/postgres,postgres=CTc/postgres}
-[ RECORD 4 ]--+------------------------------------
oid | 16385
datname | test
datdba | 16384
encoding | 6
datlocprovider | c
datistemplate | f
datallowconn | t
datconnlimit | -1
datfrozenxid | 722
datminmxid | 1
dattablespace | 1663
datcollate | C.UTF-8
datctype | C.UTF-8
daticulocale | [null]
daticurules | [null]
datcollversion | [null]
datacl | [null]
Time: 0.675 ms
postgres=#
サーバプロセスあたり１行の形式で、現在の状況を表示
pg_stat_activity
postgres=# select * from pg_stat_activity;
-[ RECORD 1 ]----+---------------------------------------
datid | [null]
datname | [null]
pid | 845
leader_pid | [null]
usesysid | [null]
usename | [null]
application_name |
client_addr | [null]
client_hostname | [null]
client_port | [null]
backend_start | 2025-09-13 17:45:45.26481+09
xact_start | [null]
query_start | [null]
state_change | [null]
wait_event_type | Activity
wait_event | AutoVacuumMain
state | [null]
backend_xid | [null]
backend_xmin | [null]
query_id | [null]
query |
backend_type | autovacuum launcher
-[ RECORD 2 ]----+---------------------------------------
datid | [null]
datname | [null]
pid | 847
leader_pid | [null]
usesysid | 10
usename | postgres
application_name |
client_addr | [null]
client_hostname | [null]
client_port | [null]
backend_start | 2025-09-13 17:45:45.311004+09
xact_start | [null]
query_start | [null]
state_change | [null]
wait_event_type | Activity
wait_event | LogicalLauncherMain
state | [null]
backend_xid | [null]
backend_xmin | [null]
query_id | [null]
query |
backend_type | logical replication launcher
-[ RECORD 3 ]----+---------------------------------------
datid | [null]
datname | [null]
pid | 3581
leader_pid | [null]
usesysid | 10
usename | postgres
application_name | s1
client_addr | 192.168.55.102
client_hostname | [null]
client_port | 60590
backend_start | 2025-09-13 17:48:29.698023+09
xact_start | [null]
query_start | 2025-09-13 17:48:30.947999+09
state_change | 2025-09-13 17:48:30.948055+09
wait_event_type | Activity
wait_event | WalSenderMain
state | active
backend_xid | [null]
backend_xmin | [null]
query_id | [null]
query | START_REPLICATION 0/4000000 TIMELINE 1
backend_type | walsender
-[ RECORD 4 ]----+---------------------------------------
datid | 5
datname | postgres
pid | 3689
leader_pid | [null]
usesysid | 10
usename | postgres
application_name | psql
client_addr | [null]
client_hostname | [null]
client_port | -1
backend_start | 2025-09-13 17:53:28.525148+09
xact_start | 2025-09-13 23:03:32.196027+09
query_start | 2025-09-13 23:03:32.196027+09
state_change | 2025-09-13 23:03:32.196031+09
wait_event_type | [null]
wait_event | [null]
state | active
backend_xid | [null]
backend_xmin | 748
query_id | [null]
query | select * from pg_stat_activity;
backend_type | client backend
-[ RECORD 5 ]----+---------------------------------------
datid | [null]
datname | [null]
pid | 835
leader_pid | [null]
usesysid | [null]
usename | [null]
application_name |
client_addr | [null]
client_hostname | [null]
client_port | [null]
backend_start | 2025-09-13 17:45:45.020213+09
xact_start | [null]
query_start | [null]
state_change | [null]
wait_event_type | Activity
wait_event | BgWriterHibernate
state | [null]
backend_xid | [null]
backend_xmin | [null]
query_id | [null]
query |
backend_type | background writer
-[ RECORD 6 ]----+---------------------------------------
datid | [null]
datname | [null]
pid | 846
leader_pid | [null]
usesysid | [null]
usename | [null]
application_name |
client_addr | [null]
client_hostname | [null]
client_port | [null]
backend_start | 2025-09-13 17:45:45.281493+09
xact_start | [null]
query_start | [null]
state_change | [null]
wait_event_type | Activity
wait_event | ArchiverMain
state | [null]
backend_xid | [null]
backend_xmin | [null]
query_id | [null]
query |
backend_type | archiver
-[ RECORD 7 ]----+---------------------------------------
datid | [null]
datname | [null]
pid | 834
leader_pid | [null]
usesysid | [null]
usename | [null]
application_name |
client_addr | [null]
client_hostname | [null]
client_port | [null]
backend_start | 2025-09-13 17:45:44.988886+09
xact_start | [null]
query_start | [null]
state_change | [null]
wait_event_type | Activity
wait_event | CheckpointerMain
state | [null]
backend_xid | [null]
backend_xmin | [null]
query_id | [null]
query |
backend_type | checkpointer
-[ RECORD 8 ]----+---------------------------------------
datid | [null]
datname | [null]
pid | 844
leader_pid | [null]
usesysid | [null]
usename | [null]
application_name |
client_addr | [null]
client_hostname | [null]
client_port | [null]
backend_start | 2025-09-13 17:45:45.240914+09
xact_start | [null]
query_start | [null]
state_change | [null]
wait_event_type | Activity
wait_event | WalWriterMain
state | [null]
backend_xid | [null]
backend_xmin | [null]
query_id | [null]
query |
backend_type | walwriter
Time: 3.213 ms
postgres=#
DB当たり１行の形で、DB全体の情報を表示
pg_stat_database
postgres=# select * from pg_stat_database;
-[ RECORD 1 ]------------+-------------
datid | 0
datname | [null]
numbackends | 0
xact_commit | 0
xact_rollback | 0
blks_read | 115
blks_hit | 22073
tup_returned | 6423
tup_fetched | 4835
tup_inserted | 29
tup_updated | 6
tup_deleted | 0
conflicts | 0
temp_files | 1
temp_bytes | 181539
deadlocks | 0
checksum_failures | [null]
checksum_last_failure | [null]
blk_read_time | 0
blk_write_time | 0
session_time | 0
active_time | 0
idle_in_transaction_time | 0
sessions | 0
sessions_abandoned | 0
sessions_fatal | 0
sessions_killed | 0
stats_reset | [null]
-[ RECORD 2 ]------------+-------------
datid | 5
datname | postgres
numbackends | 1
xact_commit | 1582
xact_rollback | 2
blks_read | 907
blks_hit | 80765
tup_returned | 453872
tup_fetched | 29104
tup_inserted | 0
tup_updated | 0
tup_deleted | 0
conflicts | 0
temp_files | 0
temp_bytes | 0
deadlocks | 0
checksum_failures | [null]
checksum_last_failure | [null]
blk_read_time | 0
blk_write_time | 0
session_time | 18603957.828
active_time | 3085.184
idle_in_transaction_time | 0
sessions | 11
sessions_abandoned | 0
sessions_fatal | 0
sessions_killed | 0
stats_reset | [null]
-[ RECORD 3 ]------------+-------------
datid | 1
datname | template1
numbackends | 0
xact_commit | 1763
xact_rollback | 0
blks_read | 963
blks_hit | 123466
tup_returned | 451754
tup_fetched | 34456
tup_inserted | 17570
tup_updated | 754
tup_deleted | 34
conflicts | 0
temp_files | 0
temp_bytes | 0
deadlocks | 0
checksum_failures | [null]
checksum_last_failure | [null]
blk_read_time | 0
blk_write_time | 0
session_time | 0
active_time | 0
idle_in_transaction_time | 0
sessions | 0
sessions_abandoned | 0
sessions_fatal | 0
sessions_killed | 0
stats_reset | [null]
-[ RECORD 4 ]------------+-------------
datid | 4
datname | template0
numbackends | 0
xact_commit | 0
xact_rollback | 0
blks_read | 0
blks_hit | 0
tup_returned | 0
tup_fetched | 0
tup_inserted | 0
tup_updated | 0
tup_deleted | 0
conflicts | 0
temp_files | 0
temp_bytes | 0
deadlocks | 0
checksum_failures | [null]
checksum_last_failure | [null]
blk_read_time | 0
blk_write_time | 0
session_time | 0
active_time | 0
idle_in_transaction_time | 0
sessions | 0
sessions_abandoned | 0
sessions_fatal | 0
sessions_killed | 0
stats_reset | [null]
-[ RECORD 5 ]------------+-------------
datid | 16385
datname | test
numbackends | 0
xact_commit | 875
xact_rollback | 0
blks_read | 373
blks_hit | 40473
tup_returned | 385640
tup_fetched | 10806
tup_inserted | 33
tup_updated | 1
tup_deleted | 0
conflicts | 0
temp_files | 0
temp_bytes | 0
deadlocks | 0
checksum_failures | [null]
checksum_last_failure | [null]
blk_read_time | 0
blk_write_time | 0
session_time | 116.17
active_time | 56.191
idle_in_transaction_time | 0
sessions | 6
sessions_abandoned | 0
sessions_fatal | 0
sessions_killed | 0
stats_reset | [null]
Time: 1.612 ms
postgres=#
16からのビュー。DBクラスタ全体のIO情報を表示
pg_stat_io
postgres=# select * from pg_stat_io;
-[ RECORD 1 ]--+------------------------------
backend_type | autovacuum launcher
object | relation
context | bulkread
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | [null]
extend_time | [null]
op_bytes | 8192
hits | 0
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 2 ]--+------------------------------
backend_type | autovacuum launcher
object | relation
context | normal
reads | 4
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | [null]
extend_time | [null]
op_bytes | 8192
hits | 324
evictions | 0
reuses | [null]
fsyncs | 0
fsync_time | 0
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 3 ]--+------------------------------
backend_type | autovacuum worker
object | relation
context | bulkread
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | [null]
extend_time | [null]
op_bytes | 8192
hits | 0
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 4 ]--+------------------------------
backend_type | autovacuum worker
object | relation
context | normal
reads | 752
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 8
extend_time | 0
op_bytes | 8192
hits | 133755
evictions | 0
reuses | [null]
fsyncs | 0
fsync_time | 0
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 5 ]--+------------------------------
backend_type | autovacuum worker
object | relation
context | vacuum
reads | 146
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 0
extend_time | 0
op_bytes | 8192
hits | 111
evictions | 0
reuses | 109
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 6 ]--+------------------------------
backend_type | client backend
object | relation
context | bulkread
reads | 916
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | [null]
extend_time | [null]
op_bytes | 8192
hits | 14
evictions | 0
reuses | 136
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 7 ]--+------------------------------
backend_type | client backend
object | relation
context | bulkwrite
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 0
extend_time | 0
op_bytes | 8192
hits | 0
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 8 ]--+------------------------------
backend_type | client backend
object | relation
context | normal
reads | 901
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 4
extend_time | 0
op_bytes | 8192
hits | 50655
evictions | 0
reuses | [null]
fsyncs | 0
fsync_time | 0
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 9 ]--+------------------------------
backend_type | client backend
object | relation
context | vacuum
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 0
extend_time | 0
op_bytes | 8192
hits | 0
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 10 ]-+------------------------------
backend_type | client backend
object | temp relation
context | normal
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | [null]
writeback_time | [null]
extends | 0
extend_time | 0
op_bytes | 8192
hits | 0
evictions | 0
reuses | [null]
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 11 ]-+------------------------------
backend_type | background worker
object | relation
context | bulkread
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | [null]
extend_time | [null]
op_bytes | 8192
hits | 0
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 12 ]-+------------------------------
backend_type | background worker
object | relation
context | bulkwrite
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 0
extend_time | 0
op_bytes | 8192
hits | 0
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 13 ]-+------------------------------
backend_type | background worker
object | relation
context | normal
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 0
extend_time | 0
op_bytes | 8192
hits | 0
evictions | 0
reuses | [null]
fsyncs | 0
fsync_time | 0
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 14 ]-+------------------------------
backend_type | background worker
object | relation
context | vacuum
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 0
extend_time | 0
op_bytes | 8192
hits | 0
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 15 ]-+------------------------------
backend_type | background worker
object | temp relation
context | normal
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | [null]
writeback_time | [null]
extends | 0
extend_time | 0
op_bytes | 8192
hits | 0
evictions | 0
reuses | [null]
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 16 ]-+------------------------------
backend_type | background writer
object | relation
context | normal
reads | [null]
read_time | [null]
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | [null]
extend_time | [null]
op_bytes | 8192
hits | [null]
evictions | [null]
reuses | [null]
fsyncs | 0
fsync_time | 0
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 17 ]-+------------------------------
backend_type | checkpointer
object | relation
context | normal
reads | [null]
read_time | [null]
writes | 975
write_time | 0
writebacks | 975
writeback_time | 0
extends | [null]
extend_time | [null]
op_bytes | 8192
hits | [null]
evictions | [null]
reuses | [null]
fsyncs | 323
fsync_time | 0
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 18 ]-+------------------------------
backend_type | standalone backend
object | relation
context | bulkread
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | [null]
extend_time | [null]
op_bytes | 8192
hits | 0
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 19 ]-+------------------------------
backend_type | standalone backend
object | relation
context | bulkwrite
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 8
extend_time | 0
op_bytes | 8192
hits | 7
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 20 ]-+------------------------------
backend_type | standalone backend
object | relation
context | normal
reads | 535
read_time | 0
writes | 1009
write_time | 0
writebacks | 1009
writeback_time | 0
extends | 663
extend_time | 0
op_bytes | 8192
hits | 85430
evictions | 0
reuses | [null]
fsyncs | 0
fsync_time | 0
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 21 ]-+------------------------------
backend_type | standalone backend
object | relation
context | vacuum
reads | 10
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 0
extend_time | 0
op_bytes | 8192
hits | 907
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 22 ]-+------------------------------
backend_type | startup
object | relation
context | bulkread
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | [null]
extend_time | [null]
op_bytes | 8192
hits | 0
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 23 ]-+------------------------------
backend_type | startup
object | relation
context | bulkwrite
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 0
extend_time | 0
op_bytes | 8192
hits | 0
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 24 ]-+------------------------------
backend_type | startup
object | relation
context | normal
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 0
extend_time | 0
op_bytes | 8192
hits | 0
evictions | 0
reuses | [null]
fsyncs | 0
fsync_time | 0
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 25 ]-+------------------------------
backend_type | startup
object | relation
context | vacuum
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 0
extend_time | 0
op_bytes | 8192
hits | 0
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 26 ]-+------------------------------
backend_type | walsender
object | relation
context | bulkread
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | [null]
extend_time | [null]
op_bytes | 8192
hits | 0
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 27 ]-+------------------------------
backend_type | walsender
object | relation
context | bulkwrite
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 0
extend_time | 0
op_bytes | 8192
hits | 0
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 28 ]-+------------------------------
backend_type | walsender
object | relation
context | normal
reads | 10
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 0
extend_time | 0
op_bytes | 8192
hits | 20
evictions | 0
reuses | [null]
fsyncs | 0
fsync_time | 0
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 29 ]-+------------------------------
backend_type | walsender
object | relation
context | vacuum
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends | 0
extend_time | 0
op_bytes | 8192
hits | 0
evictions | 0
reuses | 0
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
-[ RECORD 30 ]-+------------------------------
backend_type | walsender
object | temp relation
context | normal
reads | 0
read_time | 0
writes | 0
write_time | 0
writebacks | [null]
writeback_time | [null]
extends | 0
extend_time | 0
op_bytes | 8192
hits | 0
evictions | 0
reuses | [null]
fsyncs | [null]
fsync_time | [null]
stats_reset | 2025-09-09 13:06:39.200036+09
Time: 2.273 ms
postgres=#
現在のDBのシステムテーブルごとに１行の形式で、特定のテーブルへのアクセスに関する統計情報を表示
pg_stat_sys_tables
postgres=# select count(*) from pg_stat_sys_tables;
count
-------
108
(1 row)
Time: 2.493 ms
postgres=# select * from pg_stat_sys_tables limit 3;
-[ RECORD 1 ]-------+------------------------------
relid | 826
schemaname | pg_catalog
relname | pg_default_acl
seq_scan | 0
last_seq_scan | [null]
seq_tup_read | 0
idx_scan | 0
last_idx_scan | [null]
idx_tup_fetch | 0
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_tup_newpage_upd | 0
n_live_tup | 0
n_dead_tup | 0
n_mod_since_analyze | 0
n_ins_since_vacuum | 0
last_vacuum | [null]
last_autovacuum | [null]
last_analyze | [null]
last_autoanalyze | [null]
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
-[ RECORD 2 ]-------+------------------------------
relid | 1213
schemaname | pg_catalog
relname | pg_tablespace
seq_scan | 8
last_seq_scan | 2025-09-13 18:19:30.268959+09
seq_tup_read | 15
idx_scan | 13
last_idx_scan | 2025-09-13 18:11:32.672021+09
idx_tup_fetch | 13
n_tup_ins | 2
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_tup_newpage_upd | 0
n_live_tup | 2
n_dead_tup | 0
n_mod_since_analyze | 0
n_ins_since_vacuum | 0
last_vacuum | 2025-09-09 13:06:39.998381+09
last_autovacuum | [null]
last_analyze | 2025-09-09 13:06:39.972626+09
last_autoanalyze | [null]
vacuum_count | 1
autovacuum_count | 0
analyze_count | 1
autoanalyze_count | 0
-[ RECORD 3 ]-------+------------------------------
relid | 1214
schemaname | pg_catalog
relname | pg_shdepend
seq_scan | 2
last_seq_scan | 2025-09-09 13:06:39.379614+09
seq_tup_read | 0
idx_scan | 89
last_idx_scan | 2025-09-09 13:06:48.421473+09
idx_tup_fetch | 0
n_tup_ins | 1
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_tup_newpage_upd | 0
n_live_tup | 1
n_dead_tup | 0
n_mod_since_analyze | 1
n_ins_since_vacuum | 1
last_vacuum | 2025-09-09 13:06:40.0017+09
last_autovacuum | [null]
last_analyze | 2025-09-09 13:06:39.972928+09
last_autoanalyze | [null]
vacuum_count | 1
autovacuum_count | 0
analyze_count | 1
autoanalyze_count | 0
Time: 3.285 ms
postgres=#
現在のDBのユーザテーブルごとに１行の形式で、特定のテーブルへのアクセスに関する統計情報を表示
pg_stat_user_tables
postgres=# \l
List of databases
-[ RECORD 1 ]-----+----------------------
Name | postgres
Owner | postgres
Encoding | UTF8
Locale Provider | libc
Collate | C.UTF-8
Ctype | C.UTF-8
ICU Locale |
ICU Rules |
Access privileges |
-[ RECORD 2 ]-----+----------------------
Name | template0
Owner | postgres
Encoding | UTF8
Locale Provider | libc
Collate | C.UTF-8
Ctype | C.UTF-8
ICU Locale |
ICU Rules |
Access privileges | =c/postgres +
| postgres=CTc/postgres
-[ RECORD 3 ]-----+----------------------
Name | template1
Owner | postgres
Encoding | UTF8
Locale Provider | libc
Collate | C.UTF-8
Ctype | C.UTF-8
ICU Locale |
ICU Rules |
Access privileges | =c/postgres +
| postgres=CTc/postgres
-[ RECORD 4 ]-----+----------------------
Name | test
Owner | test
Encoding | UTF8
Locale Provider | libc
Collate | C.UTF-8
Ctype | C.UTF-8
ICU Locale |
ICU Rules |
Access privileges |
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from pg_stat_user_tables;
-[ RECORD 1 ]-------+------------------------------
relid | 16386
schemaname | public
relname | test
seq_scan | 1
last_seq_scan | 2025-09-09 14:41:54.027225+09
seq_tup_read | 1
idx_scan | [null]
last_idx_scan | [null]
idx_tup_fetch | [null]
n_tup_ins | 2
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_tup_newpage_upd | 0
n_live_tup | 2
n_dead_tup | 0
n_mod_since_analyze | 2
n_ins_since_vacuum | 2
last_vacuum | [null]
last_autovacuum | [null]
last_analyze | [null]
last_autoanalyze | [null]
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
Time: 215.997 ms
test=#
WAL送信プロセス毎に１行の形式で、レプリケーションに関する統計情報を表示
pg_stat_replication
プライマリ側で確認。
test=# select * from pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
Time: 22.545 ms
test=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 3581
usesysid | 10
usename | postgres
application_name | s1
client_addr | 192.168.55.102
client_hostname | [null]
client_port | 60590
backend_start | 2025-09-13 17:48:29.698023+09
backend_xmin | [null]
state | streaming
sent_lsn | 0/50001C0
write_lsn | 0/50001C0
flush_lsn | 0/50001C0
replay_lsn | 0/50001C0
write_lag | [null]
flush_lag | [null]
replay_lag | [null]
sync_priority | 1
sync_state | sync
reply_time | 2025-09-13 23:27:44.540668+09
Time: 25.074 ms
test=#
WALの活動状況に関する統計情報を1行で表示
pg_stat_wal
test=# select * from pg_stat_wal;
-[ RECORD 1 ]----+------------------------------
wal_records | 25317
wal_fpi | 1444
wal_bytes | 9696104
wal_buffers_full | 4972
wal_write | 5761
wal_sync | 50
wal_write_time | 0
wal_sync_time | 0
stats_reset | 2025-09-09 13:06:39.200036+09
Time: 12.289 ms
test=#
WAL受信サーバに関する統計情報を１行で表示
pg_stat_wal_receiver
セカンダリ側で確認。
test=# select * from pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
test=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 833
status | streaming
receive_start_lsn | 0/4000000
receive_start_tli | 1
written_lsn | 0/50001C0
flushed_lsn | 0/50001C0
received_tli | 1
last_msg_send_time | 2025-09-13 23:24:11.499015+09
last_msg_receipt_time | 2025-09-13 23:24:14.32124+09
latest_end_lsn | 0/50001C0
latest_end_time | 2025-09-13 17:50:54.445559+09
slot_name |
sender_host | 192.168.55.101
sender_port | 5432
conninfo | user=postgres passfile=/var/lib/pgsql/.pgpass channel_binding=prefer dbname=replication host=192.168.55.101 port=5432 application_name=s1 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable
test=#
DB内のテーブルごとに１行の形式で、特定のテーブルに対するI/Oに関する統計情報を表示
pg_statio_all_tables
test=# select count(*) from pg_statio_all_tables;
count
-------
110
(1 row)
Time: 23.057 ms
test=# select * from pg_statio_all_tables limit 3;
-[ RECORD 1 ]---+---------------
relid | 16389
schemaname | pg_toast
relname | pg_toast_16386
heap_blks_read | 0
heap_blks_hit | 0
idx_blks_read | 0
idx_blks_hit | 0
toast_blks_read | [null]
toast_blks_hit | [null]
tidx_blks_read | [null]
tidx_blks_hit | [null]
-[ RECORD 2 ]---+---------------
relid | 16386
schemaname | public
relname | test
heap_blks_read | 1
heap_blks_hit | 1
idx_blks_read | [null]
idx_blks_hit | [null]
toast_blks_read | 0
toast_blks_hit | 0
tidx_blks_read | 0
tidx_blks_hit | 0
-[ RECORD 3 ]---+---------------
relid | 2619
schemaname | pg_catalog
relname | pg_statistic
heap_blks_read | 13
heap_blks_hit | 44
idx_blks_read | 8
idx_blks_hit | 225
toast_blks_read | 1
toast_blks_hit | 0
tidx_blks_read | 2
tidx_blks_hit | 0
Time: 4.782 ms
test=#
DBでアクティブなプロセスによって保持されたロックに関する情報を表示
pg_locks
statじゃないけど
test=# select * from pg_locks;
-[ RECORD 1 ]------+----------------
locktype | relation
database | 16385
relation | 12073
page | [null]
tuple | [null]
virtualxid | [null]
transactionid | [null]
classid | [null]
objid | [null]
objsubid | [null]
virtualtransaction | 5/1930
pid | 4873
mode | AccessShareLock
granted | t
fastpath | t
waitstart | [null]
-[ RECORD 2 ]------+----------------
locktype | virtualxid
database | [null]
relation | [null]
page | [null]
tuple | [null]
virtualxid | 5/1930
transactionid | [null]
classid | [null]
objid | [null]
objsubid | [null]
virtualtransaction | 5/1930
pid | 4873
mode | ExclusiveLock
granted | t
fastpath | t
waitstart | [null]
Time: 12.901 ms
test=#