Re: PostgreSQL and embedded PC with Compact Flash?

On Jan 17, 2007, at 9:52 , k.novo wrote:

> Hello,
>
> I have strange question and idea.
> Use PostgreSQL in embedded PC (with Linux) as data storage for
> collection measure data.
> Problem is in limited Write cycle in Compact Flash HDD (about 100.000)
> My idea is collect data to temporary table in RAM (RAM Disk) and
> once day rewrite all collect data to main table to Compact Flash.
> Question is. How set PostgreSQL and according as it is possible
> with PostgreSQL?
> Is possible minimize soever Write entry to PostgreSQL files on CF?
> Possibly, recommended another SQL server.

What I do is create a RAM file system and run the postgresql db in
it. Every 30 seconds, the ramfs is synced to the CF. I can only get
away with this because the data is mostly transient in nature, i.e.
if the box fails, the data is useless (network appliance). I sync
only to maintain some customer configuration in the database. In my
testing, the db manages to recover on failure. If it doesn't, the
contingency plan is to load in a default configuration; customers can
make backups.

It's not beautiful, but it works- also it's obviously an order of
magnitude faster than fsyncing with CF. Unfortunately, a lot of data
ends up 3 times in RAM:
1) fs cache of db on CF
2) ram fs
3) embedded application

If someone has a better way to avoid CF writes, I'm all ears. Note
that any recent CF will do wear-leveling and give you more than
100,000 (more like a million) writes per sector and they are
constantly improving, so this may all be moot soon- or so I hope. For
this type of application, I wish postgresql offered global temp tables.

čau.

-M