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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----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 of the 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 of the 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----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 of the 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 options as I'm using in CREATE SUBSCRIPTION, and foreign server works fine, which to me proves that there is no issues with pg_hba.conf.

Regards,
Igor

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 connection options as I'm using in CREATE SUBSCRIPTION, and foreign server works fine, which to me proves that there is no issues 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----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 connection options as I'm using in CREATE SUBSCRIPTION, and foreign server works fine, which to me proves that there is no issues 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 connection options as I'm using in CREATE SUBSCRIPTION, and foreign server works fine, which to me proves that there is no issues 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----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 connection options as I'm using in CREATE SUBSCRIPTION, and foreign server works fine, which to me proves that there is no issues 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----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 actually being 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----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 with any 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 actually being 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 with any 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----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 actually being 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
______________________________________________________________________________________________________

From the very beginning in this thread I was talking about logical replication built-in core server PG10 BETA, not pglogical extension.
Sorry, if I didn't make it clear.

Regards,
Igor

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----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, and it uses the same connection string as CREATE SUBSCRIPTION statement.

Regards,
Igor

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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, 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?

Regards,
Igor

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general