PDA

View Full Version : PostgresSQL too big



MellowYellow
09-29-2011, 12:40 PM
Hey I know this isn't your product, but I believe I'm only using it for HEM, anyway I installed a solid state HDD on my computer a while ago so I dont have a ton of storage, and I noticed that the Postrgres directory in Program filesx86 is now over 50gb. I was wondering if there was anything I could do to shrink the size or delete certain files so it's not taking up so much of my storage.

Thanks.

olliepower
09-29-2011, 10:05 PM
Logging:

I would suggest you turn off logging on your new 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. You do not want to purge any Hero hands so make sure your alias is properly configured before purging.

There are two types of purges (delete)

-options--> settings--> purge hands (will delete handhistory, hand from graph, $Won from sessions, etc. everything, so hand won't be used anymore for stats)
-options--> settings--> observed HH configuration--> purge hands--> will delete the handhistory and therefore the ability to replay the hand, but the hand will still be used for the graph, $Won from sessions and for the HUD.

If you're deleting a lot of hands, it's usually best to just create a new empty database.


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

MellowYellow
10-01-2011, 03:13 AM
Hi thanks for the info, I had a few problems though, when i opened the file in notepad to edit it, it already had the logging = off. Also this is postgres 8.4 if that matters. Then when i went to the directory u mentioned to delete files there was only 1 txt file that was 1k all the 50 gigs i found were in the postrgres/data/base directory. Can i delete any/all of these files?

Thanks again

Sarek
10-01-2011, 04:06 AM
Don't touch any file in Base folder!
This actually IS database, if you delete something there - you will lose DB.

MellowYellow
10-01-2011, 01:43 PM
damn.. so I'm stuck with 50 gigs of stuff that I can't get rid of? I mean at some point this is going to encompass my entire HDD. I actually made a new DB a while back and only have maybe 400k hands in it, but the old database is still on the comp i just dont use it. Could I maybe move that one to a different hard drive somehow to shrink this a bit?

Patvs
10-01-2011, 08:31 PM
Read: http://faq.holdemmanager.com/questions/368/MoveDatabaseToAnotherHardDriveOrPartition

MellowYellow
10-04-2011, 01:38 AM
Ok, well I've made a few new databases over the years and am not using all the hands i have of myself since ive switched games and what not, so could I maybe delete everything in the base folder, delete all databases in HEM, THEN reimport the hands that i WANT used and then it will create a new db? So i donthave to keep these big files for databases I will never again use?

Sarek
10-04-2011, 04:36 AM
You can renounce of old databases. But do this correctly:
1) Create new DB via Holdem Manager.
2) Import what you want in it from c:/hmarchive
2a) Do NOT use autoimport for this, press Import from folder

3) When you wll be satisfied with new DB,
run Start - all programs - postgresql - pgadminIII
double-click on server,
enter password when asked (the same as connecting to database in HM)
select your OLD database, right-click - delete