Postgresql通用安装指导手册

环境说明

操作系统版本	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';