PostgreSQL 是一个免费的对象-关系（Object-Relationship）数据库服务器(ORDBMS)
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。
所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大量数据。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
ORDBMS术语
数据库：数据库是一些关联表的集合。
主键：主键是唯一的。一个数据表中只能包含一个主键。您可以使用主键来查询数据。
外键：外键用于关联两个表。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。
SQL 语言由三种命令组成：
数据操作语言（Data Manipulation Language，DML）：这是在你 90%时间内会使用的 SQL。这些命令用来在数据库中插入、删除、更新、查询数据。
数据定义语言（Data Definition Language，DDL）：有些命令用来建表、定义关系以及控制数据库的其他结构方面的信息。
数据控制语言（Data Control Language，DCL）：这是一套通常用来控制对数据的访问许可的命令集，例如定义访问权限。很多数据库用户从来不会使用这些命令，因为这通常在大公司使用，而通常情况下会有一个或者多个数据库管理员专门管理数据库，他们的工作之一就是控制访问许可。
一、安装
1.下载postgresql源码包：https://yum.postgresql.org/repopackages.php
2.安装源码包：yum install -y pgdg-redhat-repo-latest.noarch.rpm
安装所需依赖：yum install -y postgresql12-contrib postgresql12-server
新建用户：useradd -m postgres //新建一个用户
3.解压，进入源码文件目录下，执行： ./configure --prefix=/usr/local/pgsql
4.执行：make 、make install
5.配置postgres用户环境变量：
export PATH=/usr/local/pgsql/bin:${PATH}
export LD_LIBRARY_PATH=/usr/local/pgsql/lib${LD_LIBRARY_PATH:+:${LD_LIBRARY_PATH}}
6.初始化数据库目录及启动数据库：
initdb -D data (-W 在initdb的过程中为数据库超级用户设置一个密码) //初始化数据库
注意：不要在将要初始化的数据目录中手动创建任何文件,如果数据目录中已经有文件,会有错误提示，这样做的目的是为了防止无意中覆盖已有的数据目录。
pg_ctl -D data -l logfile start //启动数据库服务器
psql (-d postgres ) //访问数据库
查看数据库版本:SELECT version();
二、psql：PostgreSQL的交互式终端程序
psql是一个以终端为基础的postgresql前端。它允许你交互地键入查询，然后把它们发出给postgresql， 再显示查询的结果。另外，输入可以来自一个文件。还有，它提供了一些元命令和多种类似shell的特性来实现书写脚本以及对大量任务的自动化。
语法psql [option...] [dbname [username]]
1.命令行运行sql文件：psql -f 文件名；（或=# \i *.sql）
2.[option...]
-d dbname/--dbname=dbname：声明想要连接的数据库名称。等效于在命令行行上把 dbname声明为第一个非选项参数。
-h hostname /--host=hostname:指定主机（为了检查网络连接）。
-l /–list:列出所有可用的数据库，然后退出。其它非连接选项将被忽略。类似于元命令\list
-p port /--port=port:指定服务器侦听的TCP端口或本地Unix域套接字文件的扩展。 缺省使用环境变量QBPORT的值， 如果没有设置的话则使用编译时声明的端口(通常是 5432)。
-U username/--username=username以用户username 代替缺省用户与数据库连接。当然，你必须有这么做的权限
-V /-version:打印psql版本信息然后退出
-w /--no-password不提示密码。如果服务器需要密码验证而密码不可通过其他方式 （比如.pgpass文件）获得，则连接尝试将失败。 该选项在批量工作和不存在用户输入密码的脚本中很有帮助。这个选项对整个会话有效，所以它影响元命令\connect的使用， 还有初始连接尝试。
-W /–password强制psql在与一个数据库连接前提示输入口令。这个选项从来不是至关重要的，因为如果服务器需求密码认证， 则psql自动提示一个密码。不过，psql 将在找出服务器想要一个密码上浪费一个连接尝试。这个选项对整个会话有效，所以它影响元命令\connect的使用， 还有初始连接尝试。
-c '命令'： 在命令行仅运行单个命令（SQL或内部）
-i:启用远程访问(如果在 postgresql.conf 文件中启用listen_addresses选项）
3.元命令：\<command> <params>
\?显示关于反斜杠命令的帮助信息
\timing :命令的计时
\s :查看历史命令
\cd [ directory ]:把当前工作目录改变到directory。 没有参数则是改变到当前用户的家目录。使用\! pwd打印出当前工作目录。
\conninfo:输出关于当前数据库连接的信息。
\dT [S+] [ pattern ]:列出数据类型
\du [+] [ pattern ]:列出数据库角色。
\db 查看表空间列表
\sf 函数名：查看函数代码
generate_series 函数产生连续的整数,使用这个函数能非常方便地产生测试数据
\dt+ 表名：查看表大小
获取元命令对应的 SQL 代码：psql 提供的元命令实质上向数据库发出相应的 SQL 查询,当使用 psql 连接数据库时, -E选项可以获取元命令的 SQL 代码,如下所示
查看当前使用的数据目录：show data_directory;
查看当前登录的用户：select * from current_user;
三、Postgresql语法
查看命令用法：\help <cmd> ==== \h <cmd>
1.列举所有数据库： \l
postgres是用户，postgres是postgres用户下的数据库
2.创建数据库：create database 数据库名; / createdb
3.切换/进入数据库：\c 数据库名
4.在数据库中创建表：create table 表名（）；
\dt:查看所有table \dv:查看所有view \du:查看所有
\d 表名:列出了所有的关系——表、序列生成器和视图(如果在数据库里头有)
在表中增添一列：ALTER TABLE 表名 ADD 列名 数据类型;
往表中插入数据：insert into 表名（key1，key2，……） values （value1，value2，……）；
如果向表中的所有字段插入值，则可以不需要指定字段（key1，key2，……）
插入多行：
查看表内容：select * from 表名；
查看个别列的数据：select 列1，列2，…… from 表名；
select还可用于计算结果：
select old_column as new_column from 表;:将查找结果重新命名
给表赋予别名：select ... from 表名 别名;
查询总行数：
5.删除数据库：drop database 数据库名 （with force）；
删除表：drop table 表名；
重命名表明：alter table 原名 rename to 新名；
在已有表里添加/删除字段：alter table 表名 add/drop column 字段名 类型/字段名
6.psql 导入、导出表数据:\copy
(1)导入数据：COPY 表名 from '数据文本的绝对路径';(注：COPY 命令只有超级用户才能使用)
也可用\copy 表名 from '数据文本的绝对路径';(没有超级用户权限的情况下,需要导出小表数据,通常使用 \copy 元命令)
一、数据类型
1、数值类型
double precision：可接受real float numeric(第一参数时可能不接收numeric)
decimal == numeric float4==real float8==double precision
PostgreSQL 中的 float 则会自动转化为 double precision
兼容函数只考虑：double precision int（big/small？） oracle.number text
numeric
一个numeric类型的标度(scale)是小数部分的位数，精度(precision)是全部数据位的数目，也就是小数点两边的位数总和。
要声明一个字段的类型为numeric，可以用以下三种语法：NUMERIC(precision, scale)意为同时指定标度和精度，精度必须为正数，标度可以为零或者正数。NUMERIC(precision)选择了标度为0，不带任何精度与标度的声明，则创建一个可以存储一个直到实现精度上限的任意精度和标度的数值。如果一个要存储的数值的标度比字段声明的标度高，那么系统将尝试圆整(四舍五入)该数值到指定的小数位。然后，如果小数点左边的数据位数超过了声明的精度减去声明的标度，那么将抛出一个错误。
除了普通的数字值之外，numeric类型允许用特殊值NaN表示"不是一个数字"。任何在NaN上面的操作都生成另外一个NaN。
1.内存中的实现
首先要知道比较重要的一个宏定义NBASE，定义值为10000，表示单个digit的范围为0-9999。由于用两个字节(int16)可以覆盖这个范围，所以digits的类型NumericDigit使用了int16。
ndigits表示数字占用的digit个数，ndigits的值为一个数字去掉前导0和末尾0剩下的有效digit位数。
weight为数字最高位表示的权重，简单理解为小数点的偏移，以NBASE为基本单位，这个值可以为正，可以为负，也可以为0。例如weight为1，表示最高位的digit要乘以10000，如果weight为-1，表示最高位的digit要乘以10-4。
sign表示正负符号，为0则表示数字为正，为1则表示数字为负。
dscale表示小数位数，也即引言中提到的标度，通过ndigts和dscale可以确定数字中有效位数。
buf该数组不直接访问，一般先开一个到两个元素的空间作为缓冲，下文将具体分析其作用。
digits作为一个柔型数组，在结构体的末尾，是存储digit的数组空间，其空间是动态开辟的，使用起来类似于一个指针。
oracle数据类型
增加列约束：
2、字符
text可以直接强转为numeric，但是不可强转为int，可通过(::numeric)::int达到目的
2.日期/时间
3、布尔
4、枚举
PostgtesSQL中的枚举类型类似于 C 语言中的 enum 类型。
与其他类型不同的是枚举类型需要使用 CREATE TYPE 命令创建。
5、数组
PostgreSQL 允许将字段定义成变长的多维数组。
数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。
也可以使用 "ARRAY" 关键字
访问数组：
修改数组
二、PostgreSQL数据库对象
datebase
database一词含义宽泛，既可表示广义的数据库系统，又可以表示某些特定数据库系统中的某一级数据存储单位。
schema
database的下一层逻辑结构就是schema。如果把 database 比作一个国家，那么schema就是一些独立的省、府、辖区等。大多数对象是隶属于某个schema的，然后schema又隶属于某个database。在创建一个新的database时，PostgreSQL会自动为其创建一个名为public的schema。如果未设置search_path变量，那么PostgreSQL会将你创建的所有对象默认放入 public schema中。如果表的数量较少,这是没问题的，但如果你有几千张表，建议将它们分门别类放入不同的schema中。
PostgreSQL 模式（SCHEMA），可以看着是一个表的集合。一个模式可以包含视图、索引、据类型、函数和操作符等。相同的对象名称可以被用于不同的模式中而不会出现冲突例如 schema1 和 myschema 都可以包含名为 mytable 的表。和数据库不同，模式并不是被严格地隔离：一个用户可以访问他们所连接的数据库中的所有模式内的对象，只要他们有足够的权限。
使用模式的优势：
允许多个用户使用一个数据库并且不会互相干扰。
将数据库对象组织成逻辑组以便更容易管理。
第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。
模式类似于操作系统层的目录，但是模式不能嵌套。
1、创建模式：create schema 模式名；
2、在该模式下创建表：create table 模式名.表名（）；
查看该模式下的表：select * from 模式名.表名;
查看该模式下的所有表：select tablename from pg_tables where schemaname = 'pg_catalog' order by tablename;
3、删除模式：drop schema 模式名；
删除一个模式以及其中包含的所有对象：DROP SCHEMA 模式名 CASCADE;
查看所有模式名：
SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname;
search_path
限定名写起来很冗长，通常最好不要把一个特定模式名拉到应用中。因此，表名通常被使用非限定名来引用，它只由表名构成。系统将沿着一条搜索路径来决定该名称指的是哪个表，搜索路径是一个进行查看的模式列表。 搜索路径中第一个匹配的表将被认为是所需要的。如果在搜索路径中没有任何匹配，即使在数据库的其他模式中存在匹配的表名也将会报告一个错误。
在不同方案中创建命名相同的对象的能力使得编写每次都准确引用相同对象的查询变得复杂。这也使得用户有可能更改其他用户查询的行为，不管是出于恶意还是无意。由于未经限定的名称在查询中以及在PostgreSQL内部的广泛使用，在search_path中增加一个方案实际上是信任所有在该方案中具有CREATE特权的用户。在你运行一个普通查询时，恶意用户可以在你的搜索路径中的以方案中创建能够夺取控制权并且执行任意SQL函数的对象，而这些事情就像是你在执行一样。
搜索路径中的第一个模式被称为当前模式。除了是第一个被搜索的模式外，如果CREATE TABLE命令没有指定模式名，它将是新创建表所在的模式。
catalog
catalog是系统级的schema，用于存储系统函数和系统元数据。每个database创建好以后默认都会含有两个catalog:一个名为pg_catalog，用于存储PostgreSQL系统自带的函数、表、系统视图、数据类型转换器以及数据类型定义等元数据，另一个是information_schena，用于存储ANSI标准中所要求提供的元数据查询视图，这些视图遵从ANSI SQL标准的要求，以指定的格式向外界提供 PostgreSQL元数据信息。（pg_catalog这个schema默认就在搜索路径search_path的最前面，并且是删除不掉的，它的优先级排在所有schema的前面。比如有两个重名的pg_class分别在这两个schema里面。pg_catalog.pg_calss, public.pg_class, 如果你执行select * from pg_class，那么搜索的是pg_catalog.pg_class.因此我们在创建extension或者创建一些共用对象时，可以创建在public里面，也可以创建在pg_catalog里面。创建在pg_catalog里面的好处是优先级最高，所有用户可见）
Infornation_schena catalog在 MySQL和SQL Server 中也有。PostgreSQL Infornation_schena中最常用的视图一般有以下几个: colunns视图，列出了数据库中的所有表列;tables视图，列出了数据库中的所有表（包括视图)﹔views视图，列出了所有视图以及用于构建或重新构建该视图的关联SQL。同样，在MySQL和SQL Server中也有这些视图，不过它们所含的列没有PostgreSQL那么多。
table
任何一个数据库中，表都是最核心和最“忙碌”的对象类型。在 PostgreSQL中，表首先属于某个schema，而 schema又属于某个database，这样就构成了一种三级存储结构。PostgreSQL的表支持两种很强大的功能。第一种是表继承，即一张表可以有父表和子表。这种层次化的结构可以极大地简化数据库设计,还可以为你省掉大量的重复查询代码。
三、运算符
1、逻辑运算符：
2、位运算符：&、 | 和 ^（异或，也可为#）
3、比较运算符：
四、子查询
子查询语句中读取 SALARY(薪资) 字段大于 65000 的数据，然后通过 EXISTS 运算符判断它是否返回行，如果有返回行则读取所有的 AGE(年龄) 字段。
五、简单语句
1、update：更新（修改）数据库中的数据
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
2、删除表中数据：DELETE FROM table_name [WHERE [condition]];
或TRUNCATE TABLE table_name;
delete和truncate的区别:
1、在功能上，truncate是清空一个表的内容，它相当于delete from table_name。
2、delete是dml操作，truncate是ddl操作；因此，用delete删除整个表的数据时，会产生大量的roolback，占用很多的rollback segments， 而truncate不会。
3、在内存中，用delete删除数据，表空间中其被删除数据的表占用的空间还在，便于以后的使用，另外它是“假相”的删除，相当于在windows中用delete删除数据是把数据放到回收站中，还可以恢复，当然如果这个时候重新启动系统（OS或者RDBMS），它也就不能恢复了！
而用truncate清除数据，内存中表空间中其被删除数据的表占用的空间会被立即释放，相当于windows中用shift+delete删除数据，不能够恢复！
5、truncate 只能对TABLE，delete 可以是table,view,synonym。
6、TRUNCATE TABLE 的对象必须是本模式下的，或者有drop any table的权限 而 DELETE 则是对象必须是本模式下的，或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的权限。
7、在外层中，truncate或者delete后，其占用的空间都将释放。
8、truncate和delete只删除数据，而drop则删除整个表（结构和数据）。
区别
1.TRUNCATE TABLE是非常快的
2.TRUNCATE之后的自增字段从头开始计数了，而DELETE的仍保留原来的最大数值
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。
注意:这里说的delete是指不带where子句的delete语句
相同点
truncate和不带where子句的delete, 以及drop都会删除表内的数据
不同点:
1. truncate和 delete只删除数据不删除表的结构(定义)
drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.
2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.
truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
3.delete语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动
显然drop语句将表所占用的空间全部释放
truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage; truncate会将高水线复位(回到最开始).
4.速度,一般来说: drop>; truncate >; delete
5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.
想删除表,当然用drop
想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete.
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据
3、查看包含特定字符的数据：SELECT FROM table_name WHERE column LIKE '%XXXX'; 也可用_
在 PostgreSQL 中，LIKE 子句是只能用于对字符进行比较
找出 AGE 以 2 开头的数据：
4、限制 SELECT 语句中查询的数据的数量：SELECT * FROM 表名 LIMIT 3 OFFSET 2;
从第三位开始（偏移量为2即跳过前两个数据从第三位开始）提取 3 个记录
5、排序：order by 列名 ASC|DESC；
6、group by：对相同的数据进行分组
7、with：一种编写辅助语句的方法，以便在更大的查询中使用
name_for_summary_data 是 WITH 子句的名称，name_for_summary_data 可以与现有的表名相同，并且具有优先级。可以在 WITH 中使用数据 INSERT, UPDATE 或 DELETE 语句，允许在同一个查询中执行多个不同的操作。
8、having：筛选分组后的各组数据
WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。
9、distinct：去除重复记录，只获取唯一的记录。
六、复杂语句
1、约束
约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
约束确保了数据库中数据的准确性和可靠性。
以下是在 PostgreSQL 中常用的约束：
NOT NULL：指示某列不能存储 NULL 值。
UNIQUE：确保某列的值都是唯一的。
PRIMARY Key：NOT NULL 和 UNIQUE 的结合。确保某一列有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN Key： 保证一个表中的数据匹配另一个表中的值的参照完整性。（REFERENCES）
CHECK： 保证列中的值符合指定的条件。
EXCLUSION ：排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。
删除约束：ALTER TABLE table_name DROP CONSTRAINT some_name;
2、join 表名 on 条件:连接
JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。在 PostgreSQL 中，JOIN 有五种连接类型：
CROSS JOIN ：交叉连接，把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。
INNER JOIN：内连接，把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。
LEFT OUTER JOIN：左外连接
RIGHT OUTER JOIN：右外连接
FULL OUTER JOIN：全外连接
3、union：合并两个或多个 SELECT 语句的结果
4、别名
5、触发器
触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。
PostgreSQL 触发器可以在下面几种情况下触发：
*在执行操作之前before（在检查约束并尝试插入、更新或删除之前）
*在执行操作之后after（在检查约束并插入、更新或删除完成之后）
*更新操作update（在对一个视图进行插入、更新、删除时）
6、视图：create view 视图名 as SQL语句；
*是一张虚拟表，只不过是通过相关的名称存储在数据库中的一个 PostgreSQL 语句。实际上是一个以预定义的 PostgreSQL 查询形式存在的表的组合。
*可以从一个或多个表创建，这取决于要创建视图的 PostgreSQL 查询。
PostgreSQL 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。
七、事务和锁
数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。
在数据库系统上执行并发操作时，事务是作为最小的控制单元来使用的，特别适用于多用户同时操作的数据库系统。
1.事务的四大特性
原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。比如在同一个事务中的SQL语句，要么全部执行成功，要么全部执行失败。
一致性（Consistency）：官网上事务一致性的概念是：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。换一种方式理解就是：事务按照预期生效，数据的状态是预期的状态。举例说明：张三向李四转100元，转账前和转账后的数据是正确的状态，这就叫一致性，如果出现张三转出100元，李四账号没有增加100元这就出现了数据错误，就没有达到一致性。
隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
2.将数据改变分组到逻辑单元
BEGIN 开始一个事务
SAVEPOINT savepointname 告诉服务器需要记住事务的当前状态。这个语法只能在 BEGIN 之后且在 COMMIT或者 ROLLBACK 之前;也就是说,必须在一个事务中。
COMMIT 表明所有的事务的元素都完成了,现在需要对所有的并行的事务和子事务进行持久化并对使其结果(在本事务之外)可访问。
ROLLBACK [TO savepointname] 表明这个事务将被放弃,SQL 事务中所有对数据的改变将被取消。数据库给所有用户的表现是从 BEGIN 开始的任何改变都没有发生,且事务被关闭。另一个带 TO 从句的版本允许我们回滚到一个命名的保存点,且不完成这个事务。
2.锁
共享锁,允许其他用户读,但不允许修改数据
排他锁,甚至避免其他事务读数据
八、PRIVILEGES（权限）
无论何时创建数据库对象，都会为其分配一个所有者，所有者通常是执行 create 语句的人。对于大多数类型的对象，初始状态是只有所有者(或超级用户)才能修改或删除对象。要允许其他角色或用户使用它，必须为该用户设置权限。
在 PostgreSQL 中，权限分为以下几种：
SELECT INSERT UPDATE DELETE TRUNCATE REFERENCES TRIGGER CREATE CONNECT TEMPORARY EXECUTE USAGE
一、授权 grant
GRANT 权限 ON [table|view|user] 对象名 TO {PUBLIC |username }
username − 要授予权限的用户名。PUBLIC 是代表所有用户的简短形式。
二、回收权限 revoke
REVOKE 权限 ON 对象名 FROM {PUBLIC |username }
postgresql源码分析
1.第一级目录结构
进入PostgreSQL的源码目录后，第一级的结构如下所示：
PostgreSQL 的src下面有
这里比较核心的是backend,bin,interface这几个目录。Backend是对应于后端，bin和interface对应于前端。
bin里面有pgsql,initdb,pg_dump等各种工具的代码。interface里面有PostgreSQL的C语言的库libpq,另外可以在C里嵌入SQL的ECPG命令的相关代码。
Backend目录的结构如下：
backend等的代码的头文件包含在include里面。其组织虽然与backend的目录结构类似，但是并非完全相同，基本上来说下一级的子目录不再设下一级目录。例如backend的目录下面有utils这个目录，而util下面还有adt这个子目录，但是include里面省略了这个目录，变成了扁平的结构。
PostgreSQL安装目录
share :存放着 PostgreSQL 的文档、man、示例文件以及一些扩展
include ：PostgreSQL 的 C、C++ 的 头 文 件
bin：PostgreSQL 的 应 用 程 序 即可执行命令
PostgreSQL本身是一个 C/S 架构的程序,这些应用程序可以分为两类:客户端程序和服务器程序。
一、客户端程序
1. 封装 SQL 命令的客户端程序
clusterdb ：SQL CLUSTER 命令的一个封装。PostgreSQL 是堆表存储的,clusterdb 通
过索引对数据库中基于堆表的物理文件重新排序,它在一定场景下可以节省磁盘访问,加
快查询速度。
createdb/dropdb：SQL 命令 CREATE DATABAS 和 DROP DATABASE 的封装。
createuser/dropuser：SQL 命令 CREATE USER 和 DROP USER 的封装
2. 备份与恢复的客户端程序
pg_basebackup 取得一个正在运行中的 PostgreSQL 实例的基础备份。
pg_dump 和 pg_dumpall 都是以数据库转储方式进行备份的工具。
pg_restore 用来从 pg_dump 命令创建的非文本格式的备份中恢复数据。
二、服务器程序
三、数据库配置基础
PostgreSQL 有两个重要的全局配置文件:postgresql.conf 和 pg_hba.conf。它们提供了很多可配置的参数,这些参数从不同层面影响着数据库系统的行为,postgresql.conf 配置文件主要负责配置文件位置、资源限制、集群复制等,pg_hba.conf (HBA表示基于主机的认证)文件则负责客户端的连接和认证。这两个文件都位于初始化数据目录（initdb所产生的目录中）。
1.pg_hba.conf:PostgreSQL 客户端认证配置文件
pg_hba.conf文件的常用格式是一组记录，每行一条。空白行将被忽略， #注释字符后面的任何文本也被忽略。记录不能跨行。一条记录由若干用空格 和 / 或 制表符分隔的域组成。如果域值用双引号包围，那么它可以包含空白。在数据库、用户或地址域中 引用一个关键字（例如，all或replication）将使该词失去其特殊含义，并且只是匹配一个有该名字的数据库、用户或主机。
每条记录指定一种连接类型、一个客户端 IP 地址范围（如果和连接类型相关）、一个数据库名、一个用户名以及对匹配这些参数的连接使用的认证方法。第一条匹配连接类型、客户端地址、连接请求的数据库和用户名的记录将被用于执行认证。这个过程没有“落空”或者“后备”的说法：如果选择了一条记录而且认证失败，那么将不再考虑后面的记录。如果没有匹配的记录，那么访问将被拒绝。
pg_hba.conf 是它所在数据库实例的“防火墙”
每一行的作用是:允许哪些主机可以通过什么连接方式和认证方式通过哪个数据库用户连接哪个数据库。也就是允许 ADDRESS 列的主机通过 TYPE 方式以 METHOD 认证方式通过 USER 用户连接 DATABASE 数据库。
(1)连接方式
TYPE 列标识允许的连接方式,可用的值有: local、host、hostssl、hostnossl,说明如下:
local :匹配使用 Unix 域套接字的连接。如果没有 TYPE 为 local 的条目则不允许通过Unix 域套接字连接。
host :匹配使用 TCP/IP 建立的连接 , 同时匹配 SSL 和非 SSL 连接。默认安装只监听本地环回地址 localhost 的连接 , 不允许使用 TCP/IP 远程连接 , 启用远程连接需要修改 postgresql.conf 中的 listen_addresses 参数。
hostssl :匹配必须是使用 SSL 的 TCP/IP 连接。配置 hostssl 有三个前提条件:
1. 客户端和服务端都安装 OpenSSL;
2. 编译 PostgreSQL 的时候指定 configure 参数 --with-openssl 打开 SSL 支持;
3. 在 postgresql.conf 中配置 ssl = on。
hostnossl 和 hostssl 相反,它只匹配使用非 SSL 的 TCP/IP 连接。
(2)目标数据库：DATABASE 列标识该行设置对哪个数据库生效;
(3)目标用户：USER 列标识该行设置对哪个数据库用户生效;
(4)访问来源：ADDRESS 列标识该行设置对哪个 IP 地址或 IP 地址段生效;
0.0.0.0/0表示所有 IPv4 地址，并且::0/0表示所有 IPv6 地址
(5)认证方法：METHOD列标识客户端的认证方法, 常见的认证方法有 trust、reject、md5 和password 等。
trust:无条件地允许连接。这种方法允许任何可以与PostgreSQL数据库服务器连接的用户以他们期望的任意PostgreSQL数据库用户身份登入，而不需要口令或者其他任何认证。
reject:无条件地拒绝连接。这有助于从一个组中“过滤出”特定主机，例如一个reject行可以阻塞一个特定的主机连接，而后面一行允许一个特定网络中的其余主机进行连接。
md5:执行SCRAM-SHA-256或MD5认证来验证用户的口令。
***方法scram-sha-256按照RFC 7677中的描述执行SCRAM-SHA-256认证。它使用的是一种挑战-响应的方案，可以防止在不可信连接上对口令的嗅探并且支持在服务器上以一种加密哈希的方式存放口令，因此被认为是安全的。
***方法md5使用一种自定义的安全性较低的挑战-响应机制。它能防止口令嗅探并且防止口令在服务器上以明文存储，但是无法保护攻击者想办法从服务器上窃取了口令哈希的情况。此外，现在认为MD5哈希算法对于确定攻击已经不再安全。
password:要求客户端提供一个未加密的口令进行认证。因为口令是以明文形式在网络上发送的，所以我们不应该在不可信的网络上使用这种方式。
2.postgresql.conf：数据库配置文件
postgresql.conf 配置文件的文件结构很简单,由多个 configparameter = value 形式的行组成,“#”开头的行为注释。 value 支持的数据类型有布尔、整数、浮点数、字符串、枚举,value 的值还支持各种单位,例如 MB、GB 和 ms、min、d 等。postgresql.conf 文件还支持include 和 include_if_exists 指令,并且允许嵌套。
在配置项末尾标记了“ # (change requires restart) ”的配置项是需要重启数据库实例才可以生效的,其他没有标记的配置项只需要 reload 即可生效。
(1)全局配置的修改方法
修改全局配置的方法有:
***修改 postgresql.conf 配置文件。
***使用 vim、nano 类的文本编辑器或者 sed 命令编辑它们。
***通过 ALTER SYSTEM 命令修改全局配置,例如:mydb=# ALTER SYSTEM SET listen_addresses = '*';通过 ALTER SYSTEM SQL 命令修改的全局配置参数,会自动编辑 postgresql.auto.conf文件,在数据库启动时会加载 postgresql.auto.conf 文件,并用它的配置覆盖 postgresql.conf中已有的配置。这个文件不要手动修改它。
***启动数据库时进行设置,例如:[postgres@pghost1 ~]$ /opt/pgsql/bin/postgres -D /pgdata/10/data -c port=1922
(2)非全局配置的修改方法
***设置和重置 Database 级别的配置,例如:
ALTER DATABASE name SET configparameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET configuration
***设置和重置 Session 级别的配置。
***通过 SET 命令设置当前 Session 的配置
(3)使配置生效:SELECT pg_reload_conf();或pg_ctl -D data reload
3.允许远程访问数据库
在默认情况下,PostgreSQL 实例是不允许通过远程访问数据库的。通过以下配置方法,允许从远程访问数据库:
1.修改监听地址
(1) PostgreSQL 管理监听地址的配置项为 postgresql.conf 文件中的 listen_addresses。默认安装只监听本地环回地址 localhost 的连接 , 不允许使用 TCP/IP 建立远程连接,启用远程连接 需 要 修 改 postgresql.conf 中 的 listen_addresses 参 数。
***what IP address(es) to listen on—监听什么 IP 地址?也就是允许用哪些 IP 地址访问,可以是一个 IP,也可以是多个 IP 地址。
***comma-separated list of addresses;—以逗号分隔地址列表。
***defaults to 'localhost'; use '*' for all— 默 认 是“ localhost ”, 使 用“ * ” 允 许 所有 地 址; 大 多 数 的 高 可 用 架 构 都 使 用 VIP 的 方 式 访 问 数 据 库, 所 以 我 们 一 般 设置为“ * ”。
***(change requires restart)—修改这个参数需要重新启动数据库。
去掉 listen_addresses 这一行开头的“ # ”号,并把它的值修改为“ * ”,即允许所有地址访问数据库,如下所示:listen_addresses = '*',修改完成之后重启数据库使配置生效。
2.修改 pg_hba.conf 文件
修改监听地址之后,还需要修改 pg_hba.conf 文件,回答 pg_hba.conf 的问题:允许哪些主机可以通过什么连接方式和认证方式通过哪个数据库用户连接哪个数据库?
修改 pg_hba.conf 文件之后需要 reload 使它生效，现在就可以通过远程访问数据库了。
允许全部ip访问：host all all 0.0.0.0/0 trust
四、客户端工具
PostgreSQL 客户端工具,例如 pgAdmin 和 psql。pgAdmin 是一款功能丰富、开源免费的 PostgreSQL 图形化客户端工具,psql 是 PostgreSQL 自带的命令行客户端工具,功能全面,是 PostgreSQL 数据库工程师必须熟练掌握的命令行工具之一。
Pldebugger，PostgreSQL调试器
对于学习一门编程语言，头脑中第一个想到的就是debug，但是pg标准安装是不支持debug的，需要安装pldebugger扩展。
一、调试的先决条件
要在 Postgres 服务器上启用 PL/pgSQL 过程的交互式调试，需要使用plugin_debugger。plugin_debugger是作为扩展交付的典型交互式调试器。它需要 Postgres 中的共享库预加载来操作设置中的 shared_preload_libraries 参数。调试器由 EDB 开发和维护。其源代码可供检查和改进。对存储过程或者函数进行调试
调试器提供了调试 PL/SQL 过程所需的服务器 API：断点管理；逐步追踪；变量采集和管理。
二、安装
1.如果通过yum安装的 PostgreSQL 12 ，则可直接用yum安装pg_debugger：
sudo yum install pldebugger (postgresql-12-pldebugger)
启动服务：sudo service postgresql restart
2.通过源码安装：
(1)https://github.com/EnterpriseDB/pldebugger
git clone git@github.com:EnterpriseDB/pldebugger.git
cp -r pldebugger/ PostgreSQL/contrib/
cd PostgreSQL/contrib/
make && make install
安装完成后会在数据库安装位置产生一个plugin_debugger.so文件(/usr/local/pgsql/lib/plugin_debugger.so)
三、配置文件
1.在自己的数据库文件（data）中编辑postgresql.conf：
shared_preload_libraries = '$libdir/plugin_debugger.so'
2.重新启动PostgreSQL以使新的设置生效
3.导入插件：
安装pgadmin4
启动pgadmin4：sudo /usr/pgadmin4/bin/setup-web.sh
更改监听ip：vi postgresql.conf 加入listen_addresses = '*'
修改数据库认证配置文件：pg_hba.conf
四、使用方法
连接pgAdmin到包含你想调试的函数的数据库。右键单击要调试的函数，并选择调试->调试，以立即执行并调试该函数。立即执行并调试该函数，或者选择调试->设置全局断点来在该函数上设置一个断点。这将导致调试器等待，这将导致调试器等待另一个会话（如服务于网络应用的后端）来执行该函数，并允许你在上下文中进行调试。
五、故障排除
我们在使用该插件时遇到的大多数问题都是由以下原因造成的:没有将调试器插件库加入（或不正确地加入）到shared_preload_libraries配置指令中（之后，服务器*必须*****）。这将阻止全局断点在所有平台上工作，而在某些平台上（特别是Windows）可能会阻止pldbgapi.sql脚本不能正确执行。
六、架构
调试器由三部分组成：
1. 客户端（debugger client）。这通常是一个GUI显示源代码、当前堆栈帧、变量等，并允许用户设置断点和单步执行代码。客户端可以驻留在不同的主机上 数据库服务器。
2. 目标后端（Target backend）。这是运行正在被调试的代码的后端。plugin_debugger.so库必须被加载到目标后端。
3. 调试代理（Debugging proxy）。这是用于客户端连接的另一个后台进程。API函数，即pldbgapi.so库中的pldbg_*，在这个后端运行。
客户端使用常规的libpq连接。当调试会话被激活时，代理通过套接字连接到目标。代理和目标之间的协议对其他人来说是不可见的，而且会有变化。API函数（pldbg_*）构成了调试设施的公共接口。
七、原理解释
①文件目录
pldbgapi.c:
此模块定义（并实现）用于调试PL的API（尤其是职能和程序用PL/pgSQL或edb spl编写）。要调试函数或过程，需要两个后端进程加上调试器客户端（客户端可以是命令行客户端，例如psql，但更可能是图形客户端，例如pgAdmin）。
*第一个后端称为目标-这是一个运行要调试的代码。
*第二个后端是一个“代理”进程，它在调试器客户端和目标。中实现的功能该模块称为代理函数。
*代理进程为调试器提供了一种简单、安全的方法，客户端连接到目标-客户端打开一个普通（大概）知道如何工作的libpq式连接，通过防火墙和身份验证迷宫（一次连接过程完成，调试器客户端已连接发送给代理）。
*调试器客户端可以调用此API中的任何函数。每个函数都由代理进程执行。代理人穿梭调试请求（如“进入”或“显示呼叫”堆栈'）到调试器服务器（在目标内部运行进程）并将结果发送回调试器客户端。
使用这个API有几个基本规则：
* 你必须先调用其中的一个连接函数，然后才可以做其他任何事情（在这一点上，唯一的连接函数是'pldbg_attach_to_port()'，但我们会在实现全局断点后立即增加更多的连接函数）。每个连接函数返回一个标识该调试会话的会话ID（一个调试器客户端可以通过跟踪每个会话标识符来维护多个同步会话）。 你把这个会话ID给所有其他的代理函数， 一旦你打开了一个会话，你必须等待目标到达一个断点（它可能已经停在一个断点上）。通过调用pldbg_wait_for_breakpoint( sessionID ) - 该函数将挂起，直到目标机到达断点（或目标机会话结束）。当目标机暂停时，你可以与调试器服务器进行交互(在目标进程中运行)，通过调用任何其他的代理函数。 例如，要告诉目标进程 "进入 "一个函数/程序调用，你可以调用pldbg_step_into()（并且该函数会挂起，直到目标机暂停）。 要告诉目标程序继续运行直到下一个断点，你可以调用pldbg_continue()（同样，该函数会挂起直到目标暂停）。每次目标暂停时，它都会返回一个类型为 "断点 "的元组。该元组包含了目标函数暂停的OID以及目标暂停的行数。事实上，该目标返回一个类型为breakpoint的元组并不意味着该目标暂停在一个断点上--它可能是因为一个跨步操作或步入操作。当目标在断点处暂停时（或在跨步或跨入后暂停），你可以通过调用pldbg_get_stack(), pldbg_get_source(), pldbg_get_breakpoints(), 或pldbg_get_variables()。 调试器服务器摸索PL调用堆栈并保持一个"焦点 "框架。 默认情况下，调试器服务器聚焦于嵌套最深的框架（因为那是实际在运行的代码运行的代码）。 你可以把调试器的焦点转移到一个不同的框架上通过调用pldbg_select_frame()。焦点是很重要的，因为许多函数（如pldbg_get_variables()）是针对拥有焦点的堆栈帧工作的。
* 任何一个代理函数都可能产生错误--特别是，如果一个代理如果目标进程结束，代理函数会抛出一个错误。 当你调用pldbg_continue()并且目标进程运行到结束时，你最有可能遇到一个错误：目标进程运行到结束（没有触及另一个断点）
②create extension后产生21个API函数
postgres扩展SQL
http://www.postgres.cn/docs/12/extend-how.html
一、postgres类型系统
1.基础类型
基础类型是那些被实现在SQL语言层面之下的类型（通常用一种底层语言，如 C），例如integer。它们通常对应于常说的抽象数据类型。PostgreSQL只能通过由用户提供的函数在这类类型上操作，并且只能理解到用户描述这种类型行为的程度。
2.容器类型
PostgreSQL有三种“容器”类型，它们是包含多个其他类型值的类型。它们是数组、组合以及范围。
只要用户创建一个表，就会创建组合类型或者行类型。也可以使用CREATE TYPE来定义一个没有关联表的“stand-alone”组合类型。一个组合类型只是一个具有相关域名称的类型列表。一个组合类型的值是一个行或者域值记录。用户可以访问来自SQL查询的组成域。
范围类型可以保存同种类型的两个值，它们是该范围的上下界。范围类型是用户创建的，不过也存在一些内建的范围类型。
3.域
一个域是基于一种特定底层类型的，并且出于很多目的它可以与其底层类型互换。不过，一个域能够具有约束来限制它的合法值于其底层基础类型允许值的一个子集。可以使用SQL命令CREATE DOMAIN创建域。
4.伪类型
有一些用于特殊目的“伪类型”。伪类型不能作为表列或者容器类型的组件出现，但是它们能被用于声明函数的参数和结果类型。这在类型系统中提供了一种机制来标识函数的特殊分类。
5.多态类型
anyelement、anyarray、anynonarray、anyenum以及anyrange，它们被统称为多态类型。任何使用这些类型声明的函数被称作是一个多态函数。通过使用根据一次特定调用实际传递的数据类型所决定的相关数据类型，一个多态函数能够在多种不同数据类型上操作。
二、用户定义的函数
PostgreSQL提供四种函数：查询语言函数（用SQL编写的函数）、过程语言函数（例如，用PL/pgSQL或PL/Tcl编写的函数）、内部函数、C 语言函数。
1.查询语言(SQL)函数
注意：在被执行前，SQL 函数的整个主体都要被解析。虽然 SQL 函数可以包含修改系统目录的命令（如CREATE TABLE），但这类命令的效果对于该函数中后续命令的解析分析不可见。例如，如果把CREATE TABLE foo (...); INSERT INTO foo VALUES(...);打包到一个 SQL 函数中是得不到预期效果的，因为在解析INSERT命令时foo还不存在。在这类情况下，推荐使用PL/pgSQL而不是 SQL 函数。
(1)SQL 函数参数只能被用做数据值而不能作为标识符。例如这是合理的：
注意我们为该函数的结果在函数体内定义了一个列别名（名为result），但是这个列别名在函数以外是不可见的。因此，结果被标记为one而不是result。
也能省掉参数的名称而使用数字：
(2)当你使用一个返回组合类型的函数时，你可能只想要其结果中的一个域（属性）。 你可以这样做：
2.过程语言函数
过程是一种类似于函数的数据库对象。两者的区别在于过程不返回值，因此没有返回类型声明。而函数可以作为一个查询或者DML命令的一部分被调用，过程则需要明确地用CALL语句调用。
版本 1 的调用约定
注意我们已经把函数指定为“strict”，这意味着如果有任何输入值为空，系统应该自动假定得到空结果。通过这种做法，我们避免在函数代码中检查空值输入。如果不这样做，我们必须使用PG_ARGISNULL()明确地检查空值输入。
宏PG_ARGISNULL(n)允许一个函数测试是否每一个输入为空（当然，只能在没有声明为“strict”的函数中这样做）。和PG_GETARG_xxx()宏一样，输入参数也是从零开始计数。注意应该在验证了一个参数不是空之后才执行PG_GETARG_xxx()。要返回一个空结果，应执行PG_RETURN_NULL()，它对严格的以及非严格的函数都有用。
这里，MODULE_PATHNAME表示共享库文件的目录（例如PostgreSQL的教程目录，它包含这一节中用到的例子的代码）。（更好的风格是先把DIRECTORY放入搜索路径，在AS子句中只使用'funcs'。在任何情况下，我们可以为一个共享库省略系统相关的扩展名，通常是.so）。
GreenPlum
术语约定
第一章 GP数据库架构
GP 是一个纯软件实现的 MPP （Massively Parallel Processing，即大规模并行处理）数据库产品,采用 Share-Nothing 架构,可管理和处理分布在多个不同主机上的大规模数据集。
对于 GP 数据库来说,一个数据库集群是由多个独立的 PostgreSQL 实例构成的,它们分布在不同的主机上,实例之间协同工作,用户可以像使用一个普通的单机数据库那样,进行访问和执行 SQL 操作。其中Master 是整个系统的访问入口,负责处理客户端的连接和 SQL 命令、协调系统中的其他实例协同工作,计算实例负责管理和处理具体的业务数据,并将处理结果反馈给Master。
1.管理节点:Master
Master 作为 GP 的访问入口,主要负责：处理客户端连接的访问以及用户提交的SQL 语句的解析、生成执行计划、优化执行计划等。Master 不存储业务数据,只存储用于维持系统运行的全局信息,比如,对象定义信息,统计信息等, Master 非常重要,如果 Master 丢失,即便是原厂专业技术支持,也不能保证恢复所有信息。
Master 目前采取的是 Active-Standby 的高可用模式,当 Master 处于 Active状态时,备用Master(简称为 Standby)是不能接受连接请求和 SQL 访问的。虽然只有一个 Master,其资源依然很空闲,并不会成为性能的瓶颈,同时,因为是单 Master,可以最大限度的规避多 Master 架构的系统表频繁不一致的缺陷。
GP 是基于 PostgreSQL 发展而来,用户端可以如同访问 PostgreSQL 那样与 GP进行交互。可以通过 PostgreSQL 客户端程序(如 psql、pgAdminIII)和应用程序接口(APIs(如 JDBC、ODBC))连接 GP。
Master 上存储着全局系统表(Global System Catalog)(包含数据库系统自身元数据的数据表),但不存储任何业务数据,业务数据只存储在 Instance（segment） 上。Master 负责客户端的登录认证、SQL 命令接收并生成并行执行计划、对执行计划进行优化、在 Instance 之间分发执行计划、整合 Instance 处理结果、将 Instance 的处理结果汇总并反馈给客户端程序。
目前,GP 还不支持 Master 的自动故障切换,不过,已经有很多人适用工具或者脚本的形式实现了 Master 和 Standby 的自动 FailOver 效果：当 Master 出现无法正常工作的故障时,自动激活 Standby 来接管 Master的任务。
Master 的连接数是有限的,缺省值为 250 个,如果要大规模提升连接的可用数量,可以配置使用 GP 自带的 pgbouncer 连接池,这对于一些应用场景会很有帮助,例如 SAS 等软件连接 GP 时,由于这些软件自身无法严格限制连接数,pgbouncer 会是一个有效的缓解连接数过大的方案.
2.计算实例:Instance(segment)
在 GP 系统中,Instance 才是承担数据存储和查询处理的角色。用户数据表和相应的索引都分布在 GP 系统中各个 Instance 上,每个 Instance 存储着一部分数据(对于复制表来说,每个 Instance 存储一份完整的数据),Instance 才是真正进行数据处理的地方。缺省情况下,用户不能跳过 Master直接访问 Instance,而只能通过 Master 来访问整个数据库系统,不过,对于管理员来说,有时需要使用 Utility 模式来访问 Instance,访问方法是:
在 GP 推荐的硬件配置环境下,每个 Instance 需要对应数个 CPU Core 的资源资源,具体的比例需要根据数据库的适用场景进行综合评估。
在 GP 推荐的硬件配置环境下,每个 Instance 需要对应数个 CPU Core 的资源资源,具体的比例需要根据数据库的适用场景进行综合评估。例如在生产环境,每个Instance 所在的主机配置了 2 个 16 Core 的 CPU,可根据不同的场景,配置 4 ~ 12个不等的 Primary,这个数字的选择需要由富有经验的专业技术支持人员进行评估,每个 Instance 所在主机配置的 Primary 越多,响应并发的能力越弱,但单个任务的处理能力越强(这也不是绝对的,当 Primary 数量多到,即便运行单个任务时都会出现资源争抢,可能运行的效率就会下降)。实际上,每个计算主机的 Primary 个数,还与其他资源有关,如,磁盘性能,网络性能,内存容量。
3.冗余与故障切换
GP 提供了避免单点故障的部署选项。本节讲述 GP 的冗余组件。
Instance 镜像
Master 镜像
网络层冗余
(1)Instance 镜像
在部署 GP 系统时,可以选择配置 Mirror,如果初始化时没有配置 Mirror,后期也可以再次添加 Mirror,当然,如果要删除已有的 Mirror 也是可以的,不过需要手动操作,因为 GP 并未提供删除 Mirror 的标准命令、操作。
Mirror 使得数据库查询在 Primary 不可用时可以自动切换到 Mirror 上。为了配置 Mirror,GP 系统需要有足够多的主机,从而可以确保作为冗余角色的 Mirror总是位于与 Primary 不同的 Host 主机上,否则,一旦主机发生宕机故障,位于同一主机上互为配对关系的 Primary 和 Mirror 将同时不可用,数据库也将处于不可用状态,这样的话,就失去了 Mirror 的意义。
(2)Instance 故障切换与恢复
在 GP 系统启用 Mirror 的情况下,当 Primary 不可访问时,Master 会自动将任务切换到对应的 Mirror 上,此时,Mirror 取代 Primary 的作用继续提供服务。只要剩余的可用 Instance 能够保证数据的完整性,在 Instance 或者 Host 主机宕机时,GP 系统仍可继续保持服务可用的状态。
每当 Master 无法连接到 Primary 时,该 Primary 在 GP 的系统表中将被标记为失败状态, Master 会激活/唤醒对应的 Mirror 取代原有的 Primary。在采取相应的措施将失败的 Primary 恢复到健康状态之前,该 Primary 一直保持失败状态。失败的 Primary 可以在系统处于运行状态下被恢复回来。恢复进程仅仅复制失败期间发生变化的增量差异,当然,如果失败时间太久或者因失败的 Instance 文件有损毁,将需要全量恢复或者需要选择全量恢复。在 6 之前的版本, GP 的 Primary 和 Mirror之间采用的是 filerep 的方式进行 block 级别的变化同步的机制,从 6 版本开始,使用 WAL 复制,这将可以从根本上解决以往的 block 损毁被复制到 Mirror 上的问题,也不再需要 persistent 系统表了(这个的确是一个让人很头疼的设计)。在未启用 Mirror 的情况下,任何的 Primary 失败都会导致 GP 数据库自动停止服务。必须恢复所有导致 Primary 失败的故障,才能重新启动 GP 数据库集群。
登录模板数据库temporacle
select * from qb_database;
update qb_database set datallowconn = TRUE where datname = 'temporacle';
