Can we do streaming and logical replication in postgresql Slave at same time?

PostgreSQL
1

We have PostgresSQL cluster with 1 master and 2 slave configuration , we want to enable logical replication from Slave , as the master a pretty work heavy database , we don’t want to put more load on DB , Is it possible if we can start both type of replication from any slave currently replicating through streaming replication

2

Hi,

Bad news/Good news

Bad News: Unfortunately, postgres doesn’t currently support logical replication from a REPLICA/SLAVE.

Good News: Set up another read-write server and pull in the data you need from the REPLICA using the postgres foreign data wrapper.

Abbreviated example snippet, execute on pg3 (it’s not really complete, just shows the essentials):

begin;
create extension postgres_fdw;
create server pg_remote foreign data wrapper postgres_fdw options (host 'pg2', dbname 'db01', port '5432');
create user mapping for CURRENT_ROLE server pg_remote options (user 'postgres', password 'postgres');
create foreign table public.t1(id integer) server pg_remote;
commit;

Of course you’ll need to add supplementary SQL in order to keep your tables uptodate i.e. perhaps an UPSERT.

Hope this helps.