PDA

View Full Version : Systemdisk getting full



He_simply
03-05-2010, 06:13 AM
Hello

Ive played alot of hands lately and the disk is getting full, I ave only 180 Mb space left right now :-( What can I do? My systemdisk is about to hang soon... Vacum didnt help... Is it possible to tell Postgree to store files in another drive?

fozzy71
03-05-2010, 11:52 AM
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.
FAQ - Hold'em Manager Poker Tracking Software :: Purge Hands from Database (http://faq.holdemmanager.com/questions/100/Purge+Hands+from+Database)
FAQ - Hold'em Manager Poker Tracking Software :: Purge Hands (http://faq.holdemmanager.com/questions/197/Purge+Hands)

Then you can vacuum/analyze - FAQ - Hold'em Manager Poker Tracking Software :: Vacuum Analyze Database (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




If you want to try and move your DB to another drive:


If you have PostgreSQL installed on the C drive where Windows is installed, but you want to move the Data folder i.e Database to a different partition or external hard drive to reclaim the space on your C drive, it can be done without having to reinstall.

We need to close down the PostgreSQL service while doing this.

Windows XP
Start > Control Panel > Administrative Tools (Use classic View) > Services

Windows XP
Start > Search > Services

This will bring up the following window:

http://208.109.95.123/faq/files/Image/postgreServices.jpg

Right click this and choose Stop to stop the service.

Then create the following folders in the external hard drive. Program Files and inside that folder create a PostgreSQL folder and inside that create a data folder. So if your external hard drive is on the I drive you would have created a path I:\Program Files\PostgreSQL\data

Then go to where you have the data folder which will be c:\program files\PostgreSQL\8.3\data by default unless you changed it. Copy (or Cut) all the files in here to the new data folder we just created above on the external hard drive. If this is a big database this may take some time.

When you've done that we need to tell PostgreSQL where the new data folder is located and to this we need to edit the registry.

Windows XP
Go to Start > Run and type the following in the textbox: regedit and then press enter and a new window should appear like in the screenshot below:

Windows Vista/W7
Go to Start > All Programs > Accessories > Run then type the following in the textbox: regedit and then press enter and a new window should appear like in the screenshot below:

http://208.109.95.123/faq/files/Image/2PCpostgresRegedit.jpg

Navigate the folders to this path: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\pgsql-8.3 and you should then see the following screen:

http://208.109.95.123/faq/files/Image/2PCpostgresRegedit2.jpg

In the image above ive highlighted the key we need to change. Go to image path and right click and choose modify and you should see the following:

"C:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N "pgsql-8.3" -D "C:\Program Files\PostgreSQL\8.3\data\"

Now we need to only change the bit thats in bold. This is the location that PostgreSQL looks for the database files but weve moved them to the external hard drive so we must tell it the location. Make sure you get the path exactly correct. The easiest way is to navigate to the actual Data folder on the external hard drive and right click any file inside that folder and choose properties and youll see this:

http://208.109.95.123/faq/files/Image/2PCpostgres6.jpg

As you can see for location it shows the actual path, in my case its the I drive but yours maybe different so just copy and paste it. So if i was creating the key based on that screenshot my old key would be:

"C:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N "pgsql-8.3" -D "C:\Program Files\PostgreSQL\8.3\data\"

And i should change it to this:
"C:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N "pgsql-8.3" -D "I:\Program Files\PostgreSQL\8.3\data\"

That now has everything in place so we should close everything down and make sure to reboot the PC.


If the moved DB is working fine you can delete the original \data folder on the C drive, if you didn't use the Cut option earlier, to free up the space on your C drive.

He_simply
03-05-2010, 02:41 PM
VERY good answer and VERY good service!!! Thanx alot , it fixed all my problems and everything works perfectly.
Ill now recommend this program to all my poker friends:)

5204salty
05-14-2010, 08:17 PM
I had the same issue and did what's noted above. I rebooted and when I launched HEM i got "the following error occurred when trying to open the database: failed to establish a connection to 127.0.0.1"

Any ideas?

5204salty
05-14-2010, 08:25 PM
As a follow-up, I went to Admin Tools>Services to see if PostgreSQL was started - it was not. And when I tried to manually start it, it would not start.

I got a message "Could not start the Postgres 8.3.9 on local computer. Error 1053:The service did not respond to the start or control request in a timely fashion."

Also, under Log On As column, instead of "local system" it says .\postgres

Startup type is still showing as automatic

5204salty
05-14-2010, 09:35 PM
I went back to regedit and switched back to my old drive got HEM running again off my almost full c drive. All I have changed was switching from c:\....... to d:\..........

Any ideas why on my partitioned d drive I can't get it running?

netsrak
05-15-2010, 12:14 PM
Seems like Fozzies solution is not working for you.

Make a backup of your database via pgadminIII and export all your hands as 2nd level backup.

1. Uninstall postgresql, download the installer from PostgreSQL: The world's most advanced open source database (http://www.postgresql.org) and install postgresql on d:

After completion create a new empty database via pgadminIII and restore the backup into this database.

or

2. Copy the postgresql data folder to your d: partition. Uninstall postgresql and reinstall it on D: with using the existing cluster: FAQ - Hold'em Manager Poker Tracking Software :: Reinstall PostgreSQL Preserving Old Database (http://faq.holdemmanager.com/questions/71/Reinstall+PostgreSQL+Preserving+Old+Database)