the fastest way to save 120g db before install my vista

[Jun]

hi,

i have my holdem manager db about 120g which is the size of the directory of postgres directory on my vista desktop.

i need to do a reinstall of the vista system. so what is the fastest way, not too hard, to make this happen?

i am trying to backup the database which looked pretty slow to me. it looks like it's not going be done overnight. i used the option of "compress" which might make this too slow but is option "plain" is going to make this fast enough?

i guess the most intuitive way to do this is to copy all of the related directories and files from my current disk to a safe location and then copy them back after i wipe out the system and a reinstall. but is it going to work and what are the directories and files i need to save？

i am trying to find a solution that can be done in the time frame of one day.

thanks

-jun

[netsrak]

If you are a database expert the copy option will work (but without guarantee). It needs some knowledge of the installation procedure. See: http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=177

The safer way is to do a backup. You need to use the compress option.
http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=12

A 120 GB database needs its time for a backup. I don't think you will make it in one day.

[fozzy71]

120gb is huge, but I have a feeling a lot of that space is being wasted by log files, and it would probably help to speed up the backup/restore process (http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=177), if those files were deleted first. You can see how much space is being taken by log files by: 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. Right-Click > Properties on the \pg_log file to get the size used on disk.

To turn off logging and delete the files, 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 Off to On.


------------------------------------------------------------------------------
# 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 (http://www.ccleaner.com) to clean up all your junk/temporary files. You want to do this before you get to the vacuum and defrag process.


************************************************** *****************

I want to re-install Holdem Manger without losing all of my settings and preferences. Can this be done? (http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=98)

You want to make sure you backup your Processed/Archived hand histories, your entire \config folder, and your exportednotes file. I usually just copy my entire \Holdem Manager directory.

I would also make sure you manually export the database to hand history text files:

HM > Options > Database Management > Connect > Export Hands

Make sure you copy that folder as well as the other stuff I mentioned.

This helps to insure that if anything goes wrong with the backup/restore, you can manually import the exported files to rebuild.


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)

[Jun]

magically, my backup finished in 3 hours with the resulting file around 9g. i guess i had more databases on my machine with dabases from programs like poker tracker 3. i checked the total hands of the holdem manager db. there are around 27 million hands which means the db should be 27g. that gives the compression ratio of 27:9=3:1. does that look right to you. i just want to make i don't lose this db.

to turn off the log, should i set up the flag to off from on? from what i have seen for my machine, the size of the logging is about 10-20% of db size. which does not look too bad to me.

except backinng up the db, what files and directories should i backup for holdem manager and postgres sql? i mean like setting up files, notes, etc. i know i probabaly should not ask this in this forum but what about the settings of stars and fulltilt clients?

thx

-jun

[fozzy71]

magically, my backup finished in 3 hours with the resulting file around 9g. i guess i had more databases on my machine with dabases from programs like poker tracker 3. i checked the total hands of the holdem manager db. there are around 27 million hands which means the db should be 27g. that gives the compression ratio of 27:9=3:1. does that look right to you. i just want to make i don't lose this db.

Well hopefully you have saved all of your original/archived hand history files. I also suggest you always export your DB to text files, in case the restore process isn't effective.

The backup/restore process is known for compressing the DB size. - http://www.linuxinsight.com/optimize_postgresql_database_size.html

That link was taken from this thread - Top 10: HEM performance increase tips (http://208.109.95.123/forum/showthread.php?p=62613&posted=1#post62613)



You can verify the number of total hands in your DB with a PostgreSQL Query:

Open pgAdmin, doubleclick the "localhost server" so the red X goes away. Select your HM database in the treeview.

Do "Tools -> Query Tool" (or click the "SQL" icon in the toolbar).

enter this in the top section of the query window:


SELECT COUNT(pokerhand_id) as hands FROM handhistories;

Then do "Query -> Execute" (or click the "play" icon, or hit F5).
It will grind the disk for a little then spit out a number in the lower pane of the query tool if all has gone well.




to turn off the log, should i set up the flag to off from on? from what i have seen for my machine, the size of the logging is about 10-20% of db size. which does not look too bad to me.

You want it set to Off. It is set to On by default.


except backinng up the db, what files and directories should i backup for holdem manager and postgres sql? i mean like setting up files, notes, etc. i know i probabaly should not ask this in this forum but what about the settings of stars and fulltilt clients?

thx

-jun

I backup my entire Holdem Manager folder and my Stars/FTP folders, just to be sure I don't miss anything.

For HM:

I want to re-install Holdem Manger without losing all of my settings and preferences. Can this be done? (http://www.holdemmanager.net/faq/?f=98) The Config folder contains all of your HUD settings and config's, as well as your general HM config file settings. I also like to export my HUD configs as well, to be safe.

That FAQ is in need of updating. You should also:

HUD Options > Player Preferences > Export Config

Options > Notes > Export Notes > Copy/backup the exported notes file

You also want to copy your Archived/Processed Hand History folder. Typically C:\HMArchive, but can be verified via the Configure Auto Import Folder > Edit.

I also export/archive the database to text: Options > Database Management > Connect > Export, and ZIP/Copy the exported files.


For FTP Stars, the important files are:

- FTP: {username}.xml

- Stars: user.ini and notes.txt

[Jun]

i noticed that postgres8.4 is just out. should i use 8.3 or 8.4 instead for holdemm manager?

-jun

[fozzy71]

8.3

You should use the one linked in our SQL Install FAQ - http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=176

Or this one-click installer version - http://www.postgresql.org/download/windows

Here is a thread about the 8.4 version of SQL and HM - http://208.109.95.123/forum/showthread.php?t=12925

[Jun]

hi,

i forget to turn off the vista auto update and system rebooted while pgadmin is restoring my database. what should i do now? continue restoring or should i start off again?

also, the backup file is only 8g but the resulting db is over 90g and it's still growing. is this normail? i did turn off the logging before i do the restore but i did not turn off the logging and delete log files before i do the backup.

thx

-jun

[fozzy71]

I would start over with the restore process.

The backup file is heavily compressed. My DB was over 2GB before I ran the Purge Hands option and exported (my logging was already off). The DB was then about 1.6GB. The backup file created from it was only ~125 MB.

I would really recommend you delete all the log files before making the backup file, so it doesn't have to restore all those log files in addition to the data files. Also may be a good idea to purge some old observed hands from the previous DB before exporting your hand histories and creating another backup file to use in the restore process. http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=107

The backup/restore process is known to reduce the DB size, even if you haven't purged and deleted log files. http://www.linuxinsight.com/optimize_postgresql_database_size.html

That link was taken from this very helpful thread about SQL performance. - http://208.109.95.123/forum/showthread.php?p=62613&posted=1#post62613

[Jun]

thx for the prompt reply.

i will take ur advice to start off with my system restore.

my system is already formatted and i only have a backup file with me. so the question is that will the loggs be gone after i restore from this backup. i remember that the log directory sized 30g on my computer.

the thing really surprised me is that my backup was only 3 hours but so far the restore has been over 10 hours and still going. any idea?

-jun

[fozzy71]

30GB is a lot of logs. I would expect the restore process will restore the log files as well, but I can't say for certain, because I never have logs in my DB if I can avoid it.

I had never used the backup/restore process before the other day. I had always imported my archives, because my DB is <300k hands. I did a backup/restore the other day to educate myself about the process. It didn't take very long for the backup, perhaps 10 or 15 minutes, I believe. The first time I tried restoring it I thought my PC had locked up so checked my Task Manager it appeared as not responding (but I think it was actually working). I ended the process and flubbed up the first DB restore. I tried it again, to a new DB, and left my PC completely alone. I think it took somewhere between 30 and 45 minutes to do the restore.

The other thing I had always liked about importing my archives, was the fact that I could separate them up into smaller chunks to import. That way my PC wasn't busy for many hours importing or restoring.

[Jun]

well, i don't know what should i do.

after more than 20 hours wait, this is what i got. the db has taken 105g space on my disk.

anything else should i do to try to fix the restore error?

could it be the version problem? i am using hm1.0804 and postgres8.3 which is a little newer than my old config, hm1.0802 maybe and postgres8.2.

-thx

-jun

[fozzy71]

Yes. If your old DB that you did the backup to was SQL 8.2 you are going to have to install 8.2 to do the restore. The major version number has to be the same. I don't think the minor version needs to be the same, but I can't be certain.

This is why having copies of original and exported hand history files is so important. :(

Hopefully one of these versions will work for you:

http://www.postgresql.org/ftp/binary/

v8.2.11
v8.2.12
v8.2.13

[Jun]

surprisingly, after i got curisous and tried to open the db with hm, it worked so far.

does that means the db was actually restored succefully? i have never used backup/restore myself and i don't know what should i expect after i run pg_restore anyway.

because i only have one huge db for hm, i would like to ask the expert because i go ahead and use it.

thx

-jun

[Jun]

guess it was too earlier for me to be happy that the restored db can do normal queries. now this db is not going to import hand histories i bought from hhdealer.

it looks like the import is rejecting files/directories randomly. but if i create a new db, it will take whatever files/directories.

what should i do?

if i export this newly restored db to file, can i do it? but for 27mil hands, how long is the export and import going to take? couple of weeks?

thx

-jun

[netsrak]

Your database seems to be somehow corrupt.

Creating a new database and importing 27Mio hands will indeed last a long time. You should calculate with 20-30 hands/second.

[Jun]

i have to decide which way should i go from here to get my db back. please advice.

the import will take about a week to finish but with guarantee. or i can try to install old versions of hm and pg to try my luck which takes 20hours. what will u do?

is fixing indexes going to do any good to this somewhat corrupted db?

i realize that maintaining one db so huge is indeed not a good choice. i actually did a backup/restore with a smaller sb and same problem happened. i do think the backup/restore can be trusted. would u suggest multiple smaller db instead of one giant db?

-jun

[netsrak]

My suggestions:
1. Start with a new fresh db with the latest postgresql 8.3.x
2. Import the hands from the handhistory files. Start with your hands and import only the mined hands you really need. For me hands older than 2-3 months are useless.
3. In future purge your mined data regularly. The stats for purged hands will remain in the database. Only the hands are deleted.
4. It makes no sense to create multiple database because HM only supports 1 database at at a time.

But i think we should wait for Fozzys thoughts about this too.

[fozzy71]

I would probably start over and import the hands. I have only used the backup/restore process once, on a 250k hand DB, and it seemed to work OK, but it pretty much made my PC unusable while restoring. In the past while doing imports to rebuild, I have always been able to do light multi-tasking while it imports, if necessary.

Regarding import speed, a 2nd dedicated slave drive for your SQL DB would increase the import speed dramatically, as well as your general DB performance. It can operate much smoother and faster when it isn't competing with your OS for read/write cycles.

I would break up your archives into much smaller groups, so your PC isn't importing for a week non-stop. Then you could set it up to import a portion before bed, and still have the option to use your PC the next day, and import another portion later.

You may want to use the Observed Hand History Configuration window to cut down on the number of mined hands that get imported. You can also use the Purge Option from that window later, to purge old mined hands, if too many old ones get imported to the new DB.

[Jun]

guess fozzy is pretty busy.

i will be use a temporary db to keep playing until i restore the old db, hopefully in a week.

should this be enough to keep my db secure??

so from now on, i will have to
-purge hands every month to keep hm db small, vacuum and re-index
-export hands out every month to backup the db
-backup with pgadmin even if i cannnot trusted it :>
-always keep at least all played hands in a safe directory and back it up

as long as i can keep the total hands to below 5mil, the import is going to be done overnight which is not much work if anything happen. i was too lazy to maintain hm db and trusted pgadmin too much for backup. after wasted 10 days i finally learned the lesson.

i am just going to write my story here in case anybody will read it and find it helpful.

it all start from a trouble making optional update for vista which corrupted my system. the main thing is that my high speed usb modem does not work anymore. vista is not going to be able to work with system restore. do i had to do dell factory image recover.

i spend a day to backup the db and all useful files.
another day to patch the vista until sp2
made sure modem is working now and intalled applications
another day to restore db ending up corrupted db
2 hours to export all hands from corrupted db
maybe another week to import all the hands.

-jun

[Jun]

Regarding import speed, a 2nd dedicated slave drive for your SQL DB would increase the import speed dramatically, as well as your general DB performance. It can operate much smoother and faster when it isn't competing with your OS for read/write cycles.



thx for the advice. ur support was great.

do u have experience with installing another drive? i assume it refers to disk raid. or if u can point me to a link who use that improved hm importing?

-jun

[Jun]

...\holdem manager\config was copied but i am not sure if i can overwrite the whole directory on my new install. those files are even have different names. i didn't make that much of setup change with the old oversion. just want to make sure no compatibility issues.

and i have lost my stars notes.txt partially. thanks god i was on trip with laptop and the notes.txt files was copied to it. it was in a different directory now called \loginname\appdata\local\pokerstars for vista. i only copies the whole c:\program files\pokerstars directory and thought i was safe.

-jun

[fozzy71]

I am going to reply to this, but it will take some time. I am going to go answer the easier posts from today that need my attention, and then I willl return with some comments.

[Jun]

thx, fozzy, u r the best

since u will be back to this thread. i would ask this question here. would u recommand me use 1.09beta13 or 1.08.x to restore my db. it's going to take some time.

i tried new stars client. it's kind of painful to have to table finder to make the hud working. it auto works for ftp though.

thx

-jun

[fozzy71]

thx, fozzy, u r the best

since u will be back to this thread. i would ask this question here. would u recommand me use 1.09beta13 or 1.08.x to restore my db. it's going to take some time.

i tried new stars client. it's kind of painful to have to table finder to make the hud working. it auto works for ftp though.

thx

-jun

The version of HM shouldn't really matter, so I don't see any harm in installing the Beta 13 update. The backup/restore is all done thru PGAdmin. Much more important is that you have the same version of PostgreSQL.




guess fozzy is pretty busy.

I will be use a temporary db to keep playing until i restore the old db, hopefully in a week.

Should this be enough to keep my db secure??

So from now on, i will have to
-purge hands every month to keep hm db small, vacuum and re-index
-export hands out every month to backup the db
-backup with pgadmin even if i cannnot trusted it :>
-always keep at least all played hands in a safe directory and back it up

This sounds like a solid plan. The HH text files compress very well using a program like www.7Zip.org . I often have multiple ZIPs in my archive, of different HM Exports, in addition to my current processed HH folder. HM has to go thru all those duplicates obviously when I do my manual imports, but it does so very efficiently. Much faster than it parses the HHs that aren't duplicates.



thx for the advice. Ur support was great.

Do u have experience with installing another drive? I assume it refers to disk raid. Or if u can point me to a link who use that improved hm importing?

-jun

I have minimal experience. I am not very educated about hardware. I hope to be more so this fall when I try to build my new PC with 3 HDs. I do not plan on running a disk raid, but instead want one as my OS (hopefully SSD), one for my SQL DB. I haven't decided if I want to get a smaller SSD for a dedicated SQL drive, or perhaps a VelociRaptor 300GB instead, so I can store my Graphics and other important data there also. The 3rd drive will be for my video and music storage, and for backing up my the 2 main drives. And of course an external for a daily redundant sync backup. Of course this is gonna likely cost me 1k - 1.5k at least. If I happen to find another $500 - $1k laying around, I might get one of these instead of a 1TB external - http://www.drobo.com/

The only time(s) I have added an extra HD was as a slave, and it was an EIDE drive, not a SATA. It is pretty much plug and play, as long as that drive doesn't have an OS on it. I wanted to get a new SATA drive to add to my current PC, but it looks like Gateway used a cheap Motherboard (obviously) and I don't see another SATA connection.



...\holdem manager\config was copied but i am not sure if i can overwrite the whole directory on my new install. Those files are even have different names. I didn't make that much of setup change with the old oversion. Just want to make sure no compatibility issues.

And i have lost my stars notes.txt partially. Thanks god i was on trip with laptop and the notes.txt files was copied to it. It was in a different directory now called \loginname\appdata\local\pokerstars for vista. I only copies the whole c:\program files\pokerstars directory and thought i was safe.

-jun

The FAQ says to back up your entire \config folder out of convenience. I personally backup my entire \Holdem Manager to 2 separate drives every day. I should always have 2 backups on hand.

If you have already set your standard preferences in HM such as import folders, archive folder, hero name, alias, etc, you probably don't need to use your old HoldemManager.config file. If you had lots of custom HUD configs you will want your prefs.xml, pospref.txt, and pospref2.txt. If you exported your old HUD configs to XML you could simply import those instead. The only other things might be custom stats or custom popups. If you didn't do much with that, you may not need any of your old config file.