PG修炼之道笔记

作者: cnxz43 | 来源:发表于2020-03-03 11:30 被阅读0次

[TOC]

安装配置

2.1 发行版本安装

# 安装
sudo apt-get install postgresql  # Debian 或 Ubuntu
yum install postgresql-server.x86_64     # Redhat、 CentOS、Fedora

# redhat下安装后需要初始化
servicepostgresqlinitdb

# 数据库数据目录
/var/lib/postgresql/<dbversion>/main  # Debian 或 Ubuntu
/var/lib/pgsql/data # Redhat、 CentOS、Fedora

# 启停数据库
sudo service postgresql status
sudo service postgresql stop
sudo service postgresql start


su - postgres
psql

postgres=# \l    # list of databases
postgres=# \d    # 查看有哪些表
postgres=#  \q   # quit

2.2 从源码安装

----1.安装----

# 1. 下载源码

# 2. 编译安装

#  ./configure

#  make # gmake3.8以上

#  make install  # root权限

# 3. 编译安装后执行

# 1）使用initdb命令初始化数据库

# 2）启动数据库实例

# 3）创建一个应用使用数据库



tar xvf postgresql-9.2.4.tar.bz2
./configure --prefix=/usr/local/pgsql8.4.17 --enable-thread-safety --with-perl --with-python # PostgreSQL8.X
./configure --prefix=/usr/local/pgsql9.2.4 --with-perl --with-python # PostgreSQL9.X

# 可以通过 --with-blocksize=128 --with-wal-blocksize=128 --with-wal-segize=64 给数据块、WAL日志块、WAL日志文件指定大小

----2.配置---
#.porfile中

# 设置可执行文件路径

export PATH=/usr/local/pgsql/bin:$PATH

# 设置共享库路径

export LD_LIBRARY_PATH=/usr/local/pgsql/lib

# 设置数据库目录环境变量

export PGDATA=/home/osdba/pgdata

# 创建数据库簇

initdb

# 安装contrib目录下工具

cd postgresql-9.2.3/contrib
make
sudo make install

# 启动停止数据库

pg_ctl start -D $PGDATA

pg_ctl stop -D $PGDATA [-m SHUTDOWN-MODE] #SHUTDOWN-MODE:smart(等所有连接终止) fast（断开连接，已有事务回滚） immediate（立即关闭）

2.4 PostgreSQL简单配置

### postgresql.conf文件中 ###
#--- 1. 修改监听 IP 和 端口 ---

#listen_addresses = ‘localhost’
#post = 5432

# 修改这两个参数后需要重启才能生效

#--- 2. log相关 ---
# 日志收集
logging_collector = on

# 日志目录
log_directory = 'pg_log'

# 日志的切换和是否选择覆盖
## 方案1 每天生成一个日志文件
log_filename= ‘postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation= off
log_rotation_age = 1d
log_rotation_size = 0

## 方案2 每当日志写满一定大小，则切换
log_filename= ‘postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation= off
log_rotation_age = 0
log_rotation_size = 10M

## 方案3 只保留7天日志，循环覆盖 (?)
log_filename= ‘postgresql-%a.log'
log_truncate_on_rotation= on
log_rotation_age = 1d
log_rotation_size = 0

#--- 3.内存参数 ---
shared_buffers:共享内存大小，默认32MB

work_mem：单个SQL执行时，排序、hash join所使用的内存，SQL运行完释放 默认1MB

3 SQL语言入门

DDL - 定义语句（创建，删除，修改表、索引等）

1. 建表
CREATE TABLE table_name (
col01_name data_type,
col02_name data_type,
col03_name data_type,
col04_name data_type,
);

CREATE TABLE score(
no int primary key,     #主键
student_name varchar(40),
chinese_score int,
math_score int,
test_date date,
);

2. 删除表
DROP TABLE table_name;

DML - 操纵语句 INSERT UPDATE DELETE

1. 插入
INSERT INTO student VALUES(1,'zhangsan',14);

# 指定插入数据列
INSERT INTO student(no, student_name, age) VALUES(1,'zhangsan',14);

2. 更新

# 所有学生age更新为15
UPDATE student SET age = 15;

# 指定更新
UPDATE student SET age = 13， student_name='xiaowang' WHERE no=3;

3. 删除
# 删除整个表数据
DELETE FROM student;

# 删除某条记录
DELETE FROM student WHERE no = 3;

DQL - 查询语句 select

1. 单表查询
select no,student_name,age from student;

# 各列可以是表名，也可以是表达式
select age+5 from student;
select no, 3+5 from student;

# 表达式与表无关时，可以不使用FROM
select 55+88;
SELECT 10*2, 3*5+2;

# 查询所有列
select * from student;

2. 过滤条件查询 where
SELECT * FROM student where no=3;
SELECT * FROM student where age>=15;

3. 排序 order by
# order by 要在 where 语句之后
# 两个列排序， “DESC“ 倒排  
SELECT * FROM student WHERE age >=15 ORDER BY age DESC, student_name;  

4. 分组查询 GROUP BY
# 使用 GROUP BY时常用聚合函数 count，sum
SELECT age,count(*) FROM student BY age;
age | count
----+--------
 15 |     2
 13 |     1

5. 表join
# 多表关联查询
# 班级表（no, class_name）  学生表(no, student_name, age, class_no)
# 查询学生和班级关系
SELECT student_name, class_name FROM student, class WHERE student.class_no = class.no;

# 别名
SELECT student_name, class_name FROM student a, class b WHERE a.class_no = b.no AND a.age>14;

其他SQL语句

1. INSERT INTO ... SELECT
# 把数据从一张表插入另一张表
CREATE TABLE student_bak(no int primary key, student_name varchar(40), age int, class_no int);

INSERT INTO student_bak SELECT * FROM student;

2. UNION 语句
# 将两张表查询的结果整合在一个集合
# UNION会把结果集中相同的记录合并
# 不想合并使用 UNION ALL
SELECT * FROM student WHERE no=1 UNION SELECT * FROM student_bak WHERE no=2; 

3. TRUNCATE TABLE
# 清空表内容
TRUNCATE TABLE student_bak;

# TRUNCATE TABLE是DDL语句，相当于删除重建  速度更快
# DELETE 是DML语句，一条条删除

4 psql工具


# psql允许交互键入SQL或命令

# 进入命令行下，psql命令都以"\"开头
psql

# 查看有哪些库
psql -l
\l

# 查看表
\d

# 连接到数据库
CREATE DATABASE testdb;
\c testdb;
\c testdb user1;

# 常用psql连接数据库方法
psql -h <hostname or ip> -p <端口> [数据库名称] [用户名称]
# 连接参数也可以在环境变量中指定
export PGDATABASE=tesetdb
export PGHOST=192.168.56.11
export PGPORT=5432
export PGUSER=postgres

1. \d

1. \d
#列出当前数据库中所有表

2. \d 后面跟表名  
# 显示表的结构定义
\d table_name

3. \d 跟索引 
# 显示索引信息

4. \d 跟通配符 “*” “？”等
\d x?
\d t*

5. \d+  显示更详细的信息
例： \d+ table_name

6. 匹配不同对象类型的\d命令
- \dt  只显示表 
- \di  只显示索引
- \ds  只显示序列
- \dv  只显示视图
- \df  只显示函数

7. 显示SQL已执行时间  \timing
\timing on
select count(*) from t;

8. \dn  列出所有schema

9. \db  显示所有表空间

10. \du \dg  数据库所有角色或用户 
#\du \dg等价，因为PG不区分用户和角色

11. \dp 或 \z  显示表的权限分配情况
\dp table_name

2. 指定字符集编译

encoding utf8;

3. \pset

用于设置输出格式

\pset border 0: 输出内容无边框
\pset border 1: 边框只在内部
\pset border 2：内外都有边控

4. \x

可以把表中每一行的每列数据都拆分成单行展示

5. `\i <文件名>` 执行存储在外部文件的sql语句或命令

也可用 psql -f <filename>

6. `\echo` 显示信息

\echo hello world

7. `\?` 更多命令

\?来查看更多命令

8. 使用技巧

连按两个tab 补全或提示

psql中事务是自动提交的，不想自动提交

可是使用 begin； dml命令； commit/rollback;

osdba# begin;
osdba# update ...;
osdba# select ...;
osdba# rollback;

关闭自动提交

\set AUTOCOMMIT off   # AUTOCOMMIT必须使用大写，小写不报错但不生效

得到psql命令实际执行SQL
- 启动psql时加 -E 参数，打印各种 \ 开头命令
```
psql -E postgres
```
- 在已运行的psql中显示某命令实际执行SQL
```
# 进入psql
psql

# 打开显示
\set ECHO_HIDDEN on

# 执行指令
\指令

# 关闭显示
\set ECHO_HIDDEN off
```

5 数据结构

布尔类型
数值类型
字符串类型
二进制数据类型
位串类型
日期\时间类型
枚举类型
几何类型
网络地址类型
复合类型
XML类型
JSON类型
Range类型
数据类型
伪类型
UUID 、pg_lsn类型

6 逻辑结构管理

1. 数据库逻辑结构

数据库：一个PostgreSQL服务下可以管理多个库
表、索引：PostgreSQL中表的术语为“Relation”，其他库为“Table”
数据行：PostgreSQL中为“Tuple”，其他库为“Row”

PostgreSQL中一个数据库服务（实例）下可以有多个库，一个库不能属于多个实例

Oracle中一个实例只能有一个数据库，一个数据库可以在多个实例中（RAC）

2. 数据库基本操作 - 创建、修改、删除库

创建数据库

CREATE DATABASE name
[ [WITH] [ OWNER [=] user_name ]   # 指定新建数据库属于哪个用户，不指定数据当前执行用户
    [ TEMPLATE [=] template ]   # 模板名（从哪个模板创建新数据库），不指定使用默认模板template1
    [ ENCODING [=] encoding ]   # 字符编码，编码和区域设置必须和模板匹配
    [ LC_COLLATE [=] lc_collate ]  
    [ LC_CTYPE [=] lc_ctype ]
    [ TABLESPACE [=] tablespace ]  # 指定关联表空间名称
    [ CONNECTION [=] connlimit ] ] # 接受并发连接数
    
    #PostgreSQL服务端不支持GBK GB18030，一般使用 UTF8 支持中文

修改数据库

ALTER DATABASE name [ [ WITH ] option [...] ]

alter database testdb1 CONNECTION LIMIT 10;
alter database testdb1 RENAME TO new_name;
alter database testdb1 OWNER TO new_owner;
alter database testdb1 SET TABLESPACE new_tablespace;

删除数据库

DROP DATABASE [ IF EXISTS ] name;

3. 模式（schema）

可以理解为一个命名空间或目录。不同模式下可以有相同名称的表、函数等对象且相互不冲突。

只要有权限，每个模式的对象可以相互调用

允许多个用户在使用同一个数据库时彼此互不干扰
把数据库对象放在不同的模式下，然后组织成逻辑组，让他们更便于管理
把第三方的应用可以放在不同的模式中，这样就不会和其他对象的名字冲突了

CREATE SCHEMA shemaname [ AUTHORIZATION username ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ]

# 创建一个名为 osdba 的模式
osdba=# create schema osdba;

# 为用户 osdba 创建一个名为 osdba 的模式
create schema authorization osdba;

# 查看有哪些模式
\dn

# 删除一个模式
drop schema osdba;

# 修改名称和属主
ALTER SCHEMA name RENAME TO newname
ALTER SCHEMA name OWNER TO newowner

创建和访问模式中的对象

需要先写出一个受修饰的名字

schema_name.table_name

通常情况下都不指定模式，实际访问的都是 “public” 模式

模式的搜索路径

SHOW search_path;

模式权限

USAGE # 访问权限
CREATE # 创建对象权限

# public是模式名 PUBLIC是"所有用户"， 收回后其他用户不能再public下创建对象
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

模式移植性

给每个用户创建同名模式，实现oracleusername.tablename
SQL标准里没有public模式概念，与其他库兼容应不使用或删除public模式

4. 表

创建表

# 创建表
CREATE TABLE table_name (
col01_name data_type primary key,
col02_name data_type,
col03_name data_type,
col04_name data_type,
);

# 复合主键
# CONSTRAINT cocnstraint_name PRIMARY KEY (col1_name. col2_name, ...)
CREATE TABLE table_name (
    col01_name data_type ,
    col02_name data_type,
    col03_name data_type,
    col04_name data_type,
    CONSTRAINT cocnstraint_name PRIMARY KEY (col1_name. col2_name, ...));

# 唯一键
# CONSTRAINT cocnstraint_name UNIQUE(col1_name. col2_name, ...)
create table test03(id1 int, id2 int, id3 int, note varchar(20), CONSTRAINT pk_test03 primary key(id1,id2), CONSTRAINT uk_test03_id3 UNIQUE(id3));

# check
# CONSTRAINT cocnstraint_name CHECK(expression)
create table child(name varchar(20), age int, note text, CONSTRAINT ck_child_age CHECK(age<18));

# 用其他模板创建新表
CREATE TABLE baby (LIKE child);  # 不复制约束
# 使用INCLUDING复制其他信息
- INCLUDING DEFAULTS
- INCLUDING CONSTRAINTS
- INCLUDING INDEXES
- INCLUDING STORAGE
- INCLUDING COMMENTS
- INCLUDING ALL
CREATE TABLE baby (LIKE child INCLUDING ALL);
#也可使用 CREATE TABLE ... AS

表的存储属性

TOAST技术

（The Oversized-Attribute Storage Technique），主要用于存储一个大字段的值。
由于PostgreSQL页面大小固定（通常8K），并且不允许跨页存储，不能直接存储非常大的字段，大字段通常被压缩或者切片成多个物理行存到另一张系统表（TOAST表）中
只有特定数据类型支持TOAST
每个字段有四种TOAST策略
- PLAIN: 避免压缩或线外存储。对不不能TOAST的数据类型可选择这个策略。
- EXTENDED：允许压缩或线外存储，大多数可以TOAST数据类型的默认策略
- EXTERNAL：允许行外存储，不允许压缩
- MAIN：允许压缩，但不允许行外存储

# 改变字段默认的TOAST策略
# ALTER TABLE SET STORAGE
  
create table blog (id int, title text, content text);
ALTER TABLE blog ALTER content SET STORAGE EXTERNAL;

临时表

会话级：保存在整个会话生命周期中
事务级：只存在这个事实的生命周期中

# 默认情况下，创建的临时表是会话级的
create TEMPORARY table tmp_t1(id int primary key, note text);
create TEMP table tmp_t1(id int primary key, note text);

# 创建事务级临时表
# ON COMMIT DELETE ROWS
create TEMPORARY tabletmp_t2(id int primary key, note text) on commit delete rows;
begin;
insert into ...
insert into ...
select into ...
end;

# ON COMMIT 子句三种形式
- ON COMMIT PRESERVE ROWS:若不带on commit 子名，默认情况下，数据会一直存在整个会话周期
- ON COMMIT DELETE ROWS：事务一提交，数据消失
- ON COMMIT DROP：事务一提交，临时表消失。这种情况下，创建临时表和插入数据语句需放在一个事务中

# 为了和其他数据库兼容还设置了 GLOBAL 和 LOCAL 关键字，但没有任何用处
create TEMPORARY table tmp_t1(id int primary key, note text);
create GLOBAL TEMPORARY table tmp_t1(id int primary key, note text);
create LOCAL TEMPORARY table tmp_t1(id int primary key, note text);

默认值

# 默认值可以是值或者表达式
create table student(no int, name varchar(20), age int default 15);
create table blog(id int, title text, created_date timestamp default now())

update student set age=DEFAULT where no=2;

# 没有声明默认值，默认为NULL

约束

检查约束

create table persons (
    name varchar(40),
    age int CHECK (age >=0 and age <= 150),
    sex boolean
);

# 给约束命名
create table persons (
    name varchar(40),
    age int CONSTRAINT check_age CHECK (age >=0 and age <= 150),
    sex boolean
);

# 一个检查约束引用多个字段
create table books(
    book_no integer,
    name text,
    price numeric CHECK (price>0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);


create table books(
    book_no integer,
    name text,
    price numeric ,
    discounted_price numeric,
    CHECK (price>0),
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

create table books(
    book_no integer,
    name text,
    price numeric ,
    discounted_price numeric,
    CHECK (price > 0 and discounted_price > 0 and price > discounted_price)
);

create table books(
    book_no integer,
    name text,
    price numeric ,
    discounted_price numeric,
    CHECK (price>0),
    CHECK (discounted_price > 0),
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

非空约束

# NOT NULL

create table books (
    book_no int not null,
    name text,
    price numeric
);


# 一个字段多个约束
create table books(
    book_no integer not null,
    name text,
    price numeric not null CHECK (price>0),
);

唯一约束

# UNIQE

# 字段约束
create table books (
    book_no int UNIQUE,
    name text,
    price numeric
);

# 表约束
create table books (
    book_no int,
    name text,
    price numeric,
    UNIQUE(book_no)
);

外键

# REFERENCES
# 表和表之间关系的一种约束
create table class(
    class_no int primary key,
    class_name varchar(40)
);

create table student(
    student_no int primary key,
    student_name varchar(40),
    age int,
    class_no int references class(class_no)    # student表中class_no的取值必须出现在表class中，且为class_no的一个数值
);

主键 - primary key

修改表

# 1.增加字段 - ADD COLUMN
alter table class ADD COLUMN class_teacher varchar(40);

# 2. 删除字段 - drop column
alter table class drop column class_teacher; 
## 删除时，和字段相关的约束也会被删除，如该字段被另一个表的外键所引用删除时会报错
## 删除外键依赖使用 cascade
alter table class drop column class_no CASCADE;

# 3.增加约束 - add check
## 增加前的表必须符合约束条件
alter table student add check (age < 16);
alter table class add unique(class_teacher);
alter table student alter column student_name set not null;

# 4. 删除约束 - drop constraint
alter table student drop constraint constraint_name;

## 查看约束名称
\d student

## 删除非空约束
alter table student alter column student_name drop not null;

# 5.修改默认值
alter table student alter column age set default 15;

# 6. 删除默认值
alter table student alter column age drio default;

# 7. 修改字段数据类型
## 现有的每项都可隐式转换成新类型
alter table student alter column student_name type text;

# 8. 重命名字段 - rename column
alter table books rename column book_no to book_id;

# 9. 重命名表 - rename to
alter table class rename to classes;

表继承

# inherits
create table persons (
    name text,
    sex boolbean,
    age int
);

create table students(
    class_no int
)inherits(persons);

查询父表时会把子表数据也查询出来，查询子表时不会查到父表新增的数据
只想查询父表本身数据，使用 ONLY关键字

select * from only persons;

所有父表的检查约束，非空约束会自动被子表继承，唯一、主键、外键约束不会
一个子表可以从多个父表继承
- 将拥有多有父表字段总和
- 重复字段名会融合，数据类型相同完成融合，不同报错
- 融合字段拥有父字段的所有检查约束
select,update,delete访问和操作父表时，会同时访问或操作相应的子表
alter table修改附表结构定义时，大多是情况下会同时修改子表结构定义
reindex,vacuum命令不会影响子表
唯一约束、外键的作用域不会扩大到子表

分区表

表大小超过了数据库服务器的物理内存大小时，应该使用分区
PostgreSQL分区表通过表继承来实现

使用继承实现分区表示，一般让父表为空，数据存储在子表中

通过触发器定位到相应分区

优化方法：打开约束排除（constraint_exclusion），比较子表check条件，排除部分子表

5.触发器 trigger

由事务自动触发执行的特殊存储过程

经常用于加强数据的完整性约束和业务规则上的约束

# 1. 创建
create [ constraint ] trigger name { defore | after | instead of } { ecent [or ...] } 
    on table_name
    [ FROM referenced_table ]
    { not deferrable | [deferrable ] { initially immediate | initially deferred }}
    [ for [ each ] { row | statement } ]
    [ when ( condition ) ]
    execute procedure function_name ( arguments)

# 2. 删除
drop trigger [ if exists] name on table [ cascade | restrict ];
## if exists: 指定触发器不存在发出notice不报错
## cascade：级联删除依赖此触发器的对象
## restrict：默认值，存在依赖拒绝删除

# 返回NULl

6. 事件触发器

#创建
CREATE EVENT TRIGGER name
    ON event
    [WHEN filter_variable IN (filter_value [, ...]) [ AND ...] ]
    EXECUTE PROCEDURE function_name()
    
# 返回 enent_trigger

create event trigger abort_DDL on DDL_command_start
    execute procedure abort_any_command();
    
alter event trigger abort_ddl disable;

# PostgreSQL9.3中，事件触发器仅仅支持TG_EVENT,TG_TAG变量
## TG_EVENT：ddl_command_start | ddl_command_end | sql_drop
## TG_TAG:指具体DDL操作，如 create table， drop table


# 修改事件触发器
alter event trigger name disable;
alter event trigger name enable [replica | always]
alter event trigger name owner to new_owner
alter event trigger name rename to new_name

7.表空间

将不同的表放在不同的存储介质或文件系统时使用

PG中表空间实际上是为表指定一个存储目录

# 1. 创建
CREATE TABLESPACE tablespace_name [ owner user_name ] LOCATION 'directory'

create tablespace tbs_data loation '/data/pgdata';

## 创建数据库时指定默认空间
create database db01 tablespace tbs_data;
## 改变数据库默认表空间
### 改变默认表空间，数据库中已有表的表空间不会改变
alter database db01 set tabelspace tbs_data;  # 操作时，必须没有人同时连接在数据库上

## 创建表时指定表空间
create table test01(id int, note text) tablespace tbs_data;

## 创建索引时指定表空间
create index idx_test01_id on test01(id) tablespace tbs_data;

## 把表从一个表空间移动到另一个  
### 移动时会锁表
alter table test01 set tablespace pg_default;

8.视图

视图是由查询语句定义的虚拟表

视图的数据可能来自多张表，也可能来自外部

使用视图的原因：

可使复杂的查询易于理解和使用
安全，视图可以隐藏表中的一些数据
把一些函数返回的结果映射成视图

一般数据库提供的视图大多为只读，PG中默认为只读。可以使用规则系统做出可更新的视图。

# 创建视图
CREATE [ OR REPLACE ] [TEMP | TEMPORARY ] VIEW name [ (column_name [,...] ) ]
    AS query

CREATE TABLE users(
    id int,
    user_name varchar(40),
    password varchar(256).
    user_email text, 
    user_mark text
    );
CREATE VIEW vm_users AS SELECT id ,user_name, user_email, user_mark FROM users;

## 为查询的各列定义另一个名称
CREATE VIEW vm_users(no, name, email, mark) AS SELECT id ,user_name, user_email, user_mark FROM users;

# 可更新视图
## 定义规则
CREATE RULE vm_users_upd AS 
    ON UPDATE TO vm_users DO INSTEAD UPDATE users SET user_email = NEW.user_email;
## 通过 INSTEAD OF 触发器

9.索引

增加数据库存储空间
插入和修改数据时花费更多时间

索引分类

B-tree：常用索引，适合等值查询和范围查询
Hash：只能处理简单等值查询
GiST：一种架构（有很多种实现策略）
SP-GiST：空间分区GiST索引
GIN：反转索引

# 创建索引
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table_name [ USING method ] 
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ]
    [ NULLS { FIRST | LAST } ] [,... ] )
    [ WITH ( storage_parameter = value [,... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]
   
    
    
create table contacts(
    id int primary key,
    name varchar(40),
    phone varchar(32)[],
    addresss text
);

## 按姓名，建立B-tree索引
create index idx_contacts_name on contacts(name);

## 按电话号码建立gin索引
create index idx_contacts_phone on contacts using gin(phone);
select * from contacts where phone @> array['13422334455'::varchar(32)]

## 指定存储参数 with(storage_parameter = value)
create index idx_contacts_name on contacts(name) with (FILLFACTOR=50);

## 指定空值排在前面
create index idx_contacts_name on contacts(name DESC NULLS FIRST);
## 指定空值排在后面
create index idx_contacts_name on contacts(name DESC NULLS LAST);


## 并发索引 CONCURRENTLY
create index concurrently idx_testtab01_note on testtab01(note);

# 删除索引
DROP INDEX [ IF EXISTS ] name [,...] [CASADE | RESTRICT]  # 默认 restrict
drop index idx_name;

# 修改索引
alter index name RENAME TO new_name;
alter index name SET TABLESPACE tablespace_name;
alter index name SET ( storage_parameter = value [, ... ])
alter index name RESET ( storage_parameter = value [, ... ])

10. 用户及权限管理

PG中用户和角色没有区别
用户和角色在整个数据库实例中是全局的，不同数据库看到的也是相同的
默认超级用户和操作系统用户名相同

# 创建
CREATE ROLE NAME [ [ WITH ] option [...] ]
CREATE USER NAME [ [ WITH ] option [...] ]
# create user 默认有 login权限
# create role 默认没有login权限

option内容：

SUPERUSER | NOSUPERUSER
CREATEDB | NOCREATEDB
CREATEROLE | NOCREATEROLE
CREATEUSER | NOCREATEUSER
INHERIT | NOINHERIT ： INHERIT -创建的用户自动拥有其所拥有的角色的权限
LOGIN | NOLOGIN
CONNECTION LIMIT connlimit：可使用并发连接数
[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'：存储在系统表中的口令是否加密
VALID UNTIL ‘timestamp'：密码失效时间，不指定永远有效
IN ROLE role_name[,...]：指定成哪些角色的成员
IN GROUP role_name[,...]：与 in role相同，过时语法
ROLE role_name[,...]：role name 成为当前创建角色的成员
ADMIN role_name[,...]：role name将有这个新建角色的 with admin option权限
USER role_name[,...]：与role子句相同，过时语法
SYSID uid：为了SQL向下兼容

# 修改权限

## 1. 超级管理员，创建数据库，LOGIN权限
ALTER ROLE name [ [ WITH ] option [ ...] ]

## 2. 其他权限  GRANT REVOKE
GRANT some_privileges ON database_object_type object_name TO role_name;

some_privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY|TEMP, USAGE, ALL PRIVILEGES(一次性给予所有可以赋予的权限)

grant delete on table table_name to role_name;
grant select on all table in schema schema_nae to role_name;

权限管理层次：

1、附在用户特殊属性上的权限：超级管理员，创建数据库，创建用户，LOGIN权限

2、创建模式的权限

3、在模式中创建数据库对象的权限：创建表、索引等

4、查询表，插入数据，更新表，删除表中数据

5、操作表中某些字段

# 创建一个只读用户
## pg默认任何用户可以在public schema中创建表

# 1. 收回权限
revoke create on schema public from public;

# 2. 创建一个名为readonly的用户
create user readonly with password 'query'；

# 3. 将现有所有表的select权限赋予用户 readonly
grant select on all tables in schema public to readonly;

# 4. 将新建表的select权限赋予readonly
alter default privileges in schema public grant select on tables to readonly;

# 5. 赋予readonly访问其他schema下表的权限,重复执行如下语句：
grant select on all tables in schema other_schema to readonly;
alter default privileges in schema other_schema grant select on tables to readonly;

11. 事务，并发，锁

ACID

原子性atomicity
一致性consistency
隔离性isolation
持久性durability

锁

# 查看锁
## 通过 pg_locks 视图查看

psql postgres

# 查看当前窗口连接到的服务进程pid
postgres=# select pg_backend_pid()

select locktype,
       relation::regclass as rel,
       virtualxid as vxid,
       transactionid as xid,
       virtualtranscation as vxid2,
       pid,
       mode,
       granted  # granted为f说明被阻塞
   from pg_locks
   where pid = 8127;

7. PG核心架构

进程结构

主进程：Postmaster
- 查询 pg_stat_activity表查看服务进程（子进程）
辅助进程
- SysLogger：系统日志进程
- BgWriter：后台写进程
- WaLWriter：预写式日志进程
- PgArch：归档进程
- AutoVAcuum：系统自动清理进程
- PgStat：统计收集进程

目录结构

/usr/local/pgsql     # 安装根目录
    |
    |--- bin   # 二进制可执行文件目录  下面有 postgres psql 等可执行程序
    |
    |--- include  # 头文件目录
    |
    |--- lib  # 动态库目录， PostgreSQL程序运行需要的动态库都在此目录下，如libpq.so
    |
    |--- share  # 此目录放有文档和配置模板文件，一些扩展包sql文件在此目录的子目录extension下

$PGDATA
    |
    |--- postgresql.conf  # 数据库实例的主配置文件
    |
    |--- pg_hba.conf  # 认证配置文件
    |
    |--- pg_ident.conf  # ident认证方式的用户映射文件
    |
    |--- base  # 默认表空间目录
    |
    |--- global  # 共享系统表目录
    |
    |--- pg_clog  # commit log 的目录
    |
    |--- pg_log  # 系统日志目录， 查看一些系统错误时查看此目录下日志文件
    |
    |--- pg_stat_tmp  # 统计信息的存储目录
    |
    |--- pg_tblsp  # 存储指向各个用户自建表空间实际目录的链接文件
    |
    |--- pg_twophase  # 使用两阶段提交功能时分布式事务的存储目录
    |
    |--- pg_xlog  # WAL日志目录

8. 服务管理

1. 服务启停和创建

启停

# 启停方法
## 启动
postgres -D /home/osdba/pgdata   # -D datapath  指定数据目录
pg_ctl -D /home/osdba/pgdata start
## 停止
### 向主进程发送signal信号：SIGTERM， SIGINT， SIGQUIT，对应smart， fast， 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

pg_ctl

# 初始化PostgreSQL数据库实例
pg_ctl init[db] [-s] [-D datadir] [-o options]
# -s 只打印错误和告警，不打印提示性信息
# -D datadir 指定数据目录
# -o option 直接传递给initdb命令的参数

# 启动数据库
pg_ctl start [-w] [-t seconds] [-s] [-D datadir] [-l filename] [-o options] [-p path] [-c]
# -w 等待启动完成
# -t 等待启动完成的等待秒数，默认60秒
# -s 只打印错误和告警，不打印提示性信息
# -D datadir 指定数据目录
# -l filename 服务器日志输出附加在filename文件上，不存在该文件则创建
# -o option 直接传递给initdb命令的参数
# -p path 指定postgres可执行文件位置，默认与pg_ctl相同目录
# -c 提高服务器软限制（ulimit -c），尝试允许数据库实例在有异常时产生一个coredump文件，以便于问题定位和故障分析

# 停止数据库实例
pg_ctl stop [-W] [-t seconds] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate]]
# -W 不等待数据库停下来，命令就返回
# -m 指定停止模式

# 重启数据库
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_hba.conf 后
pg_ctl reload -D /home/osdba/pgdata


# 查询实例状态
pg_ctl status [-D datadir]

# 给指定进程发信号 ,   windows下比较有用 
pg_ctl kill [signal_name] [process_id]

单用户模式

postgres --single -D /home/osdba/pgdata postgres
# postgres进程不进入后台服务模式，进入一个交互的命令行模式

使用场景：

多用户模式不接受所有命令时，可以使用单用户连接到数据库

initdb的阶段

修复系统表

2. 服务配置

参数

# 在postgresql.conf中使用 include 指令包含配置内容
include 'filename'

# 查询参数取值 系统视图pg_settings
select enumvals from pg_settings where name = 'client_min_messages';
select unit from pg_settings where name = 'autovacuum_vacuum_cost_delay';

# 参数分类 对应pg_settigns中context列
- internal：初始化时确定，之后不能修改
- postmaster：修改需要重启实例
- signup：postgresql.conf中修改，修改后需要附送signup信号（reload），修改后所有进程生效
- backend：postgresql.conf中修改，修改后需要附送signup信号（reload），修改后新的连接生效，已有连接中不变
- superuser：超级用户通过set改变，只影响自身session，只影响后续新的连接
- user：普通用户通过set改变，只影响自身session，只影响后续新的连接

慢查日志功能

log_min_duration_statement参数控制:

大于或等于这个毫秒数的SQL和它的运行时间会被记录到日志中，设置为0时记录所有SQL时间

log_statement：

控制是否记录DDL，DML或所有SQL

备份

逻辑备份
- pg_dump
- pg_restore
物理备份
- 冷备：拷贝 PGDATA路径
- 热备：PITR | 文件系统或块设备快照（LVM）

常用管理命令

# 查看数据库实例版本
select version();

# 查看数据库启动时间
select pg_postmaster_start_time();

# 查看load配置文件时间
select pg_conf_load_time();
# 改变配置装载时间
pg_ctl reload

# 查看当前数据库时区
show timezone;

# 查看当前用户
select user;
select current_user;

# 查看session用户
select session_user

# 查询当前连接的数据库名称
select current_catalog, current_databse();

# 查询当前session所在客户端的IP地址及端口
select inet_client_addr(), inet_client_port();

# 查询当前数据库服务器的IP地址及端口
select inet_server_addr(), inet_server_port();

# 查询当前session的后台服务进程pid
select pg_backend_pid();

# 查询当前参数配置
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_insert_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'));  # 表很多时，可能影响数据库性能
# 查看表大小
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_relation_filepath('test01')



# 生效postgresql.conf文件
pg_ctl reload
select pg_reload_conf()

# 切换log文件
select pg_rotate_logfile();

# 切换WAL文件
select pg_switch_xlog();

# 手工产生一次checkpoint
checkpoint


# 取消一个正在执行的SQL
pg_cancel_backend(pid)   # 配置一个取消标志，检测到时取消
pg_terminate_backend(pid)  # 如果没有主动监测标志，使用此命令中止SQL
# 找出长时间运行SQL
select pid, usename, query_start, query from pg_stat_activity;
# 使用pg_cancel_backend取消
select pg_cancel_backend(pid);