openEuler2403安装部署PostgreSQL17

openEuler2403安装部署PostgreSQL17

一、前言

1.简介

PostgreSQL 是一个基于 POSTGRES 4.2 版 的对象关系数据库管理系统 （ORDBMS）， 由加州大学伯克利分校计算机科学系开发。

2.环境

• LInux发行版： openEuler-24.03-LTS-SP2-x86_64-dvd.iso

• PostgreSQL 版本：PostgreSQL 17

PostgreSQL 官网：https://www.postgresql.org

PostgreSQL 官网文档：https://www.postgresql.org/docs/

PostgreSQL 下载：https://www.postgresql.org/download/linux/redhat/

PostgreSQL YUM 源仓库网址：https://download.postgresql.org/pub/repos/yum/

openEuler常用操作指令：https://blog.csdn.net/u011424614/article/details/150942929

openEuler中LVM调整实现home与root分区空间平衡：https://blog.csdn.net/u011424614/article/details/150961763

openEuler安装部署JDK11：https://blog.csdn.net/u011424614/article/details/150961633

openEuler2403安装部署MySQL8：https://blog.csdn.net/u011424614/article/details/150966094

VirtualBox安装openEuler24.03：https://blog.csdn.net/u011424614/article/details/150725588

VMware安装openEuler24.03：https://blog.csdn.net/u011424614/article/details/150723134

DBeaver使用记录：https://blog.csdn.net/u011424614/article/details/150364887

二、正文

1.准备工作

1）关闭selinux

• 修改 /etc/selinux/config

sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/config

2）防火墙

• 根据场景 2 选 1

# 场景一：测试环境
systemctl stop firewalld.service
systemctl disable firewalld.service# 场景二：生产环境，建议使用
firewall-cmd --zone=public --add-port=8081/tcp --permanent
firewall-cmd --reload
# 查看防火墙已开放端口
firewall-cmd --zone=public --list-ports

3）安装依赖软件包

dnf install bison tcl tcl-devel uuid-devel perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel libicu-devel openldap-devel python3-devel gcc-c++ openssl-devel cmake gcc* readline-devel docbook-dtds docbook-style-xsl openjade libxml2 libxml2-devel libxslt libxslt-devel xmlto fop perl-XML-SAX

4）创建系统用户

• 创建系统用户组和系统用户
• 不强制要求指定组ID和用户ID，默认情况下系统会自动分配未使用的最小ID（通常从1000开始）
• 显式指定组ID和用户ID，可以确保多节点集群中用户/组ID一致，避免文件共享或同步时权限问题

# 创建名为 postgres 的系统用户组，指定组ID
groupadd -g 10001 postgres
# 创建系统用户 postgres 并加入同名组，指定用户ID
useradd -u 10001 -g postgres postgres
# 修改用户密码
passwd postgres

5）创建目录

• 创建软件、数据和日志目录，及授权

# 创建软件、数据和日志目录
mkdir -p /opt/postgresql/{software,data,logs}
# 目录设置用户组和权限
chown postgres:postgres -R /opt/postgresql
chmod 700 -R /opt/postgresql/logs

• 编辑 /etc/profile

vim /etc/profile

末尾追加内容：

export PGHOME=/opt/postgresql
export PGDATA=/opt/postgresql/data
export PATH=$PATH:$PGHOME/bin
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

• 立即生效环境变量

source /etc/profile

2.下载和安装

• 选择版本：https://www.postgresql.org/ftp/source/

• 下载链接右击，可拷贝下载链接

cd /opt/postgresql/software# 下载压缩包，如果 wget 无法下载或下载慢，可使用浏览器或迅雷下载
wget https://ftp.postgresql.org/pub/source/v17.6/postgresql-17.6.tar.gz# 解压
tar -zxvf postgresql-17.6.tar.gz

• 编译和安装

# 预编译配置脚本
cd postgresql-17.6/
./configure --prefix=/opt/postgresql/ --with-openssl --with-pgport=5432 --with-tcl --with-perl --with-python --with-libxml --with-libxslt --with-ossp-uuid --with-pam --with-ldap
# 编译和安装PostgreSQL核心及所有附加模块（如扩展、文档、测试套件等）
# 适合需要完整功能的开发/生产环境
gmake world -j$(nproc)
gmake install-world

• 初始化数据库

# 切换系统用户
su - postgres# 默认创建数据库：postgres 和超级管理员：postgres ，该用户无密码
/opt/postgresql/bin/initdb -D /opt/postgresql/data --encoding=UTF8 --lc-collate=zh_CN.UTF-8 --lc-ctype=zh_CN.UTF-8

3.pg_ctl管理服务

• 建议使用 systemctl 服务管理

#启动
/opt/postgresql/bin/pg_ctl -D /opt/postgresql/data -l logfile start
#停止
/opt/postgresql/bin/pg_ctl -D /opt/postgresql/data -l logfile stop
#重启
/opt/postgresql/bin/pg_ctl -D /opt/postgresql/data -l logfile restart
#查看状态
/opt/postgresql/bin/pg_ctl -D /opt/postgresql/data -l logfile status

4.systemctl服务管理

• 创建 systemctl 脚本

cat > /usr/lib/systemd/system/postgresql.service <<EOF
[Unit]
Description=The PostgreSQL Database Server
After=syslog.target
After=network.target[Service]
Type=forking
User=postgres
Group=postgres
ExecStart=/opt/postgresql/bin/pg_ctl start -D /opt/postgresql/data
ExecStop=/opt/postgresql/bin/pg_ctl stop
ExecReload=/opt/postgresql/bin/pg_ctl reload -D /opt/postgresql/data
TimeoutSec=300[Install]
WantedBy=multi-user.target
EOF

• 常用指令

# 重新加载systemd单元文件
systemctl daemon-reload
# 系统重启自启动
systemctl enable postgresql
# 启动服务
systemctl start postgresql
# 停止服务
systemctl stop postgresql
# 重启服务
systemctl restart postgresql
# 查看服务状态
systemctl status postgresql

5.配置

• 编辑 postgresql.conf

vim /opt/postgresql/data/postgresql.conf

文件内容：

# 监听所有网络接口（生产环境建议结合pg_hba.conf 限制IP段）
listen_addresses = '*'
# 服务监听端口（修改默认端口可规避自动化攻击）
port = 5432      
# 最大客户端连接数（需配合shared_buffers和work_mem调整）
max_connections = 1000# 基础日志收集
logging_collector = on  # 必须开启日志收集器 
log_directory = '/opt/postgresql/logs'  # 独立日志目录（避免与数据目录混用）
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'   # 精确到秒的日志命名（便于溯源）
log_rotation_age = 1d  # 每日轮转
log_rotation_size = 1GB  # 单个日志不超过1GB（平衡存储与可读性）
# SQL监控与性能分析
log_statement = 'ddl'  # 记录所有表结构变更（审计DDL操作）
log_duration = on  # 记录SQL执行耗时 
log_min_duration_statement = 1000  # 记录超过1秒的慢查询（单位：毫秒）
log_lock_waits = on  # 记录锁等待超时（deadlock_timeout默认1秒）
# 安全与审计增强
log_connections = on  # 记录所有连接尝试（安全审计）
log_disconnections = on  # 记录连接断开（追踪异常会话）
log_line_prefix = '%m [%p] %u %d %r '  # 格式：时间戳 [进程ID] 用户名 数据库名 客户端IP:端口

• 编辑 pg_hba.conf

vim /opt/postgresql/data/pg_hba.conf

文件内容：

# 新增配置，允许所有远程主机连接
# 限制特定IP或网段，例如 0.0.0.0/0 改为：192.168.88.0/24
host    all             all             0.0.0.0/0               md5

• 修改配置后，重启 PostgreSQL 服务

systemctl restart postgresql

6.测试示例

• 创建数据库和用户

# 切换到 postgres 系统用户（PostgreSQL默认超级用户）
sudo -i -u postgres# 以 postgres 身份连接PostgreSQL默认数据库
psql postgres
# 备用，非必要执行，-U指定用户，-d指定数据库，-h指定服务器，-p指定端口
psql -U postgres -d postgres -h 127.0.0.1 -p 5432
# 修改 postgres 的用户密码
ALTER USER postgres WITH PASSWORD 'postgres';# 创建新用户并设置密码（生产环境建议使用更复杂的密码策略）
create user pguser with password 'pgsql@123456';# 创建UTF8编码的数据库，并指定 pguser 为所有者 
create database testdb with encoding='utf8' owner=pguser;# 授予 pguser 对 testdb 数据库的全部操作权限
grant all privileges on database testdb to pguser;# 备用，非必要执行，以 pguser 身份连接 testdb 数据库（-W参数强制密码提示）
# psql -U pguser -d testdb -W

• 创建表、插入数据、查询数据

# 连接到测试数据库
\c testdb# 表权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO pguser;
# 序列权限 
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT USAGE ON SEQUENCES TO pguser;
# 函数权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT EXECUTE ON FUNCTIONS TO pguser;# 创建用户表（包含基础字段和约束）
CREATE TABLE users (user_id SERIAL PRIMARY KEY,username VARCHAR(50) UNIQUE NOT NULL,password VARCHAR(100) NOT NULL CHECK (length(password) >= 8),email VARCHAR(100) UNIQUE CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$'),created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,is_active BOOLEAN DEFAULT TRUE
) WITH (OIDS=FALSE);COMMENT ON TABLE users IS '系统用户表';
COMMENT ON COLUMN users.password  IS '加密存储，最小长度8位';# 插入测试数据
INSERT INTO users (username, password, email, is_active) VALUES('admin', '$2a$10$xJwLHXW4ZPCH/tm2wQYzU.7JQ5LbKb8JjZJfV8nJQYd9JKlXJZrG2', 'admin@test.com',  TRUE),('guest', 'Guest#1234', 'guest@example.org',  FALSE),('tester', 'Test@5678', NULL, TRUE);# 基础查询
SELECT * FROM users ORDER BY created_at DESC;# 条件查询（活跃用户）
SELECT user_id, username, email 
FROM users 
WHERE is_active = TRUE AND created_at > '2025-01-01';# 聚合查询
SELECT COUNT(*) AS total_users, SUM(CASE WHEN is_active THEN 1 ELSE 0 END) AS active_users,COUNT(email) AS users_with_email
FROM users;