PostgreSQL 16.1 编译安装

一、PostgreSQL 16.1 编译安装

可用于部署泛微Ecology9
OS: RockyLinux 9.1
DB: PostgreSQL 16.1

QQ: 2219377135

1. 关闭默认防火墙 firewall

systemctl stop firewalld.service #停止firewall
systemctl disable firewalld.service #禁止firewall开机启动
systemctl mask firewalld
systemctl stop firewalld
dnf remove firewalld

2.安装iptables防火墙

dnf install -y iptables-services

3.编辑iptables配置文件

vim /etc/sysconfig/iptables

# sample configuration for iptables service
# you can edit this manually or use system-config-firewall
# please do not ask us to add additional ports/services to this default configuration
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT

#最后重启防火墙使配置生效
systemctl restart iptables.service 

#设置防火墙开机启动
systemctl enable iptables.service 

#重启防火墙
/usr/libexec/iptables/iptables.init restart 

4.关闭SELINUX

vim /etc/selinux/config
SELINUX=disabled 

# 使配置生效
setenforce 0

5.安装依赖包

dnf install -y uuid uuid-devel libuuid libuuid-devel 

dnf install -y tcl tcl-devel  perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake gcc* readline-devel icu libicu-devel

6. 编译PostgreSQL

# 创建目录
mkdir -p /usr/local/pgsql
mkdir -p /usr/local/pgsql/data
mkdir -p /usr/local/pgsql/log

#解压postgreSQL 16.1
tar zxf postgresql-16.1.tar.gz

#编译
cd postgresql-16.1
./configure --prefix=/usr/local/pgsql --with-openssl --with-pgport=5432 --with-tcl --with-perl --with-python --with-libxml --with-libxslt --with-ossp-uuid --with-pam --with-ldap

#gmake 含第三方插件全部编译
gmake world

#gmake 含第三方插件全部安装
gmake install-world

7.创建用户组

groupadd postgres
useradd -g postgres postgres

8.配置权限

chown -R postgres:postgres /usr/local/pgsql
chown -R postgres:postgres /usr/local/pgsql/data
chown -R postgres:postgres /usr/local/pgsql/log
chmod -R 700 /usr/local/pgsql/log

9.配置环境变量

vim /etc/profile.d/pgsql.sh
export PGHOME=/usr/local/pgsql
export PGDATA=/usr/local/pgsql/data
export PATH=$PATH:$PGHOME/bin
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
source /etc/profile.d/pgsql.sh

10.初始化数据库

# 切换到postgres用户
su - postgres

#初始化数据库
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8

11.启动数据库

#启动
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start 

#停止
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop 

#重启
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile restart 

#查看状态
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile status 

12.配置init启动

#切换到root
su - root

#复制启动文件
cp /usr/local/src/postgresql-14.3/contrib/start-scripts/linux /etc/init.d/postgresql

#设置运行权限
chmod +x /etc/init.d/postgresql

#编辑修改
vi /etc/init.d/postgresql
prefix=/usr/local/pgsql
PGDATA="/usr/local/pgsql/data" 
PGUSER=postgres
service postgresql start
service postgresql restart
service postgresql stop
chkconfig postgresql on

13.配置systemd启动

vi /usr/lib/systemd/system/postgresql.service

[Unit]
Description=PostgreSQL Database Server
After=syslog.target
After=network.target

[Service]
Type=forking
User=postgres
Group=postgres
ExecStart=/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data
ExecStop=/usr/local/pgsql/bin/pg_ctl stop
ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D /usr/local/pgsql/data
TimeoutSec=300

[Install]
WantedBy=multi-user.target

systemctl daemon-reload
systemctl start postgresql
systemctl enable postgresql
systemctl restart postgresql
systemctl status postgresql

14.配置密码

#切换到postgres用户
su - postgres

#连接数据库
psql -U postgres -d postgres -hlocalhost -p5432
#参数：-U指定用户，-d指定数据库，-h指定服务器，-p指定端口

# 修改数据库用户postgres的密码为postgres
ALTER USER postgres WITH PASSWORD 'postgres'

#退出控制台
postgres=#\q 

15.修改配置参数

vi /usr/local/pgsql/data/postgresql.conf

port = 5432
max_connections = 1000
listen_addresses = '*' 
log_destination = 'csvlog' 
logging_collector = on   
log_directory = 'log'  

log_filename = 'postgresql-%d.log'  


log_file_mode = 0600 
log_rotation_age = 1d   
log_rotation_size = 1024MB
log_truncate_on_rotation = on
log_lock_waits = off
log_statement = 'none'  

log_duration = on 
log_min_duration_statement = 0 
log_connections = off 
log_disconnections = off
log_line_prefix = '%m [%p] %u %d %r' 
log_timezone = 'Asia/Shanghai' 

16.配置数据库访问权限

vi /usr/local/pgsql/data/pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
systemctl restart postgresql

17. 创建数据库

CREATE USER ecology WITH PASSWORD 'ecology123';
CREATE DATABASE ecology OWNER ecology;
GRANT ALL PRIVILEGES ON DATABASE ecology to ecology;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ecology;