Simple PostgreSQL: Up and Running

发表于

配置文件

  1. postgresql.conf: 包含通用设置,比如内存分配,新建database的默认存储位置,服务器IP地址,日志位置以及其他基本配置.
  2. pg_hba.conf: 用于控制访问安全性,管理客户端对PostgreSQL服务器的访问权限,包括: 允许那些用户连接到哪个数据库,允许那些IP访问服务器,以及连接时使用的身份验证模式.
  3. pg_ident.conf: pg_hba.conf的控制权限信息中的身份验证模式字段如果指定为ident模式,则用户连接时,系统会尝试访问pg_ident.conf文件,如果该文件存在,则系统会基于文件内容将当前执行登录操作的操作系统用户映射为一个PostgreSQL数据库内部用户的身份来登录.

postgresql.conf

  1. listen_address: 当前服务使用的IP地址,一般为localhost,local,或 *.
  2. port: 服务侦听的端口号,默认5432.
  3. max_connections: 系统允许的最大并发连接数.
  4. shared_buffers: 此设置定义了用户缓存最近访问过的数据页的内存区大小,所有用户会话均可共享此缓存区.一般越大越好,最少达到系统内存的25%,但不宜超过8GB,超过后将出现边际收益递减效应.
  5. effective_cache_size: 此设置表示一个查询执行过程中可以使用的最大缓存,包括操作系统使用的部分和数据库使用的部分.一般设置为系统内存的一半或更多.
  6. work_mem: 此设置指定了用于执行排序,哈希关联,表扫描等操作的最大内存量.
  7. maintenance_work_mem: 此设置指定可用于vaccum操作这类系统内部维护操作的内存总量,不应大于1GB.

如果由于系统异常关闭而遗留的postmaster.pid文件,可以手动删除然后重启服务.

pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD 
# IPv4 local connections: 
host all all 127.0.0.1/32 ident ➊ 
# IPv6 local connections: 
host all all ::1/128 ➋ trust 
host all all 192.168.54.0/24 ➌ md5 
hostssl ➍ all all 0.0.0.0/0 md5 
# Allow replication connections from localhost, by a user with the ➎ 
# replication privilege. 
#host replication postgres 127.0.0.1/32 trust 
#host replication postgres ::1/128 trust
  1. 身份验证模式,常用的选项有: ident,md5,trust,password.
  2. 用于定义网络范围的IPV6语法.
  3. 用于定义网络范围的IPV4语法.上面的例子表示: 任何一个192.168.54.0子网中的客户端来说,只要提供经md5算法加密的密码是正确的,则允许连接服务.
  4. 针对SSL连接的规则.本例中,任何使用SSL连接并提供合法md5密码的客户端都允许连接服务.
  5. 此处是一套复制系统中其他成员节点的IP列表,每个成员的地址都必须存在于此列表中,否则无法加入该复制系统.

修改后使用命令进行重新加载:

service postgresql-9.3 reload

连接管理

取消正在运行的查询并终止连接:

  1. 检查活动连接列表和进程ID:

    SELECT * FROM pg_stat_activity;

  2. 取消连接上的活动查询:

    SELECT pg_cancel_backend(procid);

  3. 终止该连接:

    SELECT pg_terminate_backend( procid );

或者根据用户进行批量管理:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = ' some_role ';

角色管理

PostgreSQL安装过程中会创建一个名为postgres的角色,同时创建一个同名的database.不可将postgres账户提升为操作系统管理员账户.

创建具备登录权限的角色:

CREATE ROLE leo LOGIN PASSWORD 'king' CREATEDB VALID UNTIL 'infinity';

VALID为可选,指权限有效期,默认的infinity为永不过期.CREATEDB表示该角色可以创建数据库.

创建具备超级用户权限的角色:

CREATE ROLE regina LOGIN PASSWORD 'queen' SUPERUSER VALID UNTIL '2020-1-1 00:00';

创建数据库

CREATE DATABASE mydb ;

创建数据库时,PostgreSQL默认提供了两个数据库模板,template0和template1,未指定时则默认使用template1.

基于模板创建数据库:

CREATE DATABASE my_db TEMPLATE my_template_db ;

schema用于对database中的对象进行逻辑分组管理.如果创建很多database则管理起来很麻烦,可以通过在一个database中创建不同的schema将数据分组.schema不能重名,不同schema种的对象可以重名.

扩展包管理

查询已安装的扩展包:

SELECT name, default_version, installed_version, left(comment,30) As comment FROM pg_available_extensions WHERE installed_version IS NOT NULL ORDER BY name;

安装扩展包:

  1. 将安装包安装到数据库服务器.下载安装包的安装文件以及依赖文件,然后分别将他们复制到操作系统的lib和bin下,同时把SQL脚本文件复制到share/extension文件夹.或者直接通过yum进行安装.查看服务器上已有的扩展包:

    SELECT * FROM pg_available_extensions;

  2. 将扩展包安装到数据库中(<9.1).

  3. 将扩展包安装到数据库中(>9.1).

    安装fuzzystrmatch扩展包:

    CREATE EXTENSION fuzzystrmatch;

    或者通过schema安装扩展包,以确保安装包数据与业务数据隔离.建好schema后,通过命令将其指定给待安装的扩展包:

    CREATE EXTENSION fuzzystrmatch SCHEMA my_extensions;

比如常用的添加HSTORE支持,首先进入需要添加扩展的数据库:

create extension hstore

当然一般需要hstore的时候尽量使用jsonb.

备份与恢复

pg_dump用于备份指定的database,pg_dumpall用于备份所有database以及系统全局数据.

备份一个database,备份结果以自定义压缩格式输出:

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb .backup mydb

备份结果以SQL文本输出,并且包含CREATE DATABASE语句:

pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb .backup mydb

备份所有以”pay”开头的表:

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *.pay* -f pay .backup myd

备份hr和payroll两个schema中的所有数据:

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -n hr -n payroll -f hr .back- up mydb

备份除了public schema意外的所有数据:

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -N public -f all_sch_ex cept_pub .backup mydb

恢复一个SQL备份文件并忽略过程中的错误:

psql -U postgres -f myglobals.sql

恢复一个SQL备份文件,遇到错误则停止:

psql -U postgres --set ON_ERROR_STOP=on -f myglobals.sql

将SQL中的数据恢复到指定的database:

psql -U postgres -d mydb -f select_objects.sql

约束与索引

外键约束

  1. 一旦建立约束关系,如果主表中不存在约束键的记录,则引用表就不能够插入
  2. 定义级联规则,当主表的约束键发生变化,则引用表会自动进行修改,如果从表中还存在相关约束键的记录,则主表中对应的记录不能够删除.(ON UPDATE CASCADE ON DELETE RESTRICT)
  3. 在建立主键约束和唯一约束时会自动创建索引,大会外键约束在不会创建索引.需要手动创建索引以加速关联引用时的查询速度.

唯一约束

创建唯一约束会自动创建索引,支持多字段联合唯一.

check约束

对表的一个或多个字段添加一个条件,表中每一行都必须满足该条件.

排他性约束

索引

  1. B-树索引: 主键约束和唯一约束自动创建的就是B-树索引.
  2. GiST索引: 通用搜索树.适用于全文搜索,空间数据,科学数据,非结构化数据和层次化数据的搜索.
  3. GIN索引: 通用逆序索引.适用于内置的全文搜索引擎和jsonb数据类型.
  4. SP-GiST索引: 基于空间分区树算法的GiST索引.与GiST索引使用范围相同.
  5. 哈希索引: 不推荐.
  6. 基于B-树算法的GiST和GIN索引: 混合算法索引.支持多种特性.