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 restart9.参数查看方法
通过 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里面,所以只要我们查看这个文件就可以知道用户在安装好数据库之后都修改了哪些参数。从而可以帮助我们进行参数优化。
7792
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
