Linux系统之安装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.实践环境介绍

hostnameIP地址操作系统版本PostgreSQL版本jeven192.168.3.166centos 7.613.10

三、配置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 11for RHEL enabled:  1,453
pgdg12/7/x86_64                           PostgreSQL 12for RHEL enabled:  1,071
pgdg13/7/x86_64                           PostgreSQL 13for RHEL enabled:    823
pgdg14/7/x86_64                           PostgreSQL 14for RHEL enabled:    552
pgdg15/7/x86_64                           PostgreSQL 15for 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 <[email protected]>"
 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: 13256ExecStartPre=/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 2721:07:33 jeven systemd[1]: Starting PostgreSQL 13 database server...
Mar 2721:07:33 jeven postmaster[13264]: 2023-03-27 21:07:33.325 CST [13264] LOG:  redirecting log output to logging collector process
Mar 2721:07:33 jeven postmaster[13264]: 2023-03-27 21:07:33.325 CST [13264] HINT:  Future log output will appear in directory "log".
Mar 2721: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

本文转载自: https://blog.csdn.net/jks212454/article/details/129801784
版权归原作者 江湖有缘 所有，如有侵权，请联系我们删除。

Linux系统之安装PostgreSQL数据库

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数据库

发表评论

“Linux系统之安装PostgreSQL数据库”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航