Is it possible to replicate through an http proxy?

[Iban Rodriguez]

Hi all,

for a project we are working on we have the following requirements:
* PostgreSQL server on cloud
* Lot of edge devices that need access to DB
* Edges will only execute SELECT queries. No modification allowed on them.
* Internet access on edges can be lost and data must be accessible while
connection is reestablished.

Our first approach to solve this was to setup a postgreSQL server on each
edge and configure a logical replication from cloud server. This way our
clients always connect to local server who automatically syncs with cloud
server while Internet connection is up and also automatically reconnects
when Internet connection is recovered after connection lost.

However, we facing a new requirement that we do not know how to solve. This
requirement is that access to Internet from edge devices must be done
through a http proxy server. Reading documentation we have not been able to
find a way to create a replication with this limitation. The questions we
would like to ask are:
* Is it possible to create a logical replication with this limitation?
* Is there an alternative solution to our problem bearing in mind this
limitation?

Thank you very much!

Re: unexpected behavior with pglogical -- bug?

[Iban Rodriguez]

Exactly same situation here. If I LISTEN for notifications, no notification
is sent by subscriber when changes are received from replication. However,
any local change makes all pending notifications be sent in addition to the
corresponding to the local change.

I have tested it on Postgres 12.1

El jue., 27 feb. 2020 a las 15:29, Achilleas Mantzios (<
ach...@matrix.gatewaynet.com>) escribió:

> Hello
>
> have you tried the same with logical replication in postgresql >= 10?
>
> On 16/6/19 11:45 μ.μ., Torsten Förtsch wrote:
>
> Hi,
>
> out of curiosity I created the following setup, all with 9.6 and pglogical.
>
> D1 is configured as provider with a replication set that contains only 1
> table. Only inserts are replicated.
>
> D2 is configured as subscriber for that replication set. Replication
> works, all inserts on D2 arrive also on D2.
>
> Now, I add the following always firing trigger to the table:
>
> CREATE OR REPLACE FUNCTION notify.trgfn () RETURNS trigger AS $def$
> BEGIN
> PERFORM pg_notify(NEW.channel, NEW.msg);
> RETURN NULL;
> END
> $def$ LANGUAGE plpgsql;
>
> CREATE TRIGGER trg BEFORE INSERT ON notify.notify
> FOR EACH ROW
> EXECUTE PROCEDURE notify.trgfn();
>
> ALTER TABLE notify.notify ENABLE ALWAYS TRIGGER trg;
>
> As you can see, the trigger function should prevent the actual insert and
> only call pg_notify(). In principle this works but there is a catch.
> Notifications generated this way are only delivered after another
> notification genuinely generated on the subscriber node. The channel of
> this notification does not matter. If I replace PERFORM pg_notify() by RAISE
> NOTICE I see the message immediately in the log.
>
> First I thought this is related to session_replication_role=replica. So,
> I tried the direct insert on D2 with this setting using psql. The
> notification was fired immediately. Also, whether the trigger prevents or
> allows the actual insert does not matter. I tried to create the trigger
> function as SECURITY DEFINER and with a specific search_path. That didn't
> help either.
>
> By now I am thinking there must be something missing in pglogical.
>
> Thanks,
> Torsten
>
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>