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?

  • The documentation says that both client_addr and client_hostname are NULL when the client connects via Unix sockets. However, client_port is supposed to be -1 instead of NULL. – dezso Sep 21 '13 at 8:20
  • The slave is connected to the master using INET sockets only as the master is on a different node. verified it by looking at netstat output on master. – Anand Chitipothu Sep 21 '13 at 8:37

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.