0

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/

  • I suggest you to read Logical Replication especially Replication Slot Management title for understanding overall idea of logical replication and logical replication slots. – Sahap Asci Jun 23 at 20:33
  • @SahapAsci hi can you please give me the link where it is described explicitly how to replicate table which does not have any primary key or index? Please send me the steps which should be done in that case – Dejan Jun 23 at 21:02
  • just to elaborate additionally - I have read that logical replication will not work out of the box immediately in case if table does not have primary key or unique index - but I tested it and it seems to me that it is working??? can you please help me with this I am confused - should it work immediately and for other tables or not? thank you – Dejan Jun 23 at 21:20
  • 1
    The CREATE SUBSCRIPTION command would create the replication slot. There must be something in your setup that you are not telling us. – Laurenz Albe Jun 24 at 6:51
1
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.