PostgreSQL14.10+TimeScaleDB2.13.1数据库部署

#在/usr/local/src/下目录存放安装包
cd /usr/local/src/
# 解压pgsql14安装包
tar -xzf postgresql-14.10.tar.gz
# 编译postgresql
cd postgresql-14.10/
./configure --prefix=/AppHome/postgresql-14

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

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

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

#设置postgres用户密码
echo '123456' | passwd --stdin postgres

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

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

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

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

#授权postgres用户pgsql安装目录
chown -R postgres:postgres /AppHome/postgresql
chown -R postgres:postgres /AppHome/postgresql-14

#切换至postgres用户进行数据库初始化
su - postgres
#初始化数据库
/AppHome/postgresql/bin/initdb -D $PGDATA -U postgres --locale=en_US.UTF8 -E UTF8

#修改pgsql的基础设置
vim /AppHome/postgresql/data/postgresql.conf

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

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

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

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

#进入postgresql源码包的解压目录
cd /usr/local/src/postgresql-14.10/contrib/start-scripts/
#复制
cp ./linux /etc/init.d/postgresql

#然后进行配置修改
vim /etc/init.d/postgresql
#如下:
prefix=/AppHome/postgresql
PGDATA="/AppHome/postgresql/data"
PGUSER=postgres
PGLOG="/AppHome/postgresql/logs/pgsql.log"
# PGLOG="$PGDATA/serverlog"

#授权
chmod +x /etc/init.d/postgresql

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=/AppHome/postgresql/data
Environment=PGLOG=/AppHome/postgresql/logs/pgsql.log

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

TimeoutSec=300
KillMode=mixed

[Install]
WantedBy=multi-user.target
EOF

# 解压二进制发行版文件
cd /usr/local/src/
tar -zxvf cmake-3.28.1-linux-x86_64.tar.gz
cd cmake-3.28.1-linux-x86_64/bin
#测试能否直接调用
./cmake --version|head -n 1

# 替换cmame
mv /usr/bin/cmake /usr/bin/cmake_2.8
ln -s /usr/local/src/cmake-3.28.1-linux-x86_64/bin/cmake /usr/bin/

# 解压TimeScaleDB安装包
cd /usr/local/src/
tar -zxvf timescaledb-2.13.1.tar.gz

# 编译检查配置项
cd /opt/src/timescaledb-2.11.0
#./bootstrap -DREGRESS_CHECKS=OFF #提示openssl缺失执行下面
./bootstrap -DPG_CONFIG=/AppHome/postgresql/bin/pg_config -DUSE_OPENSSL=0

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

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

# 2.重启postgresql
/etc/init.d/postgresql restart

# 登录数据库
psql -U postgres -d postgres

# 添加timescaledb插件模块
postgres=# create extension timescaledb;
WARNING:  
WELCOME TO
 _____ _                               _     ____________  
|_   _(_)                             | |    |  _  \ ___ \ 
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ / 
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ 
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/

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=# 

# 登录pgsql修改当前用户密码
psql
ALTER USER postgres WITH PASSWORD 'newpassword';

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

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

#3.编译和构建cmake
cd /opt/src/cmake-3.28.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.28.1