Re: logical replication in PG10 BETA

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

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

Regards,
Igor Neyman