One way replication in PostgreSQL

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

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

You could use FDW to replicate what you need to an external server from
the provider/primary/master to the subscriber/secondary/slaveUsing
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,

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

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.

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

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