Re: Resend: Question about PostgreSQL, pgpool, and - Mailing list pgsql-general

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