1

I have three node cluster.

Now, I want to reduce the max_connections field from 300 to 100. I have changed the value in both master and replica in postgresql.conf file. I have restarted my master first than the other replica nodes. everything seems ok in master but replicas are shutting down automatically.

Here is the error: hot standby is not possible because max_connections = 100 is a lower setting than on the master server (its value was 300)

I have found a solution where need to start as hot_standby=off.

Is there any other solution rather than this?

Emon46
  • 1,506
  • 7
  • 14
  • 1
    Did you changed it to 100 on both master and replica? – pgyogesh May 19 '21 at 04:43
  • setting hot_standby to `off` will not allow you to query(read) your replica. – pgyogesh May 19 '21 at 04:44
  • yes, i have changed to 100 for both master and replica. > setting hot_standby to off will not allow you to query(read) your replica. thats why i need the value `on` – Emon46 May 19 '21 at 04:45

2 Answers2

1

As you are changing max_connections,

Stop all instances, change max_connections setting postgresql.conf in all three nodes.

  1. Start master
  2. Then start replica
pgyogesh
  • 342
  • 2
  • 13
0

So Basically what happened we can't start Replica with less max_connections value than Primary when we have set hot_standby = on.

Though i have updated The Primary server's max_connections The information didn't arrive in Replica side. Normally, Primary server's config changes are notified with wal log changes. For this reason, after restarting the primary with lower max_connections we need to wait for write operations and then change the Replica's max_connecions.

But i think this one is not a feasible solution.

Better Solution:

  1. Start the Primary with lower max_connections
  2. Start replica's with hot_standby = off (if the server stucked in starting state, after few second don't wait for start)
  3. Shut down the replica
  4. Start replica's with hot_standby = on
Emon46
  • 1,506
  • 7
  • 14