Linux系统安装PostgreSQL数据库及配置的详细过程

目录

• 一、PostgreSQL介绍
• 1.PostgreSQL简介
• 2.PostgreSQL特点
• 二、本次实践介绍
• 1.本次实践介绍
• 2.实践环境介绍
• 三、配置PostgreSQL的yum仓库源
• 1.检查本地是否部署PostgreSQL
• 2.配置镜像源
• 3.检查yum仓库镜像源状态
• 四、安装PostgreSQL
• 1.安装PostgreSQL
• 2.初始化数据库
• 3.启动PostgreSQL数据库
• 4.检查PostgreSQL数据库状态
• 五、PostgreSQL的基本命令
• 1.查看PostgreSQL相关文件
• 2.PostgreSQL的相关命令
• 六、修改用户密码
• 1.切换postgres用户
• 2.登录PostgreSQL数据库
• 3.修改PostgreSQL数据库用户密码
• 七、配置PostgreSQL远程访问
• 1.配置远程访问
• 2.重启PostgreSQL服务
• 3.远程连接PostgreSQL数据库
• 总结

一、PostgreSQL介绍

1.PostgreSQL简介

PostgreSQL (也叫 Postgres) 是一个自由的对象 – 关系数据库服务器 (数据库管理系统)，它在灵活的 BSD – 风格许可证下发行。它提供了相对其他开放源代码数据库系统 (比如 MySQL 和 Firebird)，和对专有系统比如 Oracle、Sybase、IBM 的 DB2 和 Microsoft SQL Server 的一种选择。

2.PostgreSQL特点

• 它是ORDBMS（面向对象的关系数据库管理系统）。
• 外键引用完整性
• 用户自定义的类型
• 表的继承性
• 视图、规则、子查询
• 多版本并发控制(MVCC)
• 复杂的锁定机制
• 异步复制
• 流式复制（从9.0开始）
• 嵌套事务（保存点）

二、本次实践介绍

1.本次实践介绍

从小白视角，以最快速度在LInux系统上部署一个简单的PostgreSQL数据库。

2.实践环境介绍

三、配置PostgreSQL的yum仓库源

1.检查本地是否部署PostgreSQL

检查本地环境是否部署过PostgreSQL

[root@jeven ~]# rpm -qa | grep postgres
[root@jeven ~]#

2.配置镜像源

配置PostgreSQL的yum仓库源，使用的是阿里提供的镜像源。

[root@jeven ~]# rpm -Uvh https://mirrors.aliyun.com/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Retrieving https://mirrors.aliyun.com/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
warning: /var/tmp/rpm-tmp.kcnTVa: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing… ################################# [100%]
Updating / installing…
1:pgdg-redhat-repo-42.0-32 ################################# [100%]
[root@jeven ~]# sed -i \”s@https://download.postgresql.org/pub@https://mirrors.aliyun.com/postgresql@g\” /etc/yum.repos.d/pgdg-redhat-all.repo
[root@jeven ~]#
sed -i \”s@https://download.postgresql.org/pub@https://mirrors.aliyun.com/postgresql@g\” /etc/yum.repos.d/pgdg-redhat-all.repo

生成缓存

yum clean cache
yum makecache

3.检查yum仓库镜像源状态

[root@jeven yum.repos.d]# yum repolist all |grep enable
base/7/x86_64 CentOS-7 – Base – mirr enabled: 10,072
docker-ce-stable/7/x86_64 Docker CE Stable – x86 enabled: 211
elrepo ELRepo.org Community E enabled: 157
epel/x86_64 Extra Packages for Ent enabled: 13,770
extras/7/x86_64 CentOS-7 – Extras – mi enabled: 515
mysql-connectors-community/x86_64 MySQL Connectors Commu enabled: 213
mysql-tools-community/x86_64 MySQL Tools Community enabled: 96
mysql57-community/x86_64 MySQL 5.7 Community Se enabled: 642
pgdg-common/7/x86_64 PostgreSQL common RPMs enabled: 396
pgdg11/7/x86_64 PostgreSQL 11 for RHEL enabled: 1,453
pgdg12/7/x86_64 PostgreSQL 12 for RHEL enabled: 1,071
pgdg13/7/x86_64 PostgreSQL 13 for RHEL enabled: 823
pgdg14/7/x86_64 PostgreSQL 14 for RHEL enabled: 552
pgdg15/7/x86_64 PostgreSQL 15 for RHEL enabled: 263
remi-safe Safe Remi\’s RPM reposi enabled: 5,134
updates/7/x86_64 CentOS-7 – Updates – m enabled: 4,857

四、安装PostgreSQL

1.安装PostgreSQL

使用yum直接PostgreSQL13的版本

[root@jeven yum.repos.d]# yum -y install postgresql13-server
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* elrepo: hkg.mirror.rackspace.com
* remi-safe: ftp.riken.jp
Resolving Dependencies
–> Running transaction check
—> Package postgresql13-server.x86_64 0:13.10-1PGDG.rhel7 will be installed
–> Processing Dependency: postgresql13-libs(x86-64) = 13.10-1PGDG.rhel7 for package: postgresql13-server-13.10-1PGDG.rhel7.x86_64
–> Processing Dependency: postgresql13(x86-64) = 13.10-1PGDG.rhel7 for package: postgresql13-server-13.10-1PGDG.rhel7.x86_64
–> Processing Dependency: libpq.so.5()(64bit) for package: postgresql13-server-13.10-1PGDG.rhel7.x86_64
–> Running transaction check
—> Package postgresql13.x86_64 0:13.10-1PGDG.rhel7 will be installed
—> Package postgresql13-libs.x86_64 0:13.10-1PGDG.rhel7 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================================================================
Package Arch Version Repository Size
==================================================================================================================================================
Installing:
postgresql13-server x86_64 13.10-1PGDG.rhel7 pgdg13 5.4 M
Installing for dependencies:
postgresql13 x86_64 13.10-1PGDG.rhel7 pgdg13 1.4 M
postgresql13-libs x86_64 13.10-1PGDG.rhel7 pgdg13 385 k

Transaction Summary
==================================================================================================================================================
Install 1 Package (+2 Dependent packages)

Total download size: 7.1 M
Installed size: 31 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/pgdg13/packages/postgresql13-libs-13.10-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Public key for postgresql13-libs-13.10-1PGDG.rhel7.x86_64.rpm is not installed
(1/3): postgresql13-libs-13.10-1PGDG.rhel7.x86_64.rpm | 385 kB 00:00:00
(2/3): postgresql13-13.10-1PGDG.rhel7.x86_64.rpm | 1.4 MB 00:00:01
(3/3): postgresql13-server-13.10-1PGDG.rhel7.x86_64.rpm | 5.4 MB 00:00:02
————————————————————————————————————————————————–
Total 2.6 MB/s | 7.1 MB 00:00:02
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-32.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
Installing : postgresql13-libs-13.10-1PGDG.rhel7.x86_64 1/3
Installing : postgresql13-13.10-1PGDG.rhel7.x86_64 2/3
Installing : postgresql13-server-13.10-1PGDG.rhel7.x86_64 3/3
Verifying : postgresql13-server-13.10-1PGDG.rhel7.x86_64 1/3
Verifying : postgresql13-13.10-1PGDG.rhel7.x86_64 2/3
Verifying : postgresql13-libs-13.10-1PGDG.rhel7.x86_64 3/3

Installed:
postgresql13-server.x86_64 0:13.10-1PGDG.rhel7

Dependency Installed:
postgresql13.x86_64 0:13.10-1PGDG.rhel7 postgresql13-libs.x86_64 0:13.10-1PGDG.rhel7

Complete!

2.初始化数据库

初始化PostgreSQL数据库

[root@jeven ~]# postgresql-13-setup initdb
Initializing database … OK

3.启动PostgreSQL数据库

启动PostgreSQL数据库，并设置开机自启。

[root@jeven ~]# systemctl enable postgresql-13
[root@jeven ~]# systemctl start postgresql-13
[root@jeven ~]#

4.检查PostgreSQL数据库状态

检查当前PostgreSQL服务状态

[root@jeven ~]# systemctl status postgresql-13.service
● postgresql-13.service – PostgreSQL 13 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2023-03-27 21:07:33 CST; 1min 34s ago
Docs: https://www.postgresql.org/docs/13/static/
Process: 13256 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 13264 (postmaster)
Tasks: 8
Memory: 17.6M
CGroup: /system.slice/postgresql-13.service
├─13264 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
├─13266 postgres: logger
├─13268 postgres: checkpointer
├─13269 postgres: background writer
├─13270 postgres: walwriter
├─13271 postgres: autovacuum launcher
├─13272 postgres: stats collector
└─13273 postgres: logical replication launcher

Mar 27 21:07:33 jeven systemd[1]: Starting PostgreSQL 13 database server…
Mar 27 21:07:33 jeven postmaster[13264]: 2023-03-27 21:07:33.325 CST [13264] LOG: redirecting log output to logging collector process
Mar 27 21:07:33 jeven postmaster[13264]: 2023-03-27 21:07:33.325 CST [13264] HINT: Future log output will appear in directory \”log\”.
Mar 27 21:07:33 jeven systemd[1]: Started PostgreSQL 13 database server.

五、PostgreSQL的基本命令

1.查看PostgreSQL相关文件

检查PostgreSQL服务端的所有相关文件所在目录

rpm -ql postgresql13-server

检查PostgreSQL客户端的所有相关文件所在目录

rpm -ql postgresql13

2.PostgreSQL的相关命令

PostgreSQL的所有相关命令文件

[root@jeven ~]# /usr/pgsql-13/bin/
clusterdb pg_basebackup pg_dumpall pg_test_timing postmaster
createdb pgbench pg_isready pg_upgrade psql
createuser pg_checksums pg_receivewal pg_verifybackup reindexdb
dropdb pg_config pg_resetwal pg_waldump vacuumdb
dropuser pg_controldata pg_restore postgres
initdb pg_ctl pg_rewind postgresql-13-check-db-dir
pg_archivecleanup pg_dump pg_test_fsync postgresql-13-setup

六、修改用户密码

1.切换postgres用户

切换postgres用户

[root@jeven ~]# su – postgres
-bash-4.2$

2.登录PostgreSQL数据库

登录后，提示符变为 ‘postgres=#’

[root@jeven ~]# su – postgres
-bash-4.2$ psql -U postgres
psql (13.10)
Type \”help\” for help.

postgres=#

3.修改PostgreSQL数据库用户密码

修改PostgreSQL数据库用户密码

postgres=# ALTER USER postgres WITH PASSWORD \’postgres\’;
ALTER ROLE

七、配置PostgreSQL远程访问

1.配置远程访问

修改/var/lib/pgsql/13/data/postgresql.conf配置文件，开启远程访问。删除localhost，改为监听所有，或者自己指定的服务器IP地址。

-bash-4.2$ grep listen /var/lib/pgsql/13/data/postgresql.conf
listen_addresses = \’*\’ # what IP address(es) to listen on;

修改/var/lib/pgsql/13/data/pg_hba.conf文件，新增以下两行：

host all all 127.0.0.1/32 ident
host all all 0.0.0.0/0 md5

2.重启PostgreSQL服务

重启PostgreSQL服务

systemctl restart postgresql-13.service

3.远程连接PostgreSQL数据库

使用psql命令远程连接PostgreSQL数据库

[root@jeven ~]# psql -h192.168.3.166 -Upostgres -W
Password:
psql (13.10)
Type \”help\” for help.

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)

postgres=#

总结

到此这篇关于Linux系统安装PostgreSQL数据库及配置的详细过程的文章就介绍到这了,更多相关Linux安装PostgreSQL数据库内容请搜索悠久资源以前的文章或继续浏览下面的相关文章希望大家以后多多支持悠久资源！