Thread: FDW wrapper for clustered app management
Hello,
I am considering (postgres 9.3+) the idea of opening a R/W access into a clustered application by creating one fdw server from a central database to each server a cluster.
That would imply opening a port on each server inside the application, listening for incoming connections from the database and this way all the servers would become visible with R/W access.
Is that a sound idea or does it look horrible ? Would it be reasonable to connect in this way to a cluster of 1, 10, 100, 1000 or more servers ?
is there an existing xxxx_fdw wrapper that would look like a good candidate for such a direct access inside an application ? Then I would have to implement the protocol corresponding to this xxxx_fdw inside my application.
Thank you for your feedback.
Jerome
On 10/17/2014 2:35 AM, Jerome Wagner wrote: > Hello, > > I am considering (postgres 9.3+) the idea of opening a R/W access into > a clustered application by creating one fdw server from a central > database to each server a cluster. > > That would imply opening a port on each server inside the application, > listening for incoming connections from the database and this way all > the servers would become visible with R/W access. > > Is that a sound idea or does it look horrible ? Would it be reasonable > to connect in this way to a cluster of 1, 10, 100, 1000 or more servers ? > > is there an existing xxxx_fdw wrapper that would look like a good > candidate for such a direct access inside an application ? Then I > would have to implement the protocol corresponding to this xxxx_fdw > inside my application. > is the application running on these 10, 100, 1000 nodes something resembling a table oriented relational database? I would suggest instead you look at using a MQ style message queueing system, with publish-subscribe semantics for your distributed remote procedure calls. and not from within a database, rather, from your central control application to your distributed application workers... -- john r pierce 37N 122W somewhere on the middle of the left coast
Jerome Wagner wrote: > I am considering (postgres 9.3+) the idea of opening a R/W access into a clustered application by > creating one fdw server from a central database to each server a cluster. > > That would imply opening a port on each server inside the application, listening for incoming > connections from the database and this way all the servers would become visible with R/W access. > > Is that a sound idea or does it look horrible ? Would it be reasonable to connect in this way to a > cluster of 1, 10, 100, 1000 or more servers ? > > is there an existing xxxx_fdw wrapper that would look like a good candidate for such a direct access > inside an application ? Then I would have to implement the protocol corresponding to this xxxx_fdw > inside my application. What is the application you want to access this way? Does "cluster" mean that data is distributed (sharded) across these servers? Can you reasonably translate SQL access predicates so that you can push down WHERE conditions to the remote servers? If not, every access would have to pull the entire foreign table into the PostgreSQL server before applying the conditions. Under the right conditions, such a setup could work, e.g. to join data from different data sources. How many servers make sense probably depends a lot on how you want to use them. Yours, Laurenz Albe
Hello John,
Thanks for your answer. I am also considering a publish/subscribe MQ based solution (and this may be indeed needed for queuing RPCs).
the data I would like to access R/W is more like
- configuration data
- states of different state machines
- cache values for some keys (reading, invalidating)
so you could in a sense say that they are table oriented.
I agree that this becomes 'tangential' with write access.
a request like "UPDATE server.service SET start_requested = true WHERE name = 'myService' and started = false" seems weird but it could probably work.
Thanks,
Jerome
On Fri, Oct 17, 2014 at 11:57 AM, John R Pierce <pierce@hogranch.com> wrote:
is the application running on these 10, 100, 1000 nodes something resembling a table oriented relational database?On 10/17/2014 2:35 AM, Jerome Wagner wrote:Hello,
I am considering (postgres 9.3+) the idea of opening a R/W access into a clustered application by creating one fdw server from a central database to each server a cluster.
That would imply opening a port on each server inside the application, listening for incoming connections from the database and this way all the servers would become visible with R/W access.
Is that a sound idea or does it look horrible ? Would it be reasonable to connect in this way to a cluster of 1, 10, 100, 1000 or more servers ?
is there an existing xxxx_fdw wrapper that would look like a good candidate for such a direct access inside an application ? Then I would have to implement the protocol corresponding to this xxxx_fdw inside my application.
I would suggest instead you look at using a MQ style message queueing system, with publish-subscribe semantics for your distributed remote procedure calls. and not from within a database, rather, from your central control application to your distributed application workers...
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello Albe,
Thanks for your answer.
The application is an application that is a sort of supervisor of sub applications, mainly web oriented. It manage access rights, update versions of web services, launch services, ..
No the data is not sharded across the servers, but each server can have its own set of data.
Yes I guess that the predicates (and WHERE clause) could be pushed down to the servers.
But I am not sure how fdw works regarding joins between a remote server and a local table. In this case it is hard for me to answer you.
For example I have yet to document myself on how fdw handles those 2 types of requests :
-- without join
SELECT s.name FROM server1.service AS s
WHERE s.uptime > 20000;
-- with join
SELECT s.name FROM server1.service AS s
WHERE s.uptime > u.uptime;
I also have not thought about the multi-server requests. A view on all the fdw servers, could probably enable SELECT requests communicating with all the servers (eg: what are all the servers that have a specific property).
For the write part, a stored procedure could probably be used ; I don't know if there is another option.
Clearly, I am trying to see how I could twist the fdw wrappers into a sort of manhole inside the application, without resorting to a classic event based mechanism.
I could also imagine very risky things (security wise) like :
SELECT line FROM server1.execute WHERE command = 'wc -l /my/file' ORDER by lineno;
and use pgadmin as general control mechanism for the cluster : the cluster and the data would be on the same SQL data plane :-)
Thanks,
Jerome
On Fri, Oct 17, 2014 at 12:00 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
What is the application you want to access this way?Jerome Wagner wrote:
> I am considering (postgres 9.3+) the idea of opening a R/W access into a clustered application by
> creating one fdw server from a central database to each server a cluster.
>
> That would imply opening a port on each server inside the application, listening for incoming
> connections from the database and this way all the servers would become visible with R/W access.
>
> Is that a sound idea or does it look horrible ? Would it be reasonable to connect in this way to a
> cluster of 1, 10, 100, 1000 or more servers ?
>
> is there an existing xxxx_fdw wrapper that would look like a good candidate for such a direct access
> inside an application ? Then I would have to implement the protocol corresponding to this xxxx_fdw
> inside my application.
Does "cluster" mean that data is distributed (sharded) across these servers?
Can you reasonably translate SQL access predicates so that you can push down
WHERE conditions to the remote servers? If not, every access would have to pull
the entire foreign table into the PostgreSQL server before applying the conditions.
Under the right conditions, such a setup could work, e.g. to join data from
different data sources. How many servers make sense probably depends a lot on how
you want to use them.
Yours,
Laurenz Albe
Jerome Wagner wrote: [wants to access an application server farm with FDW] > Clearly, I am trying to see how I could twist the fdw wrappers into a sort of manhole inside the > application, without resorting to a classic event based mechanism. That's too metaphorical for me to understand. > I could also imagine very risky things (security wise) like : > > SELECT line FROM server1.execute WHERE command = 'wc -l /my/file' ORDER by lineno; > > and use pgadmin as general control mechanism for the cluster : the cluster and the data would be on > the same SQL data plane :-) Such things are conceivable, but that would subvert the concept of a FDW, which is to translate SQL queries into a foreign API and convert the result to PostgreSQL data. I found that it is often a bad idea to use things in ways that are not intended (it can be good fun though). While you can certainly draw inspiration from existing FDW (where is a www_fdw for example), what you want to do would mean writing a PostgreSQL module in C (or Python, if you use Multicorn) for your application. Yours, Laurenz Albe