1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
-- An example of monitoring pgBackRest from within PostgreSQL
--
-- Use copy to export data from the pgBackRest info command into the jsonb
-- type so it can be queried directly by PostgreSQL.
-- Create monitor schema
create schema monitor;
-- Get pgBackRest info in JSON format
create function monitor.pgbackrest_info()
returns jsonb AS $$
declare
data jsonb;
begin
-- Create a temp table to hold the JSON data
create temp table temp_pgbackrest_data (data text);
-- Copy data into the table directly from the pgBackRest info command
copy temp_pgbackrest_data (data)
from program
'pgbackrest --output=json info' (format text);
select replace(temp_pgbackrest_data.data, E'\n', '\n')::jsonb
into data
from temp_pgbackrest_data;
drop table temp_pgbackrest_data;
return data;
end $$ language plpgsql;
|
