Bug: pg plugin replication slots and publications are not cleaned after teardown

[lyuboxa]

Bug description

Source connectors with cdc enabled should drop the replication slots after they are deleted.
When the slots are left to linger they can cause accumulation of WAL and cause the database to shutdown when the disk fills up.

Steps to reproduce

Setup

CREATE TABLE test (id bigserial primary key, data varchar(100));
INSERT INTO test (data) SELECT generate_series(1,20);

Reproduce

1. Create Pipeline
2. Create CDC source connector on pipeline with config

"table": "test"
"cdc": "true"
"url": "url"
"publication_name":"test_pub"
"slot_name":"test_slot"

3. Create File destination connector on pipeline with config

"path":"/tmp/test-output"

4. Start pipeline
5. Verify data in file /tmp/test-output
6. Stop pipeline
7. Delete connectors
8. Delete pipeline
9. Run following queries on the target database

SELECT * FROM pg_replication_slots;
SELECT * FROM pg_publication;

10. slot test_slot and publication test_pub should not be in the list

Version

v0.1.1-13-gef4f71b linux/amd64

[neovintage]

Note: STOP is an asynchronous action. We should make STOP idempodent.

[lovromazgon]

Here's what the connector will do after we merge ConduitIO/conduit-connector-postgres#13:

• If the logical replication slot and publication don't exist, the connector will create them on pipeline start and will drop them on pipeline stop. In this case it can't be guaranteed that the pipeline will be able to start again at the same position it left off because the WAL logs could be purged.
• If the logical replication slot or publication already exist, the connector assumes they are managed externally so it will use them and not delete them when the pipeline stops. This also ensures that Conduit will be able to start again at the same position it left off, but there is a risk of accumulating a lot of WAL if the pipeline stops running and is not resumed in a timely fashion.

Note to self: add this to docs.