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

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
andCREATE 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.klaver@aklaver.com