Re: safelly erasing dirs/files

Hello all,

How can I safelly erase (with rm command in Linux) files or dirs
concerning a specific database ?

assuming I whish to elimiante data belonging to database A but I do not
whish to disturb or cause any injury to database B

Is there documentation on how to do this or on what exactly am I erasing
if I remove some specific file or dir.

Thanks

Joao

On Fri, Nov 13, 2009 at 12:15 PM, Joao Ferreira gmail <
joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com> wrote:

> Hello all,
>
> How can I safelly erase (with rm command in Linux) files or dirs
> concerning a specific database ?
>
Using 'rm', you can't -- not safely anyways.

>
> assuming I whish to elimiante data belonging to database A but I do not
> whish to disturb or cause any injury to database B
>

Login to database B and type: 'DROP DATABASE A;' That will drop the whole
database. If you just want to delete certain tables within the database,
then you should login to database A and use: 'DROP TABLE tablename'

>
> Is there documentation on how to do this or on what exactly am I erasing
> if I remove some specific file or dir.
>

http://www.postgresql.org/docs/current/interactive/sql-dropdatabase.html

--Scott

In response to Joao Ferreira gmail :
> Hello all,
>
> How can I safelly erase (with rm command in Linux) files or dirs
> concerning a specific database ?

Don't do that!

>
> assuming I whish to elimiante data belonging to database A but I do not
> whish to disturb or cause any injury to database B

Use regular sql-commands like DROP TABLE or DROP DATABASE.

>
> Is there documentation on how to do this or on what exactly am I erasing
> if I remove some specific file or dir.

Sure:
http://www.postgresql.org/docs/8.4/static/sql-droptable.html
http://www.postgresql.org/docs/8.4/static/sql-dropdatabase.html

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

Joao Ferreira gmail <joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com> writes:
> How can I safelly erase (with rm command in Linux) files or dirs
> concerning a specific database ?

Don't even think about it.

> assuming I whish to elimiante data belonging to database A but I do not
> whish to disturb or cause any injury to database B

DROP DATABASE A;

regards, tom lane

Joao Ferreira gmail <joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com> wrote:
>
> Hello all,
>
> How can I safelly erase (with rm command in Linux) files or dirs
> concerning a specific database ?

What do you mean by this? The typical way would be to log into the
DB server and issue DROP DATABASE x.

However, if you're looking for secure data destruction (akin to rm's -P
flag) I don't know if there is a canned method for doing so.

--
Bill Moran
http://www.potentialtech.com

Joao Ferreira gmail wrote:
> How can I safelly erase (with rm command in Linux) files or dirs
> concerning a specific database ?
>
You can't do it with rm; you need to execute the SQL "drop" command
somehow to clean up all the references to that database. If you want to
remove a database from the command line, there's a wrapper script named
dropdb you can use to do that:

http://www.postgresql.org/docs/current/static/app-dropdb.html

Note that removing a database will require connecting to the server as a
superuser or owner of the database. If you want to do that in a script,
you might need to setup a .pgpass file:
http://www.postgresql.org/docs/current/static/libpq-pgpass.html

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com

ok. thx all for the explanation

my problem is I a heavilly bloated pg database that has just filled up
the partition

data is in /var/pgsql/.. and /var is 100% full...

vacuum/reindex is saying: I can't do it cause I have no space :(

how do I un-bloat the /var partition ? this is why I was considering
rm !!!!

thx
Joao

On Sat, 2009-11-14 at 14:35 -0500, Bill Moran wrote:
> Joao Ferreira gmail <joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com> wrote:
> >
> > Hello all,
> >
> > How can I safelly erase (with rm command in Linux) files or dirs
> > concerning a specific database ?
>
> What do you mean by this? The typical way would be to log into the
> DB server and issue DROP DATABASE x.
>
> However, if you're looking for secure data destruction (akin to rm's -P
> flag) I don't know if there is a canned method for doing so.
>

Bill Moran wrote:
> However, if you're looking for secure data destruction (akin to rm's -P
> flag) I don't know if there is a canned method for doing so.
>

the only really secure data destruction is to put the disk drives in a
chipper and grind them up. don't forget any media that ever held
backups of this data.

Joao Ferreira gmail <joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com> wrote:
> my problem is I a heavilly bloated pg database that has just filled up
> the partition
>
> data is in /var/pgsql/.. and /var is 100% full...
>
> vacuum/reindex is saying: I can't do it cause I have no space :(
>
> how do I un-bloat the /var partition ? this is why I was considering
> rm !!!!

Which version do you have? 8.4? If yes, you can create a new tablespace
on an other partition/disk and alter table set tablespace <your new
tablespace>.

Indexes you can DROP and recreate.

But never ever delete files on os-level!

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Joao Ferreira gmail wrote:
> ok. thx all for the explanation
>
> my problem is I a heavilly bloated pg database that has just filled up
> the partition
>
> data is in /var/pgsql/.. and /var is 100% full...
>
> vacuum/reindex is saying: I can't do it cause I have no space :(
>
> how do I un-bloat the /var partition ? this is why I was considering
> rm !!!!
>

stop postgres. mv /var//pgsql to a new larger filesystem, then ln -s
/new/path/to/pgsql /var/pgsql
then restart postgres.

On Sat, Nov 14, 2009 at 7:42 PM, Joao Ferreira gmail
<joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com> wrote:
> vacuum/reindex is saying: I can't do it cause I have no space :(

Hm, vacuum shouldn't require any extra space. I suppose you need
enough space for the transaction log though. You can probably get away
with a pretty small amount of extra space. Try removing older stuff
from /var/log/ to make some extra space or using tune2fs -m (or tunefs
-m on bsd i think) to lower the reserved percentage then run vacuum.
reindex will require a lot of extra space though.

Better is probably to create a new tablespace on a new filesystem and
do ALTER TABLE SET TABLESPACE and ALTER INDEX SET TABLESPACE to the
new tablespace. Then CLUSTER the table in the new tablespace to
compact it. Cluster needs enough space to hold the old and new table
at the same time but when it's done it'll have compacted both the
table and the indexes better than vacuum does.

--
greg

From the on-line help:

|DROP DATABASE| drops a database. It removes the catalog entries for the
database and deletes the directory containing the data.

Joao Ferreira gmail wrote:
> Hello all,
>
> How can I safelly erase (with rm command in Linux) files or dirs
> concerning a specific database ?
>
> assuming I whish to elimiante data belonging to database A but I do not
> whish to disturb or cause any injury to database B
>
> Is there documentation on how to do this or on what exactly am I erasing
> if I remove some specific file or dir.
>
> Thanks
>
> Joao
>
>
>
>

Joao Ferreira gmail wrote:
> vacuum/reindex is saying: I can't do it cause I have no space :(
> how do I un-bloat the /var partition ? this is why I was considering
> rm !!!!
>
I've gotten out of this situation before by finding other files in /var
to delete, using something like this to figure out where the big stuff
was at:

du -sk | sort -n

And then once a little room was available, using CLUSTER starting on the
smallest tables, identified with a query like the one at
http://wiki.postgresql.org/wiki/Disk_Usage except with different LIMITs
on it. Once some of those are shrunk, you might be able to work your
way up to bigger ones.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com

Greg Smith wrote:
>> how do I un-bloat the /var partition ? this is why I was considering
>> rm !!!!
>>
> I've gotten out of this situation before by finding other files in
> /var to delete, using something like this to figure out where the big
> stuff was at:

while sometimes /var/log or /var/spool data is the problem, far more
often, /var/lib/pgsql/data was the biggun on systems that didn't plan
for this...

my fix has been what I said earlier. in more detail

1) (make available a new storage volume)
2) # mkfs /dev/sd??
3) # mount /dev/sd?? /u01
4) # /etc/init.d/postgresql stop
5) # mv /var/lib/pgsql /u01
7) # ln -s /u01/pgsql /var/lib/pgsql
8) # /etc/init.d/postgresql start

adjust directories and stop/start commands as appropriate for your
distribution.

if you can't come up with a new volume, but have an existing volume with
sufficient space, then...

1) # mkdir /someplace/pgsql
2) # /etc/init.d/postgresql stop
3) # mv /var/lib/pgsql /someplace
4) # ln -s /someplace/pgsql /var/lib/pgsql
5) # /etc/init.d/postgresql start

Personally, I really like having my database data on dedicated RAID10
volumes for best performance.

On 2009-11-14, Joao Ferreira gmail <joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com> wrote:
> ok. thx all for the explanation
>
> my problem is I a heavilly bloated pg database that has just filled up
> the partition
>
> data is in /var/pgsql/.. and /var is 100% full...
>
> vacuum/reindex is saying: I can't do it cause I have no space :(
>
> how do I un-bloat the /var partition ? this is why I was considering
> rm !!!!

If ubuntu or debian /var/cache/apt/archives/* can safely be removed
and will often free up a lot of space, (or on redhat
/var/cache/yum/*/packages/*)

If you installed from source you may have several daily log files you
can remove, redhat seems also to keep the logs around
thse files are often found in /var/lib/pgsql/data/pg_log and should
contain ascii text. Debian and ubuntu put them in /var/log/postgres
and use logrotate to trim them, but there maybe other files in /var/log
that can be truncated or erased to free up space.

On Mon, Nov 16, 2009 at 2:37 AM, Jasen Betts <jasen(at)xnet(dot)co(dot)nz> wrote:
> On 2009-11-14, Joao Ferreira gmail <joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com> wrote:
>> ok. thx all for the explanation
>>
>> my problem is I a heavilly bloated pg database that has just filled up
>> the partition
>>
>> data is in /var/pgsql/.. and /var is 100% full...
>>
>> vacuum/reindex is saying: I can't do it cause I have no space :(
>>
>> how do I un-bloat the /var partition ? this is why I was considering
>> rm !!!!
>
> If ubuntu or debian  /var/cache/apt/archives/* can safely be removed
> and will often free up a lot of space, (or on redhat
> /var/cache/yum/*/packages/*)
>
> If you installed from source you may have several daily log files you
> can remove, redhat seems also to keep the logs around
> thse files are often found in /var/lib/pgsql/data/pg_log and should
> contain ascii text. Debian and ubuntu put them in /var/log/postgres
> and use logrotate to trim them, but there maybe other files in /var/log
> that can be truncated or erased to free up space.

Also note that if you're on ext2/ext3 and your partitions were set up
with some amount of reserved space for root you can free up some space
for a minute by using tune2fs:

sudo tune2fs -m 0 /dev/sda1

then turn it back to reserved when you're done:

sudo tune2fs -m 2 /dev/sda1