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