Postgresql – Postgres Logical Replication – “replication slot ”thesub“ was not created in this database”

master-slave-replicationpostgresqlpostgresql-11replication

I have two database servers 192.168.150.210 and 192.168.150.220
I created publication and subscription successfully for logical replication.

postgresql.conf file is modified on both servers.

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)

On both servers wal level is set to logical.

testha=# show wal_level;
 wal_level 
-----------
 logical
(1 row

pg_hba.conf file is modified on both servers:

on server 192.168.150.210

host    all             all             192.168.150.220/32      md5

on server 192.168.150.220

host    all             all             192.168.150.210/32      md5

with user postgres I created table article on database testha

\c testha
create table article(idart int primary key,name varchar(10), quantity int);

just in case I also granted permission to that table for user postgres/postgres

grant all on article to postgres;

I created publication on server 192.168.150.210

CREATE PUBLICATION mypub FOR TABLE article;

I can see that everything is OK:

CREATE SUBSCRIPTION mysub CONNECTION 'dbname=testha host=192.168.150.210 user=postgres password=postgres port=5432' PUBLICATION mypub;

I tested connection and communication between servers is ok:

psql -h 192.168.150.220 -U postgres -W
psql -h 192.168.150.210 -U postgres -W

I restarted servers but in the logs I see errors:

ERROR:  replication slot "mysub" was not created in this database

ERROR:  could not start WAL streaming: ERROR:  replication slot "mysub" was not created in this database
LOG:  background worker "logical replication worker" (PID 30667) exited with exit code 1

What am I doing wrong. I followed instructions from multiple sites and did not find any additional instructions.
https://blog.dbi-services.com/postgresql-10-beta-1-logical-replication/

Best Answer

CREATE SUBSCRIPTION mysub CONNECTION 'dbname=testha host=192.168.150.210 user=postgres password=postgres port=5432' PUBLICATION mypub;

What was the output of this command?

You should have gotten something like either:

NOTICE:  created replication slot "mysub" on publisher 
CREATE SUBSCRIPTION

Or 

ERROR:  could not create replication slot "mysub": ERROR:  replication slot "mysub" already exists

It sounds like what happened here is that at the time you created the subscription, the publisher was running as expected. But then you did something weird, like disconnect the subscriber, rename the database "testha" on the publisher to something else, make a new "testha" and try to reconnect the subscriber to that.

Related Question