Re: [GENERAL] logical replication in PG10 BETA - Mailing list 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
connectionoptions as I'm using in CREATE SUBSCRIPTION, and foreign server works fine, which to me proves that there is
noissues with pg_hba.conf. 
>
> What are the contents of the pg_hba.conf file?
>
> What are the CREATE PUBLICATION and CREATE SUBSCRIPTION commands you are using?
>
>
>>
>> Regards,
>> Igor
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
> ___________________________________________________________________________________________________________
>
> These two systems are my "sandboxes".

Define sandbox:

Are they on VMs on a single machine or something else?

>
> So, here is pg_hba on the publishing server:
>
> # IPv4 local connections:
> host    all             all             127.0.0.1/32            md5
> host    all             repl_user             0.0.0.0/0            md5
> # IPv6 local connections > host    all             all             ::1/128                 md5

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

host    all   repl_user  ::/0  md5



>
> SQL on the publisher:
>
>   CREATE TABLE test_repl(
>         int_c        int not null,
>         text_c       text not null,
>         ts_c         timestamp null,
>         CONSTRAINT pk_test_repl primary key(int_c)
>         );
> insert into test_repl(int_c, text_c, ts_c) values (1, 'one', current_timestamp),
>     (2, 'two', current_timestamp + interval '1 second'), (3, 'three', current_timestamp + interval '2 second');
> CREATE PUBLICATION my_first_publ FOR TABLE test_repl;
>
> The, on Subscriber:
>
> CREATE TABLE test_repl(
>         int_c        int not null,
>         text_c       text not null,
>         ts_c         timestamp null,
>         CONSTRAINT pk_test_repl primary key(int_c)
>         );
> CREATE SUBSCRIPTION my_furst_subs CONNECTION 'dbname=repl host=pub_machine port=5432 user=repl_user' PUBLICATION
my_first_publ;
>
> The last command results in:
>
> ERROR:  could not connect to the publisher: could not send data to server: Socket is not connected (0x00002749/10057)
> could not send SSL negotiation packet: Socket is not connected (0x00002749/10057)
> ********** Error **********
>
> ERROR: could not connect to the publisher: could not send data to server: Socket is not connected (0x00002749/10057)
> could not send SSL negotiation packet: Socket is not connected (0x00002749/10057)
> SQL state: XX000
>
> The same connection string works fine, when I create foreign data wrapper:
>
> CREATE SERVER pub_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'pub_machine', port '5432', dbname 'repl');
>
> Regards,
> Igor
>


--
Adrian Klaver
adrian.klaver@aklaver.com