CentOS应用-PostgreSQL14数据库

Jungout

1123人浏览 · 2023-07-27 09:56:08

Jungout · 2023-07-27 09:56:08 发布

1.PostgreSQL安装说明

本文章将会讲解CentOS7(Minimal)部署PostgreSQL 14.8数据库
本次PostgreSQL 数据库配置参考：PostgreSQL: Documentation

数据库安装步骤分为：

安装编译的依赖环境
编译和构建pgsql
数据库初始化
数据库配置文件
添加TimeScaleDB插件

2.PostgreSQL部署过程

默认情况下会自动生成my.cnf文件，数据库会按照my.cnf配置进行初始化，可以通过修改my.cnf配置内容实现MySQL安装路径配置。

2.1 安装编译的依赖环境

最小化部署的centos7缺少应用编译的环境，编译安装可定制性高，可通过优化编译参数和选项来提升性能，重要的是更深入地了解应用程序的构建过程和原理，增加对操作系统和应用的理解和学习。

部分国内的依赖程序的yum源安装不太稳定，需要yum安装多次安装或者使用rpm包单独安装。

#1.安装编译依赖环境
yum install -y gcc gcc-c++ \
openssl openssl-devel zlib zlib-devel\
epel-release clang \ 
libicu-devel perl-ExtUtils-Embed \
readline readline-devel \
pam-devel libxml2-devel libxslt-devel \
openldap-devel systemd-devel \
tcl-devel python-devel \
llvm5.0 llvm5.0-devel

2.2 编译和构建pgsql

#1.在/opt下创建apps和src目录存放应用和安装包
mkdir -p /opt/{apps,src}

#2.下载pgsql14安装包至/opt/src目录下
wget https://ftp.postgresql.org/pub/source/v14.8/postgresql-14.8.tar.gz --no-check-certificate -P /opt/src

#3.解压pgsql14安装包
cd /opt/src && tar -xzf postgresql-14.8.tar.gz

#4.修改构建文件的全局配置
vim /opt/src/postgresql-14.8/src/Makefile.global.in
#修改以下行：
    COMPILE.c.bc = $(CLANG) -Wno-ignored-attributes $(BITCODE_CFLAGS) $(CPPFLAGS) -flto=thin -emit-llvm -c
#修改为：
    COMPILE.c.bc = $(CLANG) -Wno-ignored-attributes $(BITCODE_CFLAGS) $(CPPFLAGS) -emit-llvm -c
    
#5.编译postgresql
cd /opt/src/postgresql-14.8
./configure --prefix=/opt/apps/postgresql-14 \
--enable-nls --with-perl --with-python \
--with-tcl --with-gssapi \
--with-llvm LLVM_CONFIG='/usr/lib64/llvm5.0/bin/llvm-config' \
--with-icu --with-openssl --with-pam \
--with-ldap --with-systemd \
--with-libxml --with-libxslt

#6.构建和安装postgresql
make -j4 && make install

#7.pgsql安装目录下建立软链接来管理应用的版本
cd /opt/apps && ln -s postgresql-14 postgresql

2.3 数据库初始化

#1.创建postgres用户
groupadd postgres && useradd -g postgres postgres

#2.设置postgres用户密码
echo '1234,abcd' | passwd --stdin postgres

#3.写入postgres程序的系统变量
cat > /etc/profile.d/pgsql.sh << EOF
### postgres ###
export PGUSER=postgres
export PGPORT=8432
export PGHOME=/opt/apps/postgresql
export PGDATA=\${PGHOME}/data
export PGLOG=\${PGHOME}/var/logs/pgsql.log
export PATH=\${PGHOME}/bin:\$PATH
export LD_LIBRARY_PATH=\${PGHOME}/lib:\$LD_LIBRARY_PATH
EOF

#4.重新加载系统的环境变量
source /etc/profile

#5.测试环境变量是否生效，查看pgsql版本
pg_config | tail -n 1

#6.创建pgsql数据和日志保存目录
mkdir -p ${PGHOME}/{data,var/logs}

#7.授权postgres用户pgsql安装目录
chown -R postgres:postgres /opt/apps/postgresql
chown -R postgres:postgres /opt/apps/postgresql-14

#8.切换至postgres用户进行数据库初始化
su - postgres

#9.初始化数据库
initdb -D $PGDATA -U postgres --locale=en_US.UTF8 -E UTF8
#Success. You can now start the database server using:
#    pg_ctl -D /opt/apps/postgresql/data -l logfile start

2.4 数据库配置文件

#1.修改pgsql的基础设置
vi /opt/apps/postgresql/data/postgresql.conf

#修改内容1：修改pgsql监听地址和端口
# - Connection Settings -
listen_addresses = '*'                  # what IP address(es) to listen on;
port = 8432                             # (change requires restart)
max_connections = 100                   # (change requires restart)

# 2.修改pgsql的客户端认证方式
vi /opt/apps/postgresql/data/pg_hba.conf

#增加内容1：增加一行允许所有的IPv4客户端连接（md5）
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5

# 3.启动数据库/停止数据库
pg_ctl start -D $PGDATA -s -l $PGLOG
pg_ctl stop -D $PGDATA -s -m fast

2.5 systemctl管理pgsql

cat <<EOF > /usr/lib/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=forking
User=postgres
Group=postgres

OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0
Environment=PGDATA=/opt/apps/postgresql/data
Environment=PGLOG=/opt/apps/postgresql/var/logs/pgsql.log

ExecStart=/opt/apps/postgresql/bin/pg_ctl start -D \${PGDATA} -s -l \${PGLOG}
ExecStop=/opt/apps/postgresql/bin/pg_ctl stop -D \${PGDATA} -s -m fast
ExecReload=/opt/apps/postgresql/bin/pg_ctl reload -D \${PGDATA} -s

TimeoutSec=300
KillMode=mixed

[Install]
WantedBy=multi-user.target
EOF

3. 添加TimeScaleDB插件

3.1 编译安装cmake

TimeScaleDB的编译需要使用cmake工具，CentOS7.9国内yum源提供的版本为2.8，不满足TimeScaleDB的要求，因此我们从cmake官网获取源码进行编译安装：https://cmake.org/download/

#1.下载至cmake源码包/opt/src目录
#建议下载到本地上传到服务器上
https://github.com/Kitware/CMake/releases/download/v3.27.1/

#2.解压camke安装包
cd /opt/src
tar xf cmake-3.27.1.tar.gz

#3.编译和构建cmake
cd /opt/src/cmake-3.27.1
./configure --prefix=/usr/local/cmake
make -j4 && make install

# 4.创建系统bin目录下的cmake软链接
ln -s /usr/local/cmake/bin/cmake /usr/bin/cmake
ln -s /usr/local/cmake/bin/ccmake /usr/bin/ccmake
ln -s /usr/local/cmake/bin/cpack /usr/bin/cpack
ln -s /usr/local/cmake/bin/ctest /usr/bin/ctest

# 5.测试能否直接调用
cmake --version|head -n 1
> cmake version 3.21.3

3.2 编译安装TimeScaleDB

TimeScaleDB插件需从Github项目中下载：https://codeload.github.com/timescale/timescaledb/tar.gz/refs/tags/2.11.0

编译时注意postgresql和timescaledb的版本兼容问题，如timescaledb2.1版版本只能够支持PostgreSQL 11, 12 and 13。
编译安装成功后timescaledb插件模块会存放于postgresql的lib目录下，通过修改pgsql的配置文件开启模块支持。

#1.下载至TimeScaleDB源码包/opt/src目录
#建议下载到本地上传到服务器
https://codeload.github.com/timescale/timescaledb/tar.gz/refs/tags/2.11.0

#2.解压TimeScaleDB安装包
cd /opt/src
tar xf timescaledb-2.11.0.tar.gz 

#3.编译检查配置项
cd /opt/src/timescaledb-2.11.0
./bootstrap -DREGRESS_CHECKS=OFF

#4.构建TimeScaleDB
cd ./build
make -j8 && make install

3.3 修改pgsql的配置文件

postgresql.conf配置是会在第一次启动pgsql服务时生成在pgsql的data目录下的。

# 1.添加TimeScaleDB共享库到pgsql的配置文件
su - postgres
sed -i "s/#shared_preload.*/shared_preload_libraries = 'timescaledb'/g" /opt/apps/postgresql-14/data/postgresql.conf

# 2.重启postgresql
systemctl restart postgresql.service

3.4 PGsql加载TimeScaleDB

#1.登录postgresql数据库
[root@localhost]# psql
#psql (14.8)
#Type "help" for help.

#2.添加timescaledb插件模块
postgres=# create extension timescaledb;
WARNING:  
WELCOME TO
 _____ _                               _     ____________  
|_   _(_)                             | |    |  _  \ ___ \ 
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ / 
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ 
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 2.11.0
For more information on TimescaleDB, please visit the following links:

 1. Getting started: https://docs.timescale.com/timescaledb/latest/getting-started
 2. API reference documentation: https://docs.timescale.com/api/latest
 3. How TimescaleDB is designed: https://docs.timescale.com/timescaledb/latest/overview/core-concepts

Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescale.com/timescaledb/latest/how-to-guides/configuration/telemetry.

CREATE EXTENSION
postgres=#

3.5 验证TimeScaleDB

#1.Centos7防火墙上允许访问8432端口
firewall-cmd --zone=public --add-port=8432/tcp --permanent 
firewall-cmd --reload

#2.登录pgsql修改当前用户密码
psql
ALTER USER postgres WITH PASSWORD '1234,abcd';