Re: 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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com