Postings by Rich Shepard

Upgrade to -3.2.5: permissions question

I just upgraded from 3.2.4 to 3.2.5 and ensured that /usr/sbin/postdrop
and /usr/sbin/postqueue were set gid:

-rwxr-sr-x 1 root root 13888 Jan 28 08:58 /usr/sbin/postdrop*
-rwxr-sr-x 1 root root 18012 Jan 28 08:58 /usr/sbin/postqueue*

Yet, when I start postfix I see these messages:

Jan 28 09:31:55 salmo postfix/postfix-script[16119]: warning: not owned by
group postdrop: /usr/sbin/postqueue
Jan 28 09:31:55 salmo postfix/postfix-script[16120]: warning: not owned by
group postdrop: /usr/sbin/postdrop
Jan 28 09:31:55 salmo postfix/postfix-script[16124]: starting the Postfix mail

No messages delivered to INBOX

Running postfix-3.2.4 on Slackware-14.2. My server and workstation are on
the same host. Yesterday, about mid-day, messages to me stopped being
delivered to my INBOX. /var/spool/mail shows:

-rw-rw---- 1 rshepard mail 207100 Jan 15 12:30 /var/spool/mail/rshepard

Messages from mail lists are directed by procmail's recipes.rc to the
proper file in ~/mail/.

Finding why outbound mail is delayed

Running postfix-3.2.4 here on Slackware-14.2. I am a professional services
sole practitioner, not a professional system or network admin.

After several years having outbound mail forwarded through my ISP's mail
server I changed ISPs and now have a static IP address. The other recent change
here is replacing the old Netgear FVS318 router with a Ubiquiti EdgeRouter-X
fast enough to take advantage of the 15/5Mbps speeds of my fiber connection.

Since the end of last week I find many messages delayed 1-2 days; several
hundred are listed in logwatch's daily report.

Ownership question: version 3.2.2

I just upgraded postfix from 3.1.2 to 3.2.2.

transport_destination_rate_delay question

Running postfix-3.0.3 on Slackware-14.1 here.

I need to relay outbound messages through my ISP. When I send newsletters
to subscribers I need to limit the number of messages per hour to < 300.

Ownership/Permissions of /var/spool/postfix

During the most recent upgrade I inadvertently altered owner, group,
and/or permissions in /var/spool/postfix. I've looked for information in all
the README files that seemed applicable but have not found a list of how
/var/spool/postfix subdirectories should be set. Please point me to a doc
that has this information.

While 'postfix check' shows no errors, when I run mailx from the command
line I get warnings about an inability to write to

Removing Stuck Rejected Message

For the past few days an incoming message rejected by a body_checks rule
has been stuck somewhere and prevents the daily logwatch report being mailed
to me. No subdirectory in /var/spool/postfix/defer/ or ../deferred/ contains
a file and I don't know where to find this so I can remove it.

Transitioning from cyrus-SASL to dovecot-SASL

I'm not a professional SysAdmin or network admin but have been running my
own smtpd using cyrus-SASL for years. I want now to transition to using
dovecot-SASL and have difficulty correctly configuring dovecot.

Reading the postfix/dovecot Web pages and following the links, I created
/etc/pam.d/dovecot. On the page <a href="" title=""></a>
page, under Service Name, I read, "By default Dovecot uses dovecot as the
PAM service name, so the configuration is read from /etc/pam.d/dovecot" yet
the two examples are for /etc/pam.d/imap ../pop3 and /etc/pam.d/mail.

2.11.4: Error in post-install

When upgrading to -2.11.4 (on Slackware-14.1), a message is displayed that
line 504 in /usr/libexec/postfix/post-install has too many arguments. Adding
double quotes to $path fixes this problem.

Is this an issue with the source for post-install? I don't see any
reference to that file in the SlackBuilds script.


2.11.1: sendmail issue

A few weeks ago I upgraded postfix from 2.11.0 to 2.11.1 and soon saw that
my daily log summary reports stopped appearing in my inbox each morning. The
script ( is run from /etc/cron.daily and has worked faithfully
for years. Until recently.

I've been going back and forth between the script, it's configuation
files, whether the output goes to stdout or mail, and how sendmail is
invoked, looking for the source of the problem. It is only this morning that
I confirmed the issue is with sendmail in 2.11.1.

Select Rows With Only One of Two Values

What I thought would be a simple, single table select keeps eluding me.
I've looked in Rick van der Laans' book and the Joe Celko books here and
have not learned how to write the query.

The table has a Boolean indicator column with values of 0 or 1 for each
row in the table and another attribute column for parameter names. I need to
find all parameter names where the indicator value is only 0 for all rows of
that parameter. At least some of the parameters have both rows with 0 and
rows with 1 in the indicator attribute.

Finding Duplicate Rows during INSERTs

Source data has duplicates. I have a file that creates the table then
INSERTS INTO the table all the rows. When I see errors flash by during the
'psql -d <database> -f <file.sql>' I try to scroll back in the terminal to
see where the duplicate rows are located. Too often they are too far back to
let me scroll to see them.

There must be a better way of doing this. Can I run psql with the tee
command to capture errors in a file I can examine? What is the proper/most
efficient way to identify the duplicates so they can be removed?



UPDATE Syntax Check

One table, waterchem, with primary key 'site' has columns easting and
northing with no values in them. A second table, sites, with primary key
'name' has values for easting and northing for each row. I want to update
waterchem with the easting and northing values from sites. My proposed
statement is,

UPDATE waterchem
SET waterchem.easting, waterchem.northing = sites.easting, sites.northing
FROM sites AS s

Is this correct? If not, what approach should I adopt?


Populate Table From Two Other Tables

I want to combine columns in two tables and use the exported resulting
table for statistical analyses. The SQL script is:

INSERT INTO waterchem (site, sampdate, param, quant, ceneq1, low, high,
stream, basin)
SELECT, c.sampdate, c.param, c.quant, c.ceneq1, c.low, c.high,, s.basin
FROM chemistry as c, sites as s
WHERE == s.siteid;

The problem is that both and s.siteid are of type VARCHAR(16) and
postgres tells me,

ERROR: operator does not exist: character varying == character varying

and provides the hint to add explicit type casts.

Formatting time for INSERT INTO

The table has a column 'coll_time' of type time without time zone. New
rows for the table are in a .sql file and the time values throw an error at
the colon between hours:minutes. Do time values need to be quoted?



Move Tables From One Database to Another

I'm storing vector map attribute data in postgres tables and somehow
managed to create two databases (of similar names) rather than one. I want
to combine the two.

For tables that exist in the one database I want to eliminate, I thought
to use pg_dump to create .sql files, then use pg_restore to add the table to
the other database. Did this for one table (with 4201 rows), but 'pg_restore
-d database_name -t table_name' appears to not complete; it seems to have
hung up somewhere.

Valid Input Syntax for Type DATE

I'm trying to insert rows into a table, but some date and time columns are
missing values. In the INSERT INTO ... statements of the .sql file I've
tried various formats: ,, and ,'', and ,' ', but they all generate the error
of invalid syntax for type date.

When I have missing date of the date and time types, how do I validly
represent them in the input file?


Strategy for Primary Key Generation When Populating Table

I have a lot of data currently in .pdf files. I can extract the relevant
data to plain text and format it to create a large text file of "INSERT INTO
..." rows. I need a unique ID for each row and there are no columns that
would make a natural key so the serial data type would be appropriate.

When I prepare the text file I can start each row with the delimiter (',')
to indicate there's a table column preceding.

Single Table Select With Aggregate Function

I'm probably not seeing the obvious so I keep making the same mistake.

Recommended Protocol: Adding Rows to Table

The data originated in a spreadsheet and, based on my experience, contains
duplicate records. After reformatting there are 143,260 rows to insert in
the table. The approach I tried seems to have problems (explained below) and
I would like to learn the proper way to insert rows in either an empty table
or one with existing rows since I'll need to do this procedure for my

The table was created with the primary key and I used INSERT INTO ... to
load the data. Many duplicate records, so I split the file into smaller ones
and re-ran the command to load them.

Blank Numeric Column For INSERT

I am trying to load 143K rows into a postgres-9.0.5 table from an ASCII
text file. The file consists of INSERT INTO ... statements and the VALUES
are comma delimited. One column is numeric (REAL), but ~10K rows have that
value missing, and postgres rejects the lines.

The column does not have a NOT NULL constraint.

The command line I use is 'psql -d <database_name> -f wq.sql'.

Originally I had two commas in sequence since there were no values between
them. Next I tried a space between the two commas.

Syntax To Create Table As One In Another Database

I need a pointer to the appropriate docs that show me how to specify a
table in a different database.

What I want is to CREATE TABLE <tablename> AS TABLE
<otherdatabase><same_tablename>; but using a period (dot) to separate the
source database and table name doesn't work. My searches of the 9.0.x docs
have missed finding this information.



Specifying UCE Filter: All Uppercase Letters

A common form of spam comes with the body text in all uppercase letters.
Perhaps the senders are all using Apple ][e computers. Anyway, if I add the
following to /etc/postfix/body_checks will I unintentionally reject valid


If this will not selectively identify such messages, please suggest a
regex expression that will.



psql died in midst of session

This has not happened before to me. I'm running postgres-9.0.4 on
Slackware-13.1. I've been working on the command line using the psql shell
updating and fixing a table when the application failed on me:

PANIC: could not open file "pg_xlog/000000010000000000000046" (log file 0,
segment 70): Permission denied
PANIC: could not open file "pg_xlog/000000010000000000000046" (log file 0,
segment 70): Permission denied
The connection to the server was lost.

RE: Postfix not resolving name from IP address

Where do I do this?



Postfix not resolving name from IP address

I re-installed bind and both host and dig are now working, but attempts to
join a mail list fail:

Oct 11 08:31:44 salmo postfix/smtpd[30881]: NOQUEUE: reject: RCPT from
unknown[]: 450 4.7.1 Client host rejected: cannot find your
hostname, []; from=<dabo-users- ... at leafe dot com>
to=<> proto=ESMTP helo=<>


[rshepard@salmo ~]$ host domain name pointer


[rshepard@salmo ~]$ host has address mail is handled

SELECT statement not working as intended

I'm trying to query the table to extract the single highest value of a
chemical by location and date.

Selecting All Columns Associated With Maximum Value of One Column

A table (chemistry) has columns named site_id, sample_date, param, quant,
and str_name (among other columns). I want to find the site_id, sample_date,
and quant for a specific str_name and param.

Quick-and-Dirty Data Entry with LibreOffice3?

Rather than writing an application right now to enter data into a table I
thought of trying LibreOffice as a front end. But, it doesn't seem to work
as OO.o did. This leads to two questions:

1) Can someone show me how to use LO as a front end to a postgres table?

2) Is there another tool suitable for a linux box for some data entry


/usr/local/pgsql/data permissions

My server just crashed because a CPU-intensive build threatened to
overheat the processor so the system shut down. When I rebooted and tried to
start postgres the attempt failed because `data directory
"/usr/local/pgsql/data" has group or world access'. As far as I can recall,
it's always been 755 like the other directories in /usr/local/pgsql.

When I changed the perms to 700 I was able to start postgres.