pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFDW, connections, master v replica
9+ messages / 3 participants
[nested] [flat]
* FDW, connections, master v replica
@ 2021-05-18 13:57 Godfrin, Philippe E <philippe.godfrin@nov.com>
0 siblings, 2 replies; 9+ messages in thread
From: Godfrin, Philippe E @ 2021-05-18 13:57 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Greetings,
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?
Thanks,
pg
Phil Godfrin | Database Administrator
NOV
NOV US | Engineering Data
9720 Beechnut St | Houston, Texas 77036
M 281.825.2311
E Philippe.Godfrin@nov.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: FDW, connections, master v replica
@ 2021-05-18 14:27 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Godfrin, Philippe E <philippe.godfrin@nov.com>
1 sibling, 0 replies; 9+ messages in thread
From: Vijaykumar Jain @ 2021-05-18 14:27 UTC (permalink / raw)
To: Godfrin, Philippe E <philippe.godfrin@nov.com>; +Cc: pgsql-admin@lists.postgresql.org
I guess we do not have any mechanism or proxy that parses query to redirect
it to master or replica.
I was playing with crunchy proxy and read that if the application can
provide *read hints*, it would redirect those queries to read replicas,
else all go to primary.
https://github.com/CrunchyData/crunchy-proxy
On Tue, May 18, 2021, 7:28 PM Godfrin, Philippe E <philippe.godfrin@nov.com>
wrote:
> Greetings,
>
> 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?
>
> Thanks,
>
> pg
>
>
>
> Phil Godfrin | *Database Administrator*
>
> *NOV*
>
> NOV US | Engineering Data
>
> 9720 Beechnut St | Houston, Texas 77036
>
> M 281.825.2311
>
> E Philippe.Godfrin@nov.com
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: FDW, connections, master v replica
@ 2021-05-18 14:44 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Godfrin, Philippe E <philippe.godfrin@nov.com>
1 sibling, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2021-05-18 14:44 UTC (permalink / raw)
To: Godfrin, Philippe E <philippe.godfrin@nov.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: [EXTERNAL] Re: FDW, connections, master v replica
@ 2021-05-18 16:13 Godfrin, Philippe E <philippe.godfrin@nov.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 9+ messages in thread
From: Godfrin, Philippe E @ 2021-05-18 16:13 UTC (permalink / raw)
To: laurenz.albe@cybertec.at; pgsql-admin@lists.postgresql.org
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.albe@cybertec.at>
Sent: Tuesday, May 18, 2021 9:45 AM
To: Godfrin, Philippe E <Philippe.Godfrin@nov.com>; pgsql-admin@lists.postgresql.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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [EXTERNAL] Re: FDW, connections, master v replica
@ 2021-05-18 16:27 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Godfrin, Philippe E <philippe.godfrin@nov.com>
0 siblings, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2021-05-18 16:27 UTC (permalink / raw)
To: Godfrin, Philippe E <philippe.godfrin@nov.com>; pgsql-admin@lists.postgresql.org
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".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: [EXTERNAL] Re: FDW, connections, master v replica
@ 2021-05-19 14:51 Godfrin, Philippe E <philippe.godfrin@nov.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 9+ messages in thread
From: Godfrin, Philippe E @ 2021-05-19 14:51 UTC (permalink / raw)
To: laurenz.albe@cybertec.at; pgsql-admin@lists.postgresql.org
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…
Nice… Thanks very much.
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Tuesday, May 18, 2021 11:28 AM
To: Godfrin, Philippe E <Philippe.Godfrin@nov.com>; pgsql-admin@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: FDW, connections, master v replica
Use caution when interacting with this [EXTERNAL] email!
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".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: [EXTERNAL] Re: FDW, connections, master v replica
@ 2021-05-19 15:45 Godfrin, Philippe E <philippe.godfrin@nov.com>
parent: Godfrin, Philippe E <philippe.godfrin@nov.com>
0 siblings, 1 reply; 9+ messages in thread
From: Godfrin, Philippe E @ 2021-05-19 15:45 UTC (permalink / raw)
To: laurenz.albe@cybertec.at; pgsql-admin@lists.postgresql.org
Hmm, wait a minute. Then how do you segregate connections by RO ?? I can’t wait until v14
Any will permit RO and RW,
From: Godfrin, Philippe E
Sent: Wednesday, May 19, 2021 9:52 AM
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org
Subject: RE: [EXTERNAL] Re: FDW, connections, master v replica
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…
Nice… Thanks very much.
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Tuesday, May 18, 2021 11:28 AM
To: Godfrin, Philippe E <Philippe.Godfrin@nov.com>; pgsql-admin@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: FDW, connections, master v replica
Use caution when interacting with this [EXTERNAL] email!
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".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [EXTERNAL] Re: FDW, connections, master v replica
@ 2021-05-19 16:38 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Godfrin, Philippe E <philippe.godfrin@nov.com>
0 siblings, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2021-05-19 16:38 UTC (permalink / raw)
To: Godfrin, Philippe E <philippe.godfrin@nov.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: [EXTERNAL] Re: FDW, connections, master v replica
@ 2021-05-19 17:52 Godfrin, Philippe E <philippe.godfrin@nov.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 9+ messages in thread
From: Godfrin, Philippe E @ 2021-05-19 17:52 UTC (permalink / raw)
To: laurenz.albe@cybertec.at; pgsql-admin@lists.postgresql.org
OK, thanks!
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Wednesday, May 19, 2021 11:39 AM
To: Godfrin, Philippe E <Philippe.Godfrin@nov.com>; pgsql-admin@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: FDW, connections, master v replica
Use caution when interacting with this [EXTERNAL] email!
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2021-05-19 17:52 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-18 13:57 FDW, connections, master v replica Godfrin, Philippe E <philippe.godfrin@nov.com>
2021-05-18 14:27 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2021-05-18 14:44 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-05-18 16:13 ` Godfrin, Philippe E <philippe.godfrin@nov.com>
2021-05-18 16:27 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-05-19 14:51 ` Godfrin, Philippe E <philippe.godfrin@nov.com>
2021-05-19 15:45 ` Godfrin, Philippe E <philippe.godfrin@nov.com>
2021-05-19 16:38 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-05-19 17:52 ` Godfrin, Philippe E <philippe.godfrin@nov.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox