Linux系统之安装PostgreSQL数据库

举报
江湖有缘 发表于 2023/04/03 16:28:07 2023/04/03
【摘要】 Linux系统之安装PostgreSQL数据库

@TOC

一、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.实践环境介绍

hostname IP地址 操作系统版本 PostgreSQL版本
jeven 192.168.3.166 centos 7.6

三、配置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’ 
postgres-# 

七、配置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=# 

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。