Search Postgresql Archives

empty pg_stat_replication when replication works fine?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux