Query for master in Postgres replication

I'm trying to find out if there is a way to query for the master, from a slave PostgreSQL server that has been set up with server replication.

From a slave, I can:

SELECT pg_is_in_recovery()

And this will give me a 't' result if I'm on a slave and an 'f' result on the master, which is step one.

Next, I'd like to run a query that gives me some information about the master that it's replicating. Preferably an IP address or hostname.

For the record, I can query the master with:

SELECT * from pg_stat_replication

And this will give me information about slaves. I am hoping that there is a reciprocal method for querying a slave.

Is this possible? If so, how?

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:

• I don't guarantee this regex is really 100% perfect, anyone can see a mistake?

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

StackExchange Database Administrators Q#45026, answer score: 9