Обсуждение: [GENERAL] logical replication in PG10 BETA
Hi,
Does built-in logical replication work on Windows in PG10 BETA release?
I can’t make it working so far.
I created Publication on “source” PG server, but when I’m trying to CREATE SUBSCRIPTION… on “destination” server, I’m getting:
“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) SQL state: XX000”
In order to verify that connection works between “destination” and “source” server:
I created Postgres_fdw extension on “destination” server,
then I created server “fdw_server” foreign data wrapper postgres_fdw with the same connection options that I’m using when I’m trying to CREATE SUBSCRIPTION,
and it works fine: using this server (fdw_server) I can create foreign tables to access tables on the “source” server, and see select from these foreign tables.
Please let me know if my description is not clear.
Regards,
Igor
On 05/22/2017 12:33 PM, Igor Neyman wrote: > Hi, > > Does built-in logical replication work on Windows in PG10 BETA release? Short version: Maybe. Long version: AFAIK to run on Windows you need to build pglogical 2.0 from source master branch. So what version of pglogical and how did you install it? See the issue below for more info on pglogical and Postgres 10 support. https://github.com/2ndQuadrant/pglogical/issues/93 > > I can’t make it working so far. > > I created Publication on “source” PG server, but when I’m trying to > CREATE SUBSCRIPTION… on “destination” server, I’m getting: Are both servers on Windows? > > “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) SQL > state: XX000” > > In order to verify that connection works between “destination” and > “source” server: > > I created Postgres_fdw extension on “destination” server, > > then I created server “fdw_server” foreign data wrapper postgres_fdw > with the same connection options that I’m using when I’m trying to > CREATE SUBSCRIPTION, > > and it works fine: using this server (fdw_server) I can create foreign > tables to access tables on the “source” server, and see select from > these foreign tables. > > Please let me know if my description is not clear. > > Regards, > > Igor > -- Adrian Klaver adrian.klaver@aklaver.com
-----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Monday, May 22, 2017 7:56 PM To: Igor Neyman <ineyman@perceptron.com>; pgsql-general@postgresql.org Subject: Re: [GENERAL] logical replication in PG10 BETA On 05/22/2017 12:33 PM, Igor Neyman wrote: > Hi, > > Does built-in logical replication work on Windows in PG10 BETA release? Short version: Maybe. Long version: AFAIK to run on Windows you need to build pglogical 2.0 from source master branch. So what version of pglogical and how did you install it? See the issue below for more info on pglogical and Postgres 10 support. https://github.com/2ndQuadrant/pglogical/issues/93 > > I can't make it working so far. > > I created Publication on "source" PG server, but when I'm trying to > CREATE SUBSCRIPTION... on "destination" server, I'm getting: Are both servers on Windows? > > "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) SQL > state: XX000" > > In order to verify that connection works between "destination" and > "source" server: > > I created Postgres_fdw extension on "destination" server, > > then I created server "fdw_server" foreign data wrapper postgres_fdw > with the same connection options that I'm using when I'm trying to > CREATE SUBSCRIPTION, > > and it works fine: using this server (fdw_server) I can create foreign > tables to access tables on the "source" server, and see select from > these foreign tables. > > Please let me know if my description is not clear. > > Regards, > > Igor > -- Adrian Klaver adrian.klaver@aklaver.com ________________________________________________________________________________________ Answers to Adrian questions: Both servers run on Windows. To install 10BETA I used installer from EnterpriseDB. Logical Replication (including Windows platform) should be part ofthe core, so I should not be required to build pglogical 2.0 separately. The issue you referenced concern building pglogical. Any other ideas? Regards, Igor Neyman
On 05/23/2017 06:17 AM, Igor Neyman wrote: > -----Original Message----- >> "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) SQL >> state: XX000" >> >> In order to verify that connection works between "destination" and >> "source" server: >> >> I created Postgres_fdw extension on "destination" server, >> >> then I created server "fdw_server" foreign data wrapper postgres_fdw >> with the same connection options that I'm using when I'm trying to >> CREATE SUBSCRIPTION, >> >> and it works fine: using this server (fdw_server) I can create foreign >> tables to access tables on the "source" server, and see select from >> these foreign tables. >> > Answers to Adrian questions: > > Both servers run on Windows. > To install 10BETA I used installer from EnterpriseDB. Logical Replication (including Windows platform) should be part ofthe core, so I should not be required to build pglogical 2.0 separately. > The issue you referenced concern building pglogical. Yeah, my mistake I was working with pglogical and it got stuck in my head. > > Any other ideas? Have you gone through these sections of the docs?: https://www.postgresql.org/docs/10/static/logical-replication-config.html https://www.postgresql.org/docs/10/static/logical-replication-quick-setup.html > Regards, > Igor Neyman > -- Adrian Klaver adrian.klaver@aklaver.com
-----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Tuesday, May 23, 2017 9:26 AM To: Igor Neyman <ineyman@perceptron.com>; pgsql-general@postgresql.org Subject: Re: [GENERAL] logical replication in PG10 BETA On 05/23/2017 06:17 AM, Igor Neyman wrote: > -----Original Message----- >> "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) SQL >> state: XX000" >> >> In order to verify that connection works between "destination" and >> "source" server: >> >> I created Postgres_fdw extension on "destination" server, >> >> then I created server "fdw_server" foreign data wrapper postgres_fdw >> with the same connection options that I'm using when I'm trying to >> CREATE SUBSCRIPTION, >> >> and it works fine: using this server (fdw_server) I can create >> foreign tables to access tables on the "source" server, and see >> select from these foreign tables. >> > Answers to Adrian questions: > > Both servers run on Windows. > To install 10BETA I used installer from EnterpriseDB. Logical Replication (including Windows platform) should be part ofthe core, so I should not be required to build pglogical 2.0 separately. > The issue you referenced concern building pglogical. Yeah, my mistake I was working with pglogical and it got stuck in my head. > > Any other ideas? Have you gone through these sections of the docs?: https://www.postgresql.org/docs/10/static/logical-replication-config.html https://www.postgresql.org/docs/10/static/logical-replication-quick-setup.html -- Adrian Klaver adrian.klaver@aklaver.com _______________________________________________________________________________________________________ Yes. All parameters mentioned in the docs configured properly. CREATE PUBLICATION works fine. pg_hba.conf modified to allow "replication" user to connect to the server. In the original email I mentioned that I configured Postgres_fdw foreign server that connects using the same connection optionsas I'm using in CREATE SUBSCRIPTION, and foreign server works fine, which to me proves that there is no issues withpg_hba.conf. Regards, Igor
On 05/23/2017 06:33 AM, Igor Neyman wrote: > > Yeah, my mistake I was working with pglogical and it got stuck in my head. >> >> Any other ideas? > > Have you gone through these sections of the docs?: > > https://www.postgresql.org/docs/10/static/logical-replication-config.html > > https://www.postgresql.org/docs/10/static/logical-replication-quick-setup.html > -- > Adrian Klaver > adrian.klaver@aklaver.com > _______________________________________________________________________________________________________ > > Yes. All parameters mentioned in the docs configured properly. > CREATE PUBLICATION works fine. > > pg_hba.conf modified to allow "replication" user to connect to the server. > In the original email I mentioned that I configured Postgres_fdw foreign server that connects using the same connectionoptions as I'm using in CREATE SUBSCRIPTION, and foreign server works fine, which to me proves that there is noissues with pg_hba.conf. What are the contents of the pg_hba.conf file? What are the CREATE PUBLICATION and CREATE SUBSCRIPTION commands you are using? > > Regards, > Igor > -- Adrian Klaver adrian.klaver@aklaver.com
Igor Neyman schrieb am 22.05.2017 um 21:33: > Does built-in logical replication work on Windows in PG10 BETA release? > > I can’t make it working so far. > > I created Publication on “source” PG server, but when I’m trying to CREATE SUBSCRIPTION… on “destination” server, I’m getting: > > “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) SQL > state: XX000” > could not send SSL negotiation packet sounds more like a SSL setup problem between the two Postgres servers, not really related to logical replication. Can you try to setup the connections without SSL?
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Thomas Kellerer Sent: Tuesday, May 23, 2017 9:46 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] logical replication in PG10 BETA Igor Neyman schrieb am 22.05.2017 um 21:33: > Does built-in logical replication work on Windows in PG10 BETA release? > > I can’t make it working so far. > > I created Publication on “source” PG server, but when I’m trying to CREATE SUBSCRIPTION… on “destination” server, I’m getting: > > “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) SQL > state: XX000” > could not send SSL negotiation packet sounds more like a SSL setup problem between the two Postgres servers, not really related to logical replication. Can you try to setup the connections without SSL? ________________________________________________________________________________________________________ That's what I thought when I first saw this error. But, I don't understand why is it trying to use sockets. I have ssl=off in postgresql.conf on both sides. Regards, Igor
On 05/23/2017 06:52 AM, Igor Neyman wrote: > > > sounds more like a SSL setup problem between the two Postgres servers, not really related to logical replication. > > Can you try to setup the connections without SSL? > > ________________________________________________________________________________________________________ > > That's what I thought when I first saw this error. > But, I don't understand why is it trying to use sockets. > I have ssl=off in postgresql.conf on both sides. AFAIK on Windows Postgres maps sockets to localhost. Anyway that was why I was asking about your pg_hba.conf and PUBLICATION and SUBSCRIPTION settings, to see what is actually being requested? > > Regards, > Igor > -- Adrian Klaver adrian.klaver@aklaver.com
-----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Tuesday, May 23, 2017 9:45 AM To: Igor Neyman <ineyman@perceptron.com>; pgsql-general@postgresql.org Subject: Re: [GENERAL] logical replication in PG10 BETA On 05/23/2017 06:33 AM, Igor Neyman wrote: > > Yeah, my mistake I was working with pglogical and it got stuck in my head. >> >> Any other ideas? > > Have you gone through these sections of the docs?: > > https://www.postgresql.org/docs/10/static/logical-replication-config.h > tml > > https://www.postgresql.org/docs/10/static/logical-replication-quick-se > tup.html > -- > Adrian Klaver > adrian.klaver@aklaver.com > ______________________________________________________________________ > _________________________________ > > Yes. All parameters mentioned in the docs configured properly. > CREATE PUBLICATION works fine. > > pg_hba.conf modified to allow "replication" user to connect to the server. > In the original email I mentioned that I configured Postgres_fdw foreign server that connects using the same connectionoptions as I'm using in CREATE SUBSCRIPTION, and foreign server works fine, which to me proves that there is noissues with pg_hba.conf. What are the contents of the pg_hba.conf file? What are the CREATE PUBLICATION and CREATE SUBSCRIPTION commands you are using? > > Regards, > Igor > -- Adrian Klaver adrian.klaver@aklaver.com ___________________________________________________________________________________________________________ These two systems are my "sandboxes". So, here is pg_hba on the publishing server: # IPv4 local connections: host all all 127.0.0.1/32 md5 host all repl_user 0.0.0.0/0 md5 # IPv6 local connections: host all all ::1/128 md5 SQL on the publisher: CREATE TABLE test_repl( int_c int not null, text_c text not null, ts_c timestamp null, CONSTRAINT pk_test_repl primary key(int_c) ); insert into test_repl(int_c, text_c, ts_c) values (1, 'one', current_timestamp), (2, 'two', current_timestamp + interval '1 second'), (3, 'three', current_timestamp + interval '2 second'); CREATE PUBLICATION my_first_publ FOR TABLE test_repl; The, on Subscriber: CREATE TABLE test_repl( int_c int not null, text_c text not null, ts_c timestamp null, CONSTRAINT pk_test_repl primary key(int_c) ); CREATE SUBSCRIPTION my_furst_subs CONNECTION 'dbname=repl host=pub_machine port=5432 user=repl_user' PUBLICATION my_first_publ; The last command results in: 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) ********** Error ********** 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) SQL state: XX000 The same connection string works fine, when I create foreign data wrapper: CREATE SERVER pub_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'pub_machine', port '5432', dbname 'repl'); Regards, Igor
On 05/23/2017 07:05 AM, Igor Neyman wrote: >> >> pg_hba.conf modified to allow "replication" user to connect to the server. >> In the original email I mentioned that I configured Postgres_fdw foreign server that connects using the same connectionoptions as I'm using in CREATE SUBSCRIPTION, and foreign server works fine, which to me proves that there is noissues with pg_hba.conf. > > What are the contents of the pg_hba.conf file? > > What are the CREATE PUBLICATION and CREATE SUBSCRIPTION commands you are using? > > >> >> Regards, >> Igor >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > ___________________________________________________________________________________________________________ > > These two systems are my "sandboxes". Define sandbox: Are they on VMs on a single machine or something else? > > So, here is pg_hba on the publishing server: > > # IPv4 local connections: > host all all 127.0.0.1/32 md5 > host all repl_user 0.0.0.0/0 md5 > # IPv6 local connections > host all all ::1/128 md5 I do not see an IPv6 all hosts entry for repl_user, Something like: host all repl_user ::/0 md5 > > SQL on the publisher: > > CREATE TABLE test_repl( > int_c int not null, > text_c text not null, > ts_c timestamp null, > CONSTRAINT pk_test_repl primary key(int_c) > ); > insert into test_repl(int_c, text_c, ts_c) values (1, 'one', current_timestamp), > (2, 'two', current_timestamp + interval '1 second'), (3, 'three', current_timestamp + interval '2 second'); > CREATE PUBLICATION my_first_publ FOR TABLE test_repl; > > The, on Subscriber: > > CREATE TABLE test_repl( > int_c int not null, > text_c text not null, > ts_c timestamp null, > CONSTRAINT pk_test_repl primary key(int_c) > ); > CREATE SUBSCRIPTION my_furst_subs CONNECTION 'dbname=repl host=pub_machine port=5432 user=repl_user' PUBLICATION my_first_publ; > > The last command results in: > > 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) > ********** Error ********** > > 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) > SQL state: XX000 > > The same connection string works fine, when I create foreign data wrapper: > > CREATE SERVER pub_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'pub_machine', port '5432', dbname 'repl'); > > Regards, > Igor > -- Adrian Klaver adrian.klaver@aklaver.com
-----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Tuesday, May 23, 2017 10:31 AM To: Igor Neyman <ineyman@perceptron.com>; pgsql-general@postgresql.org Subject: Re: [GENERAL] logical replication in PG10 BETA On 05/23/2017 07:05 AM, Igor Neyman wrote: >> >> pg_hba.conf modified to allow "replication" user to connect to the server. >> In the original email I mentioned that I configured Postgres_fdw foreign server that connects using the same connectionoptions as I'm using in CREATE SUBSCRIPTION, and foreign server works fine, which to me proves that there is noissues with pg_hba.conf. > > What are the contents of the pg_hba.conf file? > > What are the CREATE PUBLICATION and CREATE SUBSCRIPTION commands you are using? > > >> >> Regards, >> Igor >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > ______________________________________________________________________ > _____________________________________ > > These two systems are my "sandboxes". Define sandbox: Are they on VMs on a single machine or something else? > > So, here is pg_hba on the publishing server: > > # IPv4 local connections: > host all all 127.0.0.1/32 md5 > host all repl_user 0.0.0.0/0 md5 > # IPv6 local connections > host all all ::1/128 md5 I do not see an IPv6 all hosts entry for repl_user, Something like: host all repl_user ::/0 md5 _______________________________________________________________________________________ Not a single machine. Publisher is on a physical machine. Subscriber is on VM. Nothing changed after I added IPV6 entry: host all repl_user ::/0 md5 on publishing machine. Regards, Igor
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Adrian Klaver Sent: Tuesday, May 23, 2017 10:04 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] logical replication in PG10 BETA On 05/23/2017 06:52 AM, Igor Neyman wrote: > > > sounds more like a SSL setup problem between the two Postgres servers, not really related to logical replication. > > Can you try to setup the connections without SSL? > > ______________________________________________________________________ > __________________________________ > > That's what I thought when I first saw this error. > But, I don't understand why is it trying to use sockets. > I have ssl=off in postgresql.conf on both sides. AFAIK on Windows Postgres maps sockets to localhost. Anyway that was why I was asking about your pg_hba.conf and PUBLICATION and SUBSCRIPTION settings, to see what is actuallybeing requested? > > Regards, > Igor > -- Adrian Klaver adrian.klaver@aklaver.com _________________________________________________________________________________________________________________ Interestingly, when I add this line to pg_hba.conf: local all all md5 Postgres is not starting with the following error in the log file: 2017-05-23 11:02:10.397 EDT [4796] LOG: local connections are not supported by this build 2017-05-23 11:02:10.397 EDT [4796] CONTEXT: line 1 of configuration file "C:/PostgreSQL/10/data/pg_hba.conf" 2017-05-23 11:02:10.398 EDT [4796] FATAL: could not load pg_hba.conf 2017-05-23 11:02:10.403 EDT [4796] LOG: database system is shut down The line I added is the first line. " LOG: local connections are not supported by this build" - is this related to my problems with CREATE SUBSCRIPTION? And another question: Anyone from 2ndQuadrant could verify that pglogical included in the core PG10 BETA (packed by EnterpriseDB) supports Windows? Regards, Igor
On Tue, 23 May 2017 15:15:46 +0000, Igor Neyman <ineyman@perceptron.com> wrote: >Interestingly, when I add this line to pg_hba.conf: > >local all all md5 > >Postgres is not starting with the following error in the log file: > >2017-05-23 11:02:10.397 EDT [4796] LOG: local connections are not supported by this build >2017-05-23 11:02:10.397 EDT [4796] CONTEXT: line 1 of configuration file "C:/PostgreSQL/10/data/pg_hba.conf" >2017-05-23 11:02:10.398 EDT [4796] FATAL: could not load pg_hba.conf >2017-05-23 11:02:10.403 EDT [4796] LOG: database system is shut down > >The line I added is the first line. >" LOG: local connections are not supported by this build" - is this related to my problems with CREATE SUBSCRIPTION? Windows does not support "local" domain sockets as in Unix/Linux. Local loopback connections on Windows are made using normal TCP or UDP sockets. You need to configure permissions as with any remote client, only specifying the localhost addresses 127.0.0.1 and/or ::1. George
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of George Neuner Sent: Tuesday, May 23, 2017 4:22 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] logical replication in PG10 BETA On Tue, 23 May 2017 15:15:46 +0000, Igor Neyman <ineyman@perceptron.com> wrote: >Interestingly, when I add this line to pg_hba.conf: > >local all all md5 > >Postgres is not starting with the following error in the log file: > >2017-05-23 11:02:10.397 EDT [4796] LOG: local connections are not >supported by this build >2017-05-23 11:02:10.397 EDT [4796] CONTEXT: line 1 of configuration file "C:/PostgreSQL/10/data/pg_hba.conf" >2017-05-23 11:02:10.398 EDT [4796] FATAL: could not load pg_hba.conf >2017-05-23 11:02:10.403 EDT [4796] LOG: database system is shut down > >The line I added is the first line. >" LOG: local connections are not supported by this build" - is this related to my problems with CREATE SUBSCRIPTION? Windows does not support "local" domain sockets as in Unix/Linux. Local loopback connections on Windows are made using normal TCP or UDP sockets. You need to configure permissions as withany remote client, only specifying the localhost addresses 127.0.0.1 and/or ::1. George ____________________________________________________________________________________________________ Well, I have that in pg_hba: # IPv4 local connections: host all all 127.0.0.1/32 md5 host all repl_user 0.0.0.0/0 md5 # IPv6 local connections host all all ::1/128 md5 Regards, Igor
On 05/23/2017 08:15 AM, Igor Neyman wrote: > > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Adrian Klaver > Sent: Tuesday, May 23, 2017 10:04 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] logical replication in PG10 BETA > > On 05/23/2017 06:52 AM, Igor Neyman wrote: >> > >> >> sounds more like a SSL setup problem between the two Postgres servers, not really related to logical replication. >> >> Can you try to setup the connections without SSL? >> >> ______________________________________________________________________ >> __________________________________ >> >> That's what I thought when I first saw this error. >> But, I don't understand why is it trying to use sockets. >> I have ssl=off in postgresql.conf on both sides. > > AFAIK on Windows Postgres maps sockets to localhost. > > Anyway that was why I was asking about your pg_hba.conf and PUBLICATION and SUBSCRIPTION settings, to see what is actuallybeing requested? > >> >> Regards, >> Igor >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > _________________________________________________________________________________________________________________ > > Interestingly, when I add this line to pg_hba.conf: > > local all all md5 > > Postgres is not starting with the following error in the log file: > > 2017-05-23 11:02:10.397 EDT [4796] LOG: local connections are not supported by this build > 2017-05-23 11:02:10.397 EDT [4796] CONTEXT: line 1 of configuration file "C:/PostgreSQL/10/data/pg_hba.conf" > 2017-05-23 11:02:10.398 EDT [4796] FATAL: could not load pg_hba.conf > 2017-05-23 11:02:10.403 EDT [4796] LOG: database system is shut down > > The line I added is the first line. > " LOG: local connections are not supported by this build" - is this related to my problems with CREATE SUBSCRIPTION? > > And another question: > Anyone from 2ndQuadrant could verify that pglogical included in the core PG10 BETA (packed by EnterpriseDB) supports Windows? I think you are going to need to be more specific. I see pglogical as the extension provided by 2ndQuadrant and builtin logical as provided by the core server. If you are referring to the extension then see my post upstream where I reference the pglogical issue that addresses this at least partly. > > Regards, > Igor > -- Adrian Klaver adrian.klaver@aklaver.com
On 05/23/2017 01:38 PM, Igor Neyman wrote: > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of George Neuner > > > Windows does not support "local" domain sockets as in Unix/Linux. > > Local loopback connections on Windows are made using normal TCP or UDP sockets. You need to configure permissions as withany remote client, only specifying the localhost addresses 127.0.0.1 and/or ::1. > > George > ____________________________________________________________________________________________________ > > Well, I have that in pg_hba: > > # IPv4 local connections: > host all all 127.0.0.1/32 md5 > host all repl_user 0.0.0.0/0 md5 > # IPv6 local connections > host all all ::1/128 md5 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 > > Regards, > Igor > > -- Adrian Klaver adrian.klaver@aklaver.com
-----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Tuesday, May 23, 2017 5:48 PM To: Igor Neyman <ineyman@perceptron.com>; pgsql-general@postgresql.org Subject: Re: [GENERAL] logical replication in PG10 BETA On 05/23/2017 08:15 AM, Igor Neyman wrote: > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Adrian Klaver > Sent: Tuesday, May 23, 2017 10:04 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] logical replication in PG10 BETA > > On 05/23/2017 06:52 AM, Igor Neyman wrote: >> > >> >> sounds more like a SSL setup problem between the two Postgres servers, not really related to logical replication. >> >> Can you try to setup the connections without SSL? >> >> _____________________________________________________________________ >> _ >> __________________________________ >> >> That's what I thought when I first saw this error. >> But, I don't understand why is it trying to use sockets. >> I have ssl=off in postgresql.conf on both sides. > > AFAIK on Windows Postgres maps sockets to localhost. > > Anyway that was why I was asking about your pg_hba.conf and PUBLICATION and SUBSCRIPTION settings, to see what is actuallybeing requested? > >> >> Regards, >> Igor >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > ______________________________________________________________________ > ___________________________________________ > > Interestingly, when I add this line to pg_hba.conf: > > local all all md5 > > Postgres is not starting with the following error in the log file: > > 2017-05-23 11:02:10.397 EDT [4796] LOG: local connections are not > supported by this build > 2017-05-23 11:02:10.397 EDT [4796] CONTEXT: line 1 of configuration file "C:/PostgreSQL/10/data/pg_hba.conf" > 2017-05-23 11:02:10.398 EDT [4796] FATAL: could not load pg_hba.conf > 2017-05-23 11:02:10.403 EDT [4796] LOG: database system is shut down > > The line I added is the first line. > " LOG: local connections are not supported by this build" - is this related to my problems with CREATE SUBSCRIPTION? > > And another question: > Anyone from 2ndQuadrant could verify that pglogical included in the core PG10 BETA (packed by EnterpriseDB) supports Windows? I think you are going to need to be more specific. I see pglogical as the extension provided by 2ndQuadrant and builtin logicalas provided by the core server. If you are referring to the extension then see my post upstream where I referencethe pglogical issue that addresses this at least partly. > > Regards, > Igor > -- Adrian Klaver adrian.klaver@aklaver.com ______________________________________________________________________________________________________ From the very beginning in this thread I was talking about logical replication built-in core server PG10 BETA, not pglogicalextension. Sorry, if I didn't make it clear. Regards, Igor
-----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. Regards, Igor
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 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? > > Regards, > Igor > -- Adrian Klaver adrian.klaver@aklaver.com
-----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
CREATESUBSCRIPTION - 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
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
-----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
On 05/25/2017 11:09 AM, Igor Neyman wrote: > __________________________________________________________________________________________________________ > > 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 filewas going to put me on a "right track" because I was not using/didn't have password file. I may have steered you wrong on this. The more think about it the more I realize that the server will probably not read the .pgpass 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. Two options come to mind to test whether the password is the problem: 1) Include the password in the connection string in CREATE SUBSCRIPTION. 2) Change the auth method in pg_hba.conf on the publisher server from md5 to trust and reload the server. Neither is optimal, still it is a starting point. > > I reported my problem as a bug (bug# 14669), but so far it doesn't seem to attract any interest. > > Regards, > Igor Neyman > -- Adrian Klaver adrian.klaver@aklaver.com
-----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Thursday, May 25, 2017 3:13 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/25/2017 11:09 AM, Igor Neyman wrote: > ______________________________________________________________________ > ____________________________________ > > 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 filewas going to put me on a "right track" because I was not using/didn't have password file. I may have steered you wrong on this. The more think about it the more I realize that the server will probably not read the.pgpass 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. Two options come to mind to test whether the password is the problem: 1) Include the password in the connection string in CREATE SUBSCRIPTION. 2) Change the auth method in pg_hba.conf on the publisher server from md5 to trust and reload the server. Neither is optimal, still it is a starting point. -- Adrian Klaver adrian.klaver@aklaver.com ________________________________________________________________________________________________ Tried the 1) even though CREATE SUBSCRIPTION isn't expecting password in connections string, it doesn't help. Tried the 2) - didn't help either. In both cases still getting: 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) Regards, Igor