Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How to set max_connections and max_replication_slots? #1177

Closed
tomkcpr opened this issue Sep 13, 2019 · 9 comments

Comments

@tomkcpr
Copy link

commented Sep 13, 2019

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.

@CyberDem0n

This comment has been minimized.

Copy link
Member

commented Sep 13, 2019

patronictl -c /etc/patroni.yml edit-config postgresql0
...
AttributeError: 'NoneType' object has no attribute 'data'

That's because you are using it wrongly.
You have to provide the cluster name (scope), but you provide a node name instead.

@tomkcpr

This comment has been minimized.

Copy link
Author

commented Sep 14, 2019

Allright. So the command works now but try as I might, no combination changes the settings:

[root@psql01 patroni]# sudo su - postgres
Last login: Fri Sep 13 22:45:46 EDT 2019 on pts/0
-bash-4.2$ psql -h psql-c01 -p 5432 -W
Password:
psql (10.5)
Type "help" for help.

postgres=# show max_connections; show  max_replication_slots;
 max_connections
-----------------
 100
(1 row)

 max_replication_slots
-----------------------
 10
(1 row)

postgres=# \q
-bash-4.2$
-bash-4.2$
-bash-4.2$ logout
[root@psql01 patroni]#
[root@psql01 patroni]# patronictl -c /etc/patroni.yml edit-config postgres

Tried numerous combinations, with '256' and just 256, inside and outside the postgresql: block. No luck. Last config is as follows. Same results as above.

[root@psql01 patroni]# patronictl -c /etc/patroni.yml edit-config postgres
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  max_connections: 256
  max_replication_slots: 64
  max_wal_senders: 32
  use_pg_rewind: true
retry_timeout: 10
ttl: 30

Directly fails as well:

postgres=# alter system set max_replication_slots = '64'; alter system set max_connections = '256'; show max_connections; show  max_replication_slots;
ALTER SYSTEM
ALTER SYSTEM
 max_connections
-----------------
 100
(1 row)

 max_replication_slots
-----------------------
 10
(1 row)

postgres=#
@CyberDem0n

This comment has been minimized.

Copy link
Member

commented Sep 14, 2019

Change of max_connections and max_replication_slots requires a restart of postgres.
After doing patronictl edit-config wait for 10 seconds and run patronictl list, it will give you a hint that restart is pending. Patroni NEVER restarts postgres on its own, you should do it, patronictl restart is your friend to help with restarts.

P.S. since Patroni is using command-line arguments to pass max_connections, whatever you specified in ALTER SYSTEM will never be used due to the higher order of precedence of command-line.
P.P.S. If you change some other param which requires a restart and is not enforced by Patroni with the help ALTER SYSTEM you still need to restart the postgres!

@tomkcpr

This comment has been minimized.

Copy link
Author

commented Sep 14, 2019

Forgot to mention that I restarted multiple times using systemctl stop patroni on all nodes. Then started them up one by one. No effect. This command fails.

[root@psql01 patroni]# patronictl restart postgres
Error: 'Can not find suitable configuration of distributed configuration store\nAvailable implementations: etcd'
[root@psql01 patroni]#

No more indication as to why it threw that. Decided to check if it has the module installed:

[root@psql01 patroni]# pip install patroni[etcd]
Requirement already satisfied: patroni[etcd] in /usr/lib/python2.7/site-packages (1.5.0)
Requirement already satisfied: urllib3!=1.21,>=1.19.1 in /usr/lib/python2.7/site-packages (from patroni[etcd]) (1.24)
Requirement already satisfied: psycopg2>=2.5.4 in /usr/lib64/python2.7/site-packages (from patroni[etcd]) (2.7.5)
Requirement already satisfied: PyYAML in /usr/lib64/python2.7/site-packages (from patroni[etcd]) (3.13)
Requirement already satisfied: requests in /usr/lib/python2.7/site-packages (from patroni[etcd]) (2.6.0)
Requirement already satisfied: six>=1.7 in /usr/lib/python2.7/site-packages (from patroni[etcd]) (1.9.0)
Requirement already satisfied: click>=4.1 in /usr/lib64/python2.7/site-packages (from patroni[etcd]) (7.0)
Requirement already satisfied: prettytable>=0.7 in /usr/lib/python2.7/site-packages (from patroni[etcd]) (0.7.2)
Requirement already satisfied: tzlocal in /usr/lib/python2.7/site-packages (from patroni[etcd]) (1.5.1)
Requirement already satisfied: python-dateutil in /usr/lib/python2.7/site-packages (from patroni[etcd]) (1.5)
Requirement already satisfied: psutil in /usr/lib64/python2.7/site-packages (from patroni[etcd]) (5.4.7)
Requirement already satisfied: cdiff in /usr/lib/python2.7/site-packages (from patroni[etcd]) (1.0)
Requirement already satisfied: python-etcd<0.5,>=0.4.3 in /usr/lib/python2.7/site-packages (from patroni[etcd]) (0.4.5)
Requirement already satisfied: pytz in /usr/lib/python2.7/site-packages (from tzlocal->patroni[etcd]) (2018.5)
Requirement already satisfied: dnspython>=1.13.0 in /usr/lib/python2.7/site-packages (from python-etcd<0.5,>=0.4.3->patroni[etcd]) (1.15.0)
You are using pip version 18.1, however version 19.2.3 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
[root@psql01 patroni]#

Which it did. So couldn't figure out why it threw that at first so I decided to just upgrade pip altogether on all the nodes to get rid of the warning and see if it helps:

[root@psql03 patroni]# pip install --upgrade pip
Collecting pip
  Downloading https://files.pythonhosted.org/packages/30/db/9e38760b32e3e7f40cce46dd5fb107b8c73840df38f0046d8e6514e675a1/pip-19.2.3-py2.py3-none-any.whl (1.4MB)
    100% |################################| 1.4MB 317kB/s
Installing collected packages: pip
  Found existing installation: pip 8.1.2
    Uninstalling pip-8.1.2:
      Successfully uninstalled pip-8.1.2
Successfully installed pip-19.2.3
[root@psql03 patroni]#

Tried it again, no luck:

[root@psql01 patroni]# patronictl restart postgres
Error: 'Can not find suitable configuration of distributed configuration store\nAvailable implementations: etcd'
[root@psql01 patroni]#

All three nodes have the same config:

[root@psql03 patroni]# cat ./patroni.dynamic.json|tr ',' '\n'
{"maximum_lag_on_failover": 1048576
 "retry_timeout": 10
 "postgresql": {"max_wal_senders": 32
 "max_replication_slots": 64
 "use_pg_rewind": true
 "max_connections": 256}
 "loop_wait": 10
 "ttl": 30}[root@psql03 patroni]#

[root@psql02 patroni]# cat ./patroni.dynamic.json|tr ',' '\n'
{"maximum_lag_on_failover": 1048576
 "retry_timeout": 10
 "postgresql": {"max_wal_senders": 32
 "max_replication_slots": 64
 "use_pg_rewind": true
 "max_connections": 256}
 "loop_wait": 10
 "ttl": 30}[root@psql02 patroni]#

[root@psql01 patroni]# cat ./patroni.dynamic.json|tr ',' '\n'
{"maximum_lag_on_failover": 1048576
 "retry_timeout": 10
 "postgresql": {"max_wal_senders": 32
 "max_replication_slots": 64
 "use_pg_rewind": true
 "max_connections": 256}
 "loop_wait": 10
 "ttl": 30}[root@psql01 patroni]#
[root@psql01 patroni]#

I'll continue reading on this however please share any suggestions you have on this.

Cheers,
TK

@tomkcpr

This comment has been minimized.

Copy link
Author

commented Sep 14, 2019

Well now wasn't that silly. ;)

Tweaked my command line to provide the config file (I guess it doesn't detect it by default.), restarted it, this time it worked to restart but no luck setting the max connections anyway:

[root@psql01 patroni]#
[root@psql01 patroni]# 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 patroni]#
[root@psql01 patroni]#
[root@psql01 patroni]# patronictl -c /etc/patroni.yml list
+----------+-------------+---------------+--------+---------+-----------+
| 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 |
+----------+-------------+---------------+--------+---------+-----------+
[root@psql01 patroni]# sudo su - postgres
Last login: Fri Sep 13 22:55:50 EDT 2019 on pts/0
-bash-4.2$ psql -h psql-c01 -p 5432 -W
Password:
psql (10.5)
Type "help" for help.

postgres=# show max_connections; show  max_replication_slots;
 max_connections
-----------------
 100
(1 row)

 max_replication_slots
-----------------------
 10
(1 row)

postgres=#

@tomkcpr

This comment has been minimized.

Copy link
Author

commented Sep 14, 2019

Current state of my config is:

[root@psql01 patroni]# patronictl -c /etc/patroni.yml edit-config postgres
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  max_connections: 256
  max_replication_slots: 64
  max_wal_senders: 32
  use_pg_rewind: true
retry_timeout: 10
ttl: 30

@tomkcpr

This comment has been minimized.

Copy link
Author

commented Sep 14, 2019

If it helps:

[root@psql01 patroni]# grep -Ei max_replication_slots * 2>/dev/null
backup-postgresql.conf:max_replication_slots = '10'
patroni.dynamic.json:{"maximum_lag_on_failover": 1048576, "retry_timeout": 10, "postgresql": {"max_wal_senders": 32, "max_replication_slots": 64, "use_pg_rewind": true, "max_connections": 256}, "loop_wait": 10, "ttl": 30}
postgresql.auto.conf:max_replication_slots = '64'
postgresql.base.conf:#max_replication_slots = 64        # max number of replication slots
postgresql.base.conf.backup:#max_replication_slots = 64 # max number of replication slots
postgresql.conf:max_replication_slots = '10'
postgresql.conf.backup:max_replication_slots = '10'
postmaster.opts:/usr/pgsql-10/bin/postgres "-D" "/data/patroni" "--config-file=/data/patroni/postgresql.conf" "--hot_standby=on" "--listen_addresses=192.168.0.108" "--max_worker_processes=8" "--max_locks_per_transaction=64" "--wal_level=replica" "--cluster_name=postgres" "--wal_log_hints=on" "--max_wal_senders=10" "--track_commit_timestamp=off" "--max_prepared_transactions=0" "--port=5432" "--max_replication_slots=10" "--max_connections=100"
[root@psql01 patroni]#

[root@psql02 patroni]# grep -Ei max_replication_slots * 2>/dev/null
backup-postgresql.conf:max_replication_slots = '10'
patroni.dynamic.json:{"maximum_lag_on_failover": 1048576, "retry_timeout": 10, "postgresql": {"max_wal_senders": 32, "max_replication_slots": 64, "use_pg_rewind": true, "max_connections": 256}, "loop_wait": 10, "ttl": 30}
postgresql.base.conf:#max_replication_slots = 64        # max number of replication slots
postgresql.base.conf.backup:#max_replication_slots = 64 # max number of replication slots
postgresql.conf:max_replication_slots = '10'
postgresql.conf.backup:max_replication_slots = '10'
postmaster.opts:/usr/pgsql-10/bin/postgres "-D" "/data/patroni" "--config-file=/data/patroni/postgresql.conf" "--hot_standby=on" "--listen_addresses=192.168.0.124" "--max_worker_processes=8" "--max_locks_per_transaction=64" "--wal_level=replica" "--cluster_name=postgres" "--wal_log_hints=on" "--max_wal_senders=10" "--track_commit_timestamp=off" "--max_prepared_transactions=0" "--port=5432" "--max_replication_slots=10" "--max_connections=100"
[root@psql02 patroni]#

[root@psql03 patroni]# grep -Ei max_replication_slots * 2>/dev/null
backup-postgresql.conf:max_replication_slots = '10'
patroni.dynamic.json:{"maximum_lag_on_failover": 1048576, "retry_timeout": 10, "postgresql": {"max_wal_senders": 32, "max_replication_slots": 64, "use_pg_rewind": true, "max_connections": 256}, "loop_wait": 10, "ttl": 30}
postgresql.base.conf:#max_replication_slots = 64        # max number of replication slots
postgresql.base.conf.backup:#max_replication_slots = 64 # max number of replication slots
postgresql.conf:max_replication_slots = '10'
postgresql.conf.backup:max_replication_slots = '10'
postmaster.opts:/usr/pgsql-10/bin/postgres "-D" "/data/patroni" "--config-file=/data/patroni/postgresql.conf" "--listen_addresses=192.168.0.118" "--max_worker_processes=8" "--max_locks_per_transaction=64" "--wal_level=replica" "--track_commit_timestamp=off" "--max_prepared_transactions=0" "--port=5432" "--max_replication_slots=10" "--max_connections=100" "--hot_standby=on" "--cluster_name=postgres" "--wal_log_hints=on" "--max_wal_senders=10"
[root@psql03 patroni]#

@CyberDem0n

This comment has been minimized.

Copy link
Member

commented Sep 14, 2019

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

@tomkcpr

This comment has been minimized.

Copy link
Author

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

@CyberDem0n CyberDem0n closed this Sep 18, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
2 participants
You can’t perform that action at this time.