-----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@xxxxxxxxxxx] Sent: Wednesday, May 24, 2017 7:06 PM To: Igor Neyman <ineyman@xxxxxxxxxxxxxx>; George Neuner <gneuner2@xxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx Subject: Re: logical replication in PG10 BETA 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/ -- Adrian Klaver adrian.klaver@xxxxxxxxxxx __________________________________________________________________________________________________________ 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 was going 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
