Message-ID: <022649eb4363dc73b2b7cc2e08946e13019f238f.camel@cybertec.at>
Subject: Re: [EXTERNAL] Re: FDW, connections, master v replica
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Godfrin, Philippe E" <philippe.godfrin@nov.com>,
	pgsql-admin@lists.postgresql.org
Date: Wed, 19 May 2021 18:38:50 +0200
In-Reply-To: 
 <SA0PR15MB3933508FF29BA19E494501F2822B9@SA0PR15MB3933.namprd15.prod.outlook.com>
References: 
	<SA0PR15MB3933C775A0B3D498741C22B2822C9@SA0PR15MB3933.namprd15.prod.outlook.com>
	 <49ed5b6a7a44f245676db39d8c6c2b1f55035eac.camel@cybertec.at>
	 <SA0PR15MB393394768B6A3F3B6EF17CD5822C9@SA0PR15MB3933.namprd15.prod.outlook.com>
	 <e710b9cf36ab776d74ff83a1baf21a656bdd2797.camel@cybertec.at>
	 <SA0PR15MB3933FB683C38C76297BFFD02822B9@SA0PR15MB3933.namprd15.prod.outlook.com>
	 <SA0PR15MB3933508FF29BA19E494501F2822B9@SA0PR15MB3933.namprd15.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/022649eb4363dc73b2b7cc2e08946e13019f238f.camel%40cybertec.at>
Precedence: bulk

On Wed, 2021-05-19 at 15:45 +0000, Godfrin, Philippe E wrote:
> From: Laurenz Albe <laurenz.albe@cybertec.at> 
> >On Tue, 2021-05-18 at 16:13 +0000, Godfrin, Philippe E wrote:
> > > 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’
> >
> > Pretty much, yes. Except you can only specify a single setting for
> > "target_session_attrs".
>  
> OK – so using target_session_attr=’read-write’ will use the show transaction_read_only
> command – if the reply is ‘on’ the connection is closed, which will then go to the next
> host/port combination…
> Seems to me, then, it’s the libpq operation that permits the selection…

Yes, the postgres_fdw options are mostly libpq connection parameters.

> Hmm, wait a minute. Then how do you segregate connections by RO ?? I can’t wait until v14

Right.  If you cannot wait for v14, that is hard luck.

You could use a load balancer, perhaps that can be configured to check
"pg_is_in_recovery()".

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