postgresql 学习笔记_pgsql 创建脚本ddl 时的使用什么修饰

• SQL 入门语言

3.1 SQL（Structured Query Lanuage）

特点：它是结构化查询语句，重要的关系型数据库操作语言。PgSQql 的基础语法也同样适用于其他的关系型数据库如：mysql、oracle等

3.1.1 语法分类

Sql命令一般分为DQL、DML、DDL、DCL几类；其中，

DQL：数据查询语句,一般是select开始的

DML（Data Manipulation Language）:数据操纵语句。如：insert、delete、update

DDL（Data Definition Language）:数据定义语句。如新建表、库、删除、修改以及索引的语句

DCL：数据控制语句。如：commit、 rollback

3.1.2语法结构

每次执行的语句可以由多条Sql组成，用 ；分隔即可。Sql是由一系列记号组成，这些记号可以是关键字、标识符、双引号包围的标识符、常量、单引号包围的文本常量和特殊字符等。

可以有注释，相当于空白。

查询基本语法：

Select +要查询的列+ from+表名+ where 查询条件

3.2 DDL 语句

DDL是新建表、修改、删除表的语句

 

3.3.1 新建表

表是关系型数据库最基本的对象。类似于二维表格

--   数据库的增删改

create database YSC_test;

drop database  YSC_test;

-- show DATABASES;

-- use ysc_test;

-- SHOW TABLES;

 

-- 新增、删除、修改

 

create table student(

name varchar(10),

age int,

brithday date,

class varchar(10)

)

 

insert into student(name,age,brithday,class)  values('ysc','18','2019-3-16','一班')

 

alter table student add id int

 

alter table student drop id

 

alter table student change brithday test_date date

 

select * from student

插入时不添加列自动置为null.

3.3.2 排序

Order by 写在where条件后，asc为升序，desc 为降序。

例如：select * from student  where  id>0 order by id asc

3.3.3 分组查询

Group by  需要先聚合

第五章

-- pg  自增主键序列 方法      id是我设置的主键；  start with 设置初始值   increment by 设置每次增加值;

CREATE SEQUENCE t_app_bdz_aa_id_seq

START WITH 3141

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

alter table t_app_bdz_aa alter column id set default nextval('t_app_bdz_aa_id_seq');

 

 

-- 处理字段序列

delete from t_app_bdz_aa where length(id)>6

select max(id) from t_app_bdz_aa

insert into t_app_bdz_aa(bdzname,unit_id) values ('aaa','bbb')

 

-- insert into ... select * from 先创建表结构 再备份表内容

insert into t_app_aa select id,bdzname from  t_app_bdz

 

-- union /union all  去重、不去重

-- truncate  table  清空表 和不带where的delete功能相同，原理不同；truncate 是DDL 数据定义语言，相当于抛弃已有数据，重新建表；delete 是追条删除，数据量大时此方法会很慢

-- 默认psql  会有template0 和template1 两个数据库模板，默认是template1 可以定制函数，0是最简单模板

 

 

-- ------------数据类型

-- 1.与其他数据库对比

略

-- 2.布尔类型：要么真true,要么假false,要么不知道unknown,即null 未知  很宽泛 tRue,yes,no,y,n,1,0

可以进行位运算：and or not ,比较运算符 is

 

 

-- 3.数据类型

3.1常用整型类型

smallint==int2  磁盘紧张时用/  int(integer)==int4 常用/  bigint ==int8  integer不够时用

3.2精确的小数类型

numeric、numeric(m)、 numeric(m,n) 与 decimal 等效；m为精度，n为标度

   小数超过标度会四舍五入，既没精度又没标度会原样存储，只声明精度超了会报错

3.3浮点型

real、decimal precision 变精度数字类型

3.4序列类型

序列类型，serial与bigserial 是mysql 中的自增序列；pgsql 中等价  sequence序列 和 oracle类似

eg: create table t_app_test(id  serial)  mysql

等价于

create sequence t_app_test_id_seq;

create table t_app_test(

id integer not null default nextval('t_app_test_id_seq')

);

alter sequence t_app_test_id_seq owned by t_app_test.id;

insert into t_app_test(id,name) values(1,'aaa')

3.5货币类型（money type）

可保存固定小数货币数目，与浮点型不同，它可保证精度，占8位，可以输出与lc_monetary设置相关的货币显示; en_US.UTF-8;zh_CN.UTF-8

show lc_monetary;

select '33.44'::money;

set lc_monetary = 'zh_CN.UTF-8';

select '33.44'::money;

3.6 支持各种数学操作和函数

 

操作符：+-*/%（求余、模）  ^幂  |/(平方根)  ||/(立方根)  !(阶乘 后缀)  !!(阶乘 前缀)  @(绝对值)  &(二进制and) |(二进制 or) '#'(二进制 xor) ~(二进制 not) <<(二进制左移)  >>(二进制右移)

eg：

select |/('9'::numeric);

select 5!;

select !!6;

select  @(-66);

select  31&15;

select  31#12;

select  ~12;

select  2<<2;

函数:

abs()绝对值  cbrt()立方根  trunc() 截断s位小数 。。。

select trunc(cast('33.454' as numeric),2);

 

-- 4.字符类型：varchar(n)、char(n)、text

 

4.1 简介

character varying(n)/varchar(n) 变长，最大1G：实际 4+实际字符串长,mysql 64KB

character(n) /char(n)  定长，最大1G,不足补空白

text 变长，无限制,与mysql中的longtext类型

 

4.2 字符串的操作符和函数

-- string || string  字符串的连接

select 'aa' || '4a';

-- bit_length(s) 字符串二进制位的个数

select bit_length('abvd');

-- char_length(s) 字符串中字符的个数

select char_length('12');

-- convert() 改编码;  utf8 编码  的 'PostgreSql'

convert('PostgreSql' using iso_8859_1_utf8)

-- lower() 字母转小写

select lower('ASNNaa1');

-- uppwer()转大写

select upper('afad 23 A');

-- octet_length() 返字节数

select octet_length('sdf');

-- overlay() 替换某个字符串

select overlay('faaaarr' placing 'xx' from 2 for 3);

-- position() 指定字符串位置

select  position('b' in 'abcd');

-- substring() 截取子字符串  等价于 substr()

select substring('abcdef' from 2 for 3);

select substr('abcdef',2,3);

-- trim() 去除开头结尾空字符串或者某个字符串

select trim(both 'x' from 'xxaxbxcdx');

-- btrim()  开头和结尾删除某个指定字符  会将xx 也去除

select btrim('xxaxbxcdx','xxbx');

select btrim('  xx  axbx  cdx ',' ');

-- rtrim()删除结尾的指定字符

select rtrim('xxaxbxcdx','axx');

-- split_part()分隔字符取指定子字符串

select split_part('abadeass66arra','a',5);

-- initcap() 字符串单词首字母大写，不同单词空格隔开、

select initcap('abc def');

-- ascii() 给首字符ASCII的值、chr() 给首字符、

select ascii('c');

select chr(66);

-- length() 字符数量

select length('abc');

-- regexp_replace() 正则替换某个字符串 匹配第一次、

select regexp_replace('a1233dd44aa','.[1-9]+','ww');

-- repeat() 将某个字符串重复、replace(s,s1,s2) 把s中含有s1的字符串换成s2、strpos() 找指定字符的位置

select repeat('ssaa',2);

select replace('dsffdgkfff,dfpff','ff','**');

select strpos('aa4411','44');

-- 5.二进制数据类型

postgresql  中只有一种二进制类型bytea；mysql 和 oracle 中有blob类型。

-- 6.位串类型

保存为1和0的字符串类型：bit(n) /varying bit(n)   

操作符、:||   &  |   #  ~   <<   >>

-- 7.日期时间  除date外全精确到毫秒

timestamp([p]) 8字节 带时区、不带时区

interval([p])  12字节  时间间隔

date  4字节  一天

time([p])   8/12字节 不带时区、带时区   用作一天之内

 

日期输入：最好采用"-"分隔 如：“年-月-日”

select time '04:05:06 pst';

select time with time zone'04:05:06 pst';

7.1 对时间的操作

加减乘除 +-*/

select date '2019-06-10'+ integer '2';

7.2 函数

-- age(timestamp1,timestamp2); 时间差  前减后存在负值

-- age(timestamp); 与当前时间差

select  age(timestamp '2019-06-10','2019-06-17');

select  age(timestamp '2019-06-17');

 

select current_date; 当前日期

select current_time; 当前时间

select localtime; 当日时间

-- 当前事务开始的时间戳 带时区   三个等效  因为都是事务开始的时间

select now();  

select transaction_timestamp();             

select current_timestamp(2);

 

date_part()  获取子区域

(field from source) 获取子区域

 

select date_part('year',timestamp '2012-01-23');

select extract(century from timestamp '2012-01-23');

-- 8.枚举类型

8.1枚举类型

是一个包括一系列静态集合的数据类型  相当于java 中的enum

要使用枚举类型，先使用create type 创建一个枚举类型

 

create type week as enum ('星期一','星期二','星期三');

create  table t_app_aduty(person text,weekday week);

insert into t_app_aduty values('张三','星期二');

insert into t_app_aduty values('张s','星期11');

 

注意：大小写敏感,包括空格

'ASdd ' 不等于 'ASdd'

8.2 枚举函数

enum_first() 返回第一个

enum_last() 返回最后一个

enum_range(a1,a2) 返回范围，a1和a2 都不给返全部，a1为null,从第一个开始返；如果a2为null  返到结束

select enum_first(null::week);

select enum_range(null::week);

-- 9.几何类型

9.1postgresql 支持一些二维几何数据。

类型：point、line、lseg（线段）、box(矩形)、path(闭合、开放路径)、polygon(多边形与闭合路径类型)、circle 圆

select '108.22,34.66'::point;    === '(108.22,34.66)'

select '108.22,34.66,109.22,35.66'::lseg  ==='[(108.22,34.66),(109.22,35.66)]'

select '(108.22,34.66),(109.22,35.66)'::box

select '(108.22,34.66),(109.22,35.66)'::path

select '(108.22,34.66),(109.22,35.66)'::polygon  不能用【】

select '((108.22,34.66),8)'::circle

9.2 操作符

+- 平移 */ 缩放旋转 。。。

9.3几何函数 和 类型转换

area面积、center中心、length长度。。。

 

-- 10.网络地址类型

postgresql 提供专门的IPv4、IPv6、Mac 存储及检测

类型：

cidr  7/19字符  IPv4或IPv6的网络地址

inet  7/19字符  IPv4或IPv6的网络地址或主机地址

macaddr 6字符   以太网Mac

各种操作符和函数。。。

 

-- 11.数组类型

11.1支持一维或多维数组，数组类型可以是各种内建类型、用户自定义类型、枚举、及组合类型

语法：create table test(id varchar(255),col1 int [],col2 int [10] )

方括号的个数，数字不起限制作用，只是说明你是个数组而已；数组维数是根据数据而定的

 

text 类型数组  单引号可以再拼一个

insert into test values('abc','{1,2,3}','{4,5}');

insert into test1 values('abc','{1,2,3}','{4,5}','{"hello","who''s you"}');

11.2 不能修改多维数组中的某一维数组

11.3 操作符

  做集合比较是 不管维度都是视为集合中的一个元素

普通比较

= <>  > <  >= <=

 

集合比较

@>包含  <@ 被包含  &&重叠，是否有相同元素

 

连接 ||  可以是不同维度、同维度，元素与一维数组的连接

11.4 数组函数

-- array_append() 向数组末尾添加元素，返回新数组

select  array_append(array[1,2,3],4);    #{1,2,3,4}

-- array_cat() 连接两个数组，返回新数组

select array_cat(array[1,2],array[3,4]); #{1,2,3,4}

select array_cat(array[1,2],array[[3,4]]);  #{{1,2},{3,4}}

-- array_ndims() 返回数组维度，int

select array_ndims(array[[[1,2]]]);        #3

-- array_dims() 返回数据维度结构文本

select array_dims(array[[[1,2]]]); #[1:1][1:1][1:2]

-- array_fill() 按格式指定填充元素

-- array_length() 返回数组长度，由第二个参数决定

select array_length(array[[1,2],[3,4],[5,6],[7,8]],1);#4

-- array_upper()    返回数组上标

-- array_lower() 返回数组下标

-- array_prepend() 数组开头添加一个元素

select array_prepend(2,array[3,4]); #{2,3,4}

-- array_remove() 移去一维数组某一指定元素

select array_remove(array[1,2,3],2); #{1,3}

-- array_replace() 把数组中指定值替换

select array_replace(array[[1,2],[2,3],[3,4]],2,6);#{{1,6},{6,3},{3,4}}

-- array_to_string() 数组转string

select array_to_string(array['a','b','c'],',');#a,b,c

select array_to_string(array['a','b','c'],'1');#a1b1c

-- string_to_array() string转数组

select string_to_array('1,a,2,b',','); #{1,a,2,b}

select string_to_array('13a323b','3'); #{1,a,2,b}

-- unnest()   数组转多行

select unnest(array['a','b','c']);

a

b

c

-- array_agg() 聚合函数,把表达式变成一个数组 一般配合 array_to_string() 函数使用  string_agg，array_agg 这两个函数的功能大同小异，只不过合并数据的类型不同。

select deptno, array_to_string(array_agg(ename),',') from jinbo.employee group by deptno;

select array_agg(bdzname) from t_app_bdz  where id::integer <5   #{河寨变,南郊变,沙井村变,瓦胡同变}

select array_to_string(array_agg(bdzname),',') from t_app_bdz  where id::integer <5 #河寨变,南郊变,沙井村变,瓦胡同变

select unnest(array_agg(bdzname)) from t_app_bdz  where id::integer <5

河寨变

南郊变

沙井村变

瓦胡同变

select array_agg(bdzname) from t_app_bdz  where id::integer <5000  group by  unit_id

{临潼姜寨路供电营业厅,西一路供电营业厅}

{330代王变,330代王变111234}

{测试变电站,高新变}

 

-- 12.复合类型

略

-- 13.xml类型

13.1用于存储xml 类型数据，并会检测数据是否标准；存储的xml 有两种形式：一种是由xml定义标准的document,另一种是由xml定义标准的content 片段

select xml '<span>aaa</span><a>66</a>';

select xml '<span>aaa<span>'; 会保错 invalid XML content

等同于下面方法

字符串转xml 的唯一方式

xmlparse(document|content value)

select xmlparse(content '<span>aaa</span><a>66</a>')

13.2函数

定义把表导成xml

-- 14.json类型

14.1json 与jsonb  使用基本相同；

区别：

json 将字符串原样存数据库中，存前解析，使用是需要重新解析；而jsonb 是以二进制的形式存储，存前解析，之后不用，性能更高

jsonb 建立索引，清除多余空格，多余key和各个key的顺序

select json '"aa"',json '"true"'   单个  

select json '{"name":"ysc","cc":"ss","age":"22","cc":"ss"}'  多个

select jsonb '{"name":"ysc","age":"22","cc":"ss"}'

select '["name","age","22","cc","ss",null]' ::jsonb

14.2 json 和 jsonb 操作符

->   操作int 取json 数组内容

->    操作text  通过key取值，返值看类型

->>   操作int   取json 数组内容,返一个text结果

->>    操作text  通过key取值，返text

#> 操作text[]  通过json路径取对象

。。。

14.2 json 和 jsonb 函数

to_json  把任意类型转json

array_to_json 把数组转json

row_to_json() 把row类型转json

....

14.3 jsonb 索引  json 不能建立索引

jsonb 可以直接建立索引，推荐GIN，  BTree效率不高

其中BTree不关心jsonb内部数据结构，比较原则是根据jsonb的大小，Object>Array>BOOLEAN>Number>String>null,后在内部顺序比较

GIN 建立索引方法2种：jsonb_ops、jsonb_path_ops;后者效率更高

-- 15.范围类型

range类，范围快速搜索  比如：ip 地区范围查询，建立空间索引使用操作符@> 效率更高

操作符函数 略

-- 16.对象标识符

-- 17.伪类型

不能作为字段类型，但可声明函数的参数或结果类型。类型有：any、void、record、trigger。。。

 

-- 18.其他类型

如：UUID、pg_lsn(wal 日志系统表中的一种类型)

-- 类型名 ''  进行格式输出  或者 cast('5' as int) 或者 '' :: 类型名

select cast('56' as int),int '777','2012-6-8':: date

 

 

 

Alter database test1 rename to mydb;

 

 

 

 

 

 

 

 

 

 

• 逻辑结构管理

6.1 介绍

Pgsql 中数据的组织结构可以分为以下三层：数据库、表及索引、数据行。

6.2数据库基本操作

新建、删除、修改数据库等

Create database name;  也可以指定各种参数，知道即可，后查

Alter database testbase1 rename to mydb; 可修改数据库的各种参数信息

Drop database mydb;直接删除一个，如果有人连接数据库，不能删除

注意：不能再事务模块中删除数据库，但是可以修改数据库

6.3模式 schema

相当于一个命名空间或者目录

Create shema test;

使用模式的理由：允许多个用户在使用同一个数据库时彼此不干扰；把数据库对象放在不同模式下，组织成逻辑组，便于管理；第三方应用可以放在不用模式下，这样不与其他对象名冲突；

例如postgis 扩展对象

6.3.1 使用可以创建查看修改删除模式

6.3.2 公共模式

默认是public

6.4 表

6.4.1 建表

Create table table_name(

Col_name  data_type  primary key,

Col_name1  data_type,

...

)

如果表主键只是一个字段组成的，直接在字段类型后加“primary key”;

唯一键也是约束的一种；unique(字段)；

check是约束的一种用于东某些字段值必须满足的某种要求；

 

也可以用其他表作为模板创建新表，使用including all 把约束或其他信息复制过去

6.4.2 表的存储属性

略

6.4.3 临时表  temp

Pgsql 支持两种临时表：一种是会话级的临时表 ，数据一直保存在真个会的生命周期中；一种是事务级的临时表，只存在这个事务的生命周期；

语法： temporary 缩写temp==global==local

Create temporary table tmp1(id varchar primary key,note text); 等价写法：

Create temp table tmp1(id varchar primary key,note text);

6.4.4 默认值 default，也可表达式

建表时可以给一个字段指定默认值。

Create table student(id int,name varchar(20),age int default 20);

Update student set age=default where id = 2

没有声明，默认是null

常用是timestamp 默认是now().

create table atest (id int primary key ,title text,col1 jsonb,otime timestamp default now());

insert into atest values(1,'test','"aa"');

//1 test "aa" 2019-06-11 14:53:11.636835

6.4.5 约束

目前有5类约束

检查约束、非空约束、唯一约束、主键、外键

 

检查约束：最常见。如设定一个年龄必须在0~150之间。

create table atest1(

id int primary key ,

age int constraint check_age  check(age>=0 and age <=150),

age1  int constraint check_age1  check(age1>=0 and age1 <=150),

otime timestamp default now(),

Check( age1>age)

);

insert into atest1(id,age) values(2,151);//报错

check可加多个字段，constraint 可打印出报错信息  这种为字段约束,

字段后和字段同级的为表约束；一般字段约束可写成表约束，反过来错误；

 

非空约束：

一个字段不能为null。===not null

create table atest2(

id int primary key ,

age int constraint check_age check(age>=0 and age <=150),

otime timestamp default now()，

obdzname varchar not null

);

 

 

 

唯一约束：unique

保证在一个字段或一组字段中其行数据是唯一的

create table atest2(

id int primary key ,

book text unique,

age int constraint check_age check(age>=0 and age <=150),

otime timestamp default now()，

obdzname varchar not null

);

 

 

外键约束：表之前关系的约束。Reference class(class_no)

 

6.4.6 修改表 结构

“alter table “

操作：增加字段、删除字段、增加约束、删除约束、修改默认值、修改字段数据类型、重命名字段、重命名表

 

增加字段;

alter table atest1 add column obj varchar(20);

alter table atest1 add column obj1 varchar(20) check(obj1 <>'');

alter table atest1 alter column obj set not null;//非空约束

删除字段

alter table atest1 drop column obj;

alter table atest1 drop column obj1 cascade; //删除外键约束

alter table atest1 alter column obj drop not null;//删除非空约束

-- 修改默认值

alter table atest1 alter column obj set default 22

-- 删除默认值

alter table atest1 alter column obj drop default

-- 修改字段类型

alter table atest1 alter column age type numeric

-- 重命名字段

alter table atest1 rename column obj to obj1

-- 修改表名

alter table atest1 rename to atestok

 

6.4.7 表继承及分区表

表继承是pgsql 中特有的。有父子表关系时，查父表会把子表也查出来，返之不行。

如果只查父表  查询表名前加“only”

select * from only persons;

 

Pgsql是通过表继承来实现分区表的。

 

6.5 触发器

触发器是一种由事件自动触发执行的特殊的存储过程，这些事件可以是对一个表进行insert、update、delete等操作。

触发器经常用于加强数据的完整性约束和业务规范上的约束等。

 

建触发器的步骤;

 

1. 为触发器建一个执行函数，此函数返回的类型是触发器类型；
2. 再创建相应的触发器。

 

例子;

 

-- 触发器   学生表 成绩表 需求:在删除学生表中的一条记录，相应删除成绩表中的相应记录

create table astudent(

student_no int primary key,

student_name varchar(40),

age int

);

create table ascore(

student_no int,

chinese_score int,

math_score int,

test_date date

);

-- 先建执行函数：

create or replace function student_delete_trigger()

returns trigger as $$

begin

delete from ascore where student_no = OLD.student_no;

return OLD;

end;

$$

language plpgsql;

-- 再建触发器

create trigger delete_student_trigger

afterdelete on astudent

for each row execute procedure student_delete_trigger();

 

-- 测试数据

insert into astudent values(1,'张三',11);

insert into astudent values(2,'张三2',12);

insert into astudent values(3,'张三3',13);

 

insert into ascore values(1,60,77, date '2019-5-4');

insert into ascore values(1,66,78, date '2019-5-6');

insert into ascore values(2,60,77, date '2019-5-4');

insert into ascore values(2,70,87, date '2019-5-6');

insert into ascore values(3,80,78, date '2019-5-4');

insert into ascore values(3,90,97, date '2019-5-6');

 

-- 结果   删除记录学生号1

delete from astudent where student_no =1;

2 60 77 2019-05-04

2 70 87 2019-05-06

3 80 78 2019-05-04

3 90 97 2019-05-06

-- 删除触发器

drop trigger delete_student_trigger on astudent;

 

6.5.2 语句级触发器与行级触发器

语句级触发器：执行的sql,只执行一次

行级触发器：每行执行一次

一个修改0行的操作仍然会触发语句级触发器

例子：

--语句级触发器

create table log_student(

update_time  timestamp,

db_user varchar(40),

opr_type varchar(40)

);

create function log_student_trigger()

returns trigger as

$$

begin

insert into log_student values(now(),user,TG_OP);

return null;

end;

$$

language plpgsql;

 

"tg_op" 是触发器的特殊变量，代表DML操作类型。

 

create trigger log_student_trigger

 after insert or delete or update on astudent

 for statement execute procedure log_student_trigger();

 

 insert into astudent values(1,'zhangsan',11);

 

 2019-06-11 17:27:31.902492 postgres INSERT

 

 -----------------------------行级触发器

localhost_postgreSql delete from log_student;

 delete from astudent;

 

 1.建行级触发器

 create trigger log_student_trigger2

 after insert or delete or update on astudent

 for statement execute procedure log_student_trigger();

 2.建触发器 同上log_student_trigger

  insert into astudent values(1,'zhangsan',11); insert into astudent values(2,'zhangsan2',12);

update astudent set age=14

 

6.5.3 before 与 after 触发器

语句级别的before是在做任何事之前触发，after是语句结束才触发。

行级的before是之前触发，after是之后触发，但是它是在任何语句级别的after触发器被触发前触发的

 

6.6.4删除触发器

drop trigger delete_student_trigger on astudent;

 

6.5.5 触发器的行为

触发器函数有返回值。语句触发器总是返null  在触发器函数中写“return null”,不写会报错

 

6.5.6 触发器函数中的特殊变量

new: insert/update  行级 新数据行

old: update/delete  行级  旧数据行

tg_name: 实际触发器名

tg_when: 用于指定时before 还是after

tg_level:指定语句还行级

tg_op:insert/update/delete/truncate DML语句的类型

tg_relid:触发器所在表的名称

tg_table_name:触发器所在表名称

tg_table_schema:触发器所在表模式

tg_nargs:

6.6 事件触发器

ddl_command_start:一个ddl 开始执行前触发

ddl_command_end:一个ddl 开始执行完成触发

Sql_drop:删除一个数据库对象被触发。

 

6.6.1 创建、修改事件触发器

。。。

6.7 表空间

6.8 视图

就是查询语句定义的虚拟表。

6.8.1使用原因：

1. 可使复杂的查询易于理解。
2. 安全。可以规避一些敏感字段如密码
3. 可以把一些函数返回的结果映射成视图

 

 

 

eg:

create table auser(

id int,

username varchar(50),

useremail varchar(50)

);

create view view_auser(oid,oname,oemail) as  select id,username,useremail from auser;

 

insert into auser values(1,'abc','adfg');

 

也可添加临时视图 temp/temporary  会话结束就消失

Create temp view view_auser1(oid,oname,oemail) as  select id,username,useremail from auser;

6.8.2 更新视图

 

测试过有点问题：

create table auser1(

id int,

username varchar(50),

useremail varchar(50)

);

create temp view view_auser1(oid,oname,oemail) as  select id,username,useremail from auser;

 

create view view_auser1 as select id,username,useremail from auser1;

insert into auser1 values(2,'abcc','adfga');

-- 更新  也可以自定义规则

update view_auser set oname = 'aaa' where oid = 1;

 

create rule view_auser1_upd2 as

on  update to view_auser1 do instead of update auser1 set username =NEW.Oname;

update view_auser1 set Oname = '666' where Oid = 1;

 

也可以写触发器更新视图instead of 不能用before和after，需要时再了解。

新增删除都可以实现。

6.9 索引

6.9.1 索引简介

索引是数据库中一种快速查询数据的方法。索引记录表中列的值与其物理位置之间的对应关系，好比一本书正文的目录，通过目录的页码能快速定位到需要的内容。

好处：快速查表的记录或排序

坏处：增加储存空间；插入和修改花费较多时间，索引也要同步更新。

6.9.2 索引的分类

B-tree:最常用，适合等值查询和范围查询。

Hash: 简单的等值查询。

GiST:一种架构，用于空间几何

SP-Gist:  空间分区GIST索引

GIN：反转索引，处理多个键的值，如数组等。

6.9.3 创建索引

创建索引会读取所有数据时间取决于表大小，查询正常但是增删改需要索引完成后才能进行，pqsql 提供了一种并发创建索引的方法。

 

-- -----------创建索引

create table indextest(

id int primary key,

name varchar(20),

phone varchar(32)[],

address text

);

 

-- name 快速查 BTree索引   设置索引字段排序以及空值在非空值前后/存储参数fillfactor

create index idx_indextest_name on indextest(name) with(fillfactor=50);

 

create index idx_indextest_id on indextest(id desc);

select * from indextest where id >0;

select * from indextest where name like '%a%';

-- phone 快速查 数组索引btree不适合  用GIN索引

create index idx_indextest_phone on indextest using gin(phone)

 

select * from indextest where phone @> array['111222'::varchar(32)];

 

6.9.4 并发创建索引

通过create index + concurrently(并发创建索引) 实现，增删改阻塞。因为要扫描两遍时间更长一点。

增删改不等待

-- ----------并发索引

-- 新建表 插入测试数据  打开两个窗口一个建索引，一个删除数据

create table atext11(id int primary key,note text);

insert into atext11 select generate_series(1,5000000),generate_series(1,5000000);

 

create index idx_atext11_note on atext11(note);//60.38s

delete from atext11 where id=2;//56.38s

 

create index concurrently idx_atext12_note on atext11(note);//66.18s

delete from atext11 where id=5;//0.001s

 

6.9.5 修改索引

 

-- 修改索引  

-- 改名字

alter index  idx_atext12_note rename to idx_atext12_note12;

-- 索引转移表空间

alter index  idx_atext12_note12  set tablespace tbs_data01;

-- 把索引填充因子filltactor设置为50,默认值reset

alter index  idx_atext12_note12 set/reset (fillfactor=50);

 

6.9.6 删除索引

-- 删除索引 if 判断是否存在，不存在也不会报错 cascade 删除有约束的索引

drop index if exists idx_atext12_note12 cascade;  

 

6.10 用户权限管理

略。后学

 

6.11 事务、并发、锁

6.11.1 ACID

事务：一组相关操作要么全成功，要么全失败，即原子性，不开再分。一组操作指（多个增删改）

原子性（atomicity）:要么全成功，要么全失败。

一致性（consistency）:事务在完成前，必须使所有数据保存一致。

隔离性（isolation）:事务查看数据时数据所处的状态。要么是另一并发事务修改它之前的状态，要么是修改之后的状态，不会查看中间状态的数据。

持久性(durability):事务完成后对系统影响是永久的。今后出现致命系统故障如重启，断电，数据将一直保持。

 

6.11.2 事务的使用

Pgsql 默认配置下，自动提交atuocommit是开启的，可设置 set atuocommit =off 关闭。

使用begin 相当于关闭的自动提交

6.11.3 savepoint 保存点

Pgsql 支持保存点功能：在一个大的事务中可以把一个大的操作过程分成几个部分，第一部分完成，建立一个保存点，执行失败回滚到这个保存点，而不至于回滚全部事务。

Eg:

-- 事务--------------------------------------------------

begin;

insert into atest values(7);

insert into atest values(8);

savepoint mypoint01;

insert into atest2 values(9);

insert into atest2 values(10);

select * from atest2;

rollback to savepoint mypoint01;

select * from atest;

select * from atest2;

commit;

结果集：

9 2019-06-12 15:01:07.232699

10 2019-06-12 15:01:07.232699

 

 

1 test "aa" 2019-06-11 14:53:11.636835

7 2019-06-12 15:01:07.232699

8 2019-06-12 15:01:07.232699

 

null  null  null

 

6.11.4 事务隔离级别

四种状态：

read uncommitted: 读未提交

read committed: 读已提交

repeatable read: 重复读

serializable:串行化

 

对于并发事务，不希望发生不一致问题，这类情况级别从高到低排列：

脏读：一个事务读取另一未提交写入数据。

不可重复读：一个事务重新读取前面读取过的数据

幻读：一个事务开始后又执行了他最近提交的事务而发生改变导致逻辑错误。

6.11.5 两阶段提交（分布式事务）略

6.11.6 锁机制

Pgsql 数据库中有两类锁：表级锁和行级锁。当增删改查表中数据时，首先要获取表上的锁，其次是获取行上的锁。

6.11.7死锁及防范

略

 

1. PostgreSQL的核心架构
   1. 应用程序的访问接口

连接到数据库的驱动分为两类：

1. 使用纯语言实现的PostgreSQL驱动
2. 通过包装PostgreSQL的C语言接口库libpg实现的接口库
   1. 进程及内存结构
      1. 进程介绍

postgres 数据库启动时，会先启动一个叫postmaster的主进程，还会fork出一些辅助子进程，这些辅助子进程各自辅助一部分功能。如下：

1. SysLogger(系统日志)进程
2. BgWriter(后台写)进程
3. WalWriter(预写式日志)进程
4. PgArch(归档)进程
5. AutoVAcuum(系统自动清理)进程
6. PgStat(统计收集)进程
   1. 1. 主进程Postmaster

主进程Postmaster是整个数据库实例的总控进程，负责启动关闭该数据库的实例。postmaster命令是一个指向postgres的链接。主进程Postmaster实际上是第一个postgres进程，此主进程还会fork出一些与数据库实例相关的辅助子进程，并管理它们。

当用户与PostgreSQL数据库建立连接时，实际上是先与Postmaster进程建立连接，Postmaster验证客户端身份通过后，fork出一个子进程来为这个连接服务。fork出的子进程称为服务进程。所以PostgreSQL是进程架构模型。

当某个服务进程出现错误时，Postmaster主进程会自动完成系统的恢复。恢复过程中停掉所有的服务进程，然后进行数据库数据的一致性恢复，等恢复完成后，数据库又可以接受新的连接。

查客户端连接的主进程postmaster

select pid, usename, client_addr, client_port from pg_stat_activity;

1. 1. 1. SysLogger(系统日志)进程

配置文件postgresql.conf中有很多与日志相关的参数，logging_collect:on时，主进程才会启动SysLogger辅助进程。

SysLogger辅助进程通过从Postmaster进程，所有的服务进程及其他辅助进程收集所有的stderr输出，并将这些输出写入到日志文件中。

1. 1. 1. BgWriter(后台写)进程

BgWriter辅助进程是把共享内存中的脏页写到磁盘上的进程。当往数据库中插入或更新数据时，并不会马上把数据持久化到数据文件中。这主要是为了提高插入、更新、删除数据的性能。

1. 1. 1. WalWriter(预写式日志写)进程

在修改数据之前，必须要把这些修改记录到磁盘中，后面更新实际数据时，就不需要实时地把数据持久化到文件中了，即使机器突然宕机或数据库异常退出，导致一部分内存中的脏数据没有及时地刷新到文件中，在数据库重启后，通过读取WAL日志，并把最后一部分的WAL日志重新执行一遍，就可以恢复到宕机时的状态。

1. 1. 1. PgArch(归档)进程

WAL日志会被循环使用，即较早时间的WAL日志会被覆盖。PgArch归档进程会在覆盖前把WAL日志备份出来。PITR(Poing-In-Time-Recorey)技术：在对数据库进行一次全量的备份后，该技术将备份时间点之后的WAL日志通过归档进行备份，使用数据库的全量备份再加上后面产生的WAL日志，即可把数据库向前推到全量备份后的任意一个时间点了。

1. 1. 1. AutoVacuum(自动清理)进程

对表的DELETE与UPDATE操作后，旧数据不会立即删除与更新，而是新生成一行数据。此时，旧数据只是被标识为删除状态，只有在没有并发的其他事物读到这些旧数据时，它们才会被清除掉。

1. 1. 1. PgStat(统计数据收集)进程

PgStats辅助进程主要做数据的统计收集工作，收集的信息主要用于查询优化时的代价估算，这些信息包括在一个表和索引上进行了多少次插入、更新、删除操作，磁盘块读写的次数，以及行的读次数。系统表pg_statistic中存储了PgStat收集的各类统计信息。

1. 1. 1. 共享内存

PostgreSQL启动后，会生成一块共享内存，主要用做数据块的缓冲区，以便提高读写性能。共享内存包含WAL日志缓冲区和CLOG(Commit log)缓冲区，一些全局信息(进程信息，锁的信息，全局统计信息)等等。

1. 1. 1. 本地内存

主要存储以下几类：

1. 临时缓冲区：用于访问临时表的本地缓冲区
2. work_mem:内部排序操作和hash表在使用临时磁盘文件之前使用的内存缓冲区
3. maintenance_work_mem：在维护性操作(如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY等)中使用的内存缓冲区。
   1. 目录结构

PostgreSQL软件通常安装至/usr/local/目录下。

1. postgresql.conf：数据库实例的主配置文件，基本上所有的配置参数都在此文件中
2. pg_hba.conf：认证配置文件，配置了允许哪些IP的主机访问数据库，认证的方法是什么等信息
3. pg_ident.conf：”ident”认证方式的用户映射文件
4. base：默认表空间目录
5. global：一些共享系统表的目录
6. pg_clog：commit log目录
7. pg_log：系统日志目录
8. pg_stat_tmp：统计信息的存储目录
9. pg_talsp：存储了指向各个用户自建表空间实际目录的链接文件
10. pg_twophase：使用两阶段提交时分布式事物的存储目录
11. pg_xlog：WAL日志的目录

1. 服务管理
   1. 服务的启停与创建
      1. 启停方法

启动一个数据库实例有两种方法：

1. 直接运行postgres进程启动
2. 使用pg_ctl命令启动

postgres –D /home/osdba/pgdata &

pg_ctl –D /home/osdba/pgdata start

停止数据库方法：

1. 直接向运行postgres主进程发送signal信号，停止数据库
2. 使用pg_ctl命令停止数据库

停止数据库的模式有三种：

1. Smart Shutdown：智能关机模式。在接受关机请求后，服务器将不允许有新连接，且会等已有的连接全部结束后才关闭数据库。
2. Fast Shutdown：快速关闭模式。不再允许有新的连接，向所有活跃的服务进程发送sigterm信号，让他们立即退出，然后等待所有子进程退出并关闭数据库。如果服务器处于在线备份状态，将直接终止备份，这将导致此次备份失败。这种关机模式是经常使用的。
3. Immediate Shutdown：立即关闭模式。主进程postgres向所有子进程发送sigquit信号，并且立即退出，所有的子进程也会立即退出。这种模式并不会妥善的关闭数据库系统，下次启动时数据库会重放WAL日志进行恢复，因此推荐只在紧急的时候使用。

直接向数据库主进程发送的signal信号有三种：

1. sigterm：发送此信号为Smart Shutdown关机模式
2. sigint：发送此信号为Fast Shutdown关机模式
3. sigquit：发送此信号为Immediate Shutdown关机模式

使用pg_ctl命令时，不同的命令行参数表示不同的关机模式：

pg_ctl stop –D DATADIR –m smart
pg_ctl stop –D DATADIR –m fast

pg_ctl stop –D DATADIR –m immediate

1. 1. 1. pg_ctl

pg_ctl是一个实用工具，有以下功能：

1. 初始化PostgreSQL数据库实例
2. 启动、终止或重启PostgreSQL数据库服务
3. 查看PostgreSQL数据库服务的状态
4. 让数据库实例重新读取配置文件
5. 运行给一个指定的进程发送信号
6. 在Window平台下运行为数据库实例注册一个系统服务或取消这个系统服务

初始化PostgreSQL数据库实例命令如下：

pg_ctl init [db] [-s] [-D datadir] [-o options]

参数说明如下：

1. -s：只打印错误和警告信息，不打印提示信息
2. -D datadir：指定数据库实例的数据目录
3. -o options：为直接传递给initdb命令的参数

启动PostgreSQL数据库命令如下：

pg_ctl start [-w] [-t seconds] [-s] [-D datadir] [-l filename] [-o options] [-p path] [-c]

参数说明如下：

1. start：启动数据库实例
2. -w：等待启动完成
3. -t：等待启动完成的等待秒数，默认60秒
4. -s：只打印错误和警告信息，不打印提示信息
5. -D datadir：指定数据库实例的数据目录
6. -l：把服务器日志输出附件在“filename”文件上，如果该文件不存在则创建它
7. -o options：声明要直接传递给postgres的选项
8. -p path：指定postgres可执行文件的位置
9. -c：提高服务器的软限制（ulimit -c），尝试运行数据库实例在有异常时产生一个coredump执行文件找不到的错误

停止PostgreSQL数据库命令：

pg_ctl stop [-W] [-t seconds] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate]]

参数说明如下：

1. -W：不等待数据库停下来，命令就返回。
2. -m：指定停止的模式。

重启PostgreSQL数据库命令：

pg_ctl restart [-w] [-t seconds] [-s] [-D datadir] [-c] [-m s[mart] | f[ast] | i[mmediate]] [-o options]

让数据库实例重新读取配置文件的命令如下：

pg_ctl reload [-s] [-D datadir]

查询数据库实例状态的命令如下：

pg_ctl status [-D datadir]

给指定的进程发送信号：

pg_ctl kill [signal_name] [process_id]

windows下注册于取消服务如下：

pg_ctl register [-N servicename] [-U username] [-P password] [-D datadir] [-w] [-t seconds] [-o options]

pg_ctl unregister [-N servicename]

1. 1. 1. postgres及单用户模式

单用户模式主要用于修复数据库的以下几种场景：

1. 当多用户模式不接收所有命令时，可以使用单用户连接到数据库
2. initdb的阶段
3. 修复系统表
   1. 服务配置介绍
      1. 配置参数

所有配置项的参数名大小写不敏感，参数值有以下五种类型：

1. 布尔：on、off、true、false、yes、no、1、0
2. 整数：数值可以指定单位
3. 浮点数
4. 字符串
5. 枚举

参数分类：

1. internal：只读参数。
2. postmaster：在postgresql.conf文件中改变这些参数值需要重启PostgreSQL实例。
3. sighup：在postgresql.conf文件中改变这些参数值不需要重启数据库，只需要向postmaster进程发送sighup信号，让其重启装载配置新的参数值即可。postmaster进程收到sighup信号后，也会向它的子进程发送sighup信号，让这些参数值在所有的进程中都生效。
4. backend：在postgresql.conf文件中更改这些设置无须重启服务器，只需要向postmaster进程发送sighup信号，让它重新读取postgresql.conf中新的配置值即可。但新的配置值只会出现在这之后的新连接中，在已有的连接中，这些参数的值不会改变。
5. superuser：这类参数可以由超级用户使用set来改变。
6. user：普通用户可使用set命令通过此类参数来改变本连接中的配置值。
   1. 1. 连接配置项
7. listen_addresses：string类型，声明服务器监听客户端连接的TCP/IP地址，改变此参数需要重启数据库服务。
8. port：integer类型，指定服务器监听的TCP端口，默认为5432。改变此参数需要重启数据库服务。
9. max_connections：integer类型，运行和数据库连接的最大并发连接数。
10. superuser_reserved_connections：integer类型，决定为PostgreSQL超级用户连接而保留的连接数。改变此参数需要重启数据库服务。
11. unix_socket_directory：string类型，声明服务器监听客户端连接的unix域套接字目录。
12. unix_socket_group：string类型，设置unix域套接字的所属组。
13. unix_socket_permissions：integer类型，设置unix域套接字的访问权限。
14. bonjour：boolean类型，也称为零配置联网，是苹果电脑公司的一个服务器搜索协议，此参数表示是否让Boujour搜索到PostgreSQL服务。
15. bonjour_name：string类型，声明Boujour服务名称。
16. tcp_keepalives_idle：integer类型，表示在一个TCP连接中空闲多长时间后会发送一个keepalive报文。
17. tcp_keepalives_interval：integer类型，在一个空闲TCP连接中，定义在发送第一个TCP keepalive包后如果在该参数给定的时间间隔内没有收到对端的回包，则开始发送第二个…，直到达到tcp_keepalives_count次后仍没有收到回包，则认为中断，关闭连接。
18. tcp_keepalives_count：integer类型，在一个空闲TCP连接上，发送keepalive包后，如果一直没有收到对端的回包，最多发送keepalive次报文，就认为TCP连接已中断
    1. 1. 内存配置项
19. shared_buffers：integer类型，设置数据库服务器将使用的共享内存缓冲区数量，此缓冲区为缓存数据块所用。
20. temp_buffer：integer类型，设置每个数据库会话使用的临时缓冲区的最大数目。此本地缓冲区只用于访问临时表。
21. work_mem：integer类型，声明内部排序操作和hash表在开始使用临时磁盘文件之前可使用的内存数目。
22. maintenance_work_mem：integer类型，声明在维护性操作（比如vacuum、create index和alter table add foreign key）中使用的最大内存数。
23. max_stack_depth：integer类型，声明服务器执行堆栈的最大安全深度。
    1. 1. 预写式日志的配置项
24. wal_level：enumeration类型，可选值为“minimal”、“archive”、“hot_standby”，此配置项决定有多少信息可以写入WAL日志中。
25. fsync：boolean类型，表示是否使用fsync()系统调用（或等价调用），把文件系统中的脏页刷新到物理磁盘，确保数据库在操作系统或者硬件崩溃的情况下可恢复到谁与谁的状态。
26. synchronous_commit：boolean类型，声明提交一个事物是否需要等待其把WAL日志写入磁盘后再返回，默认值是“on”。
27. wal_sync_method：enum类型，用来指定向磁盘强制更新WAL日志数据的方法。可取值“open_datasync”、“fdatasync”、“fsync_writethrough”、“fsync”、“open_sync”。
28. full_page_writes：boolean类型，打开这个选项时，PostgreSQL服务器会在检查点（checkpoints）之后对页面第一次修改时将整个页面写到WAL日志里。
29. wal_buffers：integer类型，指定放在共享内存里用于存储WAL日志的缓冲区数目。
30. wal_write_delay：integer类型，指定wal write process把WAL日志写入磁盘的周期。
31. commit_delay：integer类型，指定向WAL缓冲区写入记录和将缓冲区刷新到磁盘之间的时间延迟，以微秒为单位。
32. commit_siblings：integer类型，在执行commit_delay延迟时，要求同时打开的最少并发事物数。默认是5。
    1. 1. 错误报告和日志项

logging_collector = on

log_rotation_age = 1d

log_rotation_size = 10MB

将PostgreSQL数据库配置成保留固定数目的日志，如一周：

log_filename = ‘postgresql-%u.log’

log_rotation_age = 1d

log_truncate_on_rotation = on

日志文件名只能是“postgresql-%u.log”，其中“%u”代表周几。

日志配置项如下：

1. log_destination：可取值“stderr”、“csvlog”、“syslog”
2. logging_collector：“on”、“off”
3. log_directory：日志输出的路径，可以是绝对路径或相对于数据目录的相对路径。
4. log_filename：文件名，可以带格式字符串。
5. log_rotation_age：日志超过多长时间时，就生成一个新的文件。
6. log_rotation_size：日志超过多大时，就生成一个新的文件。
7. log_truncate_on_rotation：当生成的新文件值文件名已存在时，是否覆盖同名旧文件。
8. syslog_facility：可取值local0、local1、local2、local3、local4、local5、local6、local7。
9. syslog_ident：当使用syslog时，用于在syslog日志中标识PostgreSQL的程序名。默认为“postgresql.conf”。

log级别由参数log_min_messages来控制，可取值debug5、debug4、debug3、debug2、debug1、info、notice、warning、error、log、fatal、panic。

记录运行慢的SQL记录，由参数log_min_duration_statement来控制。当SQL产生了一定级别的日志时，也可以被记录，由参数log_min_error_statement来控制。取值同log_min_messages。参数log_statement控制是否记录DDL、DML或所有SQL的功能。

1. 1. 访问控制配置文件
      1. pg_hba.conf文件

pg_hba.conf(host-based authentication)基于主机认证。每条记录声明一种连接类型、一个客户端IP地址范围、一个数据库名、一个用户名、以及对匹配这些参数的连接所使用的认证方法。

1. 1. 1. 认证方法介绍

PostgreSQL支持以下验证方法：

1. trust：无条件地允许连接。
2. reject：无条件的拒绝连接。
3. md5：提供一个md5加密的口令进行认证。
4. gss：用gssapi认证用户。
5. sspi：用sspi认证用户。
6. krb5：用kerberos v5认证用户。
7. ident：运行客户端上的特定操作系统用户连接到数据库。
8. ldap：用ldap服务器认证。
9. radius：用radius服务器认证。
10. cert：用ssl客户端证书认证。
11. pam：用操作系统提供的可插入认证模块服务（PAM）来认证。
    1. 备份和还原
       1. 逻辑备份

PostgreSQL提供了pg_dump、pg_dumpall命令进行数据库的逻辑备份。pg_dump生成的备份文件可以是一个SQL脚本文件或归档文件。

1. 1. 1. pg_dump命令

pg_dump [connection-option …] [option…] [dbname]

连接选项参数如下：

1. -h host或--host=host
2. -p port或--port=port
3. -U username或--username=username
4. -w或--no-password
5. -W或--password
6. --role=rolename
7. dbname
8. -a或--data-only
9. -b或--blobs
10. -c或--clean
11. -C或--create
12. -E encoding或--encoding=encoding
13. -f file --file=file
14. -F format或--format=format
15. -n schema或--schema=schema
16. -N schema或--exclude-schema=schema
17. -o或--oids
18. -O或--no-owner
19. -s或--schema-only
20. -S username或--superuser=username
21. -t table或--table=table
22. -T table或--exclude-table=table
23. -v或--verbose
24. -V或--version
25. -x或--no-privileges或--no-acl
26. -Z 0…9或--compress=0…9
27. --binary-upgrade
28. --inserts
29. --column-inserts或--attribute-inserts
30. --disable-dollar-quoting
31. --disable-triggers
32. --lock-wait-timeout=timeout
33. --no-tablespaces
34. --use-set-session-authorization
    1. 1. pg_restore命令

pg_restore[connection-option…] [option…] [filename]

连接选项参数如下：

1. -h host或--host=host
2. -p port或--port=port
3. -U username或--username=username
4. -w或--no-password
5. -W或--password
6. --role=rolename
7. filename
8. -a或--data-only
9. -c或--clean
10. -C或--create
11. -d dbname或--dbname=dbname
12. -e或--exit-on-error
13. -f filename或--file=filename
14. -F format或--format=format
15. -I index或--index=index
16. -j number-of-jobs或--jobs=number-of-jobs
17. -I或--list
18. -L list-file或--use-list=list-file
19. -n namespace或--schema=schema
20. -O或--no-owner
21. --o-tablespaces
22. -P function-name(argtype [, …])或--function=function-name(argtype [, …])
23. -s或--schema-only
24. -S username或--superuser=username
25. -t table或--table=table
26. -T trigger或--trigger-trigger
27. -v或--verbose
28. -V或--version
29. -x或--no-privileges或--no-acl
30. --disable-triggers
31. --use-set-session-authorization
32. --no-data-for-faild-tables
33. -I或--single-transaction
    1. 1. 物理备份

冷备份，停止数据库，把数据库的PGDATA目录拷贝下来。

不停止数据库完成备份，即热备份或在线备份。有两种方式：

1. 使用数据库的PITR方法进行热备份
2. 使用文件系统或块设备级别的快照功能完成备份
   1. 常用管理命令
      1. 查看系统信息的常用命令

select version();

select pg_postmaster_start_time();

pg_ctl reload

select pg_conf_load_time();

show timezone;// 显示当前数据库时区

psql -l // 查看当前实例中有哪些数据库

select user;

select current_user;

select session_user;

select current_catalog, current_database();

select inet_client_addr(), inet_client_port(), ident_server_addr(), inet_server_port, pg_backend_pid;

// 查看后台进程

ps -ef|grep 487 |grep -v grep

// 查看当前的一些参数配置

show shared_buffers;

select current_setting(‘shared_buffers’);

// 修改当前session的参数配置

set maintenance_work_mem to ‘128MB’;

select set_config(‘maintenance_work_mem’, ‘128MB’, false);

// 查看当前正在写的WAL文件

select pg_xlogfile_name(pg_current_xlog_location());

// 查看当前WAL的buffer中还有多少字节的数据没有写到磁盘中

select pg_xlog_location_diff(pg_current_xlog_insert_location(), pg_current_xlog_location());

// 查看数据库实例是否正在做基础备份

select pg_is_in_backup(), pg_backup_start_time();

// 查看当前数据库实例是Hot Standby状态还是正常数据库状态

select pg_is_in_recovery();

// 查看数据库大小

select pg_database_size(‘osdba’), pg_size_pretty(pg_database_size(‘osdba’));

// 查看表大小(不包含索引pg_relation_size(‘ipdb2’)及包含索引pg_total_relation_size(‘ipdb2’))

select pg_size_pretty(pg_relation_size(‘ipdb2’));

select pg_size_pretty(pg_total_relation_size(‘ipdb2’));

// 查看表上所有索引的大小

select pg_size_pretty(pg_indexes_size(‘ipdb2’));

// 查看全局和默认表空间大小

select pg_size_pretty(pg_tablespace_size(‘pg_global’));

select pg_size_pretty(pg_tablespace_size(‘pg_default’));

// 查看表对应的数据文件

select pg_relation_filepath(‘test01’);

1. 1. 1. 系统维护常用命令

修改postgresql.conf后，让其生效有两种方法：

1. pg_ctl reload
2. select pg_reload_conf();

 

1. PostgreSQL中执行计划
   1. 执行计划的解释
      1. EXPLAIN命令

PostgreSQL中explain命令格式如下：

EXPLAIN [(option [, …])] statement

EXPLAIN [ANALYZE] [VERBOSE] statement

ANALYZE选项通过实际执行的SQL来获得相应的执行计划。

VERBOSE选项用于显示计划的附加信息。

COSTS选项显示每个计划节点的启动成本和总成本，以及估计行数和每行宽度。

BUFFERS选项显示关于缓冲区使用的信息。

FORMAT选项指定输出格式，输出格式可以是TEXT、XML、JSON或YAML

1. 1. 1. EXPLAIN输出结果解释

explain select * from emp;

Seq Scan on emp  (cost=0.00..1.14 rows=14 width=136)

1. Seq Scan on emp表示顺序扫描表emp，即全表扫描。
2. cost=0.00..1.14后有两个数字，中间由”..”分隔，第一个数字”0.00”表示启动的成本，即返回第一行需要多少cost值；第二个数字表示返回所有的数据的成本。
3. rows=14：表示会返回14行
4. width=136：表示每行平均宽度为136字节。

”cost”描述一个SQL执行的代价是多少。值如下：

1. 顺序扫描一个数据块，cost值定为1。
2. 随机扫描一个数据块，cost值定为4。
3. 处理一个数据行的CPU，cost为0.01。
4. 处理一个索引行的CPU，cost为0.005。
5. 每个操作符的CPU代价为0.0025。

根据上述操作类型计算出一个SQL的执行代价。

explain select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;

Hash Join  (cost=1.09..2.32 rows=4 width=78)

  Hash Cond: (e.deptno = d.deptno)

  ->  Seq Scan on emp e  (cost=0.00..1.14 rows=14 width=44)

  ->  Hash  (cost=1.04..1.04 rows=4 width=58)

        ->  Seq Scan on dept d  (cost=0.00..1.04 rows=4 width=58)

1. 1. 1. EXPLAIN使用示例

默认情况下，输出的执行计划是文本格式，也可以输出Json格式，如：

explain (format json) select * from emp;

[

    {

        "Plan":{

            "Node Type":"Seq Scan",

            "Relation Name":"emp",

            "Alias":"emp",

            "Startup Cost":0,

            "Total Cost":1.14,

            "Plan Rows":14,

            "Plan Width":136

        }

    }

]

也能输出xml格式，如下：

explain (format xml) select * from emp;

<explain xmlns="http://www.postgresql.org/2009/explain">  

  <Query>

    <Plan>

      <Node-Type>Seq Scan</Node-Type>  

      <Relation-Name>emp</Relation-Name>  

      <Alias>emp</Alias>  

      <Startup-Cost>0.00</Startup-Cost>  

      <Total-Cost>1.14</Total-Cost>  

      <Plan-Rows>14</Plan-Rows>  

      <Plan-Width>136</Plan-Width>

    </Plan>

  </Query>

</explain>

还可以输出yaml格式，如下：

explain (format yaml) select * from emp;

- Plan:

    Node Type: "Seq Scan"

    Relation Name: "emp"

    Alias: "emp"

    Startup Cost: 0.00

    Total Cost: 1.14

    Plan Rows: 14

    Plan Width: 136

加上”analyze”参数后，可通过实际执行来获得更精确的执行计划，如下两种写法均可：

1. explain analyze select * from emp;
2. explain (analyze true) select * from emp;

Seq Scan on emp  (cost=0.00..1.14 rows=14 width=136) (actual time=0.010..0.012 rows=14 loops=1)

Planning time: 0.068 ms

Execution time: 0.033 ms

若不想查看执行的路径情况，不看cost，如下：

explain (costs false) select * from emp;

Seq Scan on emp

联合使用analyze选项和buffers选项，可通过实际执行查看实际的代价和缓冲区情况，命令如下：

explain (analyze true, buffers true) select * from emp;

Seq Scan on emp  (cost=0.00..1.14 rows=14 width=136) (actual time=0.010..0.011 rows=14 loops=1)

  Buffers: shared hit=1 read = x written = y

Planning time: 0.069 ms

Execution time: 0.044 ms

其中，” shared hit=1”表示在共享内存中直接读到1个块，整行表示从磁盘中读了1块，写磁盘共y块。select会写是因为共享内存中有脏块，从磁盘中读出的块必须把内存中的脏块挤出去，所以产生了很多的写。

1. 1. 1. 全表扫描

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1. PostgreSQL特色功能
   1. 规则系统

更准确的说，规则系统是查询重写规则的系统。从使用上说，规则系统上的一些功能也可以通过函数和触发器来实现。但规则系统与触发器完全不同。它是在执行前把用户发过来的SQL通过内部的规则定义改变成另一个SQL后再执行的一种方式。

1. 1. 1. SELECT规则

PostgreSQL的视图是通过SELECT规则实现的。命令如下：

create view myview as select * from mytab;

上述创建视图语句与下面两个命令执行结果相同：

create table myview (same column list as mytab);

create rule “_RETURN” as on select to myview do instead select * from mytab;

由此可见，视图实际上是一张表，只不过是在这张表上加了一个select规则。其中select规则的后续动作只能是“INSTEAD SELECT”，名称只能是“_RETURN”。

1. 1. 1. 更新规则

创建更新规则语法如下：

create [or replace] rule name as on event

to table_name [where condition]

do [also | instead] {nothing | command | (command ; command… ) }

其中event取值如下：

SELECT：当SQL的查询计划中存在查询表的操作时会重写查询计划。

INSERT：当SQL的查询计划中存在向表中插入数据的操作时会重写查询计划。

UPDATE：当SQL的查询计划中存在向表中更新数据的操作时会重写查询计划。

DELETE：当SQL的查询计划中存在将表中数据删除的操作时会重写查询计划。

其中“also”与“command”说明如下：

also：除执行原本操作外，还执行一些附件操作，由后面的“command”执行。

instead：用后面的“command”操作取代原操作。

语法中的“NOTHING”表示什么都不执行。

1. 1. 1. 规则和权限

规则（RULE）是从属于表或视图的。如果一张表属于一个用户，则这张表上的所有规则都属于这个用户。

因规则而使用的关系要对定义规则的属主进行权限检查，而不是检查执行规则的用户，这意味着一个用户只需要对查询里明确指定的表/视图拥有所需的权限就可进行操作。

1. 1. 1. 规则和命令状态

PostgreSQL服务器会为收到的每个命令返回一个命令状态字符串，用以表示这条SQL影响的行数（对于INSERT返回当前行的oid）。

insert 返回的命令状态字符串有三个字段，第一个为“INSERT”，第二个为这一行的oid，如果表没有oid字段，则返0，第三个表示实际插入的行数。delete语句返回的命令状态字符串中包含了删除掉的行数，而update语句返回的命令状态字符串中包含了更新的行数。

不过，规则会改变这些命令影响的行数。规则对命令状态的形响如下：

如果查询中存在有条件的INSTEAD 规则（创建规则时指定了where子句），那么规则的条件就会加到最初的查询里，这可能会导致查询处理的数据行数减少，从而使SQL执行结果状态中返回的行数也变少。如果查询中无任何 INSTEAD规则，原始查询会完整执行，那么结果中的状态行数就不会发生变化。

如果查询有任何一个无条件的INSTEAD规则，那么最初的查询语句将不会被执行。在这种情况下，服务器返回的命令状态为：与由 INSTEAD 规则（条件的或非条件的）插人的最后一条和最初查询语句命令类型（INSERT、UPDATE、DELETE）相同的SQL语句的命令状态。如果规则添加的查询都不符合这收要求，那么返回的命令状态显示源查询类型，而影响的行数和oid字段为零。

1. 1. 1. 规则与触发器的比较

从外部导数据的命令“copy from”会让触发器执行，但不会调用规则系统。

触发器能做的很多事情，使用PostgreSQL的规则系统也可以完成，使用哪种方式取决于具体的应用场景。规则系统是通过查询重写来实现的。而触发器通常是为每一个行都触发执行一次，所以对于批量操作，如果使用规则，可能会更好的执行计划，从而提高效率。

1. 1. 模式匹配和正则表达式
      1. PostgreSQL中的模式匹配和正则表达式介绍

PostgreSQL提供三种实现模式匹配的方法：

1. 传统SQL的LIKE操作符
2. SQL99新增的SIMILAR TO操作符
3. POSIX风格的正则表达式

另外还有一个模式匹配函数substring可用，它可以使用similar to或posix风格的正则表达式。

1. 1. 1. 传统SQL的LIKE操作符

百分号“%”代表0个或任意个字符，下划线“_”代表任意一个字符。

若匹配的字符串中有“%”或“_”，可在字符串前加转义字符反斜杠“\”。如 like ‘%\%%’。转义字符也可使用escape子句指定成其他的字符，如“#”，（select * from emp where ename like ‘%#%%’ escape ‘#’;），转义字符本身可以使用连续两个转义字符去除其特殊的意义。如like ‘%\\%’。

另外，like表达式不仅用在where子句中，也可以用于其他表达式中，如下：

select ‘abc’ like ‘ab%’;

PostgreSQL还提供了标准SQL中没有的ilike操作符，用于忽略大小写的模式匹配。此外，与like等意思相同的操作符如下：

1. ~~，等效于like
2. ~~*，等效于ilike
3. !~~，等效于not like
4. !~~*，等效于not ilike
   1. 1. SIMILAR TO正则表达式

similar to操作符只有在匹配整个字符串时才能成功。同like一样，使用下划线和百分号分别匹配单个字符和任意字符串。

此外还支持如下与posix正则表达式相同的模式匹配元字符。

1. |:表示选择两个候选项之一。
2. *:表示重复前面的项两次或更多次。
3. +:表示重复前面的项一次或更多次。
4. ?: 表示重复前面的项零次或一次。
5. {m}:表示重复前面的项m次。
6. {m, }:表示重复前面的项m次或更多次。
7. {m, n}:表示重复前面的项至少m次，不超过n次。
8. ():可以作为项目分组到一个独立的逻辑项中。
9. […]:声明一个字符类，就像posix正则表达式。

在similar to中英文的句号“.”并不是元字符。

1. 1. 1. POSIX正则表达式

POSIX正则表达式的模式匹配操作符如下：

1. ~:匹配正则表达式，区分大小写。
2. ~*:匹配正则表达式，不区分大小写。
3. !~: 不匹配正则表达式，区分大小写。
4. !~*: 不匹配正则表达式，不区分大小写。

posix正则表达式中，百分号与下划线没有像在like或similar to中的特殊意义。只要部分匹配到字符串就返回真。如果想匹配开头或者结尾，需要使用posix中的“^”或“$”元字符，如下：

select ‘abc’ ~ ‘bc$’;

1. 1. 1. 模式匹配函数substring

substring可以使用正则表达式，有三种用法：

第一种：substring(<字符串>,<数字>,[数字])

select substring(‘abc’, 2)

第二种：substring(<字符串>,<字符串>)

select substring(‘abc-1-abc’, E’(\\d+)’);

第三种：substring(<字符串>,<字符串>,<字符串>)或substring(<字符串> from <字符串> for <字符串>)

select substring(‘abc-1-abc’, ‘%#”[0-9]+#”%’, ‘#’);

1. 1. listen与notify

PostgreSQL提供了client端之间通过服务器端进行消息通信的机制。用listen和notify命令来完成。

1. listen：监听消息通道。
2. unlisten：取消先前的监听。
3. notify：发送消息到消息通道中。
4. pg_notify()：与notify命令的功能相同。
5. pg_listening_channels():调用此函数可以查询当前session已注册了哪些消息监听。
   1. 索引的特色
      1. 表达式上的索引

PostgreSQL支持函数索引。实际上PostgreSQL索引的键，除了可以是一个函数外，还可以是从一个或多个字段计算出来的标量表达式。

在做大小写无关比较时，常用方法是使用lower函数如下：

select * from mytest where lower(note) = ‘hello world’;

因为使用了函数，无法利用到“note”字段上的普通索引，所以这时就需要建一个函数索引了，如下：

create index mytest_lower_note_idx on mytest (lower(note));

表达式上的索引并不是在进行索引查找时计算表达式的，而是在插入数据行或更新数据行时进行计算的，因此在插入或更新时，表达式上的所有会慢一些。

如果把表达式上的索引声明为unique，如下：

create unique index mytest_lower_note_idx on mytest (lower(note));

此时，它会禁止往“note”列中插入只是大小写有区别而内容完全相同的数据行。因此，在表达式上的索引可以实现简单唯一约束无法实现的一些约束。

1. 1. 1. 部分索引

部分索引是对一个表中的部分行进行的索引，由一个表达式把这部分行筛选出来，这个条件表达式被称为部分索引的谓词。如下，官方手册给出的三个示例：

1. 设置一个部分索引以排除普通数值

查询和统计来自互联网外部用户访问的IP，那么就不需要对内网的IP进行索引，另因为内网的IP地址少，访问量大，索引效率也不高。假设表如下：

create table access_log (

url varchar,

client_ip inet,

…

);

建部分索引如下：

create index access_log_client_ip_idx on access_log(client_ip)

where not (client_ip > inet ‘192.168.100.0’

and client_ip < inet ‘192.168.100.255’);

如下SQL就可以使用到这个索引：

select * from access_log where client_ip = inet ‘114.113.220.27’;

而如下SQL就不能走到此部分索引了，因为它的数据不在索引中。

select * from access_log where client_ip = inet ‘192.168.100.55’;

1. 设置一个部分索引以排除不感兴趣的数值

假设有一个表，包含已付款和未付款的订单，未付款的订单占总表的一小部分，并且是经常使用的部分，那么可以只在未付款订单上创建一个索引来改善性能。表结构如下：

create table orders (

order_nr int,

amount decimal(12, 2),

billed boolean

);

创建的索引如下：

create index orders_unbilled_index on orders (order_nr) where billed is not true;

如下SQL就会用到这个索引：

select * from orders where billed is not true and order_nr < 1000;

该索引也可以用于那些完全不涉及索引键order_nr的查询，如下：

explain select * from orders where billed is not true and amount > 40105960.00;

PostgreSQL支持带任意谓词的部分索引，只要涉及被索引的表的字段就行。不过谓词必须和那些希望从该索引中获益的查询中的where条件相匹配。准确说只有在系统识别出该查询的where条件简单地包含了该索引的谓词时，此部分索引才能用于该查询。

1. 设置一个部分唯一索引

部分索引的第三种用途是在表的子集里创建唯一索引，这样就可强制在满足谓词的行中保持唯一性。同时并不约束那些不需要唯一的行。

假设有一个记录测试项目是否成功的表，希望确保在每个目标和课题的组合中只有一条“成功”的记录，但是可以有任意数量的“不成功”记录。表结构如下：

create table tests (

subject text,

target text,

success boolean,

…

);

create unique index tests_success_constraint on tests (subject, target) where success;

如果只有少数测试的结果是成功的，而很多测试的结果为不成功，那么这将是一种非常高效的实现方法。

1. 1. 1. GiST索引

GiST(Generalized Search Tree)意思是通用搜索树。它是一种平衡树结构的访问方法，是用户建立自定义索引的一个基础模板，用户只要按模板实现所要求的GiST操作类中的一系列回调函数，就可以实现自定义的索引，而不用去关心这个GiST索引具体是如何存储的。B-trees和许多其他的索引都可以用GiST实现。

PostgreSQL支持在任意数据类型上建立B-tree索引，但是B-tree只支持范围谓词(<,=,>)，hash索引仅支持相等查询，而GiST的索引还能支持包含(@>)、重叠(&&)等复杂运算。

要实现一个自定义的GiST索引操作类，需要实现GiST索引操作类的以下7个用户自定义函数。

1. consistent：给出一个索引项p和一个查询q。
2. union：表示如何在树中组合信息。将一系列的项组合成一个索引项。
3. compress：表示如何把数据项转换成一种适合存储在索引页中的格式。
4. decompress：compress的反向操作。
5. penalty：返回一个值，用于表示把一个新节点插入到一个树杈上的代价(cost)，返回值应该是一个非负值，如果负值则被当作0。
6. picksplit：当一个索引列需要分裂时，这个函数决定哪些节点需要留在旧的索引页中，哪些节点需要移到新的索引页中。
7. same：判断两个索引项是否相等，相等返回true，否则返回false。
8. distance：返回索引项p和查询值q之间的“距离”。

PostgreSQL已经对一些内置类型实现了GiST索引操作类，在这些类型上可以直接建GiST索引，如下：

如下建GiST索引的示例：

create table test01 (p point, b box, ip inet);

create index on test01 using gist (ip inet_ops);

create index on test01 using gist (p);

create index on test01 using gist (b);

contrib下的以下模块也提供了一些类型的GiST索引的操作类。

1. btree_gist：使用gist实现的btree。
2. cube：多维cube类型。
3. hstore：一种key/value存储类型。
4. intarray：一维int4数据的RD-Tree实现。
5. Itree：树型结构的索引。
6. pg_trgm：文件相似性的索引。
7. seg：浮点范围类型的索引。
   1. 1. SP-GiST索引

SP-GiST（space-partitioned GiST），即空间分区GiST索引。可以使用此框架实现以下类型的索引：

1. quad-trees
2. k-d trees
3. tadix trees

实现一个自定义的SP-GiST索引操作类，需要实现以下五个用户自定义函数：

1. config：返回索引实现中的一些静态信息。
2. choose：选择如何把新的值插入到索引内部tuple中。
3. picksplit：决定如何在一些叶子tuple上创建一个新的内部tuple。
4. inner_consistent：在树的搜索过程中返回一系列的节点（树杈上的）。
5. leaf_consistent：如果一个叶子节点满足查询则返回真。
   1. 1. GIN索引

GIN(Generalized Inverted Index)，即广义倒序排索引。GIN索引通常用于全文检索，即在文章中搜索指定的词。实现一个自定义的SP-GiST索引操作类，需要实现以下三个用户自定义函数：

1. compare：比较两个值，根据大小返回负数、0、正数。
2. extractValue
3. extractQuery

可选函数：

1. consistent
2. triConsistent
3. comparePartial

contrib下的以下一些模块也实现了一些gin索引操作类：

1. btree_gin：实现类型btree的功能。
2. hstore：存储（key，value）对的一种类型。
3. intarray：增强int[]。
4. pg_trgm：文本相似匹配。
   1. 序列的使用
      1. 序列的创建

创建序列语法如下：

create [temporary | temp] sequence name [increment [by] increment] [minvalue minvalue | no minvalue] [maxvalue maxvalue | no maxvalue] [start [with] start] [chche chche] [[no] cycle] [owned by { table.column | none }]

语法参数说明如下：

1. temporary或temp：如果创建语句中包含了此修饰词，那么会创建出只存在于这个会话中的临时序列，会话结束时该序列会自动删除。创建出的临时序列，除非用模式（schema）修饰，否则同一个会话中的同名的非临时序列是不可见的。
2. increment [by] increment：指定序列的步长，正数产生一个递增的序列，负数产生一个递减的序列。默认值是1。
3. minvalue minvalue | no minvalue：指定序列的最小值，对于递增序列，最小值默认为1；对于递减序列，最小值默认为-263-1。no minvalue相当于使用默认值。
4. maxvalue maxvalue | no maxvalue：指定序列的最大值，对于递增序列，最大值默认为263-1；对于递减序列，最大值默认为-1。no maxvalue相当于使用默认值。
5. start [with] start：指定开始的起点值。
6. [chche cache]：指定cache的数值。最小值（也是默认值）是1，表示一次只能生成一个值，也就是说没有缓存。
7. [no] cycle：在序列到达maxvalue或minvalue时cycle选项可循环继续下去。默认是no cycle。
8. owned by {table.column | none}：owned by选项用于将序列关联到一个特定的表字段上。这样，删除那个字段或其所在的表时将自动删除绑定的序列。默认的owned by none表示无关联。
   1. 1. 序列的使用及相关的函数

1. 1. 1. 常见问题

如果cache大于1，并且这个序列对象将被用于多会话并发的场合，那么每个会话在每次访问序列对象的过程中都将分配并缓存随后的序列值，并且相应地增加序列对象的last_value。这样，同一个事物中随后的cache-1次nextval将只返回预先分配的数值。

1. 1. 咨询锁的使用
      1. 咨询锁的定义

PostgreSQL运行创建由应用定义其含义，与数据库本身没有关系的锁，这种锁被称为咨询锁（advisory lock）。通过这种锁，PostgreSQL数据库可以给应用提供一种与具体的表数据没有任何关系的锁，PostgreSQL变成了一个锁服务提供中心。咨询锁与具体的数据没有关系，多个进程访问同一个数据库时，如果想协调这些进程对一些非数据库资源的并发访问，就可以使用这种咨询锁。

1. 1. 1. 咨询锁的函数及使用

咨询锁用一个64bit的数字或两个32bit的数字来表示，并提供一些函数来实现加锁和释放锁的操作。

1. 1. 1. 常见问题

当连接中断后，数据库会话就会被中止，其持有的咨询锁也会被释放。

当事务回滚或提交时，持有的session级别的咨询锁不会被释放。

两个session不能持有一把事务级别的锁，事务级别的锁在事务结束时会自动释放。

1. 1. SQL/MED
      1. SQL/MED的介绍

SQL/MED（Management of External Data）SQL语音中管理外部数据的一个扩展标准。通过定义一个外部数据包装器和数据连接类型来管理外部数据。相当于一套连接其他数据源的框架和标准。第三方可以根据PostgreSQL提供的外部数据源开发各种插件来连接其他数据库。在SQL/MED标准中，实现了以下四类数据库对象来访问外部数据源：

1. Foreign Data Wrapper：外部数据包装器，缩写“FDW”，相当于定义外部数据驱动。
2. Server：外部数据服务器，相当于定义一个外部数据源，需要指定外部数据源的Foreign Data Wrapper。
3. User mapping：用户映射，主要把外部数据源的用户映射到本地用户，用于控制权限。
4. Foreign Table：外部表，把外部数据源映射成数据库中的一张外部表。
   1. 1. 外部数据包装器对象

create function file_fdw_handler()

returns fdw_handler

as ‘file_fdw’ langusge c strict;

create function file_fdw_validator(text[], oid)

returns void

as ‘file_fdw’ language c strict;

create foreign data wrapper file_fdw

handler file_fdw_handler

validator file_fdw_validator;

对于handler函数，有如下三点要求：

1. 必须是用C语音写的扩展函数
2. 不能有参数
3. 必须返回“fdw_handler”类型

validator函数要求如下：

1. 必须有两个参数
2. 第一个参数的类型必须是text[]，表示要校验的可选参数。
3. 第二个参数的类型必须是oid，指定可选参数的分类，分类为“server”、“user mapping”、“FDW”、“Table”。

创建外部数据包装器的完整语法如下：

create foreign data wrapper name

[handler handler_function | no handler]

[validator validator_function | no validator]

[options (option ‘value’ [, …])]

外部数据包装器需要由超级用户创建，其他用户没有创建权限，但可以使用它，赋权其他用户方法如下：

grant usage on foreign data wrapper postgres_fdw to user01;

1. 1. 1. 外部服务器对象

Server对象是把FDW与连接外部数据源的连接参数关联起来的对象，主要定义如何连接外部数据源。

create server server_name [type ‘’server_type] [version ‘server_version’]

foreign data wrapper fdw_name

[options (option ‘value’ [, …])]

创建一个指定另一台PostgreSQL数据库的外部数据服务器如下：

create server postgres_fdw_server foreign data wrapper postgres_fdw

options (host ’10.0.3.236’, dbname ‘user01’, port ‘5432’);

创建一个指向MySQL数据库的外部数据服务器：

create server mysql_fdw_server

foreign data wrapper mysql_fdw

options (address ’10.0.3.236’, port ‘3306’);

一个用户创建的外部服务器，如果想让另一个用户使用，也需要赋权：

grant usage on foreign server mysql_fdw_server to user02;

1. 1. 1. 用户映射对象

用户映射主要解决PostgreSQL用户与外部服务器的用户之间的映射关系。创建用户映射语法如下：

create user mapping for {user_name | user | current_user | public}

server server_name

[options (option ‘value’ [, …])]

实例如下：

create user mapping for user01

server postgres_fdw_server

options(user ‘user02’, password ‘okuser02’);

其中“user01”是本地数据库用户，“user02”是远程数据库用户。

1. 1. 1. 外部表对象

SQL/MED就是把外部数据源中的数据对象映射成一张外部表，然后就可以像访问普通表一样访问这张外部表了。

创建外部表语法如下：

create foreign table [if not exists] table_name ([

column_name data_type [options (option ‘value’ [, …])] [collate collation] [column_constraint[…]]

])

server server_name

[options (option ‘value’ [, …])]

示例如下：

create foreign table fttest01 (

id int,

note text

) server postgres_fdw_server

options (table_name ‘testtab01’);

1. 1. 1. file_fdw使用实例

file_fdw插件为PostgreSQL数据库提供了访问外部文件数据的能力。实际上file_fdw是通过copy api来访问外部文本文件的，所以file_fdw的选项除filename外都与copy命令相同。目前file_fdw还不支持copy命令中的oids、force_quote、force_not_null选项。

1. 1. 1. postgres_fdw使用实例

postgres_fdw用于访问服务于其他PostgreSQL数据库的外部数据包装器，它提供了与原先已有dblink模块相同的功能，但使用postgres_fdw更符合SQL标准，在某些场景下比dblink更好的性能。

1. Standby数据库搭建
   1. Standby数据库原理

提供读写的服务器称为primary database或master database。若备份数据库在接收主数据库同步数据和应用同步数据时，不能提供只读服务器，则称该备库为warm standby server，否则，称该备库为hot standby server。

1. 1. 1. PITR原理

数据目录pg_xlog下始终维护着一个WAL日志文件，此文件用于记录数据库数据文件的每次改变，此日志文件主要目的是：为了在数据库异常崩溃后，通过重放最后一次checkpoint点之后的日志文件，把数据库推到一致状态，此日志文件机制也提供了一种数据库热备份方案；在把数据库只用文件系统的方式备份出来的同时把相应的WAL日志也备份出来。

备份数据库时，可通过简单的cp命令或tar命令等拷贝，备份文件来实现数据库的在线备份，称为基础备份。后续的WAL日志的备份与此基础备份构成一个完整的备份。

把WAL日志送到另一台机器有两种方式：WAL归档日志和流复制方式。

1. 1. 1. WAL日志归档

# 打开归档备份

archive_mode = on

# ”%p”表示在线WAL日志文件的全路径名

# “%f”表示不包括路径的WAL日志文件名

archive_command = ‘cp %p /backup/pgarch/%f’

# 命令scp可将WAL日志拷贝到其他机器上

archive_command = ‘scp %p postgres@192.168.1.100:/backup/pgarch/%f’

1. 1. 1. 流复制（Streaming Replication）

流复制传递日志的方式有同步和异步两种方式：

1. 同步方式：

在primary数据库提交事物时，一定会等到WAL日志传递到standby后才会返回，这样当主备库切换时可以做到零数据丢失。

1. 异步方式：

事物提交后不必等日志传递到standby就即可返回，所以standby数据库比primary库落后很少的时间。

1. 1. 1. Standby的运行原理

postgresql数据库异常中止后，数据库刚重启时，会重放停机前最后一个checkpoint点之后的WAL日志，在把数据库恢复到停机的状态后，自动进入正常状态。可以接收其他用户的查询和修改。

1. 1. 1. 创建Standby的步骤

第一步：生成一个基础备份

第二步：把基础备份拷贝到备机上，配置recovery.conf把备库启动在Standby模式下。

1. 以数据库超级管理员身份连接到数据库，执行：

select pg_start_backup(‘lable’);

1. 执行备份，将数据目录复制下来（tar）
2. 再次以数据库超级用户身份连接数据库执行：

select pg_start_backup();

这将中止备份模式并自动切换到下一个WAL段，自动切换是为了让在备份间隔中写入的最后一个WAL段文件可以立即为下次备份做好准备。

1. 拷贝备份过程中产生的WAL日志文件

注：实际中，pg_start_backup()主要做以下两个工作：

1. 设置写日志标志为：XLogCtl->Insert.forcePageWrites=true，数据库会把变化的整个数据块都记录到数据库中，而不仅仅是块中记录的变化。
2. 强制发生一次checkpoint点
   1. pg_basebackup命令行工具
      1. pg_basebackup介绍

pg_basebackup使用replication协议连接到数据库实例上，所以主库中的pg_hba.conf必须允许replication连接，即在pg_hba.conf中有：

local replication osdba trust

local replication osdba ident

local replication osdba 0.0.0.0/0 md5

其中，第二列表示允许replication连接，理论上一个数据库可以允许被几个pg_basebackup同时连接，但为了不影响主库性能，建议一个数据库上同时只有一个pg_basebackup在做备份

postgresql9.2后支持级联复制，即可以从另一个Standby库上做基础备份，但从Standby备份注意以下：

1. 备份中没有备份历史文件
2. 不确保所有需要的WAL文件都备份了，若想确保需要加命令行参数“-X”。
3. 如果在备份过程中Standby库被提升为主库，则备份会丢失。
4. 要求主库中打开了“full_page_writes”参数，WAL文件不能被类似pg_compresslog的工具去掉full_page_writes信息。
   1. 1. pg_basebackup的命令行参数

pg_basebackup [option ...]

备份到哪个目录

-D directory/--pgdata=directory

指定输出格式(p(plain)[原样输出]/t(tar)[输出的备份文件打包到一个tar文件中])

-F format/--format=format

备份时会把备份中产生的xlog也自动备份出来，恢复数据库时，用xlog把库推到一个一致点。

此命令与“-X fetch”一样，使用时，需设置“wal_keep_segments”参数，以保证在备份过程中，

需要的WAL日志文件不会被覆盖。

-x/--xlog

method可取值[f,fetch,s,stream]

"f"与"fetch"相同，与"-x"参数一样

"s"与"stream"相同，表示备份开始后，启动另一个流复制连接从主库接收WAL日志。

此方式避免了使用"-X f"时，主库上的WAL日志有可能被覆盖而导致失败的问题。

但需要与主库建两个连接，即max_wal_senders参数至少要设置为2或大于2的值。

-X method/--xlog-method=method

“method”可取值"f"、"fetch"、"s"、"stream"。其中"s"与"stream"含义相同，表示备份开始后，启动另一个流复制连接从主库接收WAL日志。此方式避免了使用”-X f”时，主库的WAL日志有可能被覆盖而失败。但此方式需要与主库建两个连接，因此使用此方式时，主库的”max_wal_senders”参数至少大于等于2。

-z/--gzip

仅能与tar输出模式配合使用，表明输出的tar备份包是经过gzip压缩的，相当于生成了一个*.tar.gz的备份包。

-Z level/--compress=level

指定gzip的压缩级别，可选1~9的数字，9表示最高压缩率。

-c fast|spread/--checkpoint=fast|spread

设置checkpoint的模式是fast还是spread。

-l label/--label=label

指定备份的一个标识，是一个任意字符串。

-P/--progress

允许在备份过程中实时的打印备份的进度。

-v/--verbose

详细模式，使用了-P参数后，会打印出正在备份的具体文件的信息。

-V/--verbose

打印pg_basebackup的版本后退出。

-?/--help

显示帮助信息后退出

-h host/--host=host

指定连接的数据库的主机名或IP地址。

-p port/--port=port

指定连接端口

-s interval/--status-interval=interval

指定向服务器端周期反馈状态的秒数，若服务器上配置了流复制的超时，在使用--xlog=stream选项时，则需要设置这个参数，默认为10秒。若设置为0，表示不向服务器反馈状态。

-U username/--username=username

指定连接的用户名

-w/--no-password

指定从来不提示输入密码

-W/--password

强制让pg_basebackup出现输入密码的提示。

1. 1. 1. pg_basebackup使用示例

pg_basebackup –D backup –Ft –z –P

其中pg_basebackup没有指定连接参数，所以会连接到本地库上，成功运行此命令，须配置pg_hba.conf中：

local replication osdba ident

其中，”osdba”是数据库中的一个超级用户，或本地的一个有”replication”权限的用户，而走的认证是”ident”，表示需要与数据库用户”osdba”存在一个对应的操作系统用户

上例中，backup_label文件中内容如下：

~/backup$ cat backup_label

start wal location:2/34000028(file 00000001...)

checkpoint location:2/34000060

backup method:streamed

backup from:master

start time:2014-02-27 21:43:52 CST

label:pg_basebackup base backup

实例二

pg_basebackup –h 10.0.3.101 –U osdba –F p –P –x –R –D /home/osdba/pgdata -1 osdbabackup201401151257

上述命令把10.0.3.101上的数据库备份到本地使用的连接用户名为osdba，输出格式为普通原样输出（-F p），在执行过程中会输出备份的进度（-P），且自动把在备份过程中产生的xlog文件也备份出来（-x），同时在备份中会生成一个recovery.conf，当用此备份启动备库时，只需要简单修改recovery.conf，就可以把数据库启动起来，备份文件都生成到/home/osdba/pgdata目录下。

1. 1. 异步流复制Hot Standby的示例
      1. 配置环境

1. 1. 1. 主数据库的配置

要使用流复制，须允许主库接受流复制的连接，即/home/osdba/pgdata/pg_hba.conf中配置：

host replication osdba 10.0.3.0/24 md5

在主库的db01的/home/osdba/pgdata/postgresql.conf中配置(须重启库)：

listen_addresses = ‘*’

max_wal_senders = 5

wal_level = hot_standby

1. 1. 1. 在Standby上生成基础备份

使用pg_basebackup命令行工具在db02机器上生成基础备份：

Pg_basebackup –h 10.0.3.101 –U osdba –F p –P –x –R –D /home/osdba/pgdata -1 osdbabackup201401151257

1. 1. 1. 启动Standby

在db02机器上启动Standby数据库之前，修改/home/osdba/pgdata/postgresql.conf参数：

hot_standby = on

启动Standby，即可进入Hot Standby状态，此时在主库上建一个测试表，插入数据，在备库上查看数据马上就同步了，因为Hot Standby是只读的，所以在Standby上做修改，操作会失败。

1. 1. 同步流复制的Standby数据库
      1. 同步流复制的架构

同步复制要求在数据写入Standby数据库后，事务的commit才返回，所以Standby库出现问题时，会导致主库被hang住。解决方案是启动两个Standby数据库，只要有一个是正常的，就不会让主库hang住。实际中，同步流复制，总是有一主库和两个以上的Standby库。

1. 1. 1. 同步复制的配置

实现同步复制功能须在主库上配置”snychronous_standby_names”，这个参数指定多个Standby的名称，各个名称通过逗号分隔，而Standby名称是在Standby连接到主库时，由连接参数“application_name”指定的。要使用同步复制，在Standby库的recovery.conf配置如下：

sandby_mode = ‘on’

primary_conninfo = ‘application_name=standby01 user=osdba password=XXXXXX host=10.0.3.101 port=5432 sslmode=disable sslcompression=1’

1. 1. 1. 配置实例

1. 第一步：

主库db01的/home/osdba/pgdata/pg_hba.conf下配置：

host replication osdba 10.0.3.0/24 md5

/home/osdba/pgdata/postgresql.conf配置：

max_wal_senders = 5

wal_level = hot_standby

在主库上指定同步复制的Standby名称，在/home/osdba/pgdata/postgresql.conf中配置：

synchronous_standby_names = ‘standby01,standby02’

其中‘standby01,standby02’是Standby库中配置连接参数“application_name”指定的。

1. 第二步：

在备库db02的/home/osdba/pgdata/recovery.conf中配置“primary_conninfo”中增加连接参数“application_name”，如下：

standby_mode = ‘on’

primary_conninfo = ‘application_name=standby01 user=osdba password=XXXXXX host=10.0.3.101 port=5432 sslmode=disable sslcompression=1’

完成后启动数据库。

1. 第三步

在db03的/home/osdba/pgdata/recovery.conf中配置“primary_conninfo”中增加连接参数“application_name”，如下：

standby_mode = ‘on’

primary_conninfo = ‘application_name=standby02 user=osdba password=XXXXXX host=10.0.3.101 port=5432 sslmode=disable sslcompression=1’

完成后启动数据库。

1. 第四步

在主库上启动同步复制，修改参数“synchronous_standby_names”，并不需要重启主库，只需要重新装载（pg_ctl reload –D /home/osdba/pgdata）配置即可。

下面测试同步复制功能：

先关掉一台Standby（db02），看主库是否能正常工作：

osdba@db02:~$ pgstop

然后到主库上做如下操作：

insert into test01 values (1, ‘111’);

可以看到，当一台Standby损坏时，主库是不受影响的。再关掉一台Standby（db03），命令如下：

osdba@db03: ~$ pgstop

在主库上做如下操作：

select * from test01;

insert into test01 values (2, ‘222’);

主库的非更新查询都是正常的，但更新操作都被hang住了。此时再启动一台Standby（db02），命令如下：

发现主库hang住的操作可以继续下去了，如下：

insert into test01 values (2, ‘222’);

1. 1. 检查备库及流复制情况
      1. 检查异步流复制的情况

查看流复制信息可使用主库上的视图pg_stat_replication:

select pid, state, client_addr, sync_priority, sync_state, sent_location, write_location, flush_location, replay_location from pg_stat_replication;

另外，在pg_stat_replication视图中有以下字段记录发送WAL位置及接收到WAL的位置、备库写WAL日志到磁盘的位置、备库应用日志的位置。

查看备库落后主库多少字节的WAL日志：

select pg_xlog_location_diff(pg_current_xlog_location(), replay_location) from pg_stat_replication;

1. 1. 1. 检查同步流复制的情况

在上一小节中，sync_priority列中可看到备库的优先级，这个优先级由synchronous_standby_names参数配置的顺序决定。目前主库与db02处于同步（sync），而db03状态为“potential”，表示它是一个潜在的同步Standby，当db02损坏时，db03会切换到同步状态，这时关掉db02。剩余db03变成“sync”状态，再次启动db02，db03又从“sync”状态变成了“potential”，db02重新变成了“同步状态”。

1. 1. 1. 视图pg_stat_replication详解

1. 1. 1. 查看备库的状态

执行select pg_is_in_recovery();

若在备库Hot Standby状态返回“t”，若在主库状态下返回“f”。

若备库不是Hot Standby，不能直接连接上去，可使用pg_controldata：

osdba@db01:~$ pg_controldata

主库上看到：Database cluster state: in production

在备库上看到：Database cluster state: in archive recovery

在Hot Standby中，查看备库接收的WAL日志和应用WAL日志的状态：

select pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), pg_last_xact_replay_timestamp();

1. 1. Hot Standby的限制
      1. Hot Standby的查询限制

DML语句（如insert、update、delete、copy from、truncate等）和DDL（如create、drop、alter、comment）都不能在Hot Standby执行，另外” select … for share|update”语句不能执行。

部分类型表锁可以使用，只是需要在”begin”启动的事物块中使用。如下：

access share

row share

row exclusive mode

序列中会导致更新的函数同样不能执行

nextval()

setval()

消息通知的语句也不能执行

listen

unlisten

notify

1. 1. 1. Hot Standby的查询冲突处理

主库的一些操作会在Hot Standby上产生冲突，导致Hot Standby上正在执行的查询被取消，如下：

1. 主库上运行的vacuum清理掉了备库上的查询还需要的多版本数据。
2. 主库上执行lock命令或各种DDL语句会在表上产生exclusive锁，在备库上对这些表进行查询时，会产生冲突
3. 主库上删除一个表空间，而备库上的查询需要存放一些临时文件在此表空间中
4. 主库上删除一个数据库，备库上有很多session还连接在这个数据库上，则session都将被断开连接

发生冲突解决方法：

1. 让备库上的应用WAL日志等待一段时间，等备库上的查询结束后再应用
2. 取消备库上正在执行的查询

max_standby_archive_delay:备库从WAL归档中读取时的最大延误。默认30s，若设置为-1，则一直等下去。

max_standby_streaming_delay:备库从流复制中读取WAL时的最大延迟，默认30s，若设置为-1，则一直等下去。

查询备库上因冲突而被取消执行的SQL数量可通过视图pg_stat_database_conflicts。

1. 1. 恢复配置详解
      1. 归档恢复配置的配置项
2. restore_command:指定Standby如何获得WAL日志文件
3. archive_cleanup_command:清理Standby数据库上不需要的WAL日志文件
4. recovery_end_command:恢复完成后，可以执行一个命令

例如在主库上配置archive_command参数，把WAL文件拷贝到Standby库的一个目录下：

archive_command = ‘scp %p 192.168.1.52:/data/archivedir/%f.mid && ssh 192.168.1.52 “mv /data/archivedir/%f.mid /data/archivedir/%f”’

然后在Standby数据库的recovery.conf中配置restore_command参数：

restore_command = ‘cp /data/archivedir /%f “%p”’

contrib目录中提供了一个命令行工具pg_archivecleanup可以很方便地实现清理Standby已使用完的WAL日志文件。

archive_cleanup_command = ‘pg_archivecleanup /data /archivedir %r’

主库归档配置如下：

1. archive_mode:是否开启归档。
2. archive_command:执行归档的命令。
3. archive_timeout:如果主库在某段时间内比较闲，可能会在很长时间后才产生WAL日志文件，这会导致主库和Standby库之间有较大的延误，将此参数配置成一个整数，则会在表示的秒内强制数据库切换一个WAL日志文件。
   1. 1. Recovery Target配置

通常Standby的恢复是一直在进行之中的，若想让Standby恢复到一个指定的点后就暂停，需要配置：

1. recovery_target:取值为空或”immediate”，”immediate”表示Standby恢复到一个一致点时，就立即停止恢复。
2. recovery_target_name:在主库上可以创建一个恢复点（调用pg_create_restore_point()创建），然后让Standby恢复到这个点，此参数就是用来指定这个恢复点的名称的。
3. recovery_target_time:用于指定恢复到哪个时间点。
4. recovery_target_xid:用于指定恢复到哪个事物。
5. recovery_target_inclusive:指定恢复到恢复目标(recovery_target)之后还是之前。默认为之后，即true。
6. recovery_target_timeline:指定恢复的时间线。”latest”表示恢复到最近的时间线。
7. pause_at_recovery_target:指示到达恢复目标后，Standby数据库恢复是否暂停。默认为true。
   1. 1. Standby Server配置

1. standby_mode:是否运行在Standby模式下
2. primary_conninfo:在流复制中，指定如何连接主库，是一个标准的libpg连接串
3. primary_slot_name:指定复制槽（replication slot）
4. trigger_file:指定激活Standby的触发文件
5. recovery_min_apply_delay:让Standby落后主库一段时间。
   1. 流复制的注意事项
      1. wal_keep_segments参数的配置

此参数含义是无论如何，在主库上都要保留wal_keep_segments个WAL日志文件。默认为0，通常设置为64，表示将为Standby保留64个WAL日志文件。每个WAL日志文件16MB，所以会占用64*16=1GB空间。

1. 1. 1. vacuum_defer_clean_age参数的配置

在主库上，vacuum进程知道某些旧版本的数据会被当前数据中的查询使用，从而不清理这些数据，但对于Hot Standby上的查询，主库是不知道的，所以主库上的vacuum可能会把Hot Standby上的查询还需要的旧版本数据清理掉，这会导致Standby上的查询失败。为了降低Hot Standby因为这个原因失败的概率，可以设置此参数，让主库延迟清理。参数含义：延迟清理多少个事务。

1. 数据库安全
   1. PostgreSQL安全简介

PostgreSQL首先通过用户标识和认证验证访问数据库的用户身份，判断其是否为合法用户以及是否有访问数据库资源。然后通过基于角色的访问控制(rbac)，并使用存取控制列表(acl)方法控制访问请求和保护信息。

1. 用户标识和认证

PostgreSQL通过用户标识和认证为系统提供最外层的安全保护措施。

1. 用户角色管理

角色是权限的集合，管理员根据角色在系统中承担的职责分配具有不同权限的角色。用户权限的管理方式被简化成角色的管理。

1. 数据库对象的访问控制

当用户或者进程在对数据库对象进行任何操作时，都必须进行相应的权限检查。

-- 配置参数

-- 监听的IP，若支持远程连接，即在本地的所有地址上监听时，须改为*(重启生效)

#listen_addresses = 'localhost'

-- 监听的数据库端口(重启生效)

#port = 5432

-- 日志收集开关

#logging_collector = on

-- 日志目录(默认值pg_log)

#log_directory = 'pg_log'

-- 共享内存大小

#shared_buffers = 32MB

-- 单个SQL执行时，排序，hash join所使用的内存

#work_mem = 1MB