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...
  • It seems there is a missing (, SELECT * FROM regexp_split_to_table ( – McNets Apr 15 '18 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'), ' '); – Anton Patsev Apr 15 '18 at 14:25
  • dbfiddle.uk/… – McNets Apr 15 '18 at 14:45
  • hello! Can you write 1 select query with regexp_split_to_table(f_showfile('recovery.conf') ? without "create table tbl" – Anton Patsev Apr 16 '18 at 5:53
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';
  • 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... – Anton Patsev Apr 16 '18 at 5:45
  • oops was missing a FROM – Jasen Apr 17 '18 at 2:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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