configs for postgresql restart and postgresql reload

-- configs requiring postgresql restart
select name, setting, context
  from pg_settings where context = 'postmaster';

postgres=# select name, setting, context
postgres-# from pg_settings where context = 'postmaster';
name | setting | context
-------------------------------------+-----------------------------------------------+------------
allow_system_table_mods | off | postmaster
archive_mode | off | postmaster
autovacuum_freeze_max_age | 200000000 | postmaster
autovacuum_max_workers | 3 | postmaster
autovacuum_multixact_freeze_max_age | 400000000 | postmaster
bonjour | off | postmaster
bonjour_name | | postmaster
cluster_name | | postmaster
config_file | /usr/local/pgtest/bin/../data/postgresql.conf | postmaster
data_directory | /usr/local/pgtest/bin/../data | postmaster
dynamic_shared_memory_type | posix | postmaster
event_source | PostgreSQL | postmaster
external_pid_file | | postmaster
hba_file | /usr/local/pgtest/bin/../data/pg_hba.conf | postmaster
hot_standby | off | postmaster
huge_pages | try | postmaster
ident_file | /usr/local/pgtest/bin/../data/pg_ident.conf | postmaster
listen_addresses | * | postmaster
logging_collector | off | postmaster
max_connections | 100 | postmaster
max_files_per_process | 1000 | postmaster
max_locks_per_transaction | 64 | postmaster
max_pred_locks_per_transaction | 64 | postmaster
max_prepared_transactions | 0 | postmaster
max_replication_slots | 0 | postmaster
max_wal_senders | 0 | postmaster
max_worker_processes | 8 | postmaster
port | 5432 | postmaster
shared_buffers | 16384 | postmaster
shared_preload_libraries | | postmaster
ssl | off | postmaster
ssl_ca_file | | postmaster
ssl_cert_file | server.crt | postmaster
ssl_ciphers | none | postmaster
ssl_crl_file | | postmaster
ssl_ecdh_curve | none | postmaster
ssl_key_file | server.key | postmaster
ssl_prefer_server_ciphers | on | postmaster
superuser_reserved_connections | 3 | postmaster
track_activity_query_size | 1024 | postmaster
track_commit_timestamp | off | postmaster
unix_socket_directories | /tmp | postmaster
unix_socket_group | | postmaster
unix_socket_permissions | 0777 | postmaster
wal_buffers | 512 | postmaster
wal_level | minimal | postmaster
wal_log_hints | off | postmaster

 

-- configs requiring postgresql reload
select name, setting, context
 from pg_settings where context = 'sighup';

postgres=# select name, setting, context
postgres-# from pg_settings where context = 'sighup';
name | setting | context
---------------------------------+--------------------------------+---------
archive_command | (disabled) | sighup
archive_timeout | 0 | sighup
authentication_timeout | 60 | sighup
autovacuum | on | sighup
autovacuum_analyze_scale_factor | 0.1 | sighup
autovacuum_analyze_threshold | 50 | sighup
autovacuum_naptime | 60 | sighup
autovacuum_vacuum_cost_delay | 20 | sighup
autovacuum_vacuum_cost_limit | -1 | sighup
autovacuum_vacuum_scale_factor | 0.2 | sighup
autovacuum_vacuum_threshold | 50 | sighup
autovacuum_work_mem | -1 | sighup
bgwriter_delay | 200 | sighup
bgwriter_lru_maxpages | 100 | sighup
bgwriter_lru_multiplier | 2 | sighup
checkpoint_completion_target | 0.5 | sighup
checkpoint_timeout | 300 | sighup
checkpoint_warning | 30 | sighup
db_user_namespace | off | sighup
fsync | on | sighup
full_page_writes | on | sighup
hot_standby_feedback | off | sighup
krb_caseins_users | off | sighup
krb_server_keyfile | | sighup
log_autovacuum_min_duration | -1 | sighup
log_checkpoints | off | sighup
log_destination | stderr | sighup
log_directory | pg_log | sighup
log_file_mode | 0600 | sighup
log_filename | postgresql-%Y-%m-%d_%H%M%S.log | sighup
log_hostname | off | sighup
log_line_prefix | | sighup
log_rotation_age | 1440 | sighup
log_rotation_size | 10240 | sighup
log_timezone | US/Pacific | sighup
log_truncate_on_rotation | off | sighup
max_standby_archive_delay | 30000 | sighup
max_standby_streaming_delay | 30000 | sighup
max_wal_size | 64 | sighup
min_wal_size | 5 | sighup
pre_auth_delay | 0 | sighup
restart_after_crash | on | sighup
stats_temp_directory | pg_stat_tmp | sighup
synchronous_standby_names | | sighup
syslog_facility | local0 | sighup
syslog_ident | postgres | sighup
trace_recovery_messages | log | sighup
vacuum_defer_cleanup_age | 0 | sighup
wal_keep_segments | 0 | sighup
wal_receiver_status_interval | 10 | sighup
wal_receiver_timeout | 60000 | sighup
wal_retrieve_retry_interval | 5000 | sighup
wal_sender_timeout | 60000 | sighup
wal_sync_method | fdatasync | sighup
wal_writer_delay | 200 | sighup

 

postgres=# select name ,context from pg_settings;
name | context
-------------------------------------+-------------------
allow_system_table_mods | postmaster
application_name | user
archive_command | sighup
archive_mode | postmaster
archive_timeout | sighup
array_nulls | user
authentication_timeout | sighup
autovacuum | sighup
autovacuum_analyze_scale_factor | sighup
autovacuum_analyze_threshold | sighup
autovacuum_freeze_max_age | postmaster
autovacuum_max_workers | postmaster
autovacuum_multixact_freeze_max_age | postmaster
autovacuum_naptime | sighup
autovacuum_vacuum_cost_delay | sighup
autovacuum_vacuum_cost_limit | sighup
autovacuum_vacuum_scale_factor | sighup
autovacuum_vacuum_threshold | sighup
autovacuum_work_mem | sighup
backslash_quote | user
bgwriter_delay | sighup
bgwriter_lru_maxpages | sighup
bgwriter_lru_multiplier | sighup
block_size | internal
bonjour | postmaster
bonjour_name | postmaster
bytea_output | user
check_function_bodies | user
checkpoint_completion_target | sighup
checkpoint_timeout | sighup
checkpoint_warning | sighup
client_encoding | user
client_min_messages | user
cluster_name | postmaster

......

对于context有多种可能的取值

internal

这些设置不能被直接修改，它们反映了内部决定的值。某些可能在使用不同配置选项重建系统时或者改变initdb的选项时可以调整。

postmaster

这些设置只能在服务器启动时应用，因此任何修改都需要重启服务器。这些设置的值通常都存储在postgresql.conf文件中，或者在启动服务器时通过命令行传递。当然，具有更低context类型的设置也可以在服务器启动时间被设置。

sighup

对于这些设置的修改可以在postgresql.conf中完成并且不需要重启服务器。发

送一个SIGHUP信号给postmaster会导致它重新读取postgresql.conf并应用修改。Postmaster将会把SIGHUP信号传递给它的孩子进程，这样它们也会获得新的值。

backend

对于这些设置的修改可以在postgresql.conf中完成并且不需要重启服务器。它们也可以在一个连接请求包（例如，通过libpq的PGOPTIONS环境变量）中为一个特定会话设置。然而，这些设置在会话启动后永不变化。如果你在postgresql.conf中修改它们，可以向postmaster发送一个SIGHUP信号让它重读postgresql.conf。新值只会影响后续启动的会话。

superuser

这些设置可以从postgresql.conf设置，或者在会话中用SET命令设置。仅当没有通过SET设置会话本地值时，postgresql.conf中的改变才会影响现有的会话。

user

这些设置可以从postgresql.conf设置，或者在会话中用SET命令设置。任何用户都被允许修改它的会话本地值。仅当没有通过SET设置会话本地值时，postgresql.conf中的改变才会影响现有的会话。

 

pg_settings视图不能被插入或者从中删除，但是它可以被更新。在pg_settings的一行上的一个UPDATE等价于在该参数上执行一个SET命令。修改将只会影响当前会话使用的值。如果一个UPDATE在一个后来中断的事务中被发出，UPDATE命令的效果也会随着事务的回滚而消失。一旦所在的事务被提交，效果将一直保持到会话结束，除非有其他UPDATE或SET重新修改它。