概述
postgresql是一个开源的关系型数据库，可以作为mysql的替代品。
本篇意在让读者快速的了解postgresql的基本概念，使用的版本是postgresql9.6本，如有错误欢迎指正。
参考书籍
《PostgreSQL 9 Administration Cookbook》第二版。
本篇省略的东西
有些内容比较冗长，或者使用场景较少，或者过于复杂，建议参考具体的手册，本篇将会省略。
- 选择postgresql的理由。
- 常用的用于操作的语法、语句。
- 数据库函数的编写与执行。
- 不同数据库、模式、表空间之间的数据移动。
- 访问其他postgres数据库或者其他类型的数据库。
- 自动数据库维护的定制化配置。
- 数据库的复制配置。
- 数据库的升级操作。
postgres服务使用
发音
post-gres-q-l。
下载、安装、启动
基本上主流unix操作系统的库里都有制作好的安装包，直接安装即可，然后用相应的系统服务启动。
也可以使用命令行工具启动或者停止。
-m参数可以指定关闭方式，fast会等待所有操作完毕后再关闭，immediate则会强行关闭。
pg_ctl -D Datadir start
pg_ctl -D Datadir -m fast stop
pg_ctl -D Datadir -m immediate stop
每个服务在初始化的时候都会被分配一个系统标识符，很多系统操作需要使用到，可以通过如下命令获得。
pg_controldata Datadir | grep “system identifier”
版本号
postgreql的版本号组成为：主版本号.次版本号.维护版本号，它的特性和兼容性是由主版本号和次版本号共同决定的。
psql --version
连接
初始管理员用户：postgres，密码：postgres，数据库：postgres。
psql postgres://user:password@host:port/db
psql -U user -W password -h host -p port -d db
| 变量 | 默认值 |
|---|---|
| user | 当前用户 |
| password | 空 |
| host | localhost |
| port | 5432 |
| db | postgres |
每个连接在同一时刻只允许有一个活跃的事务，并且在任何时刻只允许有一个完全活跃的语句。
数据库
psql -l
template0和template1是两个数据库模板，template1是可以修改的，用来自定义。
命令行执行语句和脚本
psql -c "SELECT current_time"
psql -f test.sql
postgres客户端
帮助
postgres=# help
postgres=# ?
postgres=# \h
注释
-- 单行注释
/*
* 多行注释
*/
退出
postgres=# \q
开关一行显示一列数据
postgres=# \x
运行操作系统的命令
postgres=# \! cat test.sql
命令从文件中定向输入
postgres=# \i test.sql
临时数据库配置，LOCAL表示仅对当前事务生效
postgres=# SET work_mem = '16MB';
postgres=# SET LOCAL work_mem = '16MB';
postgres=# RESET work_mem;
postgres=# SHOW work_mem;
管理数据库插件
postgres=# CREATE EXTENSION myext;
postgres=# ALTER EXTENSION myext UPDATE;
postgres=# DROP EXTENSION myext;
更新所有表大小和数据分布的统计信息
postgres=# ANALYZE;
垃圾回收
postgres=# VACUUM;
postgres数据管理
服务（server）
- 一个操作系统中可以启动多个postgres服务。
- 每个服务由多个进程组成，为首的进程名为postmaster。
- 每个服务要占用一个端口，多个服务不能共享端口。
- 每个服务都有一个data目录用于存放数据，目录不允许修改，否则会破坏数据库，并且无法修复。
- 服务使用4字节长的内部事务标识符，即时发生重叠后仍然继续使用，这会导致问题，所以需要定期进行VACUUM操作。
数据库（database）
- 一个服务中可以拥有多个数据库。
- 数据库默认是任何用户可连接的，创建好后需要修改相应的权限。
- 数据库之间的数据是隔离的，不能进行联表。
- 数据库默认的数据块大小为8192。
模式（schema）
- 一个数据库中可以有多个模式，模式相当于表的命名空间，类似于mysql中的database，可以使用带模式的完整名称来访问或者创建对象。
- 不同模式之间的表是可以联表查询的。
- 可以通过对用户设置search_path参数来指定默认搜索的模式。
表（table）
- 一个模式中可以有多张表。
- 表是由多个关系元素组成的，大字段数据放在另一个名为TOAST的表中，每张表都有一个TOAST表和TOAST索引。
- 用双引号括起来的表和没用双引号括起来的表是不一样的，即使名字一样。
- 双引号括起来的表区分大小写，没用双引号括起来的表不区分大小写。
列（column）
- 每张表都由许多列组成，每一列有一个列名、类型、默认值等属性，用来存储每一条记录中的各种值。
- 文本类型统一由一种数据类型存储，支持长度从1B到1G，经过优化，存储少的时候很高效，存储多的时候会自动管理和压缩。
- 自增类型serial本质上就是整数，通过创建并关联到一个SEQUENCE类型的对象来记录自增值。
表空间（tablespace）
- 默认情况下，所有的数据都会放在postgres指定的data目录下，通过定义表空间，可以让postgres将数据存放在不同的设备上。
- 表空间是通过软链接来实现的。
- 建议为每个数据库设立一个单独的表空间，尤其是不同数据库中有同名的模式或者表的时候。
postgres=# CREATE TABLESPACE tbs LOCATION '/usr/local/tbs';
视图（view）
- 视图本质上是预定义好的一个sql查询，以一张表的形式给出，在每次调用时都会执行相应的sql查询。
postgres=# CREATE VIEW view AS SELECT * FROM tb;
- 当视图足够简单的时候，postgres是支持视图更新的，相应的更新会传递到相应的表中。
还可以使用INSTEAD OF触发器或者规则来实现视图更新，请参考具体的操作手册。 - 物化视图可以预先将数据查询出来，这样调用的时候就不必反复查询了，更新需要手动更新。
postgres=# CREATE MATERIALIZED VIEW view AS SELECT * FROM tb;
postgres=# REFRESH MATERIALIZED VIEW view;
行（row）
- 行即表中的一条数据。
- postgres中每个行都有一个行版本，而且还有两个系统列xmin和xmax，分别标示这个行被创建和删除的事务。
删除时，设置xmax为删除事务号，不会实际执行删除。 - UPDATE操作被认为是紧跟INSERT操作后的DELETE操作。
索引（index）
- 索引可以用来给表添加约束或者提高查询速度。
- 在涉及高比例插入\删除的表中，会造成索引膨胀，这时候可以重建索引。
reindexdb
- 创建CONCURRENTLY索引时不会持有全表锁，这条指令分成两个步骤，第一部分创建索引并标记为不可用，这时候INSERT、UPDATE、DELETE操作已经开始维护索引了，但是查询不能使用索引。建立完毕后才会被标记为可用。
postgres=# CREATE CONCURRENTLY INDEX index ON tb(id);
- 可以手工设置索引的可用性。
UPDATE pg_index SET indisvalid = false WHERE indexrelid = index::regclass;
postgres目录结构
| 子目录 | 用途 |
|---|---|
| base | 主数据目录。此目录中每个数据库都有自己的目录，以oid号命名，其中包含用于每张表和索引的文件 |
| global | 数据库中的系统表，在所有的数据库中共享 |
| pg_clog | 事务状态文件 |
| pg_dynshmem | 动态共享内存信息 |
| pg_multixact | 行锁状态文件 |
| pg_notify | 监听和通知的状态文件 |
| pg_replslot | 复制槽位的信息 |
| pg_serial | 已提交的序列化事务信息 |
| pg_snapshot | 输出的快照文件 |
| pg_stat | 服务活动统计以及持久文件 |
| pg_stat_tmp | 服务活动统计以及临时文件 |
| pg_subtrans | 子事务状态文件 |
| pg_tblspc | 连接到外部的表空间 |
| pg_twophase | “两阶段提交”事务状态 |
| pg_xlog | 事务日志WAL |
postgres权限控制
连接权限
首先要修改数据目录下的postgresql.conf文件来允许它监听所有地址。修改完后需要重启服务。
listen_addresses = '*'
postgres连接的权限在数据目录下的pg_hba.conf文件中按照指定格式进行定义，每次连接会按照顺序检查每条规则，直到匹配到通过策略或者拒绝策略。
#TYPE DATABASE USER ADDRESS METHOD
host all all 127.0.0.1/32 password
每条策略的参数如下：
- type：连接类型，local表示允许本地socket连接、host表示允许ip连接、hostssl表示允许ssl连接。
- database：连接的数据库，all表示所有、sameuser表示用户名和数据库名相同、其他表示数据库名称。
- user：连接的用户，all表示所有用户、其他表示连接的数据库用户。
- address：允许连接的子网和子网掩码。
- method：认证方式，trust表示允许、password表示密码、md5表示加密密码、cert表示证书、ldap表示ldap认证，对于证书认证之后还会有一个参数来配置证书。
权限模型
- postgres采用角色（ROLE）-权限（PRIVILEGE）模型。
- 权限主要有USAGE、SELECT、INSERT、UPDATE、DELETE五种。
- 角色分两种，可以登录的用户（USER）与不可以登录的组（GROUP）。
- 角色可以继承。
postgres=# CREATE USER user;
postgres=# CREATE ROLE role NOLOGIN;
postgres=# GRANT role1 TO role2;
特殊权限
- postgres中有一些特殊权限，不能被授予，也不会被继承，分别是SUPERUSER、CREATEDB和CREATEUSER;
- 特殊权限单独对用户进行授予或者回收。
角色切换与权限继承
- 用户可以切换到自己拥有的角色。
postgres=# SET ROLE TO role;
- 可以通过NOINHERIT参数来创建不会继承角色的用户，这样用户只有切换角色才能获得相应的权限。
postgres=# CREATE USER user NOINGERIT;
postgres支持的数据类型
数
- 整数：smallint、integer、bigint
- 实数：real、double
- 自增数：smallserial、serial、bigserial
- 高精度：decimal、numeric
串
- 有限不定长字符串：varchar
- 有限定长字符串：char
- 无限不定长字符串：text
- 位串：bit
- UUID
几何
- 点：point
- 线：line、lseg
- 矩形：box
- 圆：circle
- 路径：path
- 多边形：polygon
其他
- 布尔：boolean
- 枚举：enum
- 二进制：bytea
- 时间：date、time、timestamp、interval
- 货币：money
- 网络地址：cidr、inet、macaddr
- 数据格式：json、xml
- 复合类型：table、type
postgres中一些系统变量、系统函数和系统视图
| 变量名 | 含义 |
|---|---|
| current_user | 当前用户 |
| current_time | 当前时间 |
| current_timestamp | 当前时间戳 |
| current_schema | 当前所在模式 |
| current_user | 当前登录的用户 |
| session_user | 当前会话的用户 |
| 函数名 | 功能 |
|---|---|
| current_database0 | 当前连接的数据库 |
| inet_server_addr() | 当前服务的地址 |
| inet_server_port() | 当前服务的端口 |
| version() | 当前数据库的版本 |
| pg_postmaster_start_time() | 服务启动时间 |
| pg_database_size() | 当前数据库的大小 |
| pg_size_pretty() | 格式化尺寸数字 |
| pg_relation_size(table) | 某张表的表大小 |
| pg_total_relation_size(table) | 某张表的总大小 |
| pg_reload_conf() | 重新加载配置文件 |
| generate_series(a, b) | 从a到b顺序生成整数 |
| random() | 随机一个0到1之间的数 |
| :: type | 强制类型转换 |
| pg_cancel_backend(pid) | 取消当前查询 |
| pg_terminate_backend(pid) | 杀死后端进程 |
| pg_stat_reset() | 重置表的统计信息 |
| pg_stat_statements_reset() | 重置查询的统计信息 |
| 视图名 | 内容 |
|---|---|
| pg_database | 服务中的数据库信息 |
| pg_catalog | 系统信息 |
| pg_extension | 扩展插件信息 |
| pg_available_extensions | 可用扩展插件信息 |
| pg_constraint | 表之间的约束信息 |
| pg_settings | 数据库的配置 |
| pg_stat_activity | 后台进程的信息 |
| pg_stat_user_tables | 当前使用情况统计信息 |
| pg_stat_database | 数据库的统计信息 |
| pg_stat_statements | 被执行的查询的执行状态 |
| pg_locks | 锁信息 |
| pg_prepared_xacts | 准备事务的信息 |
| pg_index | 索引的信息 |
| pg_class | 实体的信息 |
| pg_stat_replication | 复制的状态 |
postgres脚本执行
postgres会按照顺序执行脚本中的每条指令，遇到错误会报错并继续执行接下来的指令。
事务
事务的功能是让一批指令要么同时执行成功，要么同时执行失败。本质上postgres会按照顺序执行指令，一旦遇到错误，就回滚之前的指令。
事务是不能够嵌套的，否则后续的BEGIN会被忽略。
postgres=# BEGIN;
postgres=# command1;
postgres=# command2;
postgres=# COMMIT;
因为事务的特性，使得事务中不能包含以下命令。
- CREATE/DROP DATABASE/TABLESPACE
- CREATE INDEX CONCURRENTCY
- VACUUM
- REINDEX DATABASE / REINDEX SYSTEM
- CLUSTER
遇错退出
通过设置开关来实现遇到错误就停止脚本。
psql -f test.sql -v ON_ERROR_STOP=on
postgres=# \set ON_ERROR_STOP
数据导入与导出
postgres提供两个工具来进行数据导入和导出，pg_dump和pg_dumpall。
- pg_dump工具产生一个默认轻量压缩的自定义格式归档文件，通过pg_restore恢复；pg_dumpall工具产生一个脚本文件，通过psql执行。
- pg_dump不会导出角色和表空间；pg_dumpall可以导出全局对象，也可以只导出全局对象。
- pg_dump和gp_restore可以并行，pg_dumpall和psql只能串行。
- pg_dump也可以通过选项-F p来生成脚本文件。
postgres还可以进行热物理备份，需要遵循步骤来处理备份中的增量数据，详细信息请参考手册。
复制
postgres提供两种复制方法，物理流复制和逻辑流复制。
之前还有一种基于日志文件传送的复制，主节点将数据库更改记录到事务日志，然后将日志文件从主节点传动到备节点，之后备节点再重演日志。现在它已经被流复制取代了，不推荐使用。
- 物理流复制获取事务日志WAL并将数据传送到远程节点，WAL包含数据块中发生的改变的精确物理备份，因此远程节点是主节点的精确副本，也无法执行写入到数据库的事务。
- 逻辑流复制允许远程节点复制数据而不需要保持数据库的完整副本，复制中所有形式的身份验证和安全工作都和普通的连接相同。逻辑流复制可以实现多主复制。
- 复制槽位功能允许明确定义复制架构，还允许即使在节点连接断开的情况下跟踪节点的细节。逻辑流复制以及任何其他使用逻辑解码功能的工具都需要复制槽位，但对于物理流是可选的。
