Thread: [GENERAL] Schema/table replication

> On Sep 6, 2017, at 6:00 AM, Marcin Giedz <marcin.giedz@arise.pl> wrote:
>
> Hi, is there any way (3rd party software) to replicate particular schema/table not the whole database with streaming
replicationbuilt-in mechanism ? 

I don't believe so. You can do that with logical replication in v10 -
https://www.postgresql.org/docs/10/static/logical-replication.html.

pglogical will give you much the same functionality on current releases.
https://www.2ndquadrant.com/en/resources/pglogical/- installation isn't too painful (though the docs are a little
sparsewhen it comes to which node you should run which command on. Make the postgres.conf changes on master and slave
nodes,as slave nodes need replication slots too(?)). 

There are a bunch of trigger-based replication frameworks that'll work too, though less efficiently - Slony is widely
used,and I used Bucardo successfully for years before moving to pglogical. 

Cheers,
  Steve

> On Sep 6, 2017, at 8:48 AM, Marcin Giedz <marcin.giedz@arise.pl> wrote:
>
> Does pglogical support views replication as I can't find it in any restrictions ?

There's no need to replicate the contents of a view, as it doesn't contain any data.

pglogical can replicate the initial schema, including any views, but won't replicate DDL changes automatically after
that.It does provide a clean way to replicate DDL from the master to slaves with pglogical.replicate_ddl_command(). 

Cheers,
  Steve

> > On Sep 6, 2017, at 6:00 AM, Marcin Giedz <marcin.giedz@arise.pl> wrote:
> >
> > Hi, is there any way (3rd party software) to replicate particular schema/table not the whole database with
streamingreplication built-in mechanism ? 
>
> I don't believe so. You can do that with logical replication in v10 -
https://www.postgresql.org/docs/10/static/logical-replication.html.
>
> pglogical will give you much the same functionality on current releases.
https://www.2ndquadrant.com/en/resources/pglogical/- installation isn't too painful (though the docs are a little
sparsewhen it comes to which node you should run which command on. Make the postgres.conf changes on master and slave
nodes,as slave nodes need replication slots too(?)). 
>
> There are a bunch of trigger-based replication frameworks that'll work too, though less efficiently - Slony is widely
used,and I used Bucardo successfully for years before moving to pglogical. 
>
> Cheers,
>   Steve
>
>

On Wed, Sep 06, 2017 at 08:22:14AM -0700, Steve Atkins wrote:
> > On Sep 6, 2017, at 6:00 AM, Marcin Giedz <marcin.giedz@arise.pl> wrote:
> >
> > Hi, is there any way (3rd party software) to replicate particular schema/table not the whole database with
streamingreplication built-in mechanism ? 
>
> I don't believe so. You can do that with logical replication in v10 -
> https://www.postgresql.org/docs/10/static/logical-replication.html.

Well, session_replication_role exists so that you can implement your own
logical replication, and with that you can have full control over what
to replicate and what not replicate.  There is no builtin functionality
that does this.  Third-party software?  Try this search and similar in
other engines:

https://github.com/search?utf8=%E2%9C%93&q=session_replication_role+extension%3Asql&type=Code&ref=advsearch&l=&l=

> pglogical will give you much the same functionality on current
> releases. https://www.2ndquadrant.com/en/resources/pglogical/ -
> installation isn't too painful (though the docs are a little sparse
> when it comes to which node you should run which command on. Make the
> postgres.conf changes on master and slave nodes, as slave nodes need
> replication slots too(?)).
>
> There are a bunch of trigger-based replication frameworks that'll work
> too, though less efficiently - Slony is widely used, and I used
> Bucardo successfully for years before moving to pglogical.

Yeah.