腾讯云开发者社区

腾讯云开发者社区 通过脚本自动部署PostgreSQL数据库

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

说明该脚本部署的pg数据库为源码通过编译安装的方式执行过程登录后复制[root@olinux73_model ~]# chmod +x ./install.sh[root@olinux73_model ~]# ./install.sh####清理环境####关闭防火墙####关闭Selinuxsetenfo...

egzosn

1016人浏览 · 2024-09-15 12:10:11
egzosn  ·  2024-09-15 12:10:11 发布
  1. 说明

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

  1. 执行过程
[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天备份文件
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  1. 具体脚本
#!/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
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.
  • 61.
  • 62.
  • 63.
  • 64.
  • 65.
  • 66.
  • 67.
  • 68.
  • 69.
  • 70.
  • 71.
  • 72.
  • 73.
  • 74.
  • 75.
  • 76.
  • 77.
  • 78.
  • 79.
  • 80.
  • 81.
  • 82.
  • 83.
  • 84.
  • 85.
  • 86.
  • 87.
  • 88.
  • 89.
  • 90.
  • 91.
  • 92.
  • 93.
  • 94.
  • 95.
  • 96.
  • 97.
  • 98.
  • 99.
  • 100.
  • 101.
  • 102.
  • 103.
  • 104.
  • 105.
  • 106.
  • 107.
  • 108.
  • 109.
  • 110.
  • 111.
  • 112.
  • 113.
  • 114.
  • 115.
  • 116.
  • 117.
  • 118.
  • 119.
  • 120.
  • 121.
  • 122.
  • 123.
  • 124.
  • 125.
  • 126.
  • 127.
  • 128.
  • 129.
  • 130.
  • 131.
  • 132.
  • 133.
  • 134.
  • 135.
  • 136.
  • 137.
  • 138.
  • 139.
  • 140.
  • 141.
  • 142.
  • 143.
  • 144.
  • 145.
  • 146.
  • 147.
  • 148.
  • 149.
  • 150.
  • 151.
  • 152.
  • 153.
  • 154.
  • 155.
  • 156.
  • 157.
  • 158.
  • 159.
  • 160.
  • 161.
  • 162.
  • 163.
  • 164.
  • 165.
  • 166.
  • 167.
  • 168.
  • 169.
  • 170.
  • 171.
  • 172.
  • 173.
  • 174.
  • 175.
  • 176.
  • 177.
  • 178.
  • 179.
  • 180.
  • 181.
  • 182.
  • 183.
  • 184.
  • 185.
  • 186.
  • 187.
  • 188.
  • 189.
  • 190.
  • 191.
  • 192.
  • 193.
  • 194.
  • 195.
  • 196.
  • 197.
  • 198.
  • 199.
  • 200.
  • 201.
  • 202.
  • 203.
  • 204.
  • 205.
  • 206.
  • 207.
  • 208.
  • 209.
  • 210.
  • 211.
  • 212.
  • 213.
  • 214.
  • 215.
  • 216.
  • 217.
  • 218.
  • 219.
  • 220.
  • 221.
  • 222.
  • 223.
  • 224.
  • 225.
  • 226.
  • 227.
  • 228.
  • 229.
  • 230.
  • 231.
  • 232.
  • 233.
  • 234.
  • 235.
  • 236.
  • 237.
  • 238.
  • 239.
  • 240.
  • 241.
  • 242.
  • 243.
  • 244.
  • 245.
  • 246.
  • 247.
  • 248.
  • 249.
  • 250.
  • 251.
  • 252.
  • 253.
  • 254.
  • 255.
  • 256.
  • 257.
  • 258.
  • 259.
  • 260.
  • 261.
  • 262.
  • 263.
  • 264.
  • 265.
  • 266.
  • 267.
  • 268.
  • 269.
  • 270.
  • 271.
  • 272.
  • 273.
  • 274.
  • 275.
  • 276.
  • 277.
  • 278.
  • 279.
  • 280.
  • 281.
  • 282.
  • 283.
  • 284.
  • 285.
  • 286.
  • 287.
  • 288.
  • 289.
  • 290.
  • 291.
  • 292.
  • 293.
  • 294.
  • 295.
  • 296.
  • 297.
  • 298.
  • 299.
  • 300.
  • 301.
  • 302.
  • 303.
  • 304.
  • 305.
  • 306.
  • 307.
  • 308.
  • 309.
  • 310.
  • 311.
  • 312.
  • 313.
  • 314.
  • 315.
  • 316.
  • 317.
  • 318.
  • 319.
  • 320.
  • 321.
  • 322.
# 数据库 # postgresql
Logo
腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

Springboot+MySQL 实现从数据库获取数据展示到前端

1.运行效果效果图如下：2.创建数据库表数据库名为"springboot",数据库表名为"user"，数据库表里面放了5条数据，字段分别为：“id”，“name”，“age”，“email”。3.新建SpringBoot项目1、选择Spring Initial...

avatar 腾讯云开发者社区
cover

腾讯云建官网：一个完整的自助建站的过程，不用代码，2小时就可以制作自己的网站

avatar 腾讯云开发者社区

数据库 实验二 数据库的简单查询和连接查询实验

– l）用Transact-SQL语句表示下列操作．在学生选课库中实现其数据查询操作．– ①求数学系学生的学号和姓名。select sno,sname from student where sdept='MA';– ②求选修了课程的学生学号。select distinct sno from sc;– ③求选修C1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。s

avatar 腾讯云开发者社区
  • 浏览量 1016
  • 收藏 0
  • 0

所有评论(0)

查看更多评论 

egzosn

@cnzzs
已为社区贡献3条内容