爱站程序员基地1.修改参数列表（1）执行计划enable_nestloop = off #默认为onenable_seqscan = off #默认为onenable_indexscan = onenable_bitmapscan = onmax_connections = 1000 #默认为100 （2）内存相关shared_buffers = 16GB # 默认为128MBeffective_cache_size = 24GB #默认为4GBwork_mem = 32MBtemp_buffers = 32MBwal_buffers = 512MBmaintenance_work_mem = 2GB （3）autovacuumautovacuum = onautovacuum_max_workers = 3 #默认为3autovacuum_work_mem = 256MBautovacuum_vacuum_scale_factor = 0.05 #默认为0.2 （4）检查点max_wal_size = 4GB #默认为1GBmin_wal_size = 1GB #默认为80MBcheckpoint_completion_target = 0.9 #默认为0.5checkpoint_timeout = 30min #默认为5min （5）日志log_destination = csvloglog_directory = pg_loglogging_collector = onlog_min_duration_statement = 800log_rotation_size = 1024MBlog_truncate_on_rotation = onlog_filename = \’xl_log_%a.log\’ 2.参数设置公式
这个shell脚本可以使用shell的关联数组对
[code]#!/bin/bash#command to execute script: su - postgres -c \"./create.sh\"#default value refer to standard server(cpu:6cores,memory:32G,storage:12T)#value according to the actual situation#List of parameters to be modified:<<!enable_seqscan = offenable_indexscan = onenable_bitmapscan = onmax_connections = 1000shared_buffers = 16GBeffective_cache_size = 24GBwork_mem = 32MBtemp_buffers = 32MBwal_buffers = 512MBmaintenance_work_mem = 2GBautovacuum_max_workers = 3autovacuum_work_mem = 256MBcheckpoint_timeout = 30minmax_wal_size = 4GBmin_wal_size = 1GBcheckpoint_completion_target = 0.9log_destination = csvloglog_directory = pg_loglogging_collector = onlog_min_duration_statement = 800log_rotation_size = 1024MBlog_truncate_on_rotation = onlog_filename = \'xl_log_%a.log\'!ERROR=\"\\033[41;37m ERROR \\033[0m\"INFO=\"\\033[42;37m INFO \\033[0m\"WARN=\"\\033[43;37m WARN \\033[0m\"COMMON_ERROR=\"some error happened, specific information please see console output\"# Array of parametersdeclare -A parameter_arrayparameter_array=([enable_seqscan]=off [enable_indexscan]=on [enable_bitmapscan]=on [max_connections]=1000 [shared_buffers]=16GB[effective_cache_size]=24GB [work_mem]=32MB [temp_buffers]=32MB [wal_buffers]=512MB [maintenance_work_mem]=2GB[autovacuum_max_workers]=3 [autovacuum_work_mem]=256MB [checkpoint_timeout]=30min [max_wal_size]=4GB[min_wal_size]=1GB [checkpoint_completion_target]=0.9 [log_destination]=csvlog [log_directory]=pg_log[logging_collector]=on [log_min_duration_statement]=800 [log_rotation_size]=1024MB [log_truncate_on_rotation]=on [log_filename]=viid_log_%a.log )#default value :pgctl_path=data_directory=memory=# check command exit value, 0 is successfunction check_fun(){status=$?error=${COMMON_ERROR}if [[ 0 -ne ${status} ]] ; thenecho -e \"${ERROR} ${error}\"exit 1fi}# prepare conditionsfunction prepare_conditions(){data_directory=$(psql -qtAX -c \"show data_directory\" | sed \'s/[ ]//g\')check_funif [[ ! -d \"${data_directory}\" ]] ; thenecho -e \"${ERROR} database\'s data directory does not exist\"exit 1fi# physical machine environmentmemory=$(grep MemTotal /proc/meminfo | awk \'{print $2 / 1024 / 1024}\' | sed \'s/\\.[0-9]*//\' | tail -n 1)check_fun# docker environmentmemory_limit=$(($(awk \'{print $1}\' /sys/fs/cgroup/memory/memory.limit_in_bytes) / 1024 / 1024 /1024))check_fun# comparing the two, choose the smaller one.if [[ \"${memory_limit}\" -le \"${memory}\" ]] ; thenmemory=${memory_limit}fi}# calculate parametersfunction calculate_parameters(){# 50%*memoryparameter_array[shared_buffers]=$((memory * 1024 / 2))\"MB\"# 75%*memoryparameter_array[effective_cache_size]=$((memory * 1024 * 3 / 4 ))\"MB\"# <1%parameter_array[work_mem]=${memory}\"MB\"# <1%parameter_array[temp_buffers]=${memory}\"MB\"# 32GB => 512MBparameter_array[wal_buffers]=$((memory * 16 ))\"MB\"# 32GB => 2048MBparameter_array[maintenance_work_mem]=$((memory * 64 ))\"MB\"}# modify parametersfunction modify_parameters(){# modify parameters by modifying file postgresql.conf:for parameter in ${!parameter_array[*]}do#check whether the parameters have been modified# PostgreSQL\'s default parameter configuration example: enable_seqscan = on# viid\'s example: enable_seqscan=\'on\'check_out=$(grep \"^${parameter}=\" \"${data_directory}\"/postgresql.conf | grep -v \'#\' | tail -n 1)# process sleepsleep 0.2sif [[ -z \"${check_out}\" ]] ; thenecho \"${parameter}=\'${parameter_array[${parameter}]}\'\" >> \"${data_directory}\"/postgresql.confcheck_funecho -e \"${INFO} modify ${parameter} successfully\"elseif [[ \"${check_out}\" = \"${parameter}=\'${parameter_array[${parameter}]}\'\" ]] ; thenecho -e \"${INFO} ${parameter} is already configured, then skip this step\"elsesed -i s!^\"${check_out}\"!\"${parameter}=\'${parameter_array[${parameter}]}\'\"!g \"${data_directory}\"/postgresql.confcheck_funecho -e \"${INFO} modify ${parameter} successfully\"fifidone}# create directory(pg_log and pg_arch), and set user postgres permissionfunction create_dir(){directory_array=(\"pg_arch\" \"pg_log\")for directory in ${directory_array[*]};do# process sleepsleep 0.2sif [[ ! -d \"${data_directory}/${directory}\" ]] ; thenmkdir -p \"${data_directory}\"/\"${directory}\"echo -e \"${INFO} path ${data_directory}/${directory} create successfully\"elseecho -e \"${INFO} ${data_directory}/${directory} is already exists, then skip this step\"fi# set user postgres permissionchown postgres:postgres \"${data_directory}\"/\"${directory}\"done}# because the environment is different, need to find the path of the database restart command \'pg_ctl\'function find_cmd(){result=$(find / -name pg_ctl 2> /dev/null | grep bin/pg_ctl$ | tail -n 1 )# check whether the path existsif [[ -z \"${result}\" ]] ; thenecho -e \"${ERROR} database restart command \'pg_ctl\' not exists\"echo -e \"${ERROR} please check to see if the database is installed or the command directory does not have permission to access it\"exit 1elseecho -e \"${INFO} database restart command \'pg_ctl\' path: ${result}\"pgctl_path=${result}fi}# user choose whether to restart or notfunction check_restart(){read -r -p \"Is it necessary to restart database immediately?[Enter YES or NO]:\" resultif [[ \"${result,,}\" = \"yes\" ]] ; thenecho -e \"${INFO} start to restart database\"${pgctl_path} restart -D \"${data_directory}\" >& /dev/nullif [[ 0 -ne ${status} ]] ; thenecho -e \"${ERROR} restart database failed\"exit 12fielif [[ \"${result,,}\" = \"no\" ]] ; thenecho -e \"${WARN} please restart database manually\"echo -e \"${WARN} if you don\'t restart, database may not be available\"exit 11elseecho -e \"${ERROR} invalid input,please enter again\"check_restartfi}# ******* start *******# prepare conditions:memory ,data_directoryprepare_conditions# calculate parameterscalculate_parameters# modify parametersmodify_parameters# create directory(pg_log and pg_arch)create_dir# the path of the database restart command \'pg_ctl\'find_cmd# remind user that they need to restart database to take effect# process sleepsleep 0.5secho -e \"\"echo -e \"*******************************************************************\"echo -e \"* *\"echo -e \"* *\"echo -e \"* restart database to take effect *\"echo -e \"* *\"echo -e \"* *\"echo -e \"*******************************************************************\"# process sleepsleep 0.5s# user choose whether to restart or not# check_restartecho -e \"${INFO} start to restart database\"${pgctl_path} restart -D \"${data_directory}\" >& /dev/null
