部署

关闭selinux

setenforce 0

下载安装包

wget https://ftp.postgresql.org/pub/source/v17.4/postgresql-17.4.tar.gz

确认make版本不低于3.80

make --version

安装依赖

yum install libicu-devel bison flex readline-devel zlib-devel python3-devel

编译安装

tar xf postgresql-17.4.tar.gz
cd /root/postgresql-17.4
./configure --prefix=/data/postgresql-17.4 --with-perl --with-python
make
make install

安装contrib目录下的工具

cd /root/postgresql-17.4/contrib
make
make install

配置环境变量

cat >/etc/profile.d/pg.sh << 'EOF'
export PG_HOME=/data/postgresql-17.4
export PATH=$PG_HOME/bin:$PATH
export LD_LIBRARY_PATH=$PG_HOME/lib
EOF
source /etc/profile
bash

检查postgresql版本

postgres --version

创建数据库用户

groupadd postgres
useradd --gid postgres postgres

修改目录权限

chown -R postgres.postgres /data/postgresql-17.4/
chmod +x /data/postgresql-17.4/bin/pg_ctl

数据库初始化

su - postgres
initdb -D /data/postgresql-17.4/data

创建systemd配置文件

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

[Service]
User=postgres
Group=postgres
Type=forking
Environment="PATH=/data/postgresql-17.4/bin"
ExecStart=/data/postgresql-17.4/bin/pg_ctl -D /data/postgresql-17.4/data -l /var/log/postgresql-17.4/postgresql.log start
ExecStop=/data/postgresql-17.4/bin/pg_ctl -D /data/postgresql-17.4/data stop
PrivateTmp=true

[Install]
WantedBy=multi-user.target
EOF

创建日志目录

mkdir /var/log/postgresql-17.4
chown postgres.postgres /var/log/postgresql-17.4

编辑配置文件/data/postgresql-17.4/data/postgresql.conf修改监听地址

cat >> /data/postgresql-17.4/data/postgresql.conf << EOF
listen_addresses = '0.0.0.0'
EOF

启动服务

systemctl start postgresql
systemctl enable postgresql

连接数据库

psql -h localhost -U postgres

设置用户密码

psql -h localhost -U postgres -c "ALTER USER postgres WITH PASSWORD '123456';"

设置禁用无密码访问，编辑/data/postgresql-17.4/data/pg_hba.conf

sed -i \
  -e 's/^local   all             all                                     trust/local   all             all                                     md5/' \
  -e 's/^host    all             all             127\.0\.0\.1\/32            trust/host    all             all             127.0.0.1\/32            md5/' \
  -e 's/^host    all             all             ::1\/128                 trust/host    all             all             ::1\/128                 md5/' \
  -e '/^host    all             all             0\.0\.0\.0\/0            md5/! {/^# IPv6 local connections:/a host    all             all             0.0.0.0/0            md5' \
  -e '}' \
  /data/postgresql-17.4/data/pg_hba.conf

重启服务

systemctl restart postgresql

测试登录

psql -h localhost -U postgres
Password for user postgres: 
psql (17.4)
Type "help" for help.

postgres=#