Storage problem

fmonroy · 01-27-2010, 02:18 PM

Hello guys, I've a problem with an UT box: we have some computers with conficker at our network. While cleaning them, UT has been great stoping packets reaching the internet but it was consuming all the disk.

In a similar problem months ago I was instructed how to resolve it droping the events schema in the UT database. It worked well that time, but at a newer version it just crashed the system and had to recreate tables manually copying schema from another box.

So, now I tried to delete the registers from n_firewall_evt and the UT closed the connection with this message: "PANIC: could not write to file "pg_xlog/xlogtemp.17177": No space left on device"

It's still working (I'm writing this tru it) but when I try to connect to the DB it sends this message: "psql: could not connect to server: No such file or directory" "Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?"

Is there something I can delete to get some space without crashing the box?

dmorris · 01-27-2010, 02:51 PM

try deleting some stuff in /tmp/ or /var/log

some daemons have problems starting if theres no disk.

although you should never really come close to using 80 gigs regardless.
i'd figure out whats taking up the space - is it the database? something in var/log?

dwasserman · 01-27-2010, 03:25 PM

It would be easier to make a backup, install from scratch and restore the backup?

fmonroy · 01-27-2010, 04:09 PM

great dmorris, that gave the box some space to work, but eventually it will grow again, it's the database because the events, at this time I'm getting 700% of the packets being blocked against the ones passing.

Is safe to drop the events schema like in previous versions?

dwasserman, that is the last resource

mrunkel · 01-27-2010, 04:53 PM

psql -u postgres uvm
vacuum verbose full;

let 'er spin and then it should greatly reduce the amount of disk space required.

I wrote up a longer version in the thread "hard disk slowly filling"

dmorris · 01-27-2010, 05:01 PM

i'd first verify that its the db taking up the space by running this:
sudo du -hs /var/lib/postgresql/

if the db is big, run mrunkel's command. it will force clean it up.
for very large sites the auto-cleaning is working fully because a parameter is too low - that is changed in 7.2 - for now running that command should do it.

fmonroy · 01-27-2010, 05:16 PM

66G /var/lib/postgresql/

after running vacuum verbose; because the full keyword generates syntax error

dmorris · 01-27-2010, 05:21 PM

vacuum verbose full

is a syntax error?

what is the output of 'dpkg -l | grep post'

fmonroy · 01-27-2010, 05:23 PM

ii postgresql-8.3 8.3.8-0lenny1
object-relational SQL database, version 8.3
ii postgresql-client-8.3 8.3.8-0lenny1
front-end programs for PostgreSQL 8.3
ii postgresql-client-common 94lenny1
manager for multiple PostgreSQL client versi
ii postgresql-common 94lenny1
PostgreSQL database-cluster manager
ii untangle-postgresql-config 7.1.0~svn20090922r2454
4release7.1-1lenny Untangle Postgres wrapper package

fmonroy · 01-27-2010, 05:38 PM

these are the registers I want to rid of:

uvm=# select count(*) from n_firewall_evt;
count
----------
24257385
(1 row)

01-27-2010, 02:18 PM	#1 (permalink)
fmonroy Untangler Join Date: Jan 2009 Posts: 92	Storage problem Hello guys, I've a problem with an UT box: we have some computers with conficker at our network. While cleaning them, UT has been great stoping packets reaching the internet but it was consuming all the disk. In a similar problem months ago I was instructed how to resolve it droping the events schema in the UT database. It worked well that time, but at a newer version it just crashed the system and had to recreate tables manually copying schema from another box. So, now I tried to delete the registers from n_firewall_evt and the UT closed the connection with this message: "PANIC: could not write to file "pg_xlog/xlogtemp.17177": No space left on device" It's still working (I'm writing this tru it) but when I try to connect to the DB it sends this message: "psql: could not connect to server: No such file or directory" "Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?" Is there something I can delete to get some space without crashing the box?

01-27-2010, 04:53 PM	#5 (permalink)
mrunkel Join Date: Jul 2008 Posts: 1,468	psql -u postgres uvm vacuum verbose full; let 'er spin and then it should greatly reduce the amount of disk space required. I wrote up a longer version in the thread "hard disk slowly filling" __________________ m. Big Frickin Disclaimer: While I'm pretty sure, I can't guarantee that I know what I'm doing. There might be a better way to do this, and this way might actually suck. Make sure you understand the implications of what you're doing before trying to follow these directions. It often helps troubleshooting if you have a good network map. Look here if you want my advice on how to draw one.

01-27-2010, 05:16 PM	#7 (permalink)
fmonroy Untangler Join Date: Jan 2009 Posts: 92	66G /var/lib/postgresql/ after running vacuum verbose; because the full keyword generates syntax error Last edited by fmonroy; 01-27-2010 at 05:19 PM..

01-27-2010, 02:51 PM	#2 (permalink)
dmorris Untangle Junkie Join Date: Nov 2006 Location: San Mateo, CA URLs submitted: 10 Posts: 6,694	try deleting some stuff in /tmp/ or /var/log some daemons have problems starting if theres no disk. although you should never really come close to using 80 gigs regardless. i'd figure out whats taking up the space - is it the database? something in var/log?

01-27-2010, 03:25 PM	#3 (permalink)
dwasserman Join Date: Jun 2008 Location: Argentina URLs submitted: 29 Posts: 2,165	It would be easier to make a backup, install from scratch and restore the backup?

01-27-2010, 04:09 PM	#4 (permalink)
fmonroy Untangler Join Date: Jan 2009 Posts: 92	great dmorris, that gave the box some space to work, but eventually it will grow again, it's the database because the events, at this time I'm getting 700% of the packets being blocked against the ones passing. Is safe to drop the events schema like in previous versions? dwasserman, that is the last resource

01-27-2010, 05:01 PM	#6 (permalink)
dmorris Untangle Junkie Join Date: Nov 2006 Location: San Mateo, CA URLs submitted: 10 Posts: 6,694	i'd first verify that its the db taking up the space by running this: sudo du -hs /var/lib/postgresql/ if the db is big, run mrunkel's command. it will force clean it up. for very large sites the auto-cleaning is working fully because a parameter is too low - that is changed in 7.2 - for now running that command should do it.

01-27-2010, 05:21 PM	#8 (permalink)
dmorris Untangle Junkie Join Date: Nov 2006 Location: San Mateo, CA URLs submitted: 10 Posts: 6,694	vacuum verbose full is a syntax error? what is the output of 'dpkg -l \| grep post'

01-27-2010, 05:23 PM	#9 (permalink)
fmonroy Untangler Join Date: Jan 2009 Posts: 92	ii postgresql-8.3 8.3.8-0lenny1 object-relational SQL database, version 8.3 ii postgresql-client-8.3 8.3.8-0lenny1 front-end programs for PostgreSQL 8.3 ii postgresql-client-common 94lenny1 manager for multiple PostgreSQL client versi ii postgresql-common 94lenny1 PostgreSQL database-cluster manager ii untangle-postgresql-config 7.1.0~svn20090922r2454 4release7.1-1lenny Untangle Postgres wrapper package

01-27-2010, 05:38 PM	#10 (permalink)
fmonroy Untangler Join Date: Jan 2009 Posts: 92	these are the registers I want to rid of: uvm=# select count(*) from n_firewall_evt; count ---------- 24257385 (1 row)