确保无人能修改路由表

net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
net.ipv4.conf.all.secure_redirects = 0
net.ipv4.conf.default.secure_redirects = 0
#关闭ipv6
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
#内核放弃建立连接之前发送SYN 包的数量
net.ipv4.tcp_syn_retries = 2
#内核放弃建立连接之前发送SYNACK 包的数量
net.ipv4.tcp_synack_retries = 2
#当keepalive 起用的时候，TCP 发送keepalive 消息的频度。缺省是2 小时
net.ipv4.tcp_keepalive_time = 600
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_intvl =15
net.ipv4.tcp_retries1 = 3
net.ipv4.tcp_retries2 = 5
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_max_tw_buckets = 6000
#启用timewait 快速回收
net.ipv4.tcp_tw_recycle = 1
#开启重用。允许将TIME-WAIT sockets 重新用于新的TCP 连接
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.ipv4.tcp_fin_timeout = 10

开启SYN洪水攻击保护

net.ipv4.tcp_syncookies = 1
#限制仅仅是为了防止简单的DoS 攻击
net.ipv4.tcp_max_orphans = 3276800
#未收到客户端确认信息的连接请求的最大值
net.ipv4.tcp_max_syn_backlog = 16384
#限制仅仅是为了防止简单的DoS 攻击
net.ipv4.tcp_max_orphans = 3276800
net.ipv4.tcp_syncookies=1
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304
net.core.wmem_default = 8388608
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
fs.file-max = 1024000
#允许系统打开的端口范围
net.ipv4.ip_local_port_range = 1024 65000
net.ipv4.route.gc_timeout = 100
#系统中每一个端口最大的监听队列的长度,这是个全局的参数,默认值为128
net.core.somaxconn=1024
#每个网络接口接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目
net.core.netdev_max_backlog = 262144
EOF
else
echo “system configuration is already optimized, so we do nothing”
fi
}
optimizeSystemConf

echo “1.3.adjust Optimize Limit”
optimizeLimitConf(){
conf_exist=$(cat /etc/security/limits.conf|grep postgres|wc -l)
if [ $conf_exist -eq 0 ]; then
echo “optimize limit configuration”
cat >> /etc/security/limits.conf << “EOF”
#add by postgres
postgres soft nproc 16384
postgres hard nproc 16384
postgres soft nofile 65536
postgres hard nofile 65536
postgres soft stack 1024000
postgres hard stack 1024000
EOF
else
echo “limit is already optimized, so we do nothing”
fi
}
optimizeLimitConf

echo “1.4.adjust optimize selinux”
sed -i “s/SELINUX=enforcing/SELINUX=disabled/g” /etc/selinux/config
setenforce 0

echo “1.5.off firwalld – this must user do it myself”
function conf_firewall() {
##################gt>0
if [ $(systemctl status firewalld.service | grep -c running) -gt 0 ]; then
systemctl stop firewalld.service
systemctl disable firewalld.service
#firewall-cmd --zone=public --add-port=15400/tcp --permanent
#firewall-cmd --zone=public --add-port=22/tcp --permanent
#firewall-cmd --reload
#禁用防火墙区域偏移
sed -i ‘s/^AllowZoneDrifting=yes/AllowZoneDrifting=no/’ /etc/firewalld/firewalld.conf
else
echo “firewall not open”
fi
}
conf_firewall

echo "“1.6.IPC, some system have this, so do it by user 配置防火墙策略"logind进程cpu占用100%处理”
#CentOS openEuler操作系统默认为关闭，可以跳过该步骤
#sed -i ‘s/#RemoveIPC=no/RemoveIPC=no/g’ /etc/systemd/logind.conf
#systemctl daemon-reload
#systemctl restart systemd-logind
sed -i ‘s/#RemoveIPC=no/RemoveIPC=no/g’ /etc/systemd/logind.conf
systemctl daemon-reload
systemctl restart systemd-logind

echo “1.7.挂载操作系统镜像”
cd $dir
mount *.iso /mnt/
cat << EOF >> /etc/fstab
/dev/sr0 /mnt iso9660 loop 0 0
EOF

mkdir -p /etc/yum.repos.d/bak
mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/bak
cat >> /etc/yum.repos.d/os.repo <<“EOF”
[OS1]
name=OS
baseurl=file:///mnt
enabled=1
gpgcheck=0
EOF

echo “2. db variable list”
BASEPATH=/pgdb
FILE_CONF=/pgdb/data/postgresql.conf
HBA_CONF=/pgdb/data/pg_hba.conf
PGDATA=/pgdb/data
PGHOME=/pgdb/pgsql
SCRIPTS_DIR=/pgdb/scripts
LOGPATH=/pgdb/data/log
PORT=5432

echo “3. postgres exits”
id $postgres >& /dev/null
if [ $? -ne 0 ]
then
echo “postgres already exits”
else
echo “postgres not exits，please create”
groupadd postgres
useradd -g postgres postgres
echo “top@123”|passwd --stdin postgres
sed -ri ‘/^root/a\postgres ALL=(ALL) ALL’ /etc/sudoers
fi

echo “4.create directory”
if [ ! -d $BASEPATH ]
then
mkdir -p $BASEPATH/{data,pg_archive,pg_logicalbackups,pg_physicalbackups,scripts,tmp}
fi

echo “5. unzip”
cd $dir
tar -zxf postgresql*.tar.gz -C $BASEPATH
echo “pgsql upzip success”
echo “directory rights”
mv $BASEPATH/post* $BASEPATH/pgsql
chown -R postgres:postgres $BASEPATH
#-------------------------------install pgsql------------------------------------
echo “6.install dependency package”
#强制关掉yum进程
yum install -y zlib zlib-devel libaio cmake make gcc gcc-c++ readline readline-devel perl
bison flex libyaml net-tools expect openssh-clients tcl openssl openssl-devel
ncurses-devel python python-devel openldap pam systemtap-sdt-devel perl-ExtUtils-Embed

echo “start configure”
cd $PGHOME ./configure --prefix=$ PGHOME --with-pgport=$PORT --with-openssl --with-perl --with-python
if [ $? == 0 ]
then
echo “configure配置通过，开始进行make编译”
#gmake一次性将文档及附加模块全部进行编译和安装,保证所有数据库软件的一致性，避免给后期维护操作带来麻烦
gmake world -j 2
if [ $? == 0 ]
then
echo “make编译通过，开始进行make install安装步骤”
gmake install-world -j 2
if [ ? != 0 ];then echo "make install安装失败" fi echo "安装成功" else echo "make编译失败，检查错误。" fi else echo "configure检查配置失败，请查看错误进行安装库文件" fi echo "7.添加环境变量,进入postgres用户的家目录" cd /home/postgres postgresenvConf(){ conf_exist=(cat .bash_profile |grep postgres|wc -l)
if [ conf_exist -eq 0 ]; then echo "postgres user env configuration" cp .bash_profile .bash_profile.bak sed -i 's/^export PATH/#export PATH/' .bash_profile echo "#add by postgres" >> .bash_profile echo "export PGHOME=PGHOME" >> .bash_profile
echo "export PGDATA= $PGDATA" >> .bash_profile echo 'export PATH=$ PGHOME/bin: $PATH' >> .bash_profile echo 'export MANPATH=$ PGHOME/share/man: $MANPATH' >> .bash_profile echo 'export LD_LIBRARY_PATH=$ PGHOME/lib:$LD_LIBRARY_PATH’ >> .bash_profile
echo "export LANG=“en_US.UTF-8"” >> .bash_profile
echo ‘export DATE=date +"%Y%m%d%H%M"’ >> .bash_profile
source /home/postgres/.bash_profile
else
echo “postgres user env is already config, so we do nothing”
fi
}
postgresenvConf

echo “8. 开始进行pgsql的配置”
echo “切换至postgres用户来初始化数据库，设置密码文件”
su - postgres -c “echo “postgres”>> .pgpass”
su - postgres -c “chmod 0600 /home/postgres/.pgpass”
su - postgres -c ‘$PGHOME/bin/initdb --username=postgres --pwfile=/home/postgres/.pgpass -D $PGDATA --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8’
if [ $? == 0 ]
then
echo “初始化成功”
chown -R postgres:postgres $BASEPATH
chmod -R 755 $BASEPATH
chmod 600 $LOGPATH
else
echo “初始化失败”
fi
echo “configure param”

cp $FILE_CONF $PGDATA/postgresql.confbak
sed -i “/^#listen_addresses = ‘localhost’/s/#listen_addresses = ‘localhost’/listen_addresses = ‘’/" $FILE_CONF
sed -i ‘s/^#port = 5432/port = 5432/’ $FILE_CONF
sed -i ‘s/max_connections = 100/max_connections = 500/’ $FILE_CONF #max_connectionswork_mem 上千个连接，建议配置连接池
sed -i “/^#max_prepared_transactions = 0/s/#max_prepared_transactions = 0/max_prepared_transactions = 500/” $FILE_CONF #等于
sed -i “/^shared_buffers = 128MB/s/shared_buffers = 128MB/shared_buffers = 1024MB/” $FILE_CONF #物理内存25~40%
sed -i “/^#effective_cache_size = 4GB/s/#effective_cache_size = 4GB/effective_cache_size = 3GB/” $FILE_CONF #查询优化器可用的OS CACHE实际不占用内存内存0.75
sed -i “/^#work_mem = 4MB/s/^#work_mem = 4MB/work_mem = 30MB/” $FILE_CONF #去掉注释 #内存排序 # max(min(规格内存/4096, 64MB), 4MB)
sed -i “/^#maintenance_work_mem = 64MB/s/#maintenance_work_mem = 64MB/maintenance_work_mem = 256MB/” $FILE_CONF # min( 8G, (主机内存1/8)/max_parallel_maintenance_workers )
sed -i “/^#max_parallel_maintenance_workers = 2/s/#max_parallel_maintenance_workers = 2/max_parallel_maintenance_workers = 4/” $FILE_CONF #CPU核数/4
sed -i “/^#max_parallel_workers_per_gather = 2/s/#max_parallel_workers_per_gather = 2/max_parallel_workers_per_gather = 4/” $FILE_CONF #CPU核数/4
sed -i “/^#max_parallel_workers = 8/s/#max_parallel_workers = 8/max_parallel_workers = 16/” $FILE_CONF #CPU核数
sed -i “/^#max_worker_processes = 8/s/#max_worker_processes = 8/max_worker_processes = 16/” $FILE_CONF #CPU核数
sed -i “/^min_wal_size = 80MB/s/min_wal_size = 80MB/min_wal_size = 1GB/” $FILE_CONF
sed -i “/^min_wal_size = 80MB/s/min_wal_size = 80MB/min_wal_size = 1GB/” $FILE_CONF
sed -i “/^{#wal_level/s/}#//” $FILE_CONF #去掉注释
sed -i ‘s/#archive_mode = off/archive_mode = on/’ $FILE_CONF
sed -i "/^#archive_command = ‘’/s/#archive_command = ‘’/archive_command =’/usr/bin/lz4 -q -z %p $BASEPATH/pg_archive/%f.lz4’/” $FILE_CONF #-q取消警告-z强制压缩
sed -i “/^#log_destination = ‘stderr’/s/#log_destination = ‘stderr’/log_destination = ‘csvlog’/” $FILE_CONF
sed -i “/^#logging_collector = off/s/#logging_collector = off/logging_collector = on/” $FILE_CONF
sed -i “/^#log_disconnections = off/s/#log_disconnections = off/log_disconnections = on/” $FILE_CONF #用户退出时是否写入日志
sed -i “/^#log_connections = off/s/#log_connections = off/log_connections = on/” $FILE_CONF #用户session登录时写入日志
sed -i “/^#authentication_timeout = 1min/s/#authentication_timeout = 1min/authentication_timeout = 59s/” $FILE_CONF #用户session登录时写入日志
sed -i “/^#log_directory = ‘log’/s/^#//” $FILE_CONF #去掉注释
sed -i “/^{#log_filename/s/}#//” $FILE_CONF #去掉注释
sed -i “/^{#log_file_mode/s/}#//” $FILE_CONF #去掉注释
sed -i “/^{#log_rotation_age/s/}#//” $FILE_CONF #去掉注释
sed -i “/^{#log_rotation_size/s/}#//” $FILE_CONF #去掉注释

sed -i “/^#temp_buffers = 8MB/s/#temp_buffers = 8MB/temp_buffers = 256MB/” $FILE_CONF

cp $HBA_CONF $PGDATA/pg_hba.confbak
echo “host all all 0.0.0.0/0 md5” >> $HBA_CONF

echo “9. auto starting up”
cat > /usr/lib/systemd/system/postgres.service << “EOF”
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5432
Environment=PGDATA=/pgdb/data
OOMScoreAdjust=-1000
ExecStart=/pgdb/pgsql/bin/pg_ctl start -D $PGDATA
ExecStop=/pgdb/pgsql/bin/pg_ctl stop -D $PGDATA -s -m fast
ExecReload=/pgdb/pgsql/bin/pg_ctl reload -D PGDATA -s TimeoutSec=300 [Install] WantedBy=multi-user.target EOF chmod +x /usr/lib/systemd/system/postgres.service systemctl daemon-reload systemctl start postgres.service systemctl enable postgres.service #判断是否启动成功 process=(ps -ef | grep -v ‘grep’| grep ‘$PGHOME/bin/postgres’|awk '{print 2}') if [ -n "process" ];then #检测字符串长度是否不为 0，不为 0 返回 true。
echo “install success ans start success”
else
echo “install fail”
fi
echo “---------------------------切归档------------------------------------------------------”
su - postgres -c “$PGHOME/bin/psql -d postgres -h127.0.0.1 -p5432 -c “select pg_switch_wal();””
echo “---------------------------------------------------------------------------------------”
echo “12.configure crontab”
if [[ -e /var/spool/cron/postgres ]]; then
cp /var/spool/cron/postgres /var/spool/cron/postgresbak
else
touch /var/spool/cron/postgres
fi
chown -R postgres:postgres $SCRIPTS_DIR
chmod +x $SCRIPTS_DIR/*
cat >> /var/spool/cron/postgres << “EOF”

PostgresBegin

30 00 * * * $BASEPATH/scripts/pg_backup.sh > /dev/null 2>&1
#设置归档策略:每晚零点10分删除7天前归档文件，视情况更改成1个月
10 00 * * * find $BASEPATH/pg_archive -type f -name “0000000*” -mtime +7 -exec rm {} ; > /dev/null 2>&1
#设置日志保留2天，视情况更改成半年或3个月
00 01 * * * find $PGDATA/log -type f -name “postgresql*.log” -mtime +7 -exec rm {} ; > /dev/null 2>&1
00 01 * * * find $PGDATA/log -type f -name “postgresql*.csv” -mtime +7 -exec rm {} ; > /dev/null 2>&1
EOF
echo “13.数据库信息”
echo “操作系统数据库用户：postgres;密码：postgres”
echo “数据库信息:postgres;密码：postgres;port:5432”
echo “14.创建业务库和用户”
su - postgres -c “/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5432 -c “create user dh with superuser encrypted password ‘dh’;””
su - postgres -c “/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5432 -c “create database mix_db owner top_mixdb;””
su - postgres -c “/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5432 -c “grant all privileges on database mix_db to top_mixdb;””
su - postgres -c “/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5432 -c “grant all privileges on all tables in schema public to top_mixdb;””
su - postgres -c “/pgdb/pgsql/bin/psql -d postgres -h127.0.0.1 -p5432 -c “alter user postgres with valid until ‘infinity’;””

#echo “15.创建只读用户”
#su - postgres -c “PGHOME/bin/psql -d postgres -h127.0.0.1 -p5432 -c \"create user zcst_read with encrypted password 'postgres';\"" #设置用户默认开启只读事务 #su - postgres -c "PGHOME/bin/psql -d postgres -h127.0.0.1 -p5432 -c “alter user zcst_read set default_transaction_read_only=on;””
#将public schema中usage权限赋予给zcst_read用户,访问所有已存在的表
#su - postgres -c “PGHOME/bin/psql -d postgres -h127.0.0.1 -p5432 -c \"grant usage on schema public to zcst_read;\"" #将public schema中表的查询权限赋予给zcst_read用户,访问所有已存在的表 #su - postgres -c "PGHOME/bin/psql -d postgres -h127.0.0.1 -p5432 -c “grant select on all tables in schema public to zcst_read;””
#未来访问public模式下所有新建的表
#su - postgres -c “$PGHOME/bin/psql -d postgres -h127.0.0.1 -p5432 -c “alter default privileges in schema public grant select on tables to zcst_read;””

有用 0

打赏 0

董小姐

上传附件：pginstall.sh

有用 0

打赏 0

回答交流

Markdown

请输入正文

导入word

提交