PostgreSQL教程

过去几年，MySQL 一直霸占着数据库流行榜的榜首。但是，随着时间的推移，PostgreSQL 以 46.5% 的使用率第一次超过 MySQL 位居第一，而 MySQL 以 45.7% 的使用率降至第二名。同为泛用性最好的开源关系型数据库，排名第一第二的 PGSQL 与 MySQL ，与其他的数据库远远拉开了距离。

所以，从今天开始，我们进入下一个知识体系的学习：PostgreSQL 数据库。

PostgreSQL 简介

PostgreSQL 是一个功能强大的开源数据库系统。经过长达15年以上的积极开发和不断改进，PostgreSQL已在可靠性、稳定性、数据一致性等获得了业内极高的声誉。目前PostgreSQL可以运行在所有主流操作系统上，包括Linux、Unix和Windows。

PostgreSQL 是完全的事务安全性数据库，支持丰富的数据类型[1]（如JSON和JSONB类型、数组类型）和自定义类型。PostgreSQL数据库提供了丰富的接口，可以很方便地扩展它的功能，如可以在GiST框架下实现自己的索引类型，支持使用C语言写自定义函数、触发器，也支持使用流行的编程语言[2]写自定义函数。PL/Perl提供了使用Perl语言写自定义函数的功能，当然还有PL/Python、PL/Java、PL/Tcl等。

作为一种企业级数据库，PostgreSQL以它所具有的各种高级功能而自豪，像多版本并发控制( MVCC )、按时间点恢复(PITR)、表空间、异步复制、嵌套事务、在线热备、复杂查询的规划和优化以及为容错而进行的预写日志等。它支持国际字符集、多字节编码并支持使用当地语言进行排序、大小写处理和格式化等操作。它也在所能管理的大数据量和所允许的大用户量并发访问时间具有完全的高伸缩性。

更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

官网

• 官网地址: https://www.postgresql.org
• 下载地址： https://www.postgresql.org/download/
• 安装工具: https://www.pgadmin.org/download/

PostgreSQL 优势

PostgreSQL 数据库是目前功能最强大的开源数据库，它是最接近工业标准SQL92的查询语言，至少实现了SQL:2011标准中要求的179项主要功能中的160项（注：目前没有哪个数据库管理系统能完全实现SQL:2011标准中的所有主要功能）。

• 稳定可靠：PostgreSQL是唯一能做到数据零丢失的开源数据库。目前有报道称国内外有部分银行使用PostgreSQL数据库。
• 开源省钱：PostgreSQL数据库是开源的、免费的，而且使用的是类BSD协议，在使用和二次开发上基本没有限制。
• 支持广泛：PostgreSQL 数据库支持大量的主流开发语言，包括C、C++、Perl、Python、Java、Tcl以及PHP等。
• PostgreSQL社区活跃：PostgreSQL基本上每3个月推出一个补丁版本，这意味着已知的Bug很快会被修复，有应用场景的需求也会及时得到响应。

PostgreSQL 的特征

• 多版本并发控制：PostgreSQL使用多版本并发控制（MVCC，Multiversion concurrency control）系统进行并发控制，该系统向每个用户提供了一个数据库的"快照"，用户在事务内所作的每个修改，对于其他的用户都不可见，直到该事务成功提交。
• 数据类型[1]：包括文本、任意精度的数值数组、JSON 数据、枚举类型、XML 数据等。
• 全文检索[3]：通过 Tsearch2 或 OpenFTS。
• NoSQL：JSON，JSONB，XML，HStore 原生支持，甚至 NoSQL 数据库的外部数据包装器。
• 数据仓库：能平滑迁移至同属 PostgreSQL 生态的 GreenPlum，DeepGreen等，使用 FDW（Foreign data wrappers） 进行 ETL（Extract-Transform-Load）。
• 函数[4]：通过函数，可以在数据库服务器端执行指令程序。
• 索引[5]：用户可以自定义索引方法，或使用内置的 B 树，哈希表与 GiST（Generalized Search Tree） 索引。
• 触发器[6]：触发器是由SQL语句查询所触发的事件。如：一个INSERT语句可能触发一个检查数据完整性的触发器。触发器通常由INSERT或UPDATE语句触发。
• 规则[7]：规则（RULE）允许一个查询能被重写，通常用来实现对视图（VIEW）的操作，如插入（INSERT）、更新（UPDATE）、删除（DELETE）。
• 继承：PostgreSQL实现了表继承，一个表可以从0个或者多个其他表继承，而对一个表的查询则可以引用一个表的所有行或者该表的所有行加上它所有的后代表。

Postgresql 对比 mysql和oracle

Postgresql VS Oracle

Oracle数据库是目前功能最强大的商业数据库，PostgreSQL则是功能最强大的开源数据库。

PostgreSQL与Oracle的不同之处在于，PostgreSQL有更多支持互联网特征的功能。如PostgreSQL数据类型支持网络地址类型、XML类型、JSON类型、UUID类型以及数组类型，且有强大的正则表达式函数，如where条件中可以使用正则表达式匹配，也可以使用Python、Perl等语言写存储过程等。

PostgreSQL更小巧。PostgreSQL可以在内存很小的机器上完美运行起来，如在512MB的云主机中，而Oracle数据库基本要在数GB的云主机中才可以运行起来。Oracle安装包动辄几个GB以上级别，而PostgreSQL的安装包只有几十MB大小。

PostgreSQL在任何一个环境都可以轻松地安装。Oracle数据库安装花费的时间是在小时级别，而PostgreSQL在分钟级别就可以完成安装。

Postgresql VS Mysql

Postgresql和Mysql都是开源数据库。

功能强大：支持所有主流的多表连接查询的方式，如“Hash JOIN”“Sort Merge JOIN”等；字段类型还支持数组类型，甚至有一些业务功能都不再需要写代码来实现了，直接使用数据库的功能即可解决问题。

性能优化工具与度量信息丰富：PostgreSQL数据库中有大量的性能视图，可以方便地定位问题（比如可以看到正在执行的SQL，可以通过锁视图看到谁在等待，以及哪条记录被锁定等）。PostgreSQL中设计了专门架构和进程用于收集性能数据，既有物理I/O方面的统计，也有表扫描及索引扫描方面的性能数据。

在线操作功能好：PostgreSQL增加空值列时，本质上只是在系统表上把列定义上，无须对物理结构做更新，这就让PostgreSQL在加列时可以做到瞬间完成。PostgreSQL还支持在线建索引的功能，在创建索引的过程可以不锁更新操作。

从PostgreSQL9.1开始，支持同步复制（synchronous replication）功能，通过Master和Slave之间的复制可以实现零数据丢失的高可用方案。
可以方便地写插件来扩展PostgreSQL数据库的功能：支持移动互联网的新功能，如空间索引。如果应用的数据访问很简单，那么后端使用MySQL也是很合适的。但是如果应用复杂，而且不想消耗太多的开发资源，那么PostgreSQL是一个很明智的选择。

不适用 PostgreSQL 的场景

1.在不安装任何扩展包的情况下，PG需要占用100MB以上的磁盘空间，可以看出它的个头是比较大的，因此在一些存储空间极为有限的小型设备上使用PG是不合适的。因此在一些存储空间极为有限的小型设备上使用PG是不合适的，把PG当成简单的缓存区来用也是不合适的，此时应选用一些更轻量级的数据库。

2.因为作为一款企业级数据库产品，PG对其安全也是极其重视的，因此，如果你在开发一个把安全管理放到应用层去做的轻量级应用，那么PG完善的安全机制反倒会成为负担，因为它的角色和权限管理非常复杂，会带来不必要的管理复杂度和性能损耗。

鉴于上面的种种，PG数据库一般是会和别的数据库搭配使用，使他们各展所长。一种常见的组合是把Redis当成PG的查询缓存来用，另一种的组合是用PG做主数据库。

更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

PostgreSQ 数据库对象

database

每个PG服务可以包含多个独立的database。

schema

如果把databases比作一个国家，那么schema就是一些独立的省。大多数对象是隶属于某个schema的，然后schema又隶属于某个databases。在创建一个新的database时，PG会自动为其创建一个名为public的schema。如果未设置searc_path变量，那么PG会将你创建的所有对象默认放入public schema中。如果表的数量较少，这是没问题的，但是如果你有几千张表，那么我们还是建议你将他们分门别类放入不同的schema中。

表

任何一个数据库中，表都是最核心的对象类型。在PG中，表首先属于某个schema，而schema有属于某个database，这样就构成一种三级存储结构。PG的表支持两种很强大的功能。第一种是继承，即一张表可以有父表和子表，这种层次化的结构可以极大的简化数据库设计，还可以为你省掉大量的重复查询代码。第二种是创建一张表的同时，系统会自动为此表创建一种对应的自定义数据类型。

PostgreSQL 整体架构

PostgreSQL 作为一个单机的关系型数据库，与单机Oracle的架构是比较相似的，与MySQL的InnoDB引擎也比较像。据我目前的了解，单机数据库的整体架构都差不太多，都是包含一个主的进程，一些辅助进程，以及一个大的共享内存池。下面我们具体学习一下PG架构里面的这些部分。

进程架构[8]

PostgreSQL是一个多进程架构的客户端/服务器模式的关系型数据库管理系统。PG数据库中的一系列进程组合进来就是PostgreSQL服务端。这些进程可以细分为以下几大类：

• postgres server进程 -是PG数据库中所有进程的父进程。
• backend进程 - 每个客户端对于一个backend进程，处于这个客户端中的所有请求。
• background进程 - 包含多个后台进程，比如做脏块刷盘的BACKGROUND WRITER进程，做垃圾清理的AUTOVACUUM进程，做检查点的CHECKPOINTER进程等。
• replication相关进程 - 处理流复制的进程。
• background workder进程 - PG9.3版本增加，执行由用户自定义开发的逻辑。

从上图可以看出，PG数据库中有一个主的postgres server进程，针对每个客户端有一个backend postgres进程，另外有一系列的background后台进程（针对不同的功能模块）。所以这些进程都对应一个共享内存shared memory。

下面我们再具体学习一下每类进程的主要工作内容。

Postgres Server Process

postgres server process是所有PG进程的父进程，在以前的版本中称为postmaster。当使用pg_ctl start启动数据库时，这个进程就被启动了， 然后它会启动一个共享内存shared memory，启动多个background后台进程，启动复制相关进程，如有需要也启动background worker progress，然后等待客户端的连接。

当接收到一个客户端连接时，它就会启动一个backend progress，专门服务于这个客户端。

postgres server process通常有一个对应的监听端口，默认是5432。如果一台机器上安装多个postgres实例有多个postgres server process，那么就需要修改对应的端口地址比如5433、5434等。

Backend Process

backend process也称为postgres进程，是由上面的postgres server process启动的用于服务于对应的客户端，通过TCP协议和客户端进行通信。
由于这个进程只能服务于一个特定的database，所以需要在连接PG数据库的时候指定一个默认连接的database。

PG允许多个客户端同时连接数据库，由max_connections参数控制最大并发连接数，默认是100。

如果有很多客户端频繁的对数据库进行短连接与释放连接，那么可能会造成连接耗时比较长，因为PG目前没有连接池的功能。针对于这种场景，一般通过像pgbouncer或pgpool-II这种插件来优化。

Background Process

background process后台进程有多个，每个进程负责一个模块或是一类任务，下面表格总结每个进程的描述。

以下是一个环境中查看到的PG相关进程列表，

postgres> pstree -p 9687
-+= 00001 root /sbin/launchd
 -+- 09687 postgres /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
   |--= 09688 postgres postgres: logger process     
   |--= 09690 postgres postgres: checkpointer process     
   |--= 09691 postgres postgres: writer process     
   |--= 09692 postgres postgres: wal writer process     
   |--= 09693 postgres postgres: autovacuum launcher process     
   |--= 09694 postgres postgres: archiver process     
   |--= 09695 postgres postgres: stats collector process     
   |--= 09697 postgres postgres: postgres sampledb 192.168.1.100(54924) idle  
   --= 09717 postgres postgres: postgres sampledb 192.168.1.100(54964) idle in transaction  

内存架构[9]

了解完进程架构后，我们再来了解一下内存架构，PG中的内存主要分为两类：

• 本地内存区 - 用于每个backend process内部使用，每个客户端连接对应一个本地内存区。
• 共享内存区 - 所有PG进程共享使用。

本地内存区

本地内存区有多个，每个对应一个backend progres进程，用于处于这个连接内部的一些工作，包括：

共享内存区

共享内存区在数据库启动时创建，也可以划分为多个子区域，包括：

除此之外，共享内存区还包括一些其他的子区域：

• 用于多种访问控制的内存区域。
• 用于多种后台进程如checkpointer、vacuum的内存区域。
• 用于事务处理的区域如savepoint、二阶段提交。

以及其他子区域。更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

PostgreSQL 安装部署

Windows 系统

下载地址：https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

下载后安装包如下：

安装PostgreSQL

双击打开安装包，点击Next。

选择安装路径，点击Next。

点击Next

设置数据存放目录，点击Next。

设置数据库密码，用户名默认postgres，点击Next。

设置数据库端口，默认5432，点击Next。

点击Next

点击Next

点击Next

等待安装

点击Finish

安装StackBuilder

选择PostgreSQL，点击下一个

选择语言包，点击下一个。

点击下一个

弹出正在下载窗口。（如果下载超时，会返回到上一步，继续点下一个，下载成功为止）

点击下一个

选择语言，点击OK

点击Next

点击Next

点击Next

等待安装

点击Finish

点击完成

打开PostgreSQL管理工具pgAdmin

win + s 快捷键，查询pgAdmin。

可以看到PostgreSQL的欢迎界面

输入管理密码，如果之前没有可以点击下方 Reset Master Password 进行重设。

点击Servers，输入安装PostgreSQL时设置的密码，连接数据库。

点击Dashboard，选择数据库，可以看到数据库的实时状态。

打开命令行

win + s 快捷键，查询psql。**

一直回车，直到提示输入密码。

输入密码，回车。

连接成功，进入数据库命令行。

CentOS 7 系统

选定版本

安装依赖

# 获取所需依赖包
wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/libzstd-1.5.2-1.el7.x86_64.rpm
wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/llvm5.0-devel-5.0.1-7.el7.x86_64.rpm
wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/llvm5.0-5.0.1-7.el7.x86_64.rpm
wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/llvm5.0-libs-5.0.1-7.el7.x86_64.rpm
 
yum install -y ./libzstd-1.5.2-1.el7.x86_64.rpm 
yum install -y centos-release-scl-rh llvm5*
yum install -y epel-release

执行安装

# 安装版本库的RPM
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 安装 PostgreSQL
sudo yum install -y postgresql15-server postgresql15-devel

初始化

# 初始化DB
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

# 开机启动|启动|重启|状态|停止 命令
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15
sudo systemctl restart postgresql-15
sudo systemctl status postgresql-15
sudo systemctl stop postgresql-15

配置环境变量

#编辑
vim /etc/profile

#PGSQL_HOME
export PGSQL_HOME=/usr/pgsql-15
export PATH=$PATH:$PGSQL_HOME/bin

#生效配置
source /etc/profile

基础配置

进入命令行
#切换用户
su postgres
psql

#创建DB
create database test_db -U postgres ;

# 设置密码(自由发挥):postgres
alter user postgres with password 'postgres';

配置远程

#编辑
vim /var/lib/pgsql/15/data/postgresql.conf
修改参数:
listen_addresses = '*'

# 编辑配置
vim /var/lib/pgsql/15/data/pg_hba.conf

# 添加内容
host    all      all    0.0.0.0/0      md5

#重启
sudo systemctl restart postgresql-15

测试链接

• 远程链接命令

#格式
psql -h 主机IP -p 端口  -U 用户名 -W -d 数据库

#示例
psql -h 127.0.0.1 -p 5432  -U postgres -d test_db;

pgAdmin4 工具安装

下载软件包(windows)

运行安装

操作使用

更改系统语言

系统默认是英文界面，要改成中文，可在菜单File->Preferences，找到User language选项。

测试远程链接

• 设置主凭据

• 设置基础参数

• 设置链接参数

• 链接效果

更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。