Thread: Using pgsql to archive e-mail
Hi Everyone, I am looking for a way that I can archieve a lot e-mail messages to a data base. I'm thinking of using pgsql. Has anyone done this if so could you point me in the right direction as to where I can get started? Maybe an example insert funcion or such? Thanks in advance, Randy Smith Tiger Mountain Technologies
> I am looking for a way that I can archieve a lot e-mail messages to a > data > base. I'm thinking of using pgsql. Been there, done that. Works OK for me. Desreves a major rewrite, because storing message bodies as BLOBs is useless and dangerous. If only there were 25 hours in the day (sigh) So yes, it's possible. And if you know nothing about DBs and SQL, get a couple of books and start there. There are caveats in a general email archive application, so you'll be better off beginning with something simple. -- contaminated fish and microchips huge supertankers on Arabian trips oily propaganda from the leaders' lips all about the future there's people over here, people over there everybody's looking for a little more air crossing all the borders just to take their share planning for the future Rainbow, Difficult to Cure
Well, with the next version having toast, what about parsing the email and storing the body as TEXT? Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "KuroiNeko" <evpopkov@carrier.kiev.ua> To: <pgsql-general@postgresql.org> Sent: Tuesday, October 24, 2000 5:31 PM Subject: Re: [GENERAL] Using pgsql to archive e-mail > > I am looking for a way that I can archieve a lot e-mail messages to a > > data > > base. I'm thinking of using pgsql. > > Been there, done that. Works OK for me. Desreves a major rewrite, because > storing message bodies as BLOBs is useless and dangerous. If only there > were 25 hours in the day (sigh) > So yes, it's possible. And if you know nothing about DBs and SQL, get a > couple of books and start there. There are caveats in a general email > archive application, so you'll be better off beginning with something > simple. > > > -- > > contaminated fish and microchips > huge supertankers on Arabian trips > oily propaganda from the leaders' lips > all about the future > there's people over here, people over there > everybody's looking for a little more air > crossing all the borders just to take their share > planning for the future > > Rainbow, Difficult to Cure
> Well, with the next version having toast, what about parsing the email > and > storing the body as TEXT? Actually, email message should be parsed anyway, at least headers separated from the body. In most cases it's not enough and one has to store the headers parsed into separate tuples etc etc. TOAST is a splendid endeavour, but there were huge archives before, when we didn't have technology like that at hand. It takes a significant burden off developer's shoulders, but it can't stop the preservation law. This burden is placed on your server, both hardware and software parts. In brief, the strategy here depends on usage pattern. We can't predict maximal length of message body, so we have to decide beforehand, what parts should be searchable, indexed and what can just be stored. As an example, the dirty hack that I call my email archive (it was written in 40 minutes, it even uses psql to talk to the server), stores message bodies as BLOBs (it shouldn't, plain files are easier). Searching on sender's address and subject is enough for me. What's enough for you is up to you. -- contaminated fish and microchips huge supertankers on Arabian trips oily propaganda from the leaders' lips all about the future there's people over here, people over there everybody's looking for a little more air crossing all the borders just to take their share planning for the future Rainbow, Difficult to Cure