PostgreSQL学习笔记（1）简介与Windows安装

前言

过去几年，MySQL 一直霸占着数据库流行榜的榜首。但是，随着时间的推移，PostgreSQL 以 46.5% 的使用率第一次超过 MySQL 位居第一，而 MySQL 以 45.7% 的使用率降至第二名。同为泛用性最好的开源关系型数据库，排名第一第二的 PGSQL 与 MySQL ，与其他的数据库远远拉开了距离。PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。PostgreSQL 开发者把它念作 post-gress-Q-L。PostgreSQL 的 Slogan 是 "世界上最先进的开源关系型数据库"。PostgreSQL 是一个功能强大的开源数据库系统。经过长达15年以上的积极开发和不断改进，PostgreSQL已在可靠性、稳定性、数据一致性等获得了业内极高的声誉。目前PostgreSQL可以运行在所有主流操作系统上，包括Linux、Unix和Windows。
PostgreSQL 是完全的事务安全性数据库，支持丰富的数据类型（如JSON和JSONB类型、数组类型）和自定义类型。PostgreSQL数据库提供了丰富的接口，可以很方便地扩展它的功能，如可以在GiST框架下实现自己的索引类型，支持使用C语言写自定义函数、触发器，也支持使用流行的编程语言写自定义函数。PL/Perl提供了使用Perl语言写自定义函数的功能，当然还有PL/Python、PL/Java、PL/Tcl等。
作为一种企业级数据库，PostgreSQL以它所具有的各种高级功能而自豪，像多版本并发控制( MVCC )、按时间点恢复(PITR)、表空间、异步复制、嵌套事务、在线热备、复杂查询的规划和优化以及为容错而进行的预写日志等。它支持国际字符集、多字节编码并支持使用当地语言进行排序、大小写处理和格式化等操作。它也在所能管理的大数据量和所允许的大用户量并发访问时间具有完全的高伸缩性。

什么是数据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。ORDBMS（对象关系数据库系统）是面向对象技术与传统的关系数据库相结合的产物，查询处理是 ORDBMS 的重要组成部分，它的性能优劣将直接影响到DBMS 的性能。ORDBMS在原来关系数据库的基础上，增加了一些新的特性。RDBMS 是关系数据库管理系统,是建立实体之间的联系,最后得到的是关系表。OODBMS 面向对象数据库管理系统,将所有实体都看着对象,并将这些对象类进行封装,对象之间的通信通过消息 OODBMS 对象关系数据库在实质上还是关系数据库 。
 

ORDBMS 术语

在我们开始学习 PostgreSQL 数据库前，让我们先了解下 ORDBMS 的一些术语：

数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

PostgreSQL 特征

函数：通过函数，可以在数据库服务器端执行指令程序。

索引：用户可以自定义索引方法，或使用内置的 B 树，哈希表与 GiST 索引。

触发器：触发器是由SQL语句查询所触发的事件。如：一个INSERT语句可能触发一个检查数据完整性的触发器。触发器通常由INSERT或UPDATE语句触发。 多版本并发控制：PostgreSQL使用多版本并发控制（MVCC，Multiversion concurrency control）系统进行并发控制，该系统向每个用户提供了一个数据库的"快照"，用户在事务内所作的每个修改，对于其他的用户都不可见，直到该事务成功提交。

规则：规则（RULE）允许一个查询能被重写，通常用来实现对视图（VIEW）的操作，如插入（INSERT）、更新（UPDATE）、删除（DELETE）。

数据类型：包括文本、任意精度的数值数组、JSON 数据、枚举类型、XML 数据

等。
全文检索：通过 Tsearch2 或 OpenFTS，8.3版本中内嵌 Tsearch2。

NoSQL：JSON，JSONB，XML，HStore 原生支持，至 NoSQL 数据库的外部数据包装器。

数据仓库：能平滑迁移至同属 PostgreSQL 生态的 GreenPlum，DeepGreen，HAWK 等，使用 FDW 进行 ETL。

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 对比 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是一个很明智的选择。
 

不适用 PostgreSQ 的场景

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

2.因为作为一款企业级数据库产品，PG对其安全也是极其重视的，因此，如果你在开发一个把安全管理放到应用层去做的轻量级应用，那么PG完善的安全机制反倒会成为负担，因为它的角色和权限管理非常复杂，会带来不必要的管理复杂度和性能损耗。鉴于上面的种种，PG数据库一般是会和别的数据库搭配使用，使他们各展所长。一种常见的组合是把Redis当成PG的查询缓存来用，另一种的组合是用PG做主数据库。
 

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架构里面的这些部分。

进程架构

• 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  

内存架构

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

• 本地内存区 - 用于每个backend process内部使用，每个客户端连接对应一个本地内存区。

• 共享内存区 - 所有PG进程共享使用。

本地内存区

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

共享内存区

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

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

• 用于多种访问控制的内存区域。

• 用于多种后台进程如checkpointer、vacuum的内存区域。

• 用于事务处理的区域如savepoint、二阶段提交。

Windows下安装

1，在windows安装postgresql的时候需要查看一下系统类型是多少位的

我这里是64位的，下载64位的，避免下载的资源版本与系统冲突安装不上的问题

2，资源下载

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

3，开始安装，双击运行下载好的文件

步骤1，点击下载下来的postgresql.exe文件（这块可能有会报错，解决方法下面会写）弹出以下界面，点击Next

步骤2，弹出以下界面，可以选择安装目录（自定义），然后点击Next

步骤3，弹出以下界面，不做任何操作直接Next

步骤4，弹出以下界面，直接选择Next（这里是创建存放数据的目录，默认就是安装目录下建了一个data目录，不建议自己更改）

步骤5，弹出以下界面，输入要登录的密码（自定义），然后点击Next

步骤6，弹出以下界面，直接点击Next，这里是默认分配的端口，每个人可能不一样

步骤7，弹出以下界面，直接点击Next

步骤8，弹出以下界面，直接点击Next

步骤9，弹出以下界面，直接Next

步骤10，弹出以下界面，等待安装

步骤11，postgreSQL安装向导已完成，点击Finish

步骤12，弹出以下界面，安装堆栈生成器，点击下一个（此过程需要联网）

步骤13，弹出以下界面，选择安装语言包，点击下一个

步骤14，弹出以下界面，选择安装程序包，设置下载目录（可不设置），点击下一个

步骤15，弹出正在下载的窗口

下载超时，会返回到步骤14，继续点下一个，下载成功为止

步骤16，弹出以下界面，下载完成，点击下一个

步骤17，弹出语言选择框，点击ok

步骤18，弹出以下界面，语言安装包向导，点击Next

步骤19，弹出以下界面，点击Next

等待安装完成

步骤20，弹出以下界面，语言包安装完成，点击Finish

步骤21，弹出以下界面，安装完成，点击完成

4，测试是否安装成功

（1）在开始菜单中找到pgAdmin4

（2）打开这个大象头，会在浏览器中打开新的标签页。
第一次打开会提示设置密码。这个密码是pgAdmin的密码，以后连接服务器的密码需要保存，则会使用pgAdmin的密码对其加密和解密。
这里输入的密码，并不是安装过程中设置的PostgreSQL超级用户postgres的密码。
可以重新设置一套，嫌麻烦可以直接用超级用户的

密码不必担心记混或者记不住
第一次设置好后，再打开pgAdmin就是输入密码确认的过程，这里提供了reset重置功能。
重置pgAdmin密码会删除之前保存的服务器密码文件，服务器密码本身没有变化，需要重新输入才能连接

进入pgAdmin后，左侧服务器列表展开，有一个输入密码的对话框，输入安装过程设置的密码即可

密码正确，连接成功，服务器图标变成大象头，没有红叉。服务器下属的数据库等信息都可以查看，主界面显示具体信息。这时候pg库的安装验证就完事了

或使用Navicat测试是否安装成功

点击测试连接，成功如图

四、安装过程中遇到的问题

在点击postgresql.exe安装时可能会报

出现这个错误的原因可能是你电脑的登陆用户用的是中文需要改成英文
右击我的电脑，点击管理找到计算机管理

点击本地用户与组找到当前登陆账号，右击重命名为英文即可

参考：链接1  链接2 链接3