Postgresql replication – pg_stat_replication is showing empty columns

postgresqlpostgresql-9.2

I've a postgresql 9.2 streaming replication setup. It appears that the slave is getting the updates from master and is in sync. I've verified it by looking at pg_xlog dir and process list.

$ ps aux | grep 'postgres.*rec'
postgres 26349  2.3 42.9 38814656 18604176 ?   Ss   Sep20  24:06 postgres: startup process   recovering 000000010000026E00000073
postgres 26372  4.9  0.1 38959108 78880 ?      Ss   Sep20  51:27 postgres: wal receiver process   streaming 26E/731E05F0

And the startup logs on the slave also look alright.

2013-09-21 03:02:38 UTC LOG:  database system was shut down in recovery at 2013-09-21 03:02:32 UTC
2013-09-21 03:02:38 UTC LOG:  incomplete startup packet
2013-09-21 03:02:38 UTC FATAL:  the database system is starting up
2013-09-21 03:02:38 UTC LOG:  entering standby mode
2013-09-21 03:02:38 UTC LOG:  redo starts at 26E/71723BB8
2013-09-21 03:02:39 UTC FATAL:  the database system is starting up
2013-09-21 03:02:39 UTC LOG:  consistent recovery state reached at 26E/75059C90
2013-09-21 03:02:39 UTC LOG:  invalid xlog switch record at 26E/75059E98
2013-09-21 03:02:39 UTC LOG:  database system is ready to accept read only connections
2013-09-21 03:02:39 UTC LOG:  streaming replication successfully connected to primary

What worries me is that the pg_stat_replication table on the master. It shows that there is a client connected, but doesn't show anything more.

archive=> select * from pg_stat_replication;
 pid | usesysid |  usename   | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
-----+----------+------------+------------------+-------------+-----------------+-------------+---------------+-------+---------------+----------------+----------------+-----------------+---------------+------------
 999 |    16384 | replicator | walreceiver      |             |                 |             |               |       |               |                |                |                 |               |
(1 row)

Is this the expected behavior? I remember seeing values for client_addr, sent_location, replay_location etc. when I did a test run some time back. Is there anything that I'm missing?

Best Answer

From the prompt, it looks like you are not connecting as a superuser. To view the address information about the connection, you must be connected as superuser.