Re: PostgreSQL and embedded PC with Compact Flash? - Mailing list pgsql-general

From A.M.
Subject Re: PostgreSQL and embedded PC with Compact Flash?
Date
Msg-id 272CA5EE-D75B-4154-812C-FB135CE9C40E@themactionfaction.com
Whole thread Raw
In response to PostgreSQL and embedded PC with Compact Flash?  (k.novo <k.novo@seznam.cz>)
List pgsql-general
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

pgsql-general by date:

Previous
From: "Jaime Casanova"
Date:
Subject: Re: SELECT INTO TEMPORARY problem
Next
From: Neal Clark
Date:
Subject: Re: Can't use passwords for users