PDA

View Full Version : Best way to get only some hands from current DB to a fresh new one?



ffrllc
07-26-2009, 05:56 PM
I’m in the process of doing some DB maintenance. My current DB is too large so I’m looking to create a new fresh one with only some of the hands I have on my current DB. I’ve also been running 8.2 postgreSQL so I’ve taken this time to upgrade to 8.4 as well.

I exported all hands from the DB I’ve been using from Options < Database Management < Database Control Panel < Export Hands. I also did a DB backup from pgAdmin III. So I have all my exported hands, but the data is too massive to filter through manually.

So I upgraded to postgreSQL 8.4 and restored my old DB. I now need to figure out the best way to export only the hands I want so I’ll be able to re-import only those hands into the new clean DB I will create.

I’d like to keep all the hands in my DB 4 months old or newer and all the hands I have personally ever played.

So, what is the best way to filter only these hands to export?

fozzy71
07-26-2009, 06:35 PM
Unfortunately, for that big of a DB, there isn't yet a filtered export that will do what you want exactly.

What you need to do is to purge the observed hands - http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=107

and/or

delete any hands that weren't played by any of your screen names (alias) - http://www.holdemmanager.net/faq/afmviewfaq.aspx?faqid=21

Your # of players in the DB according to HM wont change yet, but the size of the DB and total number of hands should go down.

You can run an SQL query before and after for total DB hand count to see the difference:


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.


Then export the DB to text files again. The number of exported files will not change, but many of them will have massive holes, where the hands were deleted. Now you can create a new DB, and can also set your observed hand history configuration option to limit the observed hands that get imported if any snuck thru the previous steps. Once you have imported the previously exported hands you should have noticeably less players. I went from 71k to ~55k total players when i did this to my previous DB. I havent even finished importing all my archives to my current DB, cuz I have so many archives with duplicates. :-\


I would also suggest that you turn off logging in SQL before you do all this, and also do the Enterprise Tuning Wizard so your SQL will run better on your PC.

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.


Now you can delete your old DB and do a full round of maintenance. Vac/Analyze, Reindex, Defrag.



SQL Performance:

http://www.holdemmanager.net/faq/afmmain.aspx?faqid=172

http://64.77.69.66/forum/showthread.php?t=11194