Thread: [ADMIN] Replicate only 1 out of 2 databases of Server A to Server B ?

Hi all.

PostgreSQL 9.2.18
Running on replication mode Master-Slave (async).
Server A (2 databases) is fully replicated to Server B.
That works OK.

Question:
Is it possible to replicate only 1 out of 2 databases of Server A to Server B?

Best regards,
czezz

Re: [ADMIN] Replicate only 1 out of 2 databases of Server A to ServerB ?

From
John Scalia
Date:
Yes, that is possible through PostgreSQL's logical replication system. It sounds like, however, you're using streaming replication and that cannot replicate just one database from the master cluster. So, you'll have to change your mechanism. Suggest you look at configuring logical replication.
--
Jay

On Mon, Apr 24, 2017 at 9:33 AM, czezz <czezz@o2.pl> wrote:
Hi all.

PostgreSQL 9.2.18
Running on replication mode Master-Slave (async).
Server A (2 databases) is fully replicated to Server B.
That works OK.

Question:
Is it possible to replicate only 1 out of 2 databases of Server A to Server B?

Best regards,
czezz

Re: [ADMIN] Replicate only 1 out of 2 databases of Server A to ServerB ?

From
"David G. Johnston"
Date:
On Monday, April 24, 2017, czezz <czezz@o2.pl> wrote:
Hi all.

PostgreSQL 9.2.18
Running on replication mode Master-Slave (async).
Server A (2 databases) is fully replicated to Server B.
That works OK.

Question:
Is it possible to replicate only 1 out of 2 databases of Server A to Server B?


Not without third-party software.  Or separating the databases into their own clusters.   You have more logical decoding options since 9.4 though I'm not personally familiar with their use.

David J.

Re: [ADMIN] Replicate only 1 out of 2 databases of Server A to ServerB ?

From
Scott Mead
Date:


On Mon, Apr 24, 2017 at 9:33 AM, czezz <czezz@o2.pl> wrote:
Hi all.

PostgreSQL 9.2.18
Running on replication mode Master-Slave (async).
Server A (2 databases) is fully replicated to Server B.
That works OK.

Question:
Is it possible to replicate only 1 out of 2 databases of Server A to Server B?

With 9.2, I think your only real solution is to use logical replication: bucardo, slony, londiste.  These tools require a more involved setup and configuration than streaming replication (they're not built-in).


For 9.4+ you can use pg_logical.  It's 'kind of' built in, you will still need a separate download and separate configuration flow.

  All of the above allow you to select to the object (table, sequence) level.  Just be careful, unlike streaming, there are a lot of caveats.  For example, new tables are not auto-replicated, you cannot ALTER TABLE.

--Scott




Best regards,
czezz



--
--
Scott Mead
Sr. Architect
OpenSCG