Thread: Publication/Subscription Questions

> On Jul 27, 2018, at 01:34, xOChilpili <xochilpili@gmail.com> wrote:
>
> Why ? If i remove rows, from Server B and refresh publication, why data is not re-sync ?

ALTER SUBSCRIPTION ... REFRESH PUBLICATION doesn't do another initial copy of the data for existing tables in the
publication. Its function is to add tables that were added to the publication after the subscription was last created
orrefreshed.  It does (by default) copy the data from newly-added tables, but it does not resync the data from the
existingtables. 

--
-- Christophe Pettus
   xof@thebuild.com

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