关于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 initdbInitializing database ... OK[zxd@localhost ~]$ sudo systemctl enable postgresql-14Created 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...essApr 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 postgrespsql: 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 rootPassword: [root@localhost xander]# su postgresbash-4.2$ psqlcould not change directory to "/home/xander": Permission deniedpsql (14.8)Type "help" for help.bash-4.2$ exitexit[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 ... okcreating subdirectories ... okselecting dynamic shared memory implementation ... posixselecting default max_connections ... 100selecting default shared_buffers ... 128MBselecting default time zone ... America/New_Yorkcreating configuration files ... okrunning bootstrap script ... okperforming post-bootstrap initialization ... oksyncing data to disk ... okSuccess. 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 restartpg_ctl: cannot be run as rootPlease log in (using, e.g., "su") as the (unprivileged) user that willown the server process.

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

bash-4.2$ /usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data/ -l logfile startbash: ./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 0drwx------ 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 stopwaiting for server to shut down.... doneserver stopped

启动postgresql

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

重启postgresql

bash-4.2$ /usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data/ -l logfile restartwaiting for server to shut down.... doneserver stoppedwaiting for server to start.... doneserver 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_idOn 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$ psqlpsql (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 68drwx------ 5 postgres postgres    41 Apr  7 04:59 base-rw------- 1 postgres postgres    30 Apr  7 05:06 current_logfilesdrwx------ 2 postgres postgres  4096 Apr  7 04:59 globaldrwx------ 2 postgres postgres    32 Apr  7 04:59 logdrwx------ 2 postgres postgres     6 Apr  7 04:59 pg_commit_tsdrwx------ 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.confdrwx------ 4 postgres postgres    68 Apr  7 05:11 pg_logicaldrwx------ 4 postgres postgres    36 Apr  7 04:59 pg_multixactdrwx------ 2 postgres postgres     6 Apr  7 04:59 pg_notifydrwx------ 2 postgres postgres     6 Apr  7 04:59 pg_replslotdrwx------ 2 postgres postgres     6 Apr  7 04:59 pg_serialdrwx------ 2 postgres postgres     6 Apr  7 04:59 pg_snapshotsdrwx------ 2 postgres postgres     6 Apr  7 05:06 pg_statdrwx------ 2 postgres postgres    25 Apr  7 05:31 pg_stat_tmpdrwx------ 2 postgres postgres    18 Apr  7 04:59 pg_subtransdrwx------ 2 postgres postgres     6 Apr  7 04:59 pg_tblspcdrwx------ 2 postgres postgres     6 Apr  7 04:59 pg_twophase-rw------- 1 postgres postgres     3 Apr  7 04:59 PG_VERSIONdrwx------ 3 postgres postgres    60 Apr  7 04:59 pg_waldrwx------ 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 onlylocal   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                                     peerhost    replication     all             127.0.0.1/32            scram-sha-256host    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 onlylocal   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                                     peerhost    replication     all             127.0.0.1/32            scram-sha-256host    replication     all             ::1/128                 scram-sha-256# llow all access tohost    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]# psqlpsql: 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=# \diDid 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=# \cYou 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-releaseCentOS 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 MTransaction Summary================================================================================================================Remove  3 PackagesInstalled size: 31 MIs 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: fastestmirrorNo such command: rm. Please use /usr/bin/yum --help[root@localhost xander]# yum remove postgresql14-server-14.7-1PGDG.rhel7.x86_64Loaded plugins: fastestmirrorResolving Dependencies--> Running transaction check---> Package postgresql14-server.x86_64 0:14.7-1PGDG.rhel7 will be erased--> Finished Dependency ResolutionDependencies Resolved================================================================================================================ Package                          Arch                Version                        Repository            Size================================================================================================================Removing: postgresql14-server              x86_64              14.7-1PGDG.rhel7               @pgdg14               22 MTransaction Summary================================================================================================================Remove  1 PackageInstalled size: 22 MIs 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 0drwxr-xr-x 2 postgres postgres 6 Jun 26 01:19 logsdrwxr-xr-x 2 postgres postgres 6 Jun 26 01:19 pgdatadrwxr-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-pythonchecking build system type... x86_64-pc-linux-gnuchecking host system type... x86_64-pc-linux-gnuchecking which template to use... linuxchecking whether NLS is wanted... nochecking for default port number... 5432checking for block size... 8kBchecking for segment size... 1GBchecking for WAL block size... 8kBchecking for gcc... nochecking for cc... noconfigure: 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.3checking for Perl archlibexp... /usr/lib64/perl5checking for Perl privlibexp... /usr/share/perl5checking for Perl useshrplib... truechecking 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=64checking 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.noconfigure: error: could not determine flags for linking embedded Perl.This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is notinstalled.

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

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.10wget 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 datayum -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/python3ln -s /usr/local/python3/bin/pip3.7 /usr/bin/pip3# 查看Python3和pip3python3pip3 --version# 装置 pykmippip3 install pykmip

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

[root@localhost Python-3.7.10]# python3Python 3.7.10 (default, Jun 26 2023, 08:43:00) [GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linuxType "help", "copyright", "credits" or "license" for more information.>>> quitUse quit() or Ctrl-D (i.e. EOF) to exit>>> exitUse exit() or Ctrl-D (i.e. EOF) to exit>>> exit();[root@localhost Python-3.7.10]# pip3 --versionpip 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-gnuchecking host system type... x86_64-pc-linux-gnuchecking which template to use... linuxchecking whether NLS is wanted... nochecking for default port number... 5432checking for block size... 8kBchecking for segment size... 1GBchecking for WAL block size... 8kBchecking for gcc... gccchecking whether the C compiler works... yeschecking for C compiler default output file name... a.outchecking for suffix of executables... checking whether we are cross compiling... nochecking for suffix of object files... ochecking whether we are using the GNU C compiler... yeschecking whether gcc accepts -g... yeschecking for gcc option to accept ISO C89... none neededchecking for gcc option to accept ISO C99... -std=gnu99checking for g++... g++checking whether we are using the GNU C++ compiler... yeschecking whether g++ accepts -g... yeschecking for gawk... gawkchecking whether gcc -std=gnu99 supports -Wdeclaration-after-statement, for CFLAGS... yeschecking whether gcc -std=gnu99 supports -Werror=vla, for CFLAGS... yeschecking whether gcc -std=gnu99 supports -Werror=unguarded-availability-new, for CFLAGS... nochecking whether g++ supports -Werror=unguarded-availability-new, for CXXFLAGS... nochecking whether gcc -std=gnu99 supports -Wendif-labels, for CFLAGS... yeschecking whether g++ supports -Wendif-labels, for CXXFLAGS... yeschecking whether gcc -std=gnu99 supports -Wmissing-format-attribute, for CFLAGS... yeschecking whether g++ supports -Wmissing-format-attribute, for CXXFLAGS... yeschecking whether gcc -std=gnu99 supports -Wimplicit-fallthrough=3, for CFLAGS... nochecking whether g++ supports -Wimplicit-fallthrough=3, for CXXFLAGS... nochecking whether gcc -std=gnu99 supports -Wcast-function-type, for CFLAGS... nochecking whether g++ supports -Wcast-function-type, for CXXFLAGS... nochecking whether gcc -std=gnu99 supports -Wformat-security, for CFLAGS... yeschecking whether g++ supports -Wformat-security, for CXXFLAGS... yeschecking whether gcc -std=gnu99 supports -fno-strict-aliasing, for CFLAGS... yeschecking whether g++ supports -fno-strict-aliasing, for CXXFLAGS... yeschecking whether gcc -std=gnu99 supports -fwrapv, for CFLAGS... yeschecking whether g++ supports -fwrapv, for CXXFLAGS... yeschecking whether gcc -std=gnu99 supports -fexcess-precision=standard, for CFLAGS... yeschecking whether g++ supports -fexcess-precision=standard, for CXXFLAGS... nochecking whether gcc -std=gnu99 supports -funroll-loops, for CFLAGS_UNROLL_LOOPS... yeschecking whether gcc -std=gnu99 supports -ftree-vectorize, for CFLAGS_VECTORIZE... yeschecking whether gcc -std=gnu99 supports -Wunused-command-line-argument, for NOT_THE_CFLAGS... nochecking whether gcc -std=gnu99 supports -Wcompound-token-split-by-macro, for NOT_THE_CFLAGS... nochecking whether gcc -std=gnu99 supports -Wdeprecated-non-prototype, for NOT_THE_CFLAGS... nochecking whether gcc -std=gnu99 supports -Wformat-truncation, for NOT_THE_CFLAGS... nochecking whether gcc -std=gnu99 supports -Wstringop-truncation, for NOT_THE_CFLAGS... nochecking whether the C compiler still works... yeschecking how to run the C preprocessor... gcc -std=gnu99 -Echecking for pkg-config... /usr/bin/pkg-configchecking pkg-config is at least version 0.9.0... yeschecking allow thread-safe client libraries... yeschecking whether to build with ICU support... nochecking whether to build with Tcl... nochecking whether to build Perl modules... yeschecking whether to build Python modules... yeschecking whether to build with GSSAPI support... nochecking whether to build with PAM support... nochecking whether to build with BSD Authentication support... nochecking whether to build with LDAP support... nochecking whether to build with Bonjour support... nochecking whether to build with SELinux support... nochecking whether to build with systemd support... nochecking whether to build with XML support... nochecking whether to build with LZ4 support... nochecking whether to build with ZSTD support... nochecking for ld used by GCC... /usr/bin/ldchecking if the linker (/usr/bin/ld) is GNU ld... yeschecking for ranlib... ranlibchecking for strip... stripchecking whether it is possible to strip libraries... yeschecking for ar... archecking for a BSD-compatible install... /usr/bin/install -cchecking for tar... /usr/bin/tarchecking whether ln -s works... yeschecking for a thread-safe mkdir -p... /usr/bin/mkdir -pchecking for bison... noconfigure: 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... noconfigure: 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/perlconfigure: using perl 5.16.3checking for Perl archlibexp... /usr/lib64/perl5checking for Perl privlibexp... /usr/share/perl5checking for Perl useshrplib... truechecking 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=64checking 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 -lcchecking for python3... /usr/local/bin/python3configure: using python 3.7.10 (default, Jun 26 2023, 08:43:00) checking for Python sysconfig module... yeschecking Python configuration directory... /usr/local/lib/python3.7/config-3.7m-x86_64-linux-gnuchecking Python include directory... -I/usr/local/include/python3.7mchecking how to link an embedded Python application... configure: error: could not find shared library for PythonYou might have to rebuild your Python installation.  Refer to thedocumentation for details.  Use --without-python to disable buildingPL/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... noconfigure: 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_regressmake[2]: Leaving directory `/home/postgres/postgresql-15.3/src/test/isolation'make -C test/perl allmake[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 allmake[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）。

#postgresexport PATH=/opt/postgres/pgsql15.2/bin:$PATHexport LD_LIBRARY_PATH=/opt/postgres/pgsql15.2/lib:$LD_LIBRARY_PATHexport 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]$ initdbThe 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 ... okcreating subdirectories ... okselecting dynamic shared memory implementation ... posixselecting default max_connections ... 100selecting default shared_buffers ... 128MBselecting default time zone ... America/New_Yorkcreating configuration files ... okrunning bootstrap script ... okperforming post-bootstrap initialization ... oksyncing data to disk ... okinitdb: warning: enabling "trust" authentication for local connectionsinitdb: 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 startwaiting for server to start..../bin/sh: /opt/postgres/logs: Is a directory stopped waitingpg_ctl: could not start serverExamine 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 startwaiting for server to start.... doneserver 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-bit2023-06-26 09:47:06.829 EDT [106274] LOG:  listening on IPv6 address "::1", port 54322023-06-26 09:47:06.829 EDT [106274] LOG:  listening on IPv4 address "127.0.0.1", port 54322023-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 EDT2023-06-26 09:47:06.833 EDT [106274] LOG:  database system is ready to accept connections

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

[postgres@localhost postgres]$ psqlpsql (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