Re: [GENERAL] logical replication in PG10 BETA

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] 
Sent: Tuesday, May 23, 2017 9:45 AM
To: Igor Neyman <ineyman@perceptron.com>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] logical replication in PG10 BETA

On 05/23/2017 06:33 AM, Igor Neyman wrote:

>
> Yeah, my mistake I was working with pglogical and it got stuck in my head.
>>
>> Any other ideas?
>
> Have you gone through these sections of the docs?:
>
> https://www.postgresql.org/docs/10/static/logical-replication-config.h
> tml
>
> https://www.postgresql.org/docs/10/static/logical-replication-quick-se
> tup.html
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
> ______________________________________________________________________
> _________________________________
>
> Yes. All parameters mentioned in the docs configured properly.
> CREATE PUBLICATION works fine.
>
> pg_hba.conf modified to allow "replication" user to connect to the server.
> In the original email I mentioned that I configured Postgres_fdw foreign server that connects using the same
connectionoptions as I'm using in CREATE SUBSCRIPTION, and foreign server works fine, which to me proves that there is
noissues with pg_hba.conf.
 

What are the contents of the pg_hba.conf file?

What are the CREATE PUBLICATION and CREATE SUBSCRIPTION commands you are using?


>
> Regards,
> Igor
>


--
Adrian Klaver
adrian.klaver@aklaver.com
___________________________________________________________________________________________________________

These two systems are my "sandboxes".

So, here is pg_hba on the publishing server:

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

SQL on the publisher:

 CREATE TABLE test_repl(
       int_c        int not null,
       text_c       text not null,
       ts_c         timestamp null,
       CONSTRAINT pk_test_repl primary key(int_c)
       );
insert into test_repl(int_c, text_c, ts_c) values (1, 'one', current_timestamp), 
    (2, 'two', current_timestamp + interval '1 second'), (3, 'three', current_timestamp + interval '2 second');
CREATE PUBLICATION my_first_publ FOR TABLE test_repl;

The, on Subscriber:

CREATE TABLE test_repl(
       int_c        int not null,
       text_c       text not null,
       ts_c         timestamp null,
       CONSTRAINT pk_test_repl primary key(int_c)
       );
CREATE SUBSCRIPTION my_furst_subs CONNECTION 'dbname=repl host=pub_machine port=5432 user=repl_user' PUBLICATION
my_first_publ;

The last command results in:

ERROR:  could not connect to the publisher: could not send data to server: Socket is not connected (0x00002749/10057)
could not send SSL negotiation packet: Socket is not connected (0x00002749/10057)
********** Error **********

ERROR: could not connect to the publisher: could not send data to server: Socket is not connected (0x00002749/10057)
could not send SSL negotiation packet: Socket is not connected (0x00002749/10057)
SQL state: XX000

The same connection string works fine, when I create foreign data wrapper:

CREATE SERVER pub_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'pub_machine', port '5432', dbname 'repl');

Regards,
Igor