Re: logical replication in PG10 BETA

On 05/24/2017 08:30 AM, Igor Neyman wrote:
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
> Sent: Wednesday, May 24, 2017 10:00 AM
> To: Igor Neyman <ineyman(at)perceptron(dot)com>; George Neuner <gneuner2(at)comcast(dot)net>; pgsql-general(at)postgresql(dot)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(dot)klaver(at)aklaver(dot)com]
>> Sent: Tuesday, May 23, 2017 7:42 PM
>> To: Igor Neyman <ineyman(at)perceptron(dot)com>; George Neuner
>> <gneuner2(at)comcast(dot)net>; pgsql-general(at)postgresql(dot)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(dot)klaver(at)aklaver(dot)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, and it 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 user you where connecting as. Just making sure that the repl_user could connect to the remote instance outside the logical replication 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(dot)klaver(at)aklaver(dot)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 see the 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 supply the 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/
-
>
> Regards,
> Igor Neyman
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com