PDA

View Full Version : postgreSQL 8.3 taking a lot of my hard drive space



majorcrisp
09-18-2009, 08:14 AM
Hi support,

postgreSQL 8.3 is taking up a lot of space (23.3GB) on my hard drive so I have a question to ask relating to this.

Is there a way to reduce the size of this folder?
If so, how?

fozzy71
09-18-2009, 09:01 AM
First thing to do is turn off logging and delete all the unnecessary log files.



Logging:

I would suggest you turn off logging on your new SQL installation as well as these other performance improvements - Top 10: HEM performance increase tips
(http://208.109.95.123/forum/showthread.php?p=62613&posted=1#post62613)

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.

*edit:
- 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.



After that, I would next do a full/vacuum/analyze and reindex. Then defrag my whole PC. Make sure you stop the postgresql service before you defrag, so it can properly defrag your DB.

http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=163


I would suggest you also do the majority of the things in this thread: http://www.holdemmanager.net/forum/showthread.php?t=11194

Some of the most important things, imo:

- Remove the WinnerName, WinnerCards and WinnerWon from the Hands View Column Selector
- Turning off logging and deleting existing logs
- Vacuum/Full/Analyze, then Reindex - http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=163
- SQL Tuning Wizard - see below*
- Defrag last and make sure you stop the postgresql service before you defrag. I personally use Defraggler.
- Do a Cold Boot (Shut Down > Start) before you use HM again

*SQL Tuning:

1)Go to Start > Programs > PostgreSQL 8.3 and look for Application Stack Builder.

Steps 2 - 7 are obsolete and unnecessary.

8) At the end of the installation, launch the Application Stack Builder.
9) Select your PostgreSQL Database Server and click Next.
10) Expand AddOns and select EnterpriseDB Tuning Wizard.
11) Download the tuning wizard by selecting your country and continuing.
12) Continue through the end of installation and launch the EnterpriseDB Tuning Wizard.
13) Select your PostgreSQL Database server and click Next.
14) Under Server Utilization, select Mixed and click Next.
15) Continue through the installation of the tuning wizard.

PostgreSQL memory usage has now been configured specifically for your computer's specifications and performance should be improved.



Also explained in those instructions linked is a process of backup/restore to help compact the DB even more.

majorcrisp
09-18-2009, 10:18 AM
Thanks for the quick reply,
Just to get me started before I follow the rest of the advice, can I delete the content of the following folders without causing ay issues with HEM?

PostgreSQL > 8.3 > data > base
PostgreSQL > 8.3 > data >pg_log

Are these the 'unnecessary log files' you mention? If not which files are unneccesary?

Now I have another problem, I don't remember my postgreSQL password so I can't do a vacuum, is there a way of finding this?

majorcrisp
09-18-2009, 12:43 PM
BUMP! for a reply to my latest question.

fozzy71
09-18-2009, 02:06 PM
Thanks for the quick reply,
Just to get me started before I follow the rest of the advice, can I delete the content of the following folders without causing ay issues with HEM?

PostgreSQL > 8.3 > data > base

NOOOOOOOOOOOO

That is your Database.


PostgreSQL > 8.3 > data >pg_log


Are these the 'unnecessary log files' you mention? If not which files are unneccesary?


Yes



Now I have another problem, I don't remember my postgreSQL password so I can't do a vacuum, is there a way of finding this?


PT3's default name/password is - postgres/dbpass
HM's default name/password is - postgres/postgrespass

svcpass is another possibility


Go to your command function (for Vista users, type cmd in search) and type net user. This will show you which user groups are on your pc.

Net user will tell you what the user is on your machine.

If you still can't get connected thru HM you can either try to remove the password requirement entirely.


Start > Programs > PostgreSQL 8.3 > Configuration Files > Edit pg_hba.conf

The file should open with Notepad

Scroll to the bottom where you will see this code




# TYPE DATABASE USER CIDR-ADDRESS METHOD

# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 md5


You need to change the first 'md5' to 'trust', like this




# TYPE DATABASE USER CIDR-ADDRESS METHOD

# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
#host all all ::1/128 md5



Save, and close the file.

Click: Start > Programs > PostgreSQL 8.3 > Reload configuration



BUMP! for a reply to my latest question.

http://i207.photobucket.com/albums/bb176/fozzy71/Smileys/dry.gif

Veteran68
09-18-2009, 02:26 PM
- Defrag last and make sure you stop the postgresql service before you defrag. I personally use Defraggler.
Defraggler is a great little tool, but its real claim to fame is the ability to defrag specific files and folders. It'll do a drive-level defrag, but for that I'd recommend something else, especially if you want your drive to stay defragged automatically.

You can buy PerfectDisk and DiskKeeper and the like which will do scheduled and idle background defrags, but for awhile now I've used and can highly recommend IOBit's free Smart Defrag (http://www.iobit.com/iobitsmartdefrag.html). It can be set to run in the background and use your PC's idle time to silently defrag when you aren't using the PC. I also schedule it to do deep defrags every so often. It's completely free, but supported by optional adware which you can disable during the install (watch out for the Yahoo Toolbar prompt and just uncheck those boxes). I've used both DiskKeeper and PerfectDisk in the past and find SmartDefrag to be just as effective at basic defrag tasks.

fozzy71
09-18-2009, 02:42 PM
Thanks for the suggestion. I too used perfect disk for a long time, and really liked their boot-time defrag option. ;)