CentOS搭建：PostgreSQL
accttodo 12/31/2024 运维操作系统Linux
目录
参考：
# CentOS搭建：PostgreSQL
# 环境依赖
# 创建用户
# 创建用户
useradd postgres
# 设置密码：ps@at3721
passwd postgres
# 查看用户
id postgres
1
2
3
4
5
6
2
3
4
5
6
# 安装离线包
# 安装依赖
rpm -ivh libzstd-1.5.2-1.el7.x86_64.rpm
rpm -ivh libicu-50.2-4.el7_7.x86_64.rpm
# 安装lib库
rpm -ivh postgresql15-libs-15.7-1PGDG.rhel7.x86_64.rpm
# 安装客户端
rpm -ivh postgresql15-15.7-1PGDG.rhel7.x86_64.rpm
# 安装服务端
rpm -ivh postgresql15-server-15.7-1PGDG.rhel7.x86_64.rpm
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 验证数据库安装
psql --version
psql -V
1
2
3
2
3
#卸载： 先停止postges服务，然后执行下面的命令即可
rpm -qe postgresql15-libs-15.7-1PGDG.rhel7.x86_64.rpm
rpm -qe postgresql15-15.7-1PGDG.rhel7.x86_64.rpm
rpm -qe postgresql15-server-15.7-1PGDG.rhel7.x86_64.rpm
1
2
3
4
2
3
4
# 初始化数据库
/usr/pgsql-15/bin/postgresql-15-setup initdb
1
# 启动数据库
# 设置开机启动
systemctl enable postgresql-15
# 启动数据库
systemctl start postgresql-15
# 查看启动状态
systemctl status postgresql-15
# 查看数据库进程
ps -ef | grep postgre
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 移动数据目录
# 创建目录
mkdir -p /data/pgsql/
# 停止数据库
systemctl stop postgresql-15
1
2
3
4
2
3
4
# 移动数据到目录
mv /var/lib/pgsql/15/* /data/pgsql/
# 修改目录的所有用户
chown -R postgres:postgres /data/pgsql/
# 修改目录的操作权限
chmod -R 700 /data/pgsql/data
1
2
3
4
5
6
2
3
4
5
6
cp /usr/lib/systemd/system/postgresql-15.service /usr/lib/systemd/system/postgresql-15.service.bak20240703
ll -a /usr/lib/systemd/system/ |grep postgresql
# 修改指定的数据目录
vi /usr/lib/systemd/system/postgresql-15.service
1
2
3
4
2
3
4
Environment=PGDATA=/data/pgsql/data/
1
cp /data/pgsql/data/postgresql.conf /data/pgsql/data/postgresql.conf.bak20240703
ll -a /data/pgsql/data/ |grep postgresql
# 修改配置的数据目录
vi /data/pgsql/data/postgresql.conf
1
2
3
4
2
3
4
data_directory = '/data/pgsql/data'
1
# 重新加载配置文件
systemctl daemon-reload
# 重启数据库
systemctl restart postgresql-15
#确认启动成功
systemctl status postgresql-15
ps -ef | grep postgres
1
2
3
4
5
6
7
2
3
4
5
6
7
# 修改数据库用户密码
# 切换到用户postgres
su postgres
1
2
2
# 登录到postgresql
psql
1
2
2
以下为postgresql控制台命令：
# 执行命令
ALTER ROLE postgres WITH PASSWORD 'psAat3721';
# 退出
\q
1
2
3
4
2
3
4
# 修改授权，远程连接
# 修改连接认证
pg_hba.conf（Host-Based Authentication configuration file）用于定义客户端如何连接到 PostgreSQL 服务器以及这些连接需要哪些认证方式。这个文件允许数据库管理员为不同类型的连接设置不同的认证策略，以确保数据库的安全性。
- TYPE：连接类型，比如
local（Unix 域套接字连接）、host（TCP/IP 连接，包括 SSL）、hostssl（仅通过 SSL 的 TCP/IP 连接）、hostnossl（不通过 SSL 的 TCP/IP 连接，但通常不推荐使用，因为安全性较低）。 - DATABASE：数据库名称，或者使用
all代表所有数据库，sameuser代表与用户名相同的数据库名，samerole代表与用户角色相同的数据库名，replication代表复制连接。 - USER：用户名，或者使用
all代表所有用户。 - ADDRESS：客户端的 IP 地址或子网掩码，或者使用
all代表所有地址。对于 Unix 域套接字连接，此字段被忽略，通常写为samehost。 - METHOD：认证方法，如
trust（无需密码）、reject（直接拒绝连接）、md5（使用 MD5 加密的密码）、scram-sha-256（一种更安全的密码认证方法）、password（明文密码，但在实际传输中使用 MD5 加密）等。
# 备份
cp /data/pgsql/data/pg_hba.conf /data/pgsql/data/pg_hba.conf.bak20240703
ll -a /data/pgsql/data/ |grep pg_hba
# 修改数据库连接安全认证
vi /data/pgsql/data/pg_hba.conf
1
2
3
4
5
2
3
4
5
# 增加一行
host all all 0.0.0.0/0 md5
1
2
2
# 修改连接地址
# 备份
cp /data/pgsql/data/postgresql.conf /data/pgsql/data/postgresql.conf.bak20240703.2
ll -a /data/pgsql/data | grep postgresql
# 修改
vi /data/pgsql/data/postgresql.conf
1
2
3
4
5
2
3
4
5
listen_addresses = '*'
#有需求修改port
#port = 5432
1
2
3
2
3
# 重启配置生效
# 重新加载配置文件
systemctl daemon-reload
# 重启数据库
systemctl restart postgresql-15
#确认启动成功
systemctl status postgresql-15
ps -ef | grep postgres
1
2
3
4
5
6
7
2
3
4
5
6
7
# 远程连接数据库
Navicat连接postgresql数据库
问题: 错误: 字段datlastsysoid不存在（报错）
说明: Postgres 15 从表中删除了 datlastsysoid 字段,因此 Navicat 15.0.29 或 16.1 之前的任何版本在查找此已弃用字段时都会引发此错误。
解决:
方式一:
要解决此问题，请升级到最新的 Navicat 15.0.29 或 16.1 及更高版本（可能需要新的许可证）
方式二(use):
打开 Navicat 文件夹（通常在 C：\Program Files\PremiumSoft\Navicat…） 下），找到 libcc.dll 并创建此文件的备份。在任何十六进制编辑器中打开此文件，如果需要，您可以使用在线工具，例如 https://hexed.it/。 在文件中搜索“SELECT DISTINCT datlastsysoid”，并将其替换为“SELECT DISTINCT dattablespace”。 将文件保存在原始位置。如果遇到任何安全问题，请将其另存为“.txt”文件，然后将其重命名为“.dll”。
# 常用的数据库命令
# 切换到用户postgres
su postgres
#进入postgresql命令行
psql
1
2
3
4
2
3
4
postgresql命令行：
# 退出 or: exit;
\q
# 列出所有用户, 指令显示用户和用户的用户属性
\du
# 列出所有库
\l
# 列出库下所有表
\d
# 查看索引
\di
# 列举表, 相当于mysql的show tables
dt
# 查看模式schema列表
\dn
# 查看模式schema列表详情
\dn+
# 显示用户访问权限，或 \dp
\z
# 切换数据库,相当于mysql的use dbname
\c dbname
# 查看表结构, 相当于desc tblname,show columns from tbname
\d tblname
# 删除数据库
DROP DATABASE phone;
# 指令查看全部可设置的管理权限
\h CREATE ROLE
# 显示所有可设置的访问权限
\h GRANT
# 查看详细信息
select version();
# 查看版本信息
show server_version;
# 查看数字版本信息包括小版号
SHOW server_version_num;
# 或
SELECT current_setting('server_version_num');
# 返回类型为text，如果需要可以转换为interger:
SELECT current_setting('server_version_num')::integer;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
# 创建表(大小写均可)
create table test(id integer not null primary key);
# 添加表的字段
alter table phone add column phone_number character varying(11) not null;
# 重命名一个表
alter table [表名A] rename to [表名B];
# 删除一个表
drop table [表名];
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8