5.postgresql.conf 参数文件详解

1.postgresql.conf 参数示例

vi postgresql.conf
listen_addresses = '*'	    #允许的IP地址所有。
port = 5866  				#端口 
max_connections = 100       #连接数 
shared_buffers=128M         #共享缓冲区,推荐值:1/4 主机物理内存 
wal_buffers=512M            #重做日志缓冲区
work_mem=4M                 #临时排序区大小
maintenance_work_mem=500M   #数据库维护内存
random_page_cost = 2.5  	#随机扫描磁盘的代价,默认是4;SSD等高性能磁盘可以设置小点。
autovacuum_max_workers = 10 #清理进程数量 
checkpoint_completion_target = 0.7 #降低检查点IO负载。默认0.5
archive_timeout =1800 		#切换归档文件频率
archive_command = 'test ! -f /paic/dba/pgbackup/${PGNAME}/archlog/%f && pxz -2 < %p >/paic/dba/pgbackup/${PGNAME}/archlog/%f'
#归档日志我呢见存储地址。
log_directory='pg_log'
logging_collector=on 
log_truncate_on_rotation=on
log_filename='postgresql-%Y-%m-%d_%H%M%S.log'
max_wal_size=1GB  
min_wal_size=80MB 

2.配置文件结构

#注释
key = value
支持的参数值类型:布尔、整数、浮点数、字符串、枚举
include指令(允许嵌套)

备注:
如果配置值中包含数字,则需要用单引号括起。
如果参数值本身包含单引号,我们可以写两个单引号(推荐方法)或用反斜扛包围  


3.参数详细讲解

postgresql.conf-连接
(1)listen_addresses = '*'	#(关联配置文件pg_hba.conf)
#指定服务器在哪些 TCP/IP 地址上监听客户端连接。
值的形式是一个逗号分隔的主机名和/或数字 IP 地址列表。
特殊项*对应监听所有可用 IP 接口
listen_addresses = '192.168.1.*,10.10.10.*,10.120.10.45' #允许这些类型的IP连接。
0.0.0.0 允许监听所有 IPv4 地址
:: 允许监听所有 IPv6 地址
如果列表为空,服务器将根本不会监听任何 IP 接口,在这种情况中只能使用 Unix 域套接字来连接它(本地连接)。
默认值是localhost,它只允许建立本地 TCP/IP "环回"连接。
这能帮助在不安全网络接口上阻止重复的恶意连接请求。
这个参数只能在服务器启动时设置。 
(2)port = 5866
#服务器监听的 TCP 端口;默认是 5432 。
请注意服务器会同一个端口号监听所有的 IP 地址。
这个参数只能在服务器启动时设置。 
(3)max_connections = 100
#决定数据库的最大并发连接数。
默认值通常是 100 个连接,但是如果内核设置不支持(initdb时决定),可能会比这个数少。
这个参数只能在服务器启动时设置。(cpu 1核 50个链接)
(4)shared_buffers
内存,它表示数据缓冲区中的数据块的个数,每个数据块的大小是8KB。
数据缓冲区位于数据库的共享内存中,它越大越好,不能小于128KB。
这个参数只有在启动数据库时,才能被设置。
默认值是128MB。
推荐值:1/4 主机物理内存  
(5)wal_buffers 
用于还未写入磁盘的 WAL 数据的共享内存量。
默认值 -1 表示将该参数值设置为 shared_buffers 的 1/32 的大小 ( 大约 3%),
但是不小于64kB 也不大于一个WAL段的大小(通常为 16MB)。 [64K,16M]
如果自动的选择太大或太小可以手工设置该值,但是任何小于 32kB 的正值都将被当作 32kB。 
这个参数只能在服务器启动时设置。
事务日志缓冲区位于数据库的共享内存中。
推荐值:min( 2047MB, shared_buffers/32 ) = 512MB
(6)work_mem
指定在写到临时磁盘文件之前用于内部排序操作和哈希表的内存量。
ORDER BY, DISTINCT 和合并连接( merge joins) 都会用到排序操作。 
默认值为 4 兆字节( 4MB)。
推荐值:work_mem = (输入内存数量- shared_buffers)/(连接数 * 3)* 1024 (单位是 KB);
(7)maintenance_work_mem(PGA)
它决定数据库的维护操作使用的内存空间的大小。
数据库的维护操作包括 VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY等操作。 
值如果比较大,通常可以缩短VACUUM数据库和从dump文件中恢复数据库需要的时间。
maintenance_work_mem 存放在每个数据库进程的私有内存中,而不是存放在数据库的共享内存中。
这个参数可以在任何时候被设置。
postgresql.conf-优化建议
(8)random_page_cost = 2.5
规划器对一次非顺序获取磁盘页面的代价估计。 默认值是 4.0。 高端存储或者ssd可以适当调小该参数。
(9)autovacuum_max_workers = 10
指定能同时运行的 autovacuum 进程的最大数量, 适当调大, 避免vacuum不及时导致表膨胀
(10)checkpoint_completion_target = 0.7
增加checkpoint_completion_target来降低检查点的I/O负载, 默认0.5
(11)archive_timeout =1800
强制服务器来周期性地切换到一个新的 WAL 段文件,30分钟切换一次。
(12)archive_command = 'test ! -f /paic/dba/pgbackup/${PGNAME}/archlog/%f && pxz -2 < %p >/paic/dba/pgbackup/${PGNAME}/archlog/%f'
pxz压缩归档日志, 64M的归档, 压缩时间可以在0.5s内, 压缩比一般可在1:3左右

4.配置参数级别

系统级别 /集群服务级别/实例级别(全局)
用户/角色级别 
用户/角色 + 数据库级别
会话级别
##参数设置级别	参数存储位置
cluster:postgresql.conf or postgresql.auto.conf
db:					pg_db_role_setting
role:				pg_db_role_setting
db 和 role的组合:	pg_db_role_setting

5.配置修改-全局参数

以下几种方式可实现全局配置
通过linux命令(vim,echo,sed)修改配置文件
启动时设置(不推荐,除非进入单用户模式)
psql -c configparameter=newvalue 
通过ALTER SYSTEM命令修改全局配置(针对postgresql.auto.conf)
\h alter system
命令:       ALTER SYSTEM
描述:       更改服务器的配置参数
语法:
ALTER SYSTEM SET 配置参数 { TO | = } { 值 | '值' | DEFAULT }
ALTER SYSTEM RESET 配置参数
ALTER SYSTEM RESET ALL 

修改后的全局配置将保存在:postgresql.auto.conf里面。

6.配置修改-非全局参数语法 

#######Database	级别
ALTER DATABASE name SET configparameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET configuration 

#########Session级别	
通过SET命令设置当前Session的配置
SET configparameter { TO | = } { value | 'value' | DEFAULT } 
SET configparameter TO DEFAULT;
更新pg_settings视图
UPDATE pg_settings SET setting = new_value WHERE name = 'configparameter';
使用set_config函数更新会话配置 
SELECT set_config('configparameter',new_value,false); 

###########user/Role级别	
ALTER ROLE name [ IN DATABASE database_name ] SET configparameter { TO | = } { value | DEFAULT } 
重置/取消这些参数的设置
ALTER ROLE name [ IN DATABASE database_name ] RESET configparameter 
ALTER ROLE name [ IN DATABASE database_name ] RESET ALL;

7.查看所有参数

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

Context(上下文):生效方式
sihup:给服务器发送HUP信号会使服务器重新加载postgresql.conf配置,可以立即生效
postmaster:只有服务重启才能生效
internal:编译期间的设置,只有重新编译才能生效。就是无法通过命令修改。
backend:与sighup类似,但是不影响正在运行的会话,只在新会话中生效
superuser:使用superuser(如postgres)才能更改,不用重新加载所有配置即可生效
user:单个会话用户可以在任意时间做修改,只会影响该会话。会话级别:session;

8.配置参数生效的方法。

 使配置生效的几种方法
1)用超级用户运行:postgres  	
 SELECT pg_reload_conf(); 
2)使用pg_ctl命令触发SIGHUP信号,需要重新加载才能生效。
 pg_ctl reload
3)用UNIX的kill手动发起HUP信号
ps -ef|grep -i postgres|grep -v grep|awk '{ print $2}' |xargs kill -HUP
kill -HUP pid 
其中 pid 是进程标识。如果想要更改配置而不需停止并重新启动服务,则使用该命令。
4)重启数据库服务 
pg_ctl restart

9.参数查看方法

通过 SHOW 命令查看特定系统设置
SHOW  命令的输出结果会自动根据数值大小选择合适的单位的所有设置
SHOW ALL 命令,其输出结果会针对每个设置选用合适的单位
(1)配置查看示例
show all;                       #查看所有数据库参数的值
postgres=# show all;  
                  name                  |                             setting                              |                                                          description            
                                              
----------------------------------------+------------------------------------------------------------------+---------------------------------------------------------------------------------
 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                        | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f | Sets the shell command that will be called to archive a WAL file.
 archive_mode                           | on                                                               | Allows archiving of WAL files using archive_command.
 archive_timeout                        | 0                                                                | Forces a switch to the next WAL file if a new file has not been started within N
 seconds.
 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 r
eltuples.
 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_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.
 bgwriter_delay                         | 200ms                                                            | Background writer sleep time between rounds.
 bgwriter_flush_after                   | 512kB     
show shared_buffers;   #查看某个参数的当前值(可查看当前会话值)
postgres=#select current_setting('shared_buffers');
 current_setting 
-----------------
 1GB
(1 row)

查询 pg_settings 视图可以很方便地检查当前设置
它本质上是SHOW和SET命令的可替换接口
它还提供了SHOW不能提供的关于每一个参数的一些实现,例如最大值和最小值

--配置查看参考
(2)查看数据文件路径,参数文件路径
postgres=# SELECT name,setting FROM pg_settings WHERE category = 'File Locations';
       name        |              setting               
-------------------+------------------------------------
 config_file       | /postgresql/pgdata/postgresql.conf
 data_directory    | /postgresql/pgdata
 external_pid_file | 
 hba_file          | /postgresql/pgdata/pg_hba.conf
 ident_file        | /postgresql/pgdata/pg_ident.conf
(5 rows)

(3)查看指定的参数
select name,context,unit,boot_val,setting,reset_val from pg_settings 
where name in('listen_addresses','max_connections', 'shared_buffers','effective_cache_size',
'work_mem','maintenance_work_mem') order by context, name;
postgres=#
         name         |  context   | unit | boot_val  | setting | reset_val 
----------------------+------------+------+-----------+---------+-----------
 listen_addresses     | postmaster |      | localhost | *       | *
 max_connections      | postmaster |      | 100       | 500     | 500
 shared_buffers       | postmaster | 8kB  | 1024      | 131072  | 131072
 effective_cache_size | user       | 8kB  | 524288    | 524288  | 524288
 maintenance_work_mem | user       | kB   | 65536     | 65536   | 65536
 work_mem             | user       | kB   | 4096      | 4096    | 4096
(6 rows)

SELECT name,setting FROM pg_settings where name ~ 'listen_addresses';
postgres=# SELECT name,setting FROM pg_settings where name ~ 'listen_addresses';
       name       | setting 
------------------+---------
 listen_addresses | *
(1 row)

SELECT current_setting('listen_addresses');
postgres=# SELECT current_setting('listen_addresses');
 current_setting 
-----------------
 *
(1 row)

10.修改用户相关配置

--查询某参数在某用户级别的设置
postgres=# create user test password 'test';
CREATE ROLE
postgres=# alter role test set log_min_duration_statement = 100;
ALTER ROLE

--方法一:查询pg_user表
postgres=# select * from pg_user where usename='test';
 usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil |            useconfig             
---------+----------+-------------+----------+---------+--------------+----------+----------+----------------------------------
 test    |    16389 | f           | f        | f       | f            | ******** |          | {log_min_duration_statement=100}
(1 row)

 
--方法二:查询 pg_db_role_setting 表
select * from pg_db_role_setting where setrole in 
(select usesysid from pg_user where usename in ('test'))
 order by setrole,setdatabase;
 setdatabase | setrole |            setconfig             
-------------+---------+----------------------------------
           0 |   16389 | {log_min_duration_statement=100}
(1 row)

--参数在某用户下针对数据库级别的设置
alter role test in database postgres set client_min_messages='warning';

select * from pg_db_role_setting where setrole in 
(select usesysid from pg_user where usename in ('test')) 
order by setrole,setdatabase;
 setdatabase | setrole |            setconfig             
-------------+---------+----------------------------------
           0 |   16389 | {log_min_duration_statement=100}
       13593 |   16389 | {client_min_messages=warning}
(2 rows)

11.配置示例-postgresql.auto.conf

(1)#运行日志相关配置变更
alter system set logging_collector = on;
alter system set log_destination = 'csvlog';
alter system set log_directory = 'log';
alter system set log_filename ='postgresql-%Y-%m-%d_%H%M%S.log';  
alter system set log_rotation_age = '1d'; 		#每天生成一个新的日志文件
alter system set log_rotation_size = 0;			#不限制单个日志文件大小
alter system set log_truncate_on_rotation = on;	#覆盖同名文件#只保留7天日志,循环覆盖
alter system set log_hostname = on;				#记录主机信息
alter system set log_line_prefix = '%m';  		#控制每条日志信息的前缀格式,默认值是空串
alter system set log_statement = 'ddl';

(2)检查修改情况
postgres=# 
select distinct(context),name,boot_val,reset_val,min_val,max_val,setting from pg_settings 
where  name in('logging_collector','log_destination','log_directory',
'log_filename','log_rotation_age','log_rotation_size','log_truncate_on_rotation',
'log_hostname','log_line_prefix','log_statement') order by 1;
  context   |           name           |            boot_val            |           reset_val            | min_val | max_val  |            setting             
------------+--------------------------+--------------------------------+--------------------------------+---------+----------+--------------------------------
 postmaster | logging_collector        | off                            | on                             |         |          | on
 sighup     | log_destination          | stderr                         | stderr                         |         |          | stderr
 sighup     | log_directory            | log                            | pg_log                         |         |          | pg_log
 sighup     | log_filename             | postgresql-%Y-%m-%d_%H%M%S.log | postgresql-%Y-%m-%d_%H%M%S.log |         |          | postgresql-%Y-%m-%d_%H%M%S.log
 sighup     | log_hostname             | off                            | off                            |         |          | off
 sighup     | log_line_prefix          | %m [%p]                        | %m [%p]                        |         |          | %m [%p] 
 sighup     | log_rotation_age         | 1440                           | 1440                           | 0       | 35791394 | 1440
 sighup     | log_rotation_size        | 10240                          | 10240                          | 0       | 2097151  | 10240
 sighup     | log_truncate_on_rotation | off                            | on                             |         |          | on
 superuser  | log_statement            | none                           | none                           |         |          | none
(10 rows)

(3)重启
#我们发现它们并不是立即生效。
重启下看看。
[pgsql@postgresql:/home/pgsql]$pg_ctl restart 
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-03-23 08:07:36.249 GMTLOG:  starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-03-23 08:07:36.250 GMTLOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-03-23 08:07:36.250 GMTLOG:  listening on IPv6 address "::", port 5432
2023-03-23 08:07:36.251 GMTLOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-03-23 08:07:36.282 GMTLOG:  redirecting log output to logging collector process
2023-03-23 08:07:36.282 GMTHINT:  Future log output will appear in directory "log".
 done
server started

(4)检查
postgres=# select distinct(context),name,boot_val,reset_val,max_val,setting from pg_settings 
where  name in('logging_collector','log_destination','log_directory',
'log_filename','log_rotation_age','log_rotation_size','log_truncate_on_rotation',
'log_hostname','log_line_prefix','log_statement') order by 1;
  context   |           name           |            boot_val            |           reset_val            | max_val  |            setting             
------------+--------------------------+--------------------------------+--------------------------------+----------+--------------------------------
 postmaster | logging_collector        | off                            | on                             |          | on
 sighup     | log_destination          | stderr                         | csvlog                         |          | csvlog
 sighup     | log_directory            | log                            | log                            |          | log
 sighup     | log_filename             | postgresql-%Y-%m-%d_%H%M%S.log | postgresql-%Y-%m-%d_%H%M%S.log |          | postgresql-%Y-%m-%d_%H%M%S.log
 sighup     | log_hostname             | off                            | on                             |          | on
 sighup     | log_line_prefix          | %m [%p]                        | %m                             |          | %m
 sighup     | log_rotation_age         | 1440                           | 1440                           | 35791394 | 1440
 sighup     | log_rotation_size        | 10240                          | 0                              | 2097151  | 0
 sighup     | log_truncate_on_rotation | off                            | on                             |          | on
 superuser  | log_statement            | none                           | ddl                            |          | ddl
(10 rows)

#可以看到setting 列,我们修改的内容全部生效。

(5).归档相关参数修改
#WAL和归档参数变更
alter system set wal_level = replica;  
alter system set archive_mode = on;
alter system set archive_command = 'test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f';

#参数修改完成后重新启动数据库
pg_ctl -m fast stop
pg_ctl start

(6).查看参数
select distinct(context),name,boot_val,reset_val,max_val,setting from pg_settings 
where  name in('wal_level','archive_mode','archive_command') order by 1;
postgres=# select distinct(context),name,boot_val,reset_val,setting from pg_settings 
where  name in('wal_level','archive_mode','archive_command') order by 1;
  context   |      name       | boot_val |                            reset_val                             |                             setting                              
------------+-----------------+----------+------------------------------------------------------------------+------------------------------------------------------------------
 postmaster | archive_mode    | off      | on                                                               | on
 postmaster | wal_level       | replica  | replica                                                          | replica
 sighup     | archive_command |          | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f
(3 rows)

(7).postgresql.auto.conf 修改过的参数查看
[pgsql@postgresql:/postgresql/pgdata]$cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
logging_collector = 'on'
log_destination = 'csvlog'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = '1d'
log_rotation_size = '0'
log_truncate_on_rotation = 'on'
log_hostname = 'on'
log_line_prefix = '%m'
log_statement = 'ddl'
wal_level = 'replica'
archive_mode = 'on'
archive_command = 'test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f'

#可以发现我们设置的都生效了。


12.总结
setting 是指当前设置;boot_val 是指默认设置;reset_val 是指重新启动服务器或重新加载设置之后的新设置。在 postgresql.conf 中修改了设置后,一定要记得查看一下 setting 和 reset_val 并确保二者是一致的,否则说明设置并未生效,需要重新启动服务器或者重新加载设置。alter system 命令修改过的参数都在:postgresql.auto.conf里面,所以只要我们查看这个文件就可以知道用户在安装好数据库之后都修改了哪些参数。从而可以帮助我们进行参数优化。
 

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
PostgreSQL 10 中,`postgresql.conf` 文件用于指定 PostgreSQL 数据库服务器的全局配置选项。以下是一些常见的配置选项: 1. `listen_addresses`:指定数据库服务器要监听的 IP 地址或主机名。如果要允许来自所有 IP 地址的连接,则可以将其设置为 `*`。 ``` listen_addresses = '*' ``` 2. `port`:指定数据库服务器要监听的端口号。 ``` port = 5432 ``` 3. `max_connections`:指定数据库服务器同时处理的最大客户端连接数。 ``` max_connections = 100 ``` 4. `shared_buffers`:指定 PostgreSQL 要使用的共享内存缓冲区大小。该值应该足够大,以便在内存中缓存常用的数据。 ``` shared_buffers = 1GB ``` 5. `work_mem`:指定 PostgreSQL 中每个排序和哈希操作使用的内存缓冲区大小。该值应该足够大,以便在内存中缓存排序和哈希操作所需的数据。 ``` work_mem = 64MB ``` 6. `maintenance_work_mem`:指定 PostgreSQL 执行维护操作时使用的内存缓冲区大小。例如,当执行 VACUUM 操作时,该值应该足够大,以便在内存中缓存需要清理的数据。 ``` maintenance_work_mem = 256MB ``` 7. `effective_cache_size`:指定 PostgreSQL 估计的系统缓存大小。该值应该足够大,以便 PostgreSQL 可以利用系统缓存中的数据。 ``` effective_cache_size = 4GB ``` 8. `wal_level`:指定 WAL 日志的详细程度。可以设置为 `minimal`、`replica` 或 `logical`。 ``` wal_level = replica ``` 9. `max_wal_senders`:指定主服务器能够向从服务器发送 WAL 日志的最大数量。 ``` max_wal_senders = 10 ``` 以上是 PostgreSQL 10 中 `postgresql.conf` 文件的一些常见配置选项。这些选项可以根据具体的需求进行修改。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值