DevHeads.net

Postings by Rich Shepard

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
/var/spool/postfix/maildrop.

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="http://wiki2.dovecot.org/PasswordDatabase/PAM" title="http://wiki2.dovecot.org/PasswordDatabase/PAM">http://wiki2.dovecot.org/PasswordDatabase/PAM</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.

Rich

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 (logwatch.pl) 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?

TIA,

Rich

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
WHERE waterchem.site = s.name;

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

Rich

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.site, c.sampdate, c.param, c.quant, c.ceneq1, c.low, c.high,
s.stream, s.basin
FROM chemistry as c, sites as s
WHERE c.site == s.siteid;

The problem is that both c.site 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?

TIA,

Rich

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?

Rich

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
projects.

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.

TIA,

Rich

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
messages?

/[A-Z].*/

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

Thanks,

Rich

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?

Danke,

Rich

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[67.23.35.254]: 450 4.7.1 Client host rejected: cannot find your
hostname, [67.23.35.254]; from=<dabo-users- ... at leafe dot com>
to=<rshepard@appl-ecosys.com> proto=ESMTP helo=<marge.leafe.com>

Yet,

[rshepard@salmo ~]$ host 67.23.35.254
254.35.23.67.in-addr.arpa domain name pointer
67-23-35-254.static.slicehost.net.

and

[rshepard@salmo ~]$ host leafe.com
leafe.com has address 67.23.35.254
leafe.com 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
work?

Rich

/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.

Apparent Problem With NULL in Restoring pg_dump

The .sql file produced by pg_dump is properly terminated with '\.' as the
last line, yet I continue to encounter this error:

ERROR: invalid input syntax for type real: " "
CONTEXT: COPY chemistry, line 47363, column quant: " "

when trying to re-create the table.

It appears that this error is generated when a row has a missing value in
the 'quant' column and the column contains '\N' in the text file.

Cryptic Error Message Importing Table Dump

Now that I fixed the rows that had the inadvertent newlines in one column,
I'm trying to read in the fixed table from the .sql file produced by
pg_dump. I know there are duplicate rows now that I removed the newlines,
and those are easily fixed (although the reported line numbers don't match
what I see in emacs). There is, however, one problem that I don't understand
so I can't find the row and fix it.

Identifying Reason for Column Name Returned by SELECT

I run this SELECT statement on a table:

select distinct(site_id) from chemistry order by site_id;

and in the returned set I see:

GW-21
GW-22
GW-22 +

GW-24

I want to find that row returning 'GW-22 +' because I believe it
should be 'GW-23'.