|
1 | 1 | -- Initialize target database for monitoring |
2 | 2 | -- Enable pg_stat_statements extension for query monitoring |
3 | | -CREATE EXTENSION IF NOT EXISTS pg_stat_statements; |
| 3 | +create extension if not exists pg_stat_statements; |
4 | 4 |
|
5 | 5 | -- Create a sample table for demonstration |
6 | | -CREATETABLEIF NOT EXISTS sample_data ( |
7 | | - idSERIALPRIMARY KEY, |
8 | | - nameVARCHAR(100), |
9 | | - created_atTIMESTAMP DEFAULTCURRENT_TIMESTAMP |
| 6 | +createtableif not exists sample_data ( |
| 7 | + idserialprimary key, |
| 8 | + namevarchar(100), |
| 9 | + created_attimestamp defaultcurrent_timestamp |
10 | 10 | ); |
11 | 11 |
|
12 | 12 | -- Insert some sample data |
13 | | -INSERT INTO sample_data (name)VALUES |
| 13 | +insert into sample_data (name)values |
14 | 14 | ('Sample Record 1'), |
15 | 15 | ('Sample Record 2'), |
16 | 16 | ('Sample Record 3'); |
17 | 17 |
|
18 | 18 | -- Create a user for PGWatch monitoring |
19 | | -CREATEUSERmonitorWITH PASSWORD'monitor_pass'; |
20 | | -GRANT CONNECTON DATABASE target_databaseTO monitor; |
21 | | -GRANT USAGEON SCHEMA publicTO monitor; |
| 19 | +createusermonitorwith password'monitor_pass'; |
| 20 | +grant connecton database target_databaseto monitor; |
| 21 | +grant usageon schema publicto monitor; |
22 | 22 |
|
23 | 23 | -- Create a public view for pg_statistic access |
24 | | -CREATE OR REPLACEVIEWpublic.pg_statisticAS |
25 | | -SELECT |
| 24 | +create or replaceviewpublic.pg_statisticas |
| 25 | +select |
26 | 26 | n.nspnameas schemaname, |
27 | 27 | c.relnameas tablename, |
28 | 28 | a.attname, |
29 | 29 | s.stanullfracas null_frac, |
30 | 30 | s.stawidthas avg_width, |
31 | 31 | falseas inherited |
32 | | -FROM pg_statistic s |
33 | | -JOIN pg_class cONc.oid=s.starelid |
34 | | -JOIN pg_namespace nONn.oid=c.relnamespace |
35 | | -JOIN pg_attribute aONa.attrelid=s.starelidANDa.attnum=s.staattnum |
36 | | -WHEREa.attnum>0AND NOTa.attisdropped; |
| 32 | +from pg_statistic s |
| 33 | +join pg_class conc.oid=s.starelid |
| 34 | +join pg_namespace nonn.oid=c.relnamespace |
| 35 | +join pg_attribute aona.attrelid=s.starelidanda.attnum=s.staattnum |
| 36 | +wherea.attnum>0and nota.attisdropped; |
37 | 37 |
|
38 | 38 | -- Grant specific access instead of all tables |
39 | | -GRANTSELECTONpublic.pg_statisticTO pg_monitor; |
| 39 | +grantselectonpublic.pg_statisticto pg_monitor; |
40 | 40 |
|
41 | 41 | -- Grant access to monitoring views |
42 | | -GRANTSELECTON pg_stat_statementsTO monitor; |
43 | | -GRANTSELECTON pg_stat_databaseTO monitor; |
44 | | -GRANTSELECTON pg_stat_user_tablesTO monitor; |
| 42 | +grantselecton pg_stat_statementsto monitor; |
| 43 | +grantselecton pg_stat_databaseto monitor; |
| 44 | +grantselecton pg_stat_user_tablesto monitor; |
45 | 45 | -- Grant pg_monitor role to monitor user for enhanced monitoring capabilities |
46 | | -GRANT pg_monitorTO monitor; |
47 | | -GRANT EXECUTEON FUNCTION pg_stat_file(text)TO monitor; |
48 | | -GRANT EXECUTEON FUNCTION pg_stat_file(text,boolean)TO monitor; |
49 | | -GRANT EXECUTEON FUNCTION pg_ls_dir(text)TO monitor; |
50 | | -GRANT EXECUTEON FUNCTION pg_ls_dir(text,boolean,boolean)TO monitor; |
| 46 | +grant pg_monitorto monitor; |
| 47 | +grant executeon function pg_stat_file(text)to monitor; |
| 48 | +grant executeon function pg_stat_file(text,boolean)to monitor; |
| 49 | +grant executeon function pg_ls_dir(text)to monitor; |
| 50 | +grant executeon function pg_ls_dir(text,boolean,boolean)to monitor; |
51 | 51 | -- Set search path for the monitor user |
52 | | -ALTERUSER monitorSET search_path="$user", public, pg_catalog; |
| 52 | +alteruser monitorset search_path="$user", public, pg_catalog; |