0

I have two node PostgreSQL 11 HA setup as follows.

node1 - hamaster.myorg.net

node2 - haslave.myorg.net

recovery.conf on node2 is as follows:

standby_mode          = 'on'
primary_conninfo      = 'host=hamaster.myorg.net port=5432 user=<user_here> password=<password_here> sslmode=require sslcompression=0'
trigger_file = '/tmp/MasterNow'
recovery_target_timeline = 'latest'

I want to upgrade PostgreSQL to 12 on both the system. I have identified following steps in exact sequence:

  1. Stop PostgreSQL on master node.
  2. Update the RPM (i am using centos 7.8) to PostgreSQL 12 on master node and upgrade the PostgreSQL using pg_upgrade
  3. Start the PostgreSQL on master node.
  4. Stop the PostgreSQL on slave node.
  5. Update the RPM (i am using centos 7.8) to PostgreSQL 12 on slave node and upgrade the PostgreSQL using pg_upgrade
  6. On slave node, copy the contents from recovery.conf to postgresql.conf and remove the recovery.conf. Also create the file standby.signal in data directory on slave server. Rename trigger_file to promote_trigger_file. Remove the line standby_mode = 'on' as this is NOT required by PostgreSQL 12.
  7. Start the PostgreSQL on slave node.

Is there anything missing?

Niraj Nandane
  • 1,318
  • 1
  • 13
  • 24
  • You should adapt `postgresql.conf` manually. Also, you'll have to run `pg_basebackup` to create a new standby, upgrading won't work. – Laurenz Albe Oct 15 '19 at 14:16
  • [The manual](https://www.postgresql.org/docs/current/pgupgrade.html#PGUPGRADE-STEP-REPLICAS) explains the different options you have to upgrade the standby –  Oct 15 '19 at 14:21
  • Running `pg_basebackup` again is like creating the cluster again. This is painful if having large amount of data. Will upgrade NOT work at all ? @Laurenz – Niraj Nandane Oct 15 '19 at 14:38
  • Yes, this is painful, but it is the only way. – Laurenz Albe Oct 15 '19 at 15:03
  • Ok thanks. Any chance to use `pg_rewind` instead of full `pg_basebackup` for standby nodes upgrade? – Niraj Nandane Oct 15 '19 at 15:13
  • [The manual](https://www.postgresql.org/docs/current/pgupgrade.html#PGUPGRADE-STEP-REPLICAS) explains ways other than using `pg_basebackup` for the standby –  Oct 15 '19 at 15:59
  • If `pg_basebackup` is too painful, you may also look into using `rsync` to minimize downtime, but it is a little riskier – richyen Oct 15 '19 at 17:10
  • I'd recommend to set up a new pg 12 cluster and dump data from old to new server. – Elect2 Oct 18 '19 at 09:52
  • @LaurenzAlbe Any new way for upgrading PostgreSQL streaming replication cluster(2-3 node), without using `pg_basebackup` ? – Niraj Nandane Nov 15 '21 at 05:26

0 Answers0