Re: [GENERAL] logical replication in PG10 BETA - Mailing list pgsql-general

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] 
Sent: Wednesday, May 24, 2017 10:00 AM
To: Igor Neyman <ineyman@perceptron.com>; George Neuner <gneuner2@comcast.net>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] logical replication in PG10 BETA


On 05/24/2017 06:31 AM, Igor Neyman wrote:
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Tuesday, May 23, 2017 7:42 PM
> To: Igor Neyman <ineyman@perceptron.com>; George Neuner 
> <gneuner2@comcast.net>; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] logical replication in PG10 BETA
>
>
> So take the local line out of pg_hba. Then from the machine that is the subscriber do:
>
> psql -d repl -h pub_machine -p 5432 -U repl_user
>
> --
> Adrian Klaver
>
> adrian.klaver@aklaver.com
>
> ______________________________________________________________________
> __________________
>
> This psql connection works.
> Even more, like I showed in one of previous messages, connection between 2 PG servers using Postgres_fdw also works,
andit uses the same connection string as CREATE SUBSCRIPTION statement.
 

Except the FDW connection string does not specify a user and I do remember seeing a USER MAPPING that indicated what
useryou where connecting as. Just making sure that the repl_user could connect to the remote instance outside the
logicalreplication framework.
 

At this point all I could think of is to start over:

1) DROP the PUBLICATION.

2) CREATE PUBLICATION
Check the Postgres log on the publisher side.

3) CREATE SUBSCRIPTION
Check the Postgres logs on both the publisher and subscription sides.

Another thought. Have you checked the Windows Firewall settings/logs to see if it might be interfering?

--
Adrian Klaver
adrian.klaver@aklaver.com
___________________________________________________________________________________________________________

That's right. With FDW I specify user using, so it's practically the same:

CREATE USER MAPPING FOR repl_user SERVER pub_server OPTIONS (user 'repl_user', password 'blah');

I have the same user repl_user created on both servers.

1. DROP PUBLICATION ...
     
     Nothing on pg log

2. CREATE PUBLICATION my_first_publ FOR TABLE test_repl;  on publishing server
      
     Nothing in pg_log, publication created successfully. 
     "select * from pg_catalog.pg_publication" returns info about " my_first_publ" publication.

3. CREATE SUBSCRIPTION...

    I turned on log_connections on both sides.
     In Publisher's log:
     
       2017-05-24 11:00:30.624 EDT [8840] LOG:  connection received: host=192.168.5.84 port=64923     

     In Subscriber's pg log:

    ERROR:  could not connect to the publisher: could not send data to server: Socket is not connected
(0x00002749/10057)
    could not send SSL negotiation packet: Socket is not connected (0x00002749/10057)
     STATEMENT: CREATE SUBSCRIPTION ...

Now, when on subscriber machine I use FDW to read foreign table (from publishing machine), then in Publisher's log I
seethe following info about connection:
 

2017-05-24 11:02:30.849 EDT [5100] LOG:  connection received: host=192.168.5.84 port=64925
2017-05-24 11:02:30.856 EDT [5100] LOG:  connection authorized: user=repl_user database=repl

So, when using FDW Publisher's server logs both "connection received" and "connection authorized",
while when creating subscription Publisher logs only "connection received" and nothing else, even though both: FDW and
CREATESUBSCRIPTION - are using the same credentials (user=repl_user database=repl).
 

Any other thoughts?
Seems like a bug? I s there a place to report bugs for PG 10 BETA?

Regards,
Igor Neyman