replication slot getting deleted at start up

[prakharverma01]

PGsync version : lastet pull taken today
ES version : AWS OpenSearch 1.3
postgres version : 12.8 (AWS postgres aurora)

Case 1 : Working 👍
When running the latest code of PGSync in my local machine, with docker-compose, the setup works fine with above given infrastructure.

Case 2 : No Working 👎
When running the same code in AWS ECS, the code stops with error Replication slot "tenancyportal_allpropertiesuat" does not exist, one thing I noticed is that sequence of command execution while boot-up for creating replication slots
first -> PG_CREATE_LOGICAL_REPLICATION_SLOT (creates replication slot)
second -> select * FROM PG_REPLICATION_SLOTS
third -> PG_DROP_REPLICATION_SLOT (drops the replications slot)

In third step its dropping the replication slot for unknown reason.

• Its not printing error for : superuser or have permission to read, create and destroy replication slots hence i think there is no issue with the DB user
• I am running with docker, so it has already ran the bootstrap command

logs :

Dec 16 17:18:32.259
i-0a22e7da8521be02c
pgsync-uat
Make sure you have run the "bootstrap" command.

Dec 16 17:18:32.259
i-0a22e7da8521be02c
pgsync-uat
RuntimeError: Replication slot "tenancyportal_allpropertiesuat" does not exist.

Dec 16 17:18:32.259
i-0a22e7da8521be02c
pgsync-uat
f'Replication slot "{self.__name}" does not exist. '

Dec 16 17:18:32.259
i-0a22e7da8521be02c
pgsync-uat
File "/usr/local/lib/python3.7/site-packages/pgsync/sync.py", line 152, in validate

Dec 16 17:18:32.259
i-0a22e7da8521be02c
pgsync-uat
self.validate(repl_slots=repl_slots)

Dec 16 17:18:32.259
i-0a22e7da8521be02c
pgsync-uat
File "/usr/local/lib/python3.7/site-packages/pgsync/sync.py", line 99, in init

Dec 16 17:18:32.259
i-0a22e7da8521be02c
pgsync-uat
*args, **kwargs

Dec 16 17:18:32.259
i-0a22e7da8521be02c
pgsync-uat
File "/usr/local/lib/python3.7/site-packages/pgsync/singleton.py", line 18, in call

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
sync: Sync = Sync(document, verbose=verbose, **kwargs)

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
File "/usr/local/lib/python3.7/site-packages/pgsync/sync.py", line 1373, in main

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
return __callback(*args, **kwargs)

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
File "/usr/local/lib/python3.7/site-packages/click/core.py", line 760, in invoke

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
return ctx.invoke(self.callback, **ctx.params)

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
File "/usr/local/lib/python3.7/site-packages/click/core.py", line 1404, in invoke

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
rv = self.invoke(ctx)

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
File "/usr/local/lib/python3.7/site-packages/click/core.py", line 1055, in main

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
return self.main(*args, **kwargs)

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
File "/usr/local/lib/python3.7/site-packages/click/core.py", line 1130, in call

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
sync.main()

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
File "/usr/local/bin/pgsync", line 7, in

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
Traceback (most recent call last):

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
0:00:03.212034 (3.21 sec)

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat-------------------------------------------------------------------------------

Dec 16 17:18:32.258
i-0a22e7da8521be02c
pgsync-uat
AND plugin = %(plugin_1)s

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat
AND slot_type = %(slot_type_1)s

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat
WHERE slot_name = %(slot_name_1)s

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat
FROM PG_REPLICATION_SLOTS

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat
SELECT *

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat
[4mreplication_slots:�[0m

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat-------------------------------------------------------------------------------

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat
WHERE name = %(name_1)s

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat
FROM pg_settings

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat
SELECT setting

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat
[4mpg_settings:�[0m

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat-------------------------------------------------------------------------------

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat
FROM PG_DROP_REPLICATION_SLOT(%(PG_DROP_REPLICATION_SLOT_1)s)

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat
SELECT *

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat
[4mdrop_replication_slot:�[0m

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat-------------------------------------------------------------------------------

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat
AND plugin = %(plugin_1)s

Dec 16 17:18:32.257
i-0a22e7da8521be02c
pgsync-uat
AND slot_type = %(slot_type_1)s

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
WHERE slot_name = %(slot_name_1)s

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
FROM PG_REPLICATION_SLOTS

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
SELECT *

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
[4mreplication_slots:�[0m

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat-------------------------------------------------------------------------------

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
FROM PG_CREATE_LOGICAL_REPLICATION_SLOT(%(PG_CREATE_LOGICAL_REPLICATION_SLOT_1)s, %(PG_CREATE_LOGICAL_REPLICATION_SLOT_2)s)

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
SELECT *

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
[4mcreate_replication_slot:�[0m

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat-------------------------------------------------------------------------------

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
AND plugin = %(plugin_1)s

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
AND slot_type = %(slot_type_1)s

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
WHERE slot_name = %(slot_name_1)s

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
FROM PG_REPLICATION_SLOTS

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
SELECT *

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
[4mreplication_slots:�[0m

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat-------------------------------------------------------------------------------

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
WHERE name = %(name_1)s

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
FROM pg_settings

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
SELECT setting

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
[4mpg_settings:�[0m

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat -------------------------------------------------------------------------------

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
WHERE name = %(name_1)s

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
FROM pg_settings

Dec 16 17:18:32.256
i-0a22e7da8521be02c
pgsync-uat
SELECT setting

Dec 16 17:18:32.255
i-0a22e7da8521be02c
pgsync-uat

can someone help here ?

[toluaina]

Hi,
Are you running using a AWS managed RDS database?
--Tolu
@prakharverma01