一个网站的制作流程,帝国建站模板,网站建设费应该怎样入账,定制微信小程序一、前言 千里之行始于足下#xff0c;想学习一门数据库#xff0c;首先要从安装部署开始#xff0c;先拥有一套属于自己的学习测试库。为了更好的学习该数据库#xff0c;可以选择一个在企业界使用率比较普及的操作系统#xff0c;选择稳定版本的操作系统#xff1b;如果…
一、前言 千里之行始于足下想学习一门数据库首先要从安装部署开始先拥有一套属于自己的学习测试库。为了更好的学习该数据库可以选择一个在企业界使用率比较普及的操作系统选择稳定版本的操作系统如果不是为了研究数据库各版本软件差异不是为了钻研最新版本数据库的新特性建议选择一款比较新且稳定成熟的数据库版本软件。 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用户执行如下命令
[roothost19c-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 ResolutionDependencies ResolvedPackage Arch Version Repository SizeInstalling:pgdg-redhat-repo noarch 42.0-35PGDG /pgdg-redhat-repo-latest.noarch 13 kTransaction SummaryInstall 1 PackageTotal 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.6Installing : 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命令查看相关数据库软件
[roothost19c-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-yumpostgresql.orgFingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8Package : 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-yumpostgresql.orgFingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8Package : 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 doesnt have enough cached data to continue. At this point the onlysafe 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 workingupstream. This is most often useful if you are using a newerdistribution release than is supported by the repository (and thepackages for the previous distribution release still work).3. Run the command with the repository temporarily disabledyum --disablerepopgdg14 ...4. Disable the repository permanently, so yum wont use it by default. Yumwill then just ignore the repository until you permanently enable itagain or use --enablerepo for temporary usage:yum-config-manager --disable pgdg14orsubscription-manager repos --disablepgdg145. 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 muchslower). If it is a very temporary problem though, this is often a nicecompromise:yum-config-manager --save --setoptpgdg14.skip_if_unavailabletruefailure: 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命令。
[roothost19c-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-yumpostgresql.orgFingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8Package : 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-yumpostgresql.orgFingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8Package : 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 ResolutionDependencies ResolvedPackage Arch Version Repository SizeInstalling: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 Mpostgresql15-libs x86_64 15.5-1PGDG.rhel7 pgdg15 284 kTransaction SummaryInstall 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-yumpostgresql.orgFingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8Package : 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 transactionInstalling : 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目录下。
[roothost19c-node1 pgsql-15]# pwd
/usr/pgsql-15
[roothost19c-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
[roothost19c-node1 ~]# sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
-- 上述命令执行结果如下
Initializing database ... OK# 可以进行如下配置设置服务器重启后数据库自启动。# 使用root用户执行systemctl enable postgresql-15
[roothost19c-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启动数据库
[roothost19c-node1 ~]# sudo systemctl start postgresql-15# 使用systemctl status postgresql-15命令查看数据库启动状态
[roothost19c-node1 ~]# sudo systemctl status postgresql-15
● postgresql-15.service - PostgreSQL 15 database serverLoaded: 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 agoDocs: https://www.postgresql.org/docs/15/static/Process: 35602 ExecStartPre/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (codeexited, status0/SUCCESS)Main PID: 35609 (postmaster)Tasks: 7Memory: 15.0MCGroup: /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/目录下相关信息如下
[roothost19c-node1 ~]# cd /var/lib/pgsql/15/
[roothost19c-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
[roothost19c-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用户
[roothost19c-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# \lList of databasesName | 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 | | | | | | | postgresCTc/postgrestemplate1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | c/postgres | | | | | | | postgresCTc/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, user1example.com),
pg15-# (user2, user2example.com);INSERT 0 2
pg15#
pg15# select * from pgtb;id | username | email | created_at
-------------------------------------------------------------1 | user1 | user1example.com | 2023-12-27 16:37:16.2188112 | user2 | user2example.com | 2023-12-27 16:37:16.218811
(2 rows)pg15# 万里长征已开启第一步现在你已拥有了一个PG数据库请开启你的PG学习之旅吧。
五、卸载数据库
卸载使用YUM安装的数据库也比较简单可以采用yum remove postgresql15-server卸载安装的数据库。
[roothost19c-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 ResolutionDependencies ResolvedPackage Arch Version Repository SizeRemoving: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 MTransaction SummaryRemove 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 transactionErasing : 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!
