postgresql14编译安装脚本

#!/bin/bash

####################################readme###################################
#先上传postgresql源码包，再配置yum源，然后执行脚本
#备份官方yum源配置文件：
#cp /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.bak
#下载阿里云yum源配置文件：
#curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
#清除缓存生产新的缓存
#yum clean all
#yum makecache
#############################################################################

set -euo pipefail  # 启用严格错误检查

#============================= 全局配置 =============================#
export MY_SERVER_IP="172.16.1.11"       # 本机服务器IP
export MY_HOSTNAME="testdb"        # 主机名
export MY_SOFT_BASE="/opt"               # 软件包存储根目录
export PG_SOFT="postgresql-14.16.tar.gz" # PG源码包名称
export PG_VERSION="14.16"                # PG版本号
export MY_PG_HOME="/pgccc"               # PG根目录
export PGDATA="${MY_PG_HOME}/pgdata"     # 数据目录
export PGHOME="${MY_PG_HOME}/pgsql"      # 安装目录
export LOG_FILE="/var/log/pg_install.log" # 安装日志文件

#============================= 函数定义 =============================#
init_logging() {
  exec > >(tee -a "$LOG_FILE") 2>&1
  echo "[$(date '+%F %T')] 开始执行PostgreSQL安装脚本"
}

validate_environment() {
  echo "验证安装包..."
  local pg_soft_path="${MY_SOFT_BASE}/${PG_SOFT}"
  [[ -f "$pg_soft_path" ]] || { echo "错误：未找到PostgreSQL源码包 $pg_soft_path"; exit 1; }
}

install_dependencies() {
  echo "安装系统依赖..."
  local deps=(
    openssl openssl-devel pam pam-devel libxml2 libxml2-devel
    libxslt libxslt-devel perl perl-devel python-devel perl-ExtUtils-Embed
    readline readline-devel bzip2 zlib zlib-devel ntp ntpdate
    gettext gettext-devel bison flex gcc gcc-c++ boost-devel
    gmp-devel mpfr-devel libevent-devel libpython3.6m
  )
  yum install -y "${deps[@]}" || { echo "依赖安装失败"; exit 1; }
}

security_settings() {
  # 关闭防火墙
systemctl stop firewalld.service  >/dev/null 2>&1
systemctl disable firewalld.service  >/dev/null 2>&1 


  # SELinux配置
  if [[ $(getenforce) != "Disabled" ]]; then
    sed -i 's/^SELINUX=.*/SELINUX=permissive/' /etc/selinux/config
    setenforce 0
    echo "SELinux已设置为宽松模式"
  fi
}


create_pg_user() {
  if ! id postgres &>/dev/null; then
    groupadd -g 70000 postgres
    useradd -u 70000 -g postgres -m -s /bin/bash postgres
    echo "postgres:postgres" | chpasswd
    echo "已创建postgres用户"
  fi
}

setup_directories() {
  local dirs=("$MY_PG_HOME"/{pgdata,archive,scripts,backup,pgsql,soft})
  mkdir -p "${dirs[@]}"
  chown -R postgres:postgres "$MY_PG_HOME"
  chmod 2750 "$MY_PG_HOME"  # 设置SGID位保持组权限
  echo "目录结构已创建"
}

configure_env() {
  local profile="/home/postgres/.bashrc"
  cat > "$profile" <<EOF
export LANG=en_US.UTF-8
export PGPORT=5432
export PGDATA=$PGDATA
export PGHOME=$PGHOME
export PATH=\$PGHOME/bin:\$PATH
export PGUSER=postgres
export PGDATABASE=postgres
PS1='[\u@\h \W]\$ '
EOF
  chown postgres:postgres "$profile"
}

compile_pg() {
  local src_dir="${MY_PG_HOME}/soft/postgresql-${PG_VERSION}"
  local build_dir="${MY_PG_HOME}/build"

  # 解压源码
  tar -zxvf "${MY_SOFT_BASE}/${PG_SOFT}" -C "${MY_PG_HOME}/soft"

  # 编译安装
  mkdir -p "$build_dir"
  chown postgres:postgres "$build_dir"
  
  su - postgres -c "
    cd '$build_dir' &&
    '${src_dir}/configure' \
      --prefix='$PGHOME' \
      --with-openssl \
      --with-libxml \
      --with-libxslt &&
    make -j$(nproc) &&
    make install
  " || { echo "编译安装失败"; exit 1; }
}

init_db() {
  su - postgres -c "
    initdb --encoding=UTF8 --locale=en_US.utf8 --username=postgres --pwfile=<(echo 'postgres')
  " || { echo "数据库初始化失败"; exit 1; }

  # 配置参数
  cat >> "$PGDATA/postgresql.conf" <<EOF
listen_addresses = '*'
port = 5432
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
shared_preload_libraries = 'pg_stat_statements'
max_connections = 100
shared_buffers = 128MB
work_mem = 4MB
EOF

  # 配置访问控制
  cat > "$PGDATA/pg_hba.conf" <<EOF
local   all             all                     trust
host    all             all     127.0.0.1/32    trust
host    all             all     $MY_SERVER_IP/32 md5
host    replication     all     0.0.0.0/0       md5
EOF
}

start_service() {
  cat > /etc/systemd/system/postgresql.service <<EOF
[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=notify
User=postgres
ExecStart=$PGHOME/bin/postgres -D $PGDATA
ExecReload=/bin/kill -HUP \$MAINPID
KillMode=mixed

[Install]
WantedBy=multi-user.target
EOF

  systemctl daemon-reload
  systemctl enable postgresql --now
}

#============================= 主执行流程 =============================#
main() {
  init_logging
  validate_environment
  install_dependencies

  create_pg_user
  setup_directories
  configure_env
  compile_pg
  init_db
  start_service

  echo "安装完成！数据库已启动"
  echo "连接命令：psql -h $MY_SERVER_IP -U postgres"
}

main "$@"

遇到问题：

编译安装完成后，启动时报错

pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2024-03-28 10:24:59.830 CST [14206] FATAL: could not access file "pg_stat_statements": No such file or directory
2024-03-28 10:24:59.830 CST [14206] LOG: database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.

问题处理：

--进入到contrib目录中，再进入到安装包pg_stat_statements目录

cd  contrib/pg_stat_statements/

--直接进行编译安装报错

[pgsql@test:/opt/postgresql-14.16/contrib/pg_stat_statements]$ make && make install

Makefile:33: /contrib/contrib-global.mk: No such file or directory
make: *** No rule to make target '/contrib/contrib-global.mk'. Stop.

--参考官网后执行以下命令编译安装

[pgsql@test:/opt/postgresql-14.16/contrib/pg_stat_statements]$ make USE_PGXS=1&& make USE_PGXS=1 install

--编译安装完成后再次启动数据库

pg_ctl start

waiting for server to start....2024-03-28 10:43:57.642 CST [14578] LOG: redirecting log output to logging collector process
2024-03-28 10:43:57.642 CST [14578] HINT: Future log output will appear in directory "log".
 done
server started

--创建插件

[pgsql@test:/home/postgres]$psql -h127.0.0.1 -Upostgres
psql (15.0)
Type "help" for help.
postgres=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=# alter system set shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM

--查询结果成功

postgres=# select count(*) from pg_stat_statements;
 count 
-------
   2
(1 row)