一、前言
千里之行始于足下，想学习一门数据库，首先要从安装部署开始，先拥有一套属于自己的学习测试库。为了更好的学习该数据库，可以选择一个在企业界使用率比较普及的操作系统，选择稳定版本的操作系统；如果不是为了研究数据库各版本软件差异，不是为了钻研最新版本数据库的新特性，建议选择一款比较新且稳定成熟的数据库版本软件。
PostGreSQL作为一款优秀的开源数据库，在国内外广受欢迎，被用于很多行业，在国内外也有很多的PG fans。
PostGreSQL安装的方式有多种，比如在Linux或MAC操作系统上，可以选择yum、二进制、源码方式安装。
相对于Windows操作系统，PostGreSQL更多安装在Linux等操作系统上，所以对于Windows如何安装PostGreSQL不在本系列讨论范围内。
作为PostGreSQL安装部署系列篇，本次讲解如何在Linux上使用YUM来安装，本次选择采用Centos 7.9操作系统来验证。
接下来，安装部署，走起。
二、前期准备
2.1 配置YUM源
如果你的Linux能联网，建议选择配置一个YUM源，比如选择使用比较普遍的阿里yum源。如何配置YUM源，大家可以搜下网络介绍，本次不作为分享内容，大家可以自行搜索部署。
2.2 下载软件
登录PostGreSQL官网地址：https://www.postgresql.org/download/，进入到如下界面。
参照上图，选择对应的操作系统，本次选择Linux，然后选择对应的Linux的发行版，本次选择Red Hat/Rocky/AlmaLinux发行版，如下所示。
然后进入到https://www.postgresql.org/download/linux/redhat/对应的页面，然后选择相应的数据库版本、对应的操作系统版本、对应的架构模式，本次选择PostGreSQL 15版本，如下所示。
三、安装部署
根据https://www.postgresql.org/download/linux/redhat/所填写内容，拷贝相应命令分别执行。
3.1 安装仓库RPM包
# 使用root用户执行如下命令：
[root@host19c-node1 ~]# sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
-- 上述命令执行结果如下：
pgdg-redhat-repo-latest.noarch.rpm | 9.0 kB 00:00:00
Examining /var/tmp/yum-root-CYlXMy/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-35PGDG.noarch
Marking /var/tmp/yum-root-CYlXMy/pgdg-redhat-repo-latest.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat-repo.noarch 0:42.0-35PGDG will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-35PGDG /pgdg-redhat-repo-latest.noarch 13 k
Transaction Summary
=============================================================================================================================================================================================================================================
Install 1 Package
Total size: 13 k
Installed size: 13 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
** Found 3 pre-existing rpmdb problem(s), 'yum check' output follows:
R-java-3.6.0-1.el7.x86_64 has missing requires of java-headless
ant-1.9.4-2.el7.noarch has missing requires of java-devel >= ('0', '1.5.0', None)
rlwrap-0.43-2.el7.x86_64 has missing requires of /usr/bin/python3.6
Installing : pgdg-redhat-repo-42.0-35PGDG.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-35PGDG.noarch 1/1
Installed:
pgdg-redhat-repo.noarch 0:42.0-35PGDG
Complete!
上述命令执行完毕后，可以使用yum search postgresql命令来查看安装了哪些PostGreSQL数据库相关软件。
# 使用yum search postgresql命令查看相关数据库软件
[root@host19c-node1 ~]# yum search postgresql
-- 上述命令执行结果如下：
pgdg-common/7/x86_64/signature | 198 B 00:00:00
pgdg-common/7/x86_64/signature | 2.9 kB 00:00:00 !!!
pgdg11/7/x86_64/signature | 198 B 00:00:00
pgdg11/7/x86_64/signature | 3.6 kB 00:00:00 !!!
pgdg12/7/x86_64/signature | 198 B 00:00:00
pgdg12/7/x86_64/signature | 3.6 kB 00:00:00 !!!
pgdg13/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-35PGDG.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
pgdg13/7/x86_64/signature | 3.6 kB 00:00:38 !!!
pgdg14/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-35PGDG.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: N
pgdg14/7/x86_64/signature | 3.6 kB 00:00:12 !!!
https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-7-x86_64/repodata/repomd.xml: [Errno -1] Gpg Keys not imported, cannot verify repomd.xml for repo pgdg14
Trying other mirror.
One of the configured repositories failed (PostgreSQL 14 for RHEL / CentOS 7 - x86_64),
and yum doesn't have enough cached data to continue. At this point the only
safe thing yum can do is fail. There are a few ways to work "fix" this:
1. Contact the upstream for the repository and get them to fix the problem.
2. Reconfigure the baseurl/etc. for the repository, to point to a working
upstream. This is most often useful if you are using a newer
distribution release than is supported by the repository (and the
packages for the previous distribution release still work).
3. Run the command with the repository temporarily disabled
yum --disablerepo=pgdg14 ...
4. Disable the repository permanently, so yum won't use it by default. Yum
will then just ignore the repository until you permanently enable it
again or use --enablerepo for temporary usage:
yum-config-manager --disable pgdg14
or
subscription-manager repos --disable=pgdg14
5. Configure the failing repository to be skipped, if it is unavailable.
Note that yum will try to contact the repo. when it runs most commands,
so will have to try and fail each time (and thus. yum will be be much
slower). If it is a very temporary problem though, this is often a nice
compromise:
yum-config-manager --save --setopt=pgdg14.skip_if_unavailable=true
failure: repodata/repomd.xml from pgdg14: [Errno 256] No more mirrors to try.
https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-7-x86_64/repodata/repomd.xml: [Errno -1] Gpg Keys not imported, cannot verify repomd.xml for repo pgdg14
可以发现在执行yum search postgresql根据提示输入N后，会有failure: repodata/repomd.xml from pgdg14: [Errno 256] No more mirrors to try的报错信息。
根据上述报错查询了网上信息，根据https://www.postgresql.org/message-id/C7C14870-90FE-40B8-93CF-1068B1D43F78%40contoso.com这篇文章内容显示是遇到了一个bug，详细信息如下：
3.2 安装数据库
接下来，安装PostGreSQL 15版本数据库，详细信息如下：
# 使用root用户执行yum install -y postgresql15-server命令。
[root@host19c-node1 ~]# sudo yum install -y postgresql15-server
-- 上述命令执行结果如下：
base | 3.6 kB 00:00:00
epel | 4.7 kB 00:00:00
extras | 2.9 kB 00:00:00
pgdg-common/7/x86_64/signature | 198 B 00:00:00
pgdg-common/7/x86_64/signature | 2.9 kB 00:00:00 !!!
pgdg11/7/x86_64/signature | 198 B 00:00:00
pgdg11/7/x86_64/signature | 3.6 kB 00:00:00 !!!
pgdg12/7/x86_64/signature | 198 B 00:00:00
pgdg12/7/x86_64/signature | 3.6 kB 00:00:00 !!!
pgdg13/7/x86_64/signature | 198 B 00:00:00
pgdg13/7/x86_64/signature | 3.6 kB 00:00:00 !!!
pgdg14/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-35PGDG.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg14/7/x86_64/signature | 3.6 kB 00:00:00 !!!
pgdg15/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-35PGDG.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg15/7/x86_64/signature | 3.6 kB 00:00:00 !!!
updates | 2.9 kB 00:00:00
(1/11): pgdg12/7/x86_64/group_gz | 245 B 00:00:01
(2/11): pgdg13/7/x86_64/group_gz | 246 B 00:00:00
(3/11): pgdg11/7/x86_64/group_gz | 245 B 00:00:01
(4/11): pgdg12/7/x86_64/primary_db | 416 kB 00:00:01
(5/11): pgdg14/7/x86_64/primary_db | 249 kB 00:00:00
(6/11): pgdg11/7/x86_64/primary_db | 524 kB 00:00:02
(7/11): pgdg14/7/x86_64/group_gz | 244 B 00:00:00
(8/11): pgdg15/7/x86_64/group_gz | 246 B 00:00:00
(9/11): pgdg13/7/x86_64/primary_db | 337 kB 00:00:01
(10/11): pgdg15/7/x86_64/primary_db | 156 kB 00:00:00
(11/11): pgdg-common/7/x86_64/primary_db | 203 kB 00:00:02
Resolving Dependencies
--> Running transaction check
---> Package postgresql15-server.x86_64 0:15.5-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql15-libs(x86-64) = 15.5-1PGDG.rhel7 for package: postgresql15-server-15.5-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql15(x86-64) = 15.5-1PGDG.rhel7 for package: postgresql15-server-15.5-1PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql15-server-15.5-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package postgresql15.x86_64 0:15.5-1PGDG.rhel7 will be installed
---> Package postgresql15-libs.x86_64 0:15.5-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================================================================================
Installing:
postgresql15-server x86_64 15.5-1PGDG.rhel7 pgdg15 5.8 M
Installing for dependencies:
postgresql15 x86_64 15.5-1PGDG.rhel7 pgdg15 1.6 M
postgresql15-libs x86_64 15.5-1PGDG.rhel7 pgdg15 284 k
Transaction Summary
=============================================================================================================================================================================================================================================
Install 1 Package (+2 Dependent packages)
Total download size: 7.7 M
Installed size: 34 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/pgdg15/packages/postgresql15-libs-15.5-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY ] 0.0 B/s | 112 kB --:--:-- ETA
Public key for postgresql15-libs-15.5-1PGDG.rhel7.x86_64.rpm is not installed
(1/3): postgresql15-libs-15.5-1PGDG.rhel7.x86_64.rpm | 284 kB 00:00:01
(2/3): postgresql15-server-15.5-1PGDG.rhel7.x86_64.rpm | 5.8 MB 00:00:01
(3/3): postgresql15-15.5-1PGDG.rhel7.x86_64.rpm | 1.6 MB 00:00:05
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 1.3 MB/s | 7.7 MB 00:00:05
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-35PGDG.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql15-libs-15.5-1PGDG.rhel7.x86_64 1/3
Installing : postgresql15-15.5-1PGDG.rhel7.x86_64 2/3
Installing : postgresql15-server-15.5-1PGDG.rhel7.x86_64 3/3
Verifying : postgresql15-15.5-1PGDG.rhel7.x86_64 1/3
Verifying : postgresql15-server-15.5-1PGDG.rhel7.x86_64 2/3
Verifying : postgresql15-libs-15.5-1PGDG.rhel7.x86_64 3/3
Installed:
postgresql15-server.x86_64 0:15.5-1PGDG.rhel7
Dependency Installed:
postgresql15.x86_64 0:15.5-1PGDG.rhel7 postgresql15-libs.x86_64 0:15.5-1PGDG.rhel7
Complete!
3.3 初始化数据库
执行yum install -y postgresql15-server安装数据库，可以查看到数据库的相关命令在/usr/pgsql-15/bin目录下。
[root@host19c-node1 pgsql-15]# pwd
/usr/pgsql-15
[root@host19c-node1 pgsql-15]# ls -lrt
total 12
drwxr-xr-x 2 root root 4096 Dec 27 16:03 bin
drwxr-xr-x 3 root root 4096 Dec 27 16:03 lib
drwxr-xr-x 7 root root 4096 Dec 27 16:03 share
接下来来初始化数据库，操作命令如下：
# 使用root用户执行/usr/pgsql-15/bin/postgresql-15-setup initdb
[root@host19c-node1 ~]# sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
-- 上述命令执行结果如下：
Initializing database ... OK
# 可以进行如下配置设置服务器重启后数据库自启动。
# 使用root用户执行systemctl enable postgresql-15
[root@host19c-node1 ~]# sudo systemctl enable postgresql-15
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-15.service to /usr/lib/systemd/system/postgresql-15.service.
# 使用root用户执行systemctl start postgresql-15启动数据库
[root@host19c-node1 ~]# sudo systemctl start postgresql-15
# 使用systemctl status postgresql-15命令查看数据库启动状态
[root@host19c-node1 ~]# sudo systemctl status postgresql-15
● postgresql-15.service - PostgreSQL 15 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2023-12-27 16:07:39 CST; 8s ago
Docs: https://www.postgresql.org/docs/15/static/
Process: 35602 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 35609 (postmaster)
Tasks: 7
Memory: 15.0M
CGroup: /system.slice/postgresql-15.service
├─35609 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
├─35610 postgres: logger
├─35611 postgres: checkpointer
├─35612 postgres: background writer
├─35614 postgres: walwriter
├─35615 postgres: autovacuum launcher
└─35616 postgres: logical replication launcher
Dec 27 16:07:39 host19c-node1 systemd[1]: Starting PostgreSQL 15 database server...
Dec 27 16:07:39 host19c-node1 postmaster[35609]: 2023-12-27 16:07:39.363 CST [35609] LOG: redirecting log output to logging collector process
Dec 27 16:07:39 host19c-node1 postmaster[35609]: 2023-12-27 16:07:39.363 CST [35609] HINT: Future log output will appear in directory "log".
Dec 27 16:07:39 host19c-node1 systemd[1]: Started PostgreSQL 15 database server.
数据库初始并启动后，可以查看到数据库的默认安装路径在/var/lib/pgsql/15/目录下，相关信息如下：
[root@host19c-node1 ~]# cd /var/lib/pgsql/15/
[root@host19c-node1 15]# ll
total 8
drwx------ 2 postgres postgres 6 Nov 9 04:34 backups
drwx------ 20 postgres postgres 4096 Dec 27 16:07 data
-rw------- 1 postgres postgres 921 Dec 27 16:06 initdb.log
[root@host19c-node1 15]# ls -lrt ./data/
total 68
-rw------- 1 postgres postgres 3 Dec 27 16:06 PG_VERSION
drwx------ 2 postgres postgres 6 Dec 27 16:06 pg_twophase
drwx------ 2 postgres postgres 6 Dec 27 16:06 pg_tblspc
drwx------ 2 postgres postgres 6 Dec 27 16:06 pg_stat_tmp
drwx------ 2 postgres postgres 6 Dec 27 16:06 pg_snapshots
drwx------ 2 postgres postgres 6 Dec 27 16:06 pg_serial
drwx------ 2 postgres postgres 6 Dec 27 16:06 pg_replslot
drwx------ 2 postgres postgres 6 Dec 27 16:06 pg_notify
drwx------ 4 postgres postgres 36 Dec 27 16:06 pg_multixact
drwx------ 2 postgres postgres 6 Dec 27 16:06 pg_dynshmem
drwx------ 2 postgres postgres 6 Dec 27 16:06 pg_commit_ts
-rw------- 1 postgres postgres 29460 Dec 27 16:06 postgresql.conf
-rw------- 1 postgres postgres 88 Dec 27 16:06 postgresql.auto.conf
-rw------- 1 postgres postgres 1636 Dec 27 16:06 pg_ident.conf
-rw------- 1 postgres postgres 4577 Dec 27 16:06 pg_hba.conf
drwx------ 3 postgres postgres 60 Dec 27 16:06 pg_wal
drwx------ 2 postgres postgres 18 Dec 27 16:06 pg_xact
drwx------ 2 postgres postgres 18 Dec 27 16:06 pg_subtrans
drwx------ 5 postgres postgres 33 Dec 27 16:06 base
drwx------ 2 postgres postgres 32 Dec 27 16:07 log
-rw------- 1 postgres postgres 30 Dec 27 16:07 current_logfiles
-rw------- 1 postgres postgres 58 Dec 27 16:07 postmaster.opts
drwx------ 2 postgres postgres 6 Dec 27 16:07 pg_stat
-rw------- 1 postgres postgres 104 Dec 27 16:07 postmaster.pid
drwx------ 2 postgres postgres 4096 Dec 27 16:08 global
drwx------ 4 postgres postgres 68 Dec 27 16:12 pg_logical
四、验证测试
数据库安装后，我们来进行简单的验证测试。
使用yum安装数据库后，默认会创建postgres用户。
# 切换到postgres用户
[root@host19c-node1 ~]# su - postgres
Last login: Wed Dec 27 16:09:13 CST 2023 on pts/5
-bash-4.2$ psql
psql (15.5)
Type "help" for help.
# 查看初始化后的数据库默认库和用户
postgres=# \d
Did not find any relations.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)
# 创建一个测试库
postgres=# CREATE DATABASE pg15;
CREATE DATABASE
postgres=#
postgres=# \c pg15;
You are now connected to database "pg15" as user "postgres".
pg15=# CREATE TABLE pgtb (
pg15(# id SERIAL PRIMARY KEY,
pg15(# username VARCHAR(50) NOT NULL,
pg15(# email VARCHAR(100) NOT NULL UNIQUE,
pg15(# created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
pg15(# );
CREATE TABLE
pg15=#
pg15=# INSERT INTO pgtb (username, email) VALUES
pg15-# ('user1', 'user1@example.com'),
pg15-# ('user2', 'user2@example.com');
INSERT 0 2
pg15=#
pg15=# select * from pgtb;
id | username | email | created_at
----+----------+-------------------+----------------------------
1 | user1 | user1@example.com | 2023-12-27 16:37:16.218811
2 | user2 | user2@example.com | 2023-12-27 16:37:16.218811
(2 rows)
pg15=#
万里长征已开启第一步，现在你已拥有了一个PG数据库，请开启你的PG学习之旅吧。
五、卸载数据库
卸载使用YUM安装的数据库也比较简单，可以采用yum remove postgresql15-server卸载安装的数据库。
[root@host19c-node1 ~]# yum remove postgresql15-server
Resolving Dependencies
--> Running transaction check
---> Package postgresql15-server.x86_64 0:15.5-1PGDG.rhel7 will be erased
--> Processing Dependency: postgresql15-server(x86-64) = 15.5-1PGDG.rhel7 for package: postgresql15-contrib-15.5-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package postgresql15-contrib.x86_64 0:15.5-1PGDG.rhel7 will be erased
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================================================================================
Removing:
postgresql15-server x86_64 15.5-1PGDG.rhel7 @pgdg15 24 M
Removing for dependencies:
postgresql15-contrib x86_64 15.5-1PGDG.rhel7 @pgdg15 2.5 M
Transaction Summary
=============================================================================================================================================================================================================================================
Remove 1 Package (+1 Dependent package)
Installed size: 27 M
Is this ok [y/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Erasing : postgresql15-contrib-15.5-1PGDG.rhel7.x86_64 1/2
Erasing : postgresql15-server-15.5-1PGDG.rhel7.x86_64 2/2
Verifying : postgresql15-contrib-15.5-1PGDG.rhel7.x86_64 1/2
Verifying : postgresql15-server-15.5-1PGDG.rhel7.x86_64 2/2
Removed:
postgresql15-server.x86_64 0:15.5-1PGDG.rhel7
Dependency Removed:
postgresql15-contrib.x86_64 0:15.5-1PGDG.rhel7
Complete!
