Re: [GENERAL] logical replication in PG10 BETA

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] 
Sent: Wednesday, May 24, 2017 7:06 PM
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 08:30 AM, Igor Neyman wrote:
> -----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 CREATE SUBSCRIPTION - are using the same credentials (user=repl_user database=repl).
 

In a previous post you had:

CREATE SUBSCRIPTION my_furst_subs CONNECTION 'dbname=repl host=pub_machine port=5432 user=repl_user' PUBLICATION
my_first_publ;

I assumed you had a .pgpass file on the the subscriber side, is that the case or are you using some other method to
supplythe password?
 

>
> Any other thoughts?

No this taps me out.

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

The bug reporting page:

https://www.postgresql.org/account/login/?next=/account/submitbug/

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

Adrian, thanks for trying to help.

Even though the role I'm using (user=repl_user) has REPLICATION attribute,  I thought your question about .pgpass file
wasgoing to put me on a "right track" because I was not using/didn't have password file.
 
So, I created one proper password file (it works fine when I'm trying to connect through psql with no password).
Unfortunately,it didn't make any difference for CREATE SUBSCRIPTION.
 

I reported my problem as a bug (bug# 14669), but so far it doesn't seem to attract any interest.

Regards,
Igor Neyman