Thread: One way replication in PostgreSQL

One way replication in PostgreSQL

From

PALAYRET Jacques

Date:

03 June 2019, 19:00:51

Hello,

If, for security reasons, I can't create a connection or a flow from subscriber/secundary/slave towards provider/primary/master, witch replication systems can I use ?

If possible, I would prefer partial replication (only some tables) to full base replication (all instances).

Do trigger-based replication systems (like Slony or Londiste or others) need a connection or flow from subscriber to the provider ?

Thanks in advance

----- Météo-France -----
PALAYRET JACQUES
DCSC/MBD
jacques.palayret@meteo.fr
Fixe : +33 561078319

Re: One way replication in PostgreSQL

From

Stephen Frost

Date:

03 June 2019, 19:13:39

Greetings,

* PALAYRET Jacques (jacques.palayret@meteo.fr) wrote:
> If, for security reasons, I can't create a connection or a flow from subscriber/secundary/slave towards
provider/primary/master,witch replication systems can I use ?  

The simplest approach might be to use WAL shipping with one of the
various backup tools that do that- eg: you could use pgbackrest in the
archive_command of the primary system and push WAL to a repo that's on
the replica (or some other system that the replica is allowed to connect
to).

> If possible, I would prefer partial replication (only some tables) to full base replication (all instances).

Just to be clear, you couldn't use a WAL-based shipping method for
partial replication (at least, not today anyway).

Thanks,

Stephen

Attachment

signature.asc

Re: One way replication in PostgreSQL

From

Fabio Pardi

Date:

03 June 2019, 19:18:21

Hi,

On 6/3/19 6:00 PM, PALAYRET Jacques wrote:
> Hello,
> 
> If, for security reasons, I can't create a connection or a flow from
> subscriber/secundary/slave towards provider/primary/master, witch
> replication systems can I use ?
> 

To perform replication, you need some form of connectivity between the
hosts (unless, you want to only apply archived WAL files, as mentioned
by Stephen here above).
In streaming replication your replica needs to be able to initiate the
connection to master

If you instead have, let's say, master -> replica or both your hosts can
reach a 3rd host, you might work around the problem using an SSH tunnel.

I discourage you from this latter option, anyway.

> If possible, I would prefer partial replication (only some tables) to
> full base replication (all instances).

you could have a look into pglogical

regards,

fabio pardi

Re: One way replication in PostgreSQL

From

Frank Alberto Rodriguez

Date:

04 June 2019, 02:39:54

You could use FDW to replicate what you need to an external server from the provider/primary/master to the subscriber/secondary/slave

Using triggers on the master tables that you want to replicate, you can execute the insert/update/delete actions on the secondary tables through the FDW.

With this approach you only need a connection from provider to the subscriber.

Regards

On Mon, 2019-06-03 at 18:00 +0200, PALAYRET Jacques wrote:

Hello,

If, for security reasons, I can't create a connection or a flow from subscriber/secundary/slave towards provider/primary/master, witch replication systems can I use ?

If possible, I would prefer partial replication (only some tables) to full base replication (all instances).

Do trigger-based replication systems (like Slony or Londiste or others) need a connection or flow from subscriber to the provider ?

Thanks in advance
----- Météo-France -----
PALAYRET JACQUES
DCSC/MBD
jacques.palayret@meteo.fr
Fixe : +33 561078319

Re: One way replication in PostgreSQL

From

PALAYRET Jacques

Date:

04 June 2019, 10:02:45

Hello,

Thanks a lot for the suggested solutions.

So, I can use WAL-shipping replication from Primary to the Secundary server, but it's only for full replication.

Let's call " P " the provider/primary/master and " S " the subscriber/secundary/slave one.
For partial replication (not all the tables), the solutions should use a third (intermediate / middle) server which could have both ways flow with the server P but only one way flow towards the server S.
For example, a logical replication (pglogical or logical Postgresql replication) between server P and the middle server and then a WAL-shipping replication between middle server and server S.
Is that right ?

About the " FDW " solution in " an external server " (a middle one), is it possible to integrate the FDW in the P server to avoid the " external server " ?

=> What about the trigger-based replication systems like Slony or Londiste ; is it really necessary to have a connection or flow from the server S towards the server P ?

Regards

De: "PALAYRET Jacques" <jacques.palayret@meteo.fr>
À: pgsql-general@lists.postgresql.org
Envoyé: Lundi 3 Juin 2019 18:00:51
Objet: One way replication in PostgreSQL

Thanks in advance

----- Météo-France -----
PALAYRET JACQUES
DCSC/MBD
jacques.palayret@meteo.fr
Fixe : +33 561078319

Re: One way replication in PostgreSQL

From

Guillaume Lelarge

Date:

04 June 2019, 10:33:43

Le mar. 4 juin 2019 à 09:03, PALAYRET Jacques <jacques.palayret@meteo.fr> a écrit :

Hello,

Thanks a lot for the suggested solutions.

So, I can use WAL-shipping replication from Primary to the Secundary server, but it's only for full replication.

Yes.

Let's call " P " the provider/primary/master and " S " the subscriber/secundary/slave one.
For partial replication (not all the tables), the solutions should use a third (intermediate / middle) server which could have both ways flow with the server P but only one way flow towards the server S.
For example, a logical replication (pglogical or logical Postgresql replication) between server P and the middle server and then a WAL-shipping replication between middle server and server S.
Is that right ?

That could work.

About the " FDW " solution in " an external server " (a middle one), is it possible to integrate the FDW in the P server to avoid the " external server " ?

Not sure I understand where you're going here.

=> What about the trigger-based replication systems like Slony or Londiste ; is it really necessary to have a connection or flow from the server S towards the server P ?

As far as I remember, with Slony, P must be able to connect to S, and S must be able to connect to P.

Guillaume.

Dalibo.

Re: One way replication in PostgreSQL

From

Achilleas Mantzios

Date:

04 June 2019, 11:04:09

On 4/6/19 10:02 π.μ., PALAYRET Jacques wrote:

Hello,

Thanks a lot for the suggested solutions.

So, I can use WAL-shipping replication from Primary to the Secundary server, but it's only for full replication.

Let's call " P " the provider/primary/master and " S " the subscriber/secundary/slave one.
For partial replication (not all the tables), the solutions should use a third (intermediate / middle) server which could have both ways flow with the server P but only one way flow towards the server S.
For example, a logical replication (pglogical or logical Postgresql replication) between server P and the middle server and then a WAL-shipping replication between middle server and server S.
Is that right ?

About the " FDW " solution in " an external server " (a middle one), is it possible to integrate the FDW in the P server to avoid the " external server " ?

=> What about the trigger-based replication systems like Slony or Londiste ; is it really necessary to have a connection or flow from the server S towards the server P ?

I'll describe our solution here, but its a far fetched approach, a diving into uncharted waters situation, but for us it was the only solution back in early 2000.
In one word : The long forgotten DBMirror (Mentioned during "Postgresql 11 : what's old" comments that followed the presentation in Lisbon pgconf2018.eu) . You can setup your triggers, produce your SQL files, sent them to S whatever method you wish and run them without any connectivity from S to P.
Setting up is very easy. However you must be prepared to carry this as "technical debt" in future upgrades.
No Slony expert here, but in https://severalnines.com/blog/experts-guide-slony-replication-postgresql it is mentioned that you can ship in the form of SQL files as well. So you setup a slony slave inside your P system and produce SQL files which you replay to your remote S, with no connectivity to P.

Regards
De: "PALAYRET Jacques" <jacques.palayret@meteo.fr>
À: pgsql-general@lists.postgresql.org
Envoyé: Lundi 3 Juin 2019 18:00:51
Objet: One way replication in PostgreSQL

Hello,

If, for security reasons, I can't create a connection or a flow from subscriber/secundary/slave towards provider/primary/master, witch replication systems can I use ?

If possible, I would prefer partial replication (only some tables) to full base replication (all instances).

Do trigger-based replication systems (like Slony or Londiste or others) need a connection or flow from subscriber to the provider ?

Thanks in advance
----- Météo-France -----
PALAYRET JACQUES
DCSC/MBD
jacques.palayret@meteo.fr
Fixe : +33 561078319

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: One way replication in PostgreSQL

From

Frank Alberto Rodriguez

Date:

04 June 2019, 19:29:16

The FDW is a PostgreSQL extension to connect to other server from PosgreSQL server inside, with this solution you only need connections from P to S and no need a third server (external server), just use triggers to push the INSERT/UPDATE/DELETE information you want to replicate from P to S through Foreign Data Wrapper.

If you need integrity on the replicated information then I suggest to use a control table to store the actions to replicate for the case when it fails you can keep trying til the action succeeds.

Regards

On Tue, 2019-06-04 at 09:02 +0200, PALAYRET Jacques wrote:

Hello,

Thanks a lot for the suggested solutions.

So, I can use WAL-shipping replication from Primary to the Secundary server, but it's only for full replication.

Let's call " P " the provider/primary/master and " S " the subscriber/secundary/slave one.
For partial replication (not all the tables), the solutions should use a third (intermediate / middle) server which could have both ways flow with the server P but only one way flow towards the server S.
For example, a logical replication (pglogical or logical Postgresql replication) between server P and the middle server and then a WAL-shipping replication between middle server and server S.
Is that right ?

About the " FDW " solution in " an external server " (a middle one), is it possible to integrate the FDW in the P server to avoid the " external server " ?

=> What about the trigger-based replication systems like Slony or Londiste ; is it really necessary to have a connection or flow from the server S towards the server P ?

Regards
De: "PALAYRET Jacques" <jacques.palayret@meteo.fr>
À: pgsql-general@lists.postgresql.org
Envoyé: Lundi 3 Juin 2019 18:00:51
Objet: One way replication in PostgreSQL

Hello,

If, for security reasons, I can't create a connection or a flow from subscriber/secundary/slave towards provider/primary/master, witch replication systems can I use ?

If possible, I would prefer partial replication (only some tables) to full base replication (all instances).

Do trigger-based replication systems (like Slony or Londiste or others) need a connection or flow from subscriber to the provider ?

Thanks in advance
----- Météo-France -----
PALAYRET JACQUES
DCSC/MBD
jacques.palayret@meteo.fr
Fixe : +33 561078319