PostgreSQL数据库学习笔记

最近公司决定使用 PostgreSQL 数据库,提前学习一下。

PostgreSQL简介

PostgreSQL 是一个功能强大的开源对象关系数据库系统,它使用并扩展了 SQL 语言,并结合了许多功能,可以安全地存储和扩展最复杂的数据工作负载。PostgreSQL 的起源可以追溯到 1986 年,是加州大学伯克利分校 POSTGRES 项目的一部分,在核心平台上已经有超过 35 年的积极开发历史。

PostgreSQL 以其成熟的架构、可靠性、数据完整性、强大的功能集、可扩展性以及软件背后的开源社区的奉献精神而赢得了良好的声誉,以始终如一地提供高性能和创新的解决方案。PostgreSQL 可在所有主要操作系统上运行,自 2001 年以来一直符合 ACID 标准,并具有强大的附加组件,例如流行的 PostGIS 地理空间数据库扩展器。毫不奇怪,PostgreSQL已成为许多人和组织的首选开源关系数据库。

PostgreSQL 具有许多功能,旨在帮助开发人员构建应用程序、管理员保护数据完整性和构建容错环境,并帮助您管理数据,无论数据集有多大或多小。除了免费和开源之外,PostgreSQL 还具有高度可扩展性。例如,您可以定义自己的数据类型,构建自定义函数,甚至可以使用不同的编程语言编写代码,而无需重新编译数据库!

PostgreSQL 功能简介

Data Types 数据类型

  • Primitives: Integer, Numeric, String, Boolean
    基元:整数、数字、字符串、布尔值
  • Structured: Date/Time, Array, Range / Multirange, UUID
    结构化:日期/时间、数组、范围/多范围、UUID
  • Document: JSON/JSONB, XML, Key-value (Hstore)
    文档:JSON/JSONB、XML、键值 (Hstore)
  • Geometry: Point, Line, Circle, Polygon
    几何图形:点、线、圆、多边形
  • Customizations: Composite, Custom Types
    自定义:复合、自定义类型

Data Integrity 数据完整性

  • UNIQUE, NOT NULL 唯一,不为 NULL
  • Primary Keys 主键
  • Foreign Keys 外键
  • Exclusion Constraints 排除约束
  • Explicit Locks, Advisory Locks
    显式锁、咨询锁

Concurrency, Performance 并发性、性能

  • Indexing: B-tree, Multicolumn, Expressions, Partial
    索引:B 树、多列、表达式、部分
  • Advanced Indexing: GiST, SP-Gist, KNN Gist, GIN, BRIN, * Covering indexes, Bloom filters
    高级索引:GiST、SP-Gist、KNN Gist、GIN、BRIN、覆盖索引、布隆过滤器
  • Sophisticated query planner / optimizer, index-only scans, multicolumn statistics
    复杂的查询计划器/优化器、仅索引扫描、多列统计信息
  • Transactions, Nested Transactions (via savepoints)
    事务、嵌套事务(通过保存点)
  • Multi-Version concurrency Control (MVCC)
    多版本并发控制 (MVCC)
  • Parallelization of read queries and building B-tree indexes
    读取查询的并行化和构建 B 树索引
  • Table partitioning 表分区
  • All transaction isolation levels defined in the SQL standard, including Serializable
    SQL 标准中定义的所有事务隔离级别,包括 Serializable
  • Just-in-time (JIT) compilation of expressions
    表达式的实时 (JIT) 编译

PostgreSQL 安装配置

安装

PostgreSQL安装过程如下:

1
2
3
4
5
6
7
8
9
10
11
# 安装PostgreSQL服务
sudo yum install -y postgresql12 postgresql12-server

# 初始化数据库
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

# 启动PostgreSQL服务
sudo systemctl start postgresql-12

# 设置PostgreSQL服务为开机启动
sudo systemctl enable postgresql-12

配置

PostgreSQL安装成功之后,会默认创建一个名为postgres的Linux用户,初始化数据库后,会有名为postgres的数据库,来存储数据库的基础信息,例如用户信息等等,相当于MySQL中默认的名为mysql数据库。

postgres数据库中会初始化一名超级用户postgres。为了方便我们使用postgres账号进行管理,我们可以修改该账号的密码。具体命令如下:

1
2
3
4
5
6
7
8
su postgres

# 登录PostgreSQL数据库
psql
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432

# 为postgres用户设置新密码
ALTER USER postgres WITH PASSWORD 'NewPassword';

PostgreSQL 模式(SCHEMA)

PostgreSQL 模式(SCHEMA)可以看着是一个表的集合。

一个模式可以包含视图、索引、数据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突,例如 schema1 和 myschema 都可以包含名为 mytable 的表。

使用模式的优势:

  • 允许多个用户使用一个数据库并且不会互相干扰。

  • 将数据库对象组织成逻辑组以便更容易管理。

  • 第三方应用的对象可以放在独立的模式中,这样它们就不会与其他对象的名称发生冲突。

1
2
3
CREATE SCHEMA schema_name;
# 切换到新创建的模式,以查询模式下的对象
SET search_path TO schema_name;

数据库相关语法示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# 创建数据库
CREATE DATABASE mydb;

# 查看所有数据库
\l

# 切换当前数据库
\c mydb

# 创建表
CREATE TABLE test(id int,body varchar(100));

# 查看当前数据库下所有表
\d

# 列出某一张表格的结构
\d [table_name]

# 列出所有用户
\du

# 列出所有用户,包含额外列
\du+

# 打开文本编辑器
\3

# 列出当前数据库和连接的信息
\conninfo

# 退出
\q

# 查看SQL命令的解释,比如\h select。
\h

# 查看psql命令列表
\?

用户与访问授权语法示例

1
2
3
4
5
6
7
8
#新建用户
CREATE USER test WITH PASSWORD 'test';

#赋予指定账户指定数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO test;

#移除指定账户指定数据库所有权限
REVOKE ALL PRIVILEGES ON DATABASE mydb TO test

参考链接

  1. About PostgreSQL,by postgresql.
  2. PostgreSQL新手入门,by ruanyifeng.
  3. 在CentOS 7上安装&配置PostgreSQL 12,by 数据库.
  4. 【PostgreSQL】系列之 一 schema详解(二),by 阿龙先生啊.