Re: logical replication in PG10 BETA

[Igor Neyman <ineyman@xxxxxxxxxxxxxx>]

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@xxxxxxxxxxx] 
Sent: Tuesday, May 23, 2017 9:45 AM
To: Igor Neyman <ineyman@xxxxxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx
Subject: Re:  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@xxxxxxxxxxx
> ______________________________________________________________________
> _________________________________
>
> 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@xxxxxxxxxxx
___________________________________________________________________________________________________________

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general