Thread: Avahi support for Postgresql

Avahi support for Postgresql

From
Mathias Hasselmann
Date:
Postmaster already has code to announce its services via DNS-SD
(ZeroConf) by using Apple's Bonjour API. This series of patches
implements that capability on top of the Avahi library[1] which
is free software, available for a wider variety of platforms.

I've separated the change set into smaller pieces for easy review. Also
I don't know, if the 4th patch adding subtypes describing Postgresql's
capabilities is wanted by the Postgresql community. Being a desktop guy,
using Postgresql as embedded database, I'd really like to see all of the
patches merged, but I could imagine some opposition against the verbose
nature of the subtypes patch.

Each of the patches as a short description of its purpose.

I couldn't find any ChangeLog files in the repository, and also I didn't
invest much time in investigating how the HISTORY file of distribution
tarballs is assembled. So if the repository contains file I should have
modified for documenting my changes, I apologize for not updating them.

[1] http://www.avahi.org/
--
Mathias Hasselmann <mathias@openismus.com>
www.openismus.com

Re: Avahi support for Postgresql

From
Bruce Momjian
Date:
This has been saved for the 8.4 release:

    http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Mathias Hasselmann wrote:
> Postmaster already has code to announce its services via DNS-SD
> (ZeroConf) by using Apple's Bonjour API. This series of patches
> implements that capability on top of the Avahi library[1] which
> is free software, available for a wider variety of platforms.
>
> I've separated the change set into smaller pieces for easy review. Also
> I don't know, if the 4th patch adding subtypes describing Postgresql's
> capabilities is wanted by the Postgresql community. Being a desktop guy,
> using Postgresql as embedded database, I'd really like to see all of the
> patches merged, but I could imagine some opposition against the verbose
> nature of the subtypes patch.
>
> Each of the patches as a short description of its purpose.
>
> I couldn't find any ChangeLog files in the repository, and also I didn't
> invest much time in investigating how the HISTORY file of distribution
> tarballs is assembled. So if the repository contains file I should have
> modified for documenting my changes, I apologize for not updating them.
>
> [1] http://www.avahi.org/
> --
> Mathias Hasselmann <mathias@openismus.com>
> www.openismus.com

[ application/mbox is not supported, skipping... ]

[ application/mbox is not supported, skipping... ]

[ application/mbox is not supported, skipping... ]

[ application/mbox is not supported, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Avahi support for Postgresql

From
Mathias Hasselmann
Date:
Hello,

Congratulations to the 8.3 release. No, that pre-release stress is over
I'd really like to put attention on my Avahi patches again.

Avahi/Bonjour/DNS-SD support[1] is very important, for integrating
Postgresql with modern desktop environments like OSX, GNOME, KDE: It's
very convenient to choose active DBMS servers in your local network from
a list, instead of memorizing "cryptic" connection parameters.

In Glom[2] we use Postgresql as DBMS of choice. This application would
greatly benefit from the possibility to choose central Postgresql
servers from an automatically updated list.

For OSX builds Postgresql already has DNS-SD support via Apple's Bonjour
library. For Linux Avahi[3] is the library of choice. Modern distribu-
tions ship it as main package, since both GNOME and KDE make use of it.
Since DNS-SD is a published  IETF standard [4] client applications
choose whatever DNS-SD implementation they like.

Applying this patche series would bring Postgresql's Linux port on par
with the OSX port in terms of desktop integration.

People not wanting DNS-SD support for their server can easily control
that feature via the "--with-avahi" configure scripts.

Thank your for attention,
Mathias

[1] http://www.dns-sd.org/
[2] http://www.glom.org/
[3] http://www.avahi.org/
[4] http://www.ietf.org/rfc/rfc2782.txt

Am Dienstag, den 27.11.2007, 11:19 +0100 schrieb Mathias Hasselmann:
> Postmaster already has code to announce its services via DNS-SD
> (ZeroConf) by using Apple's Bonjour API. This series of patches
> implements that capability on top of the Avahi library[1] which
> is free software, available for a wider variety of platforms.
>
> I've separated the change set into smaller pieces for easy review. Also
> I don't know, if the 4th patch adding subtypes describing Postgresql's
> capabilities is wanted by the Postgresql community. Being a desktop guy,
> using Postgresql as embedded database, I'd really like to see all of the
> patches merged, but I could imagine some opposition against the verbose
> nature of the subtypes patch.
>
> Each of the patches as a short description of its purpose.
>
> I couldn't find any ChangeLog files in the repository, and also I didn't
> invest much time in investigating how the HISTORY file of distribution
> tarballs is assembled. So if the repository contains file I should have
> modified for documenting my changes, I apologize for not updating them.
>
> [1] http://www.avahi.org/
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
--
Mathias Hasselmann <mathias@openismus.com>
http://www.openismus.com/ - We can get it done.


Re: Avahi support for Postgresql

From
Peter Eisentraut
Date:
Mathias Hasselmann wrote:
> Congratulations to the 8.3 release. No, that pre-release stress is over
> I'd really like to put attention on my Avahi patches again.

I've added your item to the patch list
(http://developer.postgresql.org/index.php/Todo:PatchStatus).  It would help
if you could include or point to your actual patch.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: Avahi support for Postgresql

From
Mathias Hasselmann
Date:
Am Samstag, den 23.02.2008, 21:08 +0100 schrieb Peter Eisentraut:
> Mathias Hasselmann wrote:
> > Congratulations to the 8.3 release. Now, that pre-release stress is over
> > I'd really like to put attention on my Avahi patches again.
>
> I've added your item to the patch list
> (http://developer.postgresql.org/index.php/Todo:PatchStatus).  It would help
> if you could include or point to your actual patch.

Thanks a lot.

The patches were in my initial mail, but now I've also uploaded them to
my personal site for convenience:

    http://taschenorakel.de/files/pgsql-avahi-support/

I just failed to update the Wiki page:

    This page has been locked to prevent editing.
    You can view and copy the source of this page:

Ciao,
Mathias
--
Mathias Hasselmann <mathias@openismus.com>
http://www.openismus.com/ - We can get it done.


Re: Avahi support for Postgresql

From
Peter Eisentraut
Date:
Am Dienstag, 27. November 2007 schrieb Mathias Hasselmann:
> I don't know, if the 4th patch adding subtypes describing Postgresql's
> capabilities is wanted by the Postgresql community. Being a desktop guy,
> using Postgresql as embedded database, I'd really like to see all of the
> patches merged, but I could imagine some opposition against the verbose
> nature of the subtypes patch.

I am not convinced about the usefulness of the subtypes patch.  The examples
you cite -- FTP, bittorrent, distcc -- are cases where a client could pick
the most appropriate server from a set of equivalent instances.  I don't see
how this can be useful in a PostgreSQL setting, at least not based on the
parameters server version and protocol version that you implemented.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: [HACKERS] Avahi support for Postgresql

From
Alvaro Herrera
Date:
Mathias Hasselmann wrote:

> The patches were in my initial mail, but now I've also uploaded them to
> my personal site for convenience:
>
>     http://taschenorakel.de/files/pgsql-avahi-support/

Hmm, a quick look at the third patch reveals that it is using the
"threaded" Avahi client.  That's a showstopper.  Please consider using
some other approach -- writing our own handlers for AvahiPoll would seem
apropos.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: Avahi support for Postgresql

From
Peter Eisentraut
Date:
Am Dienstag, 27. November 2007 schrieb Mathias Hasselmann:
> Postmaster already has code to announce its services via DNS-SD
> (ZeroConf) by using Apple's Bonjour API. This series of patches
> implements that capability on top of the Avahi library[1] which
> is free software, available for a wider variety of platforms.

One thing I found curious:  The Bonjour code uses service
type "_postgresql._tcp." whereas your code uses "_postgresql._tcp" (note the
dot).  Is that an expected difference?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: Avahi support for Postgresql

From
Peter Eisentraut
Date:
Am Samstag, 23. Februar 2008 schrieb Mathias Hasselmann:
> The patches were in my initial mail, but now I've also uploaded them to
> my personal site for convenience:
>
>     http://taschenorakel.de/files/pgsql-avahi-support/

I have reworked the first three of your patches (mostly formatting and some
refactoring) and published them here:

http://git.postgresql.org/?p=~petere/postgresql.git;a=shortlog;h=avahi-support

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: Avahi support for Postgresql

From
Mathias Hasselmann
Date:
Am Montag, den 25.02.2008, 15:45 +0100 schrieb Peter Eisentraut:
> Am Dienstag, 27. November 2007 schrieb Mathias Hasselmann:
> > Postmaster already has code to announce its services via DNS-SD
> > (ZeroConf) by using Apple's Bonjour API. This series of patches
> > implements that capability on top of the Avahi library[1] which
> > is free software, available for a wider variety of platforms.
>
> One thing I found curious:  The Bonjour code uses service
> type "_postgresql._tcp." whereas your code uses "_postgresql._tcp" (note the
> dot).  Is that an expected difference?

Oh, that difference is really interesting. I didn't even see it.

DNS-SD uses the convention "_<protoname>._<type>" to describe services,
and that's the convention Avahi follows. I don't know why the Bonjour
API uses that trailing dot, but it seems wrong:

    "a trailing dot indicates the root domain. so bonjour is clearly
     wrong, since there's no ._tcp top-level domain."

        -- James Andrewartha on #avahi today

Avahi's maintainer Sjoerd Simons agreed, that the trailing dot seems to
be wrong.


Ciao,
Mathias
--
Mathias Hasselmann <mathias@openismus.com>
http://www.openismus.com/ - We can get it done.


Re: Avahi support for Postgresql

From
Mathias Hasselmann
Date:
Am Montag, den 25.02.2008, 15:27 +0100 schrieb Peter Eisentraut:
> Am Dienstag, 27. November 2007 schrieb Mathias Hasselmann:
> > I don't know, if the 4th patch adding subtypes describing Postgresql's
> > capabilities is wanted by the Postgresql community. Being a desktop guy,
> > using Postgresql as embedded database, I'd really like to see all of the
> > patches merged, but I could imagine some opposition against the verbose
> > nature of the subtypes patch.
>
> I am not convinced about the usefulness of the subtypes patch.  The examples
> you cite -- FTP, bittorrent, distcc -- are cases where a client could pick
> the most appropriate server from a set of equivalent instances.  I don't see
> how this can be useful in a PostgreSQL setting, at least not based on the
> parameters server version and protocol version that you implemented.

Well, I could imagine certain cases, where applications only want to
list Postgresql servers newer than release x.y - since they depend on
certain new features of that release.

But you could be right, that the approach I've implemented only allows
filtering for "revision == x.y", but not the intended "revision >= x.y".

So maybe the release number, or a feature list (similar the capability
list of SMTP) should be put into the announcement's TXT record.

Well, but I do not consider that sub-type feature that important.

Ciao,
Mathias
--
Mathias Hasselmann <mathias@openismus.com>
http://www.openismus.com/ - We can get it done.


Re: Avahi support for Postgresql

From
Tom Lane
Date:
Mathias Hasselmann <mathias@openismus.com> writes:
> Oh, that difference is really interesting. I didn't even see it.

> DNS-SD uses the convention "_<protoname>._<type>" to describe services,
> and that's the convention Avahi follows. I don't know why the Bonjour
> API uses that trailing dot, but it seems wrong:

Is removing that dot going to create compatibility problems for us?
If it means that an 8.3 client wouldn't find an 8.4 server, for
instance, I don't think that's going to be acceptable.  Do we need
to "listen" on both names (whatever the heck that means in zeroconf)?

            regards, tom lane

Re: Avahi support for Postgresql

From
Mathias Hasselmann
Date:
Am Montag, den 25.02.2008, 19:12 +0100 schrieb Peter Eisentraut:
> Am Samstag, 23. Februar 2008 schrieb Mathias Hasselmann:
> > The patches were in my initial mail, but now I've also uploaded them to
> > my personal site for convenience:
> >
> >     http://taschenorakel.de/files/pgsql-avahi-support/
>
> I have reworked the first three of your patches (mostly formatting and some
> refactoring) and published them here:
>
> http://git.postgresql.org/?p=~petere/postgresql.git;a=shortlog;h=avahi-support

Thanks alot.

Didn't know about that git repository, btw.

Is git://git.postgresql.org/git/postgresql.git assumed to reliably
mirror the CVS repository?

Ciao,
Mathias
--
Mathias Hasselmann <mathias@openismus.com>
http://www.openismus.com/ - We can get it done.


Re: [HACKERS] Avahi support for Postgresql

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, Feb 23, 2008 at 01:13:38PM +0100, Mathias Hasselmann wrote:

[...]

> Avahi/Bonjour/DNS-SD support[1] is very important, for integrating
> Postgresql with modern desktop environments like OSX, GNOME, KDE: It's
> very convenient to choose active DBMS servers in your local network from
> a list, instead of memorizing "cryptic" connection parameters.

[...]

> People not wanting DNS-SD support for their server can easily control
> that feature via the "--with-avahi" configure scripts.

Sorry for a dumb question, but I couldn't figure that out from your
references [1]..[4]: does that mean that the PostgreSQL server would
"advertise itself" on the local net? Or what is the purpose of liking-in
libavahi into the postmaster?

Surely one wouldn't want this in a data center? Is there a possiblity to
disable that at run time?

Thanks for any insights

- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFH7jUxBcgs9XrR2kYRAsfOAJ0Ulfydo3G1kzQo3HTOdjtswA1A2gCfYYyL
VHprJ63unId85/Iht4ha2RE=
=get0
-----END PGP SIGNATURE-----

Re: [HACKERS] Avahi support for Postgresql

From
Mathias Hasselmann
Date:
Am Samstag, den 29.03.2008, 12:25 +0000 schrieb tomas@tuxteam.de:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Sat, Feb 23, 2008 at 01:13:38PM +0100, Mathias Hasselmann wrote:
>
> [...]
>
> > Avahi/Bonjour/DNS-SD support[1] is very important, for integrating
> > Postgresql with modern desktop environments like OSX, GNOME, KDE: It's
> > very convenient to choose active DBMS servers in your local network from
> > a list, instead of memorizing "cryptic" connection parameters.
>
> [...]
>
> > People not wanting DNS-SD support for their server can easily control
> > that feature via the "--with-avahi" configure scripts.
>
> Sorry for a dumb question, but I couldn't figure that out from your
> references [1]..[4]: does that mean that the PostgreSQL server would
> "advertise itself" on the local net? Or what is the purpose of liking-in
> libavahi into the postmaster?

Yes, that's the purpose.

> Surely one wouldn't want this in a data center?

Yes, this feature definitely targets small-office use, personal use, DB
developers. Don't know enough about data centers to judge the impact
there, but since Avahi - as used in the patch - announces to the local
network only, the impact sould be small.

Still you can tell Avahi to explicitly announce at a certain, non-local
domain, but this feature is not implemented by the patch. Maybe database
developers in large network environments could make use of such
announcements. It would be trivial to add.

> Is there a possiblity to disable that at run time?

The feature is disabled by default. As long as you do not specify a
zeroconf_name in your configuration file, nothing happens. This is the
same behavior as established by the Bonjour code.

Ciao,
Mathias
--
Mathias Hasselmann <mathias@openismus.com>
http://www.openismus.com/ - We can get it done.


Re: [HACKERS] Avahi support for Postgresql

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, Apr 01, 2008 at 09:35:56AM +0200, Mathias Hasselmann wrote:
> Am Samstag, den 29.03.2008, 12:25 +0000 schrieb tomas@tuxteam.de:
> [...]
> > Sorry for a dumb question, but I couldn't figure that out from your
> > references [1]..[4]: does that mean that the PostgreSQL server would
> > "advertise itself" on the local net? Or what is the purpose of liking-in
> > libavahi into the postmaster?
>
> Yes, that's the purpose.
>
> > Surely one wouldn't want this in a data center?
>
> Yes, this feature definitely targets small-office use, personal use, DB
> developers [...]
> Still you can tell Avahi to explicitly announce at a certain, non-local
> domain, but this feature is not implemented by the patch. Maybe database
> developers in large network environments could make use of such
> announcements. It would be trivial to add.

Personally, I'be rather scared than delighted ;-)

> > Is there a possiblity to disable that at run time?
>
> The feature is disabled by default. As long as you do not specify a
> zeroconf_name in your configuration file, nothing happens. This is the
> same behavior as established by the Bonjour code.

Thanks, good to know.

Isn't there a less-intrusive option to linking a lib into each and every
possible server, like a config file in which to put what is to be announced?

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFH8iRFBcgs9XrR2kYRAmJ0AJkB7MkxfYI0nVa4RqHVEV1HYjz41gCdEgWz
YQ2T4Y/xfoLRF4D6hMLbpEk=
=Goho
-----END PGP SIGNATURE-----

Re: [HACKERS] Avahi support for Postgresql

From
Mathias Hasselmann
Date:
Am Dienstag, den 01.04.2008, 12:02 +0000 schrieb tomas@tuxteam.de:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Tue, Apr 01, 2008 at 09:35:56AM +0200, Mathias Hasselmann wrote:
> > Am Samstag, den 29.03.2008, 12:25 +0000 schrieb tomas@tuxteam.de:
> > [...]
> > > Sorry for a dumb question, but I couldn't figure that out from your
> > > references [1]..[4]: does that mean that the PostgreSQL server would
> > > "advertise itself" on the local net? Or what is the purpose of liking-in
> > > libavahi into the postmaster?
> >
> > Yes, that's the purpose.
> >
> > > Surely one wouldn't want this in a data center?
> >
> > Yes, this feature definitely targets small-office use, personal use, DB
> > developers [...]
> > Still you can tell Avahi to explicitly announce at a certain, non-local
> > domain, but this feature is not implemented by the patch. Maybe database
> > developers in large network environments could make use of such
> > announcements. It would be trivial to add.
>
> Personally, I'be rather scared than delighted ;-)

So in data centers you don't even trust the machines in your broadcast
domain?

> > > Is there a possiblity to disable that at run time?
> >
> > The feature is disabled by default. As long as you do not specify a
> > zeroconf_name in your configuration file, nothing happens. This is the
> > same behavior as established by the Bonjour code.
>
> Thanks, good to know.
>
> Isn't there a less-intrusive option to linking a lib into each and every
> possible server, like a config file in which to put what is to be announced?

You could directly talk to the D-Bus interface of Avahi. libavahi-client
just is a convenience wrapper. Well, but this route will be much more
cumbersome.

One other route is calling avahi-publish-service on startup and killing
it on shutdown, but: avahi-publish-service really only exists for
demonstration purposes and doesn't handle service name collisions for
instance. I don't believe that a high-profile application like
Postgresql should rely on low-quality hacks, like invoking educational
demo programs.

Ciao,
Mathias
--
Mathias Hasselmann <mathias@openismus.com>
http://www.openismus.com/ - We can get it done.


Re: [HACKERS] Avahi support for Postgresql

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, Apr 01, 2008 at 05:07:31PM +0200, Mathias Hasselmann wrote:
[...]
> > Personally, I'be rather scared than delighted ;-)
>
> So in data centers you don't even trust the machines in your broadcast
> domain?

Kind of. Put it another way: never have services running you don't use.

[...]

> > Isn't there a less-intrusive option to linking a lib into each and every
> > possible server, like a config file in which to put what is to be announced?
>
> You could directly talk to the D-Bus interface of Avahi. libavahi-client
> just is a convenience wrapper. Well, but this route will be much more
> cumbersome.

So this goes through the D-Bus. Makes kind of sense. Thanks for the
enlightenment.

> One other route is calling avahi-publish-service on startup and killing
> it on shutdown, but: avahi-publish-service really only exists for
> demonstration purposes and doesn't handle service name collisions for
> instance. I don't believe that a high-profile application like
> Postgresql should rely on low-quality hacks, like invoking educational
> demo programs.

Unelegant as it might seem -- this solution still affords a lot more
when it comes to "separation of concerns". I'm still a bit wary at the
prospect that each and every daemon evolves into a huge fuzzball
linked to all conceivable service-lets with a multitude of funny
side-effects (remember tcpwrappers?).

Of course, "you can always disable this at compile time", but let's face
it: with the predominance of binary distribs, the path of least
resistance will be to put up with whatever strange side-effects.

I would really prefer a more loosely coupled system.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFH8lYJBcgs9XrR2kYRAmDJAJ4jWKYkhUfKEAIaZVnIbAAEqJF2AwCfS/6D
4rH9OoY7wjia7h1cuk5CjZI=
=AF1W
-----END PGP SIGNATURE-----

Re: [HACKERS] Avahi support for Postgresql

From
Murray Cumming
Date:
On Tue, 2008-04-01 at 15:34 +0000, tomas@tuxteam.de wrote:
> I would really prefer a more loosely coupled system.

The functionality will be much the same. The implementation would be
more difficult and obscure and there would be more points of failure and
more things to configure, but it wouldn't remove much risk, I think.

Anyway, this feature is already in Postgres when it's built for MacOS X.
So this decision seems to have been made already, at least for that
platform.

--
murrayc@murrayc.com
www.murrayc.com
www.openismus.com