Query for master in Postgres replication

There is no way through SQL to get master information from a slave, as Craig said.

One solution I came up once was to use pg_read_file to get the contents of recovery.conf file, as:

SELECT pg_read_file('recovery.conf');

With that information, we can easily parse it using a regular expression to get all config/values. I used the following to get it done:

SELECT DISTINCT ON (rm[1]) rm[1] AS name, coalesce(replace(rm[4], '''''', ''''), rm[2]) AS setting FROM (
SELECT row_number() OVER() rn, confs, regexp_matches(confs, '^[\s]*([a-z_]+)[\s]*=[\s]*([A-Za-z_\200-\377]([-A-Za-z_0-9\200-\377._:/]*)|''(([^''\n]|\\.|'''')*)'')') AS rm
FROM regexp_split_to_table(pg_read_file('recovery.conf'), '\n') AS confs
) AS recovery_confs
ORDER BY rm[1], rn DESC;

With that, it is just a matter of choosing your config:

WITH recconfs AS (
    SELECT DISTINCT ON (rm[1]) rm[1] AS name, coalesce(replace(rm[4], '''''', ''''), rm[2]) AS setting FROM (
    SELECT row_number() OVER() rn, confs, regexp_matches(confs, '^[\s]*([a-z_]+)[\s]*=[\s]*([A-Za-z_\200-\377]([-A-Za-z_0-9\200-\377._:/]*)|''(([^''\n]|\\.|'''')*)'')') AS rm
    FROM regexp_split_to_table(pg_read_file('recovery.conf'), '\n') AS confs
    ) AS recovery_confs
    ORDER BY rm[1], rn DESC
)
SELECT setting FROM recconfs WHERE name = 'primary_conninfo';

The regex was adapted from PG's source file guc-file.l, but I did not used all the possible variants, which means that it will only work for strings (unquoted or with single quotes), which is Ok for primary_conninfo.

There are, in my opnion, two problems with this approach:

1. I don't guarantee this regex is really 100% perfect, anyone can see a mistake?
2. Only a superuser can use pg_read_file, but that is not really a problem, as it is expected or you can wrap it on a function (I did that, because I wanted a non-superuser to check it, in my case was a REPLICATION user).

At present (Pg 9.2 and 9.3beta) as far as I know no mechanism for asking a replica about its master is provided. All the replica knows is how to connect to the master via settings in recovery.conf, which are not accessible from SQL.

There's been work ongoing to merge recovery.conf into postgresql.conf, which would make it possible to see information about the upstream server from pg_catalog.pg_settings - assuming the security issues with that get worked out.