最近公司决定使用 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安装过程如下：

# 安装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账号进行管理，我们可以修改该账号的密码。具体命令如下：

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;

数据库相关语法示例

# 创建数据库
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命令列表
\?

用户与访问授权语法示例

#新建用户
CREATE USER test WITH PASSWORD 'test';

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

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

PostgreSQL数据库学习笔记