Determining Postgres master information

cat << EOF | curl -X PUT -d @- uberleet.org

I have to do this more often then not, so I’m going to write it down. Quickly determine the pg master by running SELECT pg_is_in_recovery() you get output similar to

 pg_is_in_recovery 
-------------------
 t
(1 row)

A result of ‘T’ means the server is a slave. ‘F’ means the server is a master.

As a bonus, you can also get the masters information from the slave, with a bastardized query like this:

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;

to get output like

           name           |                                  setting                                   
-------------------------+----------------------------------------------------------------------------
 archive_cleanup_command | /usr/pgsql-9.5/bin/pg_archivecleanup /var/lib/pgsql/current/data/pg_wal %r
 primary_conninfo        | host=192.168.0.2 port=5432 user=repmgr
 standby_mode            | on
 trigger_file            | /tmp/psql.trigger
(4 rows)

If you want non superusers to be able to grab this data you can put this query into a Postgres function (which is executed as root) and grant permissions to the function for users.

EOF
POST_CATEGORIES = ['pgsql','sql']
POST_TAGS = ''[6:].split(', ')

Leave a Reply

Your email address will not be published. Required fields are marked *