RDS 上 PPAS 由于没有对外开放超级用户,用户无法像线下使用 PPAS 那样使用 superuser 账号管理数据库对象。为此,我们推出了一组管理函数,帮助用户顺利使用云上的 PPAS 各种功能。
管理函数的使用规则
在云上的各类管理函数都要求用户使用 RDS 根账号来执行。RDS 根账号是分配实例时指定的管理账号,具有 createdb createrole login 权限。
[backcolor=transparent]插件管理函数 rds_manage_extension。
该函数帮助用户管理云上的插件,用户可以使用该函数创建和删除 PPAS 目前已经支持的插件。
-
[backcolor=transparent]
rds_manage_extension[backcolor=transparent]
([backcolor=transparent]
operation text[backcolor=transparent]
,[backcolor=transparent]
pname text[backcolor=transparent]
,[backcolor=transparent]
schema text [backcolor=transparent]
default[backcolor=transparent]
NULL[backcolor=transparent]
,[backcolor=transparent]
logging [backcolor=transparent]
bool[backcolor=transparent]
[backcolor=transparent]
default[backcolor=transparent]
[backcolor=transparent]
false[backcolor=transparent]
)
- [backcolor=transparent] operation[backcolor=transparent]:[backcolor=transparent] create [backcolor=transparent]或[backcolor=transparent] drop
- [backcolor=transparent] pname[backcolor=transparent]:[backcolor=transparent] [backcolor=transparent]支持的插件名
- [backcolor=transparent] schema[backcolor=transparent]:插件创建到的目标模式
- [backcolor=transparent] logging[backcolor=transparent]:插件创建时的日志信息
- [backcolor=transparent] [backcolor=transparent]目前支持的插件有:
- [backcolor=transparent] pg_stat_statements
- [backcolor=transparent] btree_gin
- [backcolor=transparent] btree_gist
- [backcolor=transparent] chkpass
- [backcolor=transparent] citext
- [backcolor=transparent] cube
- [backcolor=transparent] dblink
- [backcolor=transparent] dict_int
- [backcolor=transparent] earthdistance
- [backcolor=transparent] hstore
- [backcolor=transparent] intagg
- [backcolor=transparent] intarray
- [backcolor=transparent] isn
- [backcolor=transparent] ltree
- [backcolor=transparent] pgcrypto
- [backcolor=transparent] pgrowlocks
- [backcolor=transparent] pg_prewarm
- [backcolor=transparent] pg_trgm
- [backcolor=transparent] postgres_fdw
- [backcolor=transparent] sslinfo
- [backcolor=transparent] tablefunc
- [backcolor=transparent] tsearch2
- [backcolor=transparent] unaccent
- [backcolor=transparent] postgis
- [backcolor=transparent] postgis_topology
- [backcolor=transparent] fuzzystrmatch
- [backcolor=transparent] postgis_tiger_geocoder
- [backcolor=transparent] plperl
- [backcolor=transparent] pltcl
- [backcolor=transparent] plv8
- [backcolor=transparent] [backcolor=transparent]"uuid-ossp"[backcolor=transparent]
- [backcolor=transparent] plpgsql
- [backcolor=transparent] oss_fdw
- [backcolor=transparent] [backcolor=transparent]举例:
- [backcolor=transparent] [backcolor=transparent]1[backcolor=transparent] [backcolor=transparent]创建插件[backcolor=transparent] dblink
- [backcolor=transparent] [backcolor=transparent]select[backcolor=transparent] rds_manage_extension[backcolor=transparent]([backcolor=transparent]'create'[backcolor=transparent],[backcolor=transparent]'dblink'[backcolor=transparent]);
- [backcolor=transparent] [backcolor=transparent]2[backcolor=transparent] [backcolor=transparent]删除插件[backcolor=transparent] dblink
- [backcolor=transparent] [backcolor=transparent]select[backcolor=transparent] rds_manage_extension[backcolor=transparent]([backcolor=transparent]'drop'[backcolor=transparent],[backcolor=transparent]'dblink'[backcolor=transparent]);
[backcolor=transparent]当前连接会话 rds_pg_stat_activity()。
该函数类似 pg_stat_activity 视图,返回用户相关的所有连接会话信息。
[backcolor=transparent]查看慢 SQL 的函数 rds_pg_stat_statements()。
该函数是视图 pg_stat_statements 的封装,目的是让用户查看自己权限范围内的慢SQL。
[backcolor=transparent]性能分析函数。
本组函数,类似 Oracle AWR 报告,提供给用户一组函数帮助用户分析目前 PPAS 实例的试试性能信息。
-
[backcolor=transparent]
[backcolor=transparent]
1[backcolor=transparent]
rds_truncsnap[backcolor=transparent]
()
- [backcolor=transparent] [backcolor=transparent]说明:[backcolor=transparent] [backcolor=transparent]删除目前保存的所有快照。
- [backcolor=transparent] [backcolor=transparent]2[backcolor=transparent] rds_get_snaps[backcolor=transparent]()
- [backcolor=transparent] [backcolor=transparent]说明:[backcolor=transparent] [backcolor=transparent]获得目前保存的所有快照信息。
- [backcolor=transparent] [backcolor=transparent]3[backcolor=transparent] rds_snap[backcolor=transparent]()
- [backcolor=transparent] [backcolor=transparent]说明:产生一个实时快照。
- [backcolor=transparent] [backcolor=transparent]4[backcolor=transparent] rds_report[backcolor=transparent]([backcolor=transparent]beginsnap bigint[backcolor=transparent],[backcolor=transparent] endsnap bigint[backcolor=transparent])
- [backcolor=transparent] [backcolor=transparent]制定一个初始快照变化和结束快照变化,产生基于快照的性能分析报告。
- [backcolor=transparent] [backcolor=transparent]举例:下面是一个通过产生快照生成性能分析报告的过程
- [backcolor=transparent] SELECT [backcolor=transparent]*[backcolor=transparent] FROM rds_truncsnap[backcolor=transparent]();[backcolor=transparent] [backcolor=transparent]//删除之前保存的快照
- [backcolor=transparent] SELECT [backcolor=transparent]*[backcolor=transparent] [backcolor=transparent]from[backcolor=transparent] rds_snap[backcolor=transparent]();[backcolor=transparent] [backcolor=transparent]// 产生一个快照
- [backcolor=transparent] SELECT [backcolor=transparent]*[backcolor=transparent] [backcolor=transparent]from[backcolor=transparent] rds_snap[backcolor=transparent]();[backcolor=transparent] [backcolor=transparent]// 产生一个快照
- [backcolor=transparent] SELECT [backcolor=transparent]*[backcolor=transparent] [backcolor=transparent]from[backcolor=transparent] rds_snap[backcolor=transparent]();[backcolor=transparent] [backcolor=transparent]// 产生一个快照
- [backcolor=transparent] SELECT [backcolor=transparent]*[backcolor=transparent] FROM rds_get_snaps[backcolor=transparent]();[backcolor=transparent] [backcolor=transparent]//获取目前产生的快照ID: 1 2 3
- [backcolor=transparent] SELECT [backcolor=transparent]*[backcolor=transparent] FROM edbreport[backcolor=transparent]([backcolor=transparent]1[backcolor=transparent],[backcolor=transparent] [backcolor=transparent]3[backcolor=transparent]);[backcolor=transparent] [backcolor=transparent]//根据快照产生一个性能分析报告
[backcolor=transparent]终止会话函数。
-
[backcolor=transparent]
rds_pg_terminate_backend[backcolor=transparent]
([backcolor=transparent]
upid [backcolor=transparent]
int[backcolor=transparent]
)
- [backcolor=transparent] rds_pg_cancel_backend[backcolor=transparent]([backcolor=transparent]upid [backcolor=transparent]int[backcolor=transparent])
- [backcolor=transparent] [backcolor=transparent]该函数分别对应原生的[backcolor=transparent] pg_terminate_backend [backcolor=transparent]和[backcolor=transparent] pg_cancel_backend[backcolor=transparent],区别仅是他们无法操作[backcolor=transparent] supueruser [backcolor=transparent]建立的连接。
- [backcolor=transparent] [backcolor=transparent]举例:终止进程号为[backcolor=transparent] [backcolor=transparent]123456[backcolor=transparent] [backcolor=transparent]的回话
- [backcolor=transparent] [backcolor=transparent]select[backcolor=transparent] rds_pg_cancel_backend[backcolor=transparent]([backcolor=transparent]123456[backcolor=transparent]);
[backcolor=transparent]VPD 函数。
VPD 即 Virtual Private Database,是兼容 Package DBMS_RLS 的一种封装,参数完全相同。
-
[backcolor=transparent]
[backcolor=transparent]
1[backcolor=transparent]
rds_drop_policy [backcolor=transparent]
对应[backcolor=transparent]
DBMS_RLS[backcolor=transparent]
.[backcolor=transparent]
DROP_POLICY
- [backcolor=transparent] [backcolor=transparent]2[backcolor=transparent] rds_enable_policy [backcolor=transparent]对应[backcolor=transparent] DBMS_RLS[backcolor=transparent].[backcolor=transparent]ENABLE_POLICY
- [backcolor=transparent] [backcolor=transparent]3[backcolor=transparent] rds_add_policy [backcolor=transparent]对应[backcolor=transparent] DBMS_RLS[backcolor=transparent].[backcolor=transparent]ADD_POLICY