multiple nodes in FDW create server statement

From: Vijaykumar Jain <vjain(at)opentable(dot)com>
To: "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org>
Subject: multiple nodes in FDW create server statement
Date: 2019-07-03 05:27:43
Message-ID: CAE7uO5i=shi1NkXmwxweEx9uNVF06=ZxBZ+zVBgUqMnwRm4S-Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

All,

We are glad that we have this feature that allows us to load balance reads.
that has helped us a lot.
https://paquier.xyz/postgresql-2/postgres-10-multi-host-connstr/

I would like to know if it is possible to request a similar enhancement to
FDWs too?
https://www.postgresql.org/docs/11/sql-createserver.html

unless i am missing something obvious, we wanted to use this option when
one of the read instances are down when there is a FDW query for reads.
the second instance is only a fallback, not round robin. and we have 2
foreign servers, one for write and one for reads.

although they are marked by cnames, so its just a matter of cname switch,
but that takes some time for propagation and requires someone to be near
the machine.

i guess the concerns would be if primary is down, the writes would be
directed to another set of servers which may end up in confusing errors.

pgtesting=> create table foo(id int);

ERROR: cannot execute CREATE TABLE in a read-only transaction

pgtesting=> \q

Let me know if this is not clear.

Regards,
Vijay

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ayub M 2019-07-03 07:01:24 Partitioning an existing table - pg10.6
Previous Message Dirk Mika 2019-07-03 05:04:33 Re: plpgsql: How to modify a field in an array of records