PostgreSQL 和 Oracle 的管理艺术 Francs.tan 1
章节目录 2 一 体系结构二 维护经验三 备份四 监控
第一章 3 一 体系结构二 维护经验三 备份四 监控
1.1 Oracle 体系结构 4
1.2 PostgreSQL 体系结构 5 Client Interface Master Session Processes postgres postgres... postgres Utility Processes Data writer WAL writer logger process Autovacuum Stats collector Archive Data Buffer WAL Buffer Parameter file pg_hba file Data Files WAL Segments control file
第二章 6 一 体系结构二 维护经验三 备份四 监控
2.1 帮助 7 skytf=> help You are using psql, the command-line interface to PostgreSQL. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit skytf=>
2.1 帮助 8 skytf=> \h create index Command: CREATE INDEX Description: define a new index Syntax: CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ] ( { column ( expression ) } [ opclass ] [ ASC DESC ] [ NULLS { FIRST LAST } ] [,...] ) [ WITH ( storage_parameter = value [,... ] ) ] [ TABLESPACE tablespace ] [ WHERE predicate ]
2.2 建表大小写问题 9 PG 建表 DDL 语句默认小写处理 skytf=> create table TEST_56 (id integer,name varchar(32)); CREATE TABLE skytf=> select oid,relname from pg_class where relname='test_56'; oid relname -----+--------- (0 rows) skytf=> select oid,relname from pg_class where relname='test_56'; oid relname ----------+--------- 15814774 test_56 (1 row)
2.2 建表大小写问题 表名为大写时需要加双引号 skytf=> create table "TEST_57" (id integer,name varchar(32)); CREATE TABLE 10 skytf=> select oid,relname from pg_class where relname='test_57'; oid relname ----------+--------- 15814777 TEST_57 (1 row) skytf=> select * from TEST_57; ERROR: relation "test_57" does not exist LINE 1: select * from TEST_57; ^ skytf=> select * from "TEST_57"; id name ----+------ (0 rows)
2.3 kill session 11 Oracle 杀会话方式 杀会话方式 alter system kill session 'sid, serial#'; 杀操作系统进程 select 'kill -9 ' spid from v$process where addr in from v$session where SID= xxx') (select paddr
2.3 kill session 12 PostgreSQL 杀会话方式 杀会话方式 select pg_terminate_backend(pid int); 注意 : 不建议使用 kill -9 PID 杀会话进程, 否则数据库会处于 recovery 模式, 且不可连接, 报如下 ERROR psql: FATAL: the database system is in recovery mode
2.4 autocommit 13 Oracle autocommit=off PostgreSQL autocommit=on 备注 :1 在 PG 里一条 SQL 就是一条事务, 执行完后自动 commit; 2 在 PG 里, 一个函数就是一个事务, commit 命令结束后就退出 function, 而不能中途 commit
2.5 索引创建 14 共同点 : 都支持在线新建和在线重建 对比项 PostgreSQL Oracle 在线新建 支持 支持 在线重建 支持 ( 锁表 ) 支持 ( 不锁表 ) 并发创建 不支持 支持 部分索引 支持 不支持
2.5 索引创建 : 创建局部索引 15 skytf=> create index concurrently idx_create_time_ctime on activity using btree (create_time) where create_time > '2011-11-01'; CREATE INDEX skytf=> \d activity Table "skytf.activity" Column Type Modifiers ---------------+-----------------------------+---------------------- id character varying(32) not null identity_code integer not null default 256 body character varying(500) not null create_time timestamp without time zone default now() Indexes: "activity_pk" PRIMARY KEY, btree (id) "idx_create_time_ctime" btree (create_time) WHERE create_time > '2011-11-01 00:00:00'::timestamp without time zone
2.6 历史 SQL 信息追踪 16 Oracle 1 生成 AWR/ Statspack 报告 2 查询 DBA_HIS_XXX 系列系统视图 PostgreSQL 需加载外部模块 auto_explain 备注 :1 auto_explain 会记录 SQL 的执行计划并保存在数据库日志里 ; 2 SQL 的详细信息需结合 pg_stat_statements 视图
2.7 HOT-Standby : Oracle 10g 17
2.7 HOT-Standby : PostgreSQL 18
2.7 HOT-Standby : 简单比较 19 对比项 PostgreSQL9.0 Oracle 10g 原理 WAL segment Online redo log 主备切换 Trigger file sqlplus 命令方式 从库只读 支持 支持 同步速度 很快 ( 毫秒级 ) 较快
2.7 HOT-Standby : PostgreSQL 主节点增加一个库 20
2.7 HOT-Standby : 主节点增加库 21 创建用户 主机上执行 创建表空间目录 X 创建表空间目录 创建表空间 Primay X Standby
2.8 扩字段长度 22 对比项 PostgreSQL9.0 Oracle 10g varchar(n) 字段扩长 重写 不需要重写 备注 :1 PostgreSQL9.1, varchar text 类型不再需要重写表, 有一种情况除外, 即 varchar 字段是索引列
2.9 Rollback or Undo 23 Oracle 10g Data file update/ delete Undo tablespace PostgreSQL tuples 不删除, 只标记 Page tuple1 1 版 tuple1 2 版 Update Vacumm 后重用
2.9 优缺点 24 Oracle 10g 1. 需要维护 undo 表空间, 增加维护成本 2. 删除表数据时可以进行表级数据行闪回 ( flashback table) 3. 可能产生 ORA-01555 快照过旧错误 PostgreSQL 1. 没有 undo, 更新的数据仍然在数据文件, 老的数据需要 vacuum 2. 不能进行表级数据删除的闪回 3. Vacuum 机制对性能有一定影响, 理论上恢复较快 ;
第三章 25 一 体系结构二 维护经验三 备份四 监控
3.1 备份方式 26 备份方式 PostgreSQL9.0 Oracle 10g 逻辑备份 pg_dump exp/expdp 物理备份 数据备份 +WAL 归档 rman 第三方工具 pg_rman
第四章 27 一 体系结构二 维护经验三 备份四 监控
4.1 PostgreSQL 监控 28 主机监控 负载 IO 监控 硬盘使用率 swap 使用率 ping 数据库监控 pg 日志监控 Long session 分区表分区监控 低性能 SQL 监控 lock 监控
4.2 oracle 监控 29 主机监控 负载 cpu 使用率 硬盘使用率 swap 使用率 ping 数据库监控 实例是否可用 等侍事件 告警日志 表空间使用率 分区表分区 Archive/Purge AWR 报告
谢谢! 30