· 6 years ago · Jun 15, 2019, 11:02 AM
1-- connexion au serveur RDS (via un tomcat)
2psql -h rtmry1pgs10.cicbadezaffz.eu-west-1.rds.amazonaws.com -d postgres -U pgmaster -p 60901
3
4-- Connexion a la base demandee :
5\c myoffer
6
7
8=======================================================================================================================
9-- Les étapes détaillées du script "create_pgwatch2.sh" à installer directement via psql :
10=======================================================================================================================
11-------------------------------------------------------------------------------------------------
12-- Voir si l extension "pg_stat_statements" existe :
13-------------------------------------------------------------------------------------------------
14postgres=> select count(*) from pg_extension where extname = 'pg_stat_statements';
15 count
16-------
17 0
18(1 row)
19
20--> installer l extension
21postgres=> create extension pg_stat_statements;
22CREATE EXTENSION
23
24
25-------------------------------------------------------------------------------------------------
26-- Voir si l extension "plpythonu" existe :
27-------------------------------------------------------------------------------------------------
28postgres=> select count(*) from pg_extension where extname = 'plpythonu';
29 count
30-------
31 0
32(1 row)
33
34--> installer l extension
35postgres=> create extension plpythonu;
36ERROR: Extension "plpythonu" is not supported by Amazon RDS
37DETAIL: Installing the extension "plpythonu" failed, because it is not on the list of extensions supported by Amazon RDS.
38HINT: Amazon RDS allows users with rds_superuser role to install supported extensions. See: SHOW rds.extensions;
39
40
41-------------------------------------------------------------------------------------------------
42-- Creation du role pgwatch :
43-------------------------------------------------------------------------------------------------
44CREATE ROLE pgwatch2 with login password 'pgwatch2';
45
46
47-------------------------------------------------------------------------------------------------
48-- exécuter le script "stat_activity_wrapper.sql" :
49-------------------------------------------------------------------------------------------------
50/*
51A wrapper around pg_stat_activity to enable session, blocking lock, etc monitoring
52by the non-superuser pgwatch2 role.
53Assumes a role has been created named pgwatch2
54*/
55
56DO $OUTER$
57DECLARE
58 l_pgver double precision;
59 l_sproc_text_pre92 text := $SQL$
60CREATE OR REPLACE FUNCTION get_stat_activity() RETURNS SETOF pg_stat_activity AS
61$$
62 select * from pg_stat_activity where datname = current_database() and procpid != pg_backend_pid()
63$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
64$SQL$;
65 l_sproc_text_92_plus text := $SQL$
66CREATE OR REPLACE FUNCTION get_stat_activity() RETURNS SETOF pg_stat_activity AS
67$$
68 select * from pg_stat_activity where datname = current_database() and pid != pg_backend_pid()
69$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
70$SQL$;
71BEGIN
72 SELECT ((regexp_matches(
73 regexp_replace(current_setting('server_version'), '(beta|devel).*', '', 'g'),
74 E'\\d+\\.?\\d+?'))[1])::double precision INTO l_pgver;
75 EXECUTE format(CASE WHEN l_pgver > 9.1 THEN l_sproc_text_92_plus ELSE l_sproc_text_pre92 END);
76END;
77$OUTER$;
78
79GRANT EXECUTE ON FUNCTION get_stat_activity() TO pgwatch2;
80COMMENT ON FUNCTION get_stat_activity() IS 'created for pgwatch2';
81
82
83-------------------------------------------------------------------------------------------------
84-- exécuter le script "stat_statements_wrapper.sql" :
85-------------------------------------------------------------------------------------------------
86DO $OUTER$
87DECLARE
88 l_pgver double precision;
89 l_sproc_text text := $SQL$
90CREATE OR REPLACE FUNCTION get_stat_statements() RETURNS SETOF pg_stat_statements AS
91$$
92 select s.* from pg_stat_statements s join pg_database d on d.oid = s.dbid and d.datname = current_database()
93$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
94$SQL$;
95 l_sproc_text_queryid text := $SQL$
96CREATE OR REPLACE FUNCTION get_stat_statements() RETURNS TABLE (
97 queryid int8, query text, calls int8, total_time float8, rows int8, shared_blks_hit int8, shared_blks_read int8,
98 shared_blks_dirtied int8, shared_blks_written int8, local_blks_hit int8, local_blks_read int8, local_blks_dirtied int8,
99 local_blks_written int8, temp_blks_read int8, temp_blks_written int8, blk_read_time float8, blk_write_time float8,
100 userid int8, dbid int8
101) AS
102$$
103begin
104 return query
105 select (regexp_replace(md5(s.query), E'\\D', '', 'g'))::varchar(10)::int8 as queryid,
106 s.query, s.calls, s.total_time, s.rows, s.shared_blks_hit, s.shared_blks_read, s.shared_blks_dirtied, s.shared_blks_written,
107 s.local_blks_hit, s.local_blks_read, s.local_blks_dirtied, s.local_blks_written, s.temp_blks_read, s.temp_blks_written,
108 s.blk_read_time, s.blk_write_time, s.userid::int8, s.dbid::int8
109 from pg_stat_statements s join pg_database d on d.oid = s.dbid and d.datname = current_database();
110 end;
111$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
112$SQL$;
113BEGIN
114 SELECT ((regexp_matches(
115 regexp_replace(current_setting('server_version'), '(beta|devel).*', '', 'g'),
116 E'\\d+\\.?\\d+?'))[1])::double precision INTO l_pgver;
117 IF l_pgver > 9.1 THEN --parameters normalized only from 9.2
118 EXECUTE 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements';
119 EXECUTE format(CASE WHEN l_pgver > 9.3 THEN l_sproc_text ELSE l_sproc_text_queryid END);
120 EXECUTE 'GRANT EXECUTE ON FUNCTION get_stat_statements() TO pgwatch2';
121 EXECUTE 'COMMENT ON FUNCTION get_stat_statements() IS ''created for pgwatch2''';
122 END IF;
123END;
124$OUTER$;
125
126
127-------------------------------------------------------------------------------------------------
128-- exécuter le script "cpu_load_plpythonu.sql" :
129-------------------------------------------------------------------------------------------------
130--> ce script retournera une erreur car l extension "plpythonpu" n'est pas compatible avec RDS
131
132
133-------------------------------------------------------------------------------------------------
134-- exécuter le script "table_bloat_approx.sql" :
135-------------------------------------------------------------------------------------------------
136BEGIN;
137
138CREATE EXTENSION IF NOT EXISTS pgstattuple;
139DO $OUTER$
140DECLARE
141 l_sproc_text text := $_SQL_$
142CREATE OR REPLACE FUNCTION get_table_bloat_approx(
143 OUT approx_free_percent double precision, OUT approx_free_space double precision,
144 OUT dead_tuple_percent double precision, OUT dead_tuple_len double precision) AS
145$$
146 select
147 avg(approx_free_percent)::double precision as approx_free_percent,
148 sum(approx_free_space)::double precision as approx_free_space,
149 avg(dead_tuple_percent)::double precision as dead_tuple_percent,
150 sum(dead_tuple_len)::double precision as dead_tuple_len
151 from
152 pg_class c
153 join
154 pg_namespace n on n.oid = c.relnamespace
155 join lateral pgstattuple_approx(c.oid) on (c.oid not in (select relation from pg_locks where mode = 'AccessExclusiveLock')) -- skip locked tables
156 where
157 relkind in ('r', 'm')
158 and c.relpages >= 128 -- tables >1mb
159 and not n.nspname like any (array[E'pg\\_%', 'information_schema'])
160$$ LANGUAGE sql SECURITY DEFINER;
161$_SQL_$;
162BEGIN
163 IF (regexp_matches(
164 regexp_replace(current_setting('server_version'), '(beta|devel).*', '', 'g'),
165 E'\\d+\\.?\\d+?')
166 )[1]::double precision > 9.4 THEN
167 EXECUTE l_sproc_text;
168
169 EXECUTE 'GRANT EXECUTE ON FUNCTION get_table_bloat_approx() TO pgwatch2;';
170 EXECUTE 'COMMENT ON FUNCTION get_table_bloat_approx() is ''created for pgwatch2''';
171 END IF;
172END;
173$OUTER$;
174COMMIT;