On 6/3/10 , Jun 3, 11:17 AM, William Blunn wrote: > On 03/06/2010 19:08, David Jonas wrote: >> On 6/3/10 , Jun 3, 10:16 AM, William Blunn wrote: >> >>> On 03/06/2010 17:35, David Jonas wrote: >>> >>>> We're using the SQLite backend for authentication of Postfix SASL. >>>> When >>>> the db is replaced we HUP dovecot to close and reopen its connection. >>>> During this time it appears the socket file is removed and Postfix >>>> rejects the authentication attempt. From the logs: >>>> >>>> Jun 3 00:23:02 xxx dovecot: dovecot: SIGHUP received - reloading >>>> configuration >>>> Jun 3 00:23:02 xxx postfix/smtpd[14746]: warning: SASL: Connect to >>>> private/auth failed: Connection refused >>>> Jun 3 00:23:02 xxx postfix/smtpd[14746]: warning: >>>> unknown[dd.dd.dd.dd]: >>>> SASL LOGIN authentication failed: >>>> Jun 3 00:23:02 xxx postfix/smtpd[14746]: NOQUEUE: reject: RCPT from >>>> unknown[dd.dd.dd.dd]: 554 5.7.1<u...@example.com>: Relay access >>>> denied; >>>> from=<us...@example.com> to=<u...@example.com> proto=ESMTP >>>> helo=<localhost.localdomain> >>>> >>>> Jun 3 00:23:02 xxx postfix/smtpd[14930]: warning: >>>> unknown[dd.dd.dd.dd]: >>>> SASL LOGIN authentication failed: Connection lost to authentication >>>> server >>>> Jun 3 00:23:02 xxx postfix/smtpd[14930]: lost connection after AUTH >>>> from unknown[dd.dd.dd.dd] >>>> >>>> Is there an obvious way around this? I know I could somehow merge the >>>> changes into the running sqlite db but that undermines the >>>> simplicity of >>>> the design I have. Maybe a patch to reopen the db if it's replaced? Or >>>> perhaps I should just switch to a different db format -- that's >>>> probably >>>> the quickest/easiest solution. Any other ideas? There are about 20k >>>> entries to deal with. >>>> >>> It sounds like your updates arrive in the shape of entire-table >>> updates. >>> >>> That is no problem. You can easily apply entire-table updates to the >>> database without having to re-create the SQLite database file, and >>> without having to tell Dovecot. >>> >>> Just create a new table (with a different name) inside the SQLite >>> database file, with the new content, then snap it into place using a >>> pair of table renames inside a transaction; then delete the old table. >>> >>> That way you don't need to re-create the database file or HUP Dovecot, >>> and Dovecot will only ever see the old data or the new data. >>> >> That sounds reasonable, not sure why I didn't think of it! Thanks. >> > > If you are going to keep the SQLite database around, you might want to > look at vacuuming it periodically using either VACUUM or the > auto_vacuum PRAGMA depending on what fits your context best. > > http://sqlite.org/lang_vacuum.html > http://sqlite.org/pragma.html#pragma_auto_vacuum > > Bill Here is what I came up with. Seems to work pretty well.
#!/bin/sh TMP=/home/config/tmp DATA=/home/config/data rsync -t -e ssh x...@xxx:mail/accounts.sqlite $TMP echo " ATTACH '$TMP/accounts.sqlite' AS db2; BEGIN TRANSACTION; DROP TABLE IF EXISTS mytemp; CREATE TABLE mytemp ( 'user' varchar(128) COLLATE NOCASE, 'domain' varchar(128) COLLATE NOCASE, 'password' varchar(64) default NULL ); INSERT INTO mytemp (user,domain,password) SELECT user,domain,password FROM db2.accounts; DROP TABLE accounts; ALTER TABLE mytemp RENAME TO accounts; COMMIT TRANSACTION; " | sqlite3 $DATA/accounts.sqlite No vacuuming needed really. It doubles the size of the db but only once. I didn't see a speed improvement on the consecutive runs but I also didn't see any additional latency. Looks good so far. Thanks for your help!