Started off with around 37,000 worth of Stars & Tilt hands manually imported in to a fresh PT db. Made sure this raw data was pretty clean; ok, a few duplicates and a number of "button" errors on the imports, but the integrity seemed pretty good in PT and through the SQL.
pg_dumpall -U postgres > c:\pgbackup
to perform the backup;
wacked postgres and PT completely to simulate a HDD fail.
reinstalled PT and postgres, creating a fresh [empty] db.
psql -U postgres -F c:\pgbackup
to perform the restore;
restart PT, and voila, db back intact; integrity good.
you have to enter the postgres password a few times at the command line; I guess you could always write a script to get over this.
now for some numbers:
37k of hands.
original postgres size around 474MB.
pg_dump size around 253MB and zipped down to 15MB.
so i guess extrapolating this, a million hands or so could zip into around 400MB.pg_dump took like 30 seconds.
psql took around 90 seconds - there were some error prompts restoring - maybe the duplicates, but I looked past these at the overall integrity of the data; and it all looked cool.
this was all done on a standard windows 32 bit vista o/s with a turion dual core, 2GB of RAM and a 5400RPM UDMA5 HDD with postgres 8.3 and PT3 beta 13.
Hope this is useful to some of you folk, if only to know the backup/restore commands and know this method is pretty sound - remember to keep your HH's as well if you use this method, just in case this blows up and compromises the integrity of the db.
Susan
