I'm about to reformat my computer and would like to backup my postgresql db before I reformat.

I've tried going into pgadmin iii to backup the database using the utility inside it but the resulting backup database is only 300megs or so in size when the database is actually 2gigs+ in size.

thanks in advance.

My first attempt at using backup/restore thru pgadmin didnt work, but I most likely did it wrong.

Before you do anything make sure you export all your HH from HM to text file. Then you can archive those (in a RAR or ZIP for smaller file size) and put them on removable media. If your backup/restore fails, you will still be able to reimport them to rebuild.

Check these 2 threads, for some good info by _dave_. I believe he gives instructions how to do it, in one of those 2 threads.

http://forumserver.twoplustwo.com/sh...d.php?t=204183

http://forumserver.twoplustwo.com/sh...d.php?t=269850

also make sure that the version of postgresql that you put on the reformatted PC is the exact same as the one you were using. Then I do believe you can just let pgadmin do the back-up.

Whatever you try to do thru postgresql, never fail to also export your hands as well.

Is there a new, easier way to back up a HEM database now?

i simply backup my hands history....

Here is pretty much everything you need to know about different ways to backup HM and SQL.



I want to re-install Holdem Manger without losing all of my settings and preferences. Can this be done?


How do I re-install PostgreSQL and use a previous database?


How do I backup and restore my database?


How do I export or import hands into and from Holdem Manager?


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've almost never been successful with backup/restore. I highly suggest you take the advice fozzy gave and export your hands.

I have used SQL for 2 or 3 years, and I reformat my pc every 4 - 6 months. I have always reimported, until I took the job with HM. Since then I have used the backup restore process, but that is definitely not 100% sure to work. I found the process of simply installing SQL again, over top of my old \data folder, to be much faster and more reliable, as well as much less taxing on the PC. I still export my hands from HM a couple times a month, and run a dual-sync backup as well (of both my HM folder, exports, archives, SQL DB, and personal data).

I've actually always been sucessful with backup/restore. My backups range around 400mb, but real database size is several GB

I'm about to try moving my HEM db from one cpu to the next. My existing cpu is old and the operating drive is small and full (37g raptor, about 3 gigs free now).

The problem: It looks like when I export the hands, they go into a folder on the C drive by default. Well, I have 1,000,000+ hands in my DB and I'm not sure how much space that's going to take up. More than 3 gigs, probably?

Is there a way to change the default folder that HEM exports hands to?

No you can't change the path. My DB is ~250k hands, and exports to ~600MB. Yours would probably take a big majority of the 3GB you have left.


You can do a backup which compresses the data - http://www.holdemmanager.net/faq/afm....aspx?faqid=12

Then install the same version of SQL to the new machine and do a restore.

or/also

Copy the \PostgreSQL\8.3\Data folder to an external HD and then you can reinstall SQL and try to use your existing DB - http://www.holdemmanager.net/faq/afm...aspx?faqid=177


What else is on the HD? I bet you can save a ton of space, and get some life out of the HD by turning off the PostgreSQL logging, and deleting the logs. It is left on by default and can waste a lot of space. I did a remote support the other day for a customer with a 16GB DB, and 14GB of it was 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


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.

[php]------------------------------------------------------------------------------
# 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[/php]

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.

Very helpful, thanks. I eventually found some stuff to delete and compressed some old files, and got 11g free. Did the export and it only took up 1gig. wat.

So now I'm trying to make postgreSQL all nice and perfect before proceeding with the import on the other machine, but I can't figure out how to uncheck "hide extensions" in Vista. So when I edited the file in notepad and went to save, it gave me some strange error message.

Well, I figured out how to make the file save, finally, but now it appears to be named postgresql.conf.conf and I can't seem to make it have just one .conf

wtf.

Oh man. I had HEM up and running before I tried to make these mods. Now it won't open. Can't connect to the DB.

I tried reinstalling postgresql, which made it so i had to re-reg HEM, but it still won't connect. OMFG.

Sorry about the blog, but I appear to have resolved this issue. For now!

thats what I use:

Backup:

Quote:
get to the command prompt and go to the c:\program files\postgresql\8.x\bin folder and run pg_dumpall -U postgres > c:\dbbackup.sql

This may take a while but the end result will be that your entire postgres database will be backed up to a file. If you want it saved to a different folder just change that c:\dbbackup.sql to something more fitting.
Restore:

Quote:
on the new postgres folder get to the same folder in cmd and type

psql -U postgres -f c:\dbbackup.sql postgres (or change the filename to whatever makes sense)

the backup file of my database is 66Gb (I have a big database) but after I use winrar it comes down to a more manageable 5Gb

I bet a huge percentage of your DB size is unnecessary log files which cane be turned off and deleted.



Logging:

I would suggest you turn off logging on your SQL installation as well as these other performance improvements - 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.

[php]------------------------------------------------------------------------------
# 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[/php]

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.