通过脚本自动部署PostgreSQL数据库

1. 说明

该脚本部署的pg数据库为源码通过编译安装的方式

2. 执行过程

[root@olinux73_model ~]# chmod +x ./install.sh
[root@olinux73_model ~]# ./install.sh 
####清理环境
####关闭防火墙
####关闭Selinux
setenforce: SELinux is disabled
####设置PG环境变量
PGBASE=/u01/app
FILE_CONF=/u01/app/pgdata/postgresql.conf
HBA_CONF=/u01/app/pgdata/pg_hba.conf
PGDATA=/u01/app/pgdata
PGHOME=/u01/app/pgsql
SCRIPTS_DIR=/u01/app/scripts
LOGPATH/u01/app/pgdata/log
PORT=5432
PASSWD=123456
####配置yum源，这里配置的是阿里的centos7的yum源
####安装依赖
####创建postgres用户
uid=1001(postgres) gid=1001(postgres) groups=1001(postgres)
####添加postgres的sudo权限
#####创建目录
#####解压并授权
####编译安装
####配置postgres用户的环境变量
####配置.pgpass文件
####初始化数据库
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
初始化成功
####配置PG的postgresql.conf参数
####配置PG的pg_hba.conf参数
####配置system-v启动脚本
####判断是否启动成功
启动成功
####切换归档日志
 pg_switch_wal 
---------------
 0/17D1CA0
(1 row)

####创建备份用户
CREATE ROLE
ALTER ROLE
GRANT
ALTER DEFAULT PRIVILEGES
####添加备份任务
####测试备份
20240915: backup successful
####配置crontab定时任务：每日凌晨1点进行物理备份，保留7天备份文件

3. 具体脚本

#!/bin/bash
#-------------------------------------------------------------------+
# 使用说明：                                                        |
#   需要将下载的源码包放置在/opt/sws目录下,如果目录不存在请自己创建 |
# 注意事项：                                                        |
#   该脚本在使用过程中会删除脚本中提到的目录，然后重建。            |        
#                                                                   |
#-------------------------------------------------------------------+

echo "####清理环境"
systemctl -q stop postgres.service
systemctl -q disable postgres.service 
pkill -9 postgres  


echo "####关闭防火墙"
systemctl -q stop firewalld  
systemctl -q disable firewalld 


SWDIR=/opt/sws

echo "####关闭Selinux"
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
setenforce 0 
systemctl daemon-reload
systemctl restart systemd-logind


echo "####设置PG环境变量"
PGBASE=/u01/app
FILE_CONF=/u01/app/pgdata/postgresql.conf
HBA_CONF=/u01/app/pgdata/pg_hba.conf
PGDATA=/u01/app/pgdata
PGHOME=/u01/app/pgsql
SCRIPTS_DIR=/u01/app/scripts
LOGPATH=/u01/app/pgdata/log
PORT=5432
PASSWD="123456"
echo "PGBASE=$PGBASE"
echo "FILE_CONF=$FILE_CONF"
echo "HBA_CONF=$HBA_CONF"
echo "PGDATA=$PGDATA"
echo "PGHOME=$PGHOME"
echo "SCRIPTS_DIR=$SCRIPTS_DIR"
echo "LOGPATH$LOGPATH"
echo "PORT=$PORT"
echo "PASSWD=$PASSWD"

echo "####配置yum源，这里配置的是阿里的centos7的yum源"

#####https://mirrors.aliyun.com/centos-vault/7.9.2009/os/Source/repodata
#####https://mirrors.aliyun.com/centos-vault/RPM-GPG-KEY-CentOS-7

minorver=7.9.2009
basearch=x86_64
cd /etc/yum.repos.d/
rm -rf ./*
cat >> /etc/yum.repos.d/ali-centos7.repo <<-EOF
[base]
name=centos-vault-$minorver - Base - mirrors.aliyun.com
failovermethod=priority
baseurl=http://mirrors.aliyun.com/centos-vault/$minorver/os/$basearch/
        http://mirrors.aliyuncs.com/centos-vault/$minorver/os/$basearch/
        http://mirrors.cloud.aliyuncs.com/centos-vault/$minorver/os/$basearch/
gpgcheck=1
gpgkey=https://mirrors.aliyun.com/centos-vault/RPM-GPG-KEY-CentOS-7

#released updates 
[updates]
name=centos-vault-$minorver - Updates - mirrors.aliyun.com
failovermethod=priority
baseurl=http://mirrors.aliyun.com/centos-vault/$minorver/updates/$basearch/
        http://mirrors.aliyuncs.com/centos-vault/$minorver/updates/$basearch/
        http://mirrors.cloud.aliyuncs.com/centos-vault/$minorver/updates/$basearch/
gpgcheck=1
gpgkey=https://mirrors.aliyun.com/centos-vault/RPM-GPG-KEY-CentOS-7
 
#additional packages that may be useful
[extras]
name=centos-vault-$minorver - Extras - mirrors.aliyun.com
failovermethod=priority
baseurl=http://mirrors.aliyun.com/centos-vault/$minorver/extras/$basearch/
        http://mirrors.aliyuncs.com/centos-vault/$minorver/extras/$basearch/
        http://mirrors.cloud.aliyuncs.com/centos-vault/$minorver/extras/$basearch/
gpgcheck=1
gpgkey=https://mirrors.aliyun.com/centos-vault/RPM-GPG-KEY-CentOS-7
 
#additional packages that extend functionality of existing packages
[centosplus]
name=centos-vault-$minorver - Plus - mirrors.aliyun.com
failovermethod=priority
baseurl=http://mirrors.aliyun.com/centos-vault/$minorver/centosplus/$basearch/
        http://mirrors.aliyuncs.com/centos-vault/$minorver/centosplus/$basearch/
        http://mirrors.cloud.aliyuncs.com/centos-vault/$minorver/centosplus/$basearch/
gpgcheck=1
enabled=0
gpgkey=https://mirrors.aliyun.com/centos-vault/RPM-GPG-KEY-CentOS-7
 
#contrib - packages by Centos Users
[contrib]
name=centos-vault-$minorver - Contrib - mirrors.aliyun.com
failovermethod=priority
baseurl=http://mirrors.aliyun.com/centos-vault/$minorver/contrib/$basearch/
        http://mirrors.aliyuncs.com/centos-vault/$minorver/contrib/$basearch/
        http://mirrors.cloud.aliyuncs.com/centos-vault/$minorver/contrib/$basearch/
gpgcheck=1
enabled=0
gpgkey=https://mirrors.aliyun.com/centos-vault/RPM-GPG-KEY-CentOS-7
EOF

yum clean all  >/tmp/yum.log
yum makecache >>/tmp/yum.log

echo "####安装依赖"
yum -y install zlib zlib-devel   >>/tmp/pg_dep_install.log
yum -y install readline readline-devel  >>/tmp/pg_dep_install.log
yum -y install openssl openssl-devel >>/tmp/pg_dep_install.log
yum -y install libxml2 libxml2-devel >>/tmp/pg_dep_install.log
yum -y install ibxslt libxslt-devel  >>/tmp/pg_dep_install.log
yum -y install libaio cmake make gcc gcc-c++  bison  >>/tmp/pg_dep_install.log
yum -y install python python-devel >>/tmp/pg_dep_install.log
yum -y install perl perl-devel >>/tmp/pg_dep_install.log

yum -y install flex libyaml net-tools expect openssh-clients tcl ncurses-devel openldap pam systemtap-sdt-devel perl-ExtUtils-Embed  luuid-devel  >>/tmp/pg_dep_install.log


echo "####创建postgres用户"

userdel -r -f postgres  > /tmp/pg_create_user.log
groupadd postgres  >> /tmp/pg_create_user.log
useradd -g postgres postgres   >> /tmp/pg_create_user.log
echo "$PASSWD"|passwd --stdin postgres   >> /tmp/pg_create_user.log
echo $(id postgres)


echo "####添加postgres的sudo权限"
echo "postgres	ALL=(ALL) 	ALL" >>/etc/sudoers


echo "#####创建目录"
rm -rf $PGBASE
mkdir -p $PGBASE/{pgdata,pg_archive,pg_backup,scripts,tmp}


echo "#####解压并授权"
cd $SWDIR
rm -rf /opt/tmp
mkdir /opt/tmp

tar -zxf postgresql*.tar.gz -C /opt/tmp
mv /opt/tmp/postgresql-* /opt/tmp/postgresql
chown -R postgres:postgres /opt/tmp/postgresql
chmod -R 755 /opt/tmp/postgresql

echo "####编译安装"
cd /opt/tmp/postgresql
./configure --prefix=$PGHOME --with-pgport=$PORT --with-openssl --with-python --with-blocksize=32 --with-readline >/tmp/pg_configure.log
#./configure --prefix=/u01/app/pgsql --with-pgport=5432 --with-openssl --with-python --with-blocksize=32 --with-readline

make > /tmp/pg_install.log 2>&1
make install  >> /tmp/pg_install.log 2>&1


echo "####配置postgres用户的环境变量"
cd /home/postgres
rm -rf .bash_profile
echo "#add by postgres" >> .bash_profile
echo "export PGHOME=$PGHOME" >> .bash_profile
echo "export PGDATA=$PGDATA" >> .bash_profile
echo "export PGPORT=5432" >> .bash_profile
echo "export PGPASSWORD=123456" >> .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 SCRIPTS_DIR=$SCRIPTS_DIR' >> .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


echo "####配置.pgpass文件"
su - postgres -c 'echo "$PASSWD">> .pgpass'
su - postgres -c "chmod 0600 /home/postgres/.pgpass"


echo "####初始化数据库"
chown -R postgres.postgres $PGBASE
chmod -R 755 $PGBASE
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 --auth-local=trust"  > /tmp/pg_initdb.log

if [ $? == 0 ]
then
echo "初始化成功"
chown -R postgres:postgres $PGBASE
chmod -R 755 $PGBASE
chmod -R 700 $PGDATA
else
echo "初始化失败，请检查"
fi

echo "####配置PG的postgresql.conf参数"
su - postgres -c "mv $FILE_CONF $PGDATA/postgresql.confbak"
echo "listen_addresses = '*'" >>$FILE_CONF
echo "port = $PORT" >>$FILE_CONF
echo "max_connections = 1000" >>$FILE_CONF
echo "work_mem = 128MB" >>$FILE_CONF
echo "superuser_reserved_connections=10" >>$FILE_CONF
echo "temp_buffers = 256MB" >>$FILE_CONF
echo "data_directory = '$PGDATA' " >>$FILE_CONF
echo "hba_file ='$HBA_CONF' " >>$FILE_CONF
echo "password_encryption = md5" >>$FILE_CONF
echo "shared_buffers = 1024MB" >>$FILE_CONF
echo "wal_level = replica" >>$FILE_CONF
echo "min_wal_size = 200MB" >>$FILE_CONF
echo "max_wal_size = 1GB" >>$FILE_CONF
echo "log_directory = 'log'" >>$FILE_CONF
echo "log_destination = 'csvlog'" >>$FILE_CONF
echo "logging_collector = on" >>$FILE_CONF
echo "log_filename = 'postgresql-%Y-%m-%d.log'" >>$FILE_CONF
echo "log_rotation_age = 1d " >>$FILE_CONF
echo "archive_mode = on " >>$FILE_CONF
#echo "archive_command = ''" >>$FILE_CONF
echo "autovacuum = on " >>$FILE_CONF
echo "autovacuum_max_workers = 4 " >>$FILE_CONF

chown  postgres:postgres $PGDATA/postgresql.confbak
chown  postgres:postgres $FILE_CONF


echo "####配置PG的pg_hba.conf参数"
su - postgres -c "cp $HBA_CONF $PGDATA/pg_hba.confbak"
echo "host all all 0.0.0.0/0 md5" >> $HBA_CONF

chown postgres:postgres $HBA_CONF

echo "####配置system-v启动脚本"
cat > /usr/lib/systemd/system/postgresql.service << "EOF"
[Unit]
Description=PostgreSQL Database Server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5432
Environment=PGDATA=/u01/app/pgdata
OOMScoreAdjust=-1000
ExecStart=/u01/app/pgsql/bin/pg_ctl start -D ${PGDATA}
ExecStop=/u01/app/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/u01/app/pgsql/bin/pg_ctl reload -D ${PGDATA} -s
TimeoutSec=300
[Install]
WantedBy=multi-user.target
EOF

chmod +x /usr/lib/systemd/system/postgresql.service
systemctl daemon-reload
systemctl start postgresql.service
systemctl enable postgresql.service

echo "####判断是否启动成功"
process=$(ps -ef | grep -v 'grep'| grep 'postgres'|awk '{print $2}'|wc -l)
if [ "$process" -gt 5 ];then 
echo "启动成功"
else
echo "启动失败，请检查"
fi


echo "####切换归档日志"
su - postgres -c "$PGHOME/bin/psql -d postgres  -p$PORT -c \"select pg_switch_wal();\""

echo "####创建备份用户"
su - postgres -c "$PGHOME/bin/psql -d postgres  -p5432 -c \"create user backup with encrypted password 'backup';\""
su - postgres -c "$PGHOME/bin/psql -d postgres  -p5432 -c \"alter user backup set default_transaction_read_only=on;\""
su - postgres -c "$PGHOME/bin/psql -d postgres  -p5432 -c \"grant select on all tables in schema public to backup;\""
su - postgres -c "$PGHOME/bin/psql -d postgres  -p5432 -c \"alter default privileges in schema public grant select on tables to backup;\""

echo "####添加备份任务"
cat > $SCRIPTS_DIR/pg_basebackup.sh << "EOF"
#!/bin/bash
#开始数据库物理备份
PG_BASE=/u01/app
PG_HOST="127.0.0.1"
PG_PORT="5432"
PG_USER="postgres"
PG_PASSWD="123456"
DT="`date +%Y%m%d`"
DIR_BASEBACKUP=${PG_BASE}/pgsql/bin/
DIR_BACKUP="${PG_BASE}/pg_backup/backup_$DT"
FILE_LOG="${PG_BASE}/pg_backup/backup_$DT.log"

#备份数据库
$DIR_BASEBACKUP/pg_basebackup -D ${DIR_BACKUP} -U postgres -R -w -Ft -z -Z 5  > ${FILE_LOG}

if [ $? == 0 ]
then
echo "$DT: backup successful"
else
echo "$DT: backup failed"
fi
#完成数据库物理备份

#删除老旧备份（7天之前的备份）
find ${PG_BASE}/pg_backup -maxdepth 1 -type d -mtime +7 -name "backup_*" -exec rm -rf {} \;

EOF


echo "####测试备份"
chmod +x $SCRIPTS_DIR/pg_basebackup.sh
su - postgres -c "$SCRIPTS_DIR/pg_basebackup.sh"
rm -rf $PGBASE/pg_backup/*



echo "####配置crontab定时任务：每日凌晨1点进行物理备份，保留7天备份文件"
cat >> /var/spool/cron/postgres << "EOF"
0 1 * * * /bin/sh $SCRIPTS_DIR/pg_basebackup.sh >> /tmp/pg_backup.log
EOF