postgresqldatabase-replicationlogical-replication

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

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

Solution

  • 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.