postgres 11 replication stuck in startup state

Question

I'm trying to run logical replication from a postgres 11 server running on ec2 to postgres 11 on RDS, but now on the source, its stuck in startup state.

On the source I did this:

CREATE ROLE replrds;
ALTER ROLE replrds WITH NOSUPERUSER NOCREATEROLE NOCREATEDB LOGIN REPLICATION NOBYPASSRLS PASSWORD 'xxxxxxx';
GRANT CONNECT ON DATABASE db_name TO replrds;
GRANT ALL PRIVILEGES ON DATABASE db_name to replrds;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO replrds;
CREATE PUBLICATION rds_pub FOR ALL TABLES;

Then on the target, this timed out on my end (thinking my connection dropped)

CREATE SUBSCRIPTION ec2_mono_subscription CONNECTION 'host=1.2.3.4 port=5432 password=xxxxx user=replrds dbname=db_name' PUBLICATION rds_pub;

Then replication didn't start but now on the target I get this message:

mono=> CREATE SUBSCRIPTION ec2_mono_subscription CONNECTION 'host=1.2.3.4 port=5432 password=xxxxx user=replrds dbname=db_name' PUBLICATION rds_pub;
ERROR:  could not connect to the publisher: FATAL:  number of requested standby connections exceeds max_wal_senders (currently 3)

I tried to drop it and recreate but there I can't drop it on the source:

postgres=# select pg_drop_replication_slot('ec2_mono_subscription');
ERROR:  replication slot "ec2_mono_subscription" is active for PID 68255
postgres=#
postgres=# select pid, usename, application_name, state, sync_state, sent_lsn, write_lag, flush_lag, replay_lag from pg_stat_replication;
  pid  | usename  |   application_name    |   state   | sync_state |   sent_lsn    |    write_lag    |    flush_lag    |   replay_lag
-------+----------+-----------------------+-----------+------------+---------------+-----------------+-----------------+-----------------
 38270 | repluser | walreceiver           | streaming | async      | 46EF/10E85038 | 00:00:00.000403 | 00:00:00.001647 | 00:00:00.001954
 25613 | repluser | walreceiver           | streaming | async      | 46EF/10E85038 | 00:00:00.000287 | 00:00:00.001136 | 00:00:00.00144
 68255 | replrds  | ec2_mono_subscription | startup   | async      |               |                 |                 |

Also, on the target, its not even there:

mono=> select * from pg_subscription;
 subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications
---------+---------+----------+------------+-------------+-------------+---------------+-----------------
(0 rows)

---------------------------EDIT-------------------

I was able to kill the replication on the source with this: select pg_terminate_backend(68255);

but now I can't seem to start replication from the target, it just seems to freeze after this query:

mono=> CREATE SUBSCRIPTION ec2_mono_subscription CONNECTION 'host=1.2.3.4 port=5432 password=xxxxxx user=replrds dbname=db_name' PUBLICATION rds_pub;

Whats also weird is that the replication slot comes up on the source with a state of startup

postgres 11 replication stuck in startup state

0 Answers