Instalar tablefunc extension en PostgreSQL 9.6 para utilizar crosstab

El módulo "tablefunc" incluye varias funciones que devuelven tablas, es decir, obtendremos varias filas. En el siguiente enlace os dejo disponible la documentación para su correcto uso.

https://www.postgresql.org/docs/9.6/static/tablefunc.html

Tengo una entrada en este blog sobre como instalar PostgreSQL en un linux Debian partiendo del código fuente (tengo pendiente hacer una actualización de esta entrada) y en lugar de utilizar el repositorio de paquetes del sistema.

En esta entrada veremos como partiendo de esa instalación compilando el código fuente de PostgreSQL, incorporaremos la extensión tablefunc. Si buscas un poco encontrarás que para hacer uso de las funciones contenidas en tablefunc será necesario ejecutar como usuario administrador la sentencia "create extension tablefunc". Y posiblemente si has llegado hasta aquí, obtuviste como resultado algo parecido a "ERROR:  could not access file "$libdir/tablefunc": No existe el fichero o el directorio".

echo 'create extension tablefunc' | psql -U usuario base_de_datos
ERROR:  could not access file "$libdir/tablefunc": No existe el fichero o el directorio

Si es así está es tu solución. ¿Recuerdas cuando compilaste PostgreSql? Pues mira en aquel directorio donde encontrarás dentro de contrib al directorio contrib/tablefunc. Dirígete a ese directorio y ejecuta "make" y "make install".

root@myhost:~/descargas/postgresql-9.6.3/contrib/tablefunc# make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o tablefunc.o tablefunc.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/postgresql-9.6.3/lib',--enable-new-dtags -lm  -shared -o tablefunc.so tablefunc.o

root@myhost:~/descargas/postgresql-9.6.3/contrib/tablefunc# make install
/bin/mkdir -p '/opt/postgresql-9.6.3/share/extension'
/bin/mkdir -p '/opt/postgresql-9.6.3/share/extension'
/bin/mkdir -p '/opt/postgresql-9.6.3/lib'
/usr/bin/install -c -m 644 ./tablefunc.control '/opt/postgresql-9.6.3/share/extension/'
/usr/bin/install -c -m 644 ./tablefunc--1.0.sql ./tablefunc--unpackaged--1.0.sql  '/opt/postgresql-9.6.3/share/extension/'
/usr/bin/install -c -m 755  tablefunc.so '/opt/postgresql-9.6.3/lib/'

Ahora sí al ejecutar "CREATE EXTENSION tablefunc;" se instalará sin problemas y ya tendrás disponibles las funciones incluidas en tablefunc.

Ejemplo de utilización de crosstab

Una de las funciones más utilizadas de tablefunc es crosstab. Aquí dejo un simple ejemplo de ventas donde pivotaremos los costes de cada uno de los meses a columnas, de forma que nos quedará un registro (fila) por ejercicio.

Crearemos unos datos de prueba para trabajar

CREATE TABLE sales(year int, month int, qty int);
INSERT INTO sales VALUES(2017, 1, 1000);
INSERT INTO sales VALUES(2017, 2, 1500);
INSERT INTO sales VALUES(2017, 7, 500);
INSERT INTO sales VALUES(2017, 11, 1500);
INSERT INTO sales VALUES(2017, 12, 2000);
INSERT INTO sales VALUES(2018, 1, 1000);
INSERT INTO sales VALUES(2019, 5, 2500);
INSERT INTO sales VALUES(2019, 9, 800);

SELECT * FROM sales;
 year | month | qty 
------+-------+------
 2017 |     1 | 1000
 2017 |     2 | 1500
 2017 |     7 |  500
 2017 |    11 | 1500
 2017 |    12 | 2000
 2018 |     1 | 1000
 2019 |     5 | 2500
 2019 |     9 |  800
(8 rows)

Uso de crosstab

SELECT * FROM crosstab(
  $$ SELECT year, month, qty FROM sales ORDER BY 1 $$,
  $$ SELECT m FROM generate_series(1,12) m $$
) AS (
  year int, "Jan" int, "Feb" int, "Mar" int, "Apr" int, "May" int, "Jun" int, "Jul" int, "Aug" int, "Sep" int, "Oct" int, "Nov" int, "Dec" int
);

 year | Jan  | Feb  | Mar | Apr | May  | Jun | Jul | Aug | Sep | Oct | Nov  | Dec 
------+------+------+-----+-----+------+-----+-----+-----+-----+-----+------+------
 2017 | 1000 | 1500 |     |     |      |     | 500 |     |     |     | 1500 | 2000
 2018 | 1000 |      |     |     |      |     |     |     |     |     |      |   
 2019 |          |      |     |     | 2500 |     |     |     | 800 |     |      |   
(3 rows)

dbmail con PostgreSQL Debian (Wheezy)

Este manual sigue en la linea del escrito hace ya algunos años sobre dbmail y postgresql. Tras 5 años de escribir el primer manual de dbmail bajo postgresql, toca actulizarse. Así pues describo como instalar dbmail 3.0.2 y postfix 2.9.6-2, utilizando como base de datos postgresql 9.2.4 y todo corriendo sobre Debian 7.

Os dejo por aquí el enlace al manual anterior, por si alguien necesita tocar algún servidor antiguo y no sabe por donde comenzar. Encontrará algunas pautas de administración.

http://josemanuelruizbaena.blogspot.com.es/2008/09/dbmail-con-postgresql-debian-etch.html

Para el funcionamiento de dbmail es imprescindible tener instalado un sistema gestor de base de datos. He encontrado en internet muchos manuales que enseñan como montar dbmail bajo MySQL. Pero después de mi experiencia y tras probar las bondades de PostgreSQL, sin duda este manual de dbmail será para montarlo utilizando como motor de base de datos PostgreSQL.

Puesto que la instalación de PostgreSQL puede no ser trivial para algunos usuarios os dejo por aquí un manual para instalar PostgreSQL en Debian 7 desde código fuente. Si ya es robusto cuando se instala desde los repositorios de Debian, imaginad cuanto no lo será compilando tu código fuente directamente.

http://josemanuelruizbaena.blogspot.com/2013/06/compilar-postgresql-en-debian.html

Requisitos de dbmail:

Hay algunos requisitos que necesitas tener en el sistema para poder instalar dbmail desde la compilación de su código fuente. Si has seguido el manual de instalación de postgresql desde su código fuente o alguna vez has compilado no necesitarás in

aptitude install build-essential
aptitude install gcc libglib2.0-dev libmhash-dev libevent-dev libssl-dev flex automake

Es necesario también instalar libzdb y libgmime-2.4-dev, pero no están disponibles por ahora (27 de junio de 2013) en el repositorio de Debian 7. Así pues lo instalamos directamente desde el código fuente.

wget http://www.tildeslash.com/libzdb/dist/libzdb-2.10.2.tar.gz
tar xvzf libzdb-2.10.2.tar.gz
cd libzdb-2.10.2
 ./configure --with-postgresql=/opt/postgresql-9.2.4/bin/pg_config

checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for gawk... no
checking for mawk... mawk
checking whether make sets $(MAKE)... yes
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables...
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for style of include used by make... GNU
checking dependency style of gcc... none
checking how to run the C preprocessor... gcc -E
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking whether gcc needs -traditional... no
checking for re2c... re2c_not_found
checking for flex... /usr/bin/flex
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking how to print strings... printf
checking for a sed that does not truncate output... /bin/sed
checking for fgrep... /bin/grep -F
checking for ld used by gcc... /usr/bin/ld
checking if the linker (/usr/bin/ld) is GNU ld... yes
checking for BSD- or MS-compatible name lister (nm)... /usr/bin/nm -B
checking the name lister (/usr/bin/nm -B) interface... BSD nm
checking whether ln -s works... yes
checking the maximum length of command line arguments... 1572864
checking whether the shell understands some XSI constructs... yes
checking whether the shell understands "+="... yes
checking for /usr/bin/ld option to reload object files... -r
checking for objdump... objdump
checking how to recognize dependent libraries... pass_all
checking for ar... ar
checking for strip... strip
checking for ranlib... ranlib
checking command to parse /usr/bin/nm -B output from gcc object... ok
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking for dlfcn.h... yes
checking for objdir... .libs
checking if gcc supports -fno-rtti -fno-exceptions... no
checking for gcc option to produce PIC... -fPIC -DPIC
checking if gcc PIC flag -fPIC -DPIC works... yes
checking if gcc static flag -static works... yes
checking if gcc supports -c -o file.o... yes
checking if gcc supports -c -o file.o... (cached) yes
checking whether the gcc linker (/usr/bin/ld -m elf_x86_64) supports shared libraries... yes
checking whether -lc should be explicitly linked in... no
checking dynamic linker characteristics... GNU/Linux ld.so
checking how to hardcode library paths into programs... immediate
checking whether stripping libraries is possible... yes
checking if libtool supports shared libraries... yes
checking whether to build shared libraries... yes
checking whether to build static libraries... yes
checking for pthread_create in -lpthread... yes
checking for library containing crypt... -lcrypt
checking for mysql... yes
checking for mysql_config... no
configure: WARNING: mysql_config is required to build libzdb with mysql
checking for postgresql... yes
checking for /opt/postgresql-9.2.4/bin/pg_config... yes
checking libpq-fe.h usability... yes
checking libpq-fe.h presence... yes
checking for libpq-fe.h... yes
checking for SQLite3... yes
checking for sqlite3_open in -lsqlite3... no
checking for oracle... checking if Oracle support is enabled... no
checking for ANSI C header files... (cached) yes
checking for an ANSI C-conforming const... yes
checking whether byte ordering is bigendian... no
configure: creating ./config.status
config.status: creating Makefile
config.status: creating test/Makefile
config.status: creating zdb.pc
config.status: creating src/xconfig.h
config.status: executing libtool_patch commands
config.status: executing depfiles commands
config.status: executing libtool commands
+------------------------------------------------------------+
| License:                                                   |
| This is Open Source Software and use is subject to the GNU |
| GENERAL PUBLIC LICENSE, available in this distribution in  |
| the file COPYING.                                          |
|                                                            |
| By continuing this installation process, you are bound by  |
| the terms of this license agreement. If you do not agree   |
| with the terms of this license, you must abort the         |
| installation process at this point.                        |
+------------------------------------------------------------+
| Libzdb is configured as follows:                           |
|                                                            |
|   Optimized:                                    DISABLED   |
|   Protected:                                    DISABLED   |
|   Profiling:                                    DISABLED   |
|   Zild:                                         DISABLED   |
|   Sqlite3 unlock notification API:              DISABLED   |
|   Openssl:                                      DISABLED   |
|   Unit Tests Build:                             ENABLED    |
|------------------------------------------------------------|
|   SQLite3:                                      DISABLED   |
|   MySQL:                                        DISABLED   |
|   PostgreSQL:                                   ENABLED    |
|   Oracle:                                       DISABLED   |
+------------------------------------------------------------+


make
make install

Para instalar libgmime-2.4-dev lo descargamos de http://ftp.acc.umu.se/pub/GNOME/sources/gmime/2.4/.

wget http://ftp.acc.umu.se/pub/GNOME/sources/gmime/2.4/gmime-2.4.33.tar.xz
tar -Jxf gmime-2.4.33.tar.xz
cd gmime-2.4.33
./configure
make
make install

Instalar postfix

Ya hemos instalado todas las librerías y requisitos para dbmail, pero necesitamos un gestor SMTP que se comunique a la perfección con nuestro dbmail y orqueste todo el sistema.

Yo prefiero Postfix y por ahora instalaremos el del sistema, que va perfectamente. No sé si el día de mañana haré un nuevo manual con postfix compilado.

Cuando intentamos instalar Postfix nos advierte el sistema que ya existe un SMTP funcionando en el sistema, se trata de exim4. La solución que nos propone apt es la correcta, desinstalará exim4 (y los subpaquetes instalados) e instalará postfix.

aptitude install postfix
Se instalarán los siguiente paquetes NUEVOS:    
  postfix{b} ssl-cert{a}
0 paquetes actualizados, 2 nuevos instalados, 0 para eliminar y 0 sin actualizar.
Necesito descargar 1.566 kB de ficheros. Después de desempaquetar se usarán 3.380 kB.
No se satisfacen las dependencias de los siguientes paquetes:
 exim4-config : Entra en conflicto: postfix pero se va a instalar 2.9.6-2.
 postfix : Entra en conflicto: mail-transport-agent que es un paquete virtual.
 exim4-daemon-light : Entra en conflicto: mail-transport-agent que es un paquete virtual.
Las acciones siguientes resolverán estas dependencias

     Eliminar los paquetes siguientes:
1)     exim4                         
2)     exim4-base                    
3)     exim4-config                  
4)     exim4-daemon-light            

¿Acepta esta solución? [Y/n/q/?]

Al comenzar la instalación de Postfix veremos esta pantalla en el terminal, nos pregunta sobre el modo de funcionamiento de nuestra instancia de postfix. Seleccionamos "Sitio de Internet: El correo se envía y recibe directamente utilizando SMTP.".

También nos preguntará sobre el dominio (o subdominio, como es el caso de la captura) que queremos controlar con nuestro postfix.

Tras estos dos pasos terminará la instalación y configuración básica de postfix, más adelante continuaremos con configuración de postfix.

Crear base de datos (almacén de correos) 

Tras instalar todos los requisitos, creamos el usuarios dentro de PostgreSQL para que dbmail pueda almacenar los datos para la gestión del sistema de correos.

su postgres
echo "create user dbmail with password 'xxxx' " | /opt/postgresql-9.2.4/bin/psql template1
echo "create database dbmail owner dbmail encoding='UNICODE'" | /opt/postgresql-9.2.4/bin/psql template1

Instalar dbmail

Compilar dbmail

Ahora ya estamos preparados para compilar dbmail. Lo primero es descargar el paquete con el código fuente directamente de la web del proyecto http://www.dbmail.org/, en la sección download. Como comentamos al principio en este manual instalaremos la versión 3.0.2, es la última publicada (marzo de 2012) tiene más de un año, pero funciona perfectamente.

Descargamos, descomprimimos, configuramos, compilamos e instalamos con los siguientes comandos:

wget http://www.dbmail.org/download/3.0/dbmail-3.0.2.tar.gz
tar xvzf dbmail-3.0.2.tar.gz
cd dbmail-3.0.2
./configure --with-zdb=/usr/local
make
make install

Configurar dbmail

Tras instalar dbmail, lo primero que tenemos que hacer es crear la estructura de la base de datos con el fichero de inicialización. El fichero con la estructura de la base de datos se encuentra en el paquete que hemos descargado (dbmail-3.0.2.tar.gz) en "sql/postgresql/create_tables.pgsql".

Para cargar la estructura de la base de datos, necesitamos tener creada la base de datos. Esto lo hicimos en un paso anterior.

cat sql/postgresql/create_tables.pgsql | /opt/postgresql-9.2.4/bin/psql -U dbmail dbmail

Ahora procedemos a copiar el fichero de configuración en el directorio de configuración de nuestro sistema y configurar algunas parametrizaciones.

cp dbmail.conf /etc/

nano /etc/dbmail.conf

Parametrizamos la conexión a la base de datos.

driver               = postgresql
authdriver           = sql
host                 = localhost
sqlport              = 5432
pass                 = xxx
db                   = dbmail

Usuario del sistema que lanzará los procesos dbmail. Puede ser dbmail o el que creeis para este fin, si queréis crear el usuario dbmail basta con ejecutar "adduser dbmail", esto os pedirá las claves de acceso así como información descriptiva del usuario.

effective_user        = dbmail
effective_group       = dbmail

Definimos los ficheros de log, tanto para errores generales como para errores. Será necesario crear el directorio "/var/log/dbmail". Por defecto intentará crear los ficheros en "/var/log" directamente, pero a mi personalmente me gusta más tener un directorio para los ficheros de log de cada aplicación. Así será más rápido localizar los ficheros cuando sean necesarios.

logfile               = /var/log/dbmail/dbmail.log
errorlog              = /var/log/dbmail/dbmail.err

Hacemos igual para el fichero donde se guarda el pid del proceso en ejecución.

# cambiamos el directorio donde están los pidfile
pid_directory         = /var/run/dbmail

Es muy importante no olvidar asignar permisos a los directorios "/var/run/dbmail" y "/var/log/dbmail/" para el usuario "dbmail" para que el proceso pueda escribir en ellos. Puede asignar permisos ejecutando "chown -R dbmail:dbmail /var/log/dbmail/" y "chown -R dbmail:dbmail /var/run/dbmail".

Encontrarás también instrucciones para definir los certificados necesarios para tls. Si vas a utilizar tls será necesario definirlos.

tls_cafile            = /etc/ssl/certs/ssl-cert-*.pem
tls_cert              = /etc/ssl/certs/ssl-cert-*.pem
tls_key               = /etc/ssl/private/ssl-cert-*.key

Para realizar envíos de correos desde nuestro SMTP por parte de los clientes de nuestro servidor de correos, me gusta configurar estas opciónes: pop_before_smtp y imap_before_smtp, esto hace que el SMTP no requiera autenticación, simplemente verifica si el usuario ha realizado una lectura de una cuenta POP o IMAP, y si la ha realizado en los últimos 5 minutos, permitirá que realice el envío SMTP.

Para establecer esta configuración es necesario definir las siguientes instrucciones.

[POP]

pop_before_smtp       = yes

[IMAP]

imap_before_smtp      = yes

Configurar postfix

En un apartado anterior se ha descrito como instalar postfix.

/etc/postfix/main.cf

transport_maps = pgsql:/etc/postfix/sql-transport.cf
mydestination = localhost, $transport_maps


mynetworks = 127.0.0.0/8, pgsql:/etc/postfix/sql-pop-before-smtp.cf



receive_override_options = no_address_mappings

local_recipient_maps =

smtpd_recipient_restrictions =
    permit_mynetworks,
    reject_unauth_destination,
    permit

Otro enlaces de interés para dbmail

Clamav
Listas grises
MailGraph

Compilar PostgreSQL en Debian

Llevo trabajando unos años con PostgreSQL y no he tenido problemas, pero tenía la necesidad de utilizar funcionalidades de PostgreSQL 9 en un sistema que aún no lo incorporaba en su repositorio de paquetes y me aventuré a compilarlo. Descubrí que es bastante simple y adquiere aún más robustez si es posible. 

Para poder compilar el código fuente de postgresql necesitamos descargar de http://www.postgresql.org/ftp/source/  el paquete de postgresql que quieras instalar en tu sistema. En principio todos deben seguir el mismo procedimiento de instalación. Pero este manual lo he realizado con la versión 9.2.2 y probado con versiones posteriores (9.2.3 y 9.2.4).

Requisitos

Antes de comenzar con la instalación de PostgreSQL compilando el código fuente, es necesario instalar algunos paquetes.

aptitude install bison
aptitude install libreadline-dev libreadline5-dev libreadline6-dev
aptitude install flex
aptitude install zlib1g-dev

 Configurar, compilar e instalar

Simplemente con estos comandos ya tendremos nuestro postgres listo para comenzar a funcionar. Obiamente es necesario crear el directorio "/opt/postgresql-9.2.2" para ubicar ahí nuestro motor de base de datos.

./configure --prefix=/opt/postgresql-9.2.2
make
make install

Puesta en explotación

Creamos el usuario postgres que será el encargado de iniciar el sistema, así como el directorio contenedor de la instancia de la base de datos y le asignamos como propietario al usuario postgres.

adduser postgres --home /opt/postgresql-9.2.2
mkdir /opt/postgresql-9.2.2/data
chown -R postgres:postgres /opt/postgresql-9.2.2

Inicializar la base de datos.

Para iniciar la base de datos utilizaremos el usuario postgres, antes hay que inicializar la instancia de la base de datos.

su postgres
/opt/postgresql-9.2.2/bin/initdb -D /opt/postgresql-9.2.2/data
/opt/postgresql-9.2.2/bin/initdb --pgdata=/opt/postgresql-9.2.2/data

Iniciar la base de datos.

su postgres 
/opt/postgresql-9.2.2/bin/pg_ctl -D /opt/postgresql-9.2.2/data -l logfile start

Test de funcionamiento

Ya tenemos funcionando el motor de base de datos y probamos a crear una base de datos y conectarnos a ella.

/opt/postgresql-9.2.2/bin/createdb test
/opt/postgresql-9.2.2/bin/psql test

Script iniciar/parar/recargar

Un script bastante simple para el inicio y paro del sistema.

#!/bin/bash

### BEGIN INIT INFO
# Provides:             postgresql
# Required-Start:       $local_fs $remote_fs $network $time
# Required-Stop:        $local_fs $remote_fs $network $time
# Should-Start:         $syslog
# Should-Stop:          $syslog
# Default-Start:        2 3 4 5
# Default-Stop:         0 1 6
# Short-Description:    PostgreSQL RDBMS server
### END INIT INFO

case "$1" in
    start)
        su postgres -c "/opt/postgresql-9.2.2/bin/pg_ctl --pgdata /opt/postgresql-9.2.2/data --log /opt/postgresql-9.2.2/logfile start"
        ;;
    stop)
        su postgres -c "/opt/postgresql-9.2.2/bin/pg_ctl stop --pgdata /opt/postgresql-9.2.2/data -m fast"
        ;;
    status)
        su postgres -c "/opt/postgresql-9.2.2/bin/pg_ctl status --pgdata /opt/postgresql-9.2.2/data"
        ;;
    restart)
        $0 stop
        sleep 2
        $0 start
        ;;
    reload)
        su postgres -c "/opt/postgresql-9.2.2/bin/pg_ctl reload --pgdata /opt/postgresql-9.2.2/data"
        ;;
    *)
        echo "Usage: $0 {start|stop|restart|reload|status} "
        exit 1

        ;;
esac

exit 0

Primeros pasos

Ya podemos utilizar nuestra nueva instalación de postgres, aquí os dejo como crear vuestro primer usuario.

postgres@miequipo:/opt/postgresql-9.2.2$ echo "create user miusuario with password 'miclave' " | bin/psql template1
CREATE ROLE

Rotado de log

Escribí un artículo sobre el rotado de log para un xampp, basándome en ese artículo; recomiendo encarecidamente meter el fichero de log dentro del sistema de rotación de log. Es muy simple, únicamente tardarás unos minutos y puede quitarte quebraderos de cabeza posteriores.

Simplemente crea el fichero "/etc/logrotate.d/postgres" con el contenido:

/opt/postgresql-9.2.2/logfile {
    copytruncate
    compress
    dateext
    rotate 365
    size=+100M
    olddir /opt/postgresql-9.2.2/log/
    notifempty
    missingok
}

Tendrás que crear el directorio "/opt/postgresql-9.2.2/log/" donde se almacenará el histórico de log comprimidos. Para terminar solo nos falta ejecutar el procesado del fichero por primera vez para ver que todo está correcto.

logrotate --force /etc/logrotate.d/postgres

dbmail con PostgreSQL Debian (Etch)

Supongo instalado el servidor de base de datos PostgreSQL y me centraré más en la instalación y configuración de dbmail.

Una cosa si tendremos en cuenta, en el fichero /etc/postgresql/8.1/main/pg_hba.conf tenemos que modificar el modo de identificación de los usuario md5, para ello modificaremos la linea:

local all all ident sameuser

Por:

local all all md5

Una vez hecho esto simplemente reiniciaremos PostgreSQL (/etc/init.d/postgresql-8.1 restart) y empezaremos por crear la base de datos dbmail y el usuario dbmail dentro del servidor de base de datos PostgreSQL.

#su postgres
$psql template1
CREATE USER dbmail PASSWORD '123456789';
CREATE DATABASE dbmail OWNER dbmail ENCODING = 'UNICODE';
\q

Probamos el acceso a la base de datos dbmail, por si hay algún problema.

# psql -U dbmail dbmail
Contraseña para usuario dbmail:
Bienvenido a psql 8.1.11, la terminal interactiva de PostgreSQL.

Digite: \copyright para ver los términos de distribución
\h para ayuda de comandos SQL
\? para ayuda de comandos psql
\g o or termine con punto y coma para ejecutar una consulta
\q para salir

dbmail=> \q

Instalaremos postfix, postfix-pgsql, para ello:

aptitude install postfix postfix-pgsql

General type of configuration? Internet Site
Nombre de correo? miservidor.midominio

Vamos a comenzar a instalar dbmail, editaremos el fichero /etc/apt/sources.list y añadimos la linea:

deb http://debian.nfgd.net/debian stable main

Después de actualizar la lista de paquetes (aptitude update) instalaremos el paquete dbmail-pgsql, que instalará por dependencia el paquete dbmail.

aptitude install dbmail-pgsql

Tenemos que crear la estructura de la base de datos, para ello:

cp /usr/share/doc/dbmail-pgsql/examples/create_tables.pgsql.gz .
gunzip create_tables.pgsql.gz .
psql -U dbmail dbmail < create_tables.pgsql

Ya tenemos instalada la aplicación, ahora empezamos a configurar. Editamos el fichero /etc/default/dbmail (nano /etc/default/dbmail) y añadimos:

START_POP3D=1
START_IMAPD=1
START_LMTPD=1

Editamos el fichero /etc/dbmail/dbmail.conf y modificamos estas lineas:

driver = pgsql
authdriver = sql
host = localhost
pass = 123456789
[POP] POP_BEFORE_SMTP = yes,
[IMAP] IMAP_BEFORE_SMTP = yes

Reiniciamos dbmail y tendremos que ver algo parecido a esto:

/etc/init.d/dbmail restart
Starting dbmail servers: dbmail-imapd dbmail-pop3d dbmail-lmtpd.

Tenemos que instalar clamsmtp clamav-daemon postgrey.

aptitude install clamsmtp clamav-daemon postgrey

Es el fichero de configuración de postfix tendremos que tocar algunas cosas.

nano /etc/postfix/main.cf

Añadiremos en la primera línea sin comentario esta variable y la inicializaremos con el nombre del dominio del cual estemos montando el servidor de correo electrónico. Es muy importante que este dominio sea un dominio valido, es decir que tenga todas las características de un dominio, .com .es .net ... algunos servidores de anti-spam se basan en el nombre del servidor de correo para definirlo como spam y lo último que queremos que ocurra es que nuestro nuevo servidor de correo sea marcado como servidor de spam.

myhostname = www.midominio.com

# resuelve en /etc/hosts (no solo dns)
smtp_host_lookup = dns, native

Definimos que busque en el fichero /etc/postfix/sql-pop-before-smtp.cf las IPs que tienen permiso para enviar correos desde nuestro servidor, así no necesitamos tener autenticación en nuestro servidor SMPT, porque siempre que hagamos una lectura antes del intento de envío, el servidor POP o IMAP capturará nuestra IP y gracias a la conexión del fichero /etc/postfix/sql-pop-before-smtp.cf nuestra IP aparecerá en la lista de IPs permitidas.

# utilizo POP BEFORE SMTP para no actuar de OpenRelay
mynetworks = 127.0.0.0/8, pgsql:/etc/postfix/sql-pop-before-smtp.cf

Igualmente utilizaremos una conexión a la base de datos para sacar el listado de dominios de los que debemos aceptar correos electrónicos. En esta ocasión la guardamos en una variable y después lo añadimos a la variable mydestination.

# se pueden añadir dominios en la base de datos
transport_maps = pgsql:/etc/postfix/sql-transport.cf
mydestination = localhost, localhost.localdomain, $transport_maps
smtpd_recipient_restrictions =
permit_mynetworks,
reject_unauth_destination,
check_policy_service inet:127.0.0.1:60000
content_filter = scan:localhost:10026
receive_override_options = no_address_mappings

Ahora editaremos el fichero /etc/postfix/master.cf y verificamos que estas tres instrucciones está activas.

dbmail-lmtp unix - - n - - lmtp
-o disable_dns_lookups=yes
# AV scan filter (used by content_filter)
scan unix - - n - 16 smtp
-o smtp_send_xforward_command=yes
# For injecting mail back into postfix from the filter
127.0.0.1:10025 inet n - n - 16 smtpd
-o content_filter=
-o receive_override_options=no_unknown_recipient_checks,no_header_body_checks
-o smtpd_helo_restrictions=
-o smtpd_client_restrictions=
-o smtpd_sender_restrictions=
-o smtpd_recipient_restrictions=permit_mynetworks,reject
-o mynetworks_style=host
-o smtpd_authorized_xforward_hosts=127.0.0.0/8,pgsql:/etc/postfix/sql-pop-before-smtp.cf

Aún nos quedan crear los ficheros /etc/postfix/sql-pop-before-smtp.cf y /etc/postfix/sql-transport.cf
El primero le indica a Postfix las IPs que tienen permiso para enviar (SMTP) por haber leído correo previamente (POP3 o IMAP). Es el archivo /etc/postfix/sql-pop-before-smtp.cf y su contenido es:

user=dbmail
password=123456789
dbname=dbmail
hosts=localhost
table=dbmail_pbsp
select_field=since
where_field=ipnumber

El segundo nos va a permitir agregar nuevos dominios desde la base de datos. Es el archivo /etc/postfix/sql-transport.cf y su contenido:

user=dbmail
password=123456789
dbname=dbmail
table=dbmail_transport
select_field=transport
where_field=domain
hosts=localhost

Ahora tendremos que crear la tabla dbmail_transport en la base de datos dbmail si no existe. No podemos olvidarnos de hacer el INSERT que agregará el/los dominio a los que escuchará nuestro servidor de correo.

psql -U dbmail dbmail
Contraseña para usuario dbmail:
Bienvenido a psql 8.1.11, la terminal interactiva de PostgreSQL.

Digite: \copyright para ver los términos de distribución
\h para ayuda de comandos SQL
\? para ayuda de comandos psql
\g o or termine con punto y coma para ejecutar una consulta
\q para salir

dbmail=> select * from dbmail_transport;
ERROR: no existe la relación «dbmail_transport»
dbmail=> CREATE TABLE dbmail_transport (domain VARCHAR(100) NOT NULL, transport VARCHAR(100) NOT NULL DEFAULT 'dbmail-lmtp:localhost:24');
CREATE TABLE
dbmail=> INSERT INTO dbmail_transport (domain) VALUES ('midominio.com');
INSERT 0 1

Una vez hecho todo, reiniciaremos Postfix.

/etc/init.d/postfix restart

Aún queda un problema con Dbmail y PostgreSQL. Existe un problema con ciertas codificaciones. Es necesario editar el archivo /etc/init.d/dbmail y agregar un par de lineas. Se deben poner al principio ya que se trata de una variable de entorno utilizada en la conexión con PostgreSQL.

# Codificación que se lebantará con la base de datos PostgreSQL
PGCLIENTENCODING=UNICODE
export PGCLIENTENCODING

Así podemos dar de alta al usuario jmruiz con password jmruiz y correo electrónico jmruiz@midominio.com.

dbmail-users -a jmruiz -w jmruiz -s jmruiz@midominio.com
Adding INBOX for new user... ok.
[jmruiz@midominio.com]
Done
jmruiz:x:4:0:0.00:0.00:jmruiz@midominio.com

Para probar el usuario podemos hacer telnet al puerto POP y acceder como el usuario jmruiz.

telnet localhost 110
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
+OK DBMAIL pop3 server ready to rock
user jmruiz
+OK Password required for jmruiz
pass jmruiz
+OK jmruiz has 0 messages (0 octets)
quit
+OK see ya later
Connection closed by foreign host.

Si ves en el log /var/log/mail.log este error "fatal: pgsql:/etc/postfix/sql-pop-before-smtp.cf(0,lock|fold_fix): table lookup problem". Seguro que te será de mucha ayuda este enlace.

fatal: pgsql:/etc/postfix/sql-pop-before-smtp.cf(0,lock|fold_fix): table lookup problem

Después de seguir el manual de instalación de dbmail con postgres para Ubuntu, no me funcionó correctamente. Después de mucho buscar soluciones a este problema.

May 19 12:41:43 druida postfix/smtpd[3826]: connect from fg-out-1718.google.com[72.14.220.156]
May 19 12:41:43 druida postfix/smtpd[3826]: warning: pgsql query failed: fatal error from host localhost: ERROR: la sintaxis de entrada no es v?lida para tipo inet: ?fg-out-1718.google.com??
May 19 12:41:43 druida postfix/smtpd[3826]: fatal: pgsql:/etc/postfix/sql-pop-before-smtp.cf(0,lock|fold_fix): table lookup problem
May 19 12:41:44 druida postfix/master[3787]: warning: process /usr/lib/postfix/smtpd pid 3826 exit status 1
May 19 12:41:44 druida postfix/master[3787]: warning: /usr/lib/postfix/smtpd: bad command startup -- throttling

Me dí cuenta de que en el fichero /var/log/postgresql/postgresql-8.1-main.log tenía el verdadero problema:

2008-05-19 12:41:43 CEST ERROR: la sintaxis de entrada no es válida para tipo inet: «fg-out-1718.google.com»

Al parecer la estructura de la tabla dbmail_pbsp no es totalmente correcta, es posible que es esta nueva versión de dbmail no esté bien definida. Para solucionarlo hay que cambiar el tipo de datos del campo ipnumber, con este script podrás cambiarlo sin perder los datos que ya tienes.

alter table dbmail_pbsp ADD after_ipnumber text;
update dbmail_pbsp set after_ipnumber = cast(ipnumber as text);
alter table dbmail_pbsp drop ipnumber;
alter table dbmail_pbsp rename after_ipnumber to ipnumber;
alter table dbmail_pbsp alter ipnumber set default('0.0.0.0');
commit;

Después de esto el servidor me funciona perfectamente.

"This account is currently not available." Xampp Addons PostgreSQL en Fedora Core 6

/opt/lampp/lampp startpostgresql
Starting PostgreSQL
This account is currently not available.

Me pasó esto al intentar ejecutar el Addons para Xampp de PostgreSQL. El problema es que el usuario nobody (el que corre el servicio), no tiene definida shell en el /etc/passwd

Para definirla editamos en /etc/passwd la línea que define al usuario nobody:

nobody:x:99:99:Nobody:/:/sbin/nologin

Para dejarla así:

nobody:x:99:99:Nobody:/:/bin/bash

Una vez hecho esto ya podemos reiniciar Xampp que debería arrancar PostgreSQL perfectamente.

Xampp con PostgreSQL backup

Ya sabemos que existen Addons de PostgreSQL para XAMPP, pero... ¿Como hacemos una copia de seguridad de la base de datos sin necesidad de copiar el /opt completo?

Si hacemos " /opt/lampp/bin/pg_dump mydb > myfichero.sql " funciona, pero nos pedirá el password del usuario.

¿Cómo podemos hacer para que no nos pida nunca el password del usuario?

export PGPASSWORD=mypassword
/opt/lampp/bin/pg_dump mydb > myfichero.sql

Así cada vez que lancemos el script no nos pedirá el password.