PDA

View Full Version : PostgreSQL 9 How-To



bviktor
10-31-2010, 05:47 AM
this howto explains how to use your latest holdem manager with the latest postgresql release while preserving your old database. the process is a bit time-consuming (for the first time), but be patient, and then you'll never have to lose your hands history ever.

1) postgresql

you can grab the latest version from the postgresql site:

EnterpriseDB: Download PostgreSQL Installers (http://www.enterprisedb.com/products/pgdownload.do#windows)

you can use both the win x86-32 or x86-64 version.

if you have an existing posgresql installation/database, don't uninstall the old postgresql version yet. install the new one (you don't need the stack builder), then open the newer pgadmin from start menu, connect to the old server (you'll need the password), select your old database, and perform a backup (right click) with compression. it's recommended to do a vacuum operation first (right click/maintenance).

then under the new server create a new database with the same settings as the old database (right click/properties), and then do a restore. the name doesn't need to match, you can use a new name if you wish. restore should exit with 0 signal. now you can uninstall your old posgresql version. don't worry, your old database still won't be removed, just becomes unused.

notice the port number for the new server in pgadmin, you'll need it.

you may have to manually start the "secondary logon" or "postgresql-9.0" services. you can do so via control panel/administrative tools/services.

you only have to do this manual database backup/restore procedure for major postgresql updates, ie. 8.x -> 9.y or 9.x -> 9.x+1 etc, not for 9.0.x -> 9.0.x+1. of course you neither have to manually backup if you don't have a previous postgresql version installed.

2) holdem manager

when installing/updating it, don't install postgresql as you have already installed the newest version.

during/after installation it will ask for database details, enter the same values as what you used during postgresql installation (user, password...).

if you haven't uninstalled old postgresql yet, holdem will still use it instead of the new one, so modify this manually from options/database management. you'll probably only have to modify the port number (see pgadmin).

FYI, localhost = 127.0.0.1, you can see localhost in pgadmin, but 127.0.0.1 on holdem manager, they are the same.

good luck!

BR50Kab
11-01-2010, 08:20 PM
HM does not use very advanced database features. Is there any need to move from an old version (i'm using 8.2) to a new one? Is this supported by HM? Is it any faster than 8.2 or 8.4?

Biggest problem for me now is while my database is getting quite big it looks like it is getting slower and slower, import is going very slow at about 20 hands/s and dropping over time. While playing or getting a graph it seemed slow as well (just vacuum cleaned and analyzed it but haven't tried yet if it improves while playing)

As an Oracle DBA i really am curious how HM would perform with Oracle 11g as database engine

bviktor
11-01-2010, 08:31 PM
i always prefer to have the latest versions of my software stack. postgres usually improves over time in speed, and the newest version is the one which gets most of the fixes, so i don't see a reason to use an older version when the newest one also works.

i also see no reason why hem would not work with the new version. it would be a dead move for psql to break compatibility.

but hey, you can try it without any trouble. just install psql9 on a different port, backup/restore, and you can switch between them in hem in seconds.

i have no clue about how oracle would perform, but knowing it's huge it would be pretty much exaggerated. seems too big for a desktop, especially for older ones.

BR50Kab
11-01-2010, 08:40 PM
i have 5 million hands in my database, it will not just be export and import. Import will take weeks, running at 16 hands a second now.... backup ands restore much faster then?

on Oracle i used a tool called SQLLoader that read flatfiles, formatted them into Oracle blocks and direct loaded them into the database (you only will see the data after the load succesfully completes) and loaded millions and millions of rows in 15 minutes, on a desktop with just two normal 7200rpm SATA disks.

bviktor
11-01-2010, 09:11 PM
my best answer to this is, we won't know how long it would take until you try it. i doubt it would take weeks. if we calculate with 2 weeks, it would mean about 5 hands/sec processing time which would be very very poor for a db, especially since the process is basically just copying.

BR50Kab
11-02-2010, 12:14 AM
yes, i made that estimate on import but import of hand histories is very slow as it has to parse every hand, do multiple inserts per hand and also maintain the indexes (which probably get skewed in the process resulting in slow insert performance and have to be rebuild later anyway)

so far:
backup of database (27G database) with compress 1h10min. Compressed backup is 2G.
backup is written to second disk, database and software will go on this new disk

created database and it is restoring but the log shows a lot of error messages due to a role that does not exist:

2010-11-02 04:47:29 CET STATEMENT: ALTER TABLE public.holecards OWNER TO postgresuser;
2010-11-02 04:47:29 CET ERROR: role "postgresuser" does not exist
after that i see a lot of checkpoint warnings saying that they occur to often. In Oracle this means the database is receiving data in a very (too) fast way.

The new Database data directory is growing like mad.

BR50Kab
11-02-2010, 01:15 AM
ok, restore took a little more than an hour so equal to backup time.

could not get connection at first but it showed that i used another username by accident (postgres) than before (postgresuser). Hope this does not do any harm. Explains the restore errors.

Database opened with other name (and port ofcourse) and retried import which is must faster now, partly because the database is no longer on the same disk as Windows and the swapfile.

edit: import seems to run at double speed as before without any signs of dropping in speed, at 43h/s after importing 50k hands

bviktor
11-02-2010, 02:24 AM
those are very promising results, glad to hear that. hopefully speed won't drop by much with time.

it would be interesting to compare this with a 64 bit version of postgres, but that would of course need a 64 bit build of holdem, which is currently unavailable afaik...

BR50Kab
11-02-2010, 12:19 PM
One important question: as i now imported under postgres instead of postgresuser will this in any way affect me, for instance with updates? Or are you free in chosing the user (the user will bevcome the owner of the objects, that is the tables, indexes etc)?


Had a slight drop in import speed from around 44h/s down to 35.4s at the moment after 1.4M imported hands. Could this be indexes that need rebuilding? I guess the migration from 8.2 to 9.0 also caused the indexes to rebuild? In the old system i saw a similar performance of around 40h/s but it started dropping much faster. Several things have changed here at once, a new version is installed, the database was migrated and the database landed on a new harddisk so we have now some i/o spreading. Hard to draw conclusions which caused the performance improvement, might be several here. I will do an index rebuild later and see if that gets import back up in the 45h/s.

Regarding 64 bit, there is a download button in your link above that says Win64, would that not be the 64bit build? I don't have a 64 bit system yet tho...

bviktor
11-02-2010, 01:10 PM
since you are the one who defines the user name in both holdem and pgadmin, there shouldn't be any problems with that user name IMHO.

thx
01-18-2011, 08:37 AM
Can an admin tells us?

What if we install the 64 bit version? Are there any advantages/disadvantages? Does HM work with it?

bviktor
01-18-2011, 08:38 AM
Can an admin tells us?

What if we install the 64 bit version? Are there any advantages/disadvantages? Does HM work with it?

....

Patvs
01-19-2011, 05:25 AM
HoldemManager works just fine with PostgreSQL 9.0.x 64 bit.

The only advantage is 9.0 64bit can fully utilize:
-the 64 bit architecture of the operating system-->
-and therefore use more than 3 GB or RAM when needed.

Note: you do need to assign more memory first to SQL by editing the postgresql.conf file

tom10167
01-26-2011, 10:12 PM
Hey, first post here.

So yesterday I switched from PT3 to HEM. My DB password is dbpass confirmed in HEM database management.

When I install 9.0(upgrading from 8.4) either 64 or 32 it says it needs the password before it will install. dbpass isn't working, though.

So I tried postgrespass and that isn't working. Blank field isn't working either. So I edited the pg_hba.conf to trust and that didn't help either.

Since installing HEM and registering I've manually added all the hands from my stored HH folder, vacuumed, analyzed and reindexed, then I deleted my pt3 DB in that PGIII file.

I'd like 9.0, preferably the 64 bit version. I'm on fully updated Vista 64 btw

So why isn't the password working?

tom10167
01-26-2011, 10:31 PM
Strange. I restarted because that'll fix things sometimes. Instead of booting in to Windows like normal it stopped and asked me which account(I only have one and it's an admin obv) and I saw I had a postgres Windows account. I booted in to my account and changed the postgres PW to something and then tried installing 9.0 and using the PW I created, it worked.

Is this normal? Because I've never had that before with previous versions of postgres

tom10167
01-26-2011, 10:40 PM
okay now I'm getting this error when I try and back up HEM1, my HEM database.(The only other database just says postgres)

C:\Program Files (x86)\PostgreSQL\8.3\bin\pg_dump.exe --host localhost --port 5432 --username "postgres" --format custom --blobs --verbose --file "C:\Users\Thomas\file.backup" \"HEM1\"
pg_dump: [archiver (db)] connection to database ""HEM1"" failed: FATAL: database ""HEM1"" does not exist
pg_dump: *** aborted because of error

Process returned exit code 1.

Veteran68
01-27-2011, 12:50 AM
Are you sure you have the correct database name? 'HEM1' sounds odd unless you changed the default which I believe is 'Holdem_Manager', but then again mine was created years ago so maybe I named it that. :) Regardless, if the database name was spelled properly and exists, you wouldn't be getting that error.

Since you're working at the command line, type: psql -l -U postgres

(assuming HEM was setup with the default postgres user, if not replace 'postgres' with the correct user name).

This will list all the databases in the first column (may be hard to read due to line wrapping.

My output looks like this:


D:\Tools\Coding\Database\PostgreSQL\8.4\bin>psql -l -U postgres
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
----------------+----------+----------+----------------------------+----------------------------+-----------------------
Holdem_Manager | postgres | UTF8 | English_United States.1252 | English_United States.1252 |
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres
: postgres=CTc/postgres
(4 rows)

BTW, HEM includes a db backup feature now. You could also backup from the pgadmin GUI if you're not comfortable on a command-line. You can just point & click to backup the db.

Veteran68
01-27-2011, 01:06 AM
it would be interesting to compare this with a 64 bit version of postgres, but that would of course need a 64 bit build of holdem, which is currently unavailable afaik...
Pat already addressed this, but just to be more to the point: a 64-bit database engine does not require a 64-bit application to access it. It's an entirely separate application. A 32-bit application just needs 32-bit database drivers. The data itself is accessed over a network socket the same way in either case, which is indifferent to "bitness."

bviktor
02-22-2011, 11:25 PM
update: i've tested it with 64 bit postgres and it works! when you get your hands on a 64 bit machine you should do some benches :)