postgres logical replication not working. Error says "could not connect to the publisher"

Question

I have a postgres database called salephone_test with 3 tables (smartphones, listings, phone_listings) on my windows pc which I want to replicate to at least one ubuntu droplet on digitalocean. I attempted the following setup to replicate the smartphones table:

On my local machine (pc) in postgresql.conf I set the listen_addresses = '*' and wal_level = logical in pg_hba.conf, I added the following lines

host    salephone_test  rep             0.0.0.0/0               md5
host    salephone_test  all             104.248.54.230/0        md5
host    all             all             0.0.0.0/0               md5
host  all  all 0.0.0.0/0 md5

where 104.248.54.230 is the IP of my digitalocean droplet I also set up a replication user and publication by the following commands

CREATE ROLE rep REPLICATION LOGIN PASSWORD 'fakepass';
GRANT SELECT on smartphones to rep;
CREATE PUBLICATION test_phones FOR TABLE smartphones;

on my remote droplet, after installing postgres on the ubuntu, I created a database called salephone with a table called smartphones in psql, I then used the following command to subscribe for logical replication

// 50.71.125.50 is my pc ip according to google

CREATE SUBSCRIPTION phone_sub CONNECTION 'dbname = salephone_test host = 50.71.125.50 user = rep password = fakepass port = 5432' PUBLICATION test_phones;

after a minute of waiting, I received the following

ERROR:  could not connect to the publisher: connection to server at "50.71.125.50", port 5432 failed: Connection timed out
        Is the server running on that host and accepting TCP/IP connections?

Note: i tried restarting postgres on my pc multiple times already via services.msc

@ErwinBrandstetter line 63 says port = 5432 # (change requires restart) so i believe that means port 5432 is being used — Lase
– Lase, Commented Aug 27, 2022 at 6:57

jjanes · Accepted Answer · 2022-08-27 12:38:30Z

1

Your home modem/router is surely blocking the connection. You will need to configure it to accept the connection and do 'port forwarding' to your pc. How you do that (or if it is even possible) would depend on the make and model of the router.

Also, your pg_hba doesn't make much sense. The reason to have a more specific entry above a more general entry is to give it a different auth method (or configuration). Since all your entries have the same method, you might as well just have the last line and not have the preceding 3.

edited Aug 27, 2022 at 12:38

answered Aug 27, 2022 at 10:24

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lase Over a year ago

i just pgdumped and reproduced the db on a remote cluster. the replication works now

Collectives™ on Stack Overflow

postgres logical replication not working. Error says "could not connect to the publisher"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related