当前位置：首页 > article >正文

Linux安装部署数据库：PostgreSQL14

article 2024/11/1 18:49:34

Linux安装部署数据库：PostgreSQL14

- 一、安装环境
- - 1、虚拟机环境
  - 2、下载安装包
- 二、安装步骤
- - 1、在线安装 PGSQL
  - 2、离线安装 PGSQL
  - 3、源码安装 PGSQL
- 三、基本操作
- - 1、初始化配置
  - 2、数据库登录
  - 3、常用命令项
- 四、常见问题
- - 1、对用户 "postgres" 的对等认证失败
  - 2、数据库 no encryption 的 pg_hba.conf 记录
  - 3、root 用户下未找到 psql 对应命令
  - 4、源码安装下，通过 systemd 管理 pgsql 服务

写在前面：

本文在部分内容方面，参考了CSDN部分博主的文章内容，如有冒犯请联系博主协商处理。
本文所有安装部分的内容和部分配置的内容都经博主实测有效，如有错误的地方，欢迎大家指正学习。
文章创作不易，请各位看官给个三连，博主在此先行感谢了！！！

一、安装环境

1、虚拟机环境

主机部署环境：

Linux 版本 处理器类型 PostgreSQL 版本
CentOS Linux release 7.9 x86_64 postgresql 14

Linux 版本	处理器类型	PostgreSQL 版本
CentOS Linux release 7.9	x86_64	postgresql 14

2、下载安装包

官网地址：https://www.postgresql.org
下载地址：https://www.postgresql.org/download/

二、安装步骤

1、在线安装 PGSQL

查看 Linux 版本和处理器类型

$ cat /etc/centos-release			# 查看Linux版本
CentOS Linux release 7.9.2009 (Core)

$ uname -m							# 查看处理器类型
x86_64

在官网选择合适的版本的 pgsql 数据库

使用 yum 下载安装 pgsql 服务

# 下载rpm安装包
$ yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 使用rpm包安装
$ yum install -y postgresql14-server

查看安装的 postgresql 版本，确认是否安装成功

$ rpm -qa | grep postgresql
postgresql14-libs-14.10-1PGDG.rhel7.x86_64
postgresql14-14.10-1PGDG.rhel7.x86_64
postgresql14-server-14.10-1PGDG.rhel7.x86_64

修改默认数据目录，并初始化 pgsql 数据库

# 修改默认数据目录
$ mkdir -p /data/pgsql/data
$ chown -R postgres:postgres /data/pgsql
$ vim /usr/lib/systemd/system/postgresql-14.service
...
Environment=PGDATA=/data/pgsql/data/
...

# 初始化pgsql数据库
$ /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK

# 设置pgsql服务开机自启
$ systemctl enable postgresql-14
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-14.service to /usr/lib/systemd/system/postgresql-14.service.

# 启动pgsql服务
$ systemctl start postgresql-14

查看服务的运行状态
```
$ systemctl status postgresql-14
```

2、离线安装 PGSQL

官网下载地址：https://yum.postgresql.org/rpmchart/
离线安装采用 rpm 包的安装方式，官网选择下载对应版本的 rpm 包
此次安装选择 postgresql14-14.11 版本的 rpm 包下载安装

安装选择 postgresql14 的 rpm 包及其版本如下

postgresql14-14.11-1PGDG.rhel7.x86_64.rpm
postgresql14-libs-14.11-1PGDG.rhel7.x86_64.rpm
postgresql14-server-14.11-1PGDG.rhel7.x86_64.rpm
postgresql14-contrib-14.11-1PGDG.rhel7.x86_64.rpm

上传安装包至虚拟机中的 /root/postgresql 目录下

$ mkdir -p /root/postgresql
$ ls /root/postgresql/
postgresql14-14.11-1PGDG.rhel7.x86_64.rpm
postgresql14-contrib-14.11-1PGDG.rhel7.x86_64.rpm
postgresql14-libs-14.11-1PGDG.rhel7.x86_64.rpm
postgresql14-server-14.11-1PGDG.rhel7.x86_64.rpm

按照如下顺序安装 PGSQL14，或者无视依赖关系安装

$ rpm -ivh postgresql14-libs-14.11-1PGDG.rhel7.x86_64.rpm
$ rpm -ivh postgresql14-14.11-1PGDG.rhel7.x86_64.rpm
$ rpm -ivh postgresql14-server-14.11-1PGDG.rhel7.x86_64.rpm
$ rpm -ivh postgresql14-contrib-14.11-1PGDG.rhel7.x86_64.rpm

# 安装方式二选一即可
$ rpm -ivh *.rpm --nodeps --force

若出现如下问题，则需要安装相关依赖包或升级 python至3.6 版本以上

# 问题说明: 缺少python3-libs-3.6.x及以上版本的依赖包
$ rpm -ivh postgresql14-contrib-14.11-1PGDG.rhel7.x86_64.rpm
警告：postgresql14-contrib-14.11-1PGDG.rhel7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID 73e3b907: NOKEY
错误：依赖检测失败：
	libpython3.6m.so.1.0()(64bit) 被 postgresql14-contrib-14.11-1PGDG.rhel7.x86_64 需要

# 解决办法1: 
$ yum -y install libxslt libicu python36-libs python2-libs libperl.so

# 解决办法2: (推荐)
$ yum -y install libpython3.6m.so.1.0

# 内网环境下载方式: (下载不安装) 将下载好的rpm包上传虚拟机安装
 $ yum install --downloadonly --downloaddir=/root/pg14_rpm libpython3.6m.so.1.0
 $ rpm -ivh *.rpm --nodeps --force

查看安装的 postgresql 版本，确认是否安装成功

$ rpm -qa | grep postgresql
postgresql14-libs-14.11-1PGDG.rhel7.x86_64
postgresql14-14.11-1PGDG.rhel7.x86_64
postgresql14-contrib-14.11-1PGDG.rhel7.x86_64
postgresql14-server-14.11-1PGDG.rhel7.x86_64

修改默认数据目录，并初始化 pgsql 数据库

# 修改默认数据目录/var/lib/pgsql/14/data/
$ mkdir -p /data/pgsql/data
$ chown -R postgres:postgres /data/pgsql
$ vim /usr/lib/systemd/system/postgresql-14.service
...
Environment=PGDATA=/data/pgsql/data/
...

# 初始化pgsql数据库
$ /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK

# 重新加载systemd系统管理守护程序的命令
$ systemctl daemon-reload

# 设置pgsql服务开机自启
$ systemctl enable postgresql-14
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-14.service to /usr/lib/systemd/system/postgresql-14.service.

# 启动pgsql服务
$ systemctl start postgresql-14

3、源码安装 PGSQL

官网下载 pgsql 源码包，官网地址：https://www.postgresql.org/ftp/source/
此次选择 v14.10 版本，下载**postgresql-14.11.tar.gz**源码包

官网下载**postgresql-14.11.tar.gz**源码包，或使用 **wget** 命令下载源码包

$ wget https://ftp.postgresql.org/pub/source/v14.11/postgresql-14.11.tar.gz
$ ll -h postgresql-14.11.tar.gz
-rw-r--r-- 1 root root 28M 4月  24 21:58 postgresql-14.11.tar.gz

解压**postgresql-14.11.tar.gz**源码包，并安装所需的相关依赖包

# 安装相关依赖
$ yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake

# 解压源码包
$ tar -zxvf  postgresql-14.11.tar.gz

创建安装目录，编译安装 pgsql 服务

$ mkdir -p /data/pgsql
$ cd /root/postgresql-14.11
$ ./configure --prefix=/data/pgsql
$ make && make install

创建用户、组、数据目录

# 创建组
$ groupadd postgres
# 创建用户
$ useradd -g postgres postgres
# 创建数据目录
$ mkdir –p /data/pgsql/data
# 修改目录归属
$ chown -R postgres:postgres /data/pgsql/

配置用户的环境变量

$ vim /home/postgres/.bash_profile
...
export PGHOME=/data/pgsql/
export PGDATA=$PGHOME/data
export PATH=$PATH:$HOME/bin:$PGHOME/bin

$ source /home/postgres/.bash_profile

初始化 pgsql 数据库，并启动 pgsql 数据库服务

$ su - postgres
$ /data/pgsql/bin/initdb -D /data/pgsql/data/
$ /data/pgsql/bin/pg_ctl -D /data/pgsql/data/ -l /data/pgsql/logfile stop	
$ /data/pgsql/bin/pg_ctl -D /data/pgsql/data/ -l /data/pgsql/logfile start

修改 postgres 用户的账号密码

# 登录postgres用户
$ su - postgres
# 登录pgsql数据库
-bash-4.2$ psql

# 修改postgres用户密码
postgres=# alter user postgres with password 'postgres';
# 重新加载服务器配置文件, 使配置文件生效
postgres=# select pg_reload_conf();

修改 pgsql 配置文件，添加口令认证

$ vim /data/pgsql/data/pg_hba.conf
# TYPE  DATABASE        USER    ADDRESS         METHOD

# "local" is for Unix domain socket connections only
local   all             all                     md5
# IPv4 local connections:
host    all             all     0.0.0.0/0       md5
host    all             all     127.0.0.1/32    md5
# IPv6 local connections:
host    all             all     ::1/128         md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                     md5
host    replication     all     127.0.0.1/32    md5
host    replication     all     ::1/128         md5

修改 pgsql 配置文件，允许远程访问

$ vim /data/pgsql/data/postgresql.conf 
...
60 #listen_addresses = 'localhost'
61 listen_addresses = '*'

重新启动 pgsql 服务，使配置文件生效

$ su - postgres
$ /data/pgsql/bin/pg_ctl -D /data/pgsql/data/ -l /data/pgsql/logfile stop	
$ /data/pgsql/bin/pg_ctl -D /data/pgsql/data/ -l /data/pgsql/logfile start

三、基本操作

1、初始化配置

查看 pgsql 数据库相关文件

# 1. 检查 PostgreSQL 服务端的所有相关文件所在目录
$ rpm -ql postgresql14-server

# 2. 检查 PostgreSQL 客户端的所有相关文件所在目录
$ rpm -ql postgresql14

# 3. 查看 PostgreSQL 所有相关的命令文件
$ ls /usr/pgsql-14/bin/

查看数据库的超级用户：postgres

# 安装pgsql数据库, 会默认创建postgres用户
$ cat /etc/passwd | grep postgres
postgres:x:26:26:PostgreSQL Server:/home/postgres:/bin/bash

修改 postgres 用户的账号密码

# 登录postgres用户
$ su - postgres
# 登录pgsql数据库
-bash-4.2$ psql
# 修改postgres用户密码
postgres=# alter user postgres with password 'postgres';
# 重新加载服务器配置文件, 使配置文件生效
postgres=# select pg_reload_conf();

注：此时修改后仍然无法使用密码登录，需要修改配置文件设置需要密码登录模式

修改配置文件，设置需要密码验证登录模式
```
$ vim /data/pgsql/data/pg_hba.conf
# TYPE  DATABASE    	USER   ADDRESS        METHOD
local   all         	all                   md5
host    all         	all    127.0.0.1/32   scram-sha-256
host    all         	all    ::1/128        scram-sha-256
local   replication 	all                   md5
host    replication 	all    127.0.0.1/32   scram-sha-256
host    replication 	all    ::1/128        scram-sha-256

# method=md5表示使用用户口令密码登录，修改peer为md5
# method=peer表示仅当数据库用户名与操作系统用户名相同时才允许连接
```
登录验证方式：peer、md5、scram-sha-256
(1) peer：本地连接认证方法，使用操作系统的用户身份验证来确定是否允许连接。
- 验证说明：数据库会检查客户端的操作系统用户名和数据库角色名是否匹配，如果匹配则允许连接
- 适用场景：适用于本地连接，要求操作系统用户和数据库角色一一对应。
(2) md5：口令的认证方法，客户端连接到数据库时需要提供用户名和密码。
- 验证说明：密码以 MD5 加密存储在数据库中，客户端发送经过 MD5 加密的密码进行验证。
- 适用场景：适用于需要在客户端连接时提供密码进行认证的场景，通常用于远程连接。
(3) scram-sha-256：一种更安全的认证方法。
- 验证说明：它提供了更强的密码安全性和保护机制，避免了传统的明文密码传输和简单哈希密码存储的风险。
- 适用场景：适用于需要更高安全性的场景，推荐在支持的情况下使用。

配置 PostgreSQL 数据库远程访问权限

$ vim /data/pgsql/data/postgresql.conf
60 #listen_addresses = 'localhost' 
61 listen_addresses = '*'

设置允许所有 IP 访问 PostgreSQL 数据库

$ vim /data/pgsql/data/pg_hba.conf
# TYPE  DATABASE    	USER   ADDRESS        METHOD
local   all         	all                   md5
host    all         	all    127.0.0.1/32   scram-sha-256
# 新增配置行
host    all             all    0.0.0.0/0      md5
...

重新启动 PostgreSQL 数据库

# RPM安装的服务启动方式
$ systemctl restart postgresql-14.service

# 源码安装的服务启动方式
$ /data/pgsql/bin/pg_ctl -D /data/pgsql/data/ -l /data/pgsql/logfile stop
$ /data/pgsql/bin/pg_ctl -D /data/pgsql/data/ -l /data/pgsql/logfile start

2、数据库登录

登录数据库， psql 命令常见用法：

# 1. 使用指定用户名连接到指定数据库
$ psql -U username -d database_name

# 2. 指定主机名连接到指定数据库
$ psql -h host -U username -d database_name

# 3. 连接到默认数据库并执行指定的SQL查询
$ psql -c "SQL query"

使用 postgres 用户登录 PostgreSQL 数据库

$ psql -Upostgres -dpostgres
用户 postgres 的口令：
postgres=# \l							# 查看数据库列表

3、常用命令项

常用 pgsql 数据库操作

$ psql -U postgres -d postgres		# 登录数据库
postgres=# \l						# 列出所有数据库
postgres=# create database mydb;	# 创建数据库
postgres=# drop database mydb;		# 删除数据库
postgres=# \c mydb;					# 切换数据库
postgres=# \q						# 退出数据库

常用 pgsql 表操作

-- 创建 student 表
CREATE TABLE student (
	student_id SERIAL PRIMARY KEY,
	student_name VARCHAR(50) NOT NULL,
	age INT,
	class VARCHAR(20)
);

-- 创建 teacher 表
CREATE TABLE teacher (
	teacher_id SERIAL PRIMARY KEY,
	teacher_name VARCHAR(50) NOT NULL,
	subject VARCHAR(50),
	experience_years INT
);

postgres=# \dt						# 列出所有表
postgres=# \d teacher				# 显示表结构
postgres=# drop table teacher;		# 删除表

常用 pgsql 数据操作

-- 向 student 表插入数据
INSERT INTO student (student_name, age, class) VALUES ('Alice', 20, 'Mathematics');
INSERT INTO student (student_name, age, class) VALUES ('Bob', 22, 'Physics');

-- 向 teacher 表插入数据
INSERT INTO teacher (teacher_name, subject, experience_years) VALUES ('Mr. Smith', 'Mathematics', 10);
INSERT INTO teacher (teacher_name, subject, experience_years) VALUES ('Ms. Johnson', 'Physics', 8);

postgres=# select * from student;
postgres=# update student SET age=19 WHERE student_name='Bob';
postgres=# delete from student where student_name='Alice';

常用 pgsql 用户和权限操作

# 1. 创建用户
postgres=# create user yyh with password '123qqq...A';
# 2. 授予角色
postgres=# grant pg_monitor to yyh;
# 3. 撤销角色
postgres=# revoke pg_monitor from yyh;
# 4. 修改用户密码
postgres=# alter user yyh with password '密码';
# 5. 查看所有角色
postgres=# select rolname from pg_roles;
# 6. 查看用户信息
postgres=# select * from pg_shadow;
# 7. 修改密码有效期
postgres=# alter user yyh with valid until '2024-12-03 09:00:00';
# 8. 查看用户表权限
postgres=# select * from information_schema.table_privileges;

常用 pgsql 其他命令操作

postgres=# select * from pg_stat_activity;	# 查看服务器状态
postgres=# select version();				# 查看数据库版本
postgres=# select pg_reload_conf();			# 重新加载服务器配置文件
postgres=# select pg_is_in_recovery();		# 查看当前库是否为备库

四、常见问题

1、对用户 “postgres” 的对等认证失败

问题说明： 本地登录 pgsql 数据库时，出现对用户的对等认证失败
```
# 当前操作系统用户为root用户
$ psql -Upostgres -dpostgres
psql: 错误: 连接到套接字"/var/run/postgresql/.s.PGSQL.5432"上的服务器失败:致命错误:  对用户"postgres"的对等认证失败

# 配置文件method=peer表示仅当数据库用户名与操作系统用户名相同时才允许连接
# 即需要登录操作系统的postgres用户才能使用postgres登录pgsql数据库
```
登录验证方式：peer、md5、scram-sha-256
(1) peer：本地连接认证方法，使用操作系统的用户身份验证来确定是否允许连接。
- 验证说明：数据库会检查客户端的操作系统用户名和数据库角色名是否匹配，如果匹配则允许连接
- 适用场景：适用于本地连接，要求操作系统用户和数据库角色一一对应。
(2) md5：口令的认证方法，客户端连接到数据库时需要提供用户名和密码。
- 验证说明：密码以 MD5 加密存储在数据库中，客户端发送经过 MD5 加密的密码进行验证。
- 适用场景：适用于需要在客户端连接时提供密码进行认证的场景，通常用于远程连接。
(3) scram-sha-256：一种更安全的认证方法。
- 验证说明：它提供了更强的密码安全性和保护机制，避免了传统的明文密码传输和简单哈希密码存储的风险。
- 适用场景：适用于需要更高安全性的场景，推荐在支持的情况下使用。

解决办法： 修改配置文件 pg_hba.conf中的所有用户访问配置的 method 方法

# 方案一: 修改method认证方式, 修改为口令认证方式
$ vim /data/pgsql/data/pg_hba.conf
# TYPE  DATABASE    	USER   ADDRESS        METHOD
local   all         	all                   md5
host    all         	all    127.0.0.1/32   scram-sha-256
host    all         	all    ::1/128        scram-sha-256
local   replication 	all                   md5
host    replication 	all    127.0.0.1/32   scram-sha-256
host    replication 	all    ::1/128        scram-sha-256
$ systemctl restart postgresql-14.service
$ psql -Upostgres -dpostgres

# 方案二: PGSQL数据库默认仅当数据库用户名与操作系统用户名相同时才允许连接
$ su - postgres 
$ psql -Upostgres -dpostgres

2、数据库 no encryption 的 pg_hba.conf 记录

问题说明： 远程登录 pgsql 数据库时，使用 postgres 用户登录出现如下错误：

致命错误: 没有用于主机 "192.168.8.1", 用户 "postgres", 数据库 "postgres", no encryption 的 pg_hba.conf 记录

解决办法： pgsql 默认情况下，不允许远程连接，需要单独配置

(1) 修改配置文件postgresql.conf配置数据库远程访问权限

(2) 修改配置文件 pg_hba.conf，新增配置允许所有 IP 访问登录

# 修改配置文件, 配置数据库远程访问权限
$ vim /data/pgsql/data/postgresql.conf
#listen_addresses = 'localhost' 
listen_addresses = '*'

# 修改配置文件, 设置允许所有IP访问数据库
$ vim /data/pgsql/data/pg_hba.conf
...
host    all             all    0.0.0.0/0      md5

# 重启pgsql服务 (按需选择对应方式)
$ systemctl restart postgresql-14.service

$ su - postgres
$ /data/pgsql/bin/pg_ctl -D /data/pgsql/data/ -l /data/pgsql/logfile stop
$ /data/pgsql/bin/pg_ctl -D /data/pgsql/data/ -l /data/pgsql/logfile start

3、root 用户下未找到 psql 对应命令

问题说明： 在 root 用户下，使用 psql 命令登录数据库时，显示未找到 psql 命令
```
$ psql -Upostgres -dpostgres
bash: psql: 未找到命令...
```

解决办法： 为 root 用户添加环境变量，修改/root/.bash_profile文件，追加配置

$ vim /root/.bash_profile
...
export PGHOME=/data/pgsql/
export PGDATA=$PGHOME/data
export PATH=$PATH:$HOME/bin:$PGHOME/bin

$ source /root/.bash_profile

$ psql -Upostgres -dpostgres
Password for user postgres:
psql (14.11)
Type "help" for help.
postgres=#

补充说明： 若希望所有用户都能使用 psql 命令，修改 **/etc/profile** 文件，追加配置

$ vim /etc/profile
...
export PGHOME=/data/pgsql/
export PGDATA=$PGHOME/data
export PATH=$PATH:$HOME/bin:$PGHOME/bin

$ source /etc/profile

$ psql -Upostgres -dpostgres
Password for user postgres:
psql (14.11)
Type "help" for help.
postgres=#

4、源码安装下，通过 systemd 管理 pgsql 服务

问题说明： 源码安装 pgsql 后，如何通过 systemd 服务管理器管理 pgsql 服务

# 源码安装下, 通常是通过pg_ctl命令管理pgsql服务
$ su - postgres
$ /data/pgsql/bin/pg_ctl -D /data/pgsql/data/ -l /data/pgsql/logfile stop
$ /data/pgsql/bin/pg_ctl -D /data/pgsql/data/ -l /data/pgsql/logfile start

# 说明：这种启动方式很不方便, 且常需要登录 postgres 用户后, 执行命令很麻烦
# 如何通过 systemd 控制 pgsql 服务？且设置开机自启, 无需登录postgres用户

解决办法： 创建启动文件，设置服务开机自启，使用 systemd 控制 pgsql 服务

# 复制源码包内的启动文件
$ cp /root/postgresql-14.11/contrib/start-scripts/linux /etc/init.d/postgresql

# 修改安装目录和数据目录和日志文件
$ vim /etc/init.d/postgresql
...
# Installation prefix
prefix=/data/pgsql

# Data directory
PGDATA="/data/pgsql/data"

# Who to run the postmaster as, usually "postgres". 
PGUSER=postgres

# Where to keep a log file
PGLOG="$PGDATA/logfile"
...

# 添加可执行权限
$ chmod +x /etc/init.d/postgresql

# 添加服务到系统服务列表, 设置服务开机自启
$ chkconfig --add postgresql
$ chkconfig postgresql on

# 启动服务, 查看服务状态
$ systemctl start postgresql
$ systemctl status postgresql