postgresql table does not exist error
hi everyone i have installed and configured postgresql postfix and dovecot on arch linux the database called mail has this table and is owned by mailreader CREATE TABLE "al" ( alias text NOT NULL, email text NOT NULL ); the postfix file (/etc/postfix/pgsql-aliases.cf) has this hosts = /run/postgresql/ user = mailreader dbname = mail query = SELECT alias FROM "al" WHERE email='%s' (postgresql log file) ERROR: relation "al" does not exist at character 19 STATEMENT: SELECT alias FROM "al" WHERE email='ivy.tissisat.co.uk' (postfix log) Oct 05 18:19:33 testy postfix/smtpd[801]: connect from localhost.localdomain[127.0.0.1] Oct 05 18:20:21 testy postfix/proxymap[802]: warning: pgsql query failed: fatal error from host /run/postgresql: ERROR: relation "al" does not exist?LINE 1: SELECT alias FROM "al" WHERE email='ivy.tissi Oct 05 18:20:21 testy postfix/trivial-rewrite[805]: warning: virtual_alias_domains: proxy:pgsql:/etc/postfix/pgsql-aliases.cf: table lookup problem Oct 05 18:20:21 testy postfix/trivial-rewrite[805]: warning: virtual_alias_domains lookup failure Oct 05 18:21:07 testy postfix/trivial-rewrite[805]: warning: virtual_alias_domains: proxy:pgsql:/etc/postfix/pgsql-aliases.cf: table lookup problem Oct 05 18:21:07 testy postfix/trivial-rewrite[805]: warning: virtual_alias_domains lookup failure Oct 05 18:21:07 testy postfix/smtpd[801]: NOQUEUE: reject: RCPT from localhost.localdomain[127.0.0.1]: 451 4.3.0 : Temporary lookup failure; from= to= proto=ESMTP helo= Oct 05 18:26:07 testy postfix/smtpd[801]: timeout after RCPT from localhost.localdomain[127.0.0.1] Oct 05 18:26:07 testy postfix/smtpd[801]: disconnect from localhost.localdomain[127.0.0.1] ehlo=1 mail=1 rcpt=0/1 commands=2/3 i have read about quoting and case folding in postgresql my original table name and references to it was in lower case and without quotes so i recreated a new table with name lowercase characters with quotes and referenced the table with quotes and i still have the same errors. i also changed the host line to localhost in /etc/postfix/pgsql-aliases.cf and the error still occurs any sugestions what the problem might be ? shadrock
Re: postgresql table does not exist error
Hi, >the postfix file (/etc/postfix/pgsql-aliases.cf) has this > >hosts = /run/postgresql/ You should specify the socket to use. >From http://www.postfix.org/pgsql_table.5.html hosts The hosts that Postfix will try to connect to and query from. Specify unix: for UNIX-domain sockets, inet: for TCP connections (default). Example: hosts = host1.some.domain host2.some.domain:port hosts = unix:/file/name The hosts are tried in random order, with all connections over UNIX domain sockets being tried before those over TCP. The con- nections are automatically closed after being idle for about 1 minute, and are re-opened as necessary. NOTE: the unix: and inet: prefixes are accepted for backwards compatibility reasons, but are actually ignored. The PostgreSQL client library will always try to connect to an UNIX socket if the name starts with a slash, and will try a TCP connection otherwise. >user = mailreader >dbname = mail >query = SELECT alias FROM "al" WHERE email='%s' Regards - christian
