select * from depesz;

at the end of previous part of this text, we got our system working, and accepting emails for local accounts. plus i promised to add quota now so, let’s do it.

- MORE -

at the end of last part of this text, i finished having ready postgresql and exim (pop/imap installed but not configured), with exim being able to ask postgresql whether given domain is local. now, let the saga continue.

- MORE -

in first part of this text i described how i installed base ubuntu system in chroot, and then got exim, courier and postgresql in there. now to some configuration.

- MORE -

for quite some time i wanted to write about my experiences with combining smtp server with sql server.

this subject for some people might be too boring, for others - it might be seen as a simple howto. but in my opinion it clearly shows that when you combine 2 such technologies outcome is much better then it would seem (it’s called synergy in merketoid-speak).

this subject will be divided into several parts, that will be posted separately (yeah, let’s get some traffic to website

also, i hope to extend it in future if/when i’ll find something else that can be added/changed in proposed solution.

so, without further ado, let’s start:

- MORE -

just lately we found interesting case about shared_buffers settings.

the database in question is rather simple:

• 3 tables
• a bit over 60 gigabytes
• around 150 million rows

the server we run it on is not really fancy:

• 2, single core, 3ghz xeons
• 16 gb of ram
• 6x 72gb, scsi, 15krpm discs in low-end hardware raid 10.

- MORE -

zapraszam na nowo-otwarty blog motoryzacyjny auta.cc.

nice machine with 2 gb of ram, 800 megabytes in 2 logfiles. single word as search phrase. polish utf-8 locale (pl_PL.UTF-8), gnu grep 2.5.1. results?

=> time grep -in reloading postgresql-2007-10-22_000000.log postgresql-2007-10-22_120909.log
postgresql-2007-10-22_000000.log:40001:2007-10-22 10:50:13.528 CEST @ 24681 LOG: received SIGHUP, reloading configuration files
postgresql-2007-10-22_120909.log:1215696:2007-10-22 12:15:21.769 CEST @ 24681 LOG: received SIGHUP, reloading configuration files
real 1m21.212s
user 1m20.909s
sys 0m0.284s

same, check without -i:

=> time grep -n reloading postgresql-2007-10-22_000000.log postgresql-2007-10-22_120909.log
postgresql-2007-10-22_000000.log:40001:2007-10-22 10:50:13.528 CEST @ 24681 LOG: received SIGHUP, reloading configuration files
postgresql-2007-10-22_120909.log:1215696:2007-10-22 12:15:21.769 CEST @ 24681 LOG: received SIGHUP, reloading configuration files
real 0m1.147s
user 0m0.868s
sys 0m0.268s

after setting locale to C:

=> time grep -in reloading postgresql-2007-10-22_000000.log postgresql-2007-10-22_120909.log
postgresql-2007-10-22_000000.log:40001:2007-10-22 10:50:13.528 CEST @ 24681 LOG: received SIGHUP, reloading configuration files
postgresql-2007-10-22_120909.log:1215696:2007-10-22 12:15:21.769 CEST @ 24681 LOG: received SIGHUP, reloading configuration files
real 0m1.209s
user 0m0.896s
sys 0m0.316s

all tests were repeated many times to get all data in memory, and check for extreme values.

does anybody need another proof that locale “thing” is broken? of course it might be that only locale handling in grep is bad, but anyway - it’s still locale issue.

one of the more common problems new users have with postgresql (especially those that came from mysql background), is the lack of easy way to grant/revoke/do-something with many objects (tables/sequences/views) at once.

there are number of pages that deal with the problem, let’s just name some from #postgresql infobots:

• http://pgedit.com/public/sql/acl_admin/index.html
• http://www.archonet.com/pgdocs/grant-all.html

now, both of these pages have their benefits, but i’d like to show something simpler, yet (perhaps) more powerful.

instead of giving you the fish (figuratively speaking) i will give you the net and the skills so you’ll be able to do the magic yourself.

- MORE -

ever seen one of those? i mean the “fatal: ident authentication failed”?

or, ever seen anyone having this problem when connecting to postgresql?

how often is this problem related to debian/post-debian linux distributions? 99%? 100%?

on #postgresql on irc.freenode.net it is the most common problem. my own irc logs show that “ident” showed over 300 times over last 41 days. now, that’s something. and how come we have this problem? what can be done with it? read on.

- MORE -

this questions pops every once in a while on irc.

some guy has a table, and it contains duplicated rows.

basically there are 2 possible scenarios:

• all columns are duplicated
• only some columns are duplicated

so, how to remove duplicates from such tables?

- MORE -