Postgresql – Get ip postgresql master from recovery.conf using non-superuser

postgresql

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...

Best Answer

IMHO and as I pointed out in my comments I think there is a missing parentheses in your command. Just after first SELECT regexp_split_to_array

I've tested on a postgres server (a turnkey VM) using a copy of a recovery.conf file, and this is the result:

root=# SELECT regexp_split_to_array (
  (SELECT *
   FROM regexp_split_to_table(f_showfile('recovery.conf.sample'),'\n') t(a)
   WHERE a ~ '^ *primary_conninfo'),
       ' ');
                                   regexp_split_to_array
--------------------------------------------------------------------------------------------
 {primary_conninfo,=,'user=repmrg,port=5432,sslmode=prefer,sslcompression=1,host=10.0.0.1'}
(1 row)

Using unnest:

root=# SELECT unnest(regexp_split_to_array (
  (SELECT *
   FROM regexp_split_to_table(f_showfile('recovery.conf.sample'),'\n') t(a)
   WHERE a ~ '^ *primary_conninfo'),
       ' '));
      unnest
------------------
 primary_conninfo
 =
 'user=repmrg
 port=5432
 sslmode=prefer
 sslcompression=1
 host=10.0.0.1'
(7 rows)

Filtering by host:

root=# SELECT *
root-# FROM   (SELECT unnest(regexp_split_to_array (
root(#                       (SELECT *
root(#                        FROM regexp_split_to_table(f_showfile('recovery.conf.sample'),'\n') t(a)
root(#                        WHERE a ~ '^ *primary_conninfo'), ' ')) as elem) T
root-# WHERE elem LIKE '%host%'
root-# ;
      elem
----------------
 host=10.0.0.1'
(1 row)

root=#
Related Question