目录 1. 简介 2. 浏览数据库 3. 数据库配置 4. 数据库控制 5. 表与数据 6. 安全 7. 数据库管理 8. 监控与诊断 9. 一般维护 10. 性能与并发 11. 备份恢复 12. 复制与升级

Transcription

1 Postgresql Admin Cookbook DBA/ 李思亮

2 目录 1. 简介 2. 浏览数据库 3. 数据库配置 4. 数据库控制 5. 表与数据 6. 安全 7. 数据库管理 8. 监控与诊断 9. 一般维护 10. 性能与并发 11. 备份恢复 12. 复制与升级

3 简介 1. 主要功能 : 支持最新的 sql 标准 SQL2008 C/S 结构 高并发设计, 无阻塞读写 高可扩展性 高可配置性 性能优化可操作空间大 大部分的 ddl 也支持事务性, 可以回滚 2. 与 Oracle 比较 : oracle 高并发 / 事务支持通过 "Snapshort isolation" 快照读来实现的 postgresql 是通过 MVCC (Multy Version Concurrency Control 多版本并发控制 ) 实现

4 简介 3. 主要 GUI 管理工具 : pgadmin3 phppgadmin

5 简介 4. 命令行工具 : psql psql h hostname p 5432 d dbname U username -W passwd

6 浏览数据库 1. 数据库主要控制信息 : pg_controldata 命令行工具 pg_control version number: 903 Catalog version number: Database system identifier: Database cluster state: in production pg_control last modified: 2010 年 12 月 27 日星期一 15 时 51 分 12 秒 Latest checkpoint location: 0/2CC6C3C0 Prior checkpoint location: 0/2CC6A468 Latest checkpoint's REDO location: 0/2CC6C3C0 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/899 Latest checkpoint's NextOID: Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestxid: 655 Latest checkpoint's oldestxid's DB: 1 Latest checkpoint's oldestactivexid: 0 Time of latest checkpoint: 2010 年 12 月 27 日星期一 15 时 51 分 12 秒 Minimum recovery ending location: 0/0 Backup start location: 0/0 Current wal_level setting: minimal Current max_connections setting: 100 Current max_prepared_xacts setting: 0 Current max_locks_per_xact setting: 64 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: WAL block size: 8192 Bytes per WAL segment: Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value

7 浏览数据库 2.List Database on this server : psql -l psql 内置命令 \l sql 语句 select * from pg_database;

8 浏览数据库 3. 游戏规则 : 默认安装 3 个库 postgres template0 template1 默认情况下建立的数据库都是以 template1 为模板的 postgres,template0 数据库属性不允许修改, 或者说后果自负, 他们是在初始化数据库时设定的, 如果要修改, 需要删除原来的库, 重新初始化 4. 磁盘空间问题 : 查看单个库用了多少磁盘空间 lsl=# select pg_database_size(current_database()); pg_database_size (1 row) 查看整个集群所使用的磁盘空间 lsl=# select sum(pg_database_size(datname)) from pg_database; sum (1 row)

9 空间问题续 查看单个表的空间使用 lsl=# select pg_relation_size('word'); pg_relation_size (1 row) 查看表以及附属对象占用的空间 lsl=# select pg_total_relation_size('word') ; pg_total_relation_size (1 row)

10 浏览数据库 5. 查看表的行数 select count(*) from tab_name; 不带任何条件的 count(*) 走 sequece scan, 跟表的数据量正相关 扫描素所有的数据块, 大表可能要花费很长时间 估算表的行数 SELECT (CASE WHEN reltuples > 0 THEN pg_relation_size('mytable')/(8192*relpages/reltuples) ELSE 0 END)::bigint AS estimated_row_count FROM pg_class WHERE oid = 'mytable'::regclass; 原理 : 因为 MVCC 的原因, 会导致有些数据块里没有有效数据行, 每个数据块是 8k,relpages/reltuples 数据尺寸与实际数据行的比值, 这样就可以用表的尺寸 / 数据行的尺寸来估算表里有多少行数据, 这个不会扫描全表, 执行时间与表的大小无关

11 数据库配置 1. 数据库参数值 select * from pg_settings; name allow_system_table_mods setting off unit category Developer Options short_desc Allows modifications of the structure of system tables. extra_desc context postmaster vartype bool source default min_val max_val enumvals boot_val off 全库有 208 个参数 reset_val off sourcefile sourceline

12 2. 参数的设置方法 : set 命令动态调整参数 set work_mem = '16M'; set local work_mem = '16M'; 数据库配置 修改 postgresql.conf 参数文件, 然后 reload pg_clt reload -D $PGDATA $PGDATA 是数据库的数据目录, 初始化数据库时设定 修改 postgresql.conf 参数文件, 重启数据库 pg_ctl restart -D $PGDATA $PGDATA 是数据库的数据目录, 初始化数据库时设定 show 显示参数的当前值 select * from pg_settings where name='mypara'

13 数据库配置 3. 为部分用户指定相应的参数值 : ALTER DATABASE saas SET configuration_parameter = value1; ALTER ROLE saas SET configuration_parameter = value2; ALTER ROLE simon IN DATABASE saas SET configuration_parameter = value3; 4.OS 的内核参数调整 可以参照 oracle 安装时的参数设置

14 数据库配置 5. 一些主要的参数 shared_buffer 越大越好, 受限于系统内核参数 SHMMX (/etc/sysctl.conf) effictive_cache_size 这个参数并没有想象中的那么重要, 系统留给磁盘 cache 内存大小, 其实与 pg 的性能无关 wal_buffer 写多的应用应该增大尺寸, 与 redo 日志相关 checkpoint_segment 写多的应用或者要大批量导入数据这个参数需要增大 work_mem 工作内存, 可以动态调整, 大的查询应该增大, 默认 1M atuovacuum 确保打开这个选项, 默认是打开的, 垃圾回收机制参考 一般需要调整与 fsync 相关的参数, 确保安全 动态加载第三方的插件 load in session shared_preload_libriaries 配置到 postgresql.conf load_preload_libriaries 用 alter role 加载

15 系统控制 1. 数据库启动关闭 : start: pg_ctl -D $PGDATA start stop: pg_ctl -D $PGDATA stop restart: pg_ctl -D $PGDATA restart reload: pg_ctl -D $PGDATA reload 也可以通过 pg_stat_activity 查到相应的 pid, 然后用 kill -SigHup $pid 来重载参数文件 刷新 shared_buffer : psql -c "checkpoint" 备份 CACHE 与恢复 : psql -c "select pg_cache_save('mytab'); 把 cache 备份到表 mytab 中 psql -c " select pg_cache_warm('mytab') ; 把 cache 从表 mytab 中回写到内存 refer:

16 系统控制 2. 限制普通链接 : alter database mydb connection limit 0 ; 禁止登录到指定数据库 alter user username connection limit 0 ; 禁止某个用户登录 修改 $PGDATA/Hba.conf 文件 3. 踢用户 : select pg_terminate_backend(procid) from pg_stat_activity where...; 4. 数据库端连接池 : pgbouncer 单进程, 可以支持数以千记的客户端链接, 而对服务器只有几个链接就可以支撑 不支持 ssl 加密 详细配置信息参考 cook book

17 表与数据 Choosing good names for database objects Handling objects with quoted names Enforcing same name, same definition for columns Identifying and removing duplicate rows Preventing duplicate rows Finding a unique key for a set of data Generating test data Randomly sampling data Loading data from a spreadsheet Loading data from flat files 从 csv 或者 excel 装入数据库 postgres=# \COPY sample FROM sample.csv CSV HEADER postgres=# SELECT * FROM sample; key value c 2 d 本章节没有什么实质性内容, 最后的大数据量装载可以看看

18 安全 1. 本章主要是数据库授权相关的语句, 感觉授权的方式相当的灵活 将一个 schema 下的对象授权 grant select on all tables in schema myschema to user/role; create user 等同于 create role login create group 等同于 create role nologin 即 group 是没有登录权限的, 限定用户的最大并发连接数 alter user username connection limit N / -1 (-1 无限制,0 禁止登录 ); 2. 删除用户而不删除用户下的对象 : alter user abc nologin ; ( 将用户变成 group, 实现曲线救国 ) grant abc to def ; 则 def 用户也不能登录? 改变对象的属主关系 : reassign owned by abc to def ; 有限的管理权限 alter role abc with createdb /create user; 3. 审计 ddl 语句 : log_statement= DDL' postgresql.conf 参数文件设定 然后 reload

19 安全 3. 支持 ldap(lightweight directory access protocol 轻量级目录访问协议 ) 支持 ssl 加密传输 支持数据加密需要安装模块 pgcrypto

20 1. 管理任务要用事务来处理 BEGIN; COMMAND1 ; COMMAND 2; commit/ rollback; 使用命令行参数 psql -l -f scripts.sql psql --single-transaction -f script.sql POSTGRESQL 不支持嵌套的事务 数据库管理 2. 不支持事务的命令 : create database / drop database create /drop tablespace ; create index concurrently 无阻塞模式建立索引 vacuum

21 3. 设定在脚本的第一个错误的地方退出 psql -f my.sql -v on_err_stop=on 或者在脚本里添加控制代码 vi my.sql \set on_error_stop ; command ; 数据库管理 在 psqlrc 文件中添加变量, 登录后就自动设定变量 vi.psqlrc \set on_error_stop

22 数据库管理 3. 更改字段类型 : alter table mytab alter column mycol set data type text; 设为文本型 alter table mytab alter column mycol set data type integer using mycol::integer ; 将原本字符型的数字数据转换为整形 alter table mytab alter column mycol set data type date using date ( to_date(mycol::text, 'yyyymmdd') ( case when mycol/1000<15 then interval '0' else interval '100 years' end) ; 支持复杂的数据转换 alter table mytab drop default 'expression' ; 去掉默认值 drop not null ; 去除约束 ;

23 数据库管理 4. 增加删除字段 : alter table mytab add mycol col_name data type timestamp ; alter table mytab drop column mycol ; ALTER TABLE mytable DROP COLUMN IF EXISTS last_update_timestamp, ADD COLUMN last_update_timestamp TIMESTAMP WITHOUT TIME ZONE; drop column 并不会马上从行数据里删除列的值, 而是做一个标记标识这个列已经被删除, 这样,drop column 就会非常快,oracle 也是这样类似的操作方法 add column 如果没有 default 值也会非常快, 如果有 default 值, 或者 not null 的约束, 就要写数据到每行, 就会慢很多,oracle 也是类似的操作手法

24 数据库管理 5.schema 管理 : 如果 schema = username 则这个 schema 下只能放本用户的数据, 这跟 oracle 差不多 CREATE SCHEMA sharedschema AUTHORIZATION scarlett; 如果 schema <> username 则可以存放其他用户的对象 CREATE SCHEMA AUTHORIZATION scarlett; schema 跟用户同名 改变属主关系 : alter schema myschema owner to new_owner ; 删除 schema drop schema if exists myschema; drop schema myschema cascade; schema 下面的没有对象, 才可以删除, 否则只能 cascade 级联删除 权限管理 : grant select on all tables in schema myschema to public ; alter default privileges in schema myschema grant select on tables to public ; 在 schema 间移动对象 : alter table mytab set schema otherschema; alter schema oldschema rename to new_schema; ( 新的名字不存在才能执行否则报错 ) 只是逻辑关系得转变的, 数据文件不会移动, 有些对象没有 schema 的概念, 不能移动, 只能重建, 例如函数, 自定义数据类型

25 数据库管理 6. 表空间管理 : 建立表空间 : 创建一个空目录 设定目录的访问权限为 postgres 的权限, 以及属主关系 要用绝对路径 unix 系统能直接使用 mount 点 (mount point) 数据库执行建表空间命令 : create tablespace new_tablespace location '/xxxx/path'; 只能删除空的表空间 drop table tablespacename; 设定表空间的属主关系 : alter tablespace tablespace_name set owner to username ; alter user username set default tablespace = 'tablespace_name';

26 数据库管理 7.tablespace 间移动对象 : alter table mytab set tablespace new_tablespace ; alter index myindex set tablespace new_tablespace ; 基于事务的操作 : BEGIN; alter table mytab set tablespace new_tablespace ; alter index myindex set tablespace new_tablespace ; Commit; 表空间间的对象移动, 会导致数据文件的物理移动, 操作为批量的数据块拷贝会产生全表排他锁, 此类操作应谨慎实施 alter database db_name set default_tablespace new_tablespace ; alter database db_name set tablespace new_tablespacename ; 全库对象转笔表空间,

27 数据库管理 8.DBLINK postgres 支持 dblink: 需要安装一个模块,contrib model psql -f $PGHOME/share/postgresql/contrib/dblink.sql 不想 oracle 那样是严格意义上的 dblink, 仅是一组函数实现 c 语言 libpq 实现 建立 dblink: CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;( 建立远端数据的包装 ) CREATE SERVER otherdb FOREIGN DATA WRAPPER postgresql OPTIONS (host 'foo', dbname 'otherdb', port '5432'); CREATE USER MAPPING FOR PUBLIC 用 libpq 的 pguser 变量的值来连接远端数据库用户, 如果没有设定则用 os 用户权限来管理

28 数据库管理 验证建立连接 SELECT dblink_connect('otherdb'); dblink_connect OK (1 row) 断开连接 : SELECT dblink_disconnect(); dblink_connect OK (1 row) 功能有一定限制, 无法实现与 oracle 类似的本地表与远端表的关联操作 dblink 是长连接, 能够穿越事务, 事务失败后,dblink 不会断开 再安排更详细的资料说明用法

29 监控与诊断 1. 性能监控 : SNMP 协议 : pgsnmp CACTI/nagios 监控插件 监控用户 : 用户是否登录数据库 select * from pg_stat_activity where username= 'xxx'; 正在执行的 sql :set track_activity= on (supper user 执行 ) select * from pg_stat_activity where current_query!= '<IDLE>'; 系统视图 pg_stat_activity 有系统函数 pg_stat_get_activity($pid) 生成 模块 pg_stat_statement 可以顺势捕捉执行状态 3. 慢查询 : select current_timestamp - query_start as runtime, datname, usename, current_query from pg_stat_activity where current_query!= '<IDLE>' order by 1 desc;

30 监控与诊断 4. 查看语句是 active 还是 blocked : SELECT datname,usename,current_query FROM pg_stat_activity WHERE waiting = true; 只记录由于内部的锁导致的阻塞 set update_process_title=on : 设定此参数, 可以在 os 的 top ps 等命令里看到执行的 sql 有一定的风险 查看由哪些语句导致的阻塞 : (admin cook book P199, 语句太长 ) 5. 杀用户 session select pg_terminate_backend($pid) 杀死当前 session select pg_cancel_backend($pid) 终止当前查询 kill -9 从 os 删除用户 session 如果系统参数 synchronous_commit= off 这样操作时不安全的, 可能会导致已经提交的事务丢失 设定语句的最长运行时间 set statement_timeout='15s' 设定为 15 秒

31 监控与诊断 6. 监控表与索引 set log_temp_file =0 (kb) 任何建立临时表的语句都被记录到日志文件 查看表是否存在膨胀 select pg_relation_size(relid) as tablesize,schemaname, relname,n_live_tup from pg_stat_user_tables where relname = <tablename>; vacuum 无法标记索引的记录, 索引索引的膨胀是无法被检测的 7. 日志处理 : set log_rotation_age ='1d' 每天生成一个日志文件, 文件带有时间戳 处理日志的工具 pgfouine.php PQA

32 日常维护 1. 自动化数据库管理 配置 autovacuum postgresql.conf 参数文件 autovacuum = on track_ocunts= on VACUUM 有大约 35 个参数来管理, 具体参照 pg 的文档 2. 设定表的存储参数 alter table mytab set ( storage_param = value) ; autovacuum_enable autovacuum_vacuum_cost_delay autovacuum_vacuum_cost_limit... TOAST 表的存储参数 toast.autovacuum_enable toast.autovacuum_vacuum_cost_delay toast.autovacuum_cost_limit toast 表 : 列的数据超过一定长度后剩余的部分会写到另外的表中存储, 这种表就叫 toast 表

33 日常维护 3.VACUUM: vacuum 标记过期的事务记录, 非阻塞方式, 可以执行 insert,update,delete 操作但是不能执行 alter table,create index 这类操作 不会阻塞用户进程,vacuum 一旦检测到自己持有的锁与用户进程的操作发生冲突, 他会自动的释放自己的锁, 为用户放行, 不会阻塞用户进程, 保证系统效率 vacuum 不会释放磁盘空间, 除非使用 vacuum full 选项, 但是会锁表, 大表的操作需要谨慎 9.0 新特性,vacuum full 的速度大大增强, 运行速度更快 4. 数据库坏块 : pg 的坏块主要是有由于 bug 或者磁盘故障引起的 目前没有很好的方法来修复坏块 可以用工具从坏块里抽取数据 (contrib /pageinspact)

34 日常维护 5.prepared transaction( 异步提交事务 ) select * from pg_prepared_xacts; 可以在一个事务中去管理另外的一个事务,prepared transaction 可以穿越故障, 而不会回滚, 事务的信息会记录到磁盘上, 可以在另外的事务里提交这个事务或者回滚 事务 id xmin 每个 pg 表都有一个隐含字段 xmin 记录了这行记录最后的事务 id ; 可以用来去定这行数据的最后修改 6. 关注临时表 / 系统字典表 用 vacuum 处理系统表 监视临时文件的使用情况, 大小多少 调整内存参数 temp_buffer 模块 contrib/pgstattuple 可以查看表是否需要执行 vacuum select * from pgstattuple('mytab'); ngios 插件 check_postgres 集成该功能 如对临时表的操作大过正常表有必要建立单独的临时表空间 temp_tablespace 变量生成临时对象会写 pg_class 和 pg_attribute 这些系统表的 index 就会膨胀, 不能对系统表的属性做修改, 不能执行 alter table 操作设定表级别的 vacuum 参数, 只能手工执行 vacuum 或者设定系统级的 autovacuum=on, 确保对所有的系统表都做到 select relname,pg_relation_size(oid) from pg_calss where relnamespace='xxxxxx' order by 2 desc ;

35 日常维护 7.vacuum 无法检测到索引膨胀 reindex 命令重建索引, 会导致阻塞, 锁表 create index concurrently 不会锁表 重建索引的步骤 : create index concurrently new_index on table (my_col) ; begin; drop index my_old_index; alter index new_index rename to my_old_index ; commit; 找出没有使用过的索引 select schemaname,relname,indexrelname,index_scan from pg_stat_user_indexes order by index_scan ; 注意 :insert 操作不会修改 index_scan update,delete 会修改

36 日常维护 8. 索引的删除 : 删除索引可以通过修改数据字典来实现而需要采用物理删除 update pg_index set indisvalid =false where indexrelid= 'my_index'::regclass; 如果认为需要恢复索引, 则直接修改字典表, 而不需要重建, 节省时间 update pg_index set indisvalid =true where indexrelid= 'my_index'::regclass; create index concurrently 就是通过这个模式来实现不锁表的 set indisvalid =false 只是标记 sql 语句不能使用这个索引来查询数据, 但索引数据还是可以自动维护, 不会导致索引与表数据不一致

37 性能与并发处理 1. 找出慢查询 : 设置变量 log_min_duration_statement=1000 (10 秒 ) select * frompg_stat_activity wherecurrent_timestamp-query_time > 10 秒 注意查看 pg_stat_* 这些性能表 pg_stat_user_tables: seq_tup_read/seq_scan 每次扫描读取的记录数 pg_statio_user_tables: heap_blks_hit: 多少数据在 shared_buffer 中 idx_blks_read: 多少数据需要从磁盘读到内存中, 类似于 oracle 的 disk read 2. 收集一般的性能数据 pg_statlogs.tar.gz 3.explain / explain analyze mydb=# explain analyse select count(*) from t; QUERY PLAN Aggregate (cost= rows=1 width=0) (actual time= rows=1 loops=1) -> Seq Scan on t (cost= rows=901 width=0) (actual time= rows=901 loops=1) Total runtime: ms

38 性能与并发处理 4.sql 优化思路 : 限制 sql 返回的行数 复杂 sql 简单化, 物化视图, 视图, 临时表 设定系统参数 show default_statistics_target; alter database mydb set default_statistics_target=200; alter table mytab alter col_with_bad_stats set statistics 500; 这些参数会影响 analyze 的结果 为没有用 ALTER TABLE SET STATISTICS 设置字段相关目标的表中其它字段设置缺省统计目标 更大的数值增加了 ANALYZE 所需要的时间, 但是可能会改善规划器的估计质量 缺省值是 10 添加复合索引 create index myidx on mytab (col1,col2) ; 添加条件索引 : create index myindex on mytab ( my_col) where my_col >0 ; 这是 pg 的一个特色的地方 使用分区表 : pg 不直接支持分区表, 同表的继承关系, 设定特定的规则曲线实现 提供完整的解决方案

39 性能与并发处理 5. 修改表的填充系数 (fill factor): 如果某些表频繁 update, 可以考虑调低 fillfactor 的值默认为 100, 即表示默认 insert 操作的时候, 把数据块全部填满再写下一个块, 对于频繁更新的表可以考虑修改 alter table mytab set ( fillfactor = 70) ; pg 为每个 block 会预留 30% 的空闲空间, 便于以后的 update 造成的空间增长 这个功能跟 oracle 表的 pctfree pctused 参数功能差不多 6. 一些 HINT : set enable_seqscan to off ; 告诉 pg seqscan 性能很差, 这样会强制走索引 random_page_cost 设定一个更低的值, 让 pg 认为走离散 io 效果更好, 从而选择走索引 seq_page_cost 设定一个一个更大的值, 让 pg 认为走 seq io 的开销增大, 从而选择索引这两个参数互斥的 7. 报告性能问题 : 社区的反应速度很快

40 备份与恢复 1. 相关参数 checkpoint_segments=1000 WAL log 的最大数量, 系统默认值是 3 该值越大, 在执行介质恢复时处理的数据量也越大, 时间相对越长 checkpoint_timeout=3600 系统自动执行 checkpoint 之间的最大时间间隔, 同样间隔越大介质恢复的时间越长 系统默认值是 5 分钟 WAL ( write ahead log) 功能跟 oracle 的 redolog 一样的, 实现方式, 跟 mysql 的 binlog 差不多, 事务提交前一定是先写 wal, WAL 默认尺寸是 16M 2. 各种备份 / 恢复方式 逻辑备份 pg_dump 很多参数实现不同的备份功能 物理备份 + 持续归档 ( WAL 起决定性作用 ) 可以实现基于时间点的回复 无法对单个对象进行恢复, 只能将全库恢复到异地, 然后把对象导出导入 关于备份系统,cookbook 列出了各种备份恢复的情形与相关的执行方案, 大家直接看书吧

41 复制与升级 PG9.0 版本对复制功能做了很大的增强,standby 库可以在线只读打开提供查询, PG 的复制功能是 pg 的高可用性, 可扩展性的很重要的组成部分 cookbook 提供了各种情形先系统复制的完整解决方案大家直接看书吧

42 日常维护 使用过 oracle 的人都知道, 在 oracle 数据库中支持在 sql 语句上加 hints 来固定 sql 的执行计划, 但在 PostgreSQL 数据库不支持这种在 sql 语句上直接加 hints 的方法 那么在 PostgreSQL 数据库中是否有固定执行计划的方法? 答案显然是肯定的 首先在 PostgreSQL 数据库中可以设置有很多参数来改变执行计划, 这些参数都支持在事务级 session 级别和全局设置 一般我们通过在事务级别或 session 级别设置, 就基本上等于了在 sql 上加 Hints, 这些参数为 : 参数名称 参数的使用 enable_seqscan 是否走全表扫描 enable_hashjoin 是否允许走 hash 连接 enable_nestloop 是否允许走 nestloop 连接 enable_mergejoin 是否允许走合并连接 enable_tidscan 是否允许走 tid 扫描 ( 类似 oracle 中的按 rowid 访 问 ) enable_bitmapscan 是否允许走 bitmap 扫描 enable_hashagg 是否允许走 hash 聚集 ( 也就是做 group by 时 ) enable_indexscan 是否允许走索引 enable_sort 是否允许走排序

43