Re: Resend: Question about PostgreSQL, pgpool, and

Of course, RTFM. :) Thanks for the pointer. I'll pursue that path and
see how it goes.

Thanks,
Greg

On Sat, 2005-11-26 at 17:41 +0000, John Sidney-Woollett wrote:

> Take a loot at postfix's proxymap feature - I believe it works for MySQL
> and Postgres, and may solve your problem.
>
> http://www.postfix.org/proxymap.8.html
>
> From the docs
>
> "To consolidate the number of open lookup tables by
> sharing one open table among multiple processes.
> For example, making mysql connections from every
> Postfix daemon process results in "too many connec-
> tions" errors. The solution:
>
> virtual_alias_maps =
> proxy:mysql:/etc/postfix/virtual_alias.cf
>
> The total number of connections is limited by the
> number of proxymap server processes."
>
>
> John
>
> Gregory Youngblood wrote:
> > [I don't know if this message made it out before or not. If it did,
> > please accept my apologies for the duplicate message. Thanks.]
> >
> > I'm running postfix 2.0.18 with a postgresql 8.0.3 database backend. I'm
> > also using courier imap/pop servers connected to postgresql as well. All
> > email users are stored in tables, with views providing lookup
> > information to courier and postfix. It works very well, with one
> > exception.
> >
> > Postfix likes to hang on to idle connections to the database, even if
> > there are not that many postfix processes running. For example, with
> > postfix:
> >
> > 18338 ? Ss 0:00 /usr/lib/postfix/master
> > 18339 ? S 0:00 \_ pickup -l -t fifo -u
> > 18340 ? S 0:00 \_ qmgr -l -t fifo -u
> > 18344 ? S 0:00 \_ trivial-rewrite -n rewrite -t unix -u
> > 18358 ? S 0:00 \_ smtpd -n smtp -t inet -u -s 2
> > 18360 ? S 0:00 \_ smtpd -n smtp -t inet -u -s 2
> > 18361 ? S 0:00 \_ smtpd -n smtp -t inet -u -s 2
> > 18362 ? S 0:00 \_ smtpd -n smtp -t inet -u -s 2
> > 18363 ? S 0:00 \_ cleanup -z -t unix -u
> > 18370 ? S 0:00 \_ smtpd -n smtp -t inet -u -s 2
> > 18371 ? S 0:00 \_ smtpd -n smtp -t inet -u -s 2
> > 18372 ? S 0:00 \_ smtpd -n smtp -t inet -u -s 2
> > 18373 ? S 0:00 \_ smtpd -n smtp -t inet -u -s 2
> > 18386 ? S 0:00 \_ cleanup -z -t unix -u
> > 18390 ? S 0:00 \_ cleanup -z -t unix -u
> > 18397 ? S 0:00 \_ cleanup -z -t unix -u
> > 18401 ? S 0:00 \_ cleanup -z -t unix -u
> > 18402 ? S 0:00 \_ cleanup -z -t unix -u
> > 18403 ? S 0:00 \_ cleanup -z -t unix -u
> > 18427 ? S 0:00 \_ cleanup -z -t unix -u
> > 18440 ? S 0:00 \_ smtpd -n smtp -t inet -u -s 2
> > 18441 ? S 0:00 \_ smtpd -n smtp -t inet -u -s 2
> > 18557 ? S 0:00 \_ cleanup -z -t unix -u
> > 18558 ? S 0:00 \_ virtual -t unix
> >
> > This is what postgresql looks like:
> > 17610 pts/1 S 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data
> > 17612 pts/1 S 0:00 \_ postgres: writer process
> > 17613 pts/1 S 0:00 \_ postgres: stats buffer process
> > 17614 pts/1 S 0:00 | \_ postgres: stats collector process
> > 17916 pts/1 S 0:00 \_ postgres: courier netio 127.0.0.1(3037)
> > idle
> > 18345 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27587) idle
> > 18346 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27588)
> > idle
> > 18347 pts/1 S 0:00 \_ postgres: domains netio 127.0.0.1(27589)
> > idle
> > 18364 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27590) idle
> > 18365 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27591)
> > idle
> > 18366 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27592)
> > idle
> > 18367 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27593) idle
> > 18377 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27596) idle
> > 18378 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27597)
> > idle
> > 18379 pts/1 S 0:00 \_ postgres: mailbox netio 127.0.0.1(27598)
> > idle
> > 18387 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27601) idle
> > 18388 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27602)
> > idle
> > 18389 pts/1 S 0:00 \_ postgres: mailbox netio 127.0.0.1(27603)
> > idle
> > 18394 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27604) idle
> > 18395 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27605)
> > idle
> > 18396 pts/1 S 0:00 \_ postgres: mailbox netio 127.0.0.1(27606)
> > idle
> > 18398 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27607) idle
> > 18399 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27608)
> > idle
> > 18400 pts/1 S 0:00 \_ postgres: mailbox netio 127.0.0.1(27609)
> > idle
> > 18404 pts/1 S 0:00 \_ postgres: mailbox netio 127.0.0.1(27610)
> > idle
> > 18408 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27611) idle
> > 18409 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27612)
> > idle
> > 18410 pts/1 S 0:00 \_ postgres: mailbox netio 127.0.0.1(27613)
> > idle
> > 18411 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27614) idle
> > 18412 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27615)
> > idle
> > 18413 pts/1 S 0:00 \_ postgres: mailbox netio 127.0.0.1(27616)
> > idle
> > 18420 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27618) idle
> > 18422 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27619)
> > idle
> > 18423 pts/1 S 0:00 \_ postgres: mailbox netio 127.0.0.1(27620)
> > idle
> > 18428 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27622)
> > idle
> > 18429 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27623) idle
> > 18436 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27624)
> > idle
> > 18437 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27625) idle
> > 18438 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27626)
> > idle
> > 18439 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27627) idle
> > 18442 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27628) idle
> > 18443 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27629)
> > idle
> > 18444 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27630) idle
> > 18445 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27631)
> > idle
> > 18446 pts/1 S 0:00 \_ postgres: mailbox netio 127.0.0.1(27632)
> > idle
> > 18447 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(27633)
> > idle
> > 18448 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(27634) idle
> > 18452 pts/1 S 0:00 \_ postgres: mailbox netio 127.0.0.1(27636)
> > idle
> > 18555 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(2386)
> > idle
> > 18556 pts/1 S 0:00 \_ postgres: transport netio 127.0.0.1(2387)
> > idle
> > 18559 pts/1 S 0:00 \_ postgres: mailbox netio 127.0.0.1(2388)
> > idle
> > 18609 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(2393)
> > idle
> > 18610 pts/1 S 0:00 \_ postgres: transport netio 127.0.0.1(2394)
> > idle
> > 18654 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(2398)
> > idle
> > 18655 pts/1 S 0:00 \_ postgres: transport netio 127.0.0.1(2399)
> > idle
> > 18940 pts/1 S 0:00 \_ postgres: virtual netio 127.0.0.1(20578)
> > idle
> > 18941 pts/1 S 0:00 \_ postgres: transport netio
> > 127.0.0.1(20579) idle
> >
> >
> > I've been wondering about pgpool.
> >
> > Does pgpool have enough intelligence to reuse idle connections? My
> > understanding is that it is able to reuse existing connections with a
> > database after the client disconnects from pgpool.
> >
> > I'm in the process of updating my server, and I'm hoping to migrate to
> > newer versions of postfix and postgresql. I'm wondering if pgpool will
> > gain me anything. I don't think it will - since it appears postfix is
> > hanging on and keeping each connection open, and not disconnecting. Is
> > anyone else using postfix with postgresql in this manner? If so, are you
> > having this problem?
> >
> > Thanks,
> > Greg
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq