RE: [EXTERNAL] Re: FDW, connections, master v replica

Hi Laurenz,

That seems very promising, though I’m not certain how I’d code that in the create foreign server syntax. My testbed is not quire ready right now, so I’ll ask instead of testing.

Is this what you mean:

CREATE SERVER FDW001 FOREIGN DATA WRAPPER POSTGES_FDW

OPTIONS (HOST ‘FOORW, FOORO’, DBNAME ‘FOODB,FOODB’, PORT ‘5432,6432’,

   TARGET_SESSION_ATTRS ‘ANY,READ-ONLY’

;

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Sent: Tuesday, May 18, 2021 9:45 AM
To: Godfrin, Philippe E <Philippe(dot)Godfrin(at)nov(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: [EXTERNAL] Re: FDW, connections, master v replica

Use caution when interacting with this [EXTERNAL] email!

On Tue, 2021-05-18 at 13:57 +0000, Godfrin, Philippe E wrote:
> Does anyone know of a method to define a foreign server to point to a patroni
> cluster of 2 postgres cluster instances and be able to select between the master for RW and the replica for RO?

Use the "target_session_attrs" connection parameter.

It can be used to always connect to the primary, and from v14 on
it can also be used to preferably connect to the standby.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com