Goofing around with postgres and PT
Posted: Wed Jun 25, 2008 6:50 amJust want to share some things i've done over the last few days; one in particular was stressing all the postgres backup & restores with PT3 on a gash system of ours.
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
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
