pg给我背

基本概念

名称	作用
关系	表和索引
元组和域	元组就是表中的一行记录，域就是一行记录的不同列，元组由一个至多个域组成
数据库和数据库簇	数据库簇就是单个pg服务器下的所有数据库的集合
表空间	存放数据库对象的一个位置
数据段和数据页	每个表和索引都保存在一个单独的文件中，每个文件就是一个数据段（太大会拆分），一个数据段由多个数据页组成

存储体系结构

表空间的存储结构

使用psql

作用	指令	参数
连接数据库	psql -h *** -p *** -u *** -d ***	h(ip) p(端口) u(用户名) d(数据库名称)
断开数据库	\q
查看当前数据库的表，视图等	\d	\d table name(查看表的定义)
查看表空间的信息	\db
查看所有模式的信息	\dn
查看数据库中的角色,用户	\du

创建表空间

语法

--创建表空间

CREATE TABLESPACE  tablespace_name
[OWNER {new_owner|CURRENT_USER|SESSION_USER}]
LOCATION 'directory'
[WITH(tablespace_option=value,...)] 

--修改表空间

ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO {new_owner|CURRENT_USER|SESSION_USER}
ALTER TABLESPACE name SET (tablespace_option=value,...)

--删除表空间
只有表空间为空才能删除

DROP TABLESPACE[IF EXISTS] name

创建数据库

CREATE DATABASE name
[[with][options=optionvalues]

]

with参数说明

option	作用
owner	指定db所属user
template	创建数据库使用的模板库，默认为template1
encoding	创建数据库使用的编码集，默认为template1的
allowconn	是否可以连接该数据库，默认为true
connlimit	允许并发连接数据库的个数，默认-1，没有限制
istemplate	是否为模板库，默认false，只有superuser和该数据库用户可以复制；设置为true后任何具有createdb权限的用户都可以复制该db
tablespace_name	创建数据库关联的表空间，默认和template1一样

用户，角色，权限

对于指定表的权限

权限	说明
SELECT	对指定的表，视图或者序列的任何列进行设置selsect权限
INSERT	允许插入一行到指定表中，如果指定了插入的列，其他列使用默认值
UPDATE	对指定表，视图或者序列及特定的列设置update权限（需要select）权限
DELETE	允许删除指定表中的一行（需要select权限）
TRUNCATE	允许在指定表上清空
REFERENCES	允许创建指定表或指定列的外键约束
TRIGGER	允许在指定表上创建触发器

非表级别的

权限	说明
CREATE	1.对于数据库来说，允许创建新模式 2.对于模式来说，允许创建新对象 3.对于表空间来说，允许创建表，索引，临时文件
CONNECT	允许用户连接到指定的数据库
TEMPORARY	允许使用指定的数据库创建临时表
EXECUTE	允许使用指定的函数
USAGE	对于模式来说，允许访问指定模式下的对象

创建用户并且设置登录权限

CREATE USER username WITH LOGIN

设置密码

ALTER USER username WITH PASSWORD '****'

删除用户

DROP USER user

创建角色

CREATE ROLE  rolename [[WITH] options]
options: 
        - 登录权限 LOGIN
        - 超级用户权限 SUPERUSER
        - 创建数据库权限 CREATEDB
        - 创建角色权限 CREATEROLE
        - 发起复制流权限 REPLICATION(必须具备login权限)
        - 密码权限 PASSWORD

删除角色
1. REASSIGN OWNED BY current_role TO other_role 所拥有的对象转移
2. DROP OWNED BY current_role 取消对所拥有的对象的使用权
3. DROP ROLE current_role 删除该对象

管理数据库模式

创建模式
CREATE SCHEMA schemaname [SUTHORIZATION user_name(为指定用户创建模式)]

操作数据库对象时未指定模式将放到 ‘public’ 模式中

删除模式

DROP SCHEMA schemaname [CASCADE(删除非空的模式)]

默认权限

每个用户都有在public模式下的CREATE权限和USAGE权限

撤销权限

REVOKE CREATE ON SCHEMA schemaname FROM PUBLIC(指的是所有用户)

pg_catelog模式

包含数据库系统的所有内置对象，系统表，内置数据类型，函数等

postgresql.conf文件参数配置

连接和安全认证参数

参数名	作用	默认值	建议
listen_addresses	指定服务器监听客户端的tcp/ip 地址 1.支持监听多个ip地址，用逗号隔开 2.设置为'*'或者'0.0.0.0' 监听本机的所有ip地址 3.如果监听字为空，只有unix域套接字可以连接 4. 连接管理通过 pg_hba.conf文件来控制
port	监听tcp端口号	5432
max_connections	数据库最大连接数	100

缓冲和存储参数（调优 重要）

参数名	作用	默认值	建议
shared_buffers	共享内存缓冲区的数量		系统内存的25%-40%
temp_buffers	每个数据库会话使用临时缓冲区的最大空间	8MB
work_mem	工作内存，负责内部的sort和hash操作
maintenance_work_mem	维护工作内存，主要针对数据库维护操作（vacuum,create index等）		比work_mem 大得多
autovacuum_work_mem	自动清理工作进程所使用的最大内存	-1（表示使用maintenance_work_mem）
temp_file_limit	声明一个会话能使用的临时文件的最大硬盘空间	-1（没有限制）
huge_pages

wal日志参数