I am in the process of writing a bash script to upgrade Postgres servers in our org from 9.6 to 10.4, under Ubuntu trusty. I've got the master upgrade done, but we have instances in dev, staging and production with and without standby servers. I know how to upgrade a standby server, but I am struggling with a generic way to determine if a master has standby's, and if so, their hostnames. Anybody done this already and can share some light on this?
On the master, you can see the listening standby servers in pg_stat_replication:
select client_addr, client_hostname from pg_stat_replication
On the standby, you can see the master in pg_stat_wal_receiver:
select conninfo from pg_stat_wal_receiver