3

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?

Erik A
  • 31,639
  • 12
  • 42
  • 67
Juan Jimenez
  • 443
  • 4
  • 18

1 Answers1

4

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
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 1
    `pg_stat_replication` issues on the master, added the standby equivalent to the answer – Andomar May 29 '18 at 18:02
  • Bingo. That's what I needed. I can now use this to get what I want, a list of IP's for Salt minions that are running as database replicas. Now I can automate the upgrades. :) `sudo -u postgres psql -t -c "select to_json(client_addr) from pg_stat_replication;"` – Juan Jimenez May 30 '18 at 11:44
  • Huh. So now I know I have the two servers running with one as master and the other as a hot standby. pg_basebackup ran fine with the replication credentials. But the select ... from pg_stat_replication returns nothing. Zip. ??? – Juan Jimenez Jun 01 '18 at 13:23
  • 2
    `pg_stat_replication` shows the active listeners. Did you start the sever with a `recovery.conf` that instructs it to start as a hot standby? – Andomar Jun 01 '18 at 15:10
  • Ok, so that's the next step. LOL! :) – Juan Jimenez Jun 02 '18 at 16:23
  • Everything works now, but that's just the base state. On the replicas, if I do an in-place upgrade of the database, and the config files are not touched, I should not have to do anything else? Or should I rm the files in the data directory (except for recovery.conf), do a pg_base_backup, restore the recovery.conf file and restart the server? – Juan Jimenez Jun 04 '18 at 09:16
  • The recovery.conf is always required. Not sure when a base backup is required, definitely not within a major version (say 9.4.5 to 9.4.6) – Andomar Jun 04 '18 at 11:25
  • Because it's a major version change the replicas have to be baselined again using either rsync or pg_basebackup. What I did find is that pg_upgradecluster using in-place upgrade does not work correctly. When it was finished I couldn't do pg_basebackup, it would only pick up some of the data. – Juan Jimenez Jun 05 '18 at 13:27
  • Yeah, I never do in place upgrade. Export using `pg_dumpall` and import into the new version. – Andomar Jun 06 '18 at 10:37
  • I ended up using pg_upgrade and --j 4. On my environment it's handling 1gb of data about every minute or so (45gb in 51min), not counting redoing the optimization data. The diff is that pg_upgrade wants the options in excruciating detail. But I'm done now. thanks! – Juan Jimenez Jun 07 '18 at 11:51