postegreSQL data/ folder 125GB ??!

[jenkulle]

I figure out today that the postegreSQL/8.3/data folder is 125GB... that take so much space I was wondering why and if I need to keep all the files

folder base is 95GB
pg_log is 30GB


I'm using only one database right now, all the others are from previous years but I still need them for taxes purpose


thanks for your help and keep up the great work on HEM

[fozzy71]

Logging:

I would suggest you turn off logging on your SQL installation as well as these other performance improvements in my signature - Top 10: HEM performance increase tips


To turn off logging, follow these instructions. This is the first thing I do anytime I install SQL for someone.

- Start > My Computer > Tools > Folder Options > View > UnCheck 'Hide Extensions for known file types' > OK

- C:\Program Files\PostgreSQL\data\postgresql.conf > Right-Click > Open With.. > NotePad > Check 'Always use this program'

- Scroll about half way down to ~Lines 245 - 260:

- The last line of this block of code, for the 'logging_collector', must be changed from On to Off.



------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = off # Enable capturing of stderr and csvlog


File > Save; File > Close

Start > Programs > PostgreSQL 8.3 > Reload Configuration.


- Start > My Computer > C:\Program Files\PostgreSQL\data\pg_log, or C:\Program Files (x86)\PostgreSQL\data\pg_log, if you have 64 bit Vista. Delete all files in that folder. And it might be a good idea to empty your recycle bin at that point, or use a 3rd party utility like CCleaner to clean up all your junk/temporary files. You want to do this before you get to the vacuum and defrag process.



Regarding database maintenance:

You should regularly purge mined hands in your database, after purge vacuum / full analyze the database via pgadmin.
http://faq.holdemmanager.com/questions/100/Purge+Hands+from+Database
http://faq.holdemmanager.com/questions/197/Purge+Hands

Then you can vacuum/analyze - http://faq.holdemmanager.com/questions/88/Vacuum+Analyze+Database+#manual

Before you defragment you should stop the PostgreSQL service:

Start > Programs > PostgreSQL 8.x > Stop Server

Use your favorite defrag tool. I recommend Smart Defrag.

After defrag I recommend you reboot your PC. If you need to access the DB before rebooting you can start the PostgreSQL service.

Start > Programs > PostgreSQL 8.x > Start Server

[jenkulle]

thank you so much,

just deleted all the pg_log files, what about the 'base' folder ?

I'm looking into the rest of things to do, thanks again for your quick answer

[jenkulle]

thank you so much,

just deleted all the pg_log files, what about the 'base' folder ?

I'm looking into the rest of things to do, thanks again for your quick answer

ok I guess that after purging most database that I have, size of the 'base' folder will get smaller ... :rolleyes:

[fozzy71]

Yes. The only files you can safely delete are the \pg_log files. Once you turn off the logging collector and reload the configuration it won't right more logs. A purge, vacuum, defrag will reclaim some space, yes.