[GENERAL] Logical replication + before trigger = ERROR: attempted to lock invisible tuple

[Thomas Rosenstein]

Hi,

I'm trying to execute a BEFORE UPDATE trigger for my logical replication 
subscription with Postgresql 10.1 but the apply worker crashes with:


ERROR:  attempted to lock invisible tuple


The trigger creation:

CREATE TRIGGER customers_anonymize_before_update BEFORE UPDATE ON 
customers FOR EACH ROW EXECUTE PROCEDURE customers_anonymize();


The "BEFORE INSERT" trigger seems to work fine.

I found this Issue on 2ndQuadrant: 
https://github.com/2ndQuadrant/pglogical/issues/97


Is this on the radar? Is it already fixed?

Thanks
BR
Thomas


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

[GENERAL] Logical replication - behavior of REFRESH PUBLICATION's copy_data option

[Adam LaMore]

Hi all,

I'm exploring the new PG10 logical replication feature and trying to
understand how ALTER SUBSCRIPTION ... REFRESH PUBLICATION works.

My planned publication will have over 100 tables, some of which are quite
large. If I add a table to the publication, I understand that I have to use
the above command on the subscriber to refresh its subscription. The
default value of the copy_data option is true, but I'm unclear if that will
(A) re-copy all of the data for all of the tables in the publication; (B)
copy the data for only the newly added table; or (C) some other behavior.
So does anyone know which one it is?

If (A) is true, then I assume I can avoid that by setting copy_data =
false. If I do that, how do I copy only the data for the newly added table
from the publisher to the subscriber? Perhaps an out-of-band dump and
restore of just that table? And should that be before or after the call to
"REFRESH PUBLICATION?

Thanks in advance,
Adam

Re: [GENERAL] Logical Replication - test_decoding - unchanged-toast-datum

[Andres Freund]

On 2017-09-28 08:19:08 +0800, Craig Ringer wrote:
> This is one of the MANY reasons test_decoding isn't suitable as the base
> for a replication solution. It has "test" in its name for a reason.

FWIW, I don't see why the unchanged toast stuff is that. It's clearly
discernible from actual datums, so ...

I agree that test_decoding isn't a great base of a replication tool, but
I don't think it's completely unsuitable, and I also think that ship has
sailed.

Greetings,

Andres Freund


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

Re: [GENERAL] Logical Replication - test_decoding - unchanged-toast-datum

[Craig Ringer]

On 26 September 2017 at 05:01, Abhinav Singh 
wrote:

> Hello,
>
> I am currently using PostgreSQL Community version 9.4.9 and then using
> this instance, I am doing logical replication(using replication slots). I
> have created the replication slots using the following query:
>
> SELECT xlog_position FROM pg_create_logical_replication_
> slot('cjkimqvfuvixqyjd_00016389_e6f7c975_a311_4067_bcf1_a6accb57ab37',
> 'test_decoding')
>
> ...

> 3. So now I started my replication.
>
>
This is one of the MANY reasons test_decoding isn't suitable as the base
for a replication solution. It has "test" in its name for a reason.

Your replication model, whatever it is, is broken, since it's not handling
special cases like unchanged TOASTed values in UPDATEs. This is a bug in
your replication tool.


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

[GENERAL] Logical Replication - test_decoding - unchanged-toast-datum

[Abhinav Singh]

Hello,

I am currently using PostgreSQL Community version 9.4.9 and then using this
instance, I am doing logical replication(using replication slots). I have
created the replication slots using the following query:

SELECT xlog_position FROM
pg_create_logical_replication_slot('cjkimqvfuvixqyjd_00016389_e6f7c975_a311_4067_bcf1_a6accb57ab37',
'test_decoding')

So the issue that I am facing is because of the updates that are being done
to my table. I was able to reproduce the same issue again using the
following sample:
_
1. Table on the source(which is RDS PostgreSQL):

CREATE TABLE public.toast_test1
(
id SERIAL PRIMARY KEY NOT NULL,
is_not_toast INT,
is_toast VARCHAR(32767)
);
CREATE UNIQUE INDEX toast_test_id_uindex1 ON public.toast_test1 (id);

2. Insert some values:

INSERT INTO public.toast_test1
(is_not_toast, is_toast) VALUES
(0, (SELECT string_agg(series::text, ',')
FROM generate_series(1, 1000) AS series));

So basically, every time you execute the above query, a new row will be
inserted. So execute the same for 4-5 times.

3. So now I started my replication.

4. If for example, I am doing an update using the below mentioned query on
my source instance:

UPDATE public.toast_test SET is_not_toast = 1;

5. On the target, when I do a select * and see that the column with
character varying() datatype has changed to 'unchanged-toast-datum'.

6. So on further checking the replication slot at the time, when I issued
an update, I can see this:

postgres2@t1=> SELECT * FROM
pg_logical_slot_get_changes('cjkimqvfuvixqyjd_00016389_e6f7c975_a311_4067_bcf1_a6accb57ab37',
NULL, NULL);
  location   |  xid  |
  data
-+---+---
3D/95003D58 | 17974 | BEGIN 17974
3D/950049D0 | 17974 | table public.toast_test1: UPDATE: id[integer]:1
is_not_toast[integer]:1 is_toast[character varying]:unchanged-toast-datum
3D/95004A78 | 17974 | COMMIT 17974
(3 rows)

---

Even after setting the REPLICA IDENTITY to FULL for this table did not
help.
_

Kindly review and please share your comments on this matter.

[GENERAL] logical replication API to read WAL file through replication slot

[Dipesh Dangol]

hi,

I am trying to implement logical replication stream API of postgresql.
I am facing unusual connection breakdown problem. Here is the simple code
that I am
using to read WAL file:

String url = "jdbc:postgresql://pcnode2:5432/benchmarksql";
Properties props = new Properties();
PGProperty.USER.set(props, "benchmarksql");
PGProperty.PASSWORD.set(props, "benchmarksql");
PGProperty.ASSUME_MIN_SERVER_VERSION.set(props, "9.4");
PGProperty.REPLICATION.set(props, "database");
PGProperty.PREFER_QUERY_MODE.set(props, "simple");

Connection conn = DriverManager.getConnection(url, props);
PGConnection replConnection = conn.unwrap(PGConnection.class);

PGReplicationStream stream = replConnection.getReplicationAPI()
.replicationStream().logical()
.withSlotName("replication_slot3")
.withSlotOption("include-xids", true)
.withSlotOption("include-timestamp", "on")
.withSlotOption("skip-empty-xacts", true)
.withStatusInterval(20, TimeUnit.MILLISECONDS).start();
while (true) {

ByteBuffer msg = stream.read();

if (msg == null) {
TimeUnit.MILLISECONDS.sleep(10L);
continue;
}

int offset = msg.arrayOffset();
byte[] source = msg.array();
int length = source.length - offset;
String data = new String(source, offset, length);
   * System.out.println(data);*

stream.setAppliedLSN(stream.getLastReceiveLSN());
stream.setFlushedLSN(stream.getLastReceiveLSN());

}

Even the slightest modification in the code like commenting
*System.out.println(data)*;
which is just printing the data in the console, causes connection breakdown
problem with
following error msg

org.postgresql.util.PSQLException: Database connection failed when reading
from copy
at org.postgresql.core.v3.QueryExecutorImpl.readFromCopy(
QueryExecutorImpl.java:1028)
at org.postgresql.core.v3.CopyDualImpl.readFromCopy(
CopyDualImpl.java:41)
at org.postgresql.core.v3.replication.V3PGReplicationStream.
receiveNextData(V3PGReplicationStream.java:155)
at org.postgresql.core.v3.replication.V3PGReplicationStream.
readInternal(V3PGReplicationStream.java:124)
at org.postgresql.core.v3.replication.V3PGReplicationStream.read(
V3PGReplicationStream.java:70)
at Server.main(Server.java:52)
Caused by: java.net.SocketException: Socket closed
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
at java.net.SocketInputStream.read(SocketInputStream.java:171)
at java.net.SocketInputStream.read(SocketInputStream.java:141)
at org.postgresql.core.VisibleBufferedInputStream.readMore(
VisibleBufferedInputStream.java:140)
at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(
VisibleBufferedInputStream.java:109)
at org.postgresql.core.VisibleBufferedInputStream.read(
VisibleBufferedInputStream.java:191)
at org.postgresql.core.PGStream.receive(PGStream.java:495)
at org.postgresql.core.PGStream.receive(PGStream.java:479)
at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(
QueryExecutorImpl.java:1161)
at org.postgresql.core.v3.QueryExecutorImpl.readFromCopy(
QueryExecutorImpl.java:1026)
... 5 more

I am trying to implement some logic like filtering out the unrelated table
after reading log.
But due to this unusual behavior I couldn't implement properly.
Can somebody give me some hint how to solve this problem.

Thank you.

Dipesh Dangol

Re: [GENERAL] logical replication in PG10 BETA

[Igor Neyman]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Thursday, May 25, 2017 3:13 PM
To: Igor Neyman <iney...@perceptron.com>; George Neuner <gneun...@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 file was 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.kla...@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 (0x2749/10057)
could not send SSL negotiation packet: Socket is not connected 
(0x2749/10057)

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

Re: [GENERAL] logical replication in PG10 BETA

[Adrian Klaver]

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 file was 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.kla...@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

Re: [GENERAL] logical replication in PG10 BETA

[Igor Neyman]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Wednesday, May 24, 2017 7:06 PM
To: Igor Neyman <iney...@perceptron.com>; George Neuner <gneun...@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.kla...@aklaver.com]
> Sent: Wednesday, May 24, 2017 10:00 AM
> To: Igor Neyman <iney...@perceptron.com>; George Neuner 
> <gneun...@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.kla...@aklaver.com]
>> Sent: Tuesday, May 23, 2017 7:42 PM
>> To: Igor Neyman <iney...@perceptron.com>; George Neuner 
>> <gneun...@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.kla...@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?
>
> --
> Adrian Klaver
> adrian.kla...@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 (0x2749/10057)
>   could not send SSL negotiation packet: Socket is not connected 
> (0x2749/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

Re: [GENERAL] logical replication in PG10 BETA

[Adrian Klaver]

On 05/24/2017 08:30 AM, Igor Neyman wrote:

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Wednesday, May 24, 2017 10:00 AM
To: Igor Neyman <iney...@perceptron.com>; George Neuner <gneun...@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.kla...@aklaver.com]
Sent: Tuesday, May 23, 2017 7:42 PM
To: Igor Neyman <iney...@perceptron.com>; George Neuner
<gneun...@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.kla...@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?

--
Adrian Klaver
adrian.kla...@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 (0x2749/10057)
could not send SSL negotiation packet: Socket is not connected 
(0x2749/10057)
  STATEMENT: CREATE SUBSCRIPTION ...

Now, when on subscriber machine I use FDW to read foreign table (from 
publishing machine), then in Publisher's log I see the following info about 
connection:

2017-05-24 11:02:30.849 EDT [5100] LOG:  connection received: host=192.168.5.84 
port=64925
2017-05-24 11:02:30.856 EDT [5100] LOG:  connection authorized: user=repl_user 
database=repl

So, when using FDW Publisher's server logs both "connection received" and 
"connection authorized",
while when creating subscription Publisher logs only "connection received" and 
nothing else, even though both: FDW and CREATE SUBSCRIPTION - are using the same 
credentials (user=repl_user database=repl).


In a previous post you had:

CREATE SUBSCRIPTION my_furst_subs CONNECTION 'dbname=repl 
host=pub_machine port=5432 user=repl_user' PUBLICATION my_first_publ;


I assumed you had a .pgpass file on the the subscriber side, is that the 
case or are you using some other method to supply the password?




Any other thoughts?


No this taps me out.


Seems like a bug? I s there a place to report bugs for PG 10 BETA?


The bug reporting page:

https://www.postgresql.org/account/login/?next=/account/submitbug/
-


Regards,
Igor Neyman





--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] logical replication in PG10 BETA

[Igor Neyman]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Wednesday, May 24, 2017 10:00 AM
To: Igor Neyman <iney...@perceptron.com>; George Neuner <gneun...@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.kla...@aklaver.com]
> Sent: Tuesday, May 23, 2017 7:42 PM
> To: Igor Neyman <iney...@perceptron.com>; George Neuner 
> <gneun...@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.kla...@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?

--
Adrian Klaver
adrian.kla...@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 (0x2749/10057)
could not send SSL negotiation packet: Socket is not connected 
(0x2749/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).

Any other thoughts?
Seems like a bug? I s there a place to report bugs for PG 10 BETA?

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

Re: [GENERAL] logical replication in PG10 BETA

[Adrian Klaver]

On 05/24/2017 06:31 AM, Igor Neyman wrote:


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Tuesday, May 23, 2017 7:42 PM
To: Igor Neyman <iney...@perceptron.com>; George Neuner <gneun...@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.kla...@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.kla...@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

Re: [GENERAL] logical replication in PG10 BETA

[Igor Neyman]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Tuesday, May 23, 2017 7:42 PM
To: Igor Neyman <iney...@perceptron.com>; George Neuner <gneun...@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.kla...@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

Re: [GENERAL] logical replication in PG10 BETA

[Igor Neyman]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Tuesday, May 23, 2017 5:48 PM
To: Igor Neyman <iney...@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-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@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.kla...@aklaver.com
> __
> ___
>
> Interestingly, when I add this line to pg_hba.conf:
>
> localall 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.kla...@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

Re: [GENERAL] logical replication in PG10 BETA

[Adrian Klaver]

On 05/23/2017 01:38 PM, Igor Neyman wrote:

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@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:
  hostall all 127.0.0.1/32md5
  hostall repl_user 0.0.0.0/0md5
  # IPv6 local connections
  hostall 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.kla...@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

Re: [GENERAL] logical replication in PG10 BETA

[Adrian Klaver]

On 05/23/2017 08:15 AM, Igor Neyman wrote:


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@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.kla...@aklaver.com
_

Interestingly, when I add this line to pg_hba.conf:

localall 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.kla...@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

Re: [GENERAL] logical replication in PG10 BETA

[Igor Neyman]

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@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 +, Igor Neyman <iney...@perceptron.com> wrote:

>Interestingly, when I add this line to pg_hba.conf:
>
>localall 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:
 hostall all 127.0.0.1/32md5
 hostall repl_user 0.0.0.0/0md5
 # IPv6 local connections 
 hostall 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

Re: [GENERAL] logical replication in PG10 BETA

[George Neuner]

On Tue, 23 May 2017 15:15:46 +, Igor Neyman
 wrote:

>Interestingly, when I add this line to pg_hba.conf:
>
>localall 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

Re: [GENERAL] logical replication in PG10 BETA

[Igor Neyman]

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@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.kla...@aklaver.com
_

Interestingly, when I add this line to pg_hba.conf:

localall 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

Re: [GENERAL] logical replication in PG10 BETA

[Igor Neyman]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Tuesday, May 23, 2017 10:31 AM
To: Igor Neyman <iney...@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.kla...@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:
> hostall all 127.0.0.1/32md5
> hostall repl_user 0.0.0.0/0md5
> # IPv6 local connections > hostall all ::1/128
>  md5

I do not see an IPv6 all hosts entry for repl_user, Something like:

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

hostall   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

Re: [GENERAL] logical replication in PG10 BETA

[Adrian Klaver]

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.kla...@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:
hostall all 127.0.0.1/32md5
hostall repl_user 0.0.0.0/0md5
# IPv6 local connections > hostall all ::1/128  
   md5


I do not see an IPv6 all hosts entry for repl_user, Something like:

hostall   repl_user  ::/0  md5





SQL on the publisher:

  CREATE TABLE test_repl(
int_cint 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_cint 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 (0x2749/10057)
could not send SSL negotiation packet: Socket is not connected 
(0x2749/10057)
** Error **

ERROR: could not connect to the publisher: could not send data to server: 
Socket is not connected (0x2749/10057)
could not send SSL negotiation packet: Socket is not connected 
(0x2749/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.kla...@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

Re: [GENERAL] logical replication in PG10 BETA

[Igor Neyman]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Tuesday, May 23, 2017 9:45 AM
To: Igor Neyman <iney...@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.kla...@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.kla...@aklaver.com
___

These two systems are my "sandboxes".

So, here is pg_hba on the publishing server:

# IPv4 local connections:
hostall all 127.0.0.1/32md5
hostall repl_user 0.0.0.0/0md5
# IPv6 local connections:
hostall all ::1/128 md5

SQL on the publisher:

 CREATE TABLE test_repl(
   int_cint 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_cint 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 (0x2749/10057)
could not send SSL negotiation packet: Socket is not connected 
(0x2749/10057)
** Error **

ERROR: could not connect to the publisher: could not send data to server: 
Socket is not connected (0x2749/10057)
could not send SSL negotiation packet: Socket is not connected 
(0x2749/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

Re: [GENERAL] logical replication in PG10 BETA

[Adrian Klaver]

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.kla...@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

Re: [GENERAL] logical replication in PG10 BETA

[Igor Neyman]

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@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 (0x2749/10057) could not send SSL 
> negotiation packet: Socket is not connected (0x2749/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

Re: [GENERAL] logical replication in PG10 BETA

[Thomas Kellerer]

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 (0x2749/10057) could not send SSL
> negotiation packet: Socket is not connected (0x2749/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

Re: [GENERAL] logical replication in PG10 BETA

[Adrian Klaver]

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.kla...@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.kla...@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

Re: [GENERAL] logical replication in PG10 BETA

[Igor Neyman]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Tuesday, May 23, 2017 9:26 AM
To: Igor Neyman <iney...@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 (0x2749/10057) could not send SSL 
>> negotiation packet: Socket is not connected (0x2749/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.kla...@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

Re: [GENERAL] logical replication in PG10 BETA

[Adrian Klaver]

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 (0x2749/10057) could not send SSL
negotiation packet: Socket is not connected (0x2749/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.kla...@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

Re: [GENERAL] logical replication in PG10 BETA

[Igor Neyman]

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Monday, May 22, 2017 7:56 PM
To: Igor Neyman <iney...@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 (0x2749/10057) could not send SSL 
> negotiation packet: Socket is not connected (0x2749/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.kla...@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

Re: [GENERAL] logical replication in PG10 BETA

[Adrian Klaver]

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 (0x2749/10057) could not send SSL 
negotiation packet: Socket is not connected (0x2749/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.kla...@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

[GENERAL] logical replication in PG10 BETA

[Igor Neyman]

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 (0x2749/10057) could not send SSL negotiation 
packet: Socket is not connected (0x2749/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

Re: [GENERAL] Logical replication

[Adrian Klaver]

On 05/01/2017 09:34 AM, Adrian Klaver wrote:
> On 05/01/2017 09:25 AM, Peter Eisentraut wrote:
>> On 5/1/17 10:32, Adrian Klaver wrote:
>>> On 04/30/2017 09:07 AM, Adrian Klaver wrote:
 I have started looking at the logical replication feature in Postgres
 10. One thing I have no been able to determine is the interoperability
 between it and
 pglogical(www.2ndquadrant.com/en/resources/pglogical/). I
 know the one is derived from the other, what I can not find is
 whether a
 Postgres 9.4 instance with the pglogical extension installed can
 communicate with a Postgres 10 instance using the built in code?
>>
>>> Some testing says the answer is no:
>>
>> correct
>>
>>> If I am correct, this means from 9.4 <--> 10 and points in between you
>>> would need to use the pglogical extension on both ends.
>>
>> correct
>>
>>> Going from 10
>>> --> you could use the builtin logical replication. This leads to another
>>> question. Is is possible to use both at the same time?:
>>>
>>> 9.4   --->   10(instance 1)---> 10(instance 2)
>>> pglogicalpglogical
>>>   builtinbuiltin
>>
>> That is possible.
> 
> Thanks for the information.
> 
>>
>> pglogical will continue to exist, so you can also keep using it if you
>> already have it.
>>
> 
> I tried building the pglogical extension against Postgres 10 and it
> seems that is not possible yet, which tracks the information on the
> pglogical page:
> 
> https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/
> 
> 
> I went to the GitHub repo:
> 
> https://github.com/2ndQuadrant/pglogical
> 
> and did not see anything that looks ready for Postgres 10.
> 
> Am I missing something or is this something for the future?

The present as of now:

https://www.postgresql.org/message-id/CAH%2BGA0o5B89eDJjoKO-yN%3DXQEsC%2BpA2n9LuQPun0EAz9fVcUEA%40mail.gmail.com


> 
> 
> 
> 


-- 
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Logical replication

[Adrian Klaver]

On 05/01/2017 09:25 AM, Peter Eisentraut wrote:

On 5/1/17 10:32, Adrian Klaver wrote:

On 04/30/2017 09:07 AM, Adrian Klaver wrote:

I have started looking at the logical replication feature in Postgres
10. One thing I have no been able to determine is the interoperability
between it and pglogical(www.2ndquadrant.com/en/resources/pglogical/). I
know the one is derived from the other, what I can not find is whether a
Postgres 9.4 instance with the pglogical extension installed can
communicate with a Postgres 10 instance using the built in code?



Some testing says the answer is no:


correct


If I am correct, this means from 9.4 <--> 10 and points in between you
would need to use the pglogical extension on both ends.


correct


Going from 10
--> you could use the builtin logical replication. This leads to another
question. Is is possible to use both at the same time?:

9.4   --->   10(instance 1)---> 10(instance 2)
pglogicalpglogical
  builtinbuiltin


That is possible.


Thanks for the information.



pglogical will continue to exist, so you can also keep using it if you
already have it.



I tried building the pglogical extension against Postgres 10 and it 
seems that is not possible yet, which tracks the information on the 
pglogical page:


https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/

I went to the GitHub repo:

https://github.com/2ndQuadrant/pglogical

and did not see anything that looks ready for Postgres 10.

Am I missing something or is this something for the future?




--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Logical replication

[Peter Eisentraut]

On 5/1/17 10:32, Adrian Klaver wrote:
> On 04/30/2017 09:07 AM, Adrian Klaver wrote:
>> I have started looking at the logical replication feature in Postgres
>> 10. One thing I have no been able to determine is the interoperability
>> between it and pglogical(www.2ndquadrant.com/en/resources/pglogical/). I
>> know the one is derived from the other, what I can not find is whether a
>> Postgres 9.4 instance with the pglogical extension installed can
>> communicate with a Postgres 10 instance using the built in code?

> Some testing says the answer is no:

correct

> If I am correct, this means from 9.4 <--> 10 and points in between you 
> would need to use the pglogical extension on both ends.

correct

> Going from 10
> --> you could use the builtin logical replication. This leads to another 
> question. Is is possible to use both at the same time?:
> 
> 9.4   --->   10(instance 1)---> 10(instance 2)
> pglogicalpglogical
>   builtinbuiltin

That is possible.

pglogical will continue to exist, so you can also keep using it if you
already have it.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [GENERAL] Logical replication

[Adrian Klaver]

On 04/30/2017 09:07 AM, Adrian Klaver wrote:

I have started looking at the logical replication feature in Postgres
10. One thing I have no been able to determine is the interoperability
between it and pglogical(www.2ndquadrant.com/en/resources/pglogical/). I
know the one is derived from the other, what I can not find is whether a
Postgres 9.4 instance with the pglogical extension installed can
communicate with a Postgres 10 instance using the built in code?



Some testing says the answer is no:

postgres=# CREATE SUBSCRIPTION pgsql10sub CONNECTION 
'dbname=replication_test host=localhost user=postgres port=5412' 
PUBLICATION pgsql94;
ERROR:  could not receive list of replicated tables from the publisher: 
ERROR:  syntax error


If I am correct, this means from 9.4 <--> 10 and points in between you 
would need to use the pglogical extension on both ends. Going from 10 
--> you could use the builtin logical replication. This leads to another 
question. Is is possible to use both at the same time?:


9.4   --->   10(instance 1)---> 10(instance 2)
pglogicalpglogical
 builtinbuiltin

--
Adrian Klaver
adrian.kla...@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

[GENERAL] Logical replication

[Adrian Klaver]

I have started looking at the logical replication feature in Postgres 
10. One thing I have no been able to determine is the interoperability 
between it and pglogical(www.2ndquadrant.com/en/resources/pglogical/). I 
know the one is derived from the other, what I can not find is whether a 
Postgres 9.4 instance with the pglogical extension installed can 
communicate with a Postgres 10 instance using the built in code?


--
Adrian Klaver
adrian.kla...@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

[GENERAL] Logical Replication: adjacent COMMIT messages with the wrong StartLSN

[Stefan Smith]

Hi,

I have been evaluating the logical replication feature in v42.0.0 against
postgres v9.6. One scenario I'm testing is streaming a high volume of
transactions, where each transaction contains multiple INSERTs.

Sometimes, two transaction COMMITS are side by side in the DB transaction
log, and so the transactions arrive one after the other in the logical
replication stream. This is expected behaviour.

I apply and flush the LastReceivedLSN and force an update to the server
after every COMMIT message. I'm also disconnecting and reconnecting on
every fourth INSERT received, to stress test the recovery behaviour.

In most cases I see the following expected behaviour:

The WAL might have (with made-up LSNs for illustrative purposes):

LSN=10  BEGIN  (TXN 1)
LSN=20  BEGIN  (TXN 2)
LSN=30  INSERT (TXN 1)
LSN=40  INSERT (TXN 2)
LSN=50  INSERT (TXN 1)
LSN=60  INSERT (TXN 2)
LSN=70  INSERT (TXN 1)
LSN=80  INSERT (TXN 2)
LSN=90  COMMIT (TXN 1)
LSN=100 COMMIT (TXN 2)

And so the stream receives:

BEGIN  (TXN 1) LastReceivedLSN=19
INSERT (TXN 1) LastReceivedLSN=39
INSERT (TXN 1) LastReceivedLSN=59
INSERT (TXN 1) LastReceivedLSN=79
COMMIT (TXN 1) LastReceivedLSN=99

BEGIN  (TXN 2) LastReceivedLSN=29
INSERT (TXN 2) LastReceivedLSN=49

BEGIN  (TXN 2) LastReceivedLSN=29
INSERT (TXN 2) LastReceivedLSN=49
INSERT (TXN 2) LastReceivedLSN=69
INSERT (TXN 2) LastReceivedLSN=89
COMMIT (TXN 2) LastReceivedLSN=109

The above behaviour makes sense since the replication slot's
confirmed_flush_lsn=99 upon reconnect.

My issue: occasionally after reconnecting, I observe that the INSERTs for
TXN 2 are not resent and instead the stream moves on to TXN 3.

With the same WAL as above, the stream looks like:

BEGIN  (TXN 1) LastReceivedLSN=19
INSERT (TXN 1) LastReceivedLSN=39
INSERT (TXN 1) LastReceivedLSN=59
INSERT (TXN 1) LastReceivedLSN=79
COMMIT (TXN 1) LastReceivedLSN=109 <-- This LSN is wrong!

BEGIN  (TXN 2) LastReceivedLSN=29
INSERT (TXN 2) LastReceivedLSN=49

BEGIN  (TXN 3) ...

Debugging the JDBC driver and comparing it with the WAL (via pg_xlogdump),
it seems that on these occasions COMMIT (TXN 1) arrives with the correct
payload but with StartLSN set to the StartLSN for COMMIT (TXN 2)! The JDBC
driver computes COMMIT (TXN 1) LastReceivedLSN = COMMIT (TXN 2) StartLSN +
length of COMMIT (TXN 1) payload. This causes us to unwittingly set
confirmed_flush_lsn to the end WAL position of COMMIT (TXN 2) before
disconnecting, meaning I don't decode TXN 2 after reconnection.

Is this a known issue? Is it caused by the JDBC driver or something in the
server?