<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <title>Postfix PostgreSQL Howto</title> <meta http-equiv="Content-Type" content="text/html; charset=us-ascii"> </head> <body> <h1><img src="postfix-logo.jpg" width="203" height="98" ALT="">Postfix PostgreSQL Howto</h1> <hr> <h2>Introduction</h2> <p> The Postfix pgsql map type allows you to hook up Postfix to a PostgreSQL database. This implementation allows for multiple pgsql databases: you can use one for a virtual(5) table, one for an access(5) table, and one for an aliases(5) table if you want. You can specify multiple servers for the same database, so that Postfix can switch to a good database server if one goes bad. </p> <p> Busy mail servers using pgsql maps will generate lots of concurrent pgsql clients, so the pgsql server(s) should be run with this fact in mind. You can reduce the number of concurrent pgsql clients by using the Postfix proxymap(8) service. </p> <h2>Building Postfix with PostgreSQL support</h2> <p> Note: to use pgsql with Debian GNU/Linux's Postfix, all you need to do is to install the postfix-pgsql package and you're done. There is no need to recompile Postfix. </p> <p> In order to build Postfix with pgsql map support, you specify -DHAS_PGSQL, the directory with the PostgreSQL header files, and the location of the libpq library file. </p> <p> For example: </p> <blockquote> <pre> % make tidy % make -f Makefile.init makefiles \ 'CCARGS=-DHAS_PGSQL -I/usr/local/include/pgsql' \ 'AUXLIBS=-L/usr/local/lib -lpq' </pre> </blockquote> <p> Then just run 'make'. </p> <h2>Configuring PostgreSQL lookup tables</h2> <p> Once Postfix is built with pgsql support, you can specify a map type in main.cf like this: </p> <blockquote> <pre> /etc/postfix/main.cf: alias_maps = pgsql:/etc/postfix/pgsql-aliases.cf </pre> </blockquote> <p> The file /etc/postfix/pgsql-aliases.cf specifies lots of information telling postfix how to reference the pgsql database. For a complete description, see the pgsql_table(5) manual page. </p> <h2>Example: local aliases </h2> <pre> # # pgsql config file for local(8) aliases(5) lookups # # # The hosts that Postfix will try to connect to hosts = host1.some.domain host2.some.domain # The user name and password to log into the pgsql server. user = someone password = some_password # The database name on the servers. dbname = customer_database # The table name. table = mxaliases # Query components, see below. select_field = forw_addr where_field = alias # You may specify additional_conditions or leave this empty. additional_conditions = and status = 'paid' # The above variables will result in a query of the form: # # select forw_addr from mxaliases where alias = '$lookup' and status = 'paid' # # ($lookup is escaped so if it contains single quotes or other odd # characters, it will not cause problems). # # You may also override the built-in SELECT template. See pgsql_table(5) # for details. </pre> <h2>Using mirrored databases</h2> <p> Sites that have a need for multiple mail exchangers may enjoy the convenience of using a networked mailer database, but do not want to introduce a single point of failure to their system. </p> <p> For this reason we've included the ability to have Postfix reference multiple hosts for access to a single pgsql map. This will work if sites set up mirrored pgsql databases on two or more hosts. </p> <p> Whenever queries fail with an error at one host, the rest of the hosts will be tried in random order. If no pgsql server hosts are reachable, then mail will be deferred until at least one of those hosts is reachable. </p> <h2>Credits</h2> <ul> <li> This code is based upon the Postfix mysql map by Scott Cotton and Joshua Marcus, IC Group, Inc. <li> The PostgreSQL changes were done by Aaron Sethman. <li> Updates for Postfix 1.1.x and PostgreSQL 7.1+ and support for calling stored procedures were added by Philip Warner. <li> LaMont Jones was the initial Postfix pgsql maintainer. <li> Liviu Daia revised the configuration interface and added the main.cf configuration feature. </ul> </body> </html>