环境说明
| 操作系统版本 | PG数据库版本 |
|---|
| RHEL 7.6 | PG12.5 |
操作系统配置
/etc/hosts配置
hostnamectl set-hostname pgdb
cat >> /etc/hosts <<EOF
192.1683.56.11 pgdb
EOF
关闭NetworkManager
systemctl stop NetworkManager
systemctl disable NetworkManager
systemctl status NetworkManager
关闭防火墙和Selinux
systemctl stop firewalld.service
systemctl disable firewalld.service
sed -i "s/^SELINUX=.*$/SELINUX=disabled/" /etc/selinux/config
关闭Numa配置
# 添加numa=off到内核中
grubby --args=numa=off --update-kernel /boot/vmlinuz-3.10.0-862.el7.x86_64
# 查看内核参数信息
grubby --info /boot/vmlinuz-3.10.0-862.el7.x86_64
# 重启服务器
sync
sync
sync
reboot now
内核参数配置
cat >> /etc/sysctl.conf <<-EOF
#1/2 of physical memory (1024*1024*1024)
kernel.shmmax = 1073741824
#physical memory size / pagesize, most set to 2097152
kernel.shmall = 2097152
#default set to 4096
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 4194304
fs.file-max = 6815744
#huge page, when shared_buffer is set to10G, huge page can be set to 15G*1024/2=25600
vm.nr_hugepages = 25600
EOF
#应用生效内核参数
sysctl -p
#进程限制
cat >> /etc/security/limits.conf <<-EOF
* soft nofile 1024000
* hard nofile 1024000
* soft nproc unlimited
* hard nproc unlimited
* soft core unlimited
* hard core unlimited
* soft memlock unlimited
* hard memlock unlimited
EOF
Yum源配置
mount /dev/sr0 /mnt
cat >> /etc/yum.repo.d/local.repo <<-EOF
[local]
name=local
baseurl=file///mnt
gpgcheck=0
EOF
yum repolist
安装系统依赖包
yum -y install coreutils glib2 lrzsz mpstat dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make smartmontools flex bison perl-devel perl-ExtUtils* openldap-devel jadetex openjade bzip2
源码安装PG12.5
创建用户并配置环境变量
useradd postgres
echo ‘password’ | passwd –stdin postgres
su – postgres
cat >> ~/.bash_profile <<-EOF
export PGHOME=/pg/pg12
export PGDATA=/pg/pg12/data
export PATH=$PATH:$PG_HOME/bin
EOF
创建安装目录并解压安装包
mkdir -p /pg/pg12/data
tar xzf postgresql-12.5.tar.gz -C /pg/pg12
编译安装PG12.5
cd /pg/pg12/postgresql-12.5
./configure --prefix=/pg/pg12 --enable-nls --with-perl --with-python --with-tcl --with-gssapi --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt
make world
make install-word
数据库连接配置
配置数据库连接名单
vi $PGDATA/pg_hba.conf
#allow login ips
host all all 192.168.56.0/24 md5
#reject super user remote login
host all postgres 0.0.0.0/0 reject
配置监听客户端参数
vi $PGDATA/postgresql.conf
listen_addresses ＝’*’
数据库优化配置
内存参数优化
vi $PGDATA/postgresql.conf
#4/2 of physical memory, default 128M
shared_buffers = 768MB
#1/16 of physical memory
maintenance_work_mem = 200MB
#shared_buffers/32
wal_buffers = -1
#3/4 of physical menory
effective_cache_size = 2304MB
日志参数优化
logging_collector = on
log_destination = 'csvlog'
log_min_duration_statement = 120000
审计记录参数优化
log_statement = 'ddl'
打印锁等待事件参数优化
log_lock_waits = on
临时文件参数优化
log_temp_files = 250000
报错日志的冗长参数优化
log_error_verbosity = verbose
检查点参数优化
log_checkpoints = on
设置强制检查点时间参数优化
checkpoint_timeout = 5min
流复制保留wal文件数量参数优化
wal_keep_segments = 64
设置最大连接数和super最大连接数参数优化
max_connections = 500
superuser_reserved_connections = 10
分组提交参数优化
commit_delay = 10
commit_siblings = 5
Autovacuum参数优化
autovacuum_max_workers = 3
归档配置参数优化
archive_mode = on
archive_command = 'cp %p /data/pg12/arch/archive_wal_%f'
启动数据库参数优化
initdb -D $PG_DATA
pg_ctl start -D $PG_DATA
psql
alter user postgres with password 'postgres';
| 
