#!/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>&1echo "[$(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-devellibxslt libxslt-devel perl perl-devel python-devel perl-ExtUtils-Embedreadline readline-devel bzip2 zlib zlib-devel ntp ntpdategettext gettext-devel bison flex gcc gcc-c++ boost-develgmp-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" ]]; thensed -i 's/^SELINUX=.*/SELINUX=permissive/' /etc/selinux/configsetenforce 0echo "SELinux已設置為寬松模式"fi
}create_pg_user() {if ! id postgres &>/dev/null; thengroupadd -g 70000 postgresuseradd -u 70000 -g postgres -m -s /bin/bash postgresecho "postgres:postgres" | chpasswdecho "已創建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]\$ '
EOFchown 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
EOFsystemctl daemon-reloadsystemctl enable postgresql --now
}#============================= 主執行流程 =============================#
main() {init_loggingvalidate_environmentinstall_dependenciescreate_pg_usersetup_directoriesconfigure_envcompile_pginit_dbstart_serviceecho "安裝完成！數據庫已啟動"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 downstopped 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)