Detect if backend is primary or standby

To detect whether a PostgreSQL instance is a primary (master) or standby (slave), execute pg_is_in_recovery(); TRUE indicates that the backend is 'recovering', i.e. replaying WAL files, and is therefore a standby; FALSE therefore indicates the instance is a primary (or standalone instance).

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

Alternatively, the presence of a transaction log location returned by pg_last_wal_replay_lsn() (PostgreSQL 9.6 and earlier: pg_last_xlog_replay_location()) can also be used:

postgres=# SELECT pg_last_wal_replay_lsn () IS NOT NULL;
 ?column? 
----------
 t
(1 row)