Header Shadow Image


FATAL: remaining connection slots are reserved for non-replication superuser connections

Getting this?

FATAL:  remaining connection slots are reserved for non-replication superuser connections

Fix that by updating the Patroni configuration like like so:

[root@psql01 log]# patronictl -c /etc/patroni.yml edit-config postgres

+++
@@ -1,9 +1,10 @@
 loop_wait: 10
 maximum_lag_on_failover: 1048576
 postgresql:
+  parameters:
–  max_connections: 256
+    max_connections: 256
–  max_replication_slots: 64
+    max_replication_slots: 64
–  max_wal_senders: 32
+    max_wal_senders: 32
   use_pg_rewind: true
 retry_timeout: 10
 ttl: 30

Apply these changes? [y/N]: y
Configuration changed
[root@psql01 log]#
[root@psql01 log]#
[root@psql01 log]# patronictl -c /etc/patroni.yml restart postgres
+———-+————-+—————+——–+———+———–+
| Cluster  |    Member   |      Host     |  Role  |  State  | Lag in MB |
+———-+————-+—————+——–+———+———–+
| postgres | postgresql0 | 192.168.0.108 | Leader | running |       0.0 |
| postgres | postgresql1 | 192.168.0.124 |        | running |       0.0 |
| postgres | postgresql2 | 192.168.0.118 |        | running |       0.0 |
+———-+————-+—————+——–+———+———–+
Are you sure you want to restart members postgresql0, postgresql1, postgresql2? [y/N]: y
Restart if the PostgreSQL version is less than provided (e.g. 9.5.2)  []:
When should the restart take place (e.g. 2015-10-01T14:30)  [now]:
Success: restart on member postgresql0
Success: restart on member postgresql1
Success: restart on member postgresql2
[root@psql01 log]# sudo su – postgres
Last login: Sat Sep 14 09:15:34 EDT 2019 on pts/0
-bash-4.2$ psql -h psql-c01 -p 5432 -W
Password:
psql (10.5)
Type "help" for help.

postgres=#
postgres=#
postgres=#
postgres=# show max_connections; show  max_replication_slots;
 max_connections
—————–
 256
(1 row)

 max_replication_slots
———————–
 64
(1 row)

postgres=#
 

Keep in mind that cluster name above is your scope from the config file:

[root@psql01 patroni]# cat /etc/patroni.yml
scope: postgres

Alternately, update the PostgresSQL settings with the above, if you're not running Patroni.  Verify status:

[root@psql01 ~]# patronictl -c /etc/patroni.yml list
+———-+————-+—————+——–+———+———–+
| Cluster  |    Member   |      Host     |  Role  |  State  | Lag in MB |
+———-+————-+—————+——–+———+———–+
| postgres | postgresql0 | 192.168.0.108 |        | running |       0.0 |
| postgres | postgresql1 | 192.168.0.124 | Leader | running |       0.0 |
| postgres | postgresql2 | 192.168.0.118 |        | running |       0.0 |
+———-+————-+—————+——–+———+———–+
[root@psql01 ~]#

 

Cheers,
TK

REF: My post on the project page: https://github.com/zalando/patroni/issues/1177


     
  Copyright © 2003 - 2013 Tom Kacperski (microdevsys.com). All rights reserved.

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 Unported License