On 05/24/2017 08:30 AM, Igor Neyman wrote:
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@xxxxxxxxxxx]
Sent: Wednesday, May 24, 2017 10:00 AM
To: Igor Neyman <ineyman@xxxxxxxxxxxxxx>; George Neuner <gneuner2@xxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx
Subject: Re: logical replication in PG10 BETA
On 05/24/2017 06:31 AM, Igor Neyman wrote:
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@xxxxxxxxxxx]
Sent: Tuesday, May 23, 2017 7:42 PM
To: Igor Neyman <ineyman@xxxxxxxxxxxxxx>; George Neuner
<gneuner2@xxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 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@xxxxxxxxxxx
______________________________________________________________________
__________________
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.klaver@xxxxxxxxxxx
___________________________________________________________________________________________________________
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.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
