Re: [GENERAL] logical replication in PG10 BETA - Mailing list pgsql-general
From | Igor Neyman |
---|---|
Subject | Re: [GENERAL] logical replication in PG10 BETA |
Date | |
Msg-id | DM5PR07MB28105189928D92469DD3F717DAFF0@DM5PR07MB2810.namprd07.prod.outlook.com Whole thread Raw |
In response to | Re: [GENERAL] logical replication in PG10 BETA (Adrian Klaver <adrian.klaver@aklaver.com>) |
Responses |
Re: [GENERAL] logical replication in PG10 BETA
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
List | pgsql-general |
-----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Wednesday, May 24, 2017 7:06 PM 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 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 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 supplythe 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@aklaver.com __________________________________________________________________________________________________________ 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 wasgoing 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
pgsql-general by date: