I try on PostgreSQL 9.3 Slave to get ip postgresql master from recovery.conf using non-superuser.
Created FUNCTION f_showfile
CREATE FUNCTION f_showfile(myfile text) RETURNS text AS $x$
BEGIN
RETURN pg_read_file(myfile, 0, 1000000);
END;
$x$ LANGUAGE PLPGSQL SECURITY DEFINER;
Added grant execution to f_showfile for non-superuser
GRANT EXECUTE ON FUNCTION public.f_showfile (text) TO the_user;
Get lines with primary_conninfo string from recovery.conf
SELECT *
FROM regexp_split_to_table(f_showfile('recovery.conf'),'\n') t(a)
WHERE a ~ '^ *primary_conninfo';
Output:
-----------------------------------------------------------------------------------------------------------------------------------------
user=repmgr port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres host=1.1.1.1 application_name='postgresql-93'
But i cannot get host from recovery.conf
My error. How get ip postgresql master from recovery.conf using non-superuser? Thank you
SELECT regexp_split_to_array
(SELECT *
FROM regexp_split_to_table(f_showfile('recovery.conf'),'\n') t(a)
WHERE a ~ '^ *primary_conninfo'),
' ');
ERROR: syntax error at or near "select"
LINE 1: select regexp_split_to_array(select * from regexp_split_to_t...
OR
SELECT *
FROM regexp_split_to_table
(SELECT *
FROM regexp_split_to_table(f_showfile('recovery.conf'),'\n') t(a)
WHERE a ~ '^ *primary_conninfo'),
' ');
ERROR: syntax error at or near "select"
LINE 1: select * from regexp_split_to_table(select * from regexp_spl...
(
,SELECT * FROM regexp_split_to_table (