search

toluaina / pgsync

Postgres to Elasticsearch/OpenSearch sync
https://pgsync.com
MIT License
1.16k stars 181 forks source link

pgsync.exc.ReplicationSlotError with version 2.5.0 #434

Open aubergineankit opened 1 year ago

aubergineankit commented 1 year ago

PGSync version: 2.5.0 / 2.5.1 (main branch)

Postgres version: postgres:12.4

Elasticsearch version: elasticsearch:7.12.1

Redis version: image: redis:6.0.6

Python version: 3.8

Problem Description:

pgsync.exc.ReplicationSlotError:

Permission issue after upgrading pgysnc to 2.5.0, with pgsync 2.1.1 same setup is working fine.

Error Message (if any): 

2023-03-24 09:42:17.464:ERROR:pgsync.base: Exception (psycopg2.errors.ConfigurationLimitExceeded) all replication slots are in use
HINT:  Free one or increase max_replication_slots.

[SQL: SELECT * 
FROM PG_CREATE_LOGICAL_REPLICATION_SLOT(%(PG_CREATE_LOGICAL_REPLICATION_SLOT_1)s, %(PG_CREATE_LOGICAL_REPLICATION_SLOT_2)s)]
[parameters: {'PG_CREATE_LOGICAL_REPLICATION_SLOT_1': '_tmp_', 'PG_CREATE_LOGICAL_REPLICATION_SLOT_2': 'test_decoding'}]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.ConfigurationLimitExceeded: all replication slots are in use
HINT:  Free one or increase max_replication_slots.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 806, in fetchone
    row = conn.execute(statement).fetchone()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1380, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.errors.ConfigurationLimitExceeded) all replication slots are in use
HINT:  Free one or increase max_replication_slots.

[SQL: SELECT * 
FROM PG_CREATE_LOGICAL_REPLICATION_SLOT(%(PG_CREATE_LOGICAL_REPLICATION_SLOT_1)s, %(PG_CREATE_LOGICAL_REPLICATION_SLOT_2)s)]
[parameters: {'PG_CREATE_LOGICAL_REPLICATION_SLOT_1': '_tmp_', 'PG_CREATE_LOGICAL_REPLICATION_SLOT_2': 'test_decoding'}]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
2023-03-24 09:42:17.468:ERROR:pgsync.base: (psycopg2.errors.ConfigurationLimitExceeded) all replication slots are in use
HINT:  Free one or increase max_replication_slots.

[SQL: SELECT * 
FROM PG_CREATE_LOGICAL_REPLICATION_SLOT(%(PG_CREATE_LOGICAL_REPLICATION_SLOT_1)s, %(PG_CREATE_LOGICAL_REPLICATION_SLOT_2)s)]
[parameters: {'PG_CREATE_LOGICAL_REPLICATION_SLOT_1': '_tmp_', 'PG_CREATE_LOGICAL_REPLICATION_SLOT_2': 'test_decoding'}]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.ConfigurationLimitExceeded: all replication slots are in use
HINT:  Free one or increase max_replication_slots.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 178, in _can_create_replication_slot
    self.create_replication_slot(slot_name)
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 377, in create_replication_slot
    return self.fetchone(
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 806, in fetchone
    row = conn.execute(statement).fetchone()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1380, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.errors.ConfigurationLimitExceeded) all replication slots are in use
HINT:  Free one or increase max_replication_slots.

[SQL: SELECT * 
FROM PG_CREATE_LOGICAL_REPLICATION_SLOT(%(PG_CREATE_LOGICAL_REPLICATION_SLOT_1)s, %(PG_CREATE_LOGICAL_REPLICATION_SLOT_2)s)]
[parameters: {'PG_CREATE_LOGICAL_REPLICATION_SLOT_1': '_tmp_', 'PG_CREATE_LOGICAL_REPLICATION_SLOT_2': 'test_decoding'}]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.ConfigurationLimitExceeded: all replication slots are in use
HINT:  Free one or increase max_replication_slots.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 178, in _can_create_replication_slot
    self.create_replication_slot(slot_name)
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 377, in create_replication_slot
    return self.fetchone(
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 806, in fetchone
    row = conn.execute(statement).fetchone()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1380, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.errors.ConfigurationLimitExceeded) all replication slots are in use
HINT:  Free one or increase max_replication_slots.

[SQL: SELECT * 
FROM PG_CREATE_LOGICAL_REPLICATION_SLOT(%(PG_CREATE_LOGICAL_REPLICATION_SLOT_1)s, %(PG_CREATE_LOGICAL_REPLICATION_SLOT_2)s)]
[parameters: {'PG_CREATE_LOGICAL_REPLICATION_SLOT_1': '_tmp_', 'PG_CREATE_LOGICAL_REPLICATION_SLOT_2': 'test_decoding'}]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/bin/bootstrap", line 69, in <module>
    main()
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/bin/bootstrap", line 58, in main
    sync: Sync = Sync(
  File "/usr/local/lib/python3.8/site-packages/pgsync/singleton.py", line 17, in __call__
    cls._instances[key] = super(Singleton, cls).__call__(
  File "/usr/local/lib/python3.8/site-packages/pgsync/sync.py", line 99, in __init__
    self.validate(repl_slots=repl_slots)
  File "/usr/local/lib/python3.8/site-packages/pgsync/sync.py", line 135, in validate
    self._can_create_replication_slot("_tmp_")
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 181, in _can_create_replication_slot
    raise ReplicationSlotError(
pgsync.exc.ReplicationSlotError: 'PG_USER "postgres" needs to be superuser or have permission to read, create and destroy replication slots to perform this action.'
2023-03-24 09:42:24.113:ERROR:pgsync.base: Exception (psycopg2.errors.ConfigurationLimitExceeded) all replication slots are in use
HINT:  Free one or increase max_replication_slots.

[SQL: SELECT * 
FROM PG_CREATE_LOGICAL_REPLICATION_SLOT(%(PG_CREATE_LOGICAL_REPLICATION_SLOT_1)s, %(PG_CREATE_LOGICAL_REPLICATION_SLOT_2)s)]
[parameters: {'PG_CREATE_LOGICAL_REPLICATION_SLOT_1': '_tmp_', 'PG_CREATE_LOGICAL_REPLICATION_SLOT_2': 'test_decoding'}]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.ConfigurationLimitExceeded: all replication slots are in use
HINT:  Free one or increase max_replication_slots.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 806, in fetchone
    row = conn.execute(statement).fetchone()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1380, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.errors.ConfigurationLimitExceeded) all replication slots are in use
HINT:  Free one or increase max_replication_slots.

[SQL: SELECT * 
FROM PG_CREATE_LOGICAL_REPLICATION_SLOT(%(PG_CREATE_LOGICAL_REPLICATION_SLOT_1)s, %(PG_CREATE_LOGICAL_REPLICATION_SLOT_2)s)]
[parameters: {'PG_CREATE_LOGICAL_REPLICATION_SLOT_1': '_tmp_', 'PG_CREATE_LOGICAL_REPLICATION_SLOT_2': 'test_decoding'}]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
2023-03-24 09:42:24.115:ERROR:pgsync.base: (psycopg2.errors.ConfigurationLimitExceeded) all replication slots are in use
HINT:  Free one or increase max_replication_slots.

[SQL: SELECT * 
FROM PG_CREATE_LOGICAL_REPLICATION_SLOT(%(PG_CREATE_LOGICAL_REPLICATION_SLOT_1)s, %(PG_CREATE_LOGICAL_REPLICATION_SLOT_2)s)]
[parameters: {'PG_CREATE_LOGICAL_REPLICATION_SLOT_1': '_tmp_', 'PG_CREATE_LOGICAL_REPLICATION_SLOT_2': 'test_decoding'}]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.ConfigurationLimitExceeded: all replication slots are in use
HINT:  Free one or increase max_replication_slots.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 178, in _can_create_replication_slot
    self.create_replication_slot(slot_name)
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 377, in create_replication_slot
    return self.fetchone(
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 806, in fetchone
    row = conn.execute(statement).fetchone()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1380, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.errors.ConfigurationLimitExceeded) all replication slots are in use
HINT:  Free one or increase max_replication_slots.

[SQL: SELECT * 
FROM PG_CREATE_LOGICAL_REPLICATION_SLOT(%(PG_CREATE_LOGICAL_REPLICATION_SLOT_1)s, %(PG_CREATE_LOGICAL_REPLICATION_SLOT_2)s)]
[parameters: {'PG_CREATE_LOGICAL_REPLICATION_SLOT_1': '_tmp_', 'PG_CREATE_LOGICAL_REPLICATION_SLOT_2': 'test_decoding'}]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
 0:00:05.914382 (5.91 sec)
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.ConfigurationLimitExceeded: all replication slots are in use
HINT:  Free one or increase max_replication_slots.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 178, in _can_create_replication_slot
    self.create_replication_slot(slot_name)
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 377, in create_replication_slot
    return self.fetchone(
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 806, in fetchone
    row = conn.execute(statement).fetchone()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1380, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.errors.ConfigurationLimitExceeded) all replication slots are in use
HINT:  Free one or increase max_replication_slots.

[SQL: SELECT * 
FROM PG_CREATE_LOGICAL_REPLICATION_SLOT(%(PG_CREATE_LOGICAL_REPLICATION_SLOT_1)s, %(PG_CREATE_LOGICAL_REPLICATION_SLOT_2)s)]
[parameters: {'PG_CREATE_LOGICAL_REPLICATION_SLOT_1': '_tmp_', 'PG_CREATE_LOGICAL_REPLICATION_SLOT_2': 'test_decoding'}]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/bin/pgsync", line 7, in <module>
    sync.main()
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/pgsync/sync.py", line 1450, in main
    sync: Sync = Sync(document, verbose=verbose, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/pgsync/singleton.py", line 17, in __call__
    cls._instances[key] = super(Singleton, cls).__call__(
  File "/usr/local/lib/python3.8/site-packages/pgsync/sync.py", line 99, in __init__
    self.validate(repl_slots=repl_slots)
  File "/usr/local/lib/python3.8/site-packages/pgsync/sync.py", line 135, in validate
    self._can_create_replication_slot("_tmp_")
  File "/usr/local/lib/python3.8/site-packages/pgsync/base.py", line 181, in _can_create_replication_slot
    raise ReplicationSlotError(
pgsync.exc.ReplicationSlotError: 'PG_USER "postgres" needs to be superuser or have permission to read, create and destroy replication slots to perform this action.'

I have confirmed that my user has superuser permission along with Replication

image

@toluaina

4d11 commented 1 year ago

I resolved the issue with following steps (dev environment)

  1. Get existing replication slots

    SELECT * from PG_REPLICATION_SLOTS;

  2. Delete them

    select pg_drop_replication_slot('my_pgsyncproducts_slot_name');

  3. Set max_replication_slots

    ALTER SYSTEM SET max_replication_slots = 1;

  4. Bootstrap again

    bootstrap --config schema.json