Re: Publication/Subscription Questions

On 07/27/2018 01:34 AM, xOChilpili wrote:
> Hi everyone,
> 
> Im just testing/learning how subscriptions and publications work, then 
> this is what i have done until now:
> 
> Server A :
> create database test_pubsubs;
> 
> create table foo(
>   id_foo serial not null primary key,
> foo varchar(20) not null
> ​);​
> insert into foo values(1,'foo');
> insert into foo values(2,'foobar');
> 
> 
> create table foobar(
>    id_foobar serial not null primary key,
>   foobar varchar(20) not null
> );
> insert into foobar values(1,'foobaz');
> insert into foobar values(2,'foobax');
> 
> 
> create publication my_publication for table foo;
> 
> Server B :
> 
> create database test_pubsubs;
> 
> create table foo(
>   id_foo serial not null primary key,
> foo varchar(20) not null
> ​);​
> 
> create table foobar(
>    id_foobar serial not null primary key,
>   foobar varchar(20) not null
> );
> 
> 
> create subscription my_subscription connection 'host=server_a 
> dbname=test_pubsubs user=my_user password=my_password port=5432' 
> publication my_publication;
> 
> select * from foo;
> id_foo | foo
> 1 | foo
> 2 | foobar
> select * from foobar;
> 0 Rows
> 
> 
> Server A:
> 
> alter publication my_publication add table foobar;
> 
> Server B:
> alter subscription my_subscription refresh publication;
> 
> select * from foobar;
> id_foobar | foobar
> 1 | foobaz
> 2 | foobax
> 
> Then, here's my question :
> 
> Still on Server B:
> 
> delete from foo;
> delete from foobar;
> 
> select * from foo;
> 0 Rows
> select * from foobar;
> 0 Rows
> 
> alter subscription my_subscription refresh publication;
> 
> select * from foo;
> 0 Rows
> select * from foobar;
> 0 Rows
> 
> Why ? If i remove rows, from Server B and refresh publication, why data 
> is not re-sync ?

https://www.postgresql.org/docs/10/static/sql-altersubscription.html

"Fetch missing table information from publisher. This will start 
replication of tables that were added to the subscribed-to publications 
since the last invocation of REFRESH PUBLICATION or since CREATE 
SUBSCRIPTION."

You have not added a table to the publication there is nothing to sync.

> But if i :
> drop subscription my_subscription;
> and create it again, then i have all data back...
> 
> 
> Thanks a lot!
> 
> 
> -- 
> xOCh
> 
> 
> --
> PAranoids Group
> 
> 218


-- 
Adrian Klaver
adrian.klaver@aklaver.com