How to set max_connections and max_replication_slots?

See original GitHub issue

How to set max_connections and max_replication_slots? I’ve tried numerous ways, but no luck. Of course I googled as well but none of the suggestions I’m seeing are helping right now.

[root@psql02 patroni]# grep -Ei "max_replication_slots|max_connections" * 2>/dev/null
postgresql.base.conf:max_connections = 256                      # (change requires restart)
postgresql.base.conf:#max_replication_slots = 64        # max number of replication slots
postgresql.base.conf.backup:max_connections = 256                       # (change requires restart)
postgresql.base.conf.backup:#max_replication_slots = 64 # max number of replication slots
postgresql.conf:max_connections = '100'
postgresql.conf:max_replication_slots = '10'
postgresql.conf.backup:max_connections = '100'
postgresql.conf.backup:max_replication_slots = '10'
[root@psql02 patroni]#

Changes to /etc/patroni.xml don’t work either.

scope: postgres
namespace: /db/
name: postgresql0

restapi:
    listen: 192.168.0.108:8008
    connect_address: 192.168.0.108:8008

etcd:
    host: 192.168.0.108:2379

bootstrap:
    dcs:
        ttl: 30
        loop_wait: 10
        retry_timeout: 10
        maximum_lag_on_failover: 1048576
        postgresql:
            use_pg_rewind: true

    initdb:
    - encoding: UTF8
    - data-checksums

    pg_hba:
    - host replication replicator 127.0.0.1/32 md5
    - host replication replicator 192.168.0.108/0 md5
    - host replication replicator 192.168.0.124/0 md5
    - host replication replicator 192.168.0.118/0 md5
    - host all all 0.0.0.0/0 md5

    users:
        admin:
            password: admin
            options:
                - createrole
                - createdb

postgresql:
    listen: 192.168.0.108:5432
    bin_dir: /usr/pgsql-10/bin
    connect_address: 192.168.0.108:5432
    data_dir: /data/patroni
    pgpass: /tmp/pgpass
    unix_socket_directories: /data/patroni
    authentication:
        replication:
            username: replicator
            password: rep-pass
        superuser:
            username: postgres
            password: <SECRET>
    parameters:
        unix_socket_directories: '.'
        max_connections: '256'
        max_replication_slots: '64'

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false

Trying to set it dynamically, fails with:

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

Traceback (most recent call last):
  File "/usr/bin/patronictl", line 11, in <module>
    load_entry_point('patroni==1.5.0', 'console_scripts', 'patronictl')()
  File "/usr/lib64/python2.7/site-packages/click/core.py", line 764, in __call__
    return self.main(*args, **kwargs)
  File "/usr/lib64/python2.7/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/usr/lib64/python2.7/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/lib64/python2.7/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/lib64/python2.7/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/usr/lib64/python2.7/site-packages/click/decorators.py", line 27, in new_func
    return f(get_current_context().obj, *args, **kwargs)
  File "/usr/lib/python2.7/site-packages/patroni/ctl.py", line 1114, in edit_config
    before_editing = format_config_for_editing(cluster.config.data)
AttributeError: 'NoneType' object has no attribute 'data'
[root@psql01 patroni]#

Appreciate any tips.

Issue Analytics

  • State:closed
  • Created 4 years ago
  • Comments:9

github_iconTop GitHub Comments

1reaction
CyberDem0ncommented, Sep 14, 2019

That’s because your config is wrong. It has to be postgresql.parameters.max_connections, while you have postgresql.max_connections.

0reactions
tomkcprcommented, Sep 14, 2019

Perfect. Thank you. Worked like a charm.

[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=#

Read more comments on GitHub >

github_iconTop Results From Across the Web

[bitnami/postgres-ha] - Having multiple primaries after killing ...
bitnami/postgresql-repmgr:12.6.0-debian-10-r85; Install helm chart with ... but some parameters are not set accordingly, i.e maxReplicationSlots, audit.
Read more >
The IBM Cloud Databases API | IBM Cloud API Docs
The API is designed to provide users and developers the ability to examine their database deployments, upgrade deployments, manage users, manage connections, ...
Read more >
aiven.getPg - Pulumi
Documentation for the aiven.getPg function with examples, input properties, output properties, and supporting types.
Read more >
API Reference
Max Connections. The maximum number of max_connections is based on plan size ... Max Connections. The maximum number of max_connections is based on...
Read more >
Настройки PostgreSQL | Yandex Cloud - Документация
Max connections. ... которые позволяют изменять определения структур данных ( CREATE , ALTER , DROP и т. п.) ... Max replication slots Консоль...
Read more >

github_iconTop Related Medium Post

No results found

github_iconTop Related StackOverflow Question

No results found

github_iconTroubleshoot Live Code

Lightrun enables developers to add logs, metrics and snapshots to live code - no restarts or redeploys required.
Start Free

github_iconTop Related Reddit Thread

No results found

github_iconTop Related Hackernoon Post

No results found

github_iconTop Related Tweet

No results found

github_iconTop Related Dev.to Post

No results found

github_iconTop Related Hashnode Post

No results found