学习postgresql可以来这

最新推荐文章于 2022-04-17 17:33:29 发布

A｀空巷

最新推荐文章于 2022-04-17 17:33:29 发布

阅读量631

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_43658155/article/details/109119558

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

postgresql

它的sql语句和mysql的都一样这里没做介绍

文章目录

postgresql
postgresql

PostgresSQL与MySQL 的区别

按照SQL标准，null判断只能用is null，不能用 ==null

pg 可以设置 transform_null_equals 吧 = null 翻译成 is null 避免踩坑
mysql的text字段有不同的限制，需要手动区分small text，middle text，large text

pg没有该限制，可以支持text的各种大小。
Mysql的事务隔离级别 repeatable read 不能阻止常见的并发更新，得加锁才可以，但悲观锁会影响性能，手动实现乐观锁。而pg的列里有隐藏的乐观锁 version 字段，默认的 repeatable read 级别就能保证并发更新的正确性，并且又有乐观锁的性能，附带一个各数据库对隔离级别的行为差异比较调查。
MySQL不支持多个表从同一个序列中取 id ，而pg可以
MySQL 不支持 over 子句，而pg支持，over子句能简单的解决“每组取top 5”，几乎任何数据库的子查询（subquery）性能都比 MySQL 好
pg它可以存储 array 和 json，可以在 array 和 json 上建索引，甚至还能用表达式索引，为了实现文档数据库的功能, 设计了 jsonb 的存储结构. 有人会说为什么不用 Mongodb 的 BSON 呢? Pg 的开发团队曾经考虑过, 但是他们看到 BSON 把 [“a”, “b”, “c”] 存成 {0: “a”, 1: “b”, 2: “c”} 的时候就决定要重新做一个 jsonb 了… 现在 jsonb 的性能已经优于 BSON.

pg优势

pg性能优于mysql（有人说三条街，哈哈哈）相同的数据量计算，pg时间一般只有mysql的四分之一左右（具体情况可能有所不同）
pg对json支持比较好，还有fdw功能，可以吧别的数据库的表当自己的用
任何系统都有它的性能极限，在高并发读写，负载逼近极限下，PG的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降，而 MySQL 明显出现一个波峰后下滑（5.5版本之后，在企业级版本中有个插件可以改善很多，不过需要付费）。
PG 多年来在 GIS 领域处于优势地位，因为它有丰富的几何类型，实际上不止几何类型，PG有大量字典、数组、bitmap 等数据类型，相比之下mysql就差很多，instagram就是因为PG的空间数据库扩展POSTGIS远远强于MYSQL的my spatial而采用PGSQL的。
PG 的“无锁定”特性非常突出，甚至包括 vacuum 这样的整理数据空间的操作，这个和PGSQL的MVCC实现有关系。五、PG 的可以使用函数和条件索引，这使得PG数据库的调优非常灵活，mysql就没有这个功能，条件索引在web应用中很重要。
PG有极其强悍的 SQL 编程能力（9.x 图灵完备，支持递归！），有非常丰富的统计函数和统计语法支持，比如分析函数（ORACLE的叫法，PG里叫window函数），还可以用多种语言来写存储过程，对于R的支持也很好。这一点上MYSQL就差的很远，很多分析功能都不支持，腾讯内部数据存储主要是MYSQL，但是数据分析主要是HADOOP+PGSQL（听李元佳说过，但是没有验证过）。
PG 的有多种集群架构可以选择，plproxy 可以支持语句级的镜像或分片，slony 可以进行字段级的同步设置，standby 可以构建WAL文件级或流式的读写分离集群，同步频率和集群策略调整方便，操作非常简单。
一般关系型数据库的字符串有限定长度8k左右，无限长 TEXT 类型的功能受限，只能作为外部大数据访问。而 PG 的 TEXT 类型可以直接访问，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。用PG的话，文档数据库都可以省了。

缺点

MySQL有一些实用的运维支持，如 slow-query.log ，这个pg肯定可以定制出来，但是如果可以配置使用就更好了
mysql的innodb引擎，可以充分优化利用系统所有内存，超大内存下PG对内存使用的不那么充分
MySQL的复制可以用多级从库，但是在9.2之前，PGSQL不能用从库带从库。
从测试结果上看，mysql 5.5的性能提升很大，单机性能强于pgsql，5.6应该会强更多.第五点，对于web应用来说,mysql 5.6 的内置MC API功能很好用，PGSQL差一些。

安装

CentOS7安装并配置PostgreSQL

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql96-server postgresql96-contrib
/usr/pgsql-9.6/bin/postgresql96-setup initdb
systemctl start postgresql-9.6
systemctl enable postgresql-9.6
firewall-cmd --add-service=postgresql --permanent
firewall-cmd --reload
su - postgres
psql -U postgres
ALTER USER postgres with encrypted password 'abc123';
\q
exit
vi /var/lib/pgsql/9.6/data/postgresql.conf
vi /var/lib/pgsql/9.6/data/pg_hba.conf
systemctl restart postgresql-9.6.service

安装yum源

打开https://yum.postgresql.org/repopackages.php ，找到自己需要的版本。

以root模式进入CentOS7，输入yum install后面加上刚刚复制的链接，回车。

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

安装PostgreSQL

yum install -y postgresql96-server postgresql96-contrib
// 如果使用其他版本的PostgreSQL则需要把其中的两个96换成对应的数字

输入/usr/pgsql-9.6/bin/postgresql96-setup initdb并回车，初始化数据库。
（如果使用其他版本的PostgreSQL则需要把其中的9.6和96换成对应的数字）

输入systemctl start postgresql-9.6并回车，启动服务。
输入systemctl enable postgresql-9.6并回车，设为开机自启。
（如果使用其他版本的PostgreSQL则需要把其中的两个9.6换成对应的版本）

（如果未安装firewalld防火墙可跳过下面两步）
输入firewall-cmd --add-service=postgresql --permanent并回车，开放防火墙。
输入firewall-cmd --reload并回车，重启防火墙。

修改默认PostgreSQL用户密码
PostgreSQL安装后会创建一个用户，名为postgres。
输入su - postgres并回车，切换至用户。
输入psql -U postgres并回车，登录数据库。
输入ALTER USER postgres with encrypted password ‘abc123’;（不要漏了“;”）并回车，设置默认用户postgre的密码，此处密码为abc123，可自行修改。
输入\q并回车，退出数据库。
输入exit并回车，退出用户。

配置远程访问

输入vi /var/lib/pgsql/9.6/data/postgresql.conf并回车。
（如果使用其他版本的PostgreSQL则需要把其中的9.6换成对应的版本）
光标下翻，找到listen_addresses。

按i键进入插入编辑模式，如果想对所有IP开放，**则将localhost改为*即可，**如果想仅对部分IP开放，多个IP之间用,（逗号+空格）隔开。
改完之后去掉“listen_address”前面的#。

编辑完成后，按Esc键，输入:wq并回车。
输入vi /var/lib/pgsql/9.6/data/pg_hba.conf并回车，将光标移至底部。
（如果使用其他版本的PostgreSQL则需要把其中的9.6换成对应的版本）

按i键进入插入编辑模式，在IPv4 local connections下方添加允许连接的IP。
如果想允许所有IPv4地址，则加入一行host all all 0.0.0.0/0 md5。IPv6方法类似。

编辑完成后，按Esc键，输入:wq并回车。
输入systemctl restart postgresql-9.6.service并回车，重启服务。
（如果使用其他版本的PostgreSQL则需要把其中的9.6换成对应的版本）

重要！！！

firewall-cmd --zone=public --list-ports

firewall-cmd --zone=public --add-port=5432/tcp --permanent // 5432为postgresql端口

firewall-cmd --reload

SpringBoot集成pg

1、引入依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.3.3.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <version>2.3.3.RELEASE</version>
</dependency>

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.6</version>
</dependency>

2、yaml

spring:
  datasource:
    url: jdbc:postgresql://192.168.128.200:5432/test
    username: postgres
    password: postgres
    driver-class-name: org.postgresql.Driver
mybatis-plus:
  mapper-locations: classpath*:mapper/*.xml
  type-aliases-package: com.cwzn.**.entity
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

3、 entity

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Username {
    private int id;
    private String name;
    private int age;
}

4、mapper

@Mapper
public interface UserMapper extends BaseMapper<Username> {
}

5、xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cwzn.mapper.UserMapper">

</mapper>

6、controller

@RestController
public class UserController {
    @Resource
    private UserMapper userMapper;

    @GetMapping("/user")
    public List<Username> users(){
        return userMapper.selectList(null);
    }
}