PostgreSQL 入门

我就是我 2023-10-15 22:24 10阅读 0赞

#### 文章目录 ####

*  PostgreSQL介绍
 *  PostgreSQL和MySQL的区别
 *  PostgreSQL的安装
 *  PostgreSQL的配置
 *   *  远程连接配置
     *  配置数据库的日志
 *  PostgreSQL基本操作
 *   *  用户操作
     *  权限操作
 *  图形化界面安装
 *  总结

## PostgreSQL介绍 ##

PostgreSQL是一个功能强大的 **开源** 的关系型数据库，底层基于C实现。其开源协议和Linux内核版本的开源协议是一样的，简单来说就是：可以对PostgreSQL进行一些封装，然后商业化是收费。

PostgreSQL之前叫Ingres，后面为了解决一些ingres中的一些问题，作为后面的ingres，就起名叫PostgreSQL。

PostgreSQL版本迭代的速度比较快，现在最新的正式的发布版本，已经到了15.RELEASE。

PostgreSQL的版本选择一般有两种：

*  如果为了稳定的运行，推荐使用12.x版本。
 *  如果想体验新特性，推荐使用14.x版本。

PostgreSQL的官方地址：[https://www.postgresql.org/][https_www.postgresql.org]

PostgreSQL的国内社区：[http://www.postgres.cn/v2/home][http_www.postgres.cn_v2_home]

## PostgreSQL和MySQL的区别 ##

技术没有好坏之分，只是看一下是否符合你的业务，能否解决你的业务需求。其次也要查看社区的活跃度以及更新的频次。

MySQL不支持的几点内容：

*  MySQL的数据类型不够丰富。
 *  MySQL不支持序列概念，Sequence。
 *  使用MySQL时，网上比较好用的插件。
 *  MySQL的性能优化监控工具不是很多，定位问题的成本是比较高。
 *  MySQL的主从复制没有一个官方的同步策略，同步问题难以解决。
 *  MySQL虽然开源，但是不够彻底。

PostgreSQL相对MySQL上述问题的特点：

*  PostgreSQL的数据类型丰富。
 *  PostgreSQL是有序列的概念的。
 *  PostgreSQL的插件特别丰富。
 *  PostgreSQL支持主从复制的同步操作，可以实现数据的0丢失。
 *  PostgreSQL的MVCC实现和MySQL不大一样。PostgreSQL一行数据会存储多个版本。最多可以存储40亿个事务版本。

## PostgreSQL的安装 ##

推荐在Linux中安装，Linux的版本尽量使用7.x版本，最好是7.6或者是7.8版本。根据官网的引导进行安装：

![image.png][]

填写版本、平台等信息。拿到命令直接执行

![image.png][image.png 1]

# 下载PGSQL的rpm包
    sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    # 安装PGSQL12的软件程序，需要下载，需要等一会，一般不会失败，即便失败，他也会重新帮你找镜像
    sudo yum install -y postgresql12-server
    # 数据库初始化
    sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
    # 设置开启启动项，并设置为开启自行启动
    sudo systemctl enable postgresql-12
    # 启动PGSQL
    sudo systemctl start postgresql-12

这种方式基本不会出错。如果出错，可能是以下几个问题：

*  Linux没有连接外网
 *  Linux中的5432端口，可能被占用了

PostgreSQL不推荐使用root管理，在安装成功后，默认会创建一个用户：postgres ，进行操作时切换到该账户

su postgres

直接输入psql即可进入到postgreSQL提供的客户端

# 进入命令行
    psql
    # 查看有哪些库，如果是新安装的，有三个库，一个是postgres，template0，template1
    \l

不推荐下载Windows版本去玩

如果非要下载：[https://sbp.enterprisedb.com/getfile.jsp?fileid=1258242][https_sbp.enterprisedb.com_getfile.jsp_fileid_1258242]

## PostgreSQL的配置 ##

要关注两个配置信息，一个关于PostgreSQL的远程连接配置以及postgreSQL的日志配置。

PostgreSQL的主要配置放在数据目录下的， **postgresql.conf** 以及 **pg\_hba.conf** 配置文件

这些配置文件都放在了这个目录下

/var/lib/pgsql/12/data

![image.png][image.png 2]

上图可以看到，postgreSQL的核心文件，都属于postgres用户，操作的时候，尽可能的别用root用户，容易出坑，尽可能先切换到postgres用户。

### 远程连接配置 ###

PostgreSQL默认情况下不支持远程连接的，这个跟MySQL几乎一样

*  MySQL给mysql.user追加用户，一般是采用grant的命令。
 *  PostgreSQL要基于配置文件修改，才能制定用户是否可以远程连接。

直接去修改`pg_hba.conf`配置文件

用户以及对应数据库和连接方式的编写模板

![image.png][image.png 3]

local：代表本地连接，host代表可以指定连接的ADDRESS
    database编写数据库名，如果写all，代表所有库都可以连接
    user编写连接的用户，可以写all，代表所有用户
    address代表那些IP地址可以连接
    method加密方式，这块不用过多关注，直接md5
    # 如果为了省事，直接来个痛快的配置，允许任意地址的全部用户连接所有数据库
    host    all             all             0.0.0.0/0               md5

![image.png][image.png 4]

为了实现远程连接，除了用户级别的这种配置，还要针对服务级别修改一个配置

服务级别的配置在postgresql.conf

![image.png][image.png 5]发现默认情况下，PGSQL只允许localhost连接，直接配置为`*`即可解决问题

![image.png][image.png 6]

为了生效，一定要重启

# postgres密码不管，直接root用户
    sudo systemctl restart postgresql-12

### 配置数据库的日志 ###

查看postgresql.conf文件

![image.png][image.png 7]

postgreSQL默认情况下，只保存7天的日志，循环覆盖。

# 代表日志是开启的。
    logging_collector = on
    # 日志存放的路径，默认放到当前目录下的log里
    log_directory = 'log'
    # 日志的文件名，默认是postgresql为前缀，星期作为后缀
    log_filename = 'postgresql-%a.log'
    # 默认一周过后，日志文件会被覆盖
    log_truncate_on_rotation = on
    # 一天一个日志文件
    log_rotation_age = 1d
    # 一个日志文件，没有大小限制
    log_rotation_size = 0

## PostgreSQL基本操作 ##

只在psql命令行（客户端）下，执行了一次\\l，查看了所有的库信息

可以直接基于psql查看一些信息，也可以基于psql进入到命令行后，再做具体操作

![image.png][image.png 8] 命令不用背，需要使用的时候，直接找帮助文档，在psql命令行中，直接输入

\help，即可查看到数据库级别的一些命令
    \?，可以查看到服务级别的一些命令

### 用户操作 ###

构建用户命令

# 区别就是create user默认有连接权限，create role没有，不过可以基于选项去设置
    CREATE USER 名称 [ [ WITH ] 选项 [ ... ] ]
    create role 名称 [ [ WITH ] 选项 [ ... ] ]

构建一个超级管理员用户

create user root with SUPERUSER PASSWORD 'root';

![image.png][image.png 9]

编写psql命令尝试去用root用户登录

psql -h 192.168.11.32 -p 5432 -U root -W

这时候会发现光有用户不让登录，得让用户有一个数据库，直接构建一个root库

create database root;

### 权限操作 ###

权限操作前，要先掌握一下PostgreSQL的逻辑结构

<table> 
 <thead> 
  <tr> 
   <th>逻辑结构图</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><img src="https://img-blog.csdnimg.cn/img_convert/b4cc96406b7f7c1293ab8f150ffd5b07.png" alt="image.png"></td> 
  </tr> 
 </tbody> 
</table>

可以看到PostgreSQL一个数据库中有多个schema，在每个schema下都有自己的相应的库表信息，权限粒度会比MySQL更细一些。

在PGSQL中，权限的管理分为很多多层

> server、cluster、tablespace级别：这个级别一般是基于pg\_hba.conf去配置
> 
> database级别：通过命令级别操作，grant
> 
> namespace、schema级别：玩的不多……不去多了解这个~~
> 
> 对象级别：通过grant命令去设置

后面如果需要对database或者是对象级别做权限控制，直接基于grant命令去操作即可

# 查看grant命令
    \help grant

## 图形化界面安装 ##

图形化界面可以连接PGSQL的很多，Navicat（收费），也可以直接使用PostgreSQL官方提供的图形化界面（完全免费）。

下载[官方][Link 1]提供的各操作系统的版本，进行傻瓜式安装

![image.png][image.png 10]

添加一个新的连接

![image.png][image.png 11]

## 总结 ##

PostgreSQL其实在国外使用的比较多，国内暂时还是以MySQL为主。但是国内很多国产数据库都是基于PostgreSQL做的二次封装，比如：华为GaussDB还有腾讯的Tbase等等。很多公司原来玩的Oracle，直接平转到PostgreSQL，而且国内的很多云产品都支持PostgreSQL了。

因为开源，有很多做数据迁移的工具，可以让你快速的从MySQL，SQLServer，Oracle直接平转到PostgreSQL中内部，比如pgloader这样的数据迁移工具。

[https_www.postgresql.org]: https://www.postgresql.org/
[http_www.postgres.cn_v2_home]: http://www.postgres.cn/v2/home
[image.png]: https://img-blog.csdnimg.cn/img_convert/eed7ba6b4fbc17a25f99970e7bfb6f2c.png
[image.png 1]: https://img-blog.csdnimg.cn/img_convert/52738c9058fcffa622b3cb161aaa69c8.png
[https_sbp.enterprisedb.com_getfile.jsp_fileid_1258242]: https://sbp.enterprisedb.com/getfile.jsp?fileid=1258242
[image.png 2]: https://img-blog.csdnimg.cn/img_convert/7686f4a5956d756bccc982fcb87a1a63.png
[image.png 3]: https://img-blog.csdnimg.cn/img_convert/f6eab1e3acad3db3e1ec84b36f360835.png
[image.png 4]: https://img-blog.csdnimg.cn/img_convert/d84d9262344ab538b727002776651a85.png
[image.png 5]: https://img-blog.csdnimg.cn/img_convert/0ced74defee943c2317ce1d11f9981fc.png
[image.png 6]: https://img-blog.csdnimg.cn/img_convert/685e4684374ca57ed7495ead8cf77c33.png
[image.png 7]: https://img-blog.csdnimg.cn/img_convert/2e61be2df0f00acdf10f43590ccaf7b0.png
[image.png 8]: https://img-blog.csdnimg.cn/img_convert/8a040c1bce22bea7e41d0151a5887a6f.png
[image.png 9]: https://img-blog.csdnimg.cn/img_convert/09222cc906eb15482989bc206a891ec3.png
[Link 1]: https://www.pgadmin.org/download/
[image.png 10]: https://img-blog.csdnimg.cn/img_convert/806acfd8690380630755b5fed7e5e3d7.png
[image.png 11]: https://img-blog.csdnimg.cn/img_convert/658742678fc70758dfe2ff75034002a1.png