On Mon, 1 Dec 2014 01:34:14 +0100 "Powrie, William" <wpowrie@xxxxxxxxxxxxxxxx> wrote: > Hello, > > I have a simple database with just a few tables that runs on an > embedded Linux system 2.6.31.8. The OS including postresql 8.4 is > loaded directly from cf-flash media and is not saved in any way > across power recycles. It is always created at startup from > the /inittab/rc script and nearly all the elements are inserted at > this point. The database job is to service remote access requests > via lighttpd from a web browser and provide an up to date account of > the monitored equipment. The database is used to store hardware > parameter values that are frequency polled via a number of Linux > daemons. > > In normal operation there are no inserts but frequent updates and > reads. Reliability is of upmost importance since each system is > unmanaged but is remotely monitored. There are a number of systems > currently deployed worldwide. > > Postgresql itself runs from a 56Meg ramdisk so disk space is limited. > This is where the problem is. > > The files in pg_clog increase on a day to day basis until the ramdisk > reaches 100% utilization. This takes roughly 30 days to occur and > postgresql fails at this point. Is PostgreSQL 8.4 updated to last version? I think it was 8.4.22. pg_clog stores information about current commit status for open/active transactions. Do you close all transactions after they're work is done? Do you have any in "IDLE in transaction" state? > The software runs vacuumdb from cron every day at a predefined time > but this does not appear to do anything. I have increased it to run > more frequently but this does not have the desired effect. Performing > a full vacuum is not possible since I cannot easily get database > exclusive access for which a full vacuum appears to need. Vacuum won't work because pg_clog stores data for open/active transactions, as they are alive (have a XID) that information must be available for new transactions. > I have tried modifying the following vacuum parameters > > vacuum_freeze_table_age > vacuum_freeze_min_age > > with no effect. > > If I run a vacuumdb analyse in verbose mode, this is an extract of > what I see: > > INFO: vacuuming "public.mib_snmpinteger" > INFO: index "mib_snmpinteger_element_id_key" now contains 2880 row > versions in 23 pages DETAIL: 0 index row versions were removed. > 0 index pages have been deleted, 0 are currently reusable. > > I never see any index pages being returned to the operating system > which is the problem > > Does anyone know how I can reclaim the every growing ramdisk space? It may be a bug corrected in newer 8.4 versions, update. A fast hack will be close the application connected to PostgreSQL, it will close the transactions. Better, rewrite your app to open/close transactions, using "BEGIN/END". > Regards, As final note, why don't use Sqlite3 for embedded instead PostgreSQL? > > William Powrie --- --- Eduardo Morras <emorrasg@xxxxxxxx> -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
