Thread: 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.miguel.c.ferreira@gmail.com> wrote:
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'
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.
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.miguel.c.ferreira@gmail.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.miguel.c.ferreira@gmail.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@2ndQuadrant.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.miguel.c.ferreira@gmail.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.miguel.c.ferreira@gmail.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.miguel.c.ferreira@gmail.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:
Joao Ferreira gmail wrote:
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@2ndQuadrant.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.miguel.c.ferreira@gmail.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@xnet.co.nz> wrote: > On 2009-11-14, Joao Ferreira gmail <joao.miguel.c.ferreira@gmail.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