1

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...
4
  • It seems there is a missing (, SELECT * FROM regexp_split_to_table (
    – McNets
    Apr 15, 2018 at 13:57
  • SELECT * FROM regexp_split_to_table ( (SELECT * FROM regexp_split_to_table(f_showfile('recovery.conf'),'\n') t(a) WHERE a ~ '^ *primary_conninfo'), ' '); Apr 15, 2018 at 14:25
  • dbfiddle.uk/…
    – McNets
    Apr 15, 2018 at 14:45
  • hello! Can you write 1 select query with regexp_split_to_table(f_showfile('recovery.conf') ? without "create table tbl" Apr 16, 2018 at 5:53

2 Answers 2

1

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=#
0

perhaps like this:

select substring(a from ' host=([^ ]*) ')
FROM regexp_split_to_table(f_showfile('postmaster.opts'),'\n') t(a)
WHERE a ~ '^ *primary_conninfo';
2
  • select substring(a from ' host=([^ ]*) ') regexp_split_to_table(f_showfile('postmaster.opts'),'\n') t(a) WHERE a ~ '^ primary_conninfo'; ERROR: syntax error at or near "(" LINE 1: ...ing(a from ' host=([^ ]) ') regexp_split_to_table(f_showfil... Apr 16, 2018 at 5:45
  • oops was missing a FROM
    – Jasen
    Apr 17, 2018 at 2:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.