关于postgresql:PostgresqlCenterOs-79-Postgresql14-数据库-安装和配置

引言

集体已经在不同机器配置以及不同的 Windows 零碎当中集体尝试屡次装置 Postgresql，发现每一次状况都不一样，强烈建议读者应用虚拟机的 Linux 零碎装置 Postgresql 最为稳当，缩小不必要的踩坑懊恼。

装置形式

一共有两种装置形式：

• 第一种是应用 官网提供的 yum 装置。
• 第二种是应用 源码编译二进制的形式装置。

第二种形式集体并不是很举荐，在依赖上可能要折腾不少工夫，并且不同的 Linux 发行零碎须要解决的依赖问题也不同。

集体环境阐明

环境阐明：

• 操作系统：Win11 2023 22H2
• CPU：13 代 i5 13500HX
• 内存：32G
• 虚拟机：VM Tool 17
• CenterOs 版本：7.9
• 镜像：CentOS-7-x86_64-Minimal-2009_2.iso

实际上还要关注一下 CPU 的兼容性：
 CPU architectures: x86, x86_64, IA64, PowerPC, PowerPC 64, S/390, S/390x, Sparc, Sparc 64, ARM, MIPS, MIPSEL, and PA-RISC. Code support exists for M68K, M32R, and VAX
 
这里提这一点是因为之前用 M1 的 Mac 尝试的时候，发现 M1 须要 通过 Rosetta 转译，并且执行略微简单的 SQL 的时候就会很卡，十分蛋疼，这一点只能看官网是否有兼容 M1 版本的志愿了。

RPM 装置

RPM 装置形式间接简略，适宜集体疾速搭建和应用 Postgresql。

装置 RPM 存储库

存储库用于做数据存储以及 Postgresql 的自动更新操作。首先拜访官网链接：https://www.postgresql.org/download/linux/redhat/ 填写本人的配置状况，利用官网的配置脚本操作即可。

以 Postgresql-14 和集体的环境配置为例，截图局部对应的代码如下，留神集体应用的是虚拟机的CenterOs7.9 的版本。

rpm 下载

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm  

装置 postgresql

sudo yum install -y postgresql14-server 

配置 Postgresql 开机启动

sudo /usr/pgsql-14/bin/postgresql-14-setup initdb 

sudo systemctl enable postgresql-14  

sudo systemctl start postgresql-14

集体的运行后果如下：

[zxd@localhost ~]$ sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK

[zxd@localhost ~]$ sudo 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.

[zxd@localhost ~]$ sudo systemctl start postgresql-14

下面的命令执行实现之后，咱们能够看一下 postgresql 的服务是否启动胜利：

sudo systemctl status postgresql-14

[xander@localhost ~]$ sudo systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2023-04-07 05:06:33 EDT; 7min ago
     Docs: https://www.postgresql.org/docs/14/static/
  Process: 908 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 919 (postmaster)
   CGroup: /system.slice/postgresql-14.service
           ├─919 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
           ├─934 postgres: logger 
           ├─937 postgres: checkpointer 
           ├─938 postgres: background writer 
           ├─939 postgres: walwriter 
           ├─940 postgres: autovacuum launcher 
           ├─941 postgres: stats collector 
           └─942 postgres: logical replication launcher 

Apr 07 05:06:32 localhost.localdomain systemd[1]: Starting PostgreSQL 14 database server...
Apr 07 05:06:33 localhost.localdomain postmaster[919]: 2023-04-07 05:06:33.265 EDT [919] LOG:  redirec...ess
Apr 07 05:06:33 localhost.localdomain postmaster[919]: 2023-04-07 05:06:33.265 EDT [919] HINT:  Future...g".
Apr 07 05:06:33 localhost.localdomain systemd[1]: Started PostgreSQL 14 database server.
Hint: Some lines were ellipsized, use -l to show in full.

如果没有呈现下面的后果，则尝试执行上面的命令。

systemctl postgresql initdb

chkconfig postgresql on

装置结束后，零碎会在 Linux 的零碎创立数据库超级用户 postgres，明码为空。咱们应用非 postgres 用户登陆测试一下，发现 Postgresql 会回绝拜访：

[root@localhost xander]# psql -U postgres
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"

为什么失败了？查阅官网文档能够得悉，Postgresql 初始化之后配置文件只能容许本地连接，而且连贯到服务器的认证形式是 peer 和 ident。

然而咱们在 Linux 中应用 root 切换到 postgres 用户，能够间接应用 psql命令拜访，对于这一点后文还会再次介绍。

[xander@localhost ~]$ su root
Password: 

[root@localhost xander]# su postgres
bash-4.2$ psql
could not change directory to "/home/xander": Permission denied
psql (14.8)
Type "help" for help.
bash-4.2$ exit
exit

[root@localhost xander]# 

如果要实现内部拜访，咱们须要批改 pg_hba.conf 的文件以及 postgresql.conf 文件，然而因为本局部应用 RPM 的装置形式，很多配置都是官网预设的，经常会找不到这两个文件 …… 所以在介绍如何实现近程拜访之前，这里补充 Postgresql 应用 RPM 装置的配置阐明。

RPM 装置配置补充阐明

RPM 的装置形式尽管无脑简略并且无需过多配置，然而会导致装置之后配置是官网默认预设的，很多时候想要理解 Postgresql 的配置文件地位比拟麻烦。

这里花了一点工夫通过 find 命令把 RPM 装置之后的要害门路都给盘进去了。

留神这些配置可能随着不同 Linux 操作系统和不同的 Postgresql 版本变动，这里仅供 CenterOs 操作系统参考。

initdb.log

如果不喜爱官网给的主动启动服务，能够用上面的命令进行自定义重启，留神 /var/lib/pgsql/14/data/ 这个门路须要提前执行 initdb 初始化。

/usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data/ -l logfile start

bin 命令目录地位

bin 目录在/usr/pgsql-14/bin/ 的门路之下，外面蕴含了 postgresql 的命令。咱们能够配置环境变量$PG_PATH 门路为/usr/pgsql-14/bin/。

[xander@localhost ~]$ ll /usr/pgsql-14/bin/
total 11420
-rwxr-xr-x 1 root root   71784 Feb  9 07:01 clusterdb
-rwxr-xr-x 1 root root   79992 Feb  9 07:01 createdb
-rwxr-xr-x 1 root root   76232 Feb  9 07:01 createuser
-rwxr-xr-x 1 root root   71568 Feb  9 07:01 dropdb
-rwxr-xr-x 1 root root   67408 Feb  9 07:01 dropuser
-rwxr-xr-x 1 root root  134840 Feb  9 07:01 initdb
-rwxr-xr-x 1 root root   41472 Feb  9 07:01 pg_archivecleanup
-rwxr-xr-x 1 root root  126928 Feb  9 07:01 pg_basebackup
-rwxr-xr-x 1 root root  181024 Feb  9 07:01 pgbench
-rwxr-xr-x 1 root root   62568 Feb  9 07:01 pg_checksums
-rwxr-xr-x 1 root root   41248 Feb  9 07:01 pg_config
-rwxr-xr-x 1 root root   58056 Feb  9 07:01 pg_controldata
-rwxr-xr-x 1 root root   71112 Feb  9 07:01 pg_ctl
-rwxr-xr-x 1 root root  413872 Feb  9 07:01 pg_dump
-rwxr-xr-x 1 root root  109984 Feb  9 07:01 pg_dumpall

pg_hba.conf 文件地位

pg_hba.conf 文件默认在 /var/lib/psql/pg 版本号 / 下。

postgresql.conf 文件地位

postgresql.conf文件默认在 /var/lib/psql/pg 版本号 / 下。

RPM 装置日志

装置日志的查问门路如下：

[xander@localhost ~]$ sudo cat /var/lib/pgsql/14/initdb.log

不出意外这个启动日志会像上面这样：

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/pgsql/14/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data/ -l logfile start

pg_ctl 重启 postgresql

如果想要利用 pg_ctl 重启，能够用上面的命令：

/usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data/ -l logfile restart

留神以 root 用户重启会被 postgresql 回绝，记得切回到官网创立的 postgres 用户再进行操作。

[root@localhost xander]# /usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data/ -l logfile restart
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.

咱们切换到 postgres 用户，执行下面一长串命令，却收到了提醒没有权限。

bash-4.2$ /usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data/ -l logfile start
bash: ./pg_ctl: Permission denied

这里发现文件夹的所属人是 root，天然是没有权限的。既然是权限问题，那么咱们就用 root 从新给 postgres 用户调配权限即可，做法只须要把所属人交给 postgres 用户即可。

[root@localhost xander]# chown -R postgres  /var/lib/pgsql

[root@localhost xander]# ll /var/lib/pgsql/
total 0
drwx------ 4 postgres postgres 51 Apr  7 04:59 14

pg_ctl 罕用操作

敞开 postgresql

bash-4.2$ /usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data/ -l logfile stop
waiting for server to shut down.... done
server stopped

启动 postgresql

bash-4.2$ /usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data/ -l logfile start
waiting for server to start.... done
server started

重启 postgresql

bash-4.2$ /usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data/ -l logfile restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started

PostgreSQL: Documentation: 14: pg_ctl 官网文档有更多例子，篇幅无限，这里就不一一展现了。

pg_ctl init[db] [-D datadir] [-s] [-o initdb-options]

pg_ctl start [-D datadir] [-l filename] [-W] [-t seconds] [-s] [-o options] [-p path] [-c]

pg_ctl stop [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ] [-W] [-t seconds] [-s]

pg_ctl restart [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ] [-W] [-t seconds] [-s] [-o options] [-c]

pg_ctl reload [-D datadir] [-s]

pg_ctl status [-D datadir]

pg_ctl promote [-D datadir] [-W] [-t seconds] [-s]

pg_ctl logrotate [-D datadir] [-s]

pg_ctl kill signal_name process_id

On Microsoft Windows, also:

pg_ctl register [-D datadir] [-N servicename] [-U username] [-P password] [-S a[uto] | d[emand] ] [-e source] [-W] [-t seconds] [-s] [-o options]

pg_ctl unregister [-N servicename]

本地连接形式连贯 pgsql

如果你感觉这数据库我不想做任何配置，只想连忙上手体验一下 postgresql，那么能够间接用本地连接的形式，官网默认给新建的 postgres 赋予了免密拜访权限。

能够通过less /etc/passwd 的命令查看以后零碎的所有用户：

xander:x:1000:1000:xander:/home/xander:/bin/bash

# pgsql 构建新用户
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash

postgres 为 RPM 装置之后 Postgresql 默认创立的。因为想要不做任何配置以本地形式拜访，须要先切换到 postgres 用户。

su postgres

切换到 postgres 用户之后，间接执行 psql 即可。

bash-4.2$ psql
psql (14.7)
Type "help" for help.

postgres=# 

实现 Postgresql 近程拜访

理解 RPM 装置模式的几个重要文件目录地位之后，上面介绍如何实现 Postgresql 的近程拜访。

批改 pg_hba.conf 文件

应用 RPM 装置形式，pg_hba.conf文件默认在 /var/lib/psql/pg 版本号 / 下。

[root@localhost pgsql-14]# ll /var/lib/pgsql/14/data/
total 68
drwx------ 5 postgres postgres    41 Apr  7 04:59 base
-rw------- 1 postgres postgres    30 Apr  7 05:06 current_logfiles
drwx------ 2 postgres postgres  4096 Apr  7 04:59 global
drwx------ 2 postgres postgres    32 Apr  7 04:59 log
drwx------ 2 postgres postgres     6 Apr  7 04:59 pg_commit_ts
drwx------ 2 postgres postgres     6 Apr  7 04:59 pg_dynshmem
-rw------- 1 postgres postgres  4577 Apr  7 04:59 pg_hba.conf
-rw------- 1 postgres postgres  1636 Apr  7 04:59 pg_ident.conf
drwx------ 4 postgres postgres    68 Apr  7 05:11 pg_logical
drwx------ 4 postgres postgres    36 Apr  7 04:59 pg_multixact
drwx------ 2 postgres postgres     6 Apr  7 04:59 pg_notify
drwx------ 2 postgres postgres     6 Apr  7 04:59 pg_replslot
drwx------ 2 postgres postgres     6 Apr  7 04:59 pg_serial
drwx------ 2 postgres postgres     6 Apr  7 04:59 pg_snapshots
drwx------ 2 postgres postgres     6 Apr  7 05:06 pg_stat
drwx------ 2 postgres postgres    25 Apr  7 05:31 pg_stat_tmp
drwx------ 2 postgres postgres    18 Apr  7 04:59 pg_subtrans
drwx------ 2 postgres postgres     6 Apr  7 04:59 pg_tblspc
drwx------ 2 postgres postgres     6 Apr  7 04:59 pg_twophase
-rw------- 1 postgres postgres     3 Apr  7 04:59 PG_VERSION
drwx------ 3 postgres postgres    60 Apr  7 04:59 pg_wal
drwx------ 2 postgres postgres    18 Apr  7 04:59 pg_xact
-rw------- 1 postgres postgres    88 Apr  7 04:59 postgresql.auto.conf
-rw------- 1 postgres postgres 28776 Apr  7 04:59 postgresql.conf
-rw------- 1 postgres postgres    58 Apr  7 05:06 postmaster.opts
-rw------- 1 postgres postgres   102 Apr  7 05:06 postmaster.pid

在文件中咱们找到上面的内容：

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

这里简略介绍一下参数作用：

• TYPE：连贯类型个别有 local 和 host 两种，local 指的是本地连接，host 则相似内部的 ssh 近程服务器的拜访形式。
• DATABASE：示意要连贯的数据库，all 示意所有。这里能够改为具体的数据库名称
• USER：这里填写 Linux 用户。比方默认调配的 postgres 能够免密拜访。
• ADDRESS：127.0.0.1/32示意只容许来本人本机的连贯，0.0.0.0/0 示意所有的 Ip 放行。192.168.1.0/24示意容许 192.168.1.1–192.168.1.255 这个地址段的 ip 地址连贯。

• METHOD：示意连贯的认证形式，PostgreSQL 的连贯命令 psql 有两种连贯形式：

  • 不带 -h 参数或 host 参数时，是 local 连贯，用的是 peer 认证形式。
  • 应用 -h localhost、-h 127.0.0.1、postgres@localhost 或 postgres@127.0.0.1 这样的格局，则会应用 host 类型，应用 TCP/IP 的形式连贯，应用的是ident 的认证形式。

为了实现内部近程拜访，配置做如下批改：

[xander@localhost ~]$ sudo vim /var/lib/pgsql/14/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

# llow all access to
host    all             all             0.0.0.0/0               trust

开端增加一行：

host    all             all             0.0.0.0/0               trust

配置实现之后 xq 保留并且退出。能够通过上面的形式重启服务：

[root@localhost bin]# systemctl restart postgresql-14

重启 postgresql 之后咱们继续执行psql，发现报错还是存在，root 还是无法访问：

[root@localhost bin]# psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "root" does not exist

咱们须要批改另一个文件postgresql.conf。

批改 postgresql.conf 文件

通过 vim /var/lib/psql/pg 版本号 /postgresql.conf，咱们找到上面的内容，把listen_addresses 的正文放开，并且把内容批改为 *，而后wq 保留退出即可。

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;

navicat 连贯测试

最初咱们通过 Windows 的 Navicat 配置连贯测试一下，明码这里能够抉择留空，起因能够看上文。

当然倡议用 postgres 构建一个权限较高的用户给内部拜访。

常用命令

参考自：https://blog.csdn.net/u010856284/article/details/70142810

\di 查看索引 

postgres=# \di
Did not find any relations.

创立数据库

create database [数据库名];  

删除数据库

drop database [数据库名];    

重命名一个表

alter table [表名 A] rename to [表名 B];

删除一个表

drop table [表名]; 

在已有的表里增加字段

alter table [表名] add column [字段名] [类型];   

删除表中的字段

alter table [表名] drop column [字段名];   

重命名一个字段

alter table [表名] rename column [字段名 A] to [字段名 B];

给一个字段设置缺省值

alter table [表名] alter column [字段名] set default [新的默认值];  

去除缺省值

alter table [表名] alter column [字段名] drop default;   

在表中插入数据

insert into 表名 ([字段名 m],[字段名 n],......) values ([列 m 的值],[列 n 的值],......);  

批改表中的某行某列的数据：

update [表名] set [指标字段名]=[目标值] where [该行特色];   

删除表中某行数据：

delete from [表名] where [该行特色];   

-- 删空整个表  
delete from [表名]; 

创立表

create table ([字段名 1] [类型 1] ;,[字段名 2] [类型 2],......<,primary key (字段名 m, 字段名 n,...)>;);

\l 列举表

列举数据库，相当于 mysql 的show databases。

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

\c dbname 列举数据库

切换数据库, 相当于 mysql 的use dbname。

postgres=# \c
You are now connected to database "postgres" as user "postgres".

\dt 查看表构造

查看表构造，相当于desc tblname，show columns from tbname。

postgres=# \dt pg_class;
            List of relations
   Schema   |   Name   | Type  |  Owner   
------------+----------+-------+----------
 pg_catalog | pg_class | table | postgres
(1 row)

RPM 装置形式进行卸载

在 Linux 上很多时候卸载要比装置还要麻烦很多，这里屡次试验之后确认上面这套指令是删的比拟洁净的计划。

查看零碎版本号和类型

cat /etc/redhat-release

[root@localhost xander]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

集体试验为 CenterOs7.9。

敞开服务

为了确保操作平安，倡议先进行服务。

sudo systemctl stop postgresql-14

yum 删除软件包

yum remove postgresql*

执行之后会呈现上面的提醒：

Dependencies Resolved
================================================================================================================
 Package                          Arch                Version                        Repository            Size
================================================================================================================
Removing:
 postgresql14                     x86_64              14.7-1PGDG.rhel7               @pgdg14              7.9 M
 postgresql14-libs                x86_64              14.7-1PGDG.rhel7               @pgdg14              923 k
 postgresql14-server              x86_64              14.7-1PGDG.rhel7               @pgdg14               22 M

Transaction Summary
================================================================================================================
Remove  3 Packages

Installed size: 31 M
Is this ok [y/N]: 

除了下面的命令，还能够用上面的形式列出已装置的包，并应用 yum remove 逐个删除。

rpm -qa | grep postgresql

咱们以下面提醒 Remove 3 Packages 的其中一个包为例演示，其余两个反复操作即可：

[root@localhost xander]# yum rm postgresql14-server-14.7-1PGDG.rhel7.x86_64

执行之后呈现上面的提醒：

Loaded plugins: fastestmirror
No such command: rm. Please use /usr/bin/yum --help
[root@localhost xander]# yum remove postgresql14-server-14.7-1PGDG.rhel7.x86_64
Loaded plugins: fastestmirror
Resolving Dependencies
--> Running transaction check
---> Package postgresql14-server.x86_64 0:14.7-1PGDG.rhel7 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================================
 Package                          Arch                Version                        Repository            Size
================================================================================================================
Removing:
 postgresql14-server              x86_64              14.7-1PGDG.rhel7               @pgdg14               22 M

Transaction Summary
================================================================================================================
Remove  1 Package

Installed size: 22 M
Is this ok [y/N]: ^C

删除相干目录文件

rm -rf  /var/lib/pgsql

rm -rf  /usr/pgsql*

删除 pg 相干用户组 / 用户

userdel -r postgres

groupdel postgres

源码装置

创立装置用户及布局装置目录

创立装置用户指令如下。

useradd postgres

passwd postgres

创立 PostgreSQL 软件装置目录。

mkdir -p /opt/postgres/pgsql15.2

创立 PostgreSQL 数据文件目录

mkdir -p /opt/postgres/pgdata

创立 PostgreSQL 数据库日志目录

mkdir -p /opt/postgres/logs

 批改目录所属用户
 

chown postgres:postgres /opt/postgres -R

下面的命令执行实现之后，察看后果如下：

[xander@localhost ~]$ ll /opt/postgres/
total 0
drwxr-xr-x 2 postgres postgres 6 Jun 26 01:19 logs
drwxr-xr-x 2 postgres postgres 6 Jun 26 01:19 pgdata
drwxr-xr-x 2 postgres postgres 6 Jun 26 01:18 pgsql15.2

装置依赖软件包

yum install -y zlib-devel readline-devel python perl perl-ExtUtils-Embed

yum install zlib-devel

yum install readline-devel

yum install perl

yum install perl-ExtUtils-Embed

下载源码包

https://www.postgresql.org/ftp/source/

Ver15.3：PostgreSQL: File Browser

上传到服务器之后进行解压。

编译装置 PostgreSQL 数据库软件

解压之后进入到对应的 postgresql 目录，并且执行上面的命令。--prefix指定装置软件目录，--with-perl反对 perl 语言自定义函数，--with-python反对 python 语言自定义函数。

./configure --prefix=/opt/postgres/pgsql15.2 --with-perl --with-python

这里的 /opt/postgres/pgsql15.2 并不是强制规定的，

[postgres@localhost postgresql-15.3]$ ./configure --prefix=/opt/postgres/pgsql15.2 --with-perl --with-python

checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking which template to use... linux
checking whether NLS is wanted... no
checking for default port number... 5432
checking for block size... 8kB
checking for segment size... 1GB
checking for WAL block size... 8kB
checking for gcc... no
checking for cc... no
configure: error: in `/home/postgres/postgresql-15.3':
configure: error: no acceptable C compiler found in $PATH

咱们须要装置适合的编译器。

sudo yum install gcc-c++

装置之后发现还是存在问题。

configure: using perl 5.16.3
checking for Perl archlibexp... /usr/lib64/perl5
checking for Perl privlibexp... /usr/share/perl5
checking for Perl useshrplib... true
checking for CFLAGS recommended by Perl... -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64
checking for CFLAGS to compile embedded Perl... 
checking for flags to link embedded Perl... Can't locate ExtUtils/Embed.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .).
BEGIN failed--compilation aborted.
no
configure: error: could not determine flags for linking embedded Perl.
This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not
installed.

仍然是存在依赖包问题，这里继续执行上面的命令。

yum install perl-ExtUtils-Embed

持续编译，发现还是报错，持续解决依赖问题。发现 CenterOs 默认装置了 Python2.7.X 版本，然而要求 Python3 的版本，这里须要先卸载旧版本而后装置新版本。

configure: error: Python version 2.7 is too old (version 3 or later is required)

装置 Python3

装置 Python3 的步骤这里简化列举到一起：

# 装置 wget 指令
yum install wget -y

# 拉取 3.7.10
wget https://www.python.org/ftp/python/3.7.10/Python-3.7.10.tgz

# 解压 
tar -zxvf Python-3.7.10.tgz

# 进入解压目录编译装置
#创立一个空文件夹，寄存 python3 程序
mkdir /usr/local/python3

#编译装置
./configure --prefix=/usr/local/python3
# 此处会呈现大量的信息打印
#If you want a release build with all stable optimizations active (PGO, etc),
#如果你想取得一个具备所有稳固优化性能（PGO 等）的公布版本
#please run ./configure --enable-optimizations
./configure --enable-optimizations


# 编译装置
make && make install
# 急躁期待即可
# 发现报错 
# zipimport.ZipImportError: can't decompress data
yum -y install zlib*
# 批改 Module 门路的 setup 文件：Modules/Setup.dist（或者 Modules/Setup）文件
#Looking in links: /tmp/tmp7scqysgu
#Processing /tmp/tmp7scqysgu/setuptools-47.1.0-py3-none-any.whl
#Processing /tmp/tmp7scqysgu/pip-20.1.1-py2.py3-none-any.whl
#Installing collected packages: setuptools, pip
#Successfully installed pip-20.1.1 setuptools-47.1.0
# 呈现下面的相似后果阐明装置胜利


## 建设 Python3 的软连贯
ln -s /usr/local/python3/bin/python3.7 /usr/bin/python3
ln -s /usr/local/python3/bin/pip3.7 /usr/bin/pip3

# 查看 Python3 和 pip3
python3

pip3 --version


# 装置 pykmip
pip3 install pykmip

上面是 python3 和 pip3 装置胜利之后的后果。

[root@localhost Python-3.7.10]# python3
Python 3.7.10 (default, Jun 26 2023, 08:43:00) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> quit
Use quit() or Ctrl-D (i.e. EOF) to exit
>>> exit
Use exit() or Ctrl-D (i.e. EOF) to exit
>>> exit();
[root@localhost Python-3.7.10]# pip3 --version
pip 20.1.1 from /usr/local/lib/python3.7/site-packages/pip (python 3.7)

解决异样之后，咱们继续执行上面的命令。

 ./configure --prefix=/opt/postgres/pgsql15.2 --with-perl --with-python

checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking which template to use... linux
checking whether NLS is wanted... no
checking for default port number... 5432
checking for block size... 8kB
checking for segment size... 1GB
checking for WAL block size... 8kB
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables... 
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for gcc option to accept ISO C99... -std=gnu99
checking for g++... g++
checking whether we are using the GNU C++ compiler... yes
checking whether g++ accepts -g... yes
checking for gawk... gawk
checking whether gcc -std=gnu99 supports -Wdeclaration-after-statement, for CFLAGS... yes
checking whether gcc -std=gnu99 supports -Werror=vla, for CFLAGS... yes
checking whether gcc -std=gnu99 supports -Werror=unguarded-availability-new, for CFLAGS... no
checking whether g++ supports -Werror=unguarded-availability-new, for CXXFLAGS... no
checking whether gcc -std=gnu99 supports -Wendif-labels, for CFLAGS... yes
checking whether g++ supports -Wendif-labels, for CXXFLAGS... yes
checking whether gcc -std=gnu99 supports -Wmissing-format-attribute, for CFLAGS... yes
checking whether g++ supports -Wmissing-format-attribute, for CXXFLAGS... yes
checking whether gcc -std=gnu99 supports -Wimplicit-fallthrough=3, for CFLAGS... no
checking whether g++ supports -Wimplicit-fallthrough=3, for CXXFLAGS... no
checking whether gcc -std=gnu99 supports -Wcast-function-type, for CFLAGS... no
checking whether g++ supports -Wcast-function-type, for CXXFLAGS... no
checking whether gcc -std=gnu99 supports -Wformat-security, for CFLAGS... yes
checking whether g++ supports -Wformat-security, for CXXFLAGS... yes
checking whether gcc -std=gnu99 supports -fno-strict-aliasing, for CFLAGS... yes
checking whether g++ supports -fno-strict-aliasing, for CXXFLAGS... yes
checking whether gcc -std=gnu99 supports -fwrapv, for CFLAGS... yes
checking whether g++ supports -fwrapv, for CXXFLAGS... yes
checking whether gcc -std=gnu99 supports -fexcess-precision=standard, for CFLAGS... yes
checking whether g++ supports -fexcess-precision=standard, for CXXFLAGS... no
checking whether gcc -std=gnu99 supports -funroll-loops, for CFLAGS_UNROLL_LOOPS... yes
checking whether gcc -std=gnu99 supports -ftree-vectorize, for CFLAGS_VECTORIZE... yes
checking whether gcc -std=gnu99 supports -Wunused-command-line-argument, for NOT_THE_CFLAGS... no
checking whether gcc -std=gnu99 supports -Wcompound-token-split-by-macro, for NOT_THE_CFLAGS... no
checking whether gcc -std=gnu99 supports -Wdeprecated-non-prototype, for NOT_THE_CFLAGS... no
checking whether gcc -std=gnu99 supports -Wformat-truncation, for NOT_THE_CFLAGS... no
checking whether gcc -std=gnu99 supports -Wstringop-truncation, for NOT_THE_CFLAGS... no
checking whether the C compiler still works... yes
checking how to run the C preprocessor... gcc -std=gnu99 -E
checking for pkg-config... /usr/bin/pkg-config
checking pkg-config is at least version 0.9.0... yes
checking allow thread-safe client libraries... yes
checking whether to build with ICU support... no
checking whether to build with Tcl... no
checking whether to build Perl modules... yes
checking whether to build Python modules... yes
checking whether to build with GSSAPI support... no
checking whether to build with PAM support... no
checking whether to build with BSD Authentication support... no
checking whether to build with LDAP support... no
checking whether to build with Bonjour support... no
checking whether to build with SELinux support... no
checking whether to build with systemd support... no
checking whether to build with XML support... no
checking whether to build with LZ4 support... no
checking whether to build with ZSTD support... no
checking for ld used by GCC... /usr/bin/ld
checking if the linker (/usr/bin/ld) is GNU ld... yes
checking for ranlib... ranlib
checking for strip... strip
checking whether it is possible to strip libraries... yes
checking for ar... ar
checking for a BSD-compatible install... /usr/bin/install -c
checking for tar... /usr/bin/tar
checking whether ln -s works... yes
checking for a thread-safe mkdir -p... /usr/bin/mkdir -p
checking for bison... no
configure: WARNING:
*** Without Bison you will not be able to build PostgreSQL from Git nor
*** change any of the parser definition files.  You can obtain Bison from
*** a GNU mirror site.  (If you are using the official distribution of
*** PostgreSQL then you do not need to worry about this, because the Bison
*** output is pre-generated.)
checking for flex... no
configure: WARNING:
*** Without Flex you will not be able to build PostgreSQL from Git nor
*** change any of the scanner definition files.  You can obtain Flex from
*** a GNU mirror site.  (If you are using the official distribution of
*** PostgreSQL then you do not need to worry about this because the Flex
*** output is pre-generated.)
checking for perl... /usr/bin/perl
configure: using perl 5.16.3
checking for Perl archlibexp... /usr/lib64/perl5
checking for Perl privlibexp... /usr/share/perl5
checking for Perl useshrplib... true
checking for CFLAGS recommended by Perl... -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64
checking for CFLAGS to compile embedded Perl... 
checking for flags to link embedded Perl...   -fstack-protector  -L/usr/lib64/perl5/CORE -lperl -lresolv -lnsl -ldl -lm -lcrypt -lutil -lpthread -lc
checking for python3... /usr/local/bin/python3
configure: using python 3.7.10 (default, Jun 26 2023, 08:43:00) 
checking for Python sysconfig module... yes
checking Python configuration directory... /usr/local/lib/python3.7/config-3.7m-x86_64-linux-gnu
checking Python include directory... -I/usr/local/include/python3.7m
checking how to link an embedded Python application... configure: error: could not find shared library for Python
You might have to rebuild your Python installation.  Refer to the
documentation for details.  Use --without-python to disable building
PL/Python.

发现还是存在问题，从英文形容能够得悉，这里须要 Bison 以及 Flex 依赖能力通过配置查看。

configure: WARNING:
*** Without Bison you will not be able to build PostgreSQL from Git nor
*** change any of the parser definition files.  You can obtain Bison from
*** a GNU mirror site.  (If you are using the official distribution of
*** PostgreSQL then you do not need to worry about this, because the Bison
*** output is pre-generated.)
checking for flex... no
configure: WARNING:
*** Without Flex you will not be able to build PostgreSQL from Git nor
*** change any of the scanner definition files.  You can obtain Flex from
*** a GNU mirror site.  (If you are using the official distribution of
*** PostgreSQL then you do not need to worry about this because the Flex
*** output is pre-generated.)

flex bison 依赖装置

CenterOs7.9 当中执行上面的命令进行装置。

yum install flex bison

此时持续 make，发现还是报错，这里开始狐疑不是依赖的问题，而是之前的编译命令存在问题，正确的形式应该是应用上面的命令。

 ./configure --prefix=/opt/postgres/pgsql15.2 --with-perl --with-python3

make 命令编译装置查看，这一次终于失常执行了（只不过提醒了 --with-python3这个配置指令）

make 过程急躁期待编译即可。

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 isolation_main.o pg_regress.o -L../../../src/port -L../../../src/common   -Wl,--as-needed -Wl,-rpath,'/opt/postgres/pgsql15.2/lib',--enable-new-dtags  -lpgcommon -lpgport -lz -lreadline -lpthread -lrt -ldl -lm  -o pg_isolation_regress
make[2]: Leaving directory `/home/postgres/postgresql-15.3/src/test/isolation'
make -C test/perl all
make[2]: Entering directory `/home/postgres/postgresql-15.3/src/test/perl'make[2]: Nothing to be done for `all'.
make[2]: Leaving directory `/home/postgres/postgresql-15.3/src/test/perl'make[1]: Leaving directory `/home/postgres/postgresql-15.3/src'
make -C config all
make[1]: Entering directory `/home/postgres/postgresql-15.3/config'make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/home/postgres/postgresql-15.3/config'

make install 编译装置。

make[1]: Entering directory `/home/postgres/postgresql-15.3/config'/usr/bin/mkdir -p'/opt/postgres/pgsql15.2/lib/pgxs/config'/usr/bin/install -c -m 755 ./install-sh'/opt/postgres/pgsql15.2/lib/pgxs/config/install-sh'/usr/bin/install -c -m 755 ./missing'/opt/postgres/pgsql15.2/lib/pgxs/config/missing'make[1]: Leaving directory `/home/postgres/postgresql-15.3/config'

编译装置之后构建软连贯。

构建软链接

留神这里的装置门路在/home/postgres/postgresql-15.3。

[postgres@localhost ~]$ pwd
/home/postgres

[postgres@localhost postgresql-15.3]$ pwd
/home/postgres/postgresql-15.3

[postgres@localhost pgsql15.2]$ pwd
/opt/postgres/pgsql15.2

# 咱们进入到 /opt/postgres/pgsql15.2，这个目录的上一个目录
# /opt/postgres

[postgres@localhost postgresql-15.3]$ ln -sf pgsql15.2 pgsql

构建软链接的命令如下：

ln -sf pgsql15.2 pgsql

增加环境变量

这里记住装置的地位为：

/opt/postgres/pgsql15.2

批改 /etc/profile，增加环境变量（pgsql15.2 能够替换为软链接 pgsql）。

#postgres
export PATH=/opt/postgres/pgsql15.2/bin:$PATH
export LD_LIBRARY_PATH=/opt/postgres/pgsql15.2/lib:$LD_LIBRARY_PATH
export PGDATA=/opt/postgres/pgdata

应用 initdb 命令创立数据库实例

如果间接运行 pg_ctl -D $PGDATA -l /opt/postgres/logs/logfile start，零碎会提醒$PGDATA 外面不合乎启动条件。

How to find out if a directory is a valid PostgreSQL cluster

咱们须要进入 $PGDATA 的门路（/opt/postgres/pgdata），而后执行initdb，运行后果如下：

[postgres@localhost pgdata]$ initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /opt/postgres/pgdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /opt/postgres/pgdata -l logfile start

启动 pgsql

留神 pgsql 不容许应用 root 启动数据库。

[postgres@localhost pgsql15.2]$ pg_ctl -D $PGDATA -l /opt/postgres/logs start
waiting for server to start..../bin/sh: /opt/postgres/logs: Is a directory
 stopped waiting
pg_ctl: could not start server
Examine the log output.

这里启动命令谬误，须要调整命令。

pg_ctl -D $PGDATA -l /opt/postgres/logs/logfile start

正确启动之后的管制打印如下：

[postgres@localhost pgsql15.2]$ pg_ctl -D $PGDATA -l /opt/postgres/logs/logfile start
waiting for server to start.... done
server started

察看日志，发现运行是胜利的。

[postgres@localhost postgres]$ cat logs/logfile 
2023-06-26 09:47:06.820 EDT [106274] LOG:  starting PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-06-26 09:47:06.829 EDT [106274] LOG:  listening on IPv6 address "::1", port 5432
2023-06-26 09:47:06.829 EDT [106274] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-06-26 09:47:06.829 EDT [106274] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-06-26 09:47:06.831 EDT [106277] LOG:  database system was shut down at 2023-06-26 09:40:41 EDT
2023-06-26 09:47:06.833 EDT [106274] LOG:  database system is ready to accept connections

而后咱们用 postgres 这个用户执行psql。

[postgres@localhost postgres]$ psql
psql (15.3)
Type "help" for help.

postgres=# 

运行胜利。装置执行到这一步之后后续的操作思路和 RPM 的操作思路相似，这里就不过多赘述了。

写在最初

将 RPM 和源码装置两种形式的操作流程进行比照，会发现源码装置须要解决很多依赖问题的琐事，显然比拟麻烦，这里集体更偏差官网举荐的 RPM 形式进行 Postgresql 装置搭建，省去很多钻牛角尖的工夫。

参考资料

Linux 部署 postgresql 并开启近程拜访 – 掘金 (juejin.cn)

# PostgreSQL 在 Linux 下的两种装置形式

(3 条音讯)【数据库】PostgreSQL 编译装置具体过程_sdut 菜鸟的博客 -CSDN 博客

PostgreSQL 在 Linux 下的两种装置形式 – 墨天轮 (modb.pro)

https://blog.csdn.net/u010856284/article/details/70142810