multiple nodes in FDW create server statement
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
Vijaykumar Jain wrote:
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.htmlunless 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.
The documentation of postgres_fdw says:
A foreign server using the postgres_fdw foreign data wrapper can have the same
options that libpq accepts in connection strings, as described in Section 34.1.2,
except that these options are not allowed:
user and password (specify these in a user mapping, instead)
client_encoding (this is automatically set from the local server encoding)
fallback_application_name (always set to postgres_fdw)
So there is nothing that keeps you from using multiple host names or ports,
and you can also use "target_session_attrs".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
awesomeness.
Thanks Laurenz.
Regards,
Vijay
On Wed, Jul 3, 2019 at 12:48 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
Show quoted text
Vijaykumar Jain wrote:
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.The documentation of postgres_fdw says:
A foreign server using the postgres_fdw foreign data wrapper can have
the same
options that libpq accepts in connection strings, as described in
Section 34.1.2,
except that these options are not allowed:user and password (specify these in a user mapping, instead)
client_encoding (this is automatically set from the local server
encoding)fallback_application_name (always set to postgres_fdw)
So there is nothing that keeps you from using multiple host names or ports,
and you can also use "target_session_attrs".Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com