Re: empty pg_stat_replication when replication works fine?

Streaming replication set-up,

one master, 3 slaves connecting to it.

I expected ps -ef gets all wal-sender processes and SAME information I'll get via select * from pg_stat_replication. 

Instead I observed: 

- pg_stat_replication is empty

- 3 wal-sender processes up and running

- each slave has wal-receiver process running

- replication works (tried to create a table- it appears in all databases)

Question:

- why is pg_stat_replication empty?

Andrej

---------------details

[root@l2bmain ~]# tail /opt/pg_data/postgresql.conf

max_wal_senders = 5

hot_standby = on

wal_keep_segments = 128

archive_command = '/opt/postgres/dbconf/archive_command.sh %p %f'

wal_receiver_status_interval = 2

max_standby_streaming_delay = -1

max_standby_archive_delay = -1

restart_after_crash = off

hot_standby_feedback = on

wal_sender_timeout = 1min

[root@l2bmain ~]# ps f -fu postgres

UID        PID  PPID  C STIME TTY      STAT   TIME CMD

postgres 10797     1  0 15:53 ?        S      0:20 /usr/pgsql-9.3/bin/postgres -D /opt/pg_data -c config_file=/opt/pg_data//postgresql.conf

postgres 10820 10797  0 15:53 ?        Ss     0:00  \_ postgres: logger process

postgres 10823 10797  0 15:53 ?        Ss     0:00  \_ postgres: checkpointer process

postgres 10824 10797  0 15:53 ?        Ss     0:00  \_ postgres: writer process

postgres 10825 10797  0 15:53 ?        Ss     0:00  \_ postgres: wal writer process

postgres 10826 10797  0 15:53 ?        Ss     0:01  \_ postgres: autovacuum launcher process

postgres 10827 10797  0 15:53 ?        Ss     0:00  \_ postgres: archiver process   last was 0000000100000000000000A3.00000028.backup

postgres 10828 10797  0 15:53 ?        Ss     0:03  \_ postgres: stats collector process

postgres 11286 10797  0 15:54 ?        Ss     0:08  \_ postgres: wal sender process pgreplic 192.168.204.12(55231) streaming 0/A401BED8

postgres 11287 10797  0 15:54 ?        Ss     0:06  \_ postgres: wal sender process pgreplic 192.168.204.11(42937) streaming 0/A401BED8

postgres 19322 10797  0 15:58 ?        Ss     0:08  \_ postgres: wal sender process pgreplic 192.168.101.11(52379) streaming 0/A401BED8

postgres 28704 10797  0 18:44 ?        Ss     0:00  \_ postgres: CTSYSTEM lidb 192.168.102.13(58245) idle

postgres  7256 10797  0 18:52 ?        Ss     0:00  \_ postgres: CTSYSTEM lidb 192.168.102.23(55190) idle

postgres  8667 10797  0 18:53 ?        Ss     0:00  \_ postgres: CTSYSTEM lidb 192.168.102.13(58287) idle

[root@l2bmain ~]# psql -U postgres -c "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 | r

eplay_location | sync_priority | sync_state

-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+-------+---------------+----------------+----------------+--

---------------+---------------+------------

(0 rows)

[root@l2bmain ~]# tail /opt/pg_data/pg_log/postgresql-Wed.log

2016-05-25 15:53:56 CEST:@:[8603] LOG:  database system is shut down

2016-05-25 15:53:58 CEST:@:[10821] LOG:  database system was shut down in recovery at 2016-05-25 15:53:56 CEST

2016-05-25 15:53:58 CEST:@:[10821] LOG:  database system was not properly shut down; automatic recovery in progress

2016-05-25 15:53:58 CEST:@:[10821] LOG:  consistent recovery state reached at 0/A2000090

2016-05-25 15:53:58 CEST:@:[10821] LOG:  record with zero length at 0/A2000090

2016-05-25 15:53:58 CEST:@:[10821] LOG:  redo is not required

2016-05-25 15:53:58 CEST:@:[10821] LOG:  MultiXact member wraparound protections are now enabled

2016-05-25 15:53:58 CEST:@:[10797] LOG:  database system is ready to accept connections

2016-05-25 15:53:58 CEST:@:[10826] LOG:  autovacuum launcher started

`

[root@l2bmain ~]# ssh 192.168.101.11

Last login: Wed May 25 22:48:18 2016 from 192.168.101.12

[root@l2amain ~]# ps f -fu postgres

UID        PID  PPID  C STIME TTY      STAT   TIME CMD

postgres  5730     1  0 15:58 ?        S      0:04 /usr/pgsql-9.3/bin/postgres -D /opt/pg_data -c config_file=/opt/pg_data//postgresql.conf

postgres  5754  5730  0 15:58 ?        Ss     0:00  \_ postgres: logger process

postgres  5755  5730  0 15:58 ?        Ss     0:00  \_ postgres: startup process   recovering 0000000100000000000000A4

postgres  5773  5730  0 15:58 ?        Ss     0:12  \_ postgres: wal receiver process   streaming 0/A401C030

postgres  5774  5730  0 15:58 ?        Ss     0:00  \_ postgres: checkpointer process

postgres  5775  5730  0 15:58 ?        Ss     0:00  \_ postgres: writer process

postgres  5776  5730  0 15:58 ?        Ss     0:00  \_ postgres: stats collector process

[root@l2amain ~]# psql -U postgres -c "select pg_is_in_recovery();"

 pg_is_in_recovery

-------------------

 t

(1 row)

[root@l2bmain ~]# ssh 192.168.204.11

Warning: Permanently added '192.168.204.11' (RSA) to the list of known hosts.

Last login: Wed May 25 16:28:49 2016 from 192.168.200.254

[root@l2abrnch ~]# ps f -fu postgres

UID        PID  PPID  C STIME TTY      STAT   TIME CMD

postgres  8174     1  0 15:48 ?        S      0:04 /usr/pgsql-9.3/bin/postgres -D /opt/geo_stdby_data -c config_file=/opt/geo_stdby_data//postgresql.conf

postgres  8195  8174  0 15:48 ?        Ss     0:00  \_ postgres: logger process

postgres  8197  8174  0 15:48 ?        Ss     0:00  \_ postgres: startup process   recovering 0000000100000000000000A4

postgres  8206  8174  0 15:48 ?        Ss     0:00  \_ postgres: checkpointer process

postgres  8207  8174  0 15:48 ?        Ss     0:00  \_ postgres: writer process

postgres  8208  8174  0 15:48 ?        Ss     0:00  \_ postgres: stats collector process

postgres 11414  8174  0 15:53 ?        Ss     0:13  \_ postgres: wal receiver process   streaming 0/A401C0D0

[root@l2bmain ~]# ssh 192.168.204.11

Warning: Permanently added '192.168.204.11' (RSA) to the list of known hosts.

Last login: Wed May 25 16:28:49 2016 from 192.168.200.254

[root@l2abrnch ~]# ps f -fu postgres

UID        PID  PPID  C STIME TTY      STAT   TIME CMD

postgres  8174     1  0 15:48 ?        S      0:04 /usr/pgsql-9.3/bin/postgres -D /opt/geo_stdby_data -c config_file=/opt/geo_stdby_data//postgresql.conf

postgres  8195  8174  0 15:48 ?        Ss     0:00  \_ postgres: logger process

postgres  8197  8174  0 15:48 ?        Ss     0:00  \_ postgres: startup process   recovering 0000000100000000000000A4

postgres  8206  8174  0 15:48 ?        Ss     0:00  \_ postgres: checkpointer process

postgres  8207  8174  0 15:48 ?        Ss     0:00  \_ postgres: writer process

postgres  8208  8174  0 15:48 ?        Ss     0:00  \_ postgres: stats collector process

postgres 11414  8174  0 15:53 ?        Ss     0:13  \_ postgres: wal receiver process   streaming 0/A401C0D0

[root@l2abrnch ~]# psql -U postgres -c "select pg_is_in_recovery();"

 pg_is_in_recovery

-------------------

 t

(1 row)

[root@l2abrnch ~]# logout

Connection to 192.168.204.11 closed.

[root@l2bmain ~]# ssh 192.168.204.12

Warning: Permanently added '192.168.204.12' (RSA) to the list of known hosts.

Last login: Wed May 25 14:58:03 2016 from 192.168.200.254

[root@l2bbrnch ~]# ps f -fu postgres

UID        PID  PPID  C STIME TTY      STAT   TIME CMD

postgres 22885     1  0 15:48 ?        S      0:00 /usr/pgsql-9.3/bin/postgres -D /opt/geo_stdby_data -c config_file=/opt/geo_stdby_data//postgresql.conf

postgres 22913 22885  0 15:48 ?        Ss     0:00  \_ postgres: logger process

postgres 22914 22885  0 15:48 ?        Ss     0:00  \_ postgres: startup process   recovering 0000000100000000000000A4

postgres 22917 22885  0 15:48 ?        Ss     0:00  \_ postgres: checkpointer process

postgres 22918 22885  0 15:48 ?        Ss     0:00  \_ postgres: writer process

postgres 22919 22885  0 15:48 ?        Ss     0:00  \_ postgres: stats collector process

postgres 26163 22885  0 15:54 ?        Ss     0:13  \_ postgres: wal receiver process   streaming 0/A401C0D0