三、PostgreSQL单机安装

一、规划
用户：postgres
目录：/postgresql/{soft,pg1414,pgdata,scripts,backup,archive} -p
二、安装过程

# 创建postgres用户
useradd -rmU postgres
# 修改postgres用户密码,密码也是：postgres
passwd postgres

#创建相关目录并授权
mkdir /postgresql/{soft,pg1414,pgdata,scripts,backup,archive} -p
chown -R postgres. /postgresql/
chmod -R 700 /postgresql

#安装依赖
#挂载ISO
mount /dev/cdrom /mnt

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

yum install -y  gcc readline-devel zlib-devel openssl-devel wget
#下载
cd /postgresql/soft
wget https://ftp.postgresql.org/pub/source/v14.14/postgresql-14.14.tar.gz --no-check-certificate
#或者下载好上传到此目录

#解压安装
tar xf postgresql-14.14.tar.gz
cd postgresql-14.14
./configure --prefix=/postgresql/pg1414 --with-openssl --without-icu
gmake -j2 world && gmake -j2 install-world

#做链接: 之后编译安装高版本的PG, 停掉数据库，将目录指向新的数据库
#如： /postgresql/pg1416
#修改链接到新软件安装目录上：ln -snf /postgresql/pg1416 /postgresql/pg14
ln -sf /postgresql/pg1414 /postgresql/pg14

#配置环境变量
cat >> /home/postgres/.bash_profile <<"EOF"
export LANG=en_US.UTF-8
export PS1="[\u@\h \W]\$ "
export PGPORT=5432
export PGHOME=/postgresql/pg14
export PGDATA=/postgresql/pgdata
export PGUSER=postgres
export PGDATABASE=postgres
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH
export DATE=`date +"%Y%m%d%H%M"`
export MANPATH=$PGHOME/share/man:$MANPATH
EOF

source  /home/postgres/.bash_profile

#初始化
su - postgres
initdb -D $PGDATA -E UTF8 --locale=en_US.utf8

#配置数据库参数
cat >> $PGDATA/postgresql.conf <<"EOF"
listen_addresses = '*'
port = 5432
max_connections = 300
shared_buffers = 1GB
work_mem = 8MB
maintenance_work_mem = 256MB
effective_io_concurrency = 2
wal_sync_method = fdatasync
wal_buffers = 16MB
wal_keep_size = 65536
wal_sender_timeout = 60
min_wal_size = 2GB
max_wal_size = 8GB
archive_mode = on
archive_command = 'test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f'
archive_timeout = 15min
effective_cache_size = 3GB
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 10MB
log_statement = 'ddl'
log_min_duration_statement = 200ms
autovacuum = on
datestyle = 'ISO, YMD'
timezone = 'Asia/Shanghai'
shared_preload_libraries = 'pg_stat_statements'
EOF

#配置认证参数
cat >> $PGDATA/pg_hba.conf <<"EOF"
host    all             all             0.0.0.0/0        scram-sha-256
host    replication     all             0.0.0.0/0        scram-sha-256
EOF

#配置系统服务
su - root
cat > /etc/systemd/system/pg-14.service <<"EOF"
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5432
Environment=PGDATA=/postgresql/pgdata/
OOMScoreAdjust=-1000
ExecStart=/postgresql/pg14/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/postgresql/pg14/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/postgresql/pg14/bin/pg_ctl reload -D ${PGDATA} -s
TimeoutSec=300
[Install]
WantedBy=multi-user.target
EOF

#systemctl启动PG
systemctl daemon-reload
systemctl enable pg-14
systemctl start pg-14
systemctl status pg-14
systemctl stop pg-14