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

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

参考资料：

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

PostgrepSQL特点

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

对比Oracle

PostgreSQL与Oracle的不同之处在于，PostgreSQL有更多支持互联网特征的功能。如PostgreSQL数据类型支持网络地址类型、XML类型、JSON类型、UUID类型以及数组类型，且有强大的正则表达式函数，如where条件中可以使用正则表达式匹配，也可以使用Python、Perl等语言写存储过程等。
PostgreSQL更小巧。PostgreSQL可以在内存很小的机器上完美运行起来，如在512MB的云主机中，而Oracle数据库基本要在数GB的云主机中才可以运行起来。Oracle安装包动辄几个GB以上级别，而PostgreSQL的安装包只有几十MB大小。
PostgreSQL在任何一个环境都可以轻松地安装。Oracle数据库安装花费的时间是在小时级别，而PostgreSQL在分钟级别就可以完成安装

对比mysql

PostgreSQL9.1开始，支持同步复制（synchronous replication）功能，通过Master和Slave之间的复制可以实现零数据丢失的高可用方案。
性能优化工具与度量信息丰富
在线操作功能好：PostgreSQL还支持在线建索引的功能，在创建索引的过程可以不锁更新操作。

数据库对象

database schema 表以上称为：三级存储结构

## PostgreSQL 整体架构 PostgreSQL是一个多进程架构的客户端/服务器模式的关系型数据库管理系统。

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

postgres server process是所有PG进程的父进程，当使用pg_ctl start启动数据库时，这个进程就被启动了，然后它会启动一个共享内存shared memory，启动多个background后台进程，启动复制相关进程，如有需要也启动background worker progress，然后等待客户端的连接。当接收到一个客户端连接时，它就会启动一个backend progress，专门服务于这个客户端。 postgres server process通常有一个对应的监听端口，默认是5432。如果一台机器上安装多个postgres实例有多个postgres server process，那么就需要修改对应的端口地址比如5433、5434等。

backend process也称为postgres进程，是由上面的postgres server process启动的用于服务于对应的客户端，通过TCP协议和客户端进行通信。由于这个进程只能服务于一个特定的database，所以需要在连接PG数据库的时候指定一个默认连接的database。 PG允许多个客户端同时连接数据库，由max_connections参数控制最大并发连接数，默认是100。如果有很多客户端频繁的对数据库进行短连接与释放连接，那么可能会造成连接耗时比较长，因为PG目前没有连接池的功能。针对于这种场景，一般通过像pgbouncer或pgpool-II这种插件来优化。

background process后台进程有多个，每个进程负责一个模块或是一类任务

内存架构

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

下载安装

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

依赖包下载复制： https://archives.fedoraproject.org/pub/archive/epel/7/x86_64/Packages/l/

配置目录说明

 pg_hba.conf：#认证配置文件，配置了允许哪些IP访问数据库，及认证方式等信息。
 pg_ident.conf：#"ident"认证方式的用户映射文件。
 PG_VERSION：#记录了数据库版本号信息。
 postgresql.auto.conf：#作用同 postgresql.conf ，优先级高于 postgresql.conf，在数据库中通过alter命令更改的参数记录在此文件中。
 postgresql.conf：#数据库实例主配置文件，基本上所有的数据库参数配置都在此文件中。
 postmaster.opts：#记录数据库启动命令。
 postmaster.pid：#数据库进程文件，数据库启动时被创建，关闭时消失。
 base：#该目录包含数据库用户所创建的各个数据库，同时也包括postgres、template0和template1的pg_defaulttablespace。
 global：#该目录包含集群范围的各个表和相关视图。(pg_database、pg_tablespace）
 pg_dynshmem：#该目录包含动态共享内存子系统使用的文件。
 pg_commit_ts：#该目录包含已提交事务的时间。
 pg_logical：#该目录包含逻辑解码的状态数据。
 pg_multixact：#该目录包含多事务状态数据。（等待锁定的并发事务）
 pg_notify：#该目录包含LISTEN/NOTIFY状态数据。
 pg_replslot：#该目录包含复制槽数据。
 pg_snapshots：#该目录包含导出的快照。
 pg_stat：#该目录包含统计子系统的永久文件。
 pg_stat_tmp：#该目录包含统计子系统的临时文件。
 pg_subtrans：#该目录包含子事务状态数据。
 pg_tblspc：#该目录包含表空间的符号链接。
 pg_twophase：#该目录包含预备事务的状态文件。
 pg_wal：#该目录包含wal日志。
 pg_xact：#该目录包含事务提交状态数据。

登陆前配置

#编辑
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

#重启
systemctl restart postgresql-15 

#测试登录
 psql -h 127.0.0.1 -Uky -p 5432 -d ky36; 
 用户 ky 的口令：
psql (15.8)
输入 "help" 来获取帮助信息.

ky36=>

登录及常用指令

#默认不允许超级管理员账户登录 需要切换已经建好的postgres账户登录 默认密码是空
bash-4.2$ psql
could not change directory to "/root": 权限不够
psql (15.8)
Type "help" for help.

postgres=#

#连接指定服务器上的数据库
psql -h IP -p 端口 -U 用户名 -d 数据库名 -W

-h #数据库所在的IP地址
-p #（默认5432）数据库的监听端口
-U #用户名
-d #数据库名称

常用命令

\? #所有命令帮助
\l #列出所有数据库
\d #列出数据库中所有表
\dt #列出数据库中所有表
\d [table_name] #显示指定表的结构
\di #列出数据库中所有 index
\dv #列出数据库中所有 view
\h #sql命令帮助
\q #退出连接
\c [database_name] #切换到指定的数据库
\c #显示当前数据库名称和用户
\conninfo #显示客户端的连接信息
\du #显示所有用户
\dn #显示数据库中的schema
\encoding #显示字符集
select version(); #显示版本信息
\i testdb.sql #执行sql文件
\x #扩展展示结果信息，相当于MySQL的\G
\o /tmp/test.txt #将下一条sql执行结果导入文件中

创建用户

create user 用户名 password '密码';

#设置只读权限
alter user 用户名 set default_transaction_read_only = on;

#设置可操作的数据库
grant all on database 数据库名 to 用户名;

#授权可操作的模式和权限
-- 授权
grant select on all tables in schema public to 用户名;

-- 授权
GRANT ALL ON TABLE public.user TO mydata;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public.user TO mydata_dml;
GRANT SELECT ON TABLE public.user TO mydata_qry;

删除用户

#撤回在public模式下的权限
revoke select on all tables in schema public from 用户名;

#撤回在information_schema模式下的权限
revoke select on all tables in schema information_schema from 用户名;

#撤回在pg_catalog模式下的权限
revoke select on all tables in schema pg_catalog from 用户名;

#撤回对数据库的操作权限
revoke all on database 数据库名 from 用户名;

#删除用户
drop user 用户名;

权限管理

#设置只读权限
alter user 用户名 set default_transaction_read_only = on;

#设置可操作的数据库
grant all on database 数据库名 to 用户名;

#设置可操作的模式和权限
grant select,insert,update,delete on all tables in schema public to 用户名;

##########撤回权限######################################
#撤回在public模式下的权限
revoke select on all tables in schema public from 用户名;

#撤回在information_schema模式下的权限
revoke select on all tables in schema information_schema from 用户名;

#撤回在pg_catalog模式下的权限
revoke select on all tables in schema pg_catalog from 用户名;

#撤回对数据库的操作权限
revoke all on database 数据库名 from 用户名;

数据库管理

#查询所有数据库
select datname from pg_database;

#创建数据库
create database 数据库名 owner 所属用户 encoding UTF8;

删除数据库

#会连同schema一起删除
drop database 数据库名;